Database Replication

What Is Replication? Replication refers to a set of technologies used for copying and distributing data and database objects from one database to another and maintaining synchronization among databases to ensure there is consistency. SQL Server Replication doesn’t distribute the whole database. It only distributes selected parts of the database such as tables, views, etc. What is SQL Server Replication? SQL Server Replication is a process of copying and distributing data and Database objects from one Database to another Database and synchronizing all the data between the Databases to maintain integrity and consistency of the data. The process can be set to synchronize data continuously in real-time or scheduled to run at the desired intervals. Mainly the SQL Server Replication is used to copy and reproduce data at the desired targets. Some of the SQL Server Replication techniques are listed below: • One-Way • One-to-One • Many-to-One • Bi-Directional SQL Server Replication Components This model is made up of the following components: Article: This is the basic unit of SQL Server and is made up of views, tables, and stored procedures. You can use the filter option to scale the article either vertically or horizontally. You can create multiple articles on one object with particular restrictions or limitations. Publication: A publication is a logical collection of articles from the database. It allows you to define and configure properties of articles at a higher level so that all articles in the group may inherit the properties. Publisher Database: The Publisher is a database that contains a list of objects designed as replication articles. The Publisher may have one or more publications. Each Publisher creates many internal replication stored procedures that define a data propagation mechanism. Publisher: This is a database instance responsible for making data available to other locations through replication. It may have one or more publications, with each publication defining a set of logically related objects and data to be replicated.

Distributor: This is a database that stores replication specific data from one or more Publishers. In most cases, the Distributor is a single database acting as both a Publisher and a Distributor. Such a Distributor is referred to as a Local Distributor. Each Publisher must be associated with a single database called a Distributor or a Distribution database. Distribution Database: Each Distributor should have at least one Distribution database. The Distribution database stores the article details, data, and replication meta-data. Note that a single Distributor may hold more than one Distribution database. However, all publications from one Publisher must use one Distribution database. Subscriber: This is a database instance that consumes the SQL Server Replication data from a publication. It is possible for the Subscriber to receive data from one or more publications and Publishers. Subscription: This is a request for a copy of a publication to be sent to a Subscriber. The subscription defines when the publication data is to be received, and where. Subscription database: This is the target database of a replication model. Prerequisites Ensure that your computer has the following installed: • Microsoft SQL Server 2008 or above. • Microsoft SQL Server Management Studio (SSMS). • Step 1: Configuring the SQL Server Distributor • Step 2: Configuring the SQL Server Publisher • Step 3: Configuring the SQL Server Subscriber Step 1: Configuring the SQL Server Distributor To configure the SQL Replication Distributor, follow the steps given below: Step 1: Open SSMS and establish a connection to your SQL Server instance. Step 2: Right-click on the “Replication” folder on the Object Explorer and select “Configure Distribution”. Step 3: The “Distribution Configuration Wizard” will popup. The wizard shows the general details about configuring the Distributor. Just click on the “Next” button.

Step 4: On the “Distributor” page, you can set the current instance as the Distributor or choose another instance that has already been configured as a Distributor. Since the Distributor is local, go with the default setting, that is, “‘ServerName’ will act as its own Distributor; SQL Server will create a distribution database and log”. Click on the “Next” button. Step 5: In the next window, type the path to the SQL replication snapshot folder, or go with the default path. In my case, I will use the default path. This folder is used for initial data synchronization of Transactional Replication, so ensure that it has enough size to hold all the replicated data. Click on the “Next” button. Step 6: You can now configure the replication distribution database. Give the distribution database a name and specify the location of the data and log files. Click on the “Next” button. Step 7: You will be taken to the “Publishers” page. Specify the Publishers that will access the Distributor, and then click on the “Next” button. Step 8: In “Wizard Action”, you can choose to run immediately or create a script to be executed later. Click on “Next”. Step 9: In the next window, review the settings and configuration options. If they are okay, click on the “Finish” button to enable the Distributor. Step 10: You will then be taken to the “Success” window, meaning that the configuration was successful. Now that the Distributor is ready, let’s go ahead and configure the Publisher. Step 2: Configuring the SQL Server Publisher

Follow the steps given below: Step 1: Expand the “Replication” folder from the “Object Explorer”. Right-click on “Local Publications” and choose “New Publication”. Step 2: A new window will appear giving you the general details about creating a new publication. Click on “Next”. Step 3: The “Publication Database” window will appear. Choose AdventureWorks2016 database, and then click on “Next”. Step 4: The “Publication Type” window will appear. Choose “Transactional Publication” then click on “Next”. Step 5: You will be taken to the “Articles” page. Select all the articles that you need to be included in the publication and click on “Next”. Step 6: After selecting the objects, click the “Show only checked articles in the list” option to check it. You will have listed the candidates for publication. Click on “Next”. Step 7: You will be taken to the “Filter Table Rows” page. Here, you should specify the filters to be applied to your articles. Just go with the default values. Click on “Next”. Step 8: In the next window, you should specify when the Snapshot Agent will start. You can run it immediately or schedule it to be run later. Choose the first option, which is to create a snapshot immediately. Click on “Next”. Step 9: On the next page, specify the account to be used to run the Snapshot Agent and click on “OK”. Step 10: Choose the first option to create the publication immediately. Click on “Next”.

Step 11: Type in the name for the publication and click on “Finish”. I have given it the name, AdvWorks_Pub. Step 12: In the next window, you should see the success message. You can then check under “Local Publications” to confirm whether the publication has been created. Step 3: Configuring the SQL Server Subscriber This is the final step of setting up SQL Server Replication. To create the subscription, follow the steps given below: Step 1: Expand the “Replication” folder from “Object Explorer”, right-click on “Local Subscriptions”, and then choose “New Subscriptions”. Step 2: The next window will show the general details about creating subscriptions. Click on “Next”. Step 3: In the next window, select the publication and then click on “Next”. Step 4: The “Distribution Agent Location” page will be opened. Select “Run all agents at the Distributor”, and then click on “Next”. Step 5: The “Subscribers” window will open. Select “Add Subscriber”, and then select “Add SQL Server Subscriber” from the provided drop-down. The “Connect to Server” dialogue box will be opened. Enter the name of the Subscriber instance and choose “Connect”. Step 6: After adding the SQL Subscriber instance, select the dropdown next to your Subscriber instance. Select “New Database”, type the name of the database, and then click on “OK”. Since it’s a replica database, I have given it the name AdventureWorks2016.

Step 7: The subscription database will be created and then registered to the Subscriber. Ensure that the account has the db_owner permission on the new database. Step 8: The “Distribution Agent Security” page will be opened. Click on the ellipsis (…) button. Type in the details of the process account then click on “OK”. Step 9: Click on the “Finish” and accept the default values on the remaining pages. Step 10: On the “Creating Subscription(s)…” page, you will know whether the process was successful or not. Click on “Close”. Step 11: Establish a connection to the Publisher in the SQL Server Management Studio. Right-click on the “Replication” folder from “Object Explorer”, then select “Launch Replication Monitor” so as to check the status of the Transactional Replication setup that you have just created. You should now be able to see data being propagated between the servers when you insert, update, or delete data from the articles that you included in the publication.