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.
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.
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 email@example.com.
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.