Configure replication in SQL 2008
In my example, I will configure transactional one-way replication. Keep in mind that there are other types of SQL data replication.
You must first verify that SQL Server Replication is installed on both servers.
1.1. Specify the database whose data we want to replicate to another server
1.2. Choose the type of replication. In our case, this will be Transactional Publication
1.3. We indicate 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, nothing is noted
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 SQL Server Agent runs. Later, in the properties of the created publication, we can specify a different account for launching the Snapshot Agent.
1.7. On the Wizard Actions page, check the Create the Publication checkbox.
1.8. On the final page, set the publication name
1.9. In the properties of the newly created publication, we set the folder for storing snapshots (Put files in the following folder), which must first be created. The snapshot folder is a regular shared folder. For agents reading and writing to this folder, you must set the appropriate permissions to access it. This directory stores the objects necessary for replication on the first node. In our case, it is necessary to grant full rights to this folder to the account on behalf of which SQL Server Agent is launched on the distribution server. Next, in the properties of the created local publication, select the “Subscription Options” item and put a daw in front of the “Compress Snapshot” option (this will reduce the load on the network), and set the “Allow anonymous subscriptions” option 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 whom the snapshot agent starts on this server. In the Default database field, select the database that we are replicating, and in the User Mapping tab we assign this user the db_owner role on the replicated database, as well as on the Distribution database created during the creation of the replication.
2.1. We select the distribution server and the publication we need
2.2. On the Distribution Agent Location page, specify where the Distributor Agent will start. In the case of running all the agents on the distribution server, we get simplified administration, but also an increased load on the distribution 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 base 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 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 SQL server with the Public role on the replicated database, and then in the Access List of the publication (in the properties of the local publication, select Publication Access List).
2.5. On the Synchronization Schedule page, specify the synchronization schedule for each Agent. For our case, we select the launch on demand or on a schedule (for example, every hour)
2.6. On the Initialize Subscriptions page, specify the initialization at the first synchronization
2.7. On the Wizard Actions page, check the Create the subscription (s) box.
3.1. On the distribution server, run Reinitialize All Subscriptions. Indicate 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 replication status and start replication in the Replication Monitor utility, as well as through SQL Server Agent jobs.
You must first verify that SQL Server Replication is installed on both servers.
1. On the distribution server, create a local publication.
1.1. Specify the database whose data we want to replicate to another server
1.2. Choose the type of replication. In our case, this will be Transactional Publication
1.3. We indicate 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, nothing is noted
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 SQL Server Agent runs. Later, in the properties of the created publication, we can specify a different account for launching the Snapshot Agent.
1.7. On the Wizard Actions page, check the Create the Publication checkbox.
1.8. On the final page, set the publication name
1.9. In the properties of the newly created publication, we set the folder for storing snapshots (Put files in the following folder), which must first be created. The snapshot folder is a regular shared folder. For agents reading and writing to this folder, you must set the appropriate permissions to access it. This directory stores the objects necessary for replication on the first node. In our case, it is necessary to grant full rights to this folder to the account on behalf of which SQL Server Agent is launched on the distribution server. Next, in the properties of the created local publication, select the “Subscription Options” item and put a daw in front of the “Compress Snapshot” option (this will reduce the load on the network), and set the “Allow anonymous subscriptions” option 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 whom the snapshot agent starts on this server. In the Default database field, select the database that we are replicating, and in the User Mapping tab we assign this user the db_owner role on the replicated database, as well as on the Distribution database created during the creation of the replication.
2. On the subscription server, create a local subscription.
2.1. We select the distribution server and the publication we need
2.2. On the Distribution Agent Location page, specify where the Distributor Agent will start. In the case of running all the agents on the distribution server, we get simplified administration, but also an increased load on the distribution 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 base 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 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 SQL server with the Public role on the replicated database, and then in the Access List of the publication (in the properties of the local publication, select Publication Access List).
2.5. On the Synchronization Schedule page, specify the synchronization schedule for each Agent. For our case, we select the launch on demand or on a schedule (for example, every hour)
2.6. On the Initialize Subscriptions page, specify the initialization at the first synchronization
2.7. On the Wizard Actions page, check the Create the subscription (s) box.
3. Start replication, check.
3.1. On the distribution server, run Reinitialize All Subscriptions. Indicate 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 replication status and start replication in the Replication Monitor utility, as well as through SQL Server Agent jobs.