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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Following is a checklist you can use before setting up replication:
Trusted Network
Memory Requirements
Other
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:
Figure 12.5.
The Replication Configuration menu and option list.
Figure 12.6.
The Install Replication Publishing dialog 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:
- Do not try to create the distribution database while connected to a SQL Server that is on the same machine as the Enterprise Manager using the default server name of (local). You will get midway through the distribution process and encounter error number 21271 (see Figure 12.7 for actual error message). Make sure that you connect to the server using the server name.
- Make sure that you are using trusted connection or you will not be able to replicate.
- If you run into problems while creating the distribution database, check the file INSTDIST.OUT in the \INSTALL directory for errors.
- If your SQL Server is not configured with at least 16M of memory, you are warned and will be unable to build the distribution database until you allocate at least the minimum amount.
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:
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:
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:
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.
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:
Figure 12.11.
The Replication Subscribing dialog box.
Once you have set up your replication, you can view the overall replication topology. Using the Enterprise Manager, follow these steps:
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.
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:
Figure 12.13.
The Manage Publications dialog box.
Figure 12.14.
The Edit Publications dialog box.
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.
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.
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.
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.
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:
Figure 12.19.
The Manage Subscriptions dialog box.
Figure 12.20.
The Subscription Options dialog box.
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.
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:
Figure 12.21.
The Publication Subscribers dialog box.
Figure 12.22.
The Subscription Options dialog box.
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.
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:
Figure 12.23.
The ODBC Administrator dialog box.
Figure 12.24.
The System Data Sources dialog box.
Figure 12.25.
The ODBC Microsoft Access 7.0 Setup dialog box.
TIP: The Access database can be located on a network drive.
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:
Figure 12.27.
The Replication Publishing dialog box.
Figure 12.28.
The New ODBC Subscriber dialog box.
Once the ODBC DSN has been registered as a subscribing server, follow these steps to push a publication or article to the ODBC subscriber:
Figure 12.29.
The Publication Subscribers dialog box.
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.
To wrap up this chapter on replication, the following sections review a last few pointers, tips, and reminders when dealing with SQL Server replication.
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.
Replication cannot occur on a subscribing database if the read-only database option is set to TRUE.
SQL Server 6.5 allows you to disable constraints during replication.
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.
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.
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.
Following are some of the important things to remember for SQL Server replication:
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.
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.