SQL Server Database Replication – Clouding.io

In a production environment, we may need to replicate our SQL Server database to reduce some of the load and run other processes we need to query data.

In this article, we explain how to create and configure transactional database replication locally, starting with a production server with SQL Server 2016 Standard and Visio 2016 installed.
Before making any changes, we recommend taking a Snapshot.

It is summarized in the following sections:

Permission configuration
1. Create user(s)
2. Configure user permissions
Install SQL Server Replication
Distribution configuration
Create and configure the publication for transactional replication
Enable permissions for the agent in the publication access list (PAL)
Create the subscription to the transactional publication
Set database permissions for the subscriber
View the subscription synchronization status

1. Permission configuration

1. Create user(s)

In this section, we will create a single local user with Full permissions to create the replication snapshot, and we will use it throughout the process, although different users can be created to restrict permissions.

Step 1/3: From panel de control > Herramientas administrativas > Administración de equipo
(Control Panel\All Control Panel Items\Administrative Tools\Computer Management)

Step 2/3: Expand the "local Users and Groups" options and right-click the "Users" folder to create a new user

Step 3/3: In this example, we use the username replica, but we can give it any name we want.

A new window will then open where we can fill in the new user's details, such as the name and password. In our example, the username is "replica". Fill in the password fields and leave the checkboxes selected as shown in the following image so that the password cannot be changed and never expires:

Click the "Crear" button to create the user, and we can now close the computer management window.

2. Configure user permissions:

Once the user has been created, we proceed to configure permissions. Some permissions need to be configured beforehand, and others will need to be configured later, once the replication database, distribution database, and publication have been created.

Next, to make things clearer, we will list all the permissions we need to grant the user we created. This can also serve as a reference if we want to restrict permissions for different users:

1. Directory containing the database to replicate (See point 3).

2. Directory containing the replication database (See point 3).

3. Directory containing the replication database data and logs:

They are normally stored in the same directory, but they may be in different locations. In this example, they are in the same directory shown in the following image, and editing permissions must be added for the user "replica".

Step1/5: To assign permissions: Right-click the "DATA" folder name and select "Propiedades":

Step 2/5 - On the security tab, click the "Edit..." button:

Step 3/5: To add the user, click the "Add..." button:

Step 4/5: A new window will open to select the user: Enter the username (1) and verify it by clicking the "Check Names" button (2), and finally accept the changes by clicking the OK button (3):

Step 5/5: Once the user has been assigned in the previous step, the window closes and we remain in the same window as in "Step 2". Make sure the user replica is selected and select all checkboxes except "Full control". To finish, click the "OK" button to accept the changes.

4. Directory containing the share for the publication snapshot (Snapshot):

Grant full permissions to the user "replica" for the folder where the Snapshot will be created, which is configured when creating the publisher for the publication:

We can perform this step later, since the directory we are about to create manually will, if it does not exist, be created when distribution is configured, showing the path where it is created, or a different path can be used if required. In any case, the user we created earlier will need to be granted permissions for this directory.

Create the folder named repldata at the path: C:\Archivos de programa\Microsoft SQL Server\MSSQL.X\MSSQL\Data
Assign permissions: Right-click the name of the folder we created earlier and open "Propiedades":

Step 1/4: On the security tab, click the "Edit..." button:

Step 2/4: To add the user, click the "Add..." button:

Step 3/4: A new window will open to select the user: Enter the username (1) and verify it (2), and finally accept the changes by clicking the OK button (3):

Step 4/4: Once the user has been assigned in the previous step, the window closes and we remain in the same window as in "Step 2". Make sure the user replica is selected, select the "Full control" checkbox, and we will see that all permissions are selected. To finish, click the "OK" button to accept the changes:

5. Access permissions for the database to replicate:

To add access permissions for the database to replicate for the user we will use, "replica", follow these 3 steps:

Step 1/3: Open the Microsoft SQL Server Management Studio application:

