Uredi

Deli putem

Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)

  • Članak

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.

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 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.

Note

  • 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

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 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.

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:

    Screenshot of 'New User' dialog box.

  5. Repeat the previous step to create the repl_logreader, repl_distribution, and repl_merge accounts:

    Screenshot of list of replication users.

  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.

    1. On the Sharing tab in the repldata Properties dialog box, select Advanced Sharing.

    2. In the Advanced Sharing dialog box, select Share this Folder, and then select Permissions.

    Screenshot of selections for sharing the repldata folder.

  4. 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.

    Screenshot of selections to add sharing permissions.

  5. Repeat step 6 to add the other two accounts that you created previously: <Publisher_Machine_Name>\repl_merge and <Publisher_Machine_Name>\repl_distribution.

  6. After you add the three accounts, assign the following permissions:

    • repl_distribution: Read
    • repl_merge: Read
    • repl_snapshot: Full Control

    Screenshot of shared permissions for each account.

  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.

  8. In the repldata Properties dialog box, select the Security tab and select Edit:

    Screenshot of 'Edit' button on the 'Security' tab.

  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.

    Screenshot of selections to add security permissions.

  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.

  11. Verify that the following permissions are allowed:

    • repl_distribution: Read
    • repl_merge: Read
    • repl_snapshot: Full Control

    Screenshot of User permissions for replication data.

  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.

    Screenshot of network path on the 'Sharing' tab.

  13. 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 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.

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:

    Screenshot of 'Configure Distribution' command on the shortcut menu.

    • 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.

    Note

    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.

  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.

    Screenshot of option to make the server act as its own distributor.

  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.

    Screenshot of comparison of network paths in the 'repldata Properties' dialog box and in the Configure Distribution Wizard.

  6. Accept the default values on the remaining pages of the wizard.

    Screenshot of last page of the wizard.

  7. 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.

Screenshot of error message for configuring the SQL Server Agent.

If your SQL Server Management Studio instance is running with administrative rights, you can start the SQL Agent manually from within SSMS:

Screenshot of selecting 'Start' on the shortcut menu for the agent in 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

  1. In SQL Server Management Studio, expand Security, right-click Logins, and then select New Login:

    Screenshot of 'New Login' command on the shortcut menu.

  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.

    Screenshot of selections for entering the object name.

  3. 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.

    Screenshot of selecting the databases and their role.

  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.

    Screenshot of all four accounts in Object Explorer.

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

Next step

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