SQL Server High Availability & Disaster Recovery: A Complete Guide

This comprehensive guide covers everything you need to know about SQL Server High Availability (HA) and Disaster Recovery (DR) solutions. Learn how to ensure your databases remain accessible and protected against failures.

Conceptual Understandings

[High Availability in SQL Server]

Target: All SQL Server Instances need to be kept 100% working and 24x7 running.

Utilizing: 'Fault Tolerant' components and also needless other components.

1. Replication

Publisher SQL Server Instance (Pub) copies the databases to Subscriber SQL Server Instance (Sub) scheduled with SQL Server Agents with job processes.

Distributor SQL Server Instance (Dis) duplicates Sub with normal sync operations.

In a Server to Server interaction, anyone can be Sub or Pub depending on who is on receiving end and who is on sending end respectively at any point of time.

Types or Varieties of Replication:

• 1.1 Snapshot Replication
• 1.2 Transactional Replication
• 1.3 Merge Replication
• 1.4 Peer to Peer (P2P) Replication

1.1 Snapshot Replication

Pub provides database snapshots to the Sub.

In cases of multiple servers connected sequentially as given below.

At any time,

***S1 is a Pub for S2 and S2 is Sub for S1

S2 is a Pub for S3 and S3 is Sub for S2

S3 is a Pub for S4 and S4 is Sub for S3

'→' Indicates a Snapshot Replication is being carried out over the source (left of '→') to target (right of '→')

The main benefit of the first interaction in this (S1 → S2) is that the snapshots replicated here becomes a source of lookup for minimal changes in further same snapshot replications ahead in sequence.

1.2 Transactional Replication

Since we are bound to have some Transactions on SQL Servers at any moment of time, such transactions on Pub end need to be updated again on Sub end or 'Dis' end. For this Transaction Replications are carried out by transactional processes (SSA Jobs).

1.3 Merge Replication

Moving further in time (i.e. after snapshot replication and transactional replication has just finished), at that instant, both System (Pub and Sub) are shadowed OFFLINE, and made ONLINE immediately with no other delay. And carry out a Synchronization Process between them.

1.4 Peer to Peer (P2P) Replication

In a Peer to Peer replication, we need to consider Sequential interaction from above:

The transaction that has been carried out on S2 from S1 need also be carried out over the S3 and S4 servers. This process if happens then, as soon S2 gets the transactional instructions same would be passed on to S3 and S4.

Hence Peer to Peer Replication is carried out.

REPLICATION AGENTS:

• Replication Agents are standalone programs which generate snapshots, reads transaction logs, distributes data to Sub and reconciles changes.
• It can perform one-way (transactional, snapshot), bidirectional (transactional), and multi-master setups (merge and peer-to-peer).

2. Log Shipping

Databases in the Secondary server are restored with no recovery using SSA and job schedules at a definite interval from Databases in Primary. Supports multiple secondary servers.

Used in the smallest amounts of time for HA because when fail-over happens, the secondary database needs to be updated manually with un-restored log backups.

3. Mirroring

Databases in the Mirror server are restored with no recovery from the databases in principal server. Once this has completed, transactions are copied to the Mirror server. Fail-over gets finished in seconds. If the Principal server is down, the Mirror server acts as Principal server.

4. Clustering

Clusters involve a minimum of 2 servers.

If one is down, others must take up the responsibility for same. This is no brainer for architectures with near 100% up-time. Assume a Server fails, then where the task on it was unable to complete, the same instructions need to be passed on to other servers in the same cluster, the task need not be necessarily restarted.

There are 2 scenarios for setting up a 2 server cluster:

1. Server 1 is Active and Server 2 is Active.
   Either one of them fails then the other shall failover.
2. Server 1 is Active and Server 2 is Passive
   Active fails then Passive one become immediately Active and start working. Remember, in Passive Node we just need sql server services to start running and be in sync with data alike previously Active Node.

5. AlwaysON Availability Groups

AlwaysON AG is a feature of MS SQL server 2012 and versions ahead.

An Availability Group (AG) is a group of selected databases. The group is decided on the basis such that the group itself fails over together if a failure has occurred.

It consists of one Primary Replica for read-write connections and upto 8 Secondary Replicas, which is used for read only connections for reporting purposes. To make the interaction between databases easier AG Listener can be created. It is a Virtual Name that consists of a Unique DNS name, Virtual IPs, and a TCP Port number, which provides a direct connection to the replica on that AG.

Tutorial: Prepare SQL Server for Replication (Publisher, Distributor, Subscriber)

