Changing default permenant USERS tablespace to bigfile

Change existing:

SQL> create bigfile tablespace users2 datafile '/oradata/ORCL2/users.dbf' size 100M;

Tablespace created.

SQL> alter database default tablespace users2;

Database altered.

Create new one:

DBCA allows you to choose bigfile tablespace for a Custom Database:

A "General Purpose" or "Data Warehouse" database is actually a pre-created database, there you do not have this option.

Or you can specify bigfile when creating a database with the CREATE DATABASE statement.


How to see what's using TempDb space in Azure SQL DW?

So far, I've found the below, using these pages:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017

sys.dm_pdw_nodes_db_file_space_usage doesn't appear to hold any information about tempDb, so therefore we can only use sys.dm_pdw_nodes_io_virtual_file_stats.

The example in Microsoft's documentation doesn't explain what each file_id means, although the example they give filters on it. Until I can find any more information, I'll sum all files in TempDb as this appears to give the most sensible result (but it's a complete guess!).

        select sum(num_of_bytes_written) from sys.dm_pdw_nodes_io_virtual_file_stats
where database_name = 'tempdb'

I cannot find any documentation of transaction size limits for Azure DW Gen2.

This only applies to gen 1 - https://docs.microsoft.com/en-gb/azure/sql-data-warehouse/sql-data-warehouse-develop-transactions#transaction-size

Edit:

after further experimentation, it appears this query doesn't work either, the storage use always increases, so it appears to be more like a write counter. the size_on_disk_bytes never changes, so I'm not sure this would do it either.


Having Sharepoint on a different Collation than the system Databases?

Having a different default collation for the instance than for the SharePoint DB is not necessarily a problem. The main thing to ensure is that the SharePoint DB is set to the correct collation for SharePoint, which is Latin1_General_CI_AS_KS_WS, and which you have done.

You quoted a section from Supportability regarding SQL collation for SharePoint Databases and TempDB (in the future, when you quote something, you really need to attribute the source) which clearly states that SharePoint does indeed "support any CI collation for the SQL instance (for master, tempdb databases)". Hence, Latin1_General_CI_AS being case-insensitive, is supported.

The only other guidance I have found, and which makes sense, even outside of SharePoint, is this:

SharePoint SQL Collation - Best practice

The main point expressed there is that if you have more running on this instance of SQL Server than SharePoint, then sure, use whatever collation makes sense. Else, it is easier to just have it match between SharePoint and the system DBs.


Is there a way to get the IP address of a client virtual machine from pg_stat_activity?

I have added the ApplicationName to my connection string

I believe that the machine name on Oracle is voluntarily reported by the client to the server, which is why it works even behind NAT.

PostgreSQL only has one self-reported field, so I think that what you will have to do is stuff both "program name" and "machine name" into the ApplicationName field with some kind of delimiter between them.


Disk space full but logical space available in database

Space is available inside the database because data has been moved around. Perhaps you have very high levels of page splits, or have recently deleted a large portion of data that had previously caused the data file to grow.

SQL Server does not shrink database files automatically when you've freed up space within them, because the logical assumption is that if you've used that space once, you'll use it again. Autogrow can be an expensive event and unnecessary if you've only freed up space temporarily (what were you able to do with all that free space in the meantime?). For the same reasons, you shouldn't try to temporarily reclaim space, either. Just let SQL Server use the 18 GB of available space as you add more data. If you think you will need more than 18 GB of additional space going forward (in which case, you will need to add file(s) on other disks, or move to a bigger disk).

sp_spaceused (and in turn the UI dialog you're looking at) may return more space than is possible because of synchronization issues in the metadata about your tables/indexes/files. In order to make sure it reflects accurate space, run this:

DBCC UPDATEUSAGE(0);

I have also seen scenarios where rebuilding indexes was required in order to rectify the counts/space, but I haven't seen that specific case since SQL Server 2000.

(I suspect this isn't a simple case where your database already spans multiple disks, or you surely would have mentioned that in the question.)

That all said, the fact that when you shrink the data file expands almost immediately leads me to believe that you are actually using the space, but must also be performing big deletes or updates that are freeing it up (which is when you see 18 GB free). Unfortunately it's impossible for us to see exactly why the data file is expanding and then clearing itself out - perhaps you have transactions where you are truncating / re-populating large tables, performing massive archive operations, etc.


question is not clear to me but this might be what you are looking for

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l on cr.license_level = l.license_level
                and cr.residential = isnull(l.residential,1);


Internals of Windows-to-Windows SQL Server data transfer

There is a difference:

An RDBMS is optimised for such bulk row operations
A client app has to process row by row

You have factors like:

buffering of output while the client consumes it but for an RDBMS this doesn't matter
SQL Server + Windows integration
choice of engine in MySQL


The error points to a syntax problem. When you want to update multiple columns at once, you list all of the columns to be updated first, followed by all of the values to use (in the same order that the columns are listed). For example:

UPDATE CONTACT C
SET (C.PHONE, C.FAX) = (
    SELECT P.PHONE, P.FAX
    FROM PERSON P
    WHERE P.PERSON_ID = C.PERSON_ID
)
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

(You'll have to check my logic, because I'm not entirely clear on your relationships.)

Or, instead of doing an update with a correlated sub-query (as above), you can write it as a merge:

MERGE INTO CONTACT C
USING (
    SELECT PERSON_ID, PHONE, FAX
    FROM PERSON
) P
    ON (P.PERSON_ID = C.PERSON_ID)
WHEN MATCHED THEN UPDATE
    SET C.PHONE = P.PHONE, C.FAX = P.FAX
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

And another way:

UPDATE
    ( SELECT c.PHONE, c.FAX,
             p.PHONE AS P_PHONE, p.FAX AS P_FAX
      FROM PERSON p
        INNER JOIN CONTACT c ON c.PERSON_ID = p.PERSON_ID
      WHERE c.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
        )
    ) x
SET
    PHONE = P_PHONE, FAX = P_FAX ;


Would I loose that data, or not ?

Good question!  As outlined in the DataStax documentation section on The Write Path To Compaction, you would not lose data.

As you can see, when Cassandra processes a write operation, data is written to both the commit log (on-disk) and to the memtable (which you referred to).  In case of a "plug out of the wall" event, the writes are still in the commit log.  When Cassandra restarts on a node, it first replays the commit log, and verifies that all of its data have been written to disk.  If it finds data in the commit log that was not flushed to disk, it writes to disk at that time.


how can I convert multiple rows into one row

while other values of a column are NULL, easily you can use this query:

SELECT Name,MAX(property_1),MAX(property_2),MAX(property_3)
FROM YourTable
GROUP BY Name


Why is this query not using my nonclustered index, and how can I make it?

Expressing the query using different syntax can sometimes help communicate your wish to use a non-clustered index to the optimizer.  You should find the form below gives you the plan you want:

SELECT
    [ID],
    [DeviceID],
    [IsPUp],
    [IsWebUp],
    [IsPingUp],
    [DateEntered]
FROM [dbo].[Heartbeats]
WHERE
    [ID] IN
(
    -- Keys
    SELECT TOP (1000)
        [ID]
    FROM [dbo].[Heartbeats]
    WHERE
        [DateEntered] >= CONVERT(datetime, '2011-08-30', 121)
        AND [DateEntered]  < CONVERT(datetime, '2011-08-31', 121)
);

Compare that plan with the one produced when the non-clustered index is forced with a hint:

SELECT TOP (1000)
    *
FROM [dbo].[Heartbeats] WITH (INDEX(CommonQueryIndex))
WHERE
    [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

The plans are essentially the same (a Key Lookup is nothing more than a seek on the clustered index).  Both plan forms will only ever perform one seek on the non-clustered index and a maximum of 1000 lookups into the clustered index.

The important difference is in the position of the Top operator.  Positioned between the two seeks, the Top prevents the optimizer from replacing the two seeking operations with a logically-equivalent scan of the clustered index.  The optimizer works by replacing parts of a logical plan with equivalent relational operations.  Top is not a relational operator, so the rewrite prevents the transformation to a clustered index scan.  If the optimizer were able to reposition the Top operator, it would still prefer the scan over the seek + lookup because of the way cost estimation works.

Costing of scans and seeks

At a very high level, the optimizer's cost model for scans and seeks is quite simple: it estimates that 320 random seeks cost the same as reading 1350 pages in a scan.  This probably bears little resemblance to the hardware capabilities of any particular modern I/O system, but it does work reasonably well as a practical model.

The model also makes a number of simplifying assumptions, a major one being that every query is assumed to start with no data or index pages already in cache.  The implication is that every I/O will result in a physical I/O - though this will rarely be the case in practice.  Even with a cold cache, pre-fetching and read-ahead mean that the pages needed are actually quite likely to be in memory by the time the query processor needs them.

Another consideration is that the first request for a row that is not in memory will cause the whole page to be fetched from disk.  Subsequent requests for rows on the same page will very likely not incur a physical I/O.  The costing model does contain logic to take some account of effects like this, but it is not perfect.

All these things (and more) means the optimizer tends to switch to a scan earlier than in probably should.  Random I/O is only 'much more expensive' than 'sequential' I/O if a physical operation results - accessing pages in memory is very fast indeed.  Even where a physical read is required, a scan may not result in sequential reads at all due to fragmentation, and seeks may be collocated such that the pattern is essentially sequential.  Add to that the changing performance characteristic of modern I/O systems (especially solid-state) and the whole thing starts to look very shaky.

Row Goals

The presence of a Top operator in a plan modifies the costing approach.  The optimizer is smart enough to know that finding 1000 rows using a scan will likely not require scanning the whole clustered index - it can stop as soon as 1000 rows have been found.  It sets a 'row goal' of 1000 rows at the Top operator and uses statistical information to work back from there to estimate how many rows it expects to need from the row source (a scan in this case).  I wrote about the details of this calculation here.

The images in this answer were created using SQL Sentry Plan Explorer.


Availability Group: ReadIntent connection failing

Script to create read only rounting:

/*

set up read-only intent routing

Test it:

sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername" -Kreadonly
sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername"

*/

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com \instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));-- not read_only

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node1.DevDomain.com:49598'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node2.DevDomain.com\instance:49877'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node1.DevDomain.com\instance'
                ,'node2.DevDomain.com\instance'
                ))
        );

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instanced'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node2.DevDomain.com\instancee'
                ,'node1.DevDomain.com\instance'
                ))
        );
