How to tell if a backup log chain is broken?

Reference Reading / Similar Q&As

You might want to check out my answer that I posted in response to the question:  Will VSS backups break logchain? (dba.stackexchange.com)

The explanation in my answer also links to the question How can I backup an SQL Server database using Windows Server Backup? (serverfault.com) which was also answered by myself.

Transaction Log Chain

When a Transaction Log (TLOG) backup is performed, the backup information is stored in the msdb database in various tables. The information stored will contain information like backup_type, logical_device_name, physical_device_name, is_copy_only, is_snapshot, and various ..._lsn columns (lsn = log sequence number).

You can retrieve the transaction log backup chain information from your SQL Server instance via the msdb database with the following script:

/* ==================================================================
 Author......:  hot2use
 Date........:  25.04.2018
 Version.....:  0.1
 Server......:  localhost (first created for)
 Database....:  msdb
 Owner.......:  -
 Table.......:  various
 Type........:  Script
 Name........:  ADMIN_Retrieve_Backup_History_Information.sql
 Description.:  Retrieve backup history information from msdb database
 ............
 ............
 ............
 History.....:   0.1    h2u First created
 ............
 ............
================================================================== */
SELECT /* Columns for retrieving information */
       -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
       msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_start_date,
       msdb.dbo.backupset.backup_finish_date,
       -- msdb.dbo.backupset.expiration_date,

       CASE msdb.dbo.backupset.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Diff'
            WHEN 'L' THEN 'Log'
       END  AS backup_type,
       -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
       msdb.dbo.backupmediafamily.logical_device_name,
       msdb.dbo.backupmediafamily.physical_device_name,
       -- msdb.dbo.backupset.name AS backupset_name,
       -- msdb.dbo.backupset.description,
       msdb.dbo.backupset.is_copy_only,
       msdb.dbo.backupset.is_snapshot,
       msdb.dbo.backupset.checkpoint_lsn,
       msdb.dbo.backupset.database_backup_lsn,
       msdb.dbo.backupset.differential_base_lsn,
       msdb.dbo.backupset.first_lsn,
       msdb.dbo.backupset.fork_point_lsn,
       msdb.dbo.backupset.last_lsn
FROM   msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
            ON  msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

        /* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs
        -------------------------------------------------------------------------------*/
WHERE  1 = 1

       /* ----------------------------------------------------------------------------
       WHERE statement to find Device Backups with '{' and date n days back
       ------------------------------------------------------------------------------- */
       -- AND     physical_device_name LIKE '{%'

       /* -------------------------------------------------------------------------------
       WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b
       ---------------------------------------------------------------------------------- */
       -- AND     physical_device_name  LIKE '[fF]:%'                          -- STANDARD F: Backup Directory
       -- AND     physical_device_name  NOT LIKE '[nN]:%'                      -- STANDARD N: Backup Directory

       -- AND     physical_device_name  NOT LIKE '{%'                          -- Outstanding Analysis
       -- AND     physical_device_name  NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
       -- AND     backupset_name NOT LIKE '%Galaxy%'                           -- CommVault Sympana Backup

       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b
       ----------------------------------------------------------------------------------
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  -- 7 days old or younger
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE())  -- n days old or older

       */

       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for (a) given database(s)
       ---------------------------------------------------------------------------------- */
       AND database_name IN ('AdventureWorks2012') -- database names
       -- AND     database_name IN ('rtc')  -- database names

        /* -------------------------------------------------------------------------------
        ORDER Clause for other statements
        ---------------------------------------------------------------------------------- */
        --ORDER BY        msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause

        ---WHERE msdb..backupset.type = 'I' OR  msdb..backupset.type = 'D'
ORDER BY
       --2,

       2       DESC,
       3       DESC

Caution: The where clause currently select the AdventureWorks2012 database

Broken Transaction Log Chain

The (transaction) log chain is never broken unless one of the following conditions is met:

a Transaction Log backup file was deleted
a Transaction Log backup file is not accessible (somewhere in a backup device; 3rd-party backup solution)
the database is in SIMPLE recovery model
a Transaction Log backup was performed with the option TRUNCATE_ONLY
a FULL Database backup was taken without the COPY_ONLY option and was then deleted from disk because the developers only needed a quick backup to analyse a situation in the database and your FULL backup before that was deleted by (a) backup procedure.

Your Situation

In the screenshot you provided you have a FULL backup of the database that is is_copy_only and shortly after a FULL backup that is not is_copy_only. Now what you don't know:

Is the second FULL, non-is_copy_only backup a snapshot or not?

If you use my script from above and change the WHERE clause to match your database name, you might find out that that FULL backup that is not is_copy_only might just be a is_snapshot.

And that might just open up a new question:

Will the FULL, is_snapshot database backup of my database break the log backup chain?

But...

....whichever way this goes, as long as you have an unbroken chain of FULL and TLOG backups you can access, you can still restore your database to any point in time, even if you have another FULL backup in-between.

You can verify this with the output of my script for your database, by looking at the first_lsn and last_lsn numbers. They should match up, even when bypassing a FULL backup.

Better Be Safe Than Sorry

I have an AdminDB2 database on one of my instances. I created a TLOG backup, modified data, performed a FULL backup, modified data, performed a TLOG backup, ....

Lets have a look at my backup history of my AdminDB2:

dbname    backup_start_date       backup_finish_date            type    Log   physical_device_name                                          C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-25 17:29:08.000 2018-04-25 17:29:08.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172908.trn   0   0   36000002022400042   36000002022400042   NULL    36000002021900001   NULL    36000002025100001
AdminDB2    2018-04-25 17:28:48.000 2018-04-25 17:28:48.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172848.bak   0   0   36000002022400042   36000002018900037   NULL    36000002022400042   NULL    36000002024200001
AdminDB2    2018-04-25 17:28:23.000 2018-04-25 17:28:23.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172823.trn   0   0   36000002018900037   36000002018900037   NULL    36000002021500001   NULL    36000002021900001
AdminDB2    2018-04-25 17:28:07.000 2018-04-25 17:28:07.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172807.trn   0   0   36000002018900037   36000002018900037   NULL    36000002018400001   NULL    36000002021500001
AdminDB2    2018-04-25 17:27:32.000 2018-04-25 17:27:32.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak   0   0   36000002018900037   36000001990800037   NULL    36000002018900037   NULL    36000002020600001
AdminDB2    2018-04-25 17:15:00.000 2018-04-25 17:15:00.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_171500.trn   0   0   36000002016000003   36000001990800037   NULL    36000002018100001   NULL    36000002018400001

The order is date descending

You can see the last TLOG backup at the top, the previous FULL (in-between) backup at 2018-04-25 17:28:48.000, the previous TLOG backup at 2018-04-25 17:28:23.000,  and so on.

To restore the AdminDB2 database to the current point-in-time I would have to use the first FULL backup from 2018-04-25 17:27:32.000 (because I deleted the in-between FULL backup) together with all the TLOG backups.

Let's give that a try.

Delete the FULL backup file AdminDB2_FULL_20180425_172848.bak on my disk (or rename it), just because it is the one in-between.
Open up the Restore GUI in SSMS and add ..

the FULL backup AdminDB2_FULL_20180425_172732.bak
all the TLOG backup files

AdminDB2_TLOG_20180425_172807.trn
AdminDB2_TLOG_20180425_172823.trn
AdminDB2_TLOG_20180425_172908.trn

Make sure i set the option Overwrite the existing database (WITH REPLACE)
Perform the restore (or script the statement out into a query window)

Script

