Verify Active Geo Replication

In the last couple of blogs we learned the basic intro about the Active Geo Replication and also learned step-by-step how to configure Active Geo Replication using Azure Portal. In this post we will cross verify Active Geo Replication whether it is working or not & if the secondary database is read only or in read write mode.

Steps to verify Active Geo Replication

The first thing we want to verify is whether the database is created in the secondary region or not, to verify this we connected to the target server which was selected to host one of our secondary database. In this case it was SQLChampDR and the moment we connected to the target server it was evident that the database was created on the other end.

The next thing to verify was whether the data changes are getting replicated to secondary servers or not and if the replication is happening what is the delay (lag)

To verify we selected one of the table in primary database and updated one of the records. Please pay close attention we just updated the DOB column of one of the record to today’s date & we were connected to primary server/database.

Now next thing we did was connected to secondary database and verify the DOB of the same record. We observed no lag in between – it could be because the data change was quite small and there were only 3 records but the bottom line is the data changes were replicated instantaneously.

Please pay close attention to the bottom of the query window which clearly shows that we were connected to the target server.

Now the last thing on our list was to verify if the secondary database is read only, because SSMS doesn’t show any such thing in object explorer.

So we tried changing the DOB of the same record once again on the secondary database & the query resulted in an error & the error message was quite self-explanatory à “Msg 3906, Level 16, State 2, Line 3 Failed to update database “DDM_Sarab” because the database is read-only.”

However, I am not that convinced with the error message the message could have given more description about the Geo-replication.

After getting this error message the first place where anyone would check is sys.databases but there is no such entry about the replication and is_read_only also shows false. Obviously we are talking about those users who have direct access to query your databases and are not aware about the Active Geo Replication.

Hope you got answer to your questions! Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp
Follow @Sarab_SQLGeek