A client of mine was upgrading a 2008R2 system to 2016, they previously had database mirroring as HA solution, I suggested they go with AlwaysOn instead as mirroring is a deprecated, although simple to implement, solution. In addition to mirroring they had snapshot replication running once a day replicating a number of tables to a development environment. The old solution demanded the database was primary on one specific server, they could not change the application to use the FAILOVER PARTNER option, so at patching they always had to leave the primary running on one particular server. That wouldn’t do!
So when they decided to upgrade we went with SQL Server 2016 on Windows 2016 as that was the latest and greatest at the time. We set up Server1 and Server2 which were part of a new AlwaysOn cluster and Server3 which was a DEV server that would get some tables updated via snapshot replication each night. Simple setup, I won’t go into detail about AO, that can be found everywhere. What I was interested in was how to setup snapshot replication pushing data to a dev env. Turned out it was quite easy.
First we decided on where to setup the distribution database, I chose one of the AO servers, Server1 to be exact as replication was not a part of HA or DR, just copying some data and it didn’t matter if we missed a day rebuilding a server if it had crashed miserably. So on Server1 under the Replication tab we right click and choose to create a new distribution database and we also choose to have all agent jobs running on this server.
Set Server1 as PRIMARY AO server and start the New Publisher wizard, choose snapshot replication, set a schedule and choose to run a snapshot to disk right away. You will probably, depending on your setup, get an error that it can’t write to the replication catalog, easily fixed, just give the service account rights to do so and start the snapshot agent job again. Which one is the snapshot job? Well, look at the properties of each replication agent job and choose the one that says it’s the snapshot job. If it works you will get a new catalog called unc and the job will succeed, you can always check more with the Replication Monitor program.
Still on Server1 we right click on the published replication and choose New Subscriber, choose your security setup and create the subscription. After this first replication has been pushed out we should see the changes made on Server3.
We have to change the publisher from Server1 to the AG, so still on Server1 that is still Primary we run something similar to:
USE distribution; GO EXEC sp_redirect_publisher @original_publisher = 'server1', @publisher_db = 'db_to_replicate', @redirected_publisher = 'YOUR-AG-NAME' GO
This can give an error message that the secondary is not in readable mode, not sure if this is a real problem or not as I only tested on Enterprise Edition, I set the AG to Readable Secondaries=Yes and ran it again and it worked. I’ll try on Standard later. Next we run a verification, I got an error on Linked Server saying it can’t link to itself:
Warning: A linked server that refers to the originating server is not a supported scenario. If you wish to use a four-part name to reference a local table, please use the actual server name rather than an alias.
USE distribution; GO DECLARE @redirected_publisher sysname; EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = 'server1', @publisher_db = 'db_to_replicate', @redirected_publisher = @redirected_publisher output; GO
Ok, so yes, this it it, I did some changes on the tables I wanted to replicate, ran the snapshot agent job again and it replicated nicely. Next I failed over to Server2 and again did some changes and ran the snapshot job on Server1 and voila! Everything still worked!!
One thing to remember, I only chose to replicate a subset of all the tables so after my first change and snapshot replication it didn’t replicate my changes, weird, but the change I did was to add another table, DUH, of course it didn’t replicate that, I had to go in under Properties of the Publication and choose that new table to have it replicated, ran the snapshot again and now it worked just fine 🙂