Chapter 12
Replication




by Mark Spenik

One of the exciting new features that first shipped with SQL Server version 6.0 and that has been enhanced in version 6.5 is data replication.


NOTE: I throw in the fact that the data replication feature ships with the standard product because several other RDBMS vendors treat replication as a separate product for which you must pay extra.

In a nutshell, replication is the capability to reliably duplicate data from a source database to one or more destination databases. Using Microsoft SQL Server replication, you can automatically distribute read-only transaction-based data from one SQL Server to many different SQL Servers through ODBC (Open Database Connectivity). SQL Server 6.5 extends replication by allowing you to replicate non-SQL Server ODBC sources such as an Oracle RDBMS or a Microsoft Access database. When would you want to use SQL Server replication? Here are some examples:

SQL Server replication uses a loose consistency distributed data model. Loose consistency means that the data synchronization between the source and destination server does not occur simultaneously. Before going into more detail, look at another distributed data model: tight consistency. A tight consistency distributed data model can be accomplished with SQL Server using two-phase commits. In a tight consistency model, all transactions are committed or rolled back on all the servers so that the data is in synch 100 percent of the time. In a loose consistency model, transactions are committed or rolled back on a source server. The transactions on the source server are then replicated asynchronously to subscribing servers. The big difference between the tight consistency model and the loose consistency model is that, on the loose consistency model, there is some lag time between when changes are made to the source server and when they are replicated to the destination servers (that is, the databases are temporarily out of synch).


TIP: SQL Server replication is not designed to be used in a hot backup server situation (that is, for another machine to be used when the primary machine goes down). The loose consistency model used by SQL Server replication does not keep the two servers "in synch" at all times.

Replication Overview

SQL Server can perform replication by using the existing transaction log. If a table is marked for replication, the changes made to the table are automatically replicated to other servers. It sounds simple, but in reality, the replication process is a little complicated. Before getting into the details of replication, it is important to understand some of the terminology used when discussing SQL Server replication. In Figure 12.1, SQL Server A is replicating data from the pubs database to several other servers. Using Figure 12.1, review the terminology used in SQL Server replication defined in the following sections.

Figure 12.1.
Overview of replication.

Publish and Subscribe

SQL Server replication uses a publish and subscribe metaphor. Servers publish publications to which other servers can subscribe. A SQL Server that makes data available to other servers for subscribing purposes is called a publisher. For example, Server A in Figure 12.1 is a publisher. A SQL Server that subscribes to a publication published by another SQL Server is said to be a subscriber (an example of a subscription server is Server B in Figure 12.1). A SQL Server that contains the distribution database is said to be a distributor (the distribution server is Server A in Figure 12.1).

Publication and Articles

A publisher publishes a collection of one or more articles called a publication. The publication shown in Figure 12.1 contains the authors, titles, and publishers article. An article is the basic unit of replication and can be a table or a subset of a table.


NOTE: Articles are always associated with a publication and cannot be published by themselves.

Publications can contain one or more of the following:

A vertical partitioned table is an article that uses a filter to select only certain columns of a table. A horizontal partitioned table is an article that uses a filter to select only specific rows in the table.

The following cannot be published:

In SQL Server 6.5, data replication for text and image datatypes are supported with fewer restrictions then in SQL Server 6.0. See the Microsoft documentation for the exact restrictions on image and text datatypes.

SQL Server replication provides a level of security. For example, publications can be selectively marked restricted or unrestricted to different subscribing servers. In Figure 12.1, Server B has been restricted from viewing the company_finance publication, which is marked restricted.


NOTE: Subscribing servers can see only the publications to which they have access.

Server Roles

SQL Server can play one or more of the following roles during the replication process:

Non-SQL Server systems such as Oracle and Microsoft Access can only be sub-scribers.

Replication Models

SQL Server can participate in one or more replication roles. For example, in many cases, a publication server also serves as a distribution server and can also subscribe to other publications from other publishers (in which case, the same server that was acting as a publisher and distributor is also acting as a subscriber). When setting up replication, there are several publisher and subscriber models you can use. Figure 12.1 is an example of a single publisher server providing information to multiple subscribers. Figure 12.2 shows a single publisher server using a distribution server to replicate data to subscribers.

Figure 12.2.
Replication model: a single publisher using a distribution server.


The replication model in Figure 12.3 shows multiple publishers and multiple subscribers.

