Setting up Scalearc to monitor replication of a AlwaysON cluster with a stanalone SQL server in Transactional replication

Release Classification Level DB Platform Category
3.2 and Later How-to
TUNE
SKILLED
 

MSSQL

Administration

 

QUESTION

 

How to setup Replication Monitoring on a Scalearc Cluster which is built with an AlwaysON Listener serving as a primary (R/W) node

and a non-AlwaysON SQL server as a secondary (R) node.

 

ANSWER

 

The steps described below can be followed to achieve this:

 

1. In Scalearc set the listener ip as the "R+W" node (primary) and the non-AlwaysON transactional node as the secondary. If you need further information on

setting up an AlwaysON primary replica with a non-alwaysON secondary replica in transactional replication then please refer link below:

 

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-database-that-is-part-of-an-alwayson-availability-group/

 

2. Once both nodes are setup in Scalearc you can see as below:

 

 

 

Node1 - 192.168.11.69 - is the AlwaysON listener ip address (which points to the Primary replica of AlwaysON)

Node2 - 192.168.11.74 - is the non-AlwaysON DB which has a transactional replication sync with Node1.

 

3. We can now set Replication monitoring between Node1 and Node2. A database eg. "rep_test" is created in both of these SQL nodes to enable the monitoring. As seen

below you need to specify that database name in the Replication monitoring setup on Scalearc (after you create the empty database on the SQL server from SSMS).

 

 

 

4. Now press the Download option on the same page - which will save a script named replication_database.sql.

 

 

5. Run the contents of this script with command "USE rep_test" as the first command (add this command at top of the pasted contents) and run the script on both node1 and node2.

6. This step is the final wherein you will enable rep_test.dbf as the "Availabiity Database" in the AlwaysON Availability group. This ensures that secondary replica has updated contents

for the replicaton Database.

Once above steps are executed then you can observe that  Node2 of the transactional setup in Scalearc turns "Yellow" from "Green" meaning that a Lag is being monitored

on the Second DB (node2) with respect to the AON primary replica. Any amount of a very small lag will also be monitored in this case.

 

 

 

Comments

0 comments

Please sign in to leave a comment.