Sql-server – SQL Server 2016: Bidirectional Transactional Replication using Backup, Do I use/need Snapshot

sql serversql-server-2016transactional-replication

Overview:

I have been setting up bidirectional transactional replication between 2 SQL Servers. We have another pair of servers, running Inductive Automation's Ignition Gateway server creating and sending SQL data to Server 1 as the primary data source, and Server 2 as a backup data source. There are 3 databases that I need to keep synchronized at all times on each of the servers, so if a failover occurs the backup has the same data the primary has.

I have been setting up and testing this configuration for a long time, and testing the failover functionality of the Gateway software. All seems to be working very well, and the databases seem to be synchronizing.

The instructions I used had me setup network shares for the snapshots on each server, but since I used a backup of the 3 databases, it doesn't appear to ever use the Snapshots.

One of these synchronized databases is over 600GB of data. The deployment took 3 steps over a weekend.

Implementation:

Step 1: was starting the SQL Server Maintenance Plan of the primary server, consolidating\backing up all databases. This took over 6 hours to complete.

Step 2: was copying the files to the Backup Server, and restoring the databases to the Backup server, again taking many hours to finish.

At this point all 3 databases on Server 1 and Server 2 are identical.

Step 3: I ran my scripts that: activate Distribution on each server, Publish the 3 databases on each server, and subscribe each server to the published databases on the other server (ensuring that loopback detection was set to true for each subscription).

Questions:

  1. What are the snapshot folders for, and why were they needed to setup the synchronization? I suspect something is either not working, or the part of the instructions that had me setup the network shares "\Server1\snapshots" and "\Server2\snapshots", was completely erroneous.

  2. What is the Snapshot Agent supposed to be doing for the type of synchronization we have here?

  3. I am seeing options in SQL Server Management Studio under the Replication section to 'validate', 'reinitialize', and 'check subscription status' of the subscriptions. I've run each and the results don't give me the expected feedback. What is the difference?

  4. In the event that the subscriptions have problems, the only maintenance procedure I feel confident about is repeating the steps we used to deploy this configuration. The length of time it takes to complete this leaves me very nervous! Production runs: Mon-Wed 24 hours, Thurs-Sat 4AM-4PM. Interruptions to production are very expensive. I'm looking for any guidance you might have about maintaining this setup.

  5. Day 1 after implementation, I spotted that the Snapshot Agents had never started in the Replication Monitor, so I started the Agents manually. When I did, both servers became unresponsive and had to be rebooted. Now the Log Reader agents all look happy, performance is all excellent. However, the Snapshot agents all have errors "Not completed", and I cannot seem to reset them or clear them in the Replication Monitor. How do I fix this?

Best Answer

Snapshot are not required if subscribers are setup using backup\restore or another technique to preload the subscriber data.

Related Question