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
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.