It's important to plan for security before you configure your replication topology. This tutorial shows you how to better secure a replication topology. It also shows you how to configure distribution, which is the first step in replicating data. You must complete this tutorial before any of the others.

This tutorial teaches you to prepare a server so that replication can run securely with least privileges.

In this tutorial, you learn how to:

• Create Windows accounts for replication.
• Prepare the snapshot folder.
• Configure distribution.

Prerequisites

This tutorial is for users who are familiar with fundamental database operations, but who have limited exposure to replication.

To complete this tutorial, you need SQL Server, SQL Server Management Studio (SSMS), and an AdventureWorks database:

At the publisher server (source), install:

• Any edition of SQL Server, except SQL Server Express or SQL Server Compact. These editions can't be replication publishers.
• The AdventureWorks 2025 sample database. To enhance security, the sample databases aren't installed by default.

At the subscriber server (destination), install any edition of SQL Server, except:

• At the subscriber server (destination), install any edition of SQL Server, except SQL Server Compact. SQL Server Compact can't be a subscriber in transactional replication.
• Install SQL Server Management Studio.
• Install SQL Server Developer edition.
• Download the AdventureWorks sample database. For instructions on restoring a database in SSMS, see Restore a Database Backup Using SSMS.

Estimated time to complete this tutorial: 30 minutes

Create Windows accounts for replication

In this section, you create Windows accounts to run replication agents. You create a separate Windows account on the local server for the following agents:

Create local Windows accounts for replication agents at the publisher

1. At the publisher, open Computer Management from Administrative Tools in Control Panel.
2. In System Tools, expand Local Users and Groups.
3. Right-click Users and then select New User.
4. Enter repl_snapshot in the User name box, provide the password and other relevant information, and then select Create to create the repl_snapshot account.
5. Repeat the previous step to create the repl_logreader, repl_distribution, and repl_merge accounts.
6. Select Close.

Create local Windows accounts for replication agents at the subscriber

1. At the subscriber, open Computer Management from Administrative Tools in Control Panel.
2. In System Tools, expand Local Users and Groups.
3. Right-click Users and then select New User.
4. Enter repl_distribution in the User name box, provide the password and other relevant information, and then select Create to create the repl_distribution account.
5. Repeat the previous step to create the repl_merge account.
6. Select Close.

For more information, see Replication Agents overview.

Prepare the snapshot folder

In this section, you configure the snapshot folder that's used to create and store the publication snapshot.

Create a share for the snapshot folder and assign permissions

1. In File Explorer, browse to the SQL Server data folder. The default location is C:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Data.
2. Create a new folder named repldata.
3. Right-click this folder and select Properties.
4. On the Sharing tab in the repldata Properties dialog box, select Advanced Sharing.
5. In the Advanced Sharing dialog box, select Share this Folder, and then select Permissions.

7. After your share permissions are configured correctly, select OK to close the Permissions for repldata dialog box. Select OK to close the Advanced Sharing dialog box.

Configure distribution

In this section, you configure distribution at the publisher and set the required permissions on the publication and distribution databases. If you already configured the distributor, you must disable publishing and distribution before you begin this section. Don't disable publishing and distribution if you must keep an existing replication topology, especially in production.

Configure distribution at the publisher

1. Connect to the publisher in SQL Server Management Studio, and then expand the server node.
2. Right-click the Replication folder and select Configure Distribution.

The Distribution Configuration Wizard starts.

3. On the Distributor page, select <ServerName> will act as its own Distributor; SQL Server will create a distribution database and log. Then select Next.
4. If the SQL Server Agent isn't running, on the SQL Server Agent Start page, select Yes, configure the SQL Server Agent service to start automatically. Select Next.
5. Enter the path \<Publisher_Machine_Name>\repldata in the Snapshot folder box, and then select Next. This path should match what you saw previously under Network Path for your repldata properties folder after configuring your share properties.
6. Accept the default values on the remaining pages of the wizard.
7. Select Finish to enable distribution.

Set database permissions

8. In SQL Server Management Studio, expand Security, right-click Logins, and then select New Login.
9. On the General page, select Search. Enter <Publisher_Machine_Name>\repl_snapshot in the Enter the object name to select box, select Check Names, and then select OK.

10. On the User Mapping page, select the distribution database and select db_owner for the Database role membership for: distribution list.
11. On the Securables page, select Add. In the Add Objects window, select All objects of the types. Select OK. In the Select Objects page, select Databases. Select OK. In the Securables page, select distribution. In the Explicit permissions list, select Grant for the View database state permission. Select OK.
12. Repeat steps 8 through 11 to create a login for repl_logreader, repl_distribution, and repl_merge.

