Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)
Applies to:
SQL Server
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.
Note
To replicate data securely between servers, you should implement all of the recommendations in Replication security best practices.
What you will learn
This tutorial teaches you to prepare a server so that replication can run securely with least privileges.
In this tutorial, you will 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 cannot be replication publishers.
- The
AdventureWorks2022
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.
Download the AdventureWorks sample database. For instructions on restoring a database in SSMS, see Restoring a database.
Note
- Replication is not supported on SQL Server instances that are more than two versions apart. For more information, see Supported SQL Server Versions in Replication Topology.
- In SQL Server Management Studio, you must connect to the publisher and subscriber by using a login that is a member of the sysadmin fixed server role. For more information on this role, see Server-level roles.
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:
Agent | Location | Account name |
---|---|---|
Snapshot Agent | Publisher | <machine_name>\repl_snapshot |
Log Reader Agent | Publisher | <machine_name>\repl_logreader |
Distribution Agent | Publisher and subscriber | <machine_name>\repl_distribution |
Merge Agent | Publisher and subscriber | <machine_name>\repl_merge |
Note
In the replication tutorials, the publisher and distributor share the same instance (NODE1\SQL2016) of SQL Server. The subscriber instance (NODE2\SQL2016) is remote. The publisher and subscriber might share the same instance of SQL Server, but it is not a requirement. If the publisher and subscriber share the same instance, the steps that are used to create accounts at the subscriber are not required.
Create local Windows accounts for replication agents at the publisher
At the publisher, open Computer Management from Administrative Tools in Control Panel.
In System Tools, expand Local Users and Groups.
Right-click Users and then select New User.
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:
Repeat the previous step to create the repl_logreader, repl_distribution, and repl_merge accounts:
Select Close.
Create local Windows accounts for replication agents at the subscriber
At the subscriber, open Computer Management from Administrative Tools in Control Panel.
In System Tools, expand Local Users and Groups.
Right-click Users and then select New User.
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.
Repeat the previous step to create the repl_merge account.
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
In File Explorer, browse to the SQL Server data folder. The default location is C:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Data.
Create a new folder named repldata.
Right-click this folder and select Properties.
a. On the Sharing tab in the repldata Properties dialog box, select Advanced Sharing.
b. In the Advanced Sharing dialog box, select Share this Folder, and then select Permissions.
In the Permissions for repldata dialog box, select Add. In the Select User, Computers, Service Account, 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.
Repeat step 6 to add the other two accounts that you created previously: <Publisher_Machine_Name>\repl_merge and <Publisher_Machine_Name>\repl_distribution.
After you add the three accounts, assign the following permissions:
- repl_distribution: Read
- repl_merge: Read
- repl_snapshot: Full Control
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.
In the repldata Properties dialog box, select the Security tab and select Edit:
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.
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.
Verify that the following permissions are allowed:
- repl_distribution: Read
- repl_merge: Read
- repl_snapshot: Full Control
Select the Sharing tab again and note the Network Path for the share. You'll need this path later when you're configuring your snapshot folder.
Select OK to close the repldata Properties dialog box.
For more information, see Secure the snapshot folder.
Configure distribution
In this section, you configure distribution at the publisher and set the required permissions on the publication and distribution databases. If you have already configured the distributor, you must disable publishing and distribution before you begin this section. Do not do this if you must keep an existing replication topology, especially in production.
Configuring a publisher with a remote distributor is outside the scope of this tutorial.
Configure distribution at the publisher
Connect to the publisher in SQL Server Management Studio, and then expand the server node.
Right-click the Replication folder and select Configure Distribution:
Note
- If you connected to SQL Server by using localhost rather than the actual server name, you'll be prompted with a warning that SQL Server cannot connect to localhost or IP Address. Select OK in the warning dialog box. In the Connect to Server dialog box, change Server name from localhost or IP Address to the name of your server. Then select Connect.
- There is currently a known issue with SQL Server Management Studio (SSMS) 18.0 (and later) where a warning message is not displayed when connecting to the Distributor with the IP address, but this is still invalid. The actual server name should be used when connecting to the Distributor.
The Distribution Configuration Wizard starts.
On the Distributor page, select <'ServerName'> will act as its own Distributor; SQL Server will create a distribution database and log. Then select Next.
If the SQL Server Agent is not running, on the SQL Server Agent Start page, select Yes, configure the SQL Server Agent service to start automatically. Select Next.
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.
Accept the default values on the remaining pages of the wizard.
Select Finish to enable distribution.
You might see the following error when configuring the distributor. It's an indication that the account that was used to start the SQL Server Agent account is not an administrator on the system. You'll either need to start the SQL Server Agent manually, grant those permissions to the existing account, or modify which account the SQL Server Agent is using.
If your SQL Server Management Studio instance is running with administrative rights, you can start the SQL Agent manually from within SSMS:
Note
If the SQL Agent doesn't visibly start, right-click the SQL Server Agent in SSMS and select Refresh. If it's still in the stopped state, start it manually from SQL Server Configuration Manager.
Set database permissions
In SQL Server Management Studio, expand Security, right-click Logins, and then select New Login:
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.
On the User Mapping page, in the Users mapped to this login list, select both the distribution and
AdventureWorks2022
databases.In the database role membership list, select the db_owner role for the login for both databases.
Select OK to create the login.
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:
Next steps
You have now successfully prepared your server for replication. The next article teaches you how to configure transactional replication: