Transactional Replication - Snapshot metrics

Question

I have an application that is using Transactional Replication. We are using snapshots to initialize the subscriptions. All publishers and subscribers are using SQL Server 2008.

Due to the way our deployment works, I end up having to reinitialize the subscribers every time we do a build. This is time consuming and annoys the business, so I have to make sure that our developers are aware of the impact their changes will have on the size of the snapshots and the time it will take to apply them. I also want to measure the impact of settings changes that I make on the publications and agents (bcp threads, batch size, etc).

The metrics I want to capture are:

1. The size of the snapshot generated for each publication.
2. The time it took to generate the snapshot.
3. The time it took to apply the snapshot at the subscriber.

Is it possible to get this information by querying distribution? I realize that I could get snapshot size by looking at the filessystem after it is generated, but I would prefer to avoid that if possible.

Answer 1

The metrics I want to capture are:

• The size of the snapshot generated for each publication.
• The time it took to generate the snapshot.
• The time it took to apply the snapshot at the subscriber.

Is it possible to get this information by querying distribution?

You cannot get such metrics using T-SQL. You can check the replication status using T-SQL though.

Depending of how large/big your publication database is (for small databases in same network, you should not even look at the time/size as it will be super fast), you can follow below steps to get a ROUGH estimate (ball park figure) of the size and time of snapshot to get generated and apply at the subscriber :

Note: I put emphasis on Rough estimate as depending on what you are replicating e.g. store procedures, indexes, constraints, etc there will be additional files generated as well.

1. Select top 10 (depends on you - how many you choose) articles with highest row counts from the publication database and BCP OUT the data. This will give a better judgement for space requirement of snapshot agent. This would be resource intensive operation and hence should be scheduled during quiet activity of the server.

   e.g. bcp publisher_database.article_name out largeTable1.dat -T -c
   

   

   Note: Above will give you an estimated time taken by BCP and the size of the BCP file.

   So above point will answer you 1st and 2nd points.

2. Now to get the time it took to apply the snapshot to the subscriber, you have to run bcp in from the publisher server specifying the SUBSCRIBER_SERVER_NAME

   e.g. bcp subscriber_database.article_name in -S SUBSCRIBER_SERVER_NAME\instance_name largeTable1.dat -T -c
   

   This way you get an estimate of how long will it take to apply snapshot to subscriber.