Configuring Replication in SQL 2008
In my example, I will configure one-way replication at the transaction level. It must be borne in mind that there are other types of SQL data replication.
You must first make sure that the SQL Server Replication component is installed on both servers.
1.1. Specify the database, the data of which we want to replicate to another server
1.2. Select the type of replication. In our case, it will be Transactional Publication.
')
1.3. Specify published tables or other objects.
1.4. If necessary, we can set a filter by which data will be selected for replication
1.5. On the Snapshot Agent page, we do not mark anything.
1.6. On the Agent Security page, specify the credentials for the Snapshot Agent and the Log Reader Agent. For the Snapshot Agent, you can specify the account under which the SQL Server Agent runs. Later, in the properties of the created publication, we can set another account for running the snapshot agent (Snapshot Agent).
1.7. On the Wizard Actions page put a checkmark Create the Publication
1.8. On the final page set the name of the publication.
1.9. In the properties of the newly created publication, set the folder for storing snapshots (Put files in the following folder), which you must first create. The snapshot folder is a common public folder. For agents reading and writing in this folder, you must set the appropriate permissions to access it. This directory stores the objects needed for replication on the first node. In our case, it is necessary to grant the full rights for this folder to the account on behalf of which the SQL Server Agent is running on the distribution server. Next, in the properties of the created local publication, select the “Subscription Options” option and put a checkmark next to the “Compress Snapshot” option (this will reduce the network load), and set the “Allow anonymous subscriptions” parameter to “false”.
1.10. In SQL Server Management Studio on the distribution server, go to Security - Logins and add the user, on behalf of which the snapshot agent starts on this server. In the Default database field, select the database that we replicate, and in the User Mapping tab, we assign this user the db_owner role to the replicated database, as well as to the Distribution database created during the replication creation process.
2.1. We select the server-distributor and the publication we need.
2.2. On the Distribution Agent Location page, specify where the Distributor Agent will be launched. In the case of launching all the agents on the distributor server, we will get simplified administration, but also an increased load on the distributor server itself. We choose the second option - Run each agent at its Subscriber (pull subscriptions), which will allow us not to additionally load our main server.
2.3. Specify the database to which data will be replicated.
2.4. On the Distribution Agent Security page, specify the credentials for running the Distributor Agent. You can specify the account under which the SQL Server Agent runs. After that, we grant this account permission to read the snapshot folder. In addition, this account must be added to the distribution server's SQL with the Public role on the replicated database, and then to the publication's Access List (in the local publishing properties, select Publication Access List).
2.5. On the Synchronization Schedule page, we specify the synchronization schedule for each Agent. For our case, we choose the launch on demand or on a schedule (for example, every hour)
2.6. On the Initialize Subscriptions page, specify the initialization during the first synchronization.
2.7. On the Wizard Actions page we put the Create the subscription (s) checkbox.
3.1. On the distribution server, run Reinitialize All Subscriptions. We point out that you need to create a new snapshot. As a result of this procedure, a new folder with snapshots should be created in the snapshot storage folder.
3.2. We can monitor the status of replication and run replication in the Replication Monitor utility, as well as through SQL Server Agent tasks.
You must first make sure that the SQL Server Replication component is installed on both servers.
1. On the distribution server, create a local publication (local publication).
1.1. Specify the database, the data of which we want to replicate to another server
1.2. Select the type of replication. In our case, it will be Transactional Publication.
')
1.3. Specify published tables or other objects.
1.4. If necessary, we can set a filter by which data will be selected for replication
1.5. On the Snapshot Agent page, we do not mark anything.
1.6. On the Agent Security page, specify the credentials for the Snapshot Agent and the Log Reader Agent. For the Snapshot Agent, you can specify the account under which the SQL Server Agent runs. Later, in the properties of the created publication, we can set another account for running the snapshot agent (Snapshot Agent).
1.7. On the Wizard Actions page put a checkmark Create the Publication
1.8. On the final page set the name of the publication.
1.9. In the properties of the newly created publication, set the folder for storing snapshots (Put files in the following folder), which you must first create. The snapshot folder is a common public folder. For agents reading and writing in this folder, you must set the appropriate permissions to access it. This directory stores the objects needed for replication on the first node. In our case, it is necessary to grant the full rights for this folder to the account on behalf of which the SQL Server Agent is running on the distribution server. Next, in the properties of the created local publication, select the “Subscription Options” option and put a checkmark next to the “Compress Snapshot” option (this will reduce the network load), and set the “Allow anonymous subscriptions” parameter to “false”.
1.10. In SQL Server Management Studio on the distribution server, go to Security - Logins and add the user, on behalf of which the snapshot agent starts on this server. In the Default database field, select the database that we replicate, and in the User Mapping tab, we assign this user the db_owner role to the replicated database, as well as to the Distribution database created during the replication creation process.
2. On the subscriber server, create a local subscription (local subscription).
2.1. We select the server-distributor and the publication we need.
2.2. On the Distribution Agent Location page, specify where the Distributor Agent will be launched. In the case of launching all the agents on the distributor server, we will get simplified administration, but also an increased load on the distributor server itself. We choose the second option - Run each agent at its Subscriber (pull subscriptions), which will allow us not to additionally load our main server.
2.3. Specify the database to which data will be replicated.
2.4. On the Distribution Agent Security page, specify the credentials for running the Distributor Agent. You can specify the account under which the SQL Server Agent runs. After that, we grant this account permission to read the snapshot folder. In addition, this account must be added to the distribution server's SQL with the Public role on the replicated database, and then to the publication's Access List (in the local publishing properties, select Publication Access List).
2.5. On the Synchronization Schedule page, we specify the synchronization schedule for each Agent. For our case, we choose the launch on demand or on a schedule (for example, every hour)
2.6. On the Initialize Subscriptions page, specify the initialization during the first synchronization.
2.7. On the Wizard Actions page we put the Create the subscription (s) checkbox.
3. Start replication, check.
3.1. On the distribution server, run Reinitialize All Subscriptions. We point out that you need to create a new snapshot. As a result of this procedure, a new folder with snapshots should be created in the snapshot storage folder.
3.2. We can monitor the status of replication and run replication in the Replication Monitor utility, as well as through SQL Server Agent tasks.
Source: https://habr.com/ru/post/130320/
All Articles