Step 2/3: Right-click "Login(1), and a new window will open where we can search for the user. Enter the username we created in the "Login name" field, which in this example is "replica", and click the search button "Search" (2).

A small window then opens where we can click the "Check Names" button (4) to verify that the user exists and has been created. Finally, accept the changes by clicking the "OK" button (5), and the search window will close, adding the corresponding user to the previous window:

Step 3/3: Next, in the same window we have open, select "User Mappinng" (1) in the side menu, select the checkbox for the database we have in production (2), and then select the "db_owner" checkbox in the lower box (3):

6. Access permissions for the distribution database:

This must be done as soon as distribution has been configured (Section 3). It consists of adding permissions for the user "replica", which already appears as added. To add the permissions, see section 7 for more details on how to add permissions.

7. Access permissions for the replication database:

This must be done as soon as the publisher has been configured (Section 4). To add the permissions, see section 7 for more details on how to add permissions.

8. Access permissions for the publication access list (PAL):

We will cover this step later when the publication is created. How to add the permissions is explained in "Section 5".

2. Install SQL Server Replication

Run the SQL Server installation file as an administrator to add this new "Replication" component. A new installation window will open, and selecting "Installation" in the side menu gives us the option to install SQL or components:

The next step allows us to install components in an existing instance and displays the detected instances. To do this, select the "Añadir complementos a una instancia existente de SQL Server 2016" option. In this case, we only have one instance, which is already selected:

If SQL Server Replication is installed, the checkbox is selected as shown in the following image; otherwise, it is not installed. Select the checkbox to install it and follow the steps until installation is complete (No restart required):

3. Distribution configuration

Open the Microsoft SQL Server Management Studio application and connect to the instance containing the database we want to replicate.
Having installed "SQL Server Replication" in the previous step allows us to configure distribution. To do this, right-click "Replication" to expand the options and select "Configure Distribution.." as shown in the following image:

A new window will then open for configuration using the wizard. Click the "Siguiente" button, or its equivalent, "Next":

In the next step, the first option is already selected by default, which is exactly the one we want.
The first option creates distribution in the current instance, and the second option allows us to select another instance that has already been configured as a distributor. In our case, the distributor is local, so leave the default option selected and click the "Siguiente" button:

It will display a warning to keep in mind, since the SQL Server agent must be running for replication and to take the snapshot. We can choose whether to start it automatically when the server starts if it detects that it is stopped, or not to start it automatically:

In the next step, it will show the default path where it will save a copy of the current database image and replicate changes in real time. We can change the path if desired, remembering to grant full permissions so it can create the snapshot, as explained in the next section.
Finally, click "Siguiente":

In the following image, you can change the distribution database name if you want. You can also define the path where the replication database will be created and the logs saved, or leave the default. If the paths are changed, take into account the permissions explained in the next section:

In the next step, select the SQL replication publishers that access the distributor and click next:

We then have two options: By default, leave only the first checkbox selected to configure distribution now, or also select the other checkbox to generate a script so we can configure it later. Then click next and configuration will begin:

Once it finishes, it will display a summary of all the points, and we click finish.

Note: If we have not selected the option to generate a script to configure it later, it is normal for an error to appear in the last step because the SQL agent service does not have permissions, which we still need to configure as explained in the next section.

4. Create and configure the publication for transactional replication

The first step is to configure the publisher, for which we create a new publication:

A wizard will open in a new window to create it; click next:

Next, select the database we want and click next:

Here it is important to note that we select "Transactional publication" to transmit transactions once the snapshot has been created, since the first option is for scheduling when to do so. Click next:

Now, at this point, we can select the objects and tables we want to replicate:

Once the objects, tables.. have been selected in the previous step, click next:

It gives us details of the selection in the previous image. Click next again.

Next, it allows us to define filters to apply to the selected articles. If we click "Add", we can select a specific table and then apply a filter. In this case, leave the default and click next:

In the next step, nothing is selected by default. In this case, we will select the first option to create the snapshot immediately, since the second option is to create it later by configuring schedules to define a time window:

In the next step, we must specify the account to use as the snapshot agent, which must have full permissions. For the second agent, "Log Reader Agent", however, we can select another user, since it does not need full permissions, only read permissions. In this example, we use the same user, so there is no need to clear the "use the security settings from the SnapShot Agent" checkbox. In any case, click the corresponding "Security Settings.." button:

A second window will open where we can specify the user type. In this case, a user account created in Windows will be used; click the "OK" button:

When the second window closes, we return to the previous window, where the field will be filled in and the "Next" button will be available to move to the next step:

The last step allows us to create the publication as shown in the following image by selecting the "Create the publication" checkbox, or to select the second option if you want to create it later using a script. Click next:

Next, we need to choose a name for the publication. We can use the same name as our database and add _Pub, for example: DatabaseEjemplo_Pub

And click the finish button:

Once the process is complete, we can check that the publication has been created:

For more details, right-click it and click the "View SnapShot Agent Status" option in the drop-down menu:

Then, if it has been created, a similar message appears:

Clicking the monitor button opens a new window where we can view more details about the process. To continue, move on to the next section, which explains how to create the subscriber.

5. Enable permissions for the agent in the publication access list (PAL):

In this section, we only need to add the user with editing permissions, but in this example, the user replica is added with full permissions:

We can then add it in this section. To do so, select "Publication Access List" and then the add button:

After clicking the add button, the following window will appear. With the user selected, click OK:

Then we return to the previous window, where we must also accept the changes by clicking the OK button:

6. Create the subscription to the transactional publication

To create the subscription to the transactional publication, right-click "Local Subscriptions":

A new wizard will open, and we will follow the steps:

It will display the PAL (Publication Access List) that we created in an earlier section. Select it and click next:

In this example, we accept the option selected by default to run all agents at the distributor:

In the next step, for the checkbox to appear selected, in this case for the "DBSQL" instance, we must first add a new subscriber using "Add SQL Server Subscriber.." and then connect to the instance:

The subscriber instance connection window will appear; click connect, and that is it:

We return to the previous window, which will display the instance containing the subscriber, and proceed to create the database by selecting the "New database" option from the drop-down menu and clicking "Next":

Next, give the new replication database a name and click OK:

Now, in the next window, we must add the user we use to connect to the subscriber we are creating. Clicking the dots icon opens the configuration window:

In the configuration window, we will configure the connection with a user account. In this case, we use a local server user that we created at the beginning of this guide. The details to fill in are those marked in the following image, the user account and password; click OK:

After clicking OK, we automatically return to the previous window, which allows us to click the finish button directly or click next. Since we leave the remaining settings at their defaults, we can click finish and it will begin creating the subscription. Once complete, the results will appear as follows, and we only need to click the "Close" button if everything is correct:

To view the process from the replication monitor, follow these steps:

7. Set database permissions for the subscriber

Database permissions on the subscriber can now be set, since the replication database has been created. To do this, expand the Security and Logins options, where we can add permissions to an existing user by right-clicking the user in question (In this case, the user is DBSQL\replica) and selecting "Properties". Left-clicking it opens the SQL user properties window:

Select the three databases. To select the "db_owner" role, we must select each database one by one and select the "db_owner" checkbox for each one. When selecting the distribution database, also select the replmonitor checkbox.
Finally, to save the changes, click OK:

8. View the subscription synchronization status

To view the subscription synchronization status, right-click the "Replication" folder and left-click "Launch Replication Monitor":

A new window will open, where we can right-click the job in progress and view more details..:

We can now see that the database is replicated and will synchronize automatically.

Remember, if you have questions about this or any other issue related to your servers at Clouding, do not hesitate to write to soporte@clouding.io We are here for whatever you need!

Articles in this section