Figure 12.3.
Replication model: multiple publishers and multiple subscribers.


Another replication model, similar to the one shown in Figure 12.3, is multiple publishers to a single subscriber. The important point to understand is that there are many ways to set up SQL Server replication. Study the various replication models and plan before implementing.


TIP: Microsoft SQL Server documentation covers the different replication models in much more detail. Make sure that you review the replication chapters before implementing replication.

Walking Through the Replication Process

Now that you have a general understanding of replication terminology, walk through the SQL Server replication process using Figure 12.4.

Figure 12.4.
An overview of the SQL Server replication process.


To begin the overview of SQL Server replication, assume that Server A has published the entire pubs database in a publication titled MyPubs for replication. Server A is also the distribution server. Before you begin your walk through, you need to learn about some of the components that make up SQL Server replication:


NOTE: The log reader process, synchronization process, and distribution process are all part of the SQL Executive service.

Now you are ready to walk through two of the main replication processes: synchronization and replicating data. Referring to Figure 12.4, Server B subscribes to the MyPubs publication. Before data replication can begin between the two databases, the synchronization process must complete successfully.

Server A and Server B Perform Synchronization

When Server B subscribes to Server A's pubs database publication, the subscriber, Server B, has the choice of accepting several different synchronization modes:

Synchronization Modes The default synchronization mode is automatic, which means that SQL Server performs the synchronization process automatically at a scheduled interval. Manual synchronization requires you to synchronize the databases and notify SQL Server when the synchronization process has completed.


TIP: Use manual synchronization when dealing with very large tables or a slow communications line. The files required for synchronization can be copied to a tape or other media and applied to the destination server or servers.

The no synchronization option assumes that the articles in the source are already in synch with articles in the destination. SQL Server does nothing to verify that the databases are synchronized--that task is up to you. The last option, snapshot only, is also referred to as a table refresh. If snapshot synchronization is selected, SQL Server ignores any changes to the published articles and instead performs the synchronization process at defined intervals (that is, it refreshes the destination tables). Automatic Synchronization For this example, assume that Server B selects automatic synchronization during the subscription process. The distribution server creates two files, referred to as a synchronization set, in the replication working directory (the default is \REPLDATA off the SQL Server home directory). The synchronization set consist of a BCP data file with the actual data of the subscribed articles and the article's table schema file.


TIP: Schema files created for replication have a SCH extension; the data files have a TMP file extension.

Once the synchronization set is created, a synchronization job is added to the distribution database. The distribution process reads the distribution database and applies the synchronization file set to the subscribing server, (in this example, Server B). First the schema file is applied to create the table schema. The table information is then copied to the subscribing server using BCP. The distribution server is notified that synchronization has completed, and Server A can begin to replicate the publication MyPubs to Server B.


NOTE: Once all other subscriptions have acknowledged successful synchronization, the TMP files are removed from the replication working directory.

Any transactions that occurred to the published articles after the subscribing server first subscribed but before the synchronization process occurred are then replicated to the subscriber.


CAUTION: Be careful when trying to perform synchronization with tables that exist on the publishing and the subscribing server, such as the pubs database. The distribution process attempts to drop a table on the subscribing server if it exists during the synchronization process. If the table being dropped has declarative referential integrity defined and is referenced, for example, by a foreign key constraint from another table, the table cannot be dropped and the synchronization process will fail. If this occurs, use manual synchronization (or no synchronization mode), or do not drop the table during synchronization. With SQL Server 6.5, constraints can be turned off during the replication process by using the NOT FOR REPLICATION option with the ALTER and CREATE table statements.

Articles on Server A Are Modified and Replicated The two servers, shown in Figure 12.4, are synchronized. Modifications are made to articles (tables) on Server A. Because the tables are published, the transactions are marked in the pubs database transaction log for replication. The log reader process, searching transaction logs for marked transactions, creates SQL statements for any marked transactions found and sends the SQL statements to the distribution database.


NOTE: Transactions marked for replication in a published database's transaction log remain in the transaction log until the distribution process copies the marked transactions to the distribution database. That is, transaction log backups do not truncate the transactions in the transaction log marked for replication until they have been copied to the distribution database.

The distribution process replicates the transactions found in the distribution database to the subscriber (Server B) using the preconstructed SQL statements or stored procedures, removes the transactions from the distribution database, and updates the Mslast_job_info table on the subscription server.


