This article will review on how to configure SQL Server Replication along with mirroring on a database.
SQL Server Replication is used to transfer data and objects from one database to another database. There are three main types of replication SQL server supports.
- Snapshot Replication
- Merge Replication
- Transactional Replication
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. The snapshot agent generates the snapshot and place snapshot files in snapshot folder. The distribution agent applies the snapshot in subscriber.
In SQL Server Replication (transactional), the initial snapshot is generated by snapshot agent and stores the snapshot files in snapshot folder and the distribution agent applies the snapshot on subscribers. The log reader agent monitors the transaction log for all incremental changes and copies the transactions to distribution database and these transactions applied on subscribers by distribution agent.
In merge replication the snapshot agent generates the snapshot and places snapshot files in snapshot folder. These files include the schema of the objects, bulk copy files, triggers, constraints and stored procedures and the initial snapshot is applied on subscribers by merge agent. The incremental changes on both publisher and subscriber were tracked by merge triggers created on each table added to merge replication and these changes are downloaded/uploaded by merge agent.
Below are servers I used while configuring replication on mirroring.
- Principal Server: SQL01V
- Mirror Server: SQL02V
- Distributor: SQLDIST01V
- Subscriber: SQLSUB01V
Below are the steps for configuring replication along with mirroring on a database. In this setup we are configuring mirroring on publisher database.
- Setup a remote distributor
- Enable principal server and mirror server to publish using same remote distributor
- Configure publication and add subscriber
- Configure mirroring
- Change SQL Server Replication agent parameters
The distribution database should be configured on a remote SQL server instance and should be available for both principal server and mirror server.
On the distributor “SQLDIST01V”, Login to SQL Server instance using SQL Server management studio.
Navigate to the replication folder, right click and click on Configure Distribution. The configure distribution wizard opens. Click Next -> select the first option “SQLDIST01V” will act as its own distributor; SQL Server will create a distribution database and log (Here the server name changes as per your distribution server) and click Next -> Input the snapshot folder (give a shared path which will be available for both principal server and mirror server) -> Click Next -> Input the distribution database name (default is distribution) and the locations to save the data file and the log file ->
Click Next and add publishers. Here we need to add both principal server and mirror server so that both can publish using the same distribution. At the end enable configure distribution, click Next and then Finish.
The remote distribution setup is completed, and we added both principal server and mirror server as publishers at distributor.
Enable Principal and Mirror to Publish Using Same Remote Distributor
Login to principal server “SQL01V” and navigate to replication folder. Right click and select Configure Distribution.
The configure distribution wizard opens. Click Next -> select second option Use the following server a Distributor -> Click on Add -> connect to the remote distributor
Click Next -> enter the administrative link password of distributor.
Click Next -> check configure distribution and click Next and then Finish
Login to the mirror server “SQL02V” and follow the same steps to add the remote distributor “SQLDIST01V”.
Configuring Publication and Add Subscriber
Login to principal server “SQL01V”, configure publication add articles to publication and generate the snapshot. Add the subscriber “SQLSUB01V” and apply the snapshot.
- Note: For step by step to configure SQL Server Replication (merge) and adding subscribers please refer to any of the below articles.
SQL Server Replication (Merge) – What gets replicated and what doesn’t
Foreign key issues while applying a snapshot in SQL Server merge replication
For step by step to configure transactional replication please refer to the below article.
SQL Server replication: Configuring Snapshot and Transactional Replication
Login to principal server and configure mirroring on publisher database between principal server “SQL01V” and mirror server “SQL02V”.
- Note: Please refer to the below article for configuring mirroring.
How to create a Database Mirroring
Change the SQL Server Replication Agent Parameters
Login to your principal database instance. Navigate to the Replication folder and the publication we created. Right click on the publication and click on Launch Replication Monitor.
In case of merge replication. Launch replication monitor, navigate to the publication and click on All Subscribers tab. if you have multiple subscribers select any one of the merge agents of the subscriber -> right click and click on Agent Profile. If the existing agent profile is default Click on New and clone new profile from default profile. Give the profile name and description of your choice and uncheck show only parameters used in this profile. Enter your mirror server name in the parameter called “-PublisherFailoverPartner” and click Ok.
Now use the profile you created for the agent. The agent uses parameters in the newly selected profile from next run. Stop and start your agents.
In case of transactional replication, launch replication monitor, navigate to the publication and click on Agents tab. select the log reader agent, right click and click on Agent Profile. If the existing agent profile is default Click on New and clone new profile from default profile. Give the profile name and description of your choice and uncheck show only parameters used in this profile. Enter your mirror server name in the parameter called “-PublisherFailoverPartner” and click Ok.
We can also create new agent profile from distributor. Login to the distribution server Navigate to Replication -> Local Publications -> Right click and click on Distributor properties -> Click on Profile Defaults -> select the type of agent and click New -> Clone from default profile, give the name and description of agent, change the agent parameters and click ok.
We need to take care while creating the agent profiles with publisher failover partner parameter when we have multiple publisher databases being mirrored.
For example, if we have two different publications and both the publisher databases are mirrored to different mirror servers. Create two different agent profiles with two different server names in “-PublisherFailoverPartner” and use the appropriate profile for each agent.
Now login to the principal server perform a failover. Do some transactions on current principal server. Check if the SQL Server Replication jobs were running with out any failure and query your subscriber database to see if the new transactions got replicated or not.
After failover from principal server to mirror server, all the publications configured will be shown in local publication folder of active server. Replication monitor can be launched from the active server in mirroring.
In case if you are using the stored procedures to administer replication at mirror database, use the actual publisher name where you configured replication i.e. the principal server. The replication meta data stored in principal server and mirror server is same. The publisher name stored in systems tables at mirror server is original publisher name.
Example: If you are adding a pull subscription to the publication after failover using stored procedures, use the actual publisher name i.e. principal server while passing the publisher name as a parameter to replication stored procedures.
Table of contents
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019