Verify that the following permissions are allowed:

8. In the repldata Properties dialog box, select the Security tab and select Edit.
9. In the Permissions for repldata dialog box, select Add. In the Select Users, Computers, Service Accounts, or Groups box, type the name of the Snapshot Agent account that you created previously, as <Publisher_Machine_Name>\repl_snapshot. Select Check Names, and then select OK.
10. Repeat the previous step to add permissions for the Distribution Agent as <Publisher_Machine_Name>\repl_distribution, and for the Merge Agent as <Publisher_Machine_Name>\repl_merge.

12. Select the Sharing tab again and note the Network Path for the share. You need this path later when you're configuring your snapshot folder.
13. Select OK to close the repldata Properties dialog box.

For more information, see Secure the Snapshot Folder.

Tutorial: Configure replication between two fully connected servers (transactional)

Transactional replication is a good solution to the problem of moving data between continuously connected servers. By using the Replication Wizard, you can easily configure and administer a replication topology.

This tutorial shows you how to configure a transactional replication topology for continuously connected servers. For more information about how transactional replication works, see the overview of transactional replication.

What you will learn

This tutorial teaches you to publish data from one database to another by using transactional replication.

In this tutorial, you will learn how to:

• Create a publisher via transactional replication.
• Create a subscriber for the transactional publisher.
• Validate the subscription and measure latency.

Prerequisites

This tutorial is for users who are familiar with basic database operations, but who have limited experience with replication. Before you start this tutorial, you must complete Tutorial: Prepare SQL Server for replication.

To complete this tutorial, you need SQL Server, SQL Server Management Studio (SSMS), and an AdventureWorks database:

At the publisher server (source), install:

• Any edition of SQL Server, except SQL Server Express or SQL Server Compact. These editions can't be replication publishers.
• The AdventureWorks 2025 sample database. To enhance security, the sample databases are not installed by default.

At the subscriber server (destination):

• Install any edition of SQL Server, except SQL Server Compact. SQL Server Compact cannot be a subscriber in transactional replication.
• Install SQL Server Management Studio.
• Install SQL Server 2017 Developer edition.

Estimated time to complete this tutorial: 60 minutes

Configure the publisher for transactional replication

In this section, you create a transactional publication by using SQL Server Management Studio to publish a filtered subset of the Product table in the AdventureWorks 2025 sample database. You also add the SQL Server login used by the Distribution Agent to the publication access list (PAL).

Create a publication and define articles

1. Connect to the publisher in SQL Server Management Studio, and then expand the server node.
2. Right-click SQL Server Agent and select Start. The SQL Server Agent should be running before you create the publication. If this step does not start your agent, you'll need to do so manually from SQL Server Configuration Manager.
3. Expand the Replication folder, right-click the Local Publications folder, and select New Publication. This step starts the New Publication Wizard.
4. On the Publication Database page, select AdventureWorks2025, and then select Next.

Set database permissions

1. In SQL Server Management Studio, expand Security, right-click Logins, and then select New Login.
2. On the General page, select Search. Enter <Publisher_Machine_Name>\repl_snapshot in the Enter the object name to select box, select Check Names, and then select OK.
3. On the User Mapping page, in the Users mapped to this login list, select both the distribution and AdventureWorks2025 databases.

In the database role membership list, select the db_owner role for the login for both databases.

4. Select OK to create the login.
5. Repeat steps 1-4 to create a login for the other local accounts (repl_distribution, repl_logreader, and repl_merge). These logins must also be mapped to users who are members of the db_owner fixed database role in the distribution and AdventureWorks databases.

For more information, see Configure distribution and Replication Agent security model.

Next step

You have successfully configured your publisher for transactional replication. Next, you'll create the publication and configure articles.

5. On the Publication Type page, select Transactional publication, and then select Next.
6. On the Articles page, expand the Tables node and select the Product check box. Then expand Product and clear the check boxes next to ListPrice and StandardCost. Select Next.
7. On the Filter Table Rows page, select Add.
8. In the Add Filter dialog box, select the SafetyStockLevel column. Select the right arrow to add the column to the filter statement WHERE clause of the filter query. Then manually type in the WHERE clause modifier as follows:

WHERE [SafetyStockLevel] < 500

9. Select OK, and then select Next.
10. Select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions check box, and select Next.