NOTE: Using SQL statements to perform the data modifications rather than sending the actual data greatly reduces the network traffic required to perform replication. The use of SQL statements makes it quite clear why the databases must first be synchronized. Using SQL statements also highlights the point of keeping subscribing databases read-only in practice so that SQL statements applied on the source database have the same effect on the records in the destination database.

Planning for Database Replication

Unlike many other database operations you perform using SQL Server, replication requires some planning before implementing. The following sections review the requirements before setting up replication.

Trusted Network Connections Are Required

SQL Servers participating in replication are required to use a trusted connection, which means that you must have trusted relationships established for NT servers residing in other domains. The SQL Server acting as the distribution server must have a client configuration default protocol setting of named-pipes or multi-protocol.


NOTE: SQL Server replication works with any of the security modes (standard, integrated, or mixed). But because replication uses a trusted connection, the distribution server always connects to the subscribing servers in an integrated security mode.

32-Bit ODBC Drivers Must Be Installed

SQL Server replication uses ODBC ( Open Database Connectivity) to replicate the data. The 32-bit ODBC drivers must be installed on all SQL Servers involved with replication.


NOTE: The ODBC drivers are installed automatically during setup. You do not have to configure the ODBC sources.

Memory Requirements

If the server is a distribution server or a distribution server and publication server, then the NT server requires at least 32M of memory with 16M of memory assigned to SQL Server.

Character Set

In Microsoft SQL Server 6.5, replication between SQL Servers with different character sets (that is, code pages) is supported. For Microsoft SQL Server 6.0, all version 6.0 SQL Servers participating in replication must have the same character set.

Adequate Transaction Log Space for Publisher Databases

Because transactions remain in the transaction log until moved to the distribution database, your publishing databases may require extra space for the added overhead of active replication transactions in the log.

All Tables You Want to Publish Have Primary Keys

You cannot publish a table that does not have a primary key declared for the table (tables without primary keys do not appear when you select articles for publication). In SQL Server 6.5, if the table uses snapshot replication, you do not need a primary key. You must use the stored procedure sp_addarticle to add an article without a primary key for snapshot replication because only tables with primary keys show up in the Enterprise Manager.

Prereplication Checklist

Following is a checklist you can use before setting up replication:

Trusted Network

Memory Requirements

Other

Creating the Distribution Database

Now it's time to walk through the steps required to set up replication. First, you examine how to set up the distribution database. Follow these steps to create the distribution database, using the Enterprise Manager:

  1. Select the server on which you want to install the distribution database.

  2. From the Enterprise Manager menu, select Server and then select the Replication Configuration option. A drop-down menu appears (see Fig- ure 12.5).

    Figure 12.5.
    The Replication Configuration menu and option list.

  3. Select the Install Publishing option. The Install Replication Publishing dialog box appears (see Figure 12.6).

    Figure 12.6.
    The Install Replication Publishing dialog box.

  4. To install the distribution database, use the default option, Local - Install New Local Distribution Database. Select an existing device for the data and the transaction log of the distribution database using the combo selection boxes or create a new device by selecting <new> from the Data Device and Log Device combo boxes.


    NOTE: In most cases you encounter, you typically use a distribution server that has already been set up, so select the second option shown in Figure 12.6 (Remote - Use Existing Remote Distribution Server).
  5. Once you select the proper devices, enter the size for the database and transaction log and click the OK button to create the distribution database.

  6. A message box appears when the process completes, stating the success or failure of the operation. If the operation succeeds, you can begin to add publishing databases and subscribers by selecting the Yes option from the message box.


NOTE: As you do for every database, put the transaction log for the distribution database on a separate device. The recommended minimum size for the distribution database is 30M for the data and 15M for the transaction log. To help determine the correct amount of space, determine the number of transactions, average transaction size, and the retention time of the data (that is, typical database size requirements).


Troubleshooting Distribution Database Setup
While creating the distribution database, you can easily run into a few gotchas. Here is a list of a few of the problems I have encountered while setting up replication distribution databases:

Figure 12.7.
The message box for error number 21271.

The process of creating a distribution database can be performed manually by following these steps:

    1. Create the distribution database and transaction log on separate devices.
    2. Run the install script INSTDIST.SQL from the distribution database. The script file adds all the required system tables and stored procedures.
    3. Dump the transaction log for the distribution database using the NO_LOG option.
    4. Use the system stored procedure sp_serveroption to set the option dist to TRUE for the server.

