This is part 5 of a 5-part series.
- Part 1 – Planning
- Part 2 – The Distributor
- Part 3 – The Publisher
- Part 4 – The Subscriber
- Part 5 – Testing
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.
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
