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.
नोट
To replicate data securely between servers, you should implement all of the recommendations in Replication security best practices.
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.
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
AdventureWorks2022
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 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.
नोट
Replication isn't supported on SQL Server instances that are more than two versions apart. For more information, see Replication backward compatibility.
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
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 |
नोट
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 isn't a requirement. If the publisher and subscriber share the same instance, the steps that are used to create accounts at the subscriber aren't required.
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.
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.
In this section, you configure the snapshot folder that's used to create and store the publication snapshot.
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.
On the Sharing tab in the repldata Properties dialog box, select Advanced Sharing.
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 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.
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.
Configuring a publisher with a remote distributor is outside the scope of this tutorial.
Connect to the publisher in SQL Server Management Studio, and then expand the server node.
Right-click the Replication folder and select Configure Distribution:
If you connect to SQL Server by using localhost rather than the actual server name, you're prompted with a warning that SQL Server can't 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's currently a known issue with SQL Server Management Studio (SSMS) 18.0 (and later) where a warning message isn't displayed when connecting to the Distributor with the IP address, but is still invalid. The actual server name should be used when connecting to the Distributor.
नोट
Server name can be specified as
<Hostname>,<PortNumber>
for a default instance or<Hostname>\<InstanceName>,<PortNumber>
for a named instance. Specify the port number for your connection when SQL Server is deployed on Linux or Windows with a custom port, and the browser service is disabled. The use of custom port numbers for remote distributor applies to SQL Server 2019 (15.x) and later versions.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 isn't 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 isn't an administrator on the system. You 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:
नोट
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.
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 Configure distribution and Replication Agent security model.