What Happens When the Distribution Database Is Created?

When you create the distribution database, you create a SQL Server database named distribution. The distribution database is created with the standard database system tables, as well as the following user tables:

Configuring Replication Publishing and Distribution Options

Once the distribution database has been successfully installed, or you have been given permission to publish to a remote distribution server, you can then configure a server to be a publisher. Using the Enterprise Manager, you can set up which servers can subscribe to published databases, which publisher servers can use the distribution server as a remote distribution server, and which replication schedule used is to replicate the data to each subscribing server.


NOTE: To set up SQL Server as a publisher, you must have one of the fol-lowing: A local distribution database Access to a remote distribution database If you have a local distribution database on your server (that is, your server is acting as a distribution server), you can do the following: Set the distribution working directory Allow other servers access to your distribution database for publishing Both distribution servers and publisher servers can control which servers allow access to published articles and which databases on your local server can publish articles.

Configuring Publishing To set up publications options, following these steps:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Server and then select Replication Configuration. From the menu that appears, select Publishing. The Replication Publishing dialog box appears (see Figure 12.8).

  3. To enable a database for publication, select the Enable checkbox next to the appropriate database.

  4. To enable a server to subscribe, select the server's Enable checkbox.

  5. To save the changes, click the OK button. To ignore changes made, click the Cancel button.

Setting Distribution Options You can administer various distribution options for each subscriber by clicking the Distribution Options button in the Replication Publishing dialog box (refer to Figure 12.8). When you click this button, the Distribution Options dialog box appears (see Figure 12.9).

Figure 12.8.
The Replication Publishing dialog box.


Figure 12.9.
The Distribution Options dialog box.


The Distribution Options dialog box enables you to set replication scheduling to subscribing servers and ODBC options such as login name and password for non-SQL Server subscribing servers. Non-SQL Server ODBC subscribers are detailed later in this chapter; for now, examine the scheduling options. If you select the Continuous schedule, transactions are replicated continuously to the subscribing servers. If you do not want continuous replication, you can specify a schedule for how often transactions are replicated to the subscribing service.

To allow a remote publisher server to use this SQL Server as a remote distribution server, select the Distribution Publishers button in the Replication Publishing dialog box. The Replication Distribution dialog box appears (see Figure 12.10).

Figure 12.10.
The Replication Distribution dialog box.


To allow a remote publishing server to use the local server as a distribution server, select the Enable checkbox next to the appropriate remote server and click the OK button.

Configuring Replication Subscribing

To allow local databases to subscribe to publications or to allow publishing servers to replicate data to the local server, you must enable permissions for the publication servers and the local databases using the Enterprise Manager.


NOTE: Setting up subscribers enables you to determine which databases can receive published articles and which remote publisher servers you can receive publications from.

To set up subscription options, follow these steps:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Server and then select the Replication Configuration option. From the menu that appears, select Subscribing. The Replication Subscribing dialog box appears (see Figure 12.11).

    Figure 12.11.
    The Replication Subscribing dialog box.

  3. To enable a server to subscribe, select the Enable checkbox next to the server or database. When you are done, click the OK button.

Viewing Replication Topology

Once you have set up your replication, you can view the overall replication topology. Using the Enterprise Manager, follow these steps:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Server and then select the Replication Configuration option. From the drop-down list box, select Topology. The Replication Topology dialog box appears (see Figure 12.12).

    Figure 12.12.
    The Replication Topology dialog box.


TIP: The Replication Topology dialog box gives you a graphical view of how replication is set up between servers. If a server is a distribution server, the server has the abbreviation DIST next to its picture. Publishing servers have the abbreviation Pub and subscribing servers have the abbreviation Sub. Notice that non-SQL Server ODBC servers are also graphically displayed. You can add a subscriber by selecting a SQL Server from the Enterprise Manager and dropping the server into the topology window.

Managing Publications

