Problem solve Get help with specific problems with your technologies, process and projects.

Creating redundant SQL Server 2000 database automatically

I want to establish a redundant database server for SQL Server 2000. I have two computers running SQL Server 2000. From Server1, my clients are retrieving the data, and new data are getting inserted/updated. I want to automate the system in such a way that Server1 automatically updates all the changes in data and databases respectively in Server2. How can I do that?

There are several options you can look at. The most commonly used approaches are log shipping and transactional replication. Log shipping is an automated process of backing up transaction logs on a database on Server1, and restoring these over the network to a duplicate database on Server2. Replication involves SQL Server reading transactions from the transaction log on a database on Server1 and re-creating the same transactions on a database on Server2. Each of these methods has its own benefits and limitations.

Log shipping has lower administration overheads and by nature ensures that the database on server2 is always a copy of that on server1 (up until the point where the log ship process run, typically you run it every 5 to 60 minutes). However log shipping reduces accesses to the standby database while the log shipping process is running. No changes can be made within the log shipped database (it is read only), and all users must disconnect before the last log shipped updates can be applied. Log shipping is usually loosely consistent (the update runs on a regular reoccurring schedule rather than real time).

Transactional replication need not replicate the entire database (as with log shipping). Also, schema objects outside that are not being replicated can have local transactions occurring against them modifying the data within them . This approach is a good one if you have two operational systems with one being the primary for areas of data (such as a product list, or employee information etc). Replication can be nearer to real time with changes being pushed to subscribers.

For more information, see Log Shipping and Transactional Replication in SQL Server Books Online.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.