One of the primary design goals of most Web applications is 24x7 availability. Most Web sites using Web database tools (ASP, ColdFusion, etc.) plan for redundancy and scalability of the Web servers using both hardware- and software-based clustering technologies. Database servers, however, are frequently given less attention; considering that data is the most important part of most applications, this behavior does not bode well for sites that fail to consider database scalability and reliability!
Virtually all databases can support some sort of standby mode where two databases on two different servers are deployed. The primary database server handles all traffic until a failure, at which point the traffic is directed towards the standby database server. This approach provides increased redundancy and is fairly simple to implement, but it is not the optimum solution. The biggest challenge with using standby servers is keeping the databases in sync, especially at sites with large transaction volumes. If a failure occurs, any changes since the last synchronization will not be reflected in the standby database until the transaction log from the primary server is applied, if that is even possible after a catastrophic failure. There are network issues as well since the machines have different names and IP addresses which will likely necessitate changes to connectivity settings.
Enterprise-class databases typically support a cluster mode, where two or more database servers are combined into a virtual database server. This virtual server can distribute processing load among all currently active machines. This approach solves the problem of changing machine names and IPs, since all of the connectivity is in the context of the virtual cluster. It also provides a means of scaling the application, since many physical servers can be added to the virtual cluster to increase processing power.
Regardless of the approach you choose, you should strongly consider using drive arrays to house the actual database files. This is basically a cluster of redundant disks connected to each database server using fiber or direct network connections. This external drive is mapped on each machine in the cluster (or primary/standby pair) and serves as the disk storage for the database. This solves the problem of data synchronization since all the servers read and write to the same database.
Both of these methods provide a means to handle unexpected downtime, as well as routine maintenance needs, but clustering provides not only reliability, but scalability since additional servers can be added to the cluster to handle increased performance needs.
About the Author
John Paul Ashenfelter is CTO & President of TransitionPoint.com, a technology development and analysis group focusing on web database applications. He is the author of Choosing a Database for Your Web Site (John Wiley & Sons, Inc.) and the co-author of ColdFusion 4 for Dummies (IDG Books). He also founded and maintains the webDatabase.org Web site.
For More Information
- The Best Database Web Links
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums. Also, give us your feedback about this tip in the "Sound Off" forum.
- Check out our new Ask the Experts feature: Our SQL gurus are waiting to answer your toughest questions.