After you set up your SQL Server as a publisher, you can create publications to which other SQL Servers can subscribe. Changes made to your published databases are then replicated to subscribing databases. Remember that a publication consists of one or many articles (tables). To create a publication using the Enterprise Man-ager, follow these steps:

  1. Select the server you want to use to manage publications from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Manage and then select Replication. From the menu that appears, select Publications. The Manage Publications dialog box appears (see Figure 12.13).

    Figure 12.13.
    The Manage Publications dialog box.

  3. Published databases are displayed in the Publications list box. The databases have the typical Enterprise Manager drill-down features. Drilling down through a database displays the publications in the database. Dril-ling down through a publication shows the articles in the publication. If you drill down through the article(s), you can see the current subscribers.


    TIP: To remove a publication, use the Remove button in the Manage Publications dialog box. To modify a publication, use the Change button in the Manage Publications dialog box.

  4. To create a new publication, click the database to which you want to add the publication and then click the New button. The Edit Publications dialog box appears (see Figure 12.14).

    Figure 12.14.
    The Edit Publications dialog box.

  5. The Edit Publications dialog box enables you to create a new publication and assign articles to the publication.

  6. To create a publication, enter the name of the publication in the Publication Title text box. In the Description text box, enter the description for the publication.

  7. To add tables to the publication, select a table from the Database Tables list in the Edit Publications dialog box and click the Add button. The table is added to the Articles in Publication list.

  8. To remove an article from the publication, select the article and click the Remove button.


    NOTE: The Replication Frequency area, shown in Figure 12.14, displays the different methods of replication for the publication. If you select the Transaction Based Replication Frequency radio button, changes in the transaction log are marked and copied to the distribution database and later applied to the destination databases. The Scheduled Table Refresh option is applied at scheduled intervals and is sometimes referred to as a snapshot. When a table refresh is performed, the destination table is dropped and re-created. The data in the source table is copied into the destination (or snapshot) table.
  9. To add a new publication, click the Add button.

Synchronization Options

To set synchronization options for a publication, use the Synchronization page in the Edit Publications dialog box (see Figure 12.15). You can control the type of BCP formats used (Native or Character format) and you can schedule the frequency of automatic synchronization by clicking the Change button.

Figure 12.15.
The Synchronization page in the Edit Publications dialog box.

Security Options

The Security page in the Edit Publications dialog box enables you to restrict which servers can view the publication (see Figure 12.16). The default is unrestricted.

Figure 12.16.
The Security page in the Edit Publications dialog box.

Managing Articles

To edit an article, to apply a filter, or to add scripts, select the article and click the Edit button in the Edit Publications dialog box (refer back to Figure 12.14). The Manage Article dialog box appears (see Figure 12.17).

Figure 12.17.
The Manage Article dialog box.


Using the Manage Article dialog box, you can perform vertical partitioning by deselecting columns for the table listed in the Replicated Columns area or you can perform horizontal partitioning by applying a filter in the Restriction Clause text box. For example, in Figure 12.17, a restriction has been added to the pubs database authors_table column, au_fname, to select only records where au_fname is equal to John. If you want to further tune the article by adding stored procedures or editing a script, select the Scripts tab. The Manage Article dialog box with the Scripts page active is shown in Figure 12.18.

Figure 12.18.
The Scripts page in the Manage Article dialog box.


The Scripts page enables you to specify the type of replication mechanism used by SQL Server during replication.

The radio buttons marked Insert Command, Update Command, and Delete Command represent the default replication mechanism used by SQL Server when constructing SQL statements for each of the commands. Select the Custom checkbox if you want to make one of the following choices:


Using Stored Procedures with Replication
Why use stored procedures during replication? Let's start with the obvious: Use stored procedures to improve the performance of the replication process. Stored procedures improve performance by using precompiled SQL statements during replication. Stored procedures also reduce network traffic because you pass only the stored procedure name and parameters instead of the entire SQL statement. You can also use stored procedures to perform custom processing on the subscribing server such as reformatting the data to simplify end-user queries.

The customer stored procedures may be as simple as translating an integer status field to a character field that reports "SUCCESS" or "ERROR". Here's an example of using custom replication stored procedures to reduce the number of joins (that is, denormalizing a table for a data warehouse). Suppose that you are replicating to a database that is to be used by several end users. The primary table the end users are concerned with, My_Customer, consists of several ID columns that reference description values in other tables. You want to prevent the end users from having to join the My_Customer table to any other tables to retrieve the descriptions associated with the ID columns (that is, you want to flatten the table).

Here are the steps required to perform this operation using replication: Create an article on the publishing server for the table My_Customer. Using the Scripts page in the Manage Article dialog box (refer back to Figure 12.18), select the Custom boxes and add stored procedure names for the data modification statements you plan to support (for example, INSERT, UPDATE, and DELETE). Subscribe the end-user database server to the article set up on the publishing server for the My_Customer table. Select No Data Synchronization for the type of replication synchronization.