11. On the Agent Security page, clear the Use the security settings from the Snapshot Agent check box.

Select Security Settings for the Snapshot Agent. Enter <Publisher_Machine_Name>\repl_snapshot in the Process account box, supply the password for this account, and then select OK.

12. Repeat the previous step to set <Publisher_Machine_Name>\repl_logreader as the process account for the Log Reader Agent. Then select OK.
13. On the Complete the Wizard page, type AdvWorksProductTrans in the Publication name box, and select Finish.

14. After the publication is created, select Close to complete the wizard.

View the status of snapshot generation

1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click AdvWorksProductTrans, and then select View Snapshot Agent Status.
3. The current status of the Snapshot Agent job for the publication appears. Verify that the snapshot job has succeeded before you continue to the next section.

If your SQL Server Agent was not running when you created the publication, you'll see that the Snapshot Agent was never run when you check the Snapshot Agent status for your publication. If that's the case, select Start to start your Snapshot Agent.

If you see an error here, see Troubleshooting Snapshot Agent error.

Add the Distribution Agent login to the PAL

1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click AdvWorksProductTrans, and then select Properties. The Publication Properties dialog box appears.
   • a. Select the Publication Access List page, and select Add.
   • b. In the Add Publication Access dialog box, select <Publisher_Machine_Name>\repl_distribution, and select OK.

For more information, see Replication programming concepts.

Create a subscription to the transactional publication

In this section, you add a subscriber to the publication that you previously created. This tutorial uses a remote subscriber (NODE2\SQL2016), but you can also add a subscription locally to the publisher.

Create the subscription

1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click the AdvWorksProductTrans publication, and then select New Subscriptions. The New Subscription Wizard starts.
3. On the Publication page, select AdvWorksProductTrans, and then select Next.

4. On the Distribution Agent Location page, select Run all agents at the Distributor, NODE1\SQL2016 (push subscriptions), and then select Next. For more information on pull and push subscriptions, see Subscribe to publications.

5. On the Subscribers page, if the name of the subscriber instance is not displayed, select Add Subscriber, and then select Add SQL Server Subscriber from the drop-down list. This step opens the Connect to Server dialog box. Enter the subscriber instance name and then select Connect.

After the subscriber has been added, select the check box next to the instance name of your subscriber. Then select New Database under Subscription Database.

6. The New Database dialog box appears. Enter ProductReplica in the Database name box, select OK, and then select Next.

7. On the Distribution Agent Security page, select the ellipsis (...) button. Enter <Publisher_Machine_Name>\repl_distribution in the Process account box, enter the password for this account, select OK, and then select Next.
8. Select Finish to accept the default values on the remaining pages and complete the wizard.

Set database permissions at the subscriber

1. Connect to the subscriber in SQL Server Management Studio. Expand Security, right-click Logins, and then select New Login.
   • a. On the General page, under Login Name, select Search and add the login for <Subscriber_Machine_Name>\repl_distribution.
   • b. On the User Mappings page, grant the login db_owner membership for the ProductReplica database.

2. Select OK to close the New Login dialog box.

View the synchronization status of the subscription

1. Connect to the publisher in SQL Server Management Studio. Expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, expand the AdvWorksProductTrans publication, right-click the subscription in the ProductReplica database, and then select View Synchronization Status. The current synchronization status of the subscription appears.
3. If the subscription is not visible under AdvWorksProductTrans, select the F5 key to refresh the list.

For more information, see:

Measure replication latency

In this section, you use tracer tokens to verify that changes are being replicated to the subscriber and to determine latency. Latency is the time it takes for a change made at the publisher to appear to the subscriber.

1. Connect to the publisher in SQL Server Management Studio. Expand the server node, right-click the Replication folder, and then select Launch Replication Monitor.
2. Expand a publisher group in the left pane, expand the publisher instance, and then select the AdvWorksProductTrans publication.
   • a. Select the Tracer Tokens tab.
   • b. Select Insert Tracer.
   • c. View elapsed time for the tracer token in the following columns: Publisher to Distributor, Distributor to Subscriber, Total Latency. A value of Pending indicates that the token has not reached a specified point.

Next Steps

Now that you have successfully configured transactional replication, you can:

• Monitor replication performance using Replication Monitor
• Test failover scenarios
• Configure additional subscribers
• Implement security best practices
• Optimize replication performance

Conclusion

This comprehensive guide has covered the essential aspects of SQL Server High Availability and Disaster Recovery solutions. From basic concepts to advanced implementation techniques, you now have the knowledge to:

• Understand HA & DR concepts - The fundamental differences and use cases
• Implement Replication - Snapshot, Transactional, Merge, and Peer-to-Peer
• Configure Log Shipping - For disaster recovery scenarios
• Set up Mirroring - For high availability with automatic failover
• Deploy Clustering - For hardware-level fault tolerance
• Implement AlwaysOn Availability Groups - The most advanced HA solution

Remember that high availability and disaster recovery are not one-time implementations but ongoing processes that require regular maintenance, monitoring, and testing. Stay updated with the latest SQL Server features and best practices to ensure your databases remain highly available and protected against various failure scenarios.

For additional resources and advanced topics, refer to the official Microsoft SQL Server documentation and consider implementing a comprehensive monitoring solution to track the health and performance of your HA/DR infrastructure.

Tutorial: Configure replication between a server and mobile clients (merge)

Merge replication is a good solution to the problem of moving data between a central server and mobile clients that are only occasionally connected. By using the replication wizards, you can easily configure and administer a merge replication topology.

This tutorial shows you how to configure a replication topology for mobile clients. For more information about merge replication, see the overview of merge replication.

What you will learn

This tutorial teaches you to use merge replication to publish data from a central database to one or more mobile users so that each user gets a uniquely filtered subset of the data.

In this tutorial, you will learn how to:

• Configure a publisher for merge replication.
• Add a mobile subscriber for merge publication.
• Synchronize the subscription to the merge publication.

Prerequisites

This tutorial is for users who are familiar with fundamental database operations, but who have limited experience with replication. Before you start this tutorial, you must complete Tutorial: Prepare SQL Server for replication.

To complete this tutorial, you need SQL Server, SQL Server Management Studio (SSMS), and an AdventureWorks database:

At the publisher server (source), install:

• Any edition of SQL Server, except for SQL Server Express or SQL Server Compact. These editions cannot be a replication publisher.
• The AdventureWorks 2025 sample database. To enhance security, the sample databases are not installed by default.

At the subscriber server (destination):

• Install any edition of SQL Server, except SQL Server Express or SQL Server Compact. The publication that's created in this tutorial does not support either SQL Server Express or SQL Server Compact.
• Install SQL Server Management Studio.
• Install SQL Server 2017 Developer edition.
• Download the AdventureWorks sample database. For instructions on restoring a database in SSMS, see Restoring a database.

Estimated time to complete this tutorial: 60 minutes

Configure a publisher for merge replication

In this section, you create a merge publication by using SQL Server Management Studio to publish a subset of the Employee, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks 2025 sample database. These tables are filtered with parameterized row filters so that each subscription contains a unique partition of the data. You also add the SQL Server login used by the Merge Agent to the publication access list (PAL).

Create merge publication and define articles

1. Connect to the publisher in SQL Server Management Studio, and then expand the server node.
2. Start the SQL Server Agent by right-clicking it in Object Explorer and selecting Start. If this step doesn't start the agent, you'll need to manually do so from SQL Server Configuration Manager.
3. Expand the Replication folder, right-click Local Publications, and select New Publication. The New Publication Wizard starts.
4. On the Publication Database page, select AdventureWorks2025, and then select Next.
5. On the Publication Type page, select Merge publication, and then select Next.

For more information, see:

6. On the Subscriber Types page, ensure that only SQL Server 2008 (10.0.x) or later is selected, and then select Next.

10. On the Filter Table Rows page, select Employee (Human Resources), select Add, and then select Add Join to Extend the Selected Filter.
    • a. In the Add Join dialog box, select Sales.SalesOrderHeader under Joined table. Select Write the join statement manually, and complete the join statement as follows:

SQL
ON [Employee].[BusinessEntityID] = [SalesOrderHeader].[SalesPersonID]

• b. In Specify join options, select Unique key, and then select OK.

11. On the Filter Table Rows page, select SalesOrderHeader, select Add, and then select Add Join to Extend the Selected Filter.
    • a. In the Add Join dialog box, select Sales.SalesOrderDetail under Joined table.
    • b. Select Use the Builder to create the statement.
    • c. In the Preview box, confirm that the join statement is as follows:

SQL
ON [SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID]

• d. In Specify join options, select Unique key, and then select OK. Select Next.

