My picture album
My code library
Search my site
SQL Server Articles New
NEW!!! Subscribe to my
|Want to keep in touch with the
latest in SQL Server world? Email firstname.lastname@example.org with
'subscribe' in the subject line
What is the best way for maintaining a standby SQL Server?
Since SQL Server provides so many options for copying data between different databases and servers, novice/inexperienced DBAs often get confused, as to which option to go with, for maintaining a standby server. Some of those options include Data Transformation Services (DTS), BCP, Replication, Clustering, Backup/Restore, Logshipping.
In this article, I will define what a standby server is and how to maintain one. I will also discuss the advantages and disadvantages of the above options.
Here's a quote from SQL Server Books Online, that defines a standby server:
"A standby server is a second server that can be brought online if the primary production server fails. The standby server contains a copy of the databases on the primary server. A standby server can also be used when a primary server becomes unavailable due to scheduled maintenance. For example, if the primary server needs a hardware or software upgrade, the standby server can be used."
As the above definition is indicating, a standby SQL Server will have a copy of the main production database, which is as closely in synch with the original database as possible, in terms of transactions. So, how can we maintain an identical and transactionally consistent copy of the ever changing production database? The answer is, to implement transaction log backup/restores or log shipping. The rest of this article will discuss the merits and demerits of all approaches commonly considered by DBAs.
Data Transformation Services (DTS):
DTS can be used to transfer database objects and data between different databases within the server as well as between servers. DTS can also be used to transform and transfer data between disparate sources.
- Rich, friendly, wizard driven user interface.
- Powerful object model for programmatic enhancement of functionality.
- Data can be transferred between heterogeneous data sources like Microsoft Access, Excel, Text files etc.
- As a data transfer and transformation tool, DTS does a very good job, but it cannot be used to maintain a warm-standby server, because, DTS has no mechanism of 'change tracking'. That means, every time the DTS package runs, the complete data gets transferred to the target server. This is inefficient especially when working with huge databases that run into several Giga Bytes (GB) in size. Though Data Driven Queries (DDQ) or timestamp based filters can be used to transfer the updates, they are inefficient and inaccurate.
- Since there is no 'change tracking' in DTS, the execution time of the DTS package is directly proportional to the amount of data that needs to be transferred. That means, while working with huge databases, it is not possible to schedule the DTS package at frequent intervals. This increases the possibility of the warm-standby server being out-of-sync with the primary server, by large amounts of data, when the primary server fails.
Very difficult to maintain a warm-standby server with DTS, because of the above mentioned disadvantages. DTS is good for transferring, transforming huge amounts of data quicker, between data sources. So, use DTS for transforming and transferring data, instead of maintaining standby servers.
Bulk Copy (BCP):
BCP can be considered as a primitive version of DTS and is a command line utility. It is powerful and high data transfer rates can be achieved by using non-logged operations.
- Faster and efficient while transferring large amounts of data.
- Has all the disadvantages associated with DTS.
- Cannot be used to transfer database objects other than tables and views. So, you have to manually transfer other objects like stored procedures, triggers etc. using scripts.
- Poor user interface, and no built-in provision for scheduling.
BCP is not a suitable option for maintaining warm-standby servers because of the above-mentioned disadvantages. Consider BCP for high speed data transfers between SQL Server and text files.
Replication transfers data from one SQL Server database to another. It can also transfer data from SQL Server to all other ODBC data sources. SQL Server supports 3 types of replication:
Advantages of transactional replication:
- Snapshot replication: Transfers the complete table data from one database to another, every time the distribution agent runs. Not ideal for bigger tables.
- Transactional replication: Instead of sending the complete data every time, transfers only the changes to the subscribers and keeps the publisher and subscriber in sync with minimal latency. Though, it can be used for maintaining warm-standby servers, it is intended and more appropriate for maintaining reporting or decision support systems.
- Merge replication: It is a two-way replication between publisher and subscriber with conflict resolution mechanism. Suitable for a distributed computing environments than for maintaining warm-standby servers.
- Transfer only the changes to the subscriber, instead of sending the complete data at every run of distribution agent.
- With careful planning and sizing, latency between the primary and the secondary server can be brought down to as low as a couple of seconds.
- Enterprise Manager provides rich and intuitive user interface for setting up and maintaining replication.
- Data alteration and custom processing can be easily implemented using custom replication stored procedures, while transferring the data.
- Replication is a complicated piece of setup and has a lot of failure points. The success of replication depends on a lot of factors like:
- Availability of all participating servers (publisher, distributor, subscriber).
- Availability of SQL Server Agent.
- Windows NT permissions on the snapshot folder.
- Network bandwidth.
- Space availability on the distribution database.
- Connectivity between the publisher, subscriber and distributor.
- A table must have a primary key, to participate in transactional replication.
- Requires the attention from a skilled DBA and needs to be monitored continuously.
- Cannot transfer database objects like UDFs.
- Non-logged operations on text/ntext/image columns cannot be replicated.
- Not possible to maintain the exact same schema on this subscriber. For example, triggers and constraints need to be disabled on the subscriber. When the primary server fails, you must activate these constraints and triggers before taking the secondary server online.
While transactional replication can transfer data between publisher and subscriber, with minimal latency, it is a bit complicated to setup and administer. Replication is recommended for sites with dedicated DBAs. Consider transactional replication for maintaining a reporting database by transferring data from the OLTP server, there by offloading the reporting to another server.
Clustering provides high availability of database servers, by automatically failing over to a different node, if the primary node fails because of a service or operating system failure or during a planned upgrade/maintenance. In an active/passive cluster configuration, the secondary server continuously monitors the primary server, and takes on the role of the primary server, as soon as it detects the failure of the primary server. In an active/active cluster configuration all the nodes are online serving different needs, but are capable of taking over the load of the other nodes should they fail.
- High availability.
- No user intervention during the fail-over process.
- Requires Enterprise editions of Windows as well as SQL Server. So, it costs more in terms of licensing.
- Requires specialized cluster-compatible hardware.
- Cannot tolerate a failure of the shared drive.
- Difficult to configure and administer for novice DBAs, but it is getting easier by the version.
- Requires un-clustering and re-clustering during SQL Server service pack installations.
- Requires the two nodes to be physically connected. So the two nodes can't be at different physical locations. That is, in case of a disaster, we might end up losing both the nodes.
Clustering is a useful high availability solution, but is not an effective approach for disaster recovery, unless it is combined with other approaches like backup/restore, disk mirroring etc.
Database backup provides a duplicate copy of the database and database restore re-creates a previously created database. This is the best method for creating exact replicas of databases, as backup copies all the objects in the database along with data. There are four types of backups:
From the above explanation, it is evident that transaction log backup/restore is the best approach for creating a warm-standby server to recover from disasters.
- Full-database backup: Backs up the complete database. The backup file size is proportional to the original database size. Can't really be used to maintain warm-standby servers, as full-database backup/restore takes a lot of time, because of the IO overheads involved.
- Transaction log backup: Backs up only the changes since last full or transaction log backup. Ideal for maintaining warm-standby servers, as transaction log backups tend to be smaller and take less time for backup and restore, while capturing all the database changes (not only data changes).
- Differential database backup: Backs up only the changed pages since the last full-database backup. Can be combined with transaction log backups, to reduce the number of transaction logs that need to be restored, in the case of disaster recovery. Size of the differential database backup files depends upon the number of data pages that got modified since the last full database backup.
- File/File group backup: Backs up only the specified file or file group within the database. Must be combined with transaction log backups, to be able to fully recover. Useful when dealing with very large databases (VLDB) but is difficult to configure and work with.
Advantages of database and transaction log backups:
Disadvantages of database and transaction log backups:
- Creates exact replicas of databases by copying all database objects along with data.
- Online backups are supported with minimal overhead on concurrency and normal OLTP operations.
- Can be easily implemented using the user friendly 'Database maintenance planner' and SQL Server agent jobs.
- Requires minimal monitoring overhead, once properly configured.
- Databases/transaction logs cannot be backed up and restored between servers with different code pages and sort orders. This is no longer a problem with SQL Server 2000.
- While restoring, the database should be in single-user mode; otherwise the restore operation will fail. In SQL Server 7.0, there is no built-in way to kill all user connections and bring the database into single-user mode. But SQL Server 2000 provides extensions to ALTER DATABASE command that help in killing connections before a restore.
A combination of full-database and transaction log backups is the best approach for maintaining warm-standby servers, because backup/restore creates exact copies of the database.
Logshipping is nothing but an automated backup and restore of transaction logs, instead of doing the same manually. Logshipping requires you to identify a primary and secondary server, the backup and restore schedules. Logshipping is not an in-built feature of SQL Server 7.0. It's released with Microsoft Back Office Resource Kit (BORK) 4.5. SQL Server 2000 Enterprise Edition comes with built-in logshipping support. Though this feature is not available in other editions of SQL Server, it can very well be implemented using your own backup/restore scripts.
- Logshipping takes a step further in enhancing the capabilities of transaction log backup and restore operations. It continuously keeps backing up the transaction log of the primary database and restores it onto the secondary database, at predefined regular intervals. With a smaller restore interval, at any given point in time, the secondary database is almost in sync with the primary database. This reduces the time a DBA would spend restoring the backups, in case of a primary server failure, there by reducing the server downtime.
There is no user interface available for configuring Logshipping in SQL Server 7.0 (Available in 2000).
As is the case with backup/restore, logshipping is the best approach towards implementing a standby/fail-over system for disaster recovery with minimal data loss and downtime.