Service provider takeaway: Customers looking for a low-cost approach to ensuring high-availability databases can...
turn to value-added resellers for help choosing the technology that best fits their shop.
One of the most difficult tasks faced by a company's database staff is choosing the right approach to ensure high-availability databases, since there are many factors that play into the decision. For large mission-critical databases, a solution involving hardware database mirroring, clustering and geoclustering (an approach in which servers are hosted in different geographic locations) is required. While such solutions are very expensive, the cost of downtime justifies the investment. But if your customers don't have the budget for such sophisticated technologies, there are cheaper ways to keep databases running. Here we examine the pros and cons of these low-cost options to help you simplify the choices for your customers.
The three low-cost methods to reduce downtime on a relational database management system (RDBMS) are log shipping, replication and database mirroring. When working with customers, it's important to stress that:
- Log shipping and replication do not provide automatic failover; clients will have to be redirected to the standby server either manually or programmatically. Any failover should be done manually in a controlled environment to prevent the application from failing over to the standby server in the event of a transient failure, like a network hiccup.
- The application using the databases must be aware of the high-availability choice -- for example, there must be logic embedded in the applications to be able to redirect to the standby server, and to minimize any data loss that might occur during the failover.
- All dependencies must be in place on both the production server and the standby server. The three low-cost approaches discussed here do not mirror dependencies; you or your customer must ensure that all dependencies are in place on the standby server and kept up to date. (Clustering provides high availability by virtualizing the RDBMS; your clients connect to a virtual RDBMS that resides on any one of the nodes that are part of your cluster. Each node has its own hardware resources and only shares the virtual databases that are on a common drive shared between all nodes of the cluster. When a node goes down, clients will automatically be reconnected to the virtualized RDBMS, which will now be running on a new node. Clustering essentially mirrors the RDBMS on each node of the cluster. Should the server fail over from one node to another, all the databases transfer to the new node, taking with them all RDBMS dependencies on the cluster.)
- The standby server should be an identical copy of the production server -- in other words, the same edition, version and patch level of the RDBMS, and the same hardware and drive path structure.
- Each of the three low-cost approaches has some potential for data loss.
Now that we've established those basic gotchas, here are the advantages and disadvantages of each approach.
Log shipping is a technology in which the backup is copied from the production server to a standby server, restored there, and then transaction logs backed up on the production server are copied or shipped to the standby server and applied there. This process keeps the two databases synchronized.
The main advantages of log shipping are:
- It is a simple, widely understood technology; for example, SQL Server 2005 has wizards to create log shipping topologies.
- All database dependencies are copied to the standby server (server dependencies, such as logins, are not).
- It is minimally intrusive on the server.
- The standby server does not require an RDBMS license until 30 days after it is promoted to a production server. (This only applies if the entire database is log shipped.)
The disadvantages of log shipping are:
- Latency (how out of sync the standby server is from the production server) can be as low as one minute but is typically five minutes. During a backup, the log cannot be generated or shipped, and during an indexing operation, the log can be huge. As a result, the latency and the exposure to data loss can be about five minutes, but it can be much larger during a backup or indexing operation.
- Log shipping is not scalable to large numbers of databases per server.
- When the logs are applied on a database on the standby server, the database must be left in recovery mode -- in other words, ready to have subsequent logs applied to it. Users will be unable to access this database to query the data there unless the database is restored in "standby mode" (read-only mode). Users will be able to access the database and query it until the next log backup is ready to be applied. To restore the next log backup, users must be disconnected and the next log backup restored. In read-only mode, the database, while accessible, is not really ready for useful work; for example, you will not be able to make modifications to it or apply indexes that will help with intensive queries for reporting purposes.
Replication is a technology that captures transactions occurring on the source database (called a publisher) and applies them on the destination database (subscriber).
The advantages of replication are:
- It can be bidirectional.
- The database is available for useful work and is writable so that indexes can be placed on tables, or data can be modified.
- A subset of the objects (that is, only a few tables) or a subset of data in a table on your database can be mirrored.
- Data can be modified as it travels from the publisher to the subscriber.
- Data can be copied to different RDBMSes, for example, from SQL Server to Oracle.
- Replication is more scalable than log shipping and database mirroring.
The disadvantages of replication are:
- You will need a license for the standby/subscriber server.
- Setting up and maintaining a replication topology can be complex.
- Latencies are unpredictable, although they might be very low.
- Certain types of replication require primary keys on every table you want to replicate (transactional replication); other types of replication require the addition of a global unique identifier (GUID) column to every table for tracking purposes.
Database mirroring is a feature included in SQL Server 2005, and companies such as EMC and Double-Take Software have database mirroring tools. Conceptually, database mirroring can be considered continuous log shipping.
The advantages of database mirroring are:
- It overcomes the distance limitation of clustering. Clustering is difficult to deploy across geographically dispersed locations -- for example, between Los Angeles and New York. Database mirroring can be configured over distances where clustering will not work.
- It supports failover and automatic client redirection, if the connection string is modified to include the name of the standby server.
The disadvantages are:
- Databases can be mirrored only from one server to one other server. In addition, the databases must have the same name and bit version, and both servers should have the same drive and directory structure.
- Some editions of RDBMSes allow only synchronous mirroring, which can greatly increase the latency between the principal (the source server) and the mirror. (SQL Server 2005 Enterprise Edition allows asynchronous mirroring.)
- It is highly sensitive to network speed and stability.
- In SQL Server's high-safety mode, the latency of short-running transactions on the principal can be much longer.
- The database on the mirror (destination server) is in a recovering state and cannot be accessed unless you use a database snapshot available only on some versions of RDBMSes.
- It is not scalable beyond 10 databases per server.
With this breakdown of the low-cost ways to ensure high-availability databases, you'll have an easier time helping your customers make the right choice.
About the author
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.