Replicating from old versions of SQL Server to SQL Server 2016

At SQL Server Saturday I was asked about replicating from SQL Server 2014 to SQL Server 2016. The attendee told me that when they tried to replicate from SQL Server 2014 to SQL Server 2016 it “blew up.” This past week, as I have had time, I have been doing some testing in a sandbox environment. I was able to create a publication in SQL Server 2014 and replicate it to SQL Server 2016. I was also able to replicate from SQL Server 2012 to SQL Server 2016. The key is that while you create the publication on the lower version you have to go to your SQL Server 2016 subscriber to create the subscription. If you try to create the subscription from the lower version publisher you will receive this error.

Yes SQL Server 2016 should more than meet the requirements when replicating from SQL Server 2012 or SQL Server 2014. It just checks for major version differences and throws this error regardless.

There is one oddity that I haven’t experienced when replicating from SQL Server 2008 to SQL Server 2012. If you right click on the subscription in SQL Server 2016 and try to View Synchronization Status you will receive this error.

So you have to view the status from the publisher.

If you need to replicate from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2016 you are out of luck. According to MDSN “Replication is supported if each SQL Server endpoint is within two major versions of the current version of SQL Server. Consequently, SQL Server 2016 does not support replication to or from SQL Server 2008 or SQL Server 2008 R2.”

One workaround, that I tried in a sandbox environment, would be to replicate from your SQL Server 2008 or SQL Server 2008 R2 system to an intermediary SQL Server 2012 system and then from the SQL Server 2012 system to SQL Server 2016 system.

UPDATE!

After applying CU1 to the SQL Server 2016 sandbox server I am now able to View Synchronization Status from the SQL Server 2016 subscriber. You can get the cumulative update here.

Speaking at SQL Server Saturday 539

I am speaking at SQL Server Saturday 539 in Sioux Falls South Dakota tomorrow July 23, 2016. My presentation is titled "My Top Ten Replication Tricks." Here are the slides. I will do a more in depth post as I have time.

Upgrading your MasterDataServices database and cleaning the tblTransactions table

If you have been running Master Data Services (MDS) you may have noticed the tblTransactions in your MasterDataServices database growing rather large. This is a situation we recently faced. Our tblTransactions was over 100 GB and by far the largest table in the database. We were faced with the challenge of trying to clean up this table. After some research we found that starting with SQL Server 2012 SP1 CU15 there would be built in stored procedures to clean up this table and related tables while maintain referential integrity. We found that for our purposes SQL Server 2012 SP2 CU7 is the build we needed. I scheduled a maintenance window for our test environment and ran the update. Once it was done I connected to the database, navigated to the stored procedures and…nothing. Discussing the issue with Microsoft PFE Josh Gerszweski provided me the answer. With Master Data Services running the cumulative update is not the end of the process but only the beginning.

First you run the cumulative update as normal. If Master Data Services service is running on a different server you will need to run the cumulative update on that server as well.

If you check the stored procedures in the MasterDataServices database you will see that there are no new procedures for cleaning up the tblTransactions table.

The next step is to connect to the Master Data Services Configuration Manager. If you are not familiar with this tool you will have to determine where you have the Master Data Services running. If it is on the same server as the MasterDataService database you can look in C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSConfigTool.exe however if it is running on a different server you will need to look in the previously mentioned location on the application server.

Change to the Database Configuration page, click the Select Database button.

Enter the information to connect to your MasterDataServices database.

You will notice that the configuration manager detects the MasterDataServices database needs an upgrade.

Click the Upgrade Database button...but first you should backup the MasterDataServices database.

Click Next, Next, Finish to complete the upgrade. Now go back refresh the stored procedures in the MasterDataServices database. You will now see mdm.udpTransactionsCleanup available for you to clean up the tblTransactions table.

To make this work you need to know the Model_ID that you need to clean up and give it a CleanupOlderThanDate. It should be noted that this only uses a date not date time. To find your model IDs you can use this query:

SELECT ID, Name FROM mdm.tblModel ORDER BY ID;

Once you have found your model ID and decided on a CleanupOlderThanDate you are ready to run the stored procedure:

EXEC mdm.udpTransactionsCleanup
@Model_ID = [model id from query],
@CleanupOlderThanDate = [date you picked]

If your tblTransactions table is large enough you may encounter issues like filling the transaction log if you try to clean up all the records in one iteration and may need to batch the process to get down to the desired date. Once you have done that you can create a SQL Agent Job to keep the table trimmed to an acceptable size.