USE [master]
RESTORE DATABASE [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5, REPLACE
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172807.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172823.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172908.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

Output

15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
75 percent processed.
90 percent processed.
100 percent processed.
Processed 848 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE DATABASE successfully processed 850 pages in 0.134 seconds (49.502 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.027 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.005 seconds (0.390 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.125 MB/sec).

...and the database is back ONLINE.

Summary

The backup chain only breaks when you lose the TLOG backups in-between, other than that you can restore a database from a FULL backup a long time ago and just add all the TLOG backups.

However...

...it is faster to have a more recent FULL, DIFF and TLOG backups handy.

Additional information in response to comment: Transaction Log backup was performed with the option TRUNCATE_ONLY - when this happens, is there any way to know this by T-SQL query

Backing Up Transaction Log With Truncate_only

In previous versions of SQL Server prior to SQL Server 2008 you could use the following statement:

 BACKUP LOG [AdminDB2] WITH TRUNCATE_ONLY

This has been deprecated and is no longer supported. You will receive an error message like the following:

Msg 155, Level 15, State 1, Line 10
'TRUNCATE_ONLY' is not a recognized BACKUP option.

The new method is to backup to disk NUL and is performed with the following command:

BACKUP LOG [AdminDB2] TO DISK='NUL'

This will return the following information:

Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.001 seconds (1.464 MB/sec).

  Advisory
  Do NOT use this in production. You will lose the ability to restore to a point-in-time during that TLOG backup.

Reference: BACKUP (Transact-SQL) (Microsoft Docs)

Your backup history will show this as:

dbname      backup_start_date       backup_finish_date      type ldev  pdev C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-26 09:35:05.000 2018-04-26 09:35:05.000 Log NULL    NUL 0   0   36000002030100002   36000002022400042   NULL    36000002033400001   NULL    36000002033700001

The information for the logical_device_name (ldev) and physical_device_name (pdev) will both contain the value NULL. This is a sign that a BACKUP LOG ... was performed with a TRUNCATE_ONLY (new: TO DISK='NUL'). You will have lost the ability to restore past this point using Transaction Log backups.

Additional information in response to comment: Yes - this was a is_snapshot = 1 [backup]

is_snapshot

Please read the section is_snapshot in my answer to the question Use of third-party VSS backup plus native SQL backup

From my original answer:

  If the database backup history has the flag is_snapshot set to 1 then you know that this backup was performed using a 3rd-party software that triggered the SQL Server Writer (VSS Service for SQL Server) which allowed the 3rd-party software to backup the database almost instantaneously.

  From the official documentation on what Snapshot Backups are:

    SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

  Reference: Snapshot Backups (Microsoft Technet)

  A backup created using this feature can also be restored almost instantaneously.

  Summary

  The 3rd-party backups should be marked as is_snapshot = 1 and is_copy_only = 1. These backups will not conflict with additional backup steps/procedures performed using native SQL Server BACKUP DATABASE..., BACKUP DATABASE ... WITH DIFFERENTIAL.... and BACKUP LOG... statements. The 3rd-party database backups are not part of an existing backup set.

I hope this information is sufficient.


Create a table with all the columns of inner join of two other tables without copying data

CREATE TABLE your_table AS your_select WITH NO DATA;

https://www.postgresql.org/docs/current/static/sql-createtableas.html


Query other database's INFORMATION_SCHEMA tables

I think this does what you need:

CREATE
    DEFINER = root
    PROCEDURE ...
    SECURITY INVOKER   -- This limits what the proc can do to what the user is GRANTed
    BEGIN ... END

See http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html


Is this the right ? from my side, the results are the same...
MERGE dbo.Transactions AS T
        USING (SELECT s.transactionID,s.transactionDate,s.productID,s.orderID,s.qty,s.unitPrice FROM dbo.Staging as s) AS s
        ON (t.transactionID = s.transactionID)
        WHEN MATCHED THEN
        UPDATE  SET
                transactionDate = s.transactionDate,
                productId = s.productId,
                ReferenceOrderID=s.orderId,
                qty = s.qty,
                ActualCost = s.unitPrice

        WHEN NOT MATCHED THEN
        INSERT (transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
        VALUES (s.transactionID, s.transactionDate,s.productId,s.orderId,s.qty,s.unitPrice);


SQL Server Express 2012 Database Size Limit

SQL Server Express only imposes file size limits on data files, log files can grow to any size. The Size shown on the database properties window is the data and log file combined sizes.

Somewhat confusingly though, the Free Space value shown on this window only relates to the data file(s). If you'd like to see this in more detail, I'd recommend querying the system tables rather than relying on the SSMS GUI (which isn't always going to give you the information you want/need). The following script will show some more detailed information about data file sizes/growth settings:

SELECT
    [TYPE] = DF.TYPE_DESC
    ,[FILE_Name] = DF.name
    --,[FILEGROUP_NAME] = FG.name
    ,[File_Location] = DF.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)/(DF.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
        + CASE is_percent_growth WHEN 1 THEN ' [WARNING: Autogrowth by percent]' ELSE '' END
FROM sys.database_files DF
LEFT JOIN sys.filegroups FG
ON DF.data_space_id = FG.data_space_id
order by DF.TYPE desc, DF.NAME;

As an aside: Since your data file is at 10GB already, future growth operations will fail (and whatever transaction triggered the growth will generate a Primary Filegroup Full error). Since the file is set to 10GB exactly, I suspect that it has been grown out to this amount deliberately to take advantage of the full amount of space offered by SQL Express.


Can you create a new filegroup for a table in a database that is live?

You can definitely add a filegroup to a database while it's running, but it will have no data until you create a table in it, or rebuild a table onto the new filegroup.

This would mean dropping the clustered key on the table, and re-creating it on the filegroup:

CREATE CLUSTERED INDEX IK_Example ON Example ( id ) ON NewFileGroup;

OR

ALTER TABLE Example ADD CONSTRAINT PK_Example PRIMARY KEY CLUSTERED (id) ON NewFileGroup;

The Microsoft Docs on this one is pretty good.


Backing up large MySQL DB for DR purposes

xtrabackup from a slave will work fine.

If you apply redo log on a linux machine(innobackupex --apply-log) you can use the restored datadir on windows.


A #temporary table is only visible from the session that created it.  So even the same user in another session would not be able to see it.  If you load a temporary table in SQLCMD it would be visible to that instance of SQLCMD.  So a subsequent command could load the data into a permanent table, or open a cursor over it and call a stored procedure for each row, etc.

Alternatively you could provision a private schema for the user and load the data into a permanent table there.  Then no other user (other than a database owner or other privileged user) would be able to read it.  The user could then move the data into another schema later.


How to count number of consecutive events matching status of last event?

Here's one solution. Not very efficient I'm afraid:

SELECT
    t.Controller,
    MIN(t.ExecutionTime) AS FirstFaultInSeries,
    MAX(t.ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
  ( SELECT
        Controller,
        COALESCE(MAX(CASE WHEN Result = 0 THEN ExecutionTime END)
                ,'19000101')
          AS LatestGood
    FROM t
    GROUP BY Controller
  ) g
  JOIN t
    ON t.Controller = g.Controller
   AND t.ExecutionTime > g.LatestGood
GROUP BY
    t.Controller ;

Tested: Fiddle

And another:

WITH cte AS
  ( SELECT
        Controller, ExecutionTime, Result,
        ROW_NUMBER() OVER (PARTITION BY Controller
                           ORDER BY ExecutionTime DESC
                          )
          AS Rn,
        ROW_NUMBER() OVER (PARTITION BY Controller, Result
                           ORDER BY ExecutionTime DESC
                          )
          AS RnResult
    FROM t
  )
SELECT
    Controller,
    MIN(ExecutionTime) AS FirstFaultInSeries,
    MAX(ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
    cte
WHERE
    Result = 1
  AND
    Rn = RnResult
GROUP BY
    Controller ;


Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?

Your merge statement is not correctly formed and will always insert all the records, so you will need to update it and tell it to insert the records not matched in the destination table using  WHEN NOT MATCHED like this:

MERGE table
USING
    (SELECT ? AS Search_Col) AS SRC
    ON table.blobTask_id = SRC.Search_Col
WHEN MATCHED THEN
    UPDATE SET
    x= ?,
    y= ?,
    z= ?
WHEN NOT MATCHED THEN
    INSERT (blobTask_id ,x, y, z, )
    VALUES (SRC.Search_Col, ?, ?, ?)

To get the exact statement and parameters for a statement that fails you'll need to capture events before the statement is executed, as the statement fails you will not get a completed statement.

 CREATE EVENT SESSION [a] ON SERVER
  ADD EVENT sqlserver.sql_statement_starting(
  ACTION(sqlserver.sql_text))


auto-incrementing key in self-referencing table, performance

In indexes, DESC is ignored.  (This will be rectified in version 8.0.)
MySQL is happy to use an ASC index (that is, any index) but scan it in descending order.
So DROP that redundant UNIQUE index.

That said, I don't see that anything you have described so far could be cause "excruciatingly slow" inserts.

To further elaborate.  You have 2 BTrees:

One with all the columns, and ordered (ASC) by ID
One with just ID and ordered (ASC) by it.

When inserting a new row (without explicitly specifying ID), the server is optimized to insert it into the "last" block of the BTree and bumping the A_I value.

Meanwhile, the UNIQUE index is acting much the same.  But it is doubling the amount of work to do, with no benefit.

On the other hand, if you have multiple connections doing the inserts, this may be worth noting -- How the inset works:

Get a new A_I value (well optimized)
Check all UNIQUE indexes for duplicates.  This may be slow, since another connection may be locking the adjacent ID.
Insert the row
Throw info into the "Change buffer" for later updating of non-unique indexes.  (This does not apply.)

Also worth noting in InnoDB:

Each transaction has some overhead -- writing to the undo log, writing the "double write" buffer, etc.
Hence, batched insert run much faster because they spread out this overhead over multiple rows.  Can you insert, say, 100 rows in a single INSERT?  That may literally run 10 times as fast.

Do you really need BIGINT?  It takes 8 bytes.  INT UNSIGNED takes only 4 bytes, and allows 4 billion values.


Refactor SQL to left join on TVF requiring input variable

Use the OUTER APPLY operator to join your TVF to your tables and still return the NULL records as well.

NOTE: In the below sample your NULL values might be excluded because there is no NULL handling in the WHERE clause, but without sample data or DDL it was difficult to write this portion.

SELECT
    a.[Date],
    a.[LimitName],
    SUM(c_elig.ProductValue) as ProductValueEligible,
    SUM(c_non.ProductValue) as ProductValueNonEligible,
FROM Limits a
LEFT JOIN LimitsProductTypes b ON b.[Date] = a.[Date] AND b.LimitName = a.LimitName
OUTER APPLY Function_DataSet(a.[Date]) c_elig
OUTER APPLY Function_DataSet(a.[Date]) c_non
WHERE a.Date = '2019-05-15'
    AND (c_elig.IsProductEligible = 'TRUE'
    AND c_elig.Date = a.Date
    AND c_elig.ProductType = b.ProductType)
    AND (f2.IsProductEligible = 'FALSE'
    AND c_non.Date = a.Date
    AND c_non.ProductType = b.ProductType)
GROUP BY a.Date, a.LimitName


Postgres parallel seq scan making high CPU Utilization

You can disable parallel query by setting max_parallel_workers_per_gather to 0 in postgresql.conf.

Then the only ways of speeding up processing are

keep the table cached in RAM
avoid a sequential scan by creating an index

If possible, the second way is much better.


It's a binding issue. The code is bound to the metadata of the table at compile time and it is not late bound.
Try using EXEC and dynamic SQL to overcome this limitation:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';
    EXEC('
    INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (1, 3.2);
    ')

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

Another option is using a stored procedure to insert the data: late binding applies to stored procedure, but not to ad-hoc queries. Again, you would have to use dynamic SQL to create the procedure, but it could make it easier for you to pass parameters:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    EXEC('
    CREATE PROCEDURE insData @p1 int, @p2 DECIMAL(18,6)
    AS
    BEGIN
        INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (@p1, @p2);
    END')

    EXEC InsData 1, 3.2;

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

A temporary stored procedure would work as well:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    EXEC('
    CREATE PROCEDURE #insData @p1 int, @p2 DECIMAL(18,6)
    AS
    BEGIN
        INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (@p1, @p2);
    END')

    EXEC #InsData 1, 3.2;

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;


SSMS Cannot show the editor for this task (Notify Operator Task)

According to this
SSMS on Microsoft Docs

Build number 14.0.17099.0 will likely fix your issue.

  Fixed an issue where "Maintenance Plans Tasks Toolbox" items may be
  missing in SSMS.


SQL Server Agent & sp_send_dbmail failing on permissions

It seems that the SQL server agent User has some problem to run sql cmd.
It uses sql cmd to run the query to attach in the email.
Please try to to give the agent user the database mail user role.
exec sp_addrolemember 'DatabaseMailUserRole', 'domain\agentuser'


All you have to do is the following:

Across the Top of the Access Window, Click the File Tab
Click the Compact and Repair Button

I would not worry about growth because data insertion into MDB usually appends changes to back of the file. Garbage collection and space reusage is not a strong suit for MSAccess. To make matters worse, there is filesize limit (I think it is 2GB) on MDB files. This is why some migrate to SQL Server Express where SQL Server can be configured to perform automatic Compacting and Repairing.


Moving the IBM Informix IFMXDATA directory to another machine

First, make sure this chunks/datafiles are from Windows OS too.
Isn't compatible read a datafile froma different plataform (windows VS Unix VS Linux)

You cannot just move the chunks/datafiles and expected the engine just re-adapt to new path. There internal pointers at theirs configurations where the path is saved and need some administration steps to change.
Basically , you can do this only at a Backup / Restore, using ontape or onbar utilities.

Copying the files can be considered like a cold backup and this way is possible to rename the path.

For that you will need to use the ontape -p -e -rename ... command.
More details, check the manual online .

If your datafiles is from older version which is installed at the target server, I'm not sure if this restore will work because you are doing a upgrade at same time...

Maybe you need install the same version of the engine used to copy the files, restore them with ontape , shutdown the engine, install the new version and upgrade ...


How many parallel workers can I have on Postgres?

The limit of concurrent parallel workers for the whole cluster is max_parallel_workers, which must be ≤ max_worker_processes. The limit of parallel workers per query is max_parallel_workers_per_gather.
Adjusting these parameters, you can start as many workers as you please. Just keep in mind that performance will degrade once you are using more parallel workers than your CPU and I/O can handle. Also, don't forget that there may be other queries running, so don't over-allocate resources.
Don't forget alternative approaches: perhaps the query can be rewritten or indexed to be faster.


List primary keys for all tables - Postgresql

This is more accurate answer:

select tc.table_schema, tc.table_name, kc.column_name
from
    information_schema.table_constraints tc,
    information_schema.key_column_usage kc
where
    tc.constraint_type = 'PRIMARY KEY'
    and kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
    and kc.constraint_name = tc.constraint_name
order by 1, 2;

You missed the and kc.constraint_name = tc.constraint_name part, so it lists all constraints.


If A -> B is augmented with C, is AC -> B legal?

Community wiki answer:

Yes. You can decompose to get AC -> B and AC -> C.


Great question! There's a handful of
ways of doing this, depending on how deeply you want to be involved in
the configuration and long term maintenance.  You certainly could use
Cassandra on top of an encrypted filesystem, such as TrueCrypt or
eCryptfs, and it should function normally.

While possible, it's a little complicated to set up encrypted
filesystem and enforce a secure key management scheme.  Gazzang offers
a commercial product built on top of eCryptfs which provides a turnkey
Encryption & Key Management Platform that would keep all of your
Cassandra data secure, without requiring any changes to your existing
database applications.  Gazzang has a custom-tailored "jumpstart"
documentation for Cassandra that simplifies and assist in your
encryption/security implementation for Cassandra.

Full disclosure: I am employed by Gazzang.


Can't restore database with (apparently) same version

The two managed types of SQL Server in Azure (SQL Database and Managed Instance) are version 12.0.2000.8. Don't be fooled by the low version number, it is always higher than on-prem meaning you can never restore an "Azure SQL" backup to on-prem.
You cannot perform a backup from SQL Database, but you can do a copy only backup from Managed Instance. This would be my guess.
Perhaps the error message is incorrect, and should have said that backup is from 12.00.2000
If it is the 3:rd type of "SQL Server in Azure", a VM with SQL Server installed, then it is of any version you like. This should be restoreable.
So, find out what type of SQL you have in Azure, then take it from there.


VMWare VMotion causing performance to increase?

VMWare VMotion isn't going to reboot your server, restart any services or drop caches. The VM stays live during the VMotion, so you shouldn't lose cache or plans, unless the host you are moving to is under severe memory pressure and ballooning is active.

What does happen during VMotion is increased network latency and maybe a dropped ping while migrating, but that effect is completely gone once the migration is over and should not affect CPU usage inside the guest.

However what you need to understand is that the %CPU use inside the guest is the % you are consuming from the pool of resources that has been allocated to you by the hypervisor (not the underlying CPU) so if you move from a host allotting you 4Ghz to a host allotting 2Ghz the CPU usage inside the guest would double.

There are a few performance counters you could monitor inside the guest VM to see the actual CPU time you are getting from the Hypervisor such as:

% Processor Time
Effective VM Speed in MHz
Host processor speed in MHz
Limit in MHz

See here for a start

Which could give you an idea of the actual MHz you are getting, any limitations imposed by the VMWare configuration etc.

If you have determined the Hypervisor isn't allocating enough cycles to your VM you could set reservations to guarantee a certain amount of MHz or add a cpu weight to the VM giving precedence to your VM over others.

If you have access to esxtop (not the flattened sampled averages charts from vCenter) you should keep an eye on %RDY (indicating your VM has threads waiting for a physical cpu) or %CSTP (indicating co-scheduling issues). For more information read through this yellowbrick post

Since you are saying the host is having other high load VM's you also need to consider that VMWare is trying to allocate resources to the most demanding VM's when configured with defaults. A sudden increase in load in another VM could have dramatic (temporary) effects on your VM's cpu allocation.

Unless there are severe memory pressure issues I don't see how you could get cache flushes unless the new host reclaims a lot of the memory through the balloon driver or dynamic memory settings resulting in cache flushes. Otherwise the machine stays live and memory is copied over in lockstep


What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

Sadly:

Disabling a trigger is not altering the trigger, just like turning off a light is not altering the lightbulb. However it is funny that it is not captured by ALTER_TRIGGER but something still manages to update modify_date when it happens. ¯\-(ツ)-/¯

Your DDL trigger catches the ALTER TABLE variation because that is explicitly altering the table - it says so in the command. While you could argue that disabling the trigger is altering the table regardless of the syntax you use (and I would agree with you, especially since the net result is the same), the difference is that this is currently a gap in what DDL triggers capture, and there is no DISABLE_TRIGGER event (you can vote for them to fix it in this feedback item on UserVoice, but that has been sitting there completely ignored for over a decade). Here is a related, understandably unanswered question on Stack Overflow.
Similarly, a long time ago I remember the ALTER_ events didn't capture RENAME, and this was added as an event later.

You'll have to catch DISABLE TRIGGER in other ways, like audit or Extended Events (I talked about this here):

When was a table trigger disabled and re-enabled?

In which case, you may as well capture the ALTER TABLE variation the same way so they're both logged in the same place.
A really dumb idea would be to poll sys.triggers every n seconds/minutes/hours and log whenever the state of is_disabled changes. This won't let you see who did it but at least you'll know that it happened and, along with other auditing, you may be able to narrow it down.


It's because you haven't actually done anything.

Doing nothing:

phil@ironforge:~$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit

File not created:

phil@ironforge:~$ ls -al test.db
ls: cannot access test.db: No such file or directory

Do something:

phil@ironforge:~$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (b number);
sqlite> .quit

File has been created:

phil@ironforge:~$ ls -al test.db
-rw-r--r-- 1 phil phil 2048 Oct 21 09:31 test.db
phil@ironforge:~$


Alter All Data Types To Different Data Type

Changing datetime2 to datetime is a fundamental data type change. Each ALTER will update every row in the specified table.  This can take a while depending on the size of your tables, especially if your largest tables have many datetime2 columns. Also, if there is any other concurrent activity, the DDL will be blocked by other activity.


SQL Server 2014 - UPSERT XML Multiple rows

Can suggest how to get the value of LineID while parsing the XML?

You can take this output in a variable to check for existence.
DECLARE @input XML = '<Orders>
        <Order>
            <OrderID>334</OrderID>
            <AmountPaid currencyID="EUR">17.10</AmountPaid>
            <UserID>marc58</UserID>
            <ShippingAddress>
                <Name>Marc Juppé</Name>
                <Address>Rue</Address>
                <City>Paris</City>
                <StateOrProvince></StateOrProvince>
                <Country>FR</Country>
                <Phone>333333333</Phone>
                <PostalCode>22222</PostalCode>
             </ShippingAddress>
             <ShippingCosts>4.50</ShippingCosts>
             <Lines>
                <Line>
                   <Details>
                        <ItemID>3664</ItemID>
                        <Store>47</Store>
                        <Title>MCPU DDA010</Title>
                        <SKU>mmx</SKU>
                    </Details>
                    <LineID>1111</LineID>
                    <Quantity>1</Quantity>
                    <Price currencyID="EUR">6.2</Price>
                </Line>
                <Line>
                   <Details>
                        <ItemID>3665</ItemID>
                        <Store>45</Store>
                        <Title>MCPU DFZ42</Title>
                        <SKU>mmy</SKU>
                    </Details>
                    <LineID>1112</LineID>
                    <Quantity>2</Quantity>
                    <Price currencyID="EUR">3.2</Price>
                </Line>
            </Lines>
        </Order>
    </Orders>
'

   SELECT
      LineId = XCol.value('(LineID)[1]','int')
      FROM
      @input.nodes('/Orders/Order/Lines/Line') AS XTbl(XCol)

Here is the result set I get:

LineId
1111
1112


Logical operators OR AND in condition and order of conditions in WHERE

There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause. The single expression that allows statement short-circuiting is CASE-WHEN. The following is from an answer I posted on Stackoverflow:
How SQL Server short-circuits WHERE condition evaluation

It does when it feels like it, but not in the way you immediately think of.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

For further details check the first link in the above blog entry, which is leading to another blog:
Does SQL Server Short-Circuit?

The final verdict?  Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression.  With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.


How to copy value from one column and update another column


Measuring Availability Group synchronization lag - Clarifications

I learned that the AG Dashboard is made up using the sys.dm_hadr_database_replica_states  results . Thanks for all help.

I got an answer what I am looking for :

filestream_send_rate (bigint )

The rate at which the FILESTREAM files are shipped to the secondary replica, in kilobytes (KB)/second.


I can open a connection with SQL Developer but when i do the same with .NET returns me ORA-12514

Your database instance "BDTEST1" will, by default, register service "BDTEST1" in the Oracle Listener. Oracle Listener will expose that SERVICE_NAME.

Replace SERVICE_NAME=BDTEST with SERVICE_NAME=BDTEST1 for your .NET config and you can connect.

Before you enter the app layer, make sure your connect-string is correct.
use sqlplus or SQL Developer (custom connect):

sqlplus user/pass@\"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=BDTEST1)))\"

If you connect using the SID the connect-string will be:

sqlplus user/pass@\"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SID=BDTEST1)))\"

I think you have to escape the " on Windows...not sure.

Side note: Behind a service there can be multiple oracle database hosts and DR hosts. If you connect using SID, you will always connect to the named instance (SID). If this one is down, no failover can happen. So you better use SERVICE_NAME. Ask you admins to provide it to you.

Best of luck!


Multi-Tenant (SaaS) Database Schema with Sharing

Often in a multi-tenant system there is little or no sharing (in fact it is often desirable that other clients can be completely unaware of the others' existence) in which case the simple context_id is sufficient. It is generally sufficient for a hierarchical tenant structure (companies within groups, resellers, etc.) too.

For simple sharing this might work:

ThingTable                                   AccessControl
============                                 =================
ThingID (PK)  <----------------------------  ThingID  (PK, FK)
Owner   (FK)  --.                       .--  TenantID (PK, FK)
Property 1      |     Tenants           |    AccessLevel
Property 2      |     =============     |
  ...           `-->  TenantID (PK)  <--'
Property N            Name
                      etc...

Where AccessLevel could be a simple boolean representing read-only/read+write, or a more complicated collection of properties. You could even remove the "Owner" column from your tables unless you want it as a convenience and include ownership as part of the data in the access control table, or if you have no concept of an owner the objects you are modelling you can do away with it completely (it is usual for the "owner" of an object to have greater control, such as being the only user/group able to chance the access control for other users/groups, but this can be implemented in your access control table without needing a specific owner column).

If you are tracking multiple types of Thing this way, you will need to abstract out the concept so that you have a single entity to point the foreign key in AccessControl at, like so:

ThingTable1                                          AccessControl
================                                     =================
ThingID (PK, FK)  --.                           .--  ThingID  (PK, FK)
Property 1          |                           |    TenantID (PK, FK)  --.
Property N          |                           |    AccessLevel          |
                    |                           |                         |
                    |                           |                         |
ThingTable2         |     Things                |                         |
================    |     =================     |                         |     Tenants
ThingID (PK, FK)  --+-->  ThingID      (PK)  <--'                         |     =============
Property 1          |     Owner        (FK)  -----------------------------+-->  TenantID (PK)
Property N          |     Shared Property 1                                     Name
                    |                                                           etc...
                    |
ThingTable3         |
================    |
ThingID (PK, FK)  --'
Property 1
Property N

Any properties common to all the "Thing" tables would go in the general "Thing" table along with the owner (if yuo are are noting the owner that way, of course).

You can get much more flexible than this: allowing for groups of tenants (from simple groups as used in Unix-a-like filesystems through to supporting hierarchical relationships), having control of which tenants or groups can see each other, ans so forth - but from your description I'm assuming such extra features would be overkill for your current needs so there would be little point complicating the design to include them.


Assuming you are SELECTing only by the PRIMARY KEY, then the following will speed it up:

NO PARTITIONing.  It only slows down such queries, especially because of 1024 partitions.
Switch to InnoDB.  The PRIMARY KEY is "clustered" with the data; this will save a disk hit on each row fetched.  Shrink key_buffer_size to 50M and raise innodb_buffer_pool_size to 70% of available RAM.  InnoDB also avoids table locks.
Compress, in the client, the JSON text; the datatype needs to be BLOB (as it mistakenly is now).

Note:  InnoDB will expand the disk space by a factor of 2-3; the compression will get that space back.  (Please test this with, say, a million rows.  Experiment with the different ROW_FORMATs; I don't know which one will work best for your situation.  And validate the SELECT speed and the lack of interference between reads and writes.)

Do you really have 5000 connections at the same time?  They are probably stumbling over each other and slowing down each other.

You have half turned off the Query cache; also do query_cache_size=0


How to Return XML Node Ordinal, or delete node based on element value?

If you have the value in a variable you can use sql:variable() in the predicate for the delete.

declare @XML xml = '
<Root>

    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>

</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'

set @XML.modify('delete /Root/Sub/Record[Guid = sql:variable("@Guid")]')

Replacing a record with a new record would look something like this.

declare @XML xml = '
<Root>

    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
      <Value>some value</Value>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>

</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '
<Record>
  <Guid>aslkjflaksjflkasjfkljsd</Guid>
  <Value>some new value</Value>
</Record>'

set @XML.modify('insert sql:variable("@NewRecord") after (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')
set @XML.modify('delete (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')

First add the new record after the existing one and the delete the first occurrence where Guid is a match. Without [1] in the delete you will delete all occurrences, not only the first one.


Why do I not see Configure Distribution when I right-click on replication?

I found that the distribution and publication wasn't correctly disabled. When I tried to run the disable the publishing and Distribution (as seen in my image above) I was getting an error that the process couldn't complete because the distribution database was still in use. so I used

SELECT spid FROM sys.sysprocesses WHERE dbid = db_id('distribution')

to find the spid that was using it and then used

KILL <the spid>

I was then able to fully disable the publishing and Distribution and now I do see the configure distribution option.


You can use several ETL tools for this, it could be on premies, or cloud based, final decision always depends from cost, corporate standards and many other moments

good starting point as most close to Your request:

Informatica Cloud -
https://marketplace.informatica.com/mpresources/docs/Implementing_DWH_on_AWS_in_Hybrid_Env.pdf
Talend Cloud - https://www.talend.com/products/integration-cloud
Community version of Talend as well could be installed on any VPS and
connect to cloud databases

Any tools and recommendations always depends from personal experience and of course on the market You can find more commercial and open source tools


Based on the information you provided I did a very basic test and I suggest you set up something similar before you make a final decision.
My result shows computed column will perform better over trigger but I want to stress that it might vary with your table structure, insert rate, other activity in the table and you need to test.

Set up:

CREATE TABLE TestTrigger
(
    amt INT NOT NULL,
    field1update INT,
    field2update INT,
    field3update INT
 )
 GO

CREATE TRIGGER triTestTrigger on TestTrigger
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO TestTrigger
       SELECT amt, (amt/12)*14, (amt/12)*16,(amt/12)*18
       FROM inserted
END
GO

CREATE TABLE TestCompCol
(
    amt INT NOT NULL,
    field1update AS (amt/12)*14,
    field2update AS (amt/12)*16,
    field3update AS (amt/12)*18
 )
 GO

Inserting into both table.

 INSERT INTO TestTrigger
 (amt)
 VALUES(1)
 GO
 INSERT INTO TestCompCol
 (amt)
 VALUES(1)
 GO

First(trigger) insert has to do more work(70%) compare to second (computed column) insert(30%).
If I look at the subtree cost for first insert (.010023+.0132842)=0.0233072 and for 2nd insert it is .0100022.

For cpu time of trigger insert I get

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 90
  ms.

For computed column insert I get:

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 2 ms.


Getting started with AWS: Price for large databases?

are you looking at RDS or EC2 ?, I would suggest you go with an EC2 as you can get a EBS backed storage around 100GB for much less on a lower instance type.have look at this calculator it will give you a good estimate


Not all variables bound ORA-01008: not all variables bound error

Maybe you have set define to off.

set define off
select last_name, salary from employees where employees.salary>&sal;

Error starting at line : 2 in command -
select last_name, salary from employees where employees.salary>&sal
Error at Command Line : 2 Column : 31
Error report -
SQL Error: ORA-01008: not all variables bound
01008. 00000 -  "not all variables bound"
*Cause:
*Action:

Set it to on (which is the default value by the way):

set define on
select last_name, salary from employees where employees.salary>&sal;

With define set to on, SQL Developer will prompt for a value for the variable &sal.


Something like this:

Party
id
type: {individual, group}
name

PartyRelationship
fromPartyId
toPartyId
type: {groupMembership}

Event
id
name
scheduledStart
scheduledEnd
actualStart
actualEnd

EventRole
eventId
roleType {organizer, scheduler, participant}
partyId

List
id
eventId
name

ListItem
id
listId
description
createdBy /* should point to a party */

An Event hasMany Roles, which could be organizers, participants, etc.

A participant can be either an individual or group, both of which are subtypes of Party

A Group party has many Individual parties, as described in the PartyRelationship table. An Individual can belong to zero+ groups.

An Event hasMany Lists, and a List hasMany Items. A list belongsTo one Event, and a ListItem belongsTo one List.

Example usage:

insert into party values (1, 'individual', 'neil');
insert into party values (2, 'individual', 'bernk');
insert into party values (3, 'individual', 'simon');
insert into party values (4, 'individual', 'ypercube');

insert into party values (5, 'group', 'canadians');

insert into partyRelationships values (5, 1);
insert into partyRelationships values (5, 2);
/* now neil and bernk are related to the 'canadians' party */

insert into event values (1, 'an event with canadians and ypercube, organized by simon', '2012-07-01', '2012-07-02');

insert into eventRoles values (1, 'organizer', 3); /* simon is the organizer*/
insert into eventRoles values (1, 'participant', 5); /* the 'canadians' group is a participant */
insert into eventRoles values (1, 'participant', 4) /* ypercube is a participant too */


Best practices for Feed restructuring in a social app database

Real mongo-design centers around the queries and only the queries. From the database perspective, there is no necessity to separate the document types. From the query perspective, I get the impression that you basically need a single collection, which contains all documents. If you need to distinguish between posts and polls, do this via a field in the document.

Also get rid of this "pointer to user" stuff. Put a copy of the necessary user fields into the respective document, and use this to display whatever you need.

The ultimate goal is, to display your complete page with a single query, without using the aggregation framework (which is slow and only a mediocre workaround for bad database design.)

And get these relational concepts out of your head. Use redundancy, optimize for the reading query. If a user changes, well yes, you'll have to update a thousand documents - but this is not the normal case, so you optimize the read and do more work on the write.

In a comment, you asked:

  What if I still created a new collection Feed, but instead of pointers, I'll use copies of documents? (without every field, only selected ones) Could that somehow slow things down or not be as fast/good as just integrating the polls inside the Post collection?

There's nothing wrong with duplicating the necessary data for a given query in a separate collection like your "Feed" collection. But "having too many fields" is once more a relational thought.

You don't have any fields in a collection. You have documents, and these documents have a number of fields individually.

There is no negative effect when you have 100 document types which sum up to 10000 different fields. There may be a technical drawback if you start to create many indexes, but apart from that you may well dump everything in a single collection.


Transforming multiple rows into a single row

You must pivot your data.

This can be done using the Pivot operator:

SELECT ID, [date]
    , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4
    , total as [Sum]
FROM (
    SELECT * FROM data d
    CROSS APPLY (SELECT total = SUM([money]) FROM data
        WHERE [date] = d.[date] AND ID = d.[id]) a
) t
PIVOT (
    MAX([money])
    FOR [hour] IN ([1], [2], [3], [4])
) piv

You are using SQL Server 2005 and therefore the total is calculated using CROSS APPLY. See SQL Fiddle.

Without PIVOT, you can also use a GROUP BY with CASEs:

SELECT ID, [date]
    , Hour1 = SUM(CASE WHEN [hour] = 1 THEN [money] END)
    , Hour2 = SUM(CASE WHEN [hour] = 2 THEN [money] END)
    , Hour3 = SUM(CASE WHEN [hour] = 3 THEN [money] END)
    , Hour4 = SUM(CASE WHEN [hour] = 4 THEN [money] END)
    , [Sum] = SUM([money])
FROM data d
GROUP BY ID, [date]

See SQL Fiddle.

With SQL Server 2012 you could easily calculate the total using the SUM(...) OVER(...) window function:

SELECT ID, [date], [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4, [Sum]
FROM (
       SELECT *, [Sum] = SUM([money]) OVER(PARTITION BY [date], ID) FROM data
) d
PIVOT (
       MAX([money])
       FOR [hour] IN ([1], [2], [3], [4])
) piv

See SQL Fiddle.

Note that a window function with an aggregate such as SUM(...) OVER(...) also works with SQL Server 2005 when the OVER(...) clause only contains a PARTITION BY ... and no ORDER BY .... See OVER Clause (2005) (Thanks Andriy M for the link).

Output:

ID  date    Hour1   Hour2   Hour3   Hour4   Sum
1   20-1    10      20      30      40      100
1   21-1    50      10      20      30      110
2   20-1    40      50      10      20      120


Compare same tables in 2 different databases and 2 different servers

Alter table add column like this:

ALTER TABLE dbo.version2 ADD xyz VARCHAR(20) NULL;

https://docs.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-2017


performance benefits of shrinking a database

If you are maintaining your indexes as you say, there will be no performance benefit.

Performance will actually get worse from shrinking, and your next index rebuild job will likely have significantly more work to do than usual.

The most common reason people think that the shrinking of a database works to increase performance is because the engine has to read fewer pages for queries, however by shrinking you're not actually freeing up any pages, you're just shifting them around in the database, leading to fragmentation and degraded performance. You do reduce the overall number of pages for the database, but not the number of used pages holding data.

Another point is it's likely to take an extremely long time to shrink that database, due to the shrink process being a single-threaded process. It will likely also cause performance issues during the shrink, and potentially blocking.

Check out When is it OK to shrink a database DBA SE question

I would highly recommend against shrinking.

One option for you might be to transfer the data into a fresh, empty copy of the same database, then delete the old one - this is likely to be quicker and tidier.


How to insert values into a table from two different tables?

You would write the query like this

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', t.id, s.id
from teachers t,students s
where t.teacher_name = 'david'
and s.student_name = 'sam';

Be careful. This is a Cartesian product. Another way to approach this is

select teacher_id into @tid from teachers where teacher_name = 'david';
select student_id into @sid from students where student_name = 'sam';
insert into classroom (date, teacher_id, student_id) values ('2014-07-08',@tid,@sid);


Read access to a login in MS SQL schemas

You should probably grant db_datareader access to that user.
Members of the db_datareader fixed database role can read all data from all user tables.

USE master
GO

DECLARE @DatabaseName VARCHAR(32)
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[MyUser]' --Your User

DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN Grant_Permission
FETCH NEXT FROM Grant_Permission INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; EXEC sp_addrolemember N''db_datareader'', ' + @User + ''

    --EXEC sp_executesql @SQL
    PRINT @SQL

FETCH NEXT FROM Grant_Permission INTO @DatabaseName
END
CLOSE Grant_Permission
DEALLOCATE Grant_Permission

With this permission in-place, you won't need to grant read access every time you create a new table.


How do I switch master with slave and vice versa on MySQL?

If you use a DBVIP, you could remove the DBVIP from the Master and bring it up on the Slave.

EXAMPLE

Let's make up a DBVIP, like 10.1.2.30.

Put this up on the Master in the OS

ip addr addr 10.1.2.30/24 dev eth1

Replace your IP in your application with 10.1.2.30, Let the application continue to run for a while, making sure the DBVIP is being used by the application.

Here is something aggressive:

service httpd stop on all Apache Servers
service mysql stop on the Master
ip addr del 10.1.2.30/24 dev eth1 on the Master
ip addr add 10.1.2.30/24 dev eth1 on the Slave
service httpd start on all Apache Servers
SHOW PROCESSLIST; on the Slave to make sure there are incoming DB Connections

If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover.

UPDATE 2012-09-19 14:28 EDT

If you cannot use a DBVIP, you must do more work instead

STEP 01) Activate Binary Logging on the Slave

Add this to /etc/my.cnf on the Slave

[mysqld]
log-bin=mysql-bin

STEP02) service mysql restart on the Slave

Binary Logging should be enabled in the Slave

STEP03) Run the CHANGE MASTER TO command on the Master using the Slave as its Master

Use mysql-bin.000001 as the Master_Log_File
Use the following number as the Master_Log_Pos

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0

STEP04) Run START SLAVE; on the Master

At this point

the Master is the Slave's Slave
the Slave is the Master Slave

This configuration is better known as

Master/Master
Circular Replication

STEP05) service httpd stop on all Apache Servers

STEP06) Change the IP address in the App to Connect to the Slave

STEP07) service httpd start on all Apache Servers

STEP08) SHOW PROCESSLIST; on the Slave to make sure there are incoming DB Connections

If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover without MySQL Downtime. The only downtime there is comes from the Window of time STEP05 - STEP07.


The root issue here is the index building - when you are building an index, you do so (by default) in the foreground and it blocks all other operations (including the mongostat polling).  In addition, the index build requires external sorting, which is computationally intensive, and potentially significant IO.   In terms of how much CPU and IO hit you should expect, the size of the index, the number of keys and the amount of data will dictate that - generally, the more data you have and the more complex the index, the longer and more expensive this process will be.

To avoid this issue, you can set your index to build in the background, though you should note that until the fix for SERVER-2771 is available in a stable release (checked into 2.5.0 as of writing this answer) the indexes will build in the foreground on secondaries (and hence still be blocking operations).  That is why this guide to building indexes on Replica Sets is still required reading for building large indexes on a set.

Finally, in terms of why mongostat made things worse from a CPU perspective - it is likely unable to poll successfully due to the blocking operation of the index build and this is manifesting as increased CPU until you kill mongostat.  This should be pretty easy to reproduce, and (depending on the level of CPU impact, and the version you are using) would potentially be grounds for filing a bug.  If it still happens with a background index build, as described above, that would be far more worrying.


When to periodically RESTART SQL SERVER 2008 R2?

From my experience, I've never needed to restart a production server, except for patches. Just like Marian, I do not understand how a reboot could have a beneficial effect on your 4 points. My simple advice would be to avoid reboots as much as possible, as they are no cure to your problems. Proper configuration, profiling ... will get you much further.


Does mysql or mariadb have the concept of SQL Server's "included columns" for indexes?

Looking at the MySQL Documentation, the glossary indicates this about Covering Indexes:

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

The implication here is that there is no direct equivalent of an INCLUDE statement in MySQL, however if the index provides coverage of the columns involved in an index, it can, rather obviously, still be considered to be covering, much like an index with included columns in SQL Server would be.
MariaDB has the following in their documentation for covering indexes:

A "Covering" index is an index that contains all the columns in the SELECT. It is special in that the SELECT can be completed by looking only at the INDEX BTree. (Since InnoDB's PRIMARY KEY is clustered with the data, "covering" is of no benefit when considering at the PRIMARY KEY.)

Mini-cookbook:

Gather the list of column(s) according to the "Algorithm", above.
Add to the end of the list the rest of the columns seen in the SELECT, in any order.

Examples:

SELECT x FROM t WHERE y = 5; ⇒ INDEX(y,x) -- The algorithm said just INDEX(y)
SELECT x,z FROM t WHERE y = 5 AND q = 7; ⇒ INDEX(y,q,x,z) -- y and q in either order (Algorithm), then x and z in either order (covering).
SELECT x FROM t WHERE y > 5 AND q > 7; ⇒ INDEX(y,q,x) -- y or q first (that's as far as the Algorithm goes), then the other two fields afterwards.

The speedup you get might be minor, or it might be spectacular; it is hard to predict.
But...

It is not wise to build an index with lots of columns. Let's cut it off at 5 (Rule of Thumb).
Prefix indexes cannot 'cover', so don't use them anywhere in a 'covering' index.
There are limits (3KB?) on how 'wide' an index can be, so "covering" may not be possible.


Get all database, table and column names and column definitions

I have an script that I used to use.
this is to compare 2 databases, in the example I am comparing bocss2 with tablebackups.

Now I only use the visual studio schema comparison tool as suggested in the comments.

It is not even properly formatted, it is not a complete, ready, free from bugs solution, but it would at least give you some ideas.

I still would like to suggest you use the visual studio community edition

--sp_CompareDb @SourceDB='Bocss2',@TargetDb='Tablebackups'

----DROP PROCEDURE sp_CompareDb

--USE MASTER
--GO

--CREATE PROC sp_CompareDb(
--                            @SourceDB SYSNAME,
--                            @TargetDb SYSNAME
--                            )
--AS

/*
DECLARE @SourceDB SYSNAME='DB1',
                  @TargetDb SYSNAME='DB2'
*/

DECLARE @SourceDB SYSNAME='Bocss2',
        @TargetDb SYSNAME='Tablebackups'

  SET nocount ON
  SET ansi_warnings ON
  SET ansi_nulls ON

  DECLARE @sqlStr VARCHAR(8000)
  SET @SourceDB= Rtrim(Ltrim(@SourceDB))
  IF DB_ID(@SourceDB) IS NULL
  BEGIN
        PRINT 'Error: Unable to find the database '+ @SourceDB +'!!!'
        RETURN
  END

  SET @TargetDb= Rtrim(Ltrim(@TargetDb))
  IF DB_ID(@TargetDb) IS NULL
  BEGIN
        PRINT 'Error: Unable to find the database '+ @TargetDb +'!!!'
        RETURN
    END

  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)
  PRINT 'Comparing databases ' + @SourceDB + ' and ' + @TargetDb
  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)
  -----------------------------------------------------------------------------------------
    -- Create temp tables needed to hold the db structure
    -----------------------------------------------------------------------------------------

    IF OBJECT_ID('TEMPDB..#TABLIST_SOURCE')IS NOT NULL
        DROP TABLE #TABLIST_SOURCE
    IF OBJECT_ID('TEMPDB..#TABLIST_TARGET')IS NOT NULL
        DROP TABLE #TABLIST_TARGET
    IF OBJECT_ID('TEMPDB..#IDXLIST_SOURCE')IS NOT NULL
        DROP TABLE #IDXLIST_SOURCE
    IF OBJECT_ID('TEMPDB..#IDXLIST_TARGET')IS NOT NULL
        DROP TABLE #IDXLIST_TARGET
    IF OBJECT_ID('TEMPDB..#FKLIST_SOURCE')IS NOT NULL
        DROP TABLE #FKLIST_SOURCE
    IF OBJECT_ID('TEMPDB..#FKLIST_TARGET')IS NOT NULL
        DROP TABLE #FKLIST_TARGET
    IF OBJECT_ID('TEMPDB..#TAB_RESULTS')IS NOT NULL
        DROP TABLE #TAB_RESULTS
    IF OBJECT_ID('TEMPDB..#IDX_RESULTS')IS NOT NULL
        DROP TABLE #IDX_RESULTS
    IF OBJECT_ID('TEMPDB..#FK_RESULTS')IS NOT NULL
        DROP TABLE #FK_RESULTS

  CREATE TABLE #TABLIST_SOURCE
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLENAME SYSNAME ,
        COLUMNNAME SYSNAME,
        DATATYPE SYSNAME,
        NULLABLE VARCHAR(15)
  )

  CREATE TABLE #TABLIST_TARGET
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLENAME SYSNAME ,
        COLUMNNAME SYSNAME,
        DATATYPE SYSNAME,
        NULLABLE VARCHAR(15)
  )

  CREATE TABLE #IDXLIST_SOURCE
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLE_NAME SYSNAME,
        IDX_NAME    SYSNAME ,
        IDX_TYPE VARCHAR(20),
        IS_PRIMARY_KEY VARCHAR(10),
        IS_UNIQUE VARCHAR(10),
        IDX_COLUMNS VARCHAR(1000),
        IDX_INCLUDED_COLUMNS VARCHAR(1000)
  );

  CREATE TABLE #IDXLIST_TARGET
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLE_NAME SYSNAME,
        IDX_NAME    SYSNAME ,
        IDX_TYPE VARCHAR(20),
        IS_PRIMARY_KEY VARCHAR(10),
        IS_UNIQUE VARCHAR(10),
        IDX_COLUMNS VARCHAR(1000),
        IDX_INCLUDED_COLUMNS VARCHAR(1000)
  );

  CREATE TABLE #FKLIST_SOURCE
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        FK_NAME SYSNAME,
        FK_TABLE sysname,
        FK_COLUMNS varchar(1000),
        PK_TABLE sysname,
        PK_COLUMNS varchar(1000)
  );

  CREATE TABLE #FKLIST_TARGET
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        FK_NAME SYSNAME,
        FK_TABLE sysname,
        FK_COLUMNS varchar(1000),
        PK_TABLE sysname,
        PK_COLUMNS varchar(1000)
  );

  CREATE TABLE #TAB_RESULTS
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLENAME SYSNAME ,
        COLUMNNAME SYSNAME,
        DATATYPE SYSNAME,
        NULLABLE VARCHAR(15),
        REASON VArchar(150)
  );

  CREATE TABLE #IDX_RESULTS
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLE_NAME SYSNAME,
        IDX_NAME    SYSNAME ,
        IDX_TYPE VARCHAR(20),
        IS_PRIMARY_KEY VARCHAR(10),
        IS_UNIQUE VARCHAR(10),
        IDX_COLUMNS VARCHAR(1000),
        IDX_INCLUDED_COLUMNS VARCHAR(1000),
        REASON Varchar(150)
  );

  CREATE TABLE #FK_RESULTS
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        FK_NAME SYSNAME,
        FK_TABLE sysname,
        FK_COLUMNS varchar(1000),
        PK_TABLE sysname,
        PK_COLUMNS varchar(1000),
        REASON VArchar(150)
  );

  PRINT 'Getting table and column list!';
  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

  BEGIN
        INSERT INTO #TABLIST_SOURCE(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
        EXEC('SELECT '''+@SourceDB +''', T.TABLE_NAME TABLENAME,
             C.COLUMN_NAME COLUMNNAME,
             TY.name + case when TY.name IN (''char'',''varchar'',''nvarchar'') THEN
                ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')''
                ELSE
                    ''''
                END
                DATATYPE,
                CASE WHEN C.is_nullable=''NO'' THEN
                    ''NOT NULL''
                    ELSE
                    ''NULL''
                END NULLABLE
                    FROM '+@SourceDB+'.INFORMATION_SCHEMA.TABLES T
                        INNER JOIN  '+@SourceDB+'.INFORMATION_SCHEMA.COLUMNS C
                            ON T.TABLE_NAME=C.TABLE_NAME
                            and T.TABLE_CATALOG=C.TABLE_CATALOG
                            and T.TABLE_SCHEMA=C.TABLE_SCHEMA
                         INNER JOIN '+@SourceDB+'.sys.types TY
                        ON C.DATA_TYPE =TY.name
                        ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION');

        INSERT INTO #TABLIST_TARGET(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
        EXEC('SELECT '''+@TargetDB +''', T.TABLE_NAME TABLENAME,
             C.COLUMN_NAME COLUMNNAME,
             TY.name + case when TY.name IN (''char'',''varchar'',''nvarchar'') THEN
                ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')''
                ELSE
                    ''''
                END
                DATATYPE,
                CASE WHEN C.is_nullable=''NO'' THEN
                    ''NOT NULL''
                    ELSE
                    ''NULL''
                END NULLABLE
                    FROM '+@TargetDB+'.INFORMATION_SCHEMA.TABLES T
                        INNER JOIN  '+@TargetDB+'.INFORMATION_SCHEMA.COLUMNS C
                            ON T.TABLE_NAME=C.TABLE_NAME
                            and T.TABLE_CATALOG=C.TABLE_CATALOG
                            and T.TABLE_SCHEMA=C.TABLE_SCHEMA
                         INNER JOIN '+@TargetDB+'.sys.types TY
                        ON C.DATA_TYPE =TY.name
                        ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION');

        PRINT 'Getting index list!';
        PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

    INSERT INTO #IDXLIST_SOURCE(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS)
        EXEC ('WITH CTE AS (
           SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS TableName
                       ,i.name AS IndexName
                                             ,case when ic.is_included_column =0 then
                                                    c.name end AS ColumnName
                                            ,case when ic.is_included_column =1 then
                                                    c.name end AS IncludedColumn
                                                    ,i.type_desc
                       ,i.is_primary_key,i.is_unique
           FROM  '+@SourceDB+'.sys.indexes i
           INNER JOIN '+@SourceDB+'.sys.index_columns ic
                   ON  i.index_id    =   ic.index_id
                   AND i.object_id   =   ic.object_id
           INNER JOIN '+@SourceDB+'.sys.columns c
                   ON  ic.column_id  =   c.column_id
                   AND i.object_id   =   c.object_id
           INNER JOIN '+@SourceDB+'.sys.tables t
                   ON  i.object_id = t.object_id
)
SELECT '''+@SourceDB+''',c.TableName TABLE_NAME,c.IndexName INDEX_NAME,c.type_desc INDEX_TYPE ,c.is_primary_key IS_PRIMARY_KEY,c.is_unique IS_UNIQUE
       ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS COLUMNS
       ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS INCLUDED_COLUMNS
FROM   CTE c
GROUP  BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
ORDER  BY c.TableName ASC,c.is_primary_key DESC, C.IndexName ' );

    INSERT INTO #IDXLIST_TARGET(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS)
        EXEC ('WITH CTE AS (
           SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS TableName
                       ,i.name AS IndexName
                                             ,case when ic.is_included_column =0 then
                                                    c.name end AS ColumnName
                                            ,case when ic.is_included_column =1 then
                                                    c.name end AS IncludedColumn
                                                    ,i.type_desc
                       ,i.is_primary_key,i.is_unique
           FROM  '+@TargetDB+'.sys.indexes i
           INNER JOIN '+@TargetDB+'.sys.index_columns ic
                   ON  i.index_id    =   ic.index_id
                   AND i.object_id   =   ic.object_id
           INNER JOIN '+@TargetDB+'.sys.columns c
                   ON  ic.column_id  =   c.column_id
                   AND i.object_id   =   c.object_id
           INNER JOIN '+@TargetDB+'.sys.tables t
                   ON  i.object_id = t.object_id
)
SELECT '''+@TargetDB+''',c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
       ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS Columns
       ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName FOR XML PATH('''')),1 ,1, '''') AS IncludedColumns
FROM   CTE c
GROUP  BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
ORDER  BY c.TableName ASC,c.is_primary_key DESC, C.IndexName ');

PRINT 'Getting foreign key list!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #FKLIST_SOURCE(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS)
    EXEC ('With CTE
                AS
            (select OBJECT_NAME(FK.parent_object_id,db_id('''+@TargetDB+''')) PK_TABLE,
                        C1.name PK_COLUMN,
            object_name(FK.referenced_object_id,db_id('''+@TargetDB+'''))FK_TABLE,
            C2.name FK_COLUMN,
            FK.name     FK_NAME
from
        '+@SourceDB+'.sys.foreign_keys FK
            inner join
        '+@SourceDB+'.sys.foreign_key_columns FKC
            on FK.object_id=FKC.constraint_object_id
            inner join
        '+@SourceDB+'.sys.columns C1
            on FKC.parent_column_id=C1.column_id
            and FKC.parent_object_id=C1.object_id
            inner join
        '+@SourceDB+'.sys.columns C2
            on FKC.referenced_column_id=C2.column_id
            and FKC.referenced_object_id=C2.object_id
    )
SELECT '''+@SourceDB+''',C.FK_NAME,
             C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS,
             C.PK_TABLE,
             STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS
FROM CTE C
group by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

order by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

             ')

INSERT INTO #FKLIST_TARGET(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS)
EXEC('
        With CTE
AS
(select OBJECT_NAME(FK.parent_object_id,db_id('''+@TargetDB+''')) PK_TABLE,
            C1.name PK_COLUMN,
            object_name(FK.referenced_object_id,db_id('''+@TargetDB+'''))FK_TABLE,
            C2.name FK_COLUMN,
            FK.name     FK_NAME
from
        '+@TargetDB+'.sys.foreign_keys FK
            inner join
        '+@TargetDB+'.sys.foreign_key_columns FKC
            on FK.object_id=FKC.constraint_object_id
            inner join
        '+@TargetDB+'.sys.columns C1
            on FKC.parent_column_id=C1.column_id
            and FKC.parent_object_id=C1.object_id
            inner join
        '+@TargetDB+'.sys.columns C2
            on FKC.referenced_column_id=C2.column_id
            and FKC.referenced_object_id=C2.object_id
    )
SELECT '''+@TargetDB+''',C.FK_NAME,
             C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS,
             C.PK_TABLE,
             STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS
FROM CTE C
group by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

order by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

             ')
END;

PRINT 'Print column mismatches!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #TAB_RESULTS(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
SELECT @SourceDB AS DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE
EXCEPT
SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS
INNER JOIN
#TABLIST_TARGET TT
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_NONMATCH
CROSS JOIN (SELECT 'Missing Column' As Reason)Tab2
UNION ALL
SELECT @TargetDb as DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET
EXCEPT
SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT
INNER JOIN
#TABLIST_SOURCE TS
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_MATCH
CROSS JOIN (SELECT 'Missing column ' As Reason)Tab2

--NON MATCHING COLUMNS
INSERT INTO #TAB_RESULTS(DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
    SELECT
        @SourceDB as DATABASENAME,
        TABLENAME,
        COLUMNNAME,
        DATATYPE,
        NULLABLE,
        REASON
    FROM
    (SELECT * FROM
    (SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS
        INNER JOIN
         #TABLIST_TARGET TT ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T
         EXCEPT
         (SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE
    INTERSECT
    SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET))TT1
    CROSS JOIN (SELECT 'Definition not matching'AS REASON) t

    UNION ALL

    SELECT @TargetDb as DATABASENAME,
        TABLENAME,
        COLUMNNAME,
        DATATYPE,
        NULLABLE,
        REASON
    FROM(
    SELECT * FROM
    (SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT
        INNER JOIN
         #TABLIST_SOURCE TS ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T
         EXCEPT
         (SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET
    INTERSECT
    SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE))TAB_NONMATCH
    CROSS JOIN (SELECT 'Definition not matching' AS REASON)T;

PRINT 'Print index mismatches!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #IDX_RESULTS(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
SELECT @SourceDB AS DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM
(SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE
EXCEPT
SELECT TS.TABLE_NAME,TS.IDX_NAME, TS.IDX_COLUMNS,TS.IDX_INCLUDED_COLUMNS,TS.IS_PRIMARY_KEY,TS.IS_UNIQUE FROM #IDXLIST_SOURCE TS
INNER JOIN
#IDXLIST_TARGET TT
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_NONMATCH
CROSS JOIN (SELECT 'Missing Index n' As Reason)Tab2
UNION ALL
SELECT @TargetDb as DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET
EXCEPT
SELECT TT.TABLE_NAME,TT.IDX_NAME,TT.IDX_COLUMNS,TT.IDX_INCLUDED_COLUMNS,TT.IS_PRIMARY_KEY,TT.IS_UNIQUE FROM #IDXLIST_TARGET TT
INNER JOIN
#IDXLIST_SOURCE TS
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_MATCH
CROSS JOIN (SELECT 'Missing index ' As Reason)Tab2

--NON MATCHING INDEX
INSERT INTO #IDX_RESULTS(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
    SELECT
        @SourceDB as DATABASENAME,
        TABLE_NAME,
        IDX_NAME,
        IDX_COLUMNS,
        IDX_INCLUDED_COLUMNS,
        IS_PRIMARY_KEY,
        IS_UNIQUE,
        REASON
    FROM
    (SELECT * FROM
    (SELECT TS.TABLE_NAME,
        TS.IDX_NAME,
        TS.IDX_COLUMNS,
        TS.IDX_INCLUDED_COLUMNS,
        TS.IS_PRIMARY_KEY,
        TS.IS_UNIQUE
        FROM #IDXLIST_SOURCE TS
        INNER JOIN
         #IDXLIST_TARGET TT ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T
         EXCEPT
         (SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE
    INTERSECT
    SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET))TT1
    CROSS JOIN (SELECT 'Definition not matching' AS REASON) t

    UNION ALL

    SELECT @TargetDb as DATABASENAME,
        TABLE_NAME,
        IDX_NAME,
        IDX_COLUMNS,
        IDX_INCLUDED_COLUMNS,
        IS_PRIMARY_KEY,
        IS_UNIQUE,
        REASON
    FROM(
    SELECT * FROM
    (SELECT TT.TABLE_NAME,
        TT.IDX_NAME,
        TT.IDX_COLUMNS,
        TT.IDX_INCLUDED_COLUMNS,
        TT.IS_PRIMARY_KEY,
        TT.IS_UNIQUE  FROM #IDXLIST_TARGET TT
        INNER JOIN
         #IDXLIST_SOURCE TS ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T
         EXCEPT
         (SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET
    INTERSECT
    SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE))TAB_NONMATCH
    CROSS JOIN (SELECT 'Definition not matching' AS REASON)T;

PRINT 'Print key mismatches!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
SELECT @SourceDB AS DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON
 FROM
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE
EXCEPT
SELECT TS.FK_NAME,TS.FK_TABLE,TS.FK_COLUMNS,TS.PK_TABLE,TS.PK_COLUMNS FROM #FKLIST_SOURCE TS
INNER JOIN
#FKLIST_TARGET TT
ON TS.FK_NAME=TT.FK_NAME) TAB_NONMATCH
CROSS JOIN (SELECT 'Missing Index n' As Reason)Tab2

UNION ALL

SELECT @TargetDb as DATABASENAME,FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON FROM
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET
EXCEPT
SELECT TT.FK_NAME,TT.FK_TABLE,TT.FK_COLUMNS,TT.PK_TABLE,TT.PK_COLUMNS FROM #FKLIST_TARGET TT
INNER JOIN
#FKLIST_SOURCE TS
ON TS.FK_NAME=TT.FK_NAME) TAB_MATCH
CROSS JOIN (SELECT 'Missing key' As Reason)Tab2

--NON MATCHING Keys
INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
    SELECT
        @SourceDB as DATABASENAME,
        FK_NAME,
        FK_TABLE,
        FK_COLUMNS,
        PK_TABLE,
        PK_COLUMNS,
        REASON
    FROM
    (SELECT * FROM
    (SELECT TS.FK_NAME,
        TS.FK_TABLE,
        TS.FK_COLUMNS,
        TS.PK_TABLE,
        TS.PK_COLUMNS
        FROM #FKLIST_SOURCE TS
        INNER JOIN
         #FKLIST_TARGET TT ON TS.FK_NAME=TT.FK_NAME)T
         EXCEPT
         (SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE
    INTERSECT
    SELECT  FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET))TT1
    CROSS JOIN (SELECT 'Definition not matching' AS REASON) t

    UNION ALL

    SELECT @TargetDb as DATABASENAME,
        FK_NAME,
        FK_TABLE,
        FK_COLUMNS,
        PK_TABLE,
        PK_COLUMNS,
        REASON
    FROM(
    SELECT * FROM
    (SELECT TT.FK_NAME,
        TT.FK_TABLE,
        TT.FK_COLUMNS,
        TT.PK_TABLE,
        TT.PK_COLUMNS  FROM #FKLIST_TARGET TT
        INNER JOIN
         #FKLIST_SOURCE TS ON TS.FK_NAME=TT.FK_NAME)T
         EXCEPT
         (SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET
    INTERSECT
    SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE))TAB_NONMATCH
    CROSS JOIN (SELECT 'Definition not matching' AS REASON)T;

--Print Final Results

--SELECT * FROM #TAB_RESULTS
--where tablename= 'tblBOrder'

SELECT * FROM #IDX_RESULTS
where TABLE_NAME = 'tblBorder'

--SELECT * FROM #FK_RESULTS

--select * from #FKLIST_TARGET

select * FROM #IDXLIST_SOURCE
go
select * FROM #IDXLIST_target
go


The query itself is problematic.

It's not using labels in the match pattern, which means it will have to perform an AllNodesScan to find both n1 and n2. So that's happening twice per row in your CSV file. An EXPLAIN of the query would have showed you this in the query plan.

You need to add labels into these match patterns for n1 and n2, which would at least get you to two NodeByLabelScans per row, but to make this performant you need to also have an index on the relevant label for the id property.


I think I am going to store the primary slug of the ticket-type in the alias table, too.

Yes. And to avoid redundancy, remove it from the table ticket_type and add a prim boolean column to ticket_type_slug_alias, to identify which of the slug aliases is the primary one. In this way, when looking for the ticket_type id by slug you have only to access one table, while when looking for the primary slug knowing the id you’d need to select FROM ticket_type t JOIN ticket_type_slug_alias a on (a.prim = true and a.id = t.id)


Setting isolation level at database level

There are better ways to handle this than dirty reads.  READ UNCOMMITTED queries will still block DDL, like TRUNCATE TABLE, DROP TABLE and ALTER TABLE … SWITCH, so it might not even help.

First, of course, is RCSI which is the basic correct solution for concurrently loading and reading from a database (even a Data Warehouse).  It does not unnecessarily slow down your loads.  It does what is necessary to enable concurrent readers and writers.  And RCSI only creates row versions for UPDATE and DELETE.  INSERT does not generate row versions unless the table has a trigger.

Second, if you want to reload the data warehouse while reports are running READ UNCOMMITTED, they will get wrong results.  So you might as well just KILL the connected sessions, or have your ETL job alter database current set single_user with rollback immediate.

Third, you can use a Database Snapshot to keep the DW available for reads while you are performing ETL.  You can additionally use the snapshot to roll back (restore) a failed ETL process even if it didn't run in a single transaction.


localtimestamp or curent_timestamp for default value

You write "I don't need the time zone information", however datatype TIMESTAMP WITH LOCAL TIME ZONE has time zone information stored.

For a TIMESTAMP WITH LOCAL TIME ZONE column I don't see any difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP either of them should work in the same way.

CURRENT_TIMESTAMP returns current data and time as of datatype TIMESTAMP WITH TIME ZONE, whereas LOCALTIMESTAMP returns a LOCALTIMESTAMP value. Both of them have to be implicitly converted to a TIMESTAMP WITH LOCAL TIME ZONE datatype.


Transaction over linked server causing time out

It seems Windows Firewall blocks DTC packages. To allow it to communicate through Windows Firewall, go to Control Panel > Windows Firewall > Allowed Apps and from list of apps, check Distributed Transaction Coordinator for both Private and Public networks.

If it doesn't works for you check out these solutions:

Unable to begin a distributed transaction
No transaction is active message when accessing Linked Server


Notify when database is accessed in any manner

To clarify, it's actually one particular table I'd like to monitor

You should use SQL Database Audit.

Below is an example that you are looking for (based on your comments)

(Modify as per your criteria and needs)

CREATE DATABASE AUDIT SPECIFICATION [AW_Audit_HR_Employee_DML]

FOR SERVER AUDIT [Server_Audit_AW]

ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON HumanResources.Employee BY sys)

WITH (STATE =ON)

More info at : SQL 2012 Security Audit 2 (Database Audit)


MySQL generic trigger to save identification data for later accessing of the changed row

I've used this setup before, controlled by triggers.  It works for any table with a primary key, and can be queried easily.

CREATE TABLE `auditLog` (
  `db_user` varchar(255) default NULL,
  `table_name` varchar(255) default NULL,
  `row_id` int(11) default NULL,
  `field_name` varchar(255) default NULL,
  `old_value` blob,
  `new_value` blob,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP
);


Parallel updates on header and line item tables in SSIS

Less of a race condition and more of an issue with order of operations and dependencies.
The way you stated your question, in steps, is how you want to think of the SSIS package.  First it has to do this, then it has to do that.
Since the line items are dependent on the latest information from the header, that should always come second.  Letting them go simultaneously, you don't really know what happens first.


Due to the "availability-groups" tag and the other terms you have used, I am assuming you are referring to a database in an availability group.

In short, the answer is no, you cannot do this.  The secondary database is read-only since it is a replica of the database on the primary.  If you removed the target replica from the AG, made the modification, and then tried to add it back to the AG, replication from the primary would no longer work.  The solution you are suggesting is 110% not possible with availability groups.

Since you can't modify the schema of the secondary replica, you'll have to start with a completely separate database for the reporting and keep it in sync with replication or an ETL process.  A lot more information would be required for anyone to be able to determine what would be the best solution.


This is easy.

let @d be the date and @k be the kind :

select *
from table a
where
  startdate = (select max(b.startdate)
               from table b
               where b.startdate < @d
               and b.kind = @k)
  and a.kind = @k