Implementing Replication

With so much terminology involved, replication can be confusing and misleading initially. In this section, you'll go through one exercise to set up snapshot replication. One added benefit of doing this is that various tools and wizards for replication setup were totally rewritten in SQL Server 2005. The gist is still the same, but it is nice to see the different user interface here.

You shouldn't have much trouble setting up snapshot replication. Transactional and merger replication is similar. We will review the major differences right after snapshot replication setup.

The Setup

We will use AdventureWorks as the publishing and subscribing database for this exercise. To make things easier, you'll create a new table for this purpose. For the purposes of the exercise, assume AdventureWorks is a car company that sells fuel-efficient hybrid cars in the U.S., China, and Sweden. You'll set up snapshot replication between database servers in the U.S. and China to refresh data. Furthermore, you can set up transactional replication between database servers in the U.S. and Sweden. The data can also be used to set up merge replication. Use the following script to create the table and insert some relevant data:

 CREATE TABLE Sales.Cars (ProdID INT PRIMARY KEY, ProdDesc varchar(35), Country varchar(7), LastUpdate smalldatetime ) INSERT INTO Sales.Cars (ProdID, ProdDesc, Country, LastUpdate) VALUES ('1', 'ProEfficient Sedan', 'US', GetDate()) INSERT INTO Sales.Cars (ProdID, ProdDesc, Country, LastUpdate) VALUES ('2', 'ProEfficient Van', 'US', GetDate()) INSERT INTO Sales.Cars (ProdID, ProdDesc, Country, LastUpdate) VALUES ('3', 'JieNeng Crossover', 'China', GetDate()) INSERT INTO Sales.Cars (ProdID, ProdDesc, Country, LastUpdate) VALUES ('4', 'JieNeng Utility', 'China', GetDate()) INSERT INTO Sales.Cars (ProdID, ProdDesc, Country, LastUpdate) VALUES ('5', 'EuroEfficient Wagon', 'Sweden', GetDate()) INSERT INTO Sales.Cars (ProdID, ProdDesc, Country, LastUpdate) VALUES ('6', 'EuroEfficient Pickup', 'Sweden', GetDate()) 

Replication can be implemented through both GUI wizard pages and scripting. If you are new to replication, we recommend that you go through the GUI and property pages through SQL Server Management Studio first. Best of all, Management Studio allows you to generate SQL scripts at the end of processes, which you can save to a file and edit for other deployment.

You must implement a distributor before you can create publications and subscribe to publications, so you'll create the distributor first. A distributor is needed for all types of replications.

Setting up Distribution

As we mentioned earlier, a distributor consists of a distribution database (where replication history, status, and other important information will be stored) and a shared folder (where data and articles can be stored, retrieved, and refreshed). In addition, you need to find out the domain name and account that will be used during the process to run various replication agents, such as the Snapshot Agent, Log Reader Agent, and Queue Reader Agent. That is not necessary, though, because you can choose to impersonate the SQL Server Agent account. That is good for testing and learning, but when you put things into production, a dedicated domain account is recommended for security reasons.

Here is a step-by-step process to follow:

1. Using SQL Server Management Studio, connect to the distributor server. Expand the server in Object Explorer.

2. Right-click Replication, and select Configure Distribution.

3. You will see the Welcome screen; click Next. Then you will see the screen in Figure 16-1, where you will pick which server will be served as distributor.

4. Click Next, and you will be asked to enter the snapshot folder. Since the snapshot folder will be accessed by subscribers, a network path is needed, which you'll enter in the next screen.

5. Once you've picked the snapshot folder, you can then proceed with the distribution database on the next page of the wizard, as shown in Figure 16-2. From here, you can separate the data file and log file so they are on different hard-disk spindles for better performance.

6. On the next page, you see a list of publishers that can use this server as its distributor. Check the boxes for the publishers you want to use.

7. On the next page, before you let the wizard create the distribution for you, you'll see a screen where you can choose to have the wizard configure the distribution and also have it generate a script for you. Select to create a script file before you click Next.

8. On the next page, you specify where and how you want the script file generated, as shown in Figure 16-3.

9. The last stage is the summary page before the wizard will go ahead.

10. Click Finish, and the process will begin. Afterward, you will see the finished report.

Figure 16-1

Figure 16-2

Figure 16-3

As you can see, setting up distributor is not a difficult task. A distribution database is created. In addition, as mentioned earlier, SQL Server Agent plays a very important role in setting up replication. If you expand the SQL Server Agent in Object Explorer, and open the Jobs folder, you'll see that a number of new jobs were created for the replication.

Implementing Snapshot Replication

By now, you know that all replication needs publication, distribution, and subscription. Now that you've set up the distribution, you can use that for the replication exercise later in this chapter. As with any replication, you need to create a publication before subscribers can subscribe to it, so that is what you'll do next.

Setting Up Snapshot Publication

Once again, we will use Management Studio to accomplish the setup, and you can elect to have everything scripted at the end of the process.

1. Within Management Studio, while connected to the server where publication database resides, expand the Replication folder. Right-click Local Publications and pick New Publication.

2. You will see the Publication Wizard welcome screen; click Next. On the next screen, you will be asked to pick a database for publication. Pick AdventureWorks as your database to create a publication, and click Next.

3. Next you will be presented with a screen to pick the publication type. In this case, pick Snapshot publication and click Next.

4. Next, you need to pick articles such as tables, views, and other database objects to be included in this publication, as shown in Figure 16-4.

5. Pick the Cars table you created earlier for publication. Expand the Tables item; then select the Cars table and its children, as shown in Figure 16-5.

   You can see that the Cars table under the Sales schema is selected. Furthermore, if necessary, you can pick and choose columns of the table for publication by unchecking the box next to the column name. We will not use this feature here, but it can be useful in certain scenarios.

   Also note that you can set properties of articles that you choose to publish. These properties affect how the article will be published and some behaviors when it is synchronized with the subscriber. Again, don't change the default properties here, but they can be very useful.

6. On the next page, the wizard will give you an option to filter out rows. Click the Add button.

7. As we mentioned earlier, since we are trying to replicate data to the Chinese market, you need to filter the cars by country, as shown in Figure 16-6.

   After you click OK, the filter will be applied.

8. Next, you need to define when to run the Snapshot Agent. In this case, don't schedule it; you will invoke it manually by running SQL Server Agent job yourself.

9. As mentioned earlier, different replication models call for different agents to be run. For security purposes, you need to define the account under which the agents run, on the next screen. Click the Security Settings button to configure the account.

   It is convenient to have a dedicated domain account with a secure password that does not need to be changed very often for this purpose. However, if you don't have that access, you can choose to impersonate the SQL Server Agent account for this exercise. It is best to have a dedicated account, though, as shown in Figure 16-7.

   Once the account is set, click OK to continue:

10. Now we are almost done with this publication. You can choose to generate a script file at this stage:

    • The rest of the process is pretty similar to the distributor creation documented earlier. Once you click Finish, the snapshot publication will be created. Again, you can use Object Explorer to see the publication and SQL Server jobs created during this process.

    • You will also notice that folders and files are created for this snapshot publication. The subfolders and files are created under the shared folder defined when the distributor was set up earlier. To verify that, you can go to the shared folder in Explorer and browse for files generated. Figure 16-8 can give you some ideas.

Figure 16-4

Figure 16-5

Figure 16-6

Figure 16-7

Figure 16-8

Setting up Subscription to the Snapshot Publication

Now that a snapshot publication is created, you can subscribe to it from a different server.

1. Once again, connect to the subscription server using SQL Server Management Studio, and expand Replication folder in Object Explorer. Right-click Local Subscription, and pick New Subscriptions.

2. You will see the welcome screen of subscription wizard. Click Next. You will need to choose the publication you want to subscribe to.

   Pick <Find SQL Server Publisher...> from the drop-down list. You will see the typical Connect to Server window that is common in SQL Server Management Studio. Connect to the server where you set up the snapshot publication earlier, and click Connect. You will then see the publisher and publication:

3. Click Next, and you'll be taken to a page where you will need to pick the distribution agent location. Here is where you decide if this going to be a pull or push delivery. Unlike SQL Server 2000, where there are separate wizards for push and pull delivery, SQL Server 2005 combines them in one convenient place.

   Make your subscription a pull subscription; then click Next.

4. In this page, you can set proper subscriber properties. Or if you want to add additional subscribers, you can do so on this page as well.

5. Click Next. Here you will see the familiar window of Agent security setting. This is similar to the step where we set up the domain account for Snapshot Agent. Click (…) to set up the security options, as shown in Figure 16-9.

   If you want to specify different domain account for Distribution Agent Security, you need to fill out account information on this screen. For the purpose of testing, you can set it up as shown in Figure 16-9. Click OK; then click Next

6. Now you need to set the synchronization schedule. Since this is just your first test example, make it simple and set it to run on demand. Click the drop-down and select Run on demand only.

7. In the exit page, you need to decide the initial synchronization. Check the box to select initialization, and in the drop-down, choose to initialize immediately:

8. The rest of steps are simple. The wizard will ask if you want to generate a script, present a report, and finish subscription creation.

Figure 16-9

Verifying Snapshot Replication

So far, the setup is pretty simple. But how do you know it actually works? You can conduct some tests here to make sure it does indeed work properly.

1. First, connect to the publication server and verify the records there:

    1> :connect MyPublisher Sqlcmd: Successfully connected to server 'MyPublisher'. 1> use adventureworks 2> go Changed database context to 'AdventureWorks'. 1> select * from sales.cars 2> go ProdID      ProdDesc                            Country LastUpdate ----------- ----------------------------------- ------- --------------------           1 ProEfficient Sedan                  US       2006-06-14 15:33:00           2 ProEfficient Van                    US       2006-06-14 15:33:00           3 JieNeng Crossover                   China    2006-06-14 15:33:00           4 JieNeng Utility                     China    2006-06-14 15:33:00           5 EuroEfficient Wagon                 Sweden   2006-06-14 15:33:00           6 EuroEfficient Pickup                Sweden   2006-06-14 15:33:00 (6 rows affected) 

2. Now connect to the subscription server. Since you've already initialized the subscription, you will only see cars for the Chinese market:

    1> :connect MySubscriber Sqlcmd: Successfully connected to server 'MySubscriber'. 1> use adventureworks 2> go Changed database context to 'AdventureWorks'. 1> select * from sales.cars 2> go ProdID      ProdDesc                            Country LastUpdate ----------- ----------------------------------- ------- --------------------           3 JieNeng Crossover                   China    2006-06-14 15:33:00           4 JieNeng Utility                     China    2006-06-14 15:33:00 (2 rows affected) 

3. Now suppose you made some changes to cars for the Chinese market and upgraded JieNeng Crossover to JieNeng Crossover LE at the publication server:

    1> :connect MyPublisher Sqlcmd: Successfully connected to server 'MyPublisher'. 1> use adventureworks 2> go Changed database context to 'AdventureWorks'. 1> update sales.cars set proddesc = 'JieNeng Crossover LE' where prodid = 3 2> go (1 rows affected) 

4. Since you've updated records at the publisher, you need to take a new snapshot by running the SQL Server Agent jobs. In the Object Explorer, expand the SQL Server Agent Jobs folder, right-click the "MYPUBLISHER-AdventureWorks-ChinaCars-1" job, and select Start Job from the context menu.

5. Since you implemented a pull subscription, we will need to go to the subscriber and run the job to refresh this snapshot. Open the Object Explorer on the subscriber, Expand the SQL Server Agent Jobs folder, right-click the job, and select Start Job from the context menu.

6. After this is done, check to make sure the data is indeed refreshed:

    1> :connect MySubscriber Sqlcmd: Successfully connected to server 'mySubscriber'. 1> use adventureworks 2> go Changed database context to 'AdventureWorks'. 1> select * from sales.cars 2> go ProdID      ProdDesc                            Country LastUpdate ----------- ----------------------------------- ------- --------------------           3 JieNeng Crossover LE                China    2006-06-14 15:33:00           4 JieNeng Utility                     China    2006-06-14 15:33:00 (2 rows affected) 

Implementing Transactional and Merge Replication

Procedurally, setting up transactional and merge replication is very similar to the snapshot replication discussed earlier. We will not be discussing the detailed step-by-step instructions here. Instead, we will discuss some differences and things you need to pay attention to.

The typical transactional replication is not too different from snapshot replication. One major component added is the Log Reader Agent. This agent tracks all changes made to the article so it can be propagated to the subscriber. As a result, the load on distribution database is higher than snapshot replication, which means you need to keep a closer eye on it, especially the log file of the distribution database.

If you implement transactional publication with updateable subscription, subscriber changes can be refreshed back to the publisher. However, SQL Server will need to add one additional column in tables included in the publication to track changes. This column is called MSrepl_tran_version and is a unique identifier column. Therefore, if you have code like this:

• insert into sales.cars values (9, 'English Car', 'UK', getdate())

This code will fail because it does not have a column list. As a result, the application of that code will need to be updated. To fix that, the column list that corresponds to the values in parenthesis must be given right after the table name.

For transactional replication with updatable subscription, a linked server is used among SQL Server publishing and subscribing databases. The linked server uses MS DTC (Distributed Transaction Coordinator) to coordinate transactions; therefore, MS DTC on the publisher must be enabled to accept remote connections.

For merge replication, all articles must have a unique identifier column with a unique index and the ROWGUIDCOL property. If they don't have it, SQL Server will add one for you. Just like transactional replication with updateable subscription, an insert statement without the column list will fail.

Additional agents will be used for transactional and merge replication, such as the Log Reader Agent and Queue Reader Agent. The agents will require a domain account to run under. The domain account can be their own or shared with other agents. How you choose to implement that depends on your company's security policy.

For merge replication, you can choose to synchronize data over https. That requires IIS (version 5.0 and version 6.0) that uses replisapi.dll.