This is part 5 of a 5-part series.

 

Scenario

Publishers: servers SQL2014AG1 and SQL2014AG2, database AGTest

Distributor: stand-alone server, SQL2014demo

Subscriber: Azure SQL Database – server jessqldb2, database ReplicationTest

Time to test!

Congratulations, you’ve configured a remote distributor, configured all of your AG replicas as publishers, and configured your SQL Database as a subscriber! Now you want to ensure that transactions are replicating to the database, and that they continue to do so if there is a failover in the AG.

Testing that replication works

This is easiest to do in a development database, where you can add data.

Connect to the AG primary, which is the publisher – run a query against a table and note results.

Connect to the subscriber, run the same query, and verify the results are the same.

Connect to the publisher. If you have the ability, enter a new value into a table. If not, find a table this is frequently updated, and note a new value that has been entered.

Go to the subscriber. Query for the new or changed value.

Testing that on AG failover, replication continues to work

Perform a manual failover from your primary replica to a secondary replica.

Connect to the AG primary, which is the publisher – run a query against a table and note results.

Connect to the subscriber, run the same query, and verify the results are the same.

Connect to the publisher. If you have the ability, enter a new value into a table. If not, find a table this is frequently updated, and note a new value that has been entered.

Go to the subscriber. Query for the new or changed value.

Troubleshooting

Troubleshooting replication is outside the scope of this blog series. If you find that things aren’t working as expected, read the article¬†Monitoring Transactional Replication in SQL Server, and review the SQLServerCentral.com Stairway to Replication article Level 10: Troubleshooting.

Troubleshooting Availability Groups is also outside the scope of this blog series. If you need help, start with AlwaysOn Availability Groups Troubleshooting and Monitoring Guide.