Data Centers

Take advantage of replication options in SQL Server 2000

Copying data from your production databases to other database servers in the enterprise can be advantageous for a variety of reasons. In this article, Tim Chapman discusses replicating data in SQL Server 2000.

SQL Server replication is the process of distributing data from a source database to one or more destination databases throughout the enterprise. The process is set up by the database administrator and maintained automatically by the database engine. The SQL Server 2000 Enterprise Manager contains relatively simple and straightforward wizards that will guide you through the set up process. However, some knowledge of the core replication concepts is recommended.

Why use replication?

Replication is useful in many ways. For instance, replication is a great solution for maintaining a reporting server. You can use replication to transfer necessary data objects to a reporting database (preferably on a separate server) to meet your company's reporting requirements while relieving load from your OLTP server. Replication also allows you to transform data as you replicate it to your reporting server, which means you can replicate only the columns or rows of tables as necessary. You can also replicate objects other than tables, such as views, user-defined functions, and stored procedures. For tables, you can include constraints, indexes, and triggers for tables to be replicated as well.

Besides for reporting purposes, you can use replication to scale out some of the processing from your production servers. In this scenario, database objects would be replicated from the production environment to a separate server. On this server, the required processing would occur. Once the necessary processing has finished, the changes can be propagated back to the production server, effectively taking the processing load off of the production server.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

Another advantage of replication is that it allows you to replicate data to physically separate locations for use. One typical scenario in which this is used is for sub-offices in an organization. A main office can maintain the "master" database copy, while the sub-offices have a copy of this database. The sub-offices are able to view and alter the data that is distributed to them, and any changes can be merged back into the main office database at configured intervals. The main office can then pass these changes to all of the other sub-offices. Allowing each sub-office their own separate database and merging changes at intervals is very effective at reducing network traffic between the main office and the sub-offices.

Replication types

There are three types of replication setups, each suiting a specific need. The following is an overview of the three types:

  • Snapshot: This simplest form of replication moves a copy of the database objects for a specific moment in time to subscribing databases. This produces very little overhead because the system is not constantly checking for changes to replicate. However, the objects being replicated are only as current as the latest snapshot.
  • Merge: This form of replication starts with an initial snapshot being applied at the destination database. As the data at the destination database is altered, changes are merged back into the source database. From there, the source database can publish these changes to the other destination databases. This allows these destination databases to work offline and, when necessary, merge its data back to the source database.
  • Transactional: With this form of replication, any changes are immediately obtained from the transaction log and moved to subscribers. This allows you to keep your source database and any destination databases "almost" the same, depending on how latent you configure the data to be. Because changes are made next to real time, much more overhead is involved in this type of replication. Transactional replication also allows the changes to be bi-directional. Changes made to data at a destination database can be propagated back to the source database.

Replication as a high availability solution

In my opinion, replication is not a very suitable high availability solution. Replication has a tendency to be error prone. These errors are typically small ones and easy to fix, but they are errors nonetheless. Also, like log shipping, a failover to a replicated database would require manual handling, which will take careful planning and lost time and data.

Additional information on replication

This article is a brief overview of the replication options available to you in SQL Server 2000. For a complete SQL Server 2000 replication reference, refer to this MSDN article.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at


Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.


Am getting this error in my database. Tha error is "ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.BAL_TBL' because it is being used for replication" Any one who can help you welcome

Tony Hopkinson
Tony Hopkinson

Don't confuse snapshot replication with taking snapshot now available in 2005. The other thing is you have to design your database with replication in mind. Going back and retrofitting it, if you are using a lot of referential integrity and or triggers can be very complex. Personally I think the thing is way too fragile to anything serious with it.

Hi Tim, Good article... Another method of replication is by complete two way crosscheck. (data compare) This method requires specification of a source query and a destination query that should both return the same results. Data keys also need to be nominated. (note that Oracle and microsoft sort strings in different orders so some work is required to avoid problems if source and destination end database differ). regards Allan Ford Analyst / Programmer - IT Application Services, IT Services, Shared Business Services Santos Ltd Email:

Tony Hopkinson
Tony Hopkinson

If you want to drop it, don't publish it. Presumably you'll want to kill the replicated versions as well. I can't remember exactly where everything is in SQL 2k. But somewhere in the database for that table it's got publications, find BAL_TBL, take it out of there Bob's your Mother's brother. PS why are you dropping tables over ODBC?


Well, I never mentioned 2005's Database Snapshots (or Snapshot Isolation for that matter), just Snapshot Replication, so I am not sure how you could get confused by it. However, I do have an article slated on Database Snapshots in the future, so stay tuned. As for designing your database with replication in mind, it depends. If you are talking about snapshot or some transaction replication for reporting purposes, then you really don't have to plan a lot out for reporting purposes. However, if you are involving some complicated merge replication or farming our processing for transactional replication then you will need to do a bit of designing with replication in mind, perhaps not so much schema design but coding design. It can be fragile, but I've not had any major problems with it in any of our production environments, so I can't say that it is too fragile to do anything serious with it.


Are you talking about using queries to update data from one data source to another?

Editor's Picks