Friday, July 29, 2016

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.

Friday, July 22, 2016

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.