Sql-server – transactional replication-snapshot agent failure … can anyone help me out

What is the best practice in order to have this scenario done?

It depends on how much data you are replicating i.e.

• Are you replicating selected columns/tables, entire database (all tables), etc ?
• Are both nodes located in the same domain or different domains ?
• Are you replicating in same region or cross region (US-UK, etc) ?

I have implemented T-Rep where I have used same server as publisher and distributor as the data that was needed to replicate was less and also, have implemented separate distribution database on separate server that does all the heavy lifting of publishing the data to subscribers where we had massive data to push down to subscribers.

You have to consider factors like -

• time taken to perform snapshot and applying that snapshot to subscribers
• Feasibility of re-initializing the articles when a major (e.g. schema) change occurs to the tables that are involved in replication.

should I create 2 distributors? You can use the same distribution database. Though, for ease of maintenance and better performance [reducing contention - both writing to and reading from the distribution database] I would highly recommend you use separate Distribution databases.

Remember that distribution database is the heart of replication. So it requires proper maintenance, backups, etc. Now if you have just 1 distribution database that supports multiple publishers and a DISASTER happened, then restoring it from a previous backup will impact ALL publishers.

From BOL :

In many cases, a single distribution database is sufficient. However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct.

Lastly some good references that will help you :

Deep Dive on Initialize from Backup for Transactional Replication

Replicating Non-Clustered Indexes Improves Subscriber Query Performance

Follow the Data in Transactional Replication - Whitepaper

Troubleshooting Transactional Replication

Scaling Out the Distribution Database

Distribution database should not reside on the servers that are part of AlwaysON availability group that the publishing database is (or will become) a member of.

Replication configuration is coupled to the SQL Server instance where the Distributor is configured; therefore the distribution database cannot be mirrored or replicated.

If you want to provide HA for distribution database, then you have to go for SQL Server Failover cluster. Thats the only option.

Your scenario is as below :

So if you loose server C, the only option to get distribution running on server D is to do a RESTORE.. with KEEP_REPLICATION from a good backup. You can use this script to restore your distribution database (with some changes as per your environment). I would go for a clean install of replication !

Make sure you script out your replication topology whenever your do any changes. You should have handy scripts of both drop and create, so in a disaster situation, you have scripts that will help easily create replication.

Also, since you are using always-ON with T-Rep, I would suggest you to enable TF 1448.

Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled,, the log reader always waits for the synchronous secondary replicas. The log reader will not go beyond the min ack of the synchronous secondary replicas. This trace flag applies to the instance of SQL Server, not just to an availability group, an availability database, or a log reader instance. This trace flag takes effect immediately without a restart. It can be activated ahead of time or when an asynchronous secondary replica fails.

Reference : Configure Replication for AlwaysOn Availability Groups