12. Select Create a snapshot immediately, clear Schedule the snapshot agent to run at the following times, and select Next.
13. On the Agent Security page, select Security Settings. Enter <Publisher_Machine_Name>\repl_snapshot in the Process account box, supply the password for this account, and then select OK. Select Next.
14. On the Complete the Wizard page, enter AdvWorksSalesOrdersMerge in the Publication name box and select Finish.
15. After the publication is created, select Close. Under the Replication node in Object Explorer, right-click Local Publications and select Refresh to view your new merge replication.

2. In the Local Publications folder, right-click AdvWorksSalesOrdersMerge, and then select View Snapshot Agent Status.
3. The current status of the Snapshot Agent job for the publication appears. Ensure that the snapshot job has succeeded before you continue to the next lesson.

Add the Merge Agent login to the PAL

1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click AdvWorksSalesOrdersMerge, and then select Properties.
   • a. Select the Publication Access List page, and select Add.
   • b. In the Add Publication Access dialog box, select <Publisher_Machine_Name>\repl_merge and select OK. Select OK again.

For more information, see:

Create a subscription to the merge publication

In this section, you add a subscription to the merge publication that you created previously. This tutorial uses the remote subscriber (NODE2\SQL2016). You then set permissions on the subscription database and manually generate the filtered data snapshot for the new subscription.

Add a subscriber for merge publication

1. Connect to the subscriber in SQL Server Management Studio, and expand the server node. Expand the Replication folder, right-click the Local Subscriptions folder, and then select New Subscriptions. The New Subscription Wizard starts.
2. On the Publication page, select Find SQL Server Publisher in the Publisher list.

In the Connect to Server dialog box, enter the name of the publisher instance in the Server name box, and select Connect.

3. Select AdvWorksSalesOrdersMerge, and select Next.
4. On the Merge Agent Location page, select Run each agent at its Subscriber, and then select Next.
5. On the Subscribers page, select the instance name of the subscriber server. Under Subscription Database, select New Database from the list.

In the New Database dialog box, enter SalesOrdersReplica in the Database name box. Select OK, and then select Next.

6. On the Merge Agent Security page, select the ellipsis (...) button. Enter <Subscriber_Machine_Name>\repl_merge in the Process account box, and supply the password for this account. Select OK, select Next, and then select Next again.
7. On the Synchronization Schedule page, set Agent Schedule to Run on demand only. Select Next.

8. On the Initialize Subscriptions page, select At first synchronization from the Initialize When list. Select Next to proceed to the Subscription Type page, and select the appropriate subscription type. This tutorial uses Client. After you select the subscription type, select Next again.
9. On the HOST_NAME Values page, enter a value of adventure-works\pamela0 in the HOST_NAME Value box. Then select Finish.
10. Select Finish again. After the subscription is created, select Close.

Set server permissions at the subscriber

1. Connect to the subscriber in SQL Server Management Studio. Expand Security, right-click Logins, and then select New Login.

On the General page, select Search and then enter <Subscriber_Machine_Name>\repl_merge in the Enter the Object Name box. Select Check Names, and then select OK.

2. On the User Mapping page, select the SalesOrdersReplica database and select the db_owner role. On the Securables page, grant the Explicit permission to Alter Trace. Select OK.

Final Steps

You have successfully configured merge replication between a server and mobile clients. The subscription is now ready for synchronization, and the filtered data will be replicated based on the HOST_NAME value specified for each subscriber.

Start synchronization and initialize the subscription

1. Connect to the subscriber in SQL Server Management Studio.
2. Make sure that the SQL Server Agent is running. If it's not, right-click the SQL Server Agent in Object Explorer and select Start. If this step fails to start your agent, you'll need to do so manually by using SQL Server Configuration Manager.
3. Expand the Replication node. In the Local Subscriptions folder, right-click the subscription in the SalesOrdersReplica database, and then select View Synchronization Status.

Select Start to initialize the subscription.

In this section, you start the Merge Agent to initialize the subscription by using SQL Server Management Studio. You also use this procedure to synchronize with the publisher.

Create the filtered data snapshot for the subscription

1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click the AdvWorksSalesOrdersMerge publication, and then select Properties.
   • a. Select the Data Partitions page, and select Add.
   • b. In the Add Data Partition dialog box, enter adventure-works\pamela0 in the HOST_NAME Value box, and then select OK.
   • c. Select the newly added partition, select Generate the selected snapshots now, and then select OK.

For more information, see:

Synchronize the subscription to the merge publication

Final Conclusion

You have successfully completed both transactional and merge replication tutorials. These comprehensive guides provide you with the knowledge to implement robust SQL Server High Availability and Disaster Recovery solutions for your enterprise applications.