Apply Your Knowledge

Exercises

11.1 Creating a Publication for Snapshot Replication

The purpose of this exercise is to create a snapshot publication in SQL Server.

Estimated Time: 15 minutes

Open the SQL Server Enterprise Manager from the Start menu and connect to your default instance of SQL Server.
Click on Tools, Replication, and then select Create and Manage Publications .
Select Northwind and then click Create Publication.
On the starting screen of the Create Publication Wizard, leave the Show Advanced Options check box unchecked and click Next. The next screen prompts you to select a database. Select Northwind and click Next.
You should be on the Publication Type screen. Select Snapshot Replication and click Next.
Now you have to choose what version of SQL Server (if they even use SQL Server) your Subscribers use. Selection screens vary depending on what you choose, so select SQL Server 2000, leaving the other two options blank, and click Next to continue.
You should be at the Specify Articles selection screen. Select the check box next to the Customers object and then click the ellipsis ( . . . ) to its right. Leave all settings as they are, except change the Destination Table Owner to Dbo and Destination Table Name to rtblCustomers and then close the dialog box. You are returned to the Article Selection screen, where you should click Next.
Arriving at the Publication Name selection screen, leave all defaults in place and click Next.
On Property Customization, select the option No, Create the Publication as Specified, and then click Next. This ends the wizard and completes the creation of a new publication.

After the creation of the publication, you can then allow the Subscribers to pull subscriptions. As an alternative, the Publisher can push the subscription out, but the following exercise shows a pull from the Subscriber's side.

It is worth noting that if this is the first time you have set up replication on any given installation, you will see additional configuration screens that were addressed in the Step by Step operations earlier in this chapter. Before Step 4 of this exercise, you would have three additional screens to provide the Distributor configuration, SQL Server startup options, and snapshot folder location.

11.2 Creating a Pull Subscription

The purpose of this exercise is to create a pull Subscription for the publication created previously in Step by Step 11.4.

Estimated Time: 15 minutes

Connect to your default instance of SQL Server.
Click on Tools, Replication, and Pull Subscription to initialize the Pull Subscription dialog box.
Click Pull New Subscription, which starts the Pull Subscription Wizard.
On the starting screen, leave Show Advanced Options unchecked and then click Next.
On the Look for Publications screen, select the first option that specifies it will search on registered servers for publications.
On the Choose Publication screen, expand the default server; you should find an entry named Northwind: Northwind. Select it and then click Next.
If you've already gone through the replication implementation in Step by Step 11.2, you should see a database called Replica . If you haven't, then create the database by clicking New and entering Replica as the database name. Leave all values except for Name to their defaults, and then click OK to complete the dialog. Click Next to continue.
Select Yes, because when you implement replication the first time an initial snapshot must already have been applied at the Subscriber; only then can the Subscriber receive incremental changes.
Select the Default Snapshot folder (MSSQL\ReplData) option and click Next.
On the next screen, select the last option (On Demand Only) and then click Next. Select On Demand Only because the data at the Subscriber does not need to be updated at all in this example or the examples to come.
You are at the final screen of the wizard. Click Finish to complete the wizard.
You now have to manually initiate the Snapshot agent. To do this, expand Replication Monitor, Agents, Snapshot Agents , and right-click Northwind. To initiate the Snapshot Agent, select Start Agent.

Now that you have tried a pull subscription, the following exercise shows you how to push a subscription from the Publisher's side of the operation.

11.3 Creating a Push Subscription

This exercise demonstrates how to push a subscription.

Estimated Time: 15 minutes

On the Tools menu, select Replication, Push Subscription to Others.
Expand Northwind, click on the Northwind publication, and then click Push New Subscription, which initiates the Push Subscription Wizard.
On the starting screen, click Next without selecting Advanced Options.
This screen inquires about the Subscriber you want to receive the publication. In this case, select your local instance of SQL Server and click Next.
In this screen you choose where the replica is placed; in this case create a new database called replicaPush . Click Browse or Create and then click the Create New button. As the database name, type ReplicaPush , and leave all settings on their default.
Determining the schedule for the Distribution Agent depends on how frequently you want updated data. Because you do not want updated data at all, select Using the Following Schedule and click Next.
Select Yes, Initialize Schema and Data because when you implement replication the first time, an initial snapshot must already have been applied at the Subscriber; only after this can the Subscriber receive incremental changes. Also select "Start the Snapshot..." to automatically start the Snapshot Agent.
You need the SQLServerAgent running for this subscription, which this screen corroborates.
This is the final screen of the Push Subscription wizard. Click Finish.
You have to start the Distribution Agent manually. To do this, select Replication Monitor, Agents, Distribution Agents, and then find the entry in the list of publications where the PublisherDB is Northwind and the type is Push.
After you have found it, right-click it and select Start Agent.

Review Questions

1:	Generally , which is the most important consideration: transactional consistency, latency, or autonomy? Why?
A1:	Generally, the most important consideration is transactional consistency; autonomy and latency considerations are usually up to you. Transactional consistency deals with the correctness of data, and this is a major factor. It is difficult to choose one factor as the most important, even generally speaking. For example, in merge replication poor transactional consistency is unavoidable, but the comparative level of transactional consistency is determined entirely by the latency. Latency in this case can be configured, but transactional consistency cannot be.
2:	According to what you have learned so far, what is the most common type of replication? Explain why it is so prominent.
A2:	Transactional replication is by far the most common type of replication. It offers a bit of everything: transactional consistency, latency, and autonomy. With the use of Updating Subscribers, it supports transactional consistency, medium-to-high autonomy, and low latency.
3:	Some suggest that merge replication might increase in popularity in the future. What's your opinion and why?
A3:	Merge replication has a chance to increase popularity because an increasing number of remote users need replicated data. Nevertheless, it still has conflicts that throw it back to where it began . These conflicts can be avoided with the use of region codes in tables that minimize the risk of same data being altered concurrently.
4:	What is the Snapshot agent's role in the replication process?
A4:	The Snapshot agent is responsible for preparing the schema and initial data files of published tables and stored procedures, storing the snapshot on the distribution server and recording information about the synchronization status in the Distribution database.
5:	How do Updating Subscribers work in SQL Server?
A5:	When Updating Subscribers are used, a trigger is located at the Subscriber database that monitors for changes and sends those changes to the publishing server.
6:	Why might you want heterogeneous interoperability?
A6:	SQL Server is built upon OLE-DB, which enables data to be replicated from SQL Server to any other database server, such as Sybase or Oracle. Using this feature is useful when one or more sites are not running simple SQL Server, but diverse database management systems instead.
7:	When do you think snapshot replication would be a good type to implement? Why?
A7:	Snapshot replication is exceptionally advantageous when replicated data is infrequently updated and modified. A snapshot strategy is preferable over others when data is to be updated in batches at distant intervals. Because data is replicated at a certain point in time and not replicated frequently, this type of replication is good for online catalogs, price lists, and so on.

Exam Questions

1:	You are the administrator of a SQL Server 2000 computer at your company's warehouse. All product orders are shipped out from this warehouse. Orders are received at 30 sales offices throughout the country. Each sales office offers a range of products specific to its own region. Each sales office contains one SQL Server 2000 computer. These servers connect to the warehouse through dial-up connections once a day. Each sales office needs data pertaining to only its own region. You need to replicate inventory data from the server at the warehouse to the servers at the sales offices. You have decided to use transactional replication. You want to minimize the amount of time needed to replicate the data. Which actions should you take? (Choose three.) Create one publication for each Subscriber. Create one publication for all Subscribers. Enable horizontal partitioning. Enable vertical partitioning. Use pull subscriptions. Use push subscriptions.
A1:	A, C, E. It is necessary to design the structure of the database so that the publications can include an article specific to each location. This will mean a compound Primary Key containing regional information and an article for each Subscriber that is horizontally partitioned based on the region. Because the connection is initiated by the Subscriber dial-up, you must also set up pull subscriptions. For more information, see the "Types of Replication" section.
2:	You are setting up a new snapshot replication environment for five large tables. To save disk space, you want to delete the data objects that are generated by snapshot replication. Where can you find these objects? Inside the MSSQL2000\Replication\Data directory on the Publisher. In the `sysArticles` table in the database that is being published. In the Mssql\Repldata directory in the Distributor. In the Mssql\Repldata directory on the Subscriber.
A2:	C. The default location for the snapshot information is in the Distributor in the Mssql\Repldata folder. Using the publication properties, this location can be altered to a newly desired position. Be sure to enable FTP access to this new location if FTP is being used to transfer the snapshots. See "Configuring the Distribution Server" for more details.
3:	The master price list for the shoes your company has in stock is maintained in SQL Server in the corporate office that is updated constantly. You want all your outlets to receive the updated prices as quickly as possible from the corporate office. Which sort of replication is best suited for a situation like this? Transactional replication Snapshot replication Merge replication Immediate replication
A3:	A. To receive updated prices as quickly as possible, use transactional replication. Merge replication is not appropriate because of the timing considerations, and snapshot replication would be too intensive , slow down operations, and produce a lot of latency (delay). Immediate replication does not exist, though immediate Updating Subscriptions are an option of transactional and snapshot replication types. Often exam answers attempt to fool you by using wording that is purposefully confusing, so read carefully and make no assumptions. For more details, see the "Types of Replication" section.
4:	You configure transactional replication on the Headquarters server and make numerous publications available. Later, a member of the Sales department subscribes to one of the publications. However, the only article from the publication that he needs is MonthlyRevenue. He needs data quickly and needs to avoid receiving the redundant data that would come from subscribing to the whole publication. What can be done to solve this? Change the style of replication being used to snapshot replication. Filter the publication so that only MonthlyRevenues can be replicated; push this subscription to the Sales department. Create a new publication that containerizes only a single article, MonthlyRevenues. There is no way to solve this problem. He needs to subscribe to the whole publication.
A4:	C. Of the options offered , creating a new publication is the best choice because it is the only option that will work. Creating filters can only be done vertically (column) or horizontally (rows). A more experienced individual could use stored procedures to subscribe to a single article, but this option is not present in the answer choices. For more information, consult the "Horizontal and Vertical Partitioning" section of this chapter.
5:	Southwest Specialists is a firm dealing with the production of valuable ornamental goods. It is using merge replication to publish customer and order information to its infrequently connected sales representatives. The DBA does not, however, want sales representatives to see actual amounts paid, so it deselects the Amounts column and then replicates the data. This article is made so that it contains only selected columns from a table. What is this called? Horizontal partitioning Horizontal filtering Vertical partitioning Column Restriction Filtering (CRF)
A5:	C. Vertical partitioning is the selection of some (but not all) columns in a table. Horizontal partitioning is the creation of an article based on some (but not all) rows in a table. Horizontal filtering is a term that is sometimes used as a synonym for horizontal partitioning. For additional details, consult the "Horizontal and Vertical Partitioning" section.
6:	What is the role of the Merge agent? Propagate updates and monitor and resolve conflicts on Publishers and Subscribers. Store records on the Distribution folder until needed and then merge them with transactions. Enable records to be broken down into smaller subsets . The Merge agent triggers when data modifications are made at the Subscriber after it has received a replica of data.
A6:	A. The Merge agent connects to the publishing server and the subscribing server and updates both as changes are made. The major role of the merge agent is to propagate the updates, and then monitor for conflicts. The agent is also responsible to apply the initial snapshot at the subscriber. See "Inside Merge Replication" for more information.
7:	You have set up a replication process using the default locations for the agents. Your process involves data being replicated to 20 Subscribers using a transactional replication strategy. How are the replication agents configured in transactional replication that uses a push subscription? The Log Reader agent resides on the Subscriber and the Distribution agent is on the Subscriber. The Log Reader agent resides on the Distributor and the Distribution agent is on the Subscriber. The Log Reader agent resides on the Subscriber and the Distribution agent is on the Publisher. The Log Reader agent resides on the Distributor and the Distribution agent is also located on the Distributor.
A7:	D. In transactional replication, the Log Reader agent, by default, resides on the Distributor. Because you are using a push subscription, the Distribution agent is also located on the Distributor, by default. If you were to use a pull subscription, the Distribution agent would by default be located on the Subscriber. You can alter the location on which the agent is run by using remote agent activation if the load would be better distributed by not using defaults. See "Inside Transactional Replication" for more details.
8:	Debra has just recently configured replication from the `Products` table to the `Analysis` table, located on the Headquarters and Research servers, respectively. Recently, Debra has noticed that replication failed for some unknown cause. Which of the following might be a likely cause of this problem? Choose all that apply. Replication requires three separate servers and Debra may not have implemented three servers. Replication cannot involve the `Master` , `Msdb` , `Tempdb` , and `Model` databases. Debra could have incorporated one of these databases with replication. Any replication process requires the use of at least two replication agents. Debra may have disabled one of the agents. Debra might have enabled Updating Subscribers, which is not allowed with any type of replication except for merge replication. The Transaction log of the Publisher may have been fully occupied, thus creating problem replication.
A8:	B, C. Out of the answer choices provided, only two were actually requirements: The `Model` , `Master` , `Msdb` , and `TempDB` databases cannot be replicated, and a replication process must contain at least two agents, depending on the replication options chosen . For more details, see the section titled "Publisher."
9:	Which of the following statements describe the role of the Log Reader agent? The Log Reader agent moves transactions from the Transaction log of the published database on the Publisher to the Distribution database or server. The Log Reader agent moves transactions and snapshot jobs held in the Distribution database out to the Subscribers. The Log Reader agent gives you the ability to run transactions in a sequence. The Log Reader agent deletes transactions at the click of a button.
A9:	A. The Log Reader agent moves transactions from the transaction log of the published database on the Publisher to the distribution database or server. For more information, see the section on the "Log Reader Agent."
10:	Transactional replication offers low site autonomy and merge replication offers high site autonomy. What is meant by "site autonomy"? The measuring of the consistency of transactions. How long data in the Subscriber can stay without being renewed. The independence of one site in relation to others. How many Subscribers and Publishers are involved in a replication process.
A10:	C. Site autonomy refers to one site's independence from all other sites for processing modifications. Autonomy measures the effect of your site's operation to another. A site that has full autonomy is completely independent of all other sites, meaning it can function without even being connected to another. See "Types of Replications" for more information on autonomy and other considerations.
11:	You work in a finance company where changes to the values in the `Finance` table are made quickly, and thus you want these incremental changes to be propagated to Subscribers as they occur. These Subscribers are frequently connected to the Publisher with a reliable connection. Which type of replication is used when you want updated changes at the server in almost real time? Snapshot replication Snapshot replication with Updating Subscribers Transactional replication Merge replication
A11:	C. Transactional replication is a good solution when you want updated changes at the server in almost real time. Because of the frequency of the changes, snapshot replication is not a good solution. Merge replication can be set up in a single direction but is generally used only when the Publisher and Subscribers make updates while connected or disconnected. See "Inside Transactional Replication" for more details.
12:	You and Josh are replicating data to multiple Subscribers who need to update data at various times and propagate those changes to the Publisher and to other Subscribers. These Subscribers need to be able to make changes offline and later synchronize data; this means that they need a replication strategy that offers high autonomy. Which type of replication offers almost complete site autonomy? Snapshot replication Snapshot replication and transactional replication Merge replication Transactional replication Transactional replication with Updating Subscribers
A12:	C. Merge replication allows the most flexibility and adds the most autonomy to the replication process, enabling the Publisher and Subscriber to work virtually independently. The Publisher and Subscriber can combine their results and updates at any time. See "Inside Merge Replication" for more details.
13:	Your company has just purchased an accounting application from a vendor. The application stores its data in a database named `Accounting` . The tables in this database contain columns that function as Primary Keys, but `PRIMARY KEY` and `FOREIGN KEY` constraints are not used. You need to replicate data from this database to another SQL Server computer. This server will use the replicated data to generate reports. Most reports will run each month, but the accounting department needs to have the ability to run reports at any time. Reports should be accurate through the last full working day. You cannot make any changes to the database, but you need to implement replication. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.) Implement merge replication. Implement snapshot replication. Implement transactional replication. Schedule replication to run continuously. Schedule replication to run during off-peak hours.
A13:	B, E. Because there is no Primary Key and no other changes to the database can be performed, the only alternative that does not require either is snapshot replication. Because the data does not need to be up-to-the-minute, a scheduled data refresh occurring overnight or during other non-peak times is most appropriate. See "Inside Snapshot Replication" for more details.
14:	Your company has just purchased an accounting application from a vendor. The application stores its data in a database named `Accounting` . The tables in this database contain columns that function as Primary Keys, but `PRIMARY KEY` and `FOREIGN KEY` constraints are not used. You need to replicate data from this database to another SQL Server computer. This server will use the replicated data to generate reports infrequently. Most reports will run each month, but the accounting department needs to have the ability to run reports at any time. Reports should be accurate through the last full working day. Disk space is at a premium and you want to conserve space as much as possible. Which action should you take? Use transactional replication. Use snapshot replication. Use a linked server for reporting. Use DTS to the reporting server. Use XML to the reporting server.
A14:	C. Use of a linked server will allow for heterogeneous data access but does not take up any additional disk space. The other solutions use some disk space; without this restriction any of the other solutions are possible. See "Heterogeneous Interoperability" for more details.

Exercises

11.1 Creating a Publication for Snapshot Replication

11.2 Creating a Pull Subscription

11.3 Creating a Push Subscription

Review Questions

Exam Questions

Suggested Readings and Resources