go


MobiLink: How to add a new table to synchronisation using Sybase Central and mlagent

This seems¹ to work:

Create a new Remote Schema Name (e.g. v2.0)
Do changes on your consolidated database (e.g. CREATE TABLE-statement)
Update schema inside your synchronisation profile and maybe increase version string (on my current test it appears to doesn't matter)
Do a deployment of your update synchronisation profile. You will need update ml_columns and ml_tables as well as ml_table_scripts etc on your consolidated database fitting your update synchronisation profile. So apply the changes to it. Also you will need the SQL of remote database
Create an upgrade remote task including the remote SQL created with step 4 (EXECUTE SQL). This task should also change Remote Schema Name
Maybe iterate your sync- task and deploy both -- upgrade task and sync task
Should be done

The biggest issue is, that the script in default is droping the tables on remote side so a huge amount of data might be transferred after update.

¹ I was able to get this running on a local test scenario with minimum database.


What are the downsides of running DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') hourly?

From Comments:

This sounds like a prime candidate for a Microsoft Product Support engineer. - Max Vernon

We experienced the same issue and our final decision was to use DBCC FREESYSTEMCACHE (&#39;TokenAndPermUserStore&#39;) on a daily basis. We consider the solution as more  predictable and managed than trace flags using. Unfortunely, MS wasn't able to find and offer some better solution.  - Denis Rubashkin

This is an old bug, I have a hunch that latest SP/CU should fix it. If not, you may need to contact MS support. As for question I faced this issue a lot in SQL Server 2008 R2 version and selectively clearing cache did not cause any issue. - Shanky


How to make datapump apply directly to the destination schema

Using Network Link Import to Move Data

  When the Import NETWORK_LINK parameter is used to specify a network
  link for an import operation, SQL is used to move the data using an
  INSERT SELECT statement. The SELECT clause retrieves the data from the
  remote database over the network link. The INSERT clause uses SQL to
  insert the data into the target database. There are no dump files
  involved.

NETWORK_LINK


Chaning tempdb settings on production database

Sure, you can do it with little risk, but it depends on the state of your current tempdb.  If tempdb has grown quite large, you might need to shrink it first before you have enough space for perhaps 4 good size tempdb files.

Because of how active tempdb is and how hard it is to shrink that you might need to schedule a little downtime to make the clean change with all files of exactly the same size.

See a post outlining a few issues at: http://www.brentozar.com/blitz/tempdb-data-files/

Notice that there are a couple of subtle decisions to make.  In the sample scripts it describes four 8 GB files, but the sizing naturally needs to support your space requirements. Consider whether tempdb auto-growth should be allowed; Brent recommends correct sizing with no auto-growth.


TokuDB is a storage engine in MariaDB, as is XtraDB/Aria/MyISAM. Queries are planned by the upper layers of MariaDB (and MySQL) calling into the storage engine to help it make decisions about optimization.

What you need to do is

create your tables and load your data
create the indexes you think will help optimize your query workload
run your queries with EXPLAIN

The EXPLAIN output will help you see that good decisions are made based on your indexes.


MyISAM defaults to 6 bytes for a data pointer in an index.  And defaults to 5 bytes for index pointers.  The "6" you are referring to is a byte offset from the .MYI file into the .MYD file.  That is not necessarily relevant to the rest of your question.

InnoDB is much more complex.  It does not use pointers, only columns.

The data is stored in a BTree that is ordered by the PRIMARY KEY.  (Actually a B+Tree.)

A secondary index is stored in a separate BTree that has the PK in each node record.

FKs are simply the indicated column(s), and are used to do a lookup in a suitable index in the other table.

id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

takes 1 byte, plus some overhead

id VARCHAR(255) CHARSET utf8mb4 PRIMARY KEY,
stuff1 VARCHAR(255) CHARSET utf8mb4,
stuff2 VARCHAR(255) CHARSET utf8mb4,
INDEX(stuff1, stuff2)

This last secondary index takes up to 3*(2+255)*4+? bytes --

3 columns (stuff1, stuff2, id)
2 -- length
255 -- max length in characters for utf8mb4
4 -- max bytes/character in utf8mb4
+? -- There is overhead; this varies.

Back to "pointers".  There are some "pointers" at lower levels:

4 byte block pointer (16KB blocks), hence a limit of 64TB on table size.
20 byte pointer to overflow data (used to point to the 'rest' of a large TEXT or BLOB.
Some 1-byte and 2-byte lengths or pointers inside a block.
? bytes for a transaction id.  (See MVCC, etc)

As a Rule of Thumb, an InnoDB table takes 2-3 times the disk space as the equivalent MyISAM table.

  So what is the size of pointer to a tuple in InnoDB Mysql systems?

If TINYINT UNSIGNED suffices (up to 255 items in the other table), then think of it as 1 byte in each table, plus 1 byte for each secondary key in the target table.
INT is often used (with a usually more-than-adequate 2-billion limit), think of it as 4 bytes each.

Perhaps a more important question is whether to have a 'surrogate' (AUTO_INCREMENT) id on a table.  Count the number of references in this table and from other tables to judge its bulk.  And use the smallest flavor of INT that will be safe.

That brings up "burning" of ids.  In almost all cases, INSERT (and its variants) will first allocate the auto_inc ids that it might need.  If it does not use them all (eg for INSERT IGNORE), the ids are lost (burned).  This can lead to using up, say, TINYINT much faster than anticipated.  For bulk normalization, I provide this technique:  http://mysql.rjweb.org/doc.php/staging_table#normalization


Restore from SQL Server 2008 R2 to SQL Server 2014 fails

You will not be able to restore a database from Microsoft SQL Server 2008 (paid version) to Microsoft SQL Server 2014 Express (Free version) due to its database file limit.

For others who need reference

SQL Server Express versions have a hard size limit on the database size.

I have listed them below from 2000-2008_r2

MSDE "SQL Server 2000" 2gig free limit
SQL Server 2005 4Gig free limit
SQL Server 2008 4gig free limit.
SQL Server 2008 R2 and newer: 10gig free limit.

Please upgrade to SQL Server 2014 (Paid version) to resolve this issue.


How to use variables in a mongodump query

You can do it like this:

start="new Date(1000*$(date -d `date -d yesterday +%F` +%s))"
end="new Date(1000*$(( $(date -d `date +%F` +%s) - 1 )))"
query="db.collection.find({created_at: {\$gte: $start, \$lte: $end}});"

echo $query
db.collection.find({created_at: {$gte: new Date(1000*1575586800), $lte: new Date(1000*1575673199)}});

Putting all in one line is a bit ugly but possible:

mongodump --db dbname --collection cname --query 'db.collection.find({created_at: {$gte: '"new Date(1000*$(date -d `date -d yesterday +%F` +%s))"', $lte: '"new Date(1000*$(( $(date -d `date +%F` +%s) - 1 )))"'}})' --out collection_01NOV2019

Note, in crontab you have to write +\% rather than +%. Perhaps it will be more clear when you write a 3-line shell script and execute this by cron.

Simplified alternative:

start="new Date('`date -d yesterday +%FT00:00:00`')"
end="new Date('`date -d yesterday +%FT23:59:59`')"
query="db.collection.find({created_at: {\$gte: $start, \$lte: $end}});"

echo $query
db.collection.find({created_at: {$gte: new Date('2019-12-05T00:00:00'), $lte: new Date('2019-12-05T23:59:59')}});


Reporting Services 2016 Multiple Email Accounts

You can only specify one mail account per SSRS installation.


Always-on Avaialbility Group Secondary replica NOT identical to Primay

It is supported, the recommendation from the docs is:

  For a given availability group, all the availability replicas should
  run on comparable systems that can handle identical workloads.

Recommendations for Computers That Host Availability Replicas

Having a larger system host a secondary replica would qualify, as both systems can handle the workload.  This is really no different than having multiple readable replicas.

In your estimate of read/write split, make sure you understand that even for a synchronous replica the redo of the log is asynchronous, and so the secondary will always be slightly behind the primary.  Any read-only query that cannot tolerate this latency must be run against the primary.  EG in an application if you write data, and then immediately query data to display to the user, expecting to see the data you just wrote, both queries must hit the primary.

Note that in case of a failure of the secondary, read-only routing will direct all traffic to the primary, which it may not be capable of handling.  So if you do this you may want to have reporting workloads connect to the secondary replica directly, instead of through the Availability Group Listener.

Also note that the IO requirements of a secondary are sometimes more than the primary, as the secondary is writing the database through the redo process, while the primary is writing the database through checkpoint/lazy writer.


Where are SQL Server catalog views persisted?

Catalog views do not provide access to replication, SQL Server Agent, or backup metadata.  Those are available in MSDB only.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

https://technet.microsoft.com/en-us/library/ms179503(v=sql.105).aspx

  The Resource database is a read-only database that contains all the
  system objects that are included with SQL Server. SQL Server system
  objects, such as sys.objects, are physically persisted in the Resource
  database, but they logically appear in the sys schema of every
  database. The Resource database does not contain user data or user
  metadata.

  The Resource database makes upgrading to a new version of SQL Server
  an easier and faster procedure. In earlier versions of SQL Server,
  upgrading required dropping and creating system objects. Because the
  Resource database file contains all system objects, an upgrade is now
  accomplished simply by copying the single Resource database file to
  the local server.

  The physical file names of the Resource database are
  mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are
  located in :\Program Files\Microsoft SQL
  Server\MSSQL.\MSSQL\Binn\ and should not be
  moved. Each instance of SQL Server has one and only one associated
  mssqlsystemresource.mdf file, and instances do not share this file.

  System base tables are the underlying tables that actually store the
  metadata for a specific database. The master database is special in
  this respect because it contains some additional tables that are not
  found in any of the other databases. These tables contain persisted
  metadata that has server-wide scope. The Resource database contains
  the same base table schema as any other non-master database.


Basically, this is about finding a row-wise minimum.

There is an elegant inline solution in the most upvoted answer to this Stack Overflow question:

SQL MAX of multiple columns?

According to that solution, if you have a table T of this kind:

C1  C2  C3
--  --  --
…   …   …
…   …   …

you can find a row-wise minimum like this:

SELECT
  C1,
  C2,
  C3,
  (
    SELECT MIN(C)
    FROM (VALUES (C1), (C2), (C3) AS v (C)
  ) AS MinC
FROM
  T
;

Basically you are arranging the values of C1, C2, C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.

Now in your case the C1, C2 etc. are expressions. Usually that is fine, you can use the VALUES row constructor with expressions. But in this case each expression already contains an aggregate function (SUM()), which prevents us from applying the method directly (VALUES expressions must not use aggregate functions).

However, that issue is easily resolved. You can use your current query as a derived table or a CTE and apply the method at the outer level, where the expressions will be just references, like this:

SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  (
    SELECT
      ... /* your current query */
  ) AS derived
;

And here is an identical solution but using a CTE:

WITH cte AS
  (
    SELECT
      ... /* your current query */
  )
SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  cte
;


How to use query result as REPLACE parameters

Managed to solve it on my own rookie way:

1- Since i couldn't make the REPLACE function to accept a query as parameter i just inserted the query result in a new table column:

INSERT INTO VideoPlayerUdpate_FLV_links (`ID`,`PostTitle`,`AmazonLinks`) SELECT `ID`, `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts WHERE `post_content` LIKE '%[flv:%'

Remember I had another table VideoPlayerUdpateLinksReady with all my new string that i wanted to replace. I had to manually construct and validate all errors in this column VideoPlayerUdpateLinksReady.Png_Link so it would be ready to replace.

2- So final Step was only to use these 2 tables as parameters in the REPLACE function:

UPDATE `wp_posts_duplicatedfortestonly`  JOIN `VideoPlayerUdpate_FLV_links` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpate_FLV_links.ID  JOIN `VideoPlayerUdpateLinksReady` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpateLinksReady.ID SET `post_content` = REPLACE(`post_content`,VideoPlayerUdpate_FLV_links.AmazonLinks, VideoPlayerUdpateLinksReady.Png_Link)

And i got all my content replaced without any further issues.


The SQL Server instance has no notion of the name resolution the client used to connect to the server IP address so what you want is not possible.
@@SERVERNAME returns the server name or FCI network name, plus instance name for a named instance. The value is unrelated to the client connection specification.


Error when only deploying a dimension on SSDT

I don't speak Portuguese very well  but it looks as if it's saying that it can't connect to the datasource.

Please verify if your Analysis services service account has access to the data source.

Keep in mind that, unless you specify the username and password the processing is done using the Analysis Services service account while when you browse data from within visual studio you are using your own account.

Please read through Set Impersonation Options which explains in detail how the different data source options work.


Missing "MSVCR120.dll" during the Installation of MySQL Workbench 6.3 CE

I am getting the same error and I installed using MSI.

Downloaded the VC redist packages for x64 and it fixed the issue:

Visual C++ Redistributable Packages for Visual Studio 2013

Strange that the error message has python.exe as it's title.


Repmgr: Error to promote Standby Cluster to Master Cluster in failover

check out this useful link :

•   http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html


Does CPU utilization affect the cost of foreign NUMA access?

A hefty question :-)
I'll outline some of the factors involved.  In any given context, these factors and others can vary and produce an interesting result.

Sorry I wasn't able to make this much shorter...

Accumuated CPU ms vs logical IO
SQL Server logical memory node alignment with physical NUMA nodes
Spinlock contention in query workspace memory allocation
Task assignment to schedulers
Relevant data placement in the buffer pool
Physical memory placement

Accumuated CPU ms vs logical IO

I use graphs of logical IO (or in perfmon terminology "buffer pool page lookups") against CPU utilization very often, in order to gauge cpu efficiency of workloads and look for spinlock prone cases.

But SQL Server accumulates CPU time with lots of activity other than page lookups and spinlocks:

Plans are compiled and re-compiled.
CLR code is executed.
Functions are performed.

A lot of other activities will chew up significant cpu time without being reflected in the page lookups.

In the workloads I observe, chief among these "non logical IO intensive but CPU-gobbling" activities is sorting/hashing activity.

It stands to reason: consider a contrived example of two queries against a hashtable with no nonclustered indexes.  The two queries have identical resultsets, but one of the resultsets is completely unordered and the second resultset is ordered by more than one of the selected columns.  The second query would be expected to consume more CPU time, even though it would reference the same number of pages in the buffer pool.

More about workspace memory, and how much of granted workspace has been used, in these posts:

http://sql-sasquatch.blogspot.com/2015/08/sql-server-grantedreservedstolen_4.html
http://sql-sasquatch.blogspot.com/2015/08/sql-server-workspace-memory-with-twist.html
http://sql-sasquatch.blogspot.com/2015/03/resource-governor-to-restrict-max-query.html

SQL Server logical memory node alignment with physical NUMA nodes

SQL Server (since incorporating its NUMA-aware strategies) by default creates a SQLOS memory node for each NUMA node on the server.  As memory allocations grow, each allocation is controlled by one of the SQLOS memory nodes.

Ideally, the SQLOS memory nodes are completely aligned with the physical NUMA nodes.  That is to say, each SQLOS memory node contains memory from a single NUMA node, with no other SQLOS memory node also containing memory from that same NUMA node.

However, that ideal situation is not always the case.

The following CSS SQL Server Engineers blog post (also included in Kin's response) details behavior which can lead to persisting cross-NUMA node memory allocations for the SQLOS memory nodes.  When this happens, the performance impact can be devastating.

http://blogs.msdn.com/b/psssql/archive/2012/12/13/how-it-works-sql-server-numa-local-foreign-and-away-memory-blocks.aspx

There have been a few fixes for the particularly painful case of persistent cross-NUMA node reference.  Probably others in addition to these two, as well:

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014
FIX: SQL Server performance issues in NUMA environments

Spinlock contention during allocation of workspace memory

This is where it starts to get fun.  I've already described that sort and hash work in workspace memory consumes CPU but is not reflected in the bpool lookup numbers.

Spinlock contention is another layer to this particular fun.  When memory is stolen from the buffer pool and allocated for use against a query memory grant, memory access is serialized with a spinlock. By default, this takes place with a resource partitioned at the NUMA node level.  So every query on the same NUMA node using workspace memory can potentially experience spinlock contention when stealing memory against grants.  Very important to note: this isn't "one time per query" contention risk, as it would be if the point of contention were at the time of the actual grant.  Rather, its when memory is stolen against the grant - so a query with a very large memory grant will have many opportunities for spinlock contention if it uses most of its grant.

Trace flag 8048 does a great job of relieving this contention by further partitioning the resource at the core level.

Microsoft says "consider trace flag 8048 if 8 or more cores per socket".  But... it's not really how many cores per socket (as long as there are multiple), but rather how many opportunities for contention in the work being done on a single NUMA node.

On the glued AMD processors (12 cores per socket, 2 NUMA nodes per socket) there were 6 cores per NUMA node.  I saw a system with 4 of those CPUs (so eight NUMA nodes, 6 cores each) that was jammed up in spinlock convoy until trace flag 8048 was enabled.

I've seen this spinlock contention drag down performance on VMs as small as 4 vCPUs.  Trace flag 8048 did what it was supposed to when enabled on those systems.

Considering that there are still some 4 core frequency optimized CPUs out there, with the right workload, they'd benefit from trace flag 8048 also.

CMEMTHREAD waits accompany the type of spinlock contention that trace flag 8048 relieves.  But a word of caution: CMEMTHREAD waits are a corroborating symptom, not root cause for this particular issue.  I've seen systems with high CMEMTHREAD "wait starts" where trace flag 8048 and/or 9024 were delayed in deployment because accumulated CMEMTHREAD wait time was fairly low.  With spinlocks, accumulated wait time is usually the wrong thing to look at. Rather, you want to look at wasted CPU time - represented primarily by the spins themselves, secondarily by the associated waits which represent potentially unnecessary context switches.

How It Works: CMemThread and Debugging Them

Task assignment to schedulers

On NUMA systems, connections are distributed to NUMA nodes (well - actually to the SQLOS scheduler groups associated with them) round-robin, assuming there aren't connection end points associated with particular NUMA nodes.  If a session executes a parallel query, there is a strong preference to use workers from a single NUMA node.  Hmmm... consider a 4 NUMA node server with a complex query broken into 4 paths, and default 0 MAXDOP.  Even if the query used only MAXDOP worker threads, there would be 4 worker threads for each logical CPU on the NUMA node.  But there are 4 paths in the complex plan - so each logical CPU on the NUMA node could have 16 workers on it - all for a single query!

This is why sometimes you'll see one NUMA node working hard while others are loafing.

There are a few other nuances to task assignment.  But the main takeaway is that CPU busy won't necessarily be evenly distributed across the NUMA nodes.
(Also good to realize that bpool page inserts (reads or first-page-writes) will go into the bpool in the SQLOS memory node associated with the scheduler the worker is on.  And stolen pages will preferentially come from the "local" SQLOS memory node, too.

I've found that bringing maxdop from 0 to no more than 8 is helpful.  Depending on the workload profile (primarily imo on the number of concurrent expected potentially long-running queries), going all the way to MAXDOP=2 may be warranted.

Adjusting the cost threshold for parallelism may also be helpful.  Systems I work on tend to be consumed with high cost queries and rarely encounter a plan below 50 or 100, so I've had more traction by adjusting maxdop (oten at workload group level) than adjusting cost threshold.

SQL Server PLE with 8 NUMA nodes

In this post, a combination of spinlock contention around workspace memory and uneven task distribution is discussed.  See - these things really do all weave together :-)
40 concurrent SQL Server parallel queries + (2 sockets * 10 cores per socket) = spinlock convoy

Relevant data placement in the bpool

This is the condition that I think is most intuitive when dealing with NUMA servers.  Its also, most typically, not extremely significant to workload performance.

What happens if the table is read into the bpool on NUMA node 3, and later a query on NUMA node 4 scans the table performing all bpool lookups across NUMA nodes?

Linchi Shea has a great post on this performance impact:

http://sqlblog.com/blogs/linchi_shea/archive/2012/01/30/performance-impact-the-cost-of-numa-remote-memory-access.aspx

Accessing memory across NUMA nodes incurs a small amount of additional memory latency.  I'm sure there are some workloads that need to eliminate that additional base memory latency for optimal performance - its not been an issue on the systems I work with.

But - cross-node access also brings another point of transfer which can potentially saturate.  If there is so much activity that memory bandwidth between NUMA nodes is saturated, memory latency between the nodes will increase.  The same work will require additional CPU cycles.

Again - I'm sure there are workloads such that memory bandwidth is a critical consideration.  For my systems, though, the other considerations I am listing have been more significant.

Physical memory placement

This one is rare but when it matters, it really matters.  On most servers, memory install is almost naturally going to balance across the NUMA nodes.  But ins some cases, special attention is needed for balancing the memory across the nodes.  Performance in some systems can be absolutely trashed if the memory was slotted in such a way that its not balanced.  This is set-it-and-forget-it, though.  Pretty rare to discover a problem like this after months of production service as opposed to after the first really busy day :-)

THE BIG FINISH!

Someone else made the point that poor plan choice, perhaps due to outdated stats, could result in the symptoms you've seen.  That hasn't been the case in my experience.  Poor plans can easily make a query take longer than expected - but usually because more logical IOs than necessary are being performed.  Or due to spill to tempdb.  Massive spill to tempdb should be evident when observing the server - and rather than high CPU one would expect measurable wait time for the spill-related disk writes.

Instead, if the situation you observed is NUMA-related, I'd expect it to be a combination of the factors enumerated above, mostly:

use of workspace memory(which won't show up in logical IO counts)
which may be cross-NUMA node due to persistent foreign memory condition(if this is the case, look for relevant fixes)
and which may incur spinlock contention within the NUMA node each time an allocation is made against a grant(fix with T8048)
and may be performed by workers on logical CPUs overloaded by other parallel query workers(adjust maxdop and/or cost threshold of parallelism as necessary)


Where does InnoDB store transaction data before committing it?

"Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?"

The new uncommitted record (clustered PK) versions are treated as the "current" version of the record on page. So they can be stored in the buffer pool and/or in the tablespace (e.g. tablename.ibd). Transactions that then need to build a snapshot/view in anything other than READ-UNCOMMITTED, need to construct a previous version of the row (following the history list) using the UNDO records (stored in the system tablespace). When reading the uncommitted record, InnoDB may also need to read some uncommitted secondary index records from the Change Buffer and apply them before presenting the record back to the user.

It's this behavior that can make rollbacks in InnoDB relatively expensive. It's the big factor that can also lead to potential performance issues from long running idle transactions that are holding updated records, as those transactions will block purge operations and the history list of old record versions grows, and the UNDO records needed to rebuild those old versions on-demand, will continue to grow. It slows down new transactions that need to read an older/committed version of the record, as they need to traverse a longer and longer history list--which is a singly linked list of UNDO records--and do more work in order to reconstruct the old version of the record. So you end up using a lot of CPU cycles (not to mention internal locking primitives: mutexes, rw_locks, semaphores, etc.) on non-user visible work that slows down query processing.

Hopefully that makes sense? :)

As an FYI, in MySQL 5.7 you can move the UNDO tablespace and logs out of the system tablespace, and have them automatically truncated. They can grow quite large if you have a long running transaction that prevents purge operations, resulting in a very long and ever growing history list length. Having them stored in the system tablespace was the single most common cause of a huge/growing ibdata1 file, which in turn cannot be truncated/shrunk/vacuumed in order to later reclaim that space.


how to approach user acceptance testing for merged constituent records in a CRM system?

With 3 million records, and 25% duplicates, you've got 750,000 constituent records that need to be merged & verified. That's a lot of manual work to merge & verify all 750,000 constituents individually.

Presumably there is some automation that you are going to use to identify & merge all of these constituent records. For your User Acceptance Testing, you don't need to verify every merge, you just need to verify a sampling of different scenarios. Your task is to devise a list of test cases that represent those different scenarios. In short, you are trying to do comprehensive (not random) spot-checking

Usually, you can devise the scenarios based on the same business rules that were used to create the automation.

For example:

Ensure that for a given merge set, a single constituent record is active, and the duplicates are deactivated/deleted.
You merge two constituents, each with different home addresses. Both constituents have that address set to preferred/primary. Should the merged record have both addresses? Which address should be the preferred/primary address?
You merge two constituents with the same last name, and same address, but different first names. How should these names be combined? Are they treated as a single person (perhaps a nickname & formal name), or as a couple/spouses/household?
You have two constituents with different first & last names, but the same address. Should they be treated as a couple/household & merged? Should they remain separate, but both active? Should you assume that one moved out & another moved in at the same address, and simply invalidate the address on the older of the two records?
etc etc... for any scenarios you care about.

As part of your test cases, you would need to provide answers for the questions I pose above, in order to tell testers what they are looking for. Essentially, you provide a scenario, and desired result. Testers need to validate that the actual result matches with the desired result. If it matches, the test case passes. If it doesn't match, then the test case fails.

In some cases you might want to combine scenarios, to sort out possible conflicts in your criteria.

Given that you have an existing database to work with, you may want to identify specific records to use as part of UAT. Your test case could identify the specific records, the pre-merge data, and the expected post-merge results. Testers would then look up those records and verify the results match what is expected.1

Once you've devised your comprehensive list of scenarios & desired results, you'll have a comprehensive test plan to use for your User Acceptance Testing!

1In software development, even the testing is automated, rather than doing this check manually--but for a one-time project that level of automation is probably not necessary--it's more work than just doing it manually.)


Is it okay to rename the MySQL data files (FRM) manually?

change lower_case_table_names back again

Then you should be able to access your tables.

But yes, as long as they as MyISAM tables you CAN rename them. Best to do it while mysql is shutdown. Need to change the MYD and MYI files too.


Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Tested on 5.5.34

I looked at this in detail (I'm interested for an upcoming project, and it's been a while since I've "got my hands dirty"...)

It appears that datetimes are hard to set defaults for - see here. From what I've read, things have got better in 5.6 - I'll see if I have time to test tomorrow.

I think that your best solution is as below. The answer to your question about the best way to CREATE your TABLE is shown (IMHO). It involves inversing the ON UPDATE clause, putting it (logically enough) with the updated_at field.

mysql> CREATE TABLE mt8
    -> (
    ->   billy int,
    ->   created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
    ->   updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At'
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql>
mysql> DELIMITER $$
mysql> create trigger my_ins8_trigger
    -> before insert on mt8
    -> for each row
    -> begin
    ->   set new.created_at = current_timestamp;
    -> end $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql>
mysql>
mysql> insert into mt8 values(123, null, null);
Query OK, 1 row affected (0.08 sec)

mysql> insert into mt8 values(454, null, null);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mt8 values(1676, null, null);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mt8 (billy) values(34334);
Query OK, 1 row affected (0.07 sec)

mysql> insert into mt8 (billy) values(3506);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mt8 (billy) values(3435454);
Query OK, 1 row affected (0.05 sec)

mysql> select * from mt8;
+---------+---------------------+---------------------+
| billy   | created_at          | updated_at          |
+---------+---------------------+---------------------+
|     123 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|     454 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|    1676 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|   34334 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
|    3506 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
| 3435454 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
+---------+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> update mt8 set billy = 455 where billy = 454;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mt8;
+---------+---------------------+---------------------+
| billy   | created_at          | updated_at          |
+---------+---------------------+---------------------+
|     123 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|     455 | 2014-06-12 01:53:40 | 2014-06-12 01:54:26 |
|    1676 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|   34334 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
|    3506 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
| 3435454 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
+---------+---------------------+---------------------+
6 rows in set (0.00 sec)
mysql>

For the row with billy = 455, the update timestamp has changed, but not the created one - exactly as you'd want!


Ola's Maintenance Solution Plan script newer version

how can i upgrade easily on all my servers?

You should use dbatools - Install-DbaMaintenanceSolution

  This script will download and install the latest version of SQL Server Maintenance Solution created by Ola Hallengren

Alternatively, Ola's solution is on github


I'd go b.  A person is a person.  Record that then layer over what role they take in your organisation.

Consider that an employee might also be a customer.  A person might take the role of other participants that you have not yet thought of or chosen to model.

A customer can also be a business which would have a different shape.

Lots of potential reasons to keep them separate.


Executed a Stored Procedure in the Production Server and the following warning message is shown

When you are closing SSMS, it check for

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

Or you can use sys.dm_exec_requests

SELECT open_transaction_count FROM sys.dm_exec_requests WHERE session_id = @SessionId

In this case you have an open transaction so on connection closure process SQL Server will roll back that transaction. In this case you need to take a look at the stored procdure dbo.GenActiveItemPriceList and identify what is this transaction is which is not commited and it's furter impact.


Sql Server merge replication primary key violations

I'd change the IDENTITY so one is odd, one even. Or negative/positive.

Probably the simplest way to do it...

Edit: I assumed identity PK columns.

I'd suggest you update the question with the relevant info contained in the comment. It's incomplete otherwise.

You said "The desired behaviour is that after the merge all rows are identical. The first one to the publisher wins..."

This sounds like conflict resolution, which is how you'd deal with PK collisions.


Measuring SQL execution time in PostgreSQL?

There isn't a reasonably easy way to do this, but you can:

Log slow queries
Use EXPLAIN ANALYZE to see where the query is actually spending its time.

You could probably store plans in the database somehow but I am not quite sure how this would be done (maybe with a pl/perl function?).  You'd have to roll your own solution though.

Storing plans in the database however is not easy.  One of the huge problems that you will have is getting the recursive structure right so you can query it.  A simple approach might be to take a JSON format and store that, or store XML.


How do I create a rule for a Primary Key

You probably don't want to use rules and instead use constraints, in this case a check constraint.  The reason that you don't want to use rules is that rules have been deprecated means that they will be removed from SQL Server at some point in the future so it would be better to use the check constraint instead.  Something like this will do the trick.

create table t1 (c1 char(4) primary key check (c1 like '[a-z][0-9][0-9][0-9]'), c2 int)


Representation of circular references constraint

All you need is to propagate TypeId down to the link table:

CREATE TABLE Commands(
    CommandId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniqueCommandNameInSpecificType UNIQUE(Name, DeviceTypeId),
-- added this constraint
    CONSTRAINT Commands_FkTarget UNIQUE(CommandId , DeviceTypeId)
);

CREATE TABLE DeviceProperties(
    PropertyId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniquePropertyInSpecificType UNIQUE(TypeId,Name),
-- added this constraint
    CONSTRAINT DeviceProperties_FkTarget UNIQUE(PropertyId , DeviceTypeId)
);

CREATE TABLE DevicePropertiesInCommands(
    CommandId int NOT NULL,
    PropertyId int NOT NULL,
        DeviceTypeId INT NOT NULL,
    OrderNoOfProperty int NOT NULL,
    PRIMARY KEY(CommandId, PropertyId),
    CONSTRAINT UniqueOrderNoInSpecificCommand UNIQUE(CommandId,OrderNoOfProperty),
        CONSTRAINT FK_DevicePropertiesInCommands_Commands
            FOREIGN KEY(CommandId , DeviceTypeId ) REFERENCES Commands(CommandId , DeviceTypeId ),
        CONSTRAINT FK_DevicePropertiesInCommands_DeviceProperties
            FOREIGN KEY(PropertyId , DeviceTypeId ) REFERENCES DeviceProperties(PropertyId , DeviceTypeId )
);


I'd suggest the first thing to determine is whether or not the server workload actually struggles during those spikes. Do you see for example IO being maxed out, or other queries being blocked (due to the splits increasing the time the write transactions take to complete) or slowed? If not, the page splits are not an immediate concern, but still may be worth looking into.

The question is, are these "good" or "bad" page splits. This link might help you determine what you're seeing. Logging your index fragmentation levels before and after a spike might also be a simpler way.

"Good" page splits are simply inserts to the end of an increasing index (clustered or otherwise)that require a new blank page, so it's really not a page split as generally thought of, even though SQL Server counts it as such--presumably because there is some overhead, but probably not more than the inserts cost in general.
"Bad" page splits are updates or inserts to the middle of an index that overflow the page, and are the ones that cause both internal and external fragmentation, with external not much of an an issue with SSDs and/or shared storage, and internal being of more potential impact due to the IO and cache memory they waste.

It could be that you've got a mix of good and bad, perhaps good into the clustered index and bad in multiple non-clustered indexes. That's pretty much unavoidable, and you'll just need to consider your index maintenance and possibly a specific fill factor on indexes that are frequently affected. But read https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ first.

However if you find your clustered index is being bad-splitted, then it may be worth considering whether a clustered index that better supports your inserts would be in order. Or if the splits are caused by updates adding more data during the life of a record, a specific fill factor might be in order, but really only if the updates are evenly distributed throughout all your data, since a fill factor to support only your recent data would waste a lot of space/IO/cache if most of your data is static over time.
The ideal clustering config really depends how your table is used overall though, not just on how it's written to.


Database Design for Employee Information

The most common pattern for handling historical data in an OLTP schema is to have a current table and a separate history table.  So any time an employee changes department, the employee_department table is updated, and a new row is inserted into the employee_department_history table.

You can also use a single table with begin/end dates.  This example of this pattern in SQL Server's older AdventureWorks sample database.  Which tracks both department and position (shift) in a single table:

CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [BusinessEntityID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED
(
    [BusinessEntityID] ASC,
    [StartDate] ASC,
    [DepartmentID] ASC,
    [ShiftID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SQL Server eventually added a feature to implement the current/history pattern automatically called Temporal Tables, and the newer WideWorldImporters sample database uses those.


MySQL - view select rooms not in booking range

Try this:

SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1
                    FROM BOOKROOM br
                    JOIN BOOK b ON br.ref = b.ref
                    WHERE r.id = br.id
                        AND ('2019-11-22' BETWEEN b.Start_Date AND b.End_Date
                            OR '2019-11-28' BETWEEN b.Start_Date AND b.End_Date
                            OR b.Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            OR b.End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            )
                  );

Or if you add room id to BOOK table you could use such query:

SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1
                    FROM BOOK
                    WHERE r.id = id
                        AND ('2019-11-22' BETWEEN Start_Date AND End_Date
                            OR '2019-11-28' BETWEEN Start_Date AND End_Date
                            OR Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            OR End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            )
                );

In this case you can create supporting index ON BOOK(id, Start_Date, End_Date)


Get monthly sums from several tables with same (date,amount) columns

It is hard to explain where you go wrong, because your logic doesn't fit in mysql.
AS you can see in my sample, you have to add a column that describes, from which table you get the data
This you can use to SUM the amount

CREATE TABLE table1
    (`date` varchar(10), `amount` int)
;

INSERT INTO table1
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table2
    (`date` varchar(10), `amount` int)
;

INSERT INTO table2
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table3
    (`date` varchar(10), `amount` int)
;

INSERT INTO table3
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table4
    (`date` varchar(10), `amount` int)
;

INSERT INTO table4
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

SELECT
CONCAT(YEAR(`date`),'-',MONTH(`date`)) AS yearmonth,
SUM(IF(groupby = 'table1' , `amount`,0)) AS 'amount_table1'
,SUM(IF(groupby = 'table2' , `amount`,0)) AS 'amount_table2'
,SUM(IF(groupby = 'table3' , `amount`,0)) AS 'amount_table3'
,SUM(IF(groupby = 'table4' , `amount`,0)) AS 'amount_table4'
FROM
(SELECT `date`, `amount`,'table1'  AS groupby FROM table1
UNION ALL
SELECT `date`, `amount`,'table2' AS groupby FROM table2
UNION ALL
SELECT `date`, `amount`,'table3' AS groupby FROM table3
UNION ALL
SELECT `date`, `amount`,'table4' AS groupby FROM table4) t1
GROUP BY CONCAT(YEAR(`date`),'-',MONTH(`date`))

yearmonth | amount_table1 | amount_table2 | amount_table3 | amount_table4
:-------- | ------------: | ------------: | ------------: | ------------:
2018-2    |            10 |            10 |            10 |            10
2018-6    |            18 |            18 |            18 |            18
2018-7    |            15 |            15 |            15 |            15

db<>fiddle here


Does the order of columns has a significant effect on INSERT?

No, you don't need to gave the columns in the same order. Not least, table order may not reflect actual on-disk order (this is 100% true for SQL Server, and I'm sure MySQL is the same)

Unless your OCD itch needs scratched


Migrate unknown Oracle database user's encrypted password - 11g hash to 12c database

This isn't really a database administration question. To "migrate" a hashed password to a different hash algorithm you will need to "unhash" it first, that is, find the original password from its salted SHA1 hash, which is not practical.

You'll need to reset that user's password.


Where can I read about the behavior of the postgress casting operators?

This is in the PostgreSQL manual section on the boolean data type:

Boolean constants can be represented in SQL queries by the SQL key words TRUE, FALSE, and NULL.
The datatype input function for type boolean accepts these string representations for the “true” state:
true
yes
on
1

and these representations for the “false” state:
false
no
off
0

Unique prefixes of these strings are also accepted, for example t or n. Leading or trailing whitespace is ignored, and case does not matter.
The datatype output function for type boolean always emits either t or f […]


Oracle : How to grant to a ROLE the ability to affect a ROLE to a USER?

You must fist grant the role (test_role) to the user (admin_role) with admin option, like this:

grant TEST_ROLE to ADMIN_ROLE with admin option;

I suggest that you name your users in a different way. admin_role is a user in your case, it would be better to name it admin_user.


The CASE you wrote is for visualization (SELECT) or for filter data (WHERE).

if is the SELECT case, then can be (not sure if you want an SUM(Sales):

SELECT
  (CASE WHEN Parameter = 'RegionWest' THEN 'A,C'
        WHEN Parameter = 'RegionEast' THEN 'B,D'
  ELSE Country) AS CountryName, Sales
FROM yourTable

If is the WHERE case I need more of your SQL to understand your question.


Writing a simple SELECT Stored Procedure in Oracle PL/SQL

You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter.

Quick procedure to test with:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_passenger_details FOR
SELECT 'test test' as Full_Name, 10 as Age, 'alien' as Nationality, 'foo' as Category,
'Name' as Airline_Name, 'wobble' as Class_Type
FROM dual;
END passenger_details;
/

Test in SQL*Plus:

SQL> variable mycursor refcursor;
SQL> exec passenger_details ( :mycursor );

PL/SQL procedure successfully completed.

SQL> print mycursor;

FULL_NAME    AGE NATIO CAT AIRL CLASS_
--------- ---------- ----- --- ---- ------
test test     10 alien foo Name wobble

SQL>


Patroni : How to handle a replica which has been disconnected from primary for long time?

I would assume you are monitoring your DB cluster health, so a missing replica would pop up very soon.  Also, it is a must to monitor disk space (running out of it might bring you into a situation that is not very easy to solve), so that would also catch this (later than sooner, usually).

Once you discover you have a replica that fell back, you have to investigate why it did so, and fix it - or remove the host from Patroni altogether.  If under disk space pressure, remove the replication slot to free up WAL space.  In a cloud setup, often simply terminating the host will solve all this by bringing up a new host.  In any case, once you have a functioning host, you might have to reinit the Patroni node.

On the other hand, I'm afraid currently there is no mechanism for fencing off replicas that doesn't appear to come back (be it any actual implementation from removing the replication slot to anything more complex than that).


Why would suser_name() not reflect an AD account name change?

Could this be related to caching with Kerberos?  (just a guess though might be unrelated)
http://blogs.technet.com/b/tspring/archive/2014/06/23/viewing-and-purging-cached-kerberos-tickets.aspx


Same function in SELECT and WHERE clause

Let's create a function that has a side effect so that we can see how many times it is executed:

CREATE OR REPLACE FUNCTION test.this_here(val integer)
    RETURNS numeric
    LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE WARNING 'I am called with %', val;
    RETURN sqrt(val);
END;
$function$;

And then call this like you do:

SELECT this_here(i) FROM generate_series(1,10) AS t(i) WHERE this_here(i) < 2;

WARNING:  I am called with 1
WARNING:  I am called with 1
WARNING:  I am called with 2
WARNING:  I am called with 2
WARNING:  I am called with 3
WARNING:  I am called with 3
WARNING:  I am called with 4
WARNING:  I am called with 5
WARNING:  I am called with 6
WARNING:  I am called with 7
WARNING:  I am called with 8
WARNING:  I am called with 9
WARNING:  I am called with 10
    this_here
──────────────────
                1
  1.4142135623731
 1.73205080756888
(3 rows)

As you see, the function is called at least once (from the WHERE clause), and when the condition is true, once again to produce the output.

To avoid the second execution, you can do what Edgar suggests - namely wrap the query and filter the result set:

SELECT *
  FROM (SELECT this_here(i) AS val FROM generate_series(1,10) AS t(i)) x
 WHERE x.val < 2;

WARNING:  I am called with 1
... every value only once ...
WARNING:  I am called with 10

To further check how this works, one can go to pg_stat_user_functions and check calls there (given track_functions is set to 'all).

Let's try with something that has no side effect:

CREATE OR REPLACE FUNCTION test.simple(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT sqrt(val);
$function$;

SELECT simple(i) AS v
  FROM generate_series(1,10) AS t(i)
 WHERE simple(i) < 2;
-- output omitted

SELECT * FROM pg_stat_user_functions WHERE funcname = 'simple';
-- 0 rows

simple() is actually too simple so it can be inlined, therefore it does not appear in the view.  Let's make it un-inlinable:

CREATE OR REPLACE FUNCTION test.other_one(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT 1; -- to prevent inlining
SELECT sqrt(val);
$function$;

SELECT other_one(i) AS v
  FROM generate_series(1,10) AS t(i)
 WHERE other_one(i) < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    13 │      0.218 │     0.218

SELECT *
  FROM (SELECT other_one(i) AS v FROM generate_series(1,10) AS t(i)) x
 WHERE v < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    23 │      0.293 │     0.293

As it looks, the picture is the same with or without side effects.

Changing other_one() to IMMUTABLE changes the behaviour (maybe surprisingly) to the worse, as it will be called 13 times in both queries.


There is no method for deleting a read only routing URL for a given replica. Your only options are to change the URL to something else (just putting a different URL in the READ_ONLY_ROUTING_URL = N'tcp://newRoutingString' or dropping the replica entirely from the AG and putting it back in again (less than ideal I know).

If you adjust the replica to SECONDARY_ROLE(ALLOW_CONNECTIONS = NONE) then you can at least prevent any potential attempts to hit that URL.


Open Source Multi-master replication ideas?

I favor Galera clustering (see Percona's PXC or MariaDB).  Put 1 node in each of 3 different datacenters.  (One node could be a trivial 'arbitrator' if you need to cut corners.)

Galera requires the use of InnoDB, but that is reasonably efficient.

If a node goes down, or is separated from the other two nodes due to a network outage, the system hums along, with the restriction that you can't write to the dead/isolated node.  When if comes back up (or is replaced/repaired), the cluster will repair it automagically.

If no node can talk to any other node (which seems extreme), manual intervention will be required.  (Actually, if more that 50% of the nodes are communicating with each other, the "system" is still alive and usable.)

Transactions are synchronous (read: latency), but there in only one round trip per transaction.

NDB Cluster (which is perhaps what you are referring to) is a distinctly different animal.  It uses an "eventual consistency" for dealing with replication.  This is not at all related to the rest of MySQL replication options.

Key:value can be implemented easily on top of InnoDB or NDB cluster.


Missing data in specific tables post log backup and shrink

We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their ldf files were growing. We took a log backup and tried to shrink the log file. Not sure if the shrink actually ran, the wizard closed in 2-3 secs and the ldf file size didn't reduce.

Did you check that if both of the AlwaysOn servers are in a healthy state? It seems that the log backup is not happening because the principal logs were still holding up, which could be due to the following reasons:

the secondary server is down
OR; the databases are not in a healthy state
the redo logs are applying very slowly on  the secondary server

Look into the SQL error logs as well as sometimes log backup does not work because the disk is full, and it couldn't write to it.

If log usage is still high, you may remove the affected databases from the AlwaysOn availability group first. Wait till the log usage drop, and  then add back the affected databases into the availability group. This shouldn't cause to any impact.

  Please clarify if the log backup and shrink has any part to play in this? If yes, how could this have been avoided and what else was supposed to be done to keep the ldf in check?

No, the log backup nor the shrinking shouldn't cause to the lost of data.

Have you checked if...

any scheduled job to purge away some data?
application team or the DBA who purge the data?


Can anyone recommend a beginner DBA course in London

Ask your boss to get you a pluralsight subscription.

Additionally, read answers from people like Paul White, Aaron Bertrand, Remus, etc and try to answer questions on this site.

Read blogs from sqlskills.com, brentozar.com, mssqltips.com, sqlperformance.com.

Also folks from sqlskills and brentozar are giving inperson training in LD.

Sqlbits event in UK and the videos on it are extremely helpful and don't forget virtual academy from Microsoft.


Anybody using HierarchyId in production? Is it reliable?

I've implemented HierarchyID and found it to provide good performance and easy to use.

I've used it on relatively small datasets (tens of thousands of rows) with hierarchy up to 10 branches deep.

Why use it? The HierarchyID type provides a number of helper methods (such as IsDescendantOf) that make your job easier than rolling your own materialized path.

Paul Nielsen's comment over on StackOverflow is confusing to me - the HierarchyID is a materialized path. I'm more inclined to agree with this comment below his answer.

A better question might be 'why not use it'. It's easy to use, provides a lot of functionality that you'd otherwise be writing for yourself, and performs well (in my limited tests).


counting how many likes one user gives another

Two things:

Group by both user IDs
Inner join to the users' table two times, use different alias to join it with different user id

SELECT u_to.username as user_to, u_from.username as user_from, count(*) as like_count
FROM likes
INNER JOIN users u_to on likes.user_id_to=u_to.user_id
INNER JOIN users u_from on likes.user_id_from=u_from.user_id
GROUP BY u_to.username, u_from.username
ORDER BY like_count DESC


Are you able to run this command from a client that has the AWS tools configured ? (you could launch an EC2 instance with an Amazon AMI Linux image which is already configured to run this command if not)

rds-describe-db-instances --headers

This should return info to verify the port number and hostname as per here


Distributed Availability groups & Listeners

After a call with someone from Microsoft, I was told that there are no listeners on the Distributed availability level yet. So currently if we want to use DAG, you will have to change your DNS or application connection to connect to the secondary availability group.


Unable to connect to database engine using built-in administrator account

Even though you are logged in as an administrator to the server were sql is running and installed, that login must still be a valid login in Sql Server with appropriate permission, is this the case?


How to transfer files at remote location using sql server job?

that's probably because the user trying to move the files is the user that is running the job (probably the user that SQL Server agent is configured to run). Try giving read permission to this user on the folder you are reading the files from and write on the folder you are writing the files.


postgres partition data record assignment

This is possible by using the system column tableoid:

select *, tableoid::regclass as partition_name
from the_table;

Online example


Why is my UTF-8 document raising UTF-8 encoding errors in Azure Data Lake Analytics?

Notes

There are several things to go over here:

Just to get this out of the way: gz[ip] and 7zip.exe have nothing to do with any of this. Compression does not alter encoding or any of the original bytes (else compression / decompression wouldn't be reliable). Well, theoretically there can be a bug in one of them such that the decompressed output is somehow slightly different, but that would be a wide-spread problem, I would think, and these algorithms, and these two tools in particular, have been around for a long time and are known to be reliable.
Keep in mind: files, even text files, contain bytes, not characters. Those bytes can represent some set of characters, or another set, or something else. But in the end, it is just a collection of bytes. So if you see Ã, that is not because there is an Ã in the file, it is due to there by one or more bytes that are currently being interpreted as representing an Ã. It could be that those same bytes should truly be interpreted as representing something else. And, at the same time, it could also be that some other sequence of bytes, under a different interpretation, also represent Ã.

  Then there is a U-SQL job setup to simply copy it from one folder to another folder.

Ok, this right here should raise a red flag: why would "simply copying a file" produce an encoding error? There can only be an encoding error if the file is being read, else it's just a collection of bytes moving from point A to point B (or perhaps it's merely being relinked and not moving, but either way).

  This process fails and raises a UTF-8 encoding error for a value: Ã©e

Here is another key indicator (that is likely being misinterpreted by some): the error is a UTF-8 encoding error. This tells us two things:

The file is being interpreted as UTF-8 already, so the byte sequences are not being interpreted as some other encoding (e.g. Windows-1252, or ISO-8859-1). This means that the Ã© characters are UTF-8 encoded bytes already (i.e. 0xC383C2A9), and not Windows-1252 bytes (0xC3A9) that should instead be interpreted as UTF-8 to produce é.
The fact that it's an error means that Ã©e is not the error, because if it was the error, then it wouldn't be able to display it. An encoding error is when a byte sequence can't produce a character in that encoding. Meaning: there is something missing from the Ã©e sequence. The Ã©e sequence should be the remaining parts of what could be decoded from the bytes in the file.
Keep in mind that Unicode is a specification, not software. The implementation is left up to each vendor. There are guidelines regarding compliant implementations, and those include how to handle decoding errors. But, there is some flexibility as well. So some implementations might throw a hard-error in this case, or others might display the "replacement" character, �, or some might display nothing in that position.
In either case, the Ã©e sequence, taken literally, is most likely a red-herring and not what we should be looking at.

  In Notepad++, it shows the document as UTF-8. I save the document as UTF-8 again and upload it back to the store. I run the process again and the process succeeds with that value as UTF-8

Ok. So, in Notepad++, when it shows "UTF-8" on the right side of the bottom bar upon opening a file, that is not necessarily a guarantee that the file is, in fact, encoded as UTF-8. That is a best-guess, probably based on common byte sequences. If the encoding indicator instead showed "UTF-8-BOM", then that would be a guarantee that the file was encoded as UTF-8. The "-BOM" indicates that a Byte Order Mark is present. a BOM is an optional 2 - 4 byte sequence at the beginning of a file that should be hidden but indicates the encoding of the file. This is only available for Unicode. There are two 2-byte BOMs for UTF-16, one for Little Endian and one for Big Endian. There are also two 4-byte BOMs for UTF-32, one for each Endianness. UTF-8 only has a single 3-byte BOM since Endianness does not apply to UTF-8 (since the code units are a single byte, there is only one order that the byte can be in). Of course, having a BOM does not guarantee that it will be respected or even understood by whatever is reading the file.
In the case presented here, having a BOM should not have any affect on the outcome since the file is already being read as a UTF-8 file.
And again, if Notepad++ is showing Ã©e and shows "UTF-8" in the lower-right corner, then it's already UTF-8 instead of Windows-1252 / ISO-8859-1 that needs to be told to be read as UTF-8 in order to produce é.
If Notepad++ thought that the file was UTF-8, then saving the file as UTF-8 shouldn't have really changed anything. Something else must have changed.

  The file is CSV, comma delimited with double quotes on strings.

and:

  It's a URL mangled among 1 million other records.

So, is Ã©e the entire value for that field? Is it within double-quotes in the file? Ã©e does not appear to be a URL ;-)

Also, does the value shown in the error match, exactly, the value you see in the file? If so, there is likely a hidden character in the file that is causing this problem.

  Looks like there are ASCII characters coming in even though it's a UTF-8 document.

What do you mean by "ASCII" characters? One of the features of UTF-8 is that the encoding of the first 128 code points (U+0000 through U+007F) are identical to standard ASCII. This was the primary design goal of UTF-8: full ACSII compatibility (not 8-bit Extended ASCII, just 7-bit standard ASCII). So, if the majority of the document is using US English characters and punctuation, then yes, you should expect to see that just the same whether you open the file as ASCII or UTF-8.

  When I convert it to ANSI and use the ASCII extractor the file succeeds.

This is not surprising. I don't think it's possible to get an ANSI / ASCII encoding error. There are only a few code points / values that are undefined in code page Windows-1252, and they generally behave as "hidden" characters.

Of course, if you do have valid UTF-8 encoded characters in that file, converting to ANSI will change them into "?" if they are not available in Windows-1252.

Take-aways

If this were an ANSI / Windows-1252 / ISO-8859-1 encoded file such that Ã© indicated a byte sequence of 0xC3A9, then there would be no UTF-8 encoding error since the byte sequence of 0xC3A9 is valid UTF-8 for é.
Since the error itself cannot be shown (else it wouldn't be an encoding error):

Ã©e isn't the error, but instead a result of the error
We need to see the exact bytes of the row that has the encoding error in order to see what could not be decoded.

Nothing says that a file needs to be imported as UTF-8. If a file (even if not this one) is encoded as Windows-1252, ISO-8859-1, or anything else, and the data is all correct in the file, then rather than trying to force the file to be UTF-8, it is better to tell whatever process / tool is reading the file what the actual file encoding is. There is no reason to not import a Windows-1252 encoded file as Windows-1252.

Bottom-line is: there is too much guessing / wild-goose-chase without seeing the original bytes of the line that has the encoding error. Having that line will help determine both a) what happened, and b) how to proceed.

If the poster of the bounty could add a comment to this answer providing additional details, especially the exact byte sequence that is getting the error, that would help solve this.


Added New Subscriber but Replication Not working

Adding comment answer as answer

The solution was to remove one of the duplicate records from the MSmerge_genhistory table and then re-run the snapshot agent to generate a new snapshot.

This is because the snapshot is a series of flat files with the exported data. Removing the duplicate entry from the DB doesn't remove it from the snapshot files, only regenerating the snapshot will do this.

Another possible option is cleaning up the merge metadata first then generating the snapshot.


Attaching / creating database on network drive - Access is denied

Just to state the full limitations, this is SQL 2008 R2 and above, and it's best to use fully qualified names for the shares rather than mapped drives.

Are both computers on a domain? The SQL service account (or if it's NETWORK SERVICE then the computer account instead like DOMAIN\COMPUTER$) needs a few things:

Full control on the share.
Full control on the NTFS folder on the other side of the share.
If you're attaching files, your own account needs the same permissions.

That last one is a little doozy. As you mentioned you were running SSMS as Administrator (which is one issue sometimes). Otherwise you may be having double-hop issues; which you could eliminate by running it from the source machine over RDP (if you aren't already).

I feel like I'm not telling you anything you don't know already. When things get sticky there are two tools:

xp_fileexist and xp_cmdshell (to check SQL's access to various parts, like can it ping the network address, can it see the share, can it echo something to a file there, etc).
When you're out of all options, run the sysinternals utility Process Monitor on the share destination (fine for Windows, not an option when it's NetApp or something else). You can add a filter on access denied, these pop up when you try to access the share, and you can dig into the account details being used for the access and getting denied.

Hope that helps a little.


This index does it :

CREATE INDEX my_idx on my_table USING GIN (my_jsonb_column);


I assume you mean Power BI Report Server.
It's useful if you want to test or develop using PBIRS without risking your SSRS installation. As far as i can tell PBIRS can do everything SSRS can and more. You can still deploy SSRS reports to PBIRS. In certain scenarios you may want to have 2 instances of a reports server. The argument would be the same for having 2 instances of SSRS. Separation, security, scalabiliy.
The downside is competition for resources and management/maintenance overhead. I think you really need to have a strong argument for maintaining both. My reccomendation is: Test it, if it does everything you need then replace SSRS with PBIRS.
If you're insterested in how to do that I discuss the steps here: https://dba.stackexchange.com/a/219134/41928


delete old 'Monitoring Agent' metrics and 'Automation Agent' Debug logs via scheduler

>use mmsdbagentlog
switched to db mmsdbagentlog
> show collections
app.agentLogs
> db.app.agentLogs.stats()
{
    "ns" : "mmsdbagentlog.app.agentLogs",
    "count" : 1711298,
   "size" : 524287948,
    "avgObjSize" : 306,
    "storageSize" : 71323648,
    "capped" : true,
    "max" : -1,
    "maxSize" : 524288000,
    ... }

Looking in OpsManager Database , they use capped size collection to store the agent log , it seems we don't need to worry about the volume.
If the collection confuse you is not capped size , you could try to schedule a job with crontab in Linux(just an example) to remove those records :

db.collection.remove({_id : {$lt : ObjectId('55b0d1c40000000000000000')}})

first 8 character is hexadecimal timestamp can be generate in Linux Shell with command :

Today=`date +%F`
T=`date --date="$Today 00:00:00" +%s`
Stamp=`printf "%X" $T`''0000000000000000
echo $Stamp


Azure SQL Server Database Error: 18456, Severity: 14, State: 1

Azure is having a pretty major outage right now!

  Microsoft has warned that a "subset of customers in South Central US"
  may experience Azure problems today after cooling issues sent the
  servers scurrying for the shutdown button.

  The warning was first raised by Microsoft at 09:29 UTC as pretty much
  everything in the South Central US region went offline thanks to a
  temperature spike that caused servers to automatically shut down to
  avoid damage.

I'd suggest following @AzureSupport to stay updated.

  Unfortunately, the Azure support website is down so I can't investigate further.

Is a pretty good clue as to what's going on.


construct a sql , assign it to a variable and execute inside shell script

You need to do it like this:

query="select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB'"
mysql -uroot -p -e"${query}"

or

query="select count(*) from information_schema.tables"
query="${query} where TABLE_SCHEMA not in"
query="${query} ('mysql','performance_schema','information_schema')"
query="${query} and engine!='INNODB'"
mysql -uroot -p -e"${query}"

Double quotes do not need to be part of the query variable


Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed

Something is keeping autovacuum from removing those dead tuples.

These can be only three things that keep VACUUM from doing its job:

A long running concurrent transaction. Check with

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

An abandoned replication slot. Check with

SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;

An orphaned prepared transaction. Check with

SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

But all that would also block VACUUM (FULL) from shrinking the table.  So there must be something extra you are doing when you run VACUUM (FULL).

You have autovacuum enabled, right?

This is what you should do:

Remove whatever is blocking VACUUM.
Make sure autovacuum runs fast enough on that table:

ALTER TABLE status SET (autovacuum_vacuum_cost_delay = 0);

If the data modifications are caused by UPDATE you could also try to get HOT updates. These remove the need for VACUUM. A prerequisite is that the updated column or columns are not indexed.


Neither. The right answer is to use the GIS Spatial Type for a point. All of those databases have their own implementation.

PostgreSQL's PostGIS is easily the most full featured GIS implementation,

CREATE TABLE foo(
  gid  int GENERATED BY DEFAULT AS IDENTITY,
  geog geography(POINT)
);

I would highly suggest going PostgreSQL, and checking out the book PostGIS in Action if you're new to this.
See also,

My comparison of MySQL and PostgreSQL's PostGIS for spatial
MySQL Spatial Data Types (which suck btw),
SQLite Spatial Extension SpatiaLite


Help to find bottleneck on slow view queries

One of the things I would recommend you look into would be to update your statistics as from the plans you have pasted they look like they could be out of date.

For example, if you look at what is happening with the index ExclusionProjectConfigurationIdx2 on table [fundhiveAvivaStaging].[dbo].[ExclusionProjectConfiguration]. The operation is a seek which is great, but when you look at what is happening you can see that the Estimated Number of Rows is 1.17062 but the Actual Number of Rows is 34607. The seek on this index counts for 20% of the total execution time.

That isn't the only index that looks like the statistics are out of date, if you look near the bottom of the plan the [LanguageCountrySC] index is used more than once and is 17% of total execution time per use. Again, the stats on this need to be updated.

These two indexes alone account for 54% of your total execution time.

I would start by running DBCC SHOW_STATISTICS on these indexes and compare the output with the number of rows that are actually contained within the table - if there is a huge variance then I recommend you run UPDATE STATISTICS on the offending indexes.

I fear that this may not be the entire solution but it is definitely something that you should look into.


Does the PCI standard forbid the use of Windows Authentication?

No.
As you suggested, the PCI Data Security Standard actually prefers Windows Authentication over any other means of authenticating to SQL Server.
The section of the standard that covers this is Requirement 8: Assign a unique ID to each person with computer access.  SQL Server authentication (which Mixed Mode authentication allows) fails, or makes it extremely difficult to satisfy, the following PCI requirements:

8.5.5 Remove/disable inactive user accounts at least every 90 days.
8.5.8 Do not use group, shared, or  generic accounts and passwords, or  other authentication methods.
8.5.12 Do not allow an individual to submit a new password that is the same as any of the last four passwords he or she has used

As an administrator, the main problem I have with allowing SQL Server authentication is that an application connecting to your database with it will likely pull usernames and passwords in plain text from a configuration file.  Anybody with read access to that configuration file now also has access to your database.
If you're in a Windows shop with a strong Active Directory setup, using only Windows Authentication to connect to your production databases confers many advantages:

Security and identity are enforced at the domain level, making it easy to confer and revoke rights across the domain.

Every person and service gets a separate domain account; service accounts can't remote into machines, and non-DBA person accounts can't connect to the databases.
Your DBA team can no longer share a DBA SQL Server login with God rights on every instance in your environment.

While SQL Server does allow you to enforce password complexity rules (which you have to enforce per PCI DSS requirements 8.5.9-11), I bet AD does this better.  Also, do you really want to enforce these rules in two different places?

Applications connecting using Windows Authentication can no longer expose their credentials in plain text.  It is much harder for someone to pop open a config file or application server and get access to your database.


Can stable (or immutable) functions call volatile functions?

Reading the documentation is in order. That comes from someone who just takes interest and never used Postgres and found the answer in the first google link:

http://www.postgresql.org/docs/current/static/xfunc-volatility.html

You may want to put particular emphasis on that part of stable:

  This category allows the optimizer to optimize multiple calls of the
  function to a single call.

So, the problem is - you want the calls to the method logged. But the optimizations of a stable function will lead to the function maybe not executed every time (because the return value for the same parameters is known, so it is reused), so the log will not be complete.

THAT BEING SAID:

As you only want to log exceptions, it may be ok - this also will keep your log smaller as you get only one instance in a query, even when the funciton WOULD be called 100 million times, as long as the optimizer optimizes it out. So, in your case - of exception logging - it may actually be ok. You will have to try out whether postgres developers are putting in protection agaisnt database manipulations, but my bet is no, and I would assume you get away with it.


I work mainly with Sql Server, but I'm not aware of any DBMS that can track changes on a 'view'.

I would suggest that a standard view is only a logical abstraction of physical tables and does not provide any actual data storage to change. Any changes done though the view would be applied to the actual physical tables. As I understand it, CDC uses the transaction log to process the changes, I think your answer is no. If you need to track the data changes, you would need to track the underlying tables.


DB2: Cannot use RUNSTATS/REORG in trigger definitions?

You can only use SQL statements in a trigger. RUNSTATS and REORG are not SQL statements; they are DB2 CLP commands, and only the command line processor understands them. You can try using the system stored procedure ADMIN_CMD() to invoke those utilities, however, 1) it still may not work because of the nature of the utilities and 2) even it does work it would not be a good idea.

There are other problems with your trigger. You cannot use FETCH FIRST in a delete statement, for example. And what is the point of updating statistics just before a REORG?


Select not repeated results from same tables in different databases

SELECT ... FROM db1.user
UNION DISTINCT
SELECT ... FROM db2.user
UNION DISTINCT
SELECT ... FROM db3.user
;

The DISTINCT will de-dup based on what you have in the select: ...


Choosing database for large set sum and difference

In maths terminology the summed set calculated at the server is called the union. The comparison between the server's set and each client's is called the difference or relative complement.
It is simple enough to write these so any combination of programming language and persistence layer would suffice. However, relational databases are build on set theory and have built-in functionality for precisely these operations, so it makes sense to use one. I have a copy of SQL Server to hand so I'll use it's syntax. Postgres will support this schema and algorithm. MySQL has work-arounds.
Define the upload table thus
create table dbo.Upload
(
  ClientNumber  int          not NULL,  -- change the type to match your needs
  StringValue   varchar(99)  not NULL,  -- whatever type and length is appropriate
);

I'm treating this as a staging table that will be emptied at the end of each day. If it must retain history add the appropriate key columns. I will define a table to hold the server-calculated superset, though this could be a view or function or equivalent.
create table dbo.Superset
(
  StringValue  varchar(99)  not NULL
);

And a list of all clients so I can process ones that send no rows.
create table dbo.Client
(
  ClientNumber  int  not NULL
);
insert dbo.Client(ClientNumber) values (1), (2), (3), (4);

The end-of-day process runs and uploads the clients' data into the staging table
insert dbo.Upload(ClientNumber, StringValue)
values (1, '1'), (1, '2'), (1, '3');

insert dbo.Upload(ClientNumber, StringValue)
values (2, '1'), (2, '2');

insert dbo.Upload(ClientNumber, StringValue)
values (3, '1'), (3, '3');

-- Client 4 sends no rows.

Since I put all the clients' rows in a single table there is no need to union them. We just need to retrieve the unique values
insert dbo.Superset(StringValue)
select distinct
  StringValue
from dbo.Upload;

select * from dbo.Superset;  -- just to check

StringValue
-----------
1
2
3

In SQL the difference operation is performed using the EXCEPT keyword.
select
  c.ClientNumber,
  v.StringValue
from dbo.Client as c  -- read from the reference table so even clients which sent no values are reported.
outer apply           -- executes the sub-query once per outer row i.e. per Client
(
  select              -- read the superset
    ss.StringValue
  from dbo.Superset as ss

  except              -- remove those rows..

  select
    StringValue
  from dbo.Upload as u
  where u.ClientNumber = c.ClientNumber  -- .. which were sent by the current row in the outer query (Client).
) as v
order by
  ClientNumber,
  StringValue;

ClientNumber StringValue
------------ -----------
1            NULL         -- NULL since client 1 sent everyting

2            3

3            2

4            1            -- Client4 was absent from Upload but is still reported
4            2
4            3

If the correlated sub-query is too tricky executing the sub-query in a cursor would do the same job.


can I create an SSRS group without adding the group to the box itself and without using Active Directory?

There are a couple of courses of action for you:

If you're friendly with your Windows Admin, I'd thoughtfully think out some AD Groups and then present them to the admin and ask them to be created (in whatever workflow you have).
From there you can assign permissions on the individual folders by these groups. Click on the folder, then click on Properties.

On the left you will see "Security," click that. Then click "Edit
Item Security." You may get a dialog that tells you that the item
currently inherits from parent. You may need to wait for a bit before
this completes, but be patient.

If successful you will have checkboxes next to the principles already
with access. From here choose "New Role Assignment"

Now you can add your AD Group in the next page and assign it the permissions necessary. Try with DOMAIN\GroupName

Obviously this will be much easier if you have AD groups, but if you can't get that, then you can do this with individual domain users. That would be a management nightmare?