When you don't have time for the usual synchronization process
Suppose you're a DBA using SQL Server 7.0 to support online transaction processing (OLTP) databases of 50GB or more in a virtually 24 * 7 operation with a narrow maintenance window. Suppose also that your users require ad hoc query access to your data for either reporting purposes or data warehouse development. For this example, you would want to maintain a copy of the production data on a separate server to avoid database performance problems such as blocking caused by users issuing long-running queries against tables that programs are also issuing inserts and updates on.
Transactional replication is the process of reissuing against remote secondary servers those transaction commands that SQL Server has initially committed on a primary production server. Transactional replication is a popular method of maintaining an updated copy of a production database. The term for the remote secondary servers is Subscribers; the term for the primary production server is Publisher. Transactional replication maintains the subscriber servers' data with minimal latency and requires a minimum of DBA maintenance. Latency determines the amount of time between when SQL Server commits the transactions against the production database and when SQL Server applies those same transactions to the Subscribers' copy of the data.
SQL Server 7.0 lets you configure a transactional replication scenario in which one or more Subscribers receive the transactions committed against the Publisher. The Microsoft Snapshot Agent facilitates an initialization process that synchronizes the Publisher's schema and data with all Subscribers. Although using the Snapshot Agent is standard practice, its synchronization process could exceed your maintenance window, depending on the size of your database. However, you can set up transactional replication for your database without using the Snapshot Agent to initialize the database schema and data. For the example in this article, you must perform a full Publisher database restore to the Subscriber server during a nonproduction period to manually synchronize the schema and data.
Implementing Transactional Replication
Implementing transactional replication by backing up the Publisher database and restoring it at the Subscriber server requires performing the following tasks in sequential order. You must configure the Distribution, Publisher, and Subscriber servers; construct publications; restore the Publisher database at the Subscriber server; create the stored procedures required for replication to take place; disable publishing on the Subscriber database; and either push the subscriptions to the Subscribers or set up the Subscribers to pull the subscriptions. Let's walk through each of these steps.
Configuring the servers. The Distribution server receives all replicated transactions from the Publisher server, stores these transactions in the Distribution database, and distributes them as transaction commands to the Subscriber server. You must configure the Distribution server first. To configure the Distribution server (which can be the same server as the Publisher server), start SQL Server 7.0 Enterprise Manager. From the Enterprise Manager menu, select Tools, Replication, Configure Publishing, Subscribers, and Distribution. Click Next on the resulting Configure Publishing and Distribution Wizard window.
The next window asks you to select a distributor. Click Next, and the Specify Snapshot Folder window appears. You won't copy any data to the Snapshot folder because you're going to manually synchronize the data. So, click Next to customize the Distribution server's configuration. For this example, take the default option, which configures the current server as its Publisher and Distribution server and enables all registered SQL Server systems to be Subscriber servers. Then, click Next, Finish; SQL Server configures your server as a Distribution server and creates the Distribution database on it.
Constructing a publication. A publication is a set of articles. Articles are tables on which you can define horizontal or vertical filters so that whenever SQL Server commits a transaction against an article, SQL Server replays that transaction command at the Subscriber database. To create a publication through Enterprise Manager, select Tools, Replication, Create and Manage Publications. The Create and Manage Publications on YourServerName window appears, as do the databases from which you can define publications. Click Create Publication to access the Create Publication Wizard. Click Next to choose a publication database. For this example, select the Northwind database. Click Next to select the publication type from three options: Snapshot Publication, Transactional Publication, or Merge Publication. Choose Transactional Publication, and click Next. The next window asks whether you want to allow immediate-updating subscribers, which replicates data modifications made at the Subscriber database back to the Publisher database. Don't select this option, because the Subscriber database is read-only, and users will use it for ad hoc queries and other reporting purposes. The next window is Select Subscriber Types. Choose Servers running SQL Server 7.0, and click Next.
Now, you can specify the articles for your publication. For this example, choose the Employees table, and click Next. The Select your publication name and description window appears; enter Northwind_Pub1 in the Publication name text box, and click Next. Then, you customize the publication's properties. Use the default selection, which reads No, create the publication as specified. Click Next, Finish to complete the create-publication process.
Backing up the Publisher database and restoring the database at the Subscriber server. You're now ready to perform a full backup of the Publisher database on the Publisher server and restore it on the Subscriber server. When the backup completes, either shut down the SQL Server service or put the server into single-user mode. This step is crucial to ensuring data synchronization. Let's review how the replication process works to understand why this step is important.
Prev. page
[1]
2
3
next page 
