Based on your question, it sounds like you are really in search of a SQL Server high availability (HA) solution. Luckily Microsoft has offered a number of options in SQL Server environments to address high availability needs with a varied level of automation, data consistency and overall system availability. Below are a set of SQL Server high availability solutions that address these three needs:
- Custom code via a DTS Package to move a subset of data scheduled on a predefined basis via SQL Server Agent. For more information visit DTS Basics.
- Full database backups and restores via native code or from a unique product that compresses the database backups yielding significant time and disk savings as delivered by SQL LiteSpeed from DBassociatesIT. For more information visit Backing Up and Restoring Databases.
- Log Shipping migrates data at a transaction level as recorded in the database transaction log in a sequential manner from a source system and applies the transactions in the same order to the destination system with the ability to manage the two data sets independently while maintaining transactional integrity between the source and destination databases. For more information visit Log Shipping.
- Replication to migrate a subset of data via a Publisher, Subscriber and Distributor paradigm with Articles to define the data and a predefined schedule to replicate the data. For more information visit Introducing Replication.
- Clustering is another option which is comprised of a redundant hardware solution with 2 or more nodes and a single data set on a shared drive to maintain availability when a hardware component fails on the SQL Server. For more information visit Failover Clustering.
- Some organizations employ combinations of these technologies such as clustering and log shipping to maintain hardware redundancy and two data sets to recover from an immediate hardware failure and maintain latency between the two data sets to recover from data corruption.
As organizations begin to move towards more automated SQL Server high availability solutions, it is important to not overlook the underlying hardware and purchase hardware with a solid reputation and redundant features. These features include redundant hard drives, controller cards and network interface cards (NICs). These components would be complimented by a redundant network infrastructure in terms of connectivity and processing for the user community to enjoy no interruptions. As time progresses it is imperative to ensure these servers remain under warranty and consider an agreement with the hardware vendor to have the needed equipment on site and repaired with one, two, four or eight hours of the failure. Although this was a brief explanation of the SQL Server high availability solutions, for more information about SQL Server 2000 high availability options visit the Microsoft SQL Server 2000 High Availability Series.
This response is continued...