On the subscribing server, create the My_Customer table with a different table schema than the one found on the publishing server (that is, replace the ID columns' datatypes with the datatypes for the description columns). Create stored procedures for all the data modifications you plan to support (for example, INSERT, UPDATE, and DELETE) on the subscribing server. For example, the INSERT stored procedure uses the ID columns of the My_Customer table passed in during replication to retrieve the description columns from the other tables in the database and inserts them (along with the other associated information for a row) into the subscriber's My_Customer table. Once everything is in place, you must sync the data between the two tables.

The easiest and safest way to sync the data is to copy all the data from the publisher server's My_Customer table to a holding table on the subscribing server (use the stored procedures DUMP and LOAD, or use BCP). Create a cursor that reads each row in the holding table and executes the replication INSERT stored procedure to place the information in the correct format in the subscriber's My_Customer table. Once the two tables are synched and the data is the same, you are ready to go.

When data is changed on the publisher, the changes are replicated to the subscribing server. The stored procedure is executed on the subscribing server, retrieving the correct columns and inserting them in the subscribing server's copy of the My_Customer table. You have created a more user-friendly table on the subscribing server by using custom stored procedures. One more thing: If you use stored procedures for replication, all subscribing servers must have the stored procedures in the subscribing database. The stored procedures do not have to exist on the publishing server.

If you are editing an existing article, the Creation Script text box in the Manage Article dialog box contains the path and filename of the synchronization schema file. To generate the schema file, click the Generate button.


TIP: To set the schema options such as table index options, truncating the table, dropping or not dropping the table during synchronization, or including the primary key, click the Generate button in the Manage Article dialog box to display a schema option dialog box.

The Advanced button in the Manage Article dialog box allows you to add a stored procedure to filter the replication results. You may want to use this feature to fur-ther limit the results of an article that has already been partitioned horizontally or vertically. The stored procedure must follow this format:

If SQL Statement Return 1 Else Return 0

The Use Column Names in SQL Statements checkbox adds column headers to replication INSERT statements for the selected article. Check this option when the column order of the subscribing table does not match the column order of the publishing table.

When you have completed any modifications or changes to the article, click the OK button to save your changes; click the Cancel button to ignore your changes. Review the other tab options available in the Edit Publications dialog box.

Managing Subscriptions

SQL Server supports two types of subscription methods: a pull subscription and a push subscription. A pull subscription is when you are managing the subscribing server and you select one or more publications to subscribe to. A push subscription occurs when you are managing a publication server and you set up subscribers from the publication server (that is, you push the article out to other servers).


NOTE: Push subscriptions cannot be performed at the publication level. Only articles can be pushed.

Pull Subscriptions

A pull subscription is when you are the subscribing server and you subscribe to one or more publications. To manage a pull subscription, follow these steps:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Manage and then select Replication. From the menu that appears, select Subscriptions. The Manage Subscriptions dialog box appears (see Figure 12.19).

    Figure 12.19.
    The Manage Subscriptions dialog box.

  3. To subscribe to a publication or an article, select a publication or an article from the Publications list. To subscribe to the selected publication or article, click the Subscribe button. The Subscription Options dialog box appears (see Figure 12.20).

    Figure 12.20.
    The Subscription Options dialog box.



    NOTE: To remove a subscription, use the Un-Subscribe button on the Manage Subscription dialog box.
  4. Select the destination database for the replicated publication from the Destination Database combo box. Select the synchronization method and then click the OK button. The subscription has been added. The process is completed when the data synchronization occurs between the source and destination databases.


TIP: The Sync Completed button in the Manage Subscriptions dialog box informs SQL Server that the synchronization process has been completed (use only for manual synchronization). In most cases, you probably want to use automatic synchronization methods and allow SQL Server to handle the required synchronization steps. There are exceptions, however. If the database you are replicating is extremely large or the servers you are replicating to are linked with a slow data line, use the manual replication mechanism instead. If you are creating a duplicate database on another server using a current backup of the database or if you are positive that the two databases participating in replication are identical, you may want to use the no synchronization option.

Push Subscriptions

In a push subscription, you set up subscribers from the publication server (that is, you push the article out to other servers). To manage a push subscription, follow these steps:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Manage and then select Replication. From the menu that appears, select Publications. The Manage Publication dialog box appears (refer back to Figure 12.13). Select the publication you want to push and click the Change button. The Edit Publications dialog box appears (refer back to Figure 12.14). You can push an entire publication or a specific article. In this example, you push a single article (for an example of pushing an entire publication, refer to the following section, "SQL Server Replication to ODBC Subscribers"). To push an article to a subscribing server, select an article and click the Edit button. The Manage Article dialog box appears (refer back to Figure 12.17). Click the Subscribers button and the Publication Subscribers dialog box appears (see Figure 12.21).

    Figure 12.21.
    The Publication Subscribers dialog box.

  3. Select a server to push the article to and click the Subscribe button. The Subscription Options dialog box appears (see Figure 12.22).

    Figure 12.22.
    The Subscription Options dialog box.

  4. Type the name of the destination database and select the synchronization method. Click OK to push the subscription to the selected server and database.

SQL Server Replication to ODBC Subscribers

SQL Server 6.5 can replicate to non-SQL Server ODBC subscribers such as Microsoft Access, IBM DB2, Sybase, and Oracle databases. As stated earlier in this chapter, non-SQL Server ODBC sources can only be subscribers, they cannot be publishers.


NOTE: Because non-SQL Server subscribers use ODBC, I'm sure that the list of possible ODBC subscribers will increase. For an updated list of supported ODBC subscribers, refer to your Microsoft documentation or check the SQL Server forums on the Internet or CompuServe. From this point on in this chapter, when we discuss ODBC subscribers, we mean non-SQL Server ODBC sources.

ODBC subscribers must be set up by the publishing server using the push subscription method. Each ODBC subscriber participating in SQL Server replication has its own individual requirements and restrictions. For example, when creating the ODBC DSN with Oracle, you must include a username; with Microsoft Access DSN, you must enter a username and a password. Check out the Microsoft SQL Server documentation for the various restrictions that apply between Microsoft SQL Server and the selected ODBC subscriber.

Here are a few known restrictions that apply to all ODBC subscribers:

Also review the datatype conversions between Microsoft SQL Server and the ODBC subscriber. The following sections describe how to set up an ODBC subscriber using Microsoft Access 7.0.

Step 1: Create an ODBC Data Source Name

The first step in setting up replication to an ODBC subscriber is to create a system ODBC Data Source Name (DSN) for the subscribing server. To create a system ODBC DSN, follow these steps:

  1. Double-click the ODBC icon located in the control panel to start the ODBC Administrator (see Figure 12.23).

    Figure 12.23.
    The ODBC Administrator dialog box.

  2. Click the System DSN button. The System Data Sources dialog box appears (see Figure 12.24).

    Figure 12.24.
    The System Data Sources dialog box.



    TIP: The System ODBC DSN must be set up on the SQL Server that is participating as the distribution server (that is, the ODBC DSN cannot be set up on a client machine or subscriber server).
  3. To add a new system DSN, click the Add button. A list of installed ODBC drivers appears. Select the correct ODBC driver for your subscribing server (for this example, select Microsoft Access). Once you select the correct ODBC driver, click OK. The ODBC Microsoft Access 7.0 Setup dialog box appears (see Figure 12.25).

    Figure 12.25.
    The ODBC Microsoft Access 7.0 Setup dialog box.



    NOTE: The look of the ODBC Setup dialog box varies for different data sources. For this example, Microsoft Access is used. Compare the Microsoft Access Setup dialog box in Figure 12.25 to the ODBC Setup screen for SQL Server (shown in Figure 12.26).

    Figure 12.26.
    The ODBC SQL Server Setup dialog box.

  4. Enter the Data Source Name for the Microsoft Access database. The Data Source Name is how the ODBC subscriber will be referenced by SQL Server. Select the Access database to push the replicated data to and click the Advanced button. Add a user name and password for the Access database (this is one of the requirements for setting up an Access ODBC subscriber). Once you have entered the correct information, click the OK button to add the ODBC subscriber.


TIP: The Access database can be located on a network drive.

Step 2: Register the ODBC Source as a Subscribing Server

The next step is to add the ODBC DSN to SQL Server's list of subscribing servers. To add an ODBC DSN to the list of subscribing servers, follow these steps:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Server and then select Replication Configuration. From the menu that appears, select Publishing. The Replication Publishing dialog box appears (see Figure 12.27).

    Figure 12.27.
    The Replication Publishing dialog box.

  3. Click the New Subscriber button. The New Subscriber dialog box appears. Click the ODBC Subscriber button. The New ODBC Subscriber dialog box appears (see Figure 12.28).

    Figure 12.28.
    The New ODBC Subscriber dialog box.

  4. Use the combo box to select the correct ODBC DSN. Enter the login ID and password if required. The login ID and password are optional because they may be stored when the system DSN is created. In the case of Microsoft Access, leave the login and password fields blank because this information is stored with the ODBC system DSN. To register the ODBC subscriber, click the OK button. This action adds the DSN to the SQL Server system table sysservers.

Step 3: Push the Publication or Article to the ODBC Subscriber

Once the ODBC DSN has been registered as a subscribing server, follow these steps to push a publication or article to the ODBC subscriber:

  1. Select the server from the Enterprise Manager.

  2. From the Enterprise Manager menu, select Manage and then select Replication. From the drop-down list box, select Publications. The Manage Publication dialog box appears (refer back to Figure 12.13). Click the Change button; the Edit Publications dialog box appears (refer back to Figure 12.14). You can push an entire publication or a specific article. In "Push Subscriptions," earlier in this chapter, a specific article was used as an example; this example pushes a publication. Select the publication you want to push and click the Subscribers button; the Publication Subscribers dialog box appears (see Figure 12.29).

    Figure 12.29.
    The Publication Subscribers dialog box.

  3. Select the subscribing server to push the publication to and click the Subscribe button. The Subscription Options dialog box appears (refer back to Figure 12.20). Select the type of synchronization mode and click the OK button. The push subscription is complete.


TIP: When performing replication to an Access database, make sure that the type of synchronization method selected is Bulk Data Copy - Character Format. You can also check the status of replication to ODBC sources just as you can check the status of replication to another SQL Server: by using the Task Scheduling dialog box.

Wrapping Up

To wrap up this chapter on replication, the following sections review a last few pointers, tips, and reminders when dealing with SQL Server replication.

Subscribing (Replicated) Databases Are Read-Only

Databases that are subscribed from a publisher server should not be modified on the subscribing server and should be used for read-only purposes. SQL Server does not prevent you from modifying the data, but if you do modify data in the replicated databases, the database will become out of synch with the published database. Also, any changes you make in the replicated database can easily be overwritten by changes made in the published database. Suppose that you modify an amount field in row 1 of a table called bank_account on the subscribing server's replicated database.

You set the value of the field to $1,000.00. A few minutes later, someone on the publishing database modifies the same record but they set the value to -$1000.00. A few minutes later, the changed record is replicated to your database, overwriting your changes--unknown to you! The values in your database are no longer valid (another reason to use SQL Server replication for read-only databases). SQL Server replication does not have a mechanism that allows you to replicate records to various databases and make changes on any of the databases (subscriber or publisher). Then replicate all the changes back to subscribing servers, notifying users of record conflicts (records modified by more than one server). Do not be discouraged--the read-only database replication scheme is very powerful and useful in many real-world scenarios.

Database Read-Only Option

Replication cannot occur on a subscribing database if the read-only database option is set to TRUE.

Table Constraints

SQL Server 6.5 allows you to disable constraints during replication.

Replication Is Transaction Driven

Remember that SQL Server replication is transaction driven. Keep this in mind as you publish and subscribe various articles and monitor the number of transactions that are replicated. Pay close attention to update transactions. If SQL Server performs a deferred update on a table, the update consists of a delete followed by an insert. Updating 10,000 records can translate to 10,000 deletes and 10,000 inserts for a total of 20,000 transactions. If you intend to perform many updates, you may want to examine the rules SQL Server uses to perform direct updates, better known as updates in place. Rather than performing a DELETE and an INSERT, SQL Server updates the record in place.

Deadlocks

Because of exclusive table-lock conditions, deadlocks can sometimes occur between the distribution process and the log reader. If you experience deadlocks between the two processes, try reducing the commit batch size using the Enterprise Manager; alternatively, reduce the frequency of the distribution process.

ODBC Subscribers

When setting up the system DSN for an ODBC subscriber, you must add the DSN on the Windows NT server with the distribution server. Remember, non-SQL Server ODBC sources can only be subscribers.

Between the Lines

Following are some of the important things to remember for SQL Server replication:

Summary

Replication is a new and exciting technology that will play an important part in many real-world solutions. As a Microsoft SQL Server DBA, you must fully understand how to correctly set up and administer database replication and how to correctly use replication to benefit your company or organization.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.