Ask the Expert - Mr. Database
This month's question:
Q: Dear Mr. Database:
What are some benefits of upgrading to SQL Server 2005?
Gary
Burlington, MA |
|
A: Dear Gary:
There are a number of improvements in Microsoft SQL Server 2005 that force every company using an older version for mission-critical tasks to evaluate the implications of upgrading their environments. All other things being equal, critical databases deserve to exist on the most highly-available platform, and in most cases that is SQL Server 2005.
Database mirroring is a technology that duplicates the transactions of a principal database into a mirror of that same database without requiring any special hardware. As opposed to failover clustering which has more stringent requirements, this is a process that can be used in a wider variety of situations such as: when no cluster-capable hardware is available or when distances preclude standard clustering. Wide-area distance mirroring can take advantage of asynchronous communications, creating a mirror that does not noticeably impact the principal’s performance. On the other hand, mirrors can also be configured to require that transactions be saved on the disks of both the principal and mirror databases. It should be noted that mirroring does not imply distributed transactions – failure of a commit on the mirror will not cause the commit to fail for the client. True multi-phase commit requires real distributed transactions, supported by Microsoft Distributed Transaction Coordinator which requires a failover cluster.
Using mirroring, automatic failover is now possible with the use of a witness server to provide a quorum with the server that remains available when one node fails. Another feature that is made more useful using mirroring is the use of database snapshots. Mirror databases are not normally available for queries, but when a snapshot is created from some consistent point in time, the mirror can provide access to the data. Clients who have connected to a principal database that has failed can be instructed to reconnect to the surviving instance upon failure with a simple change to the connect string.
Failover clustering has been incrementally improved in SQL Server 2005 to further improve availability. Now, clusters can be created that scale to a larger size (8 nodes) then previously allowed. When a service interruption event occurs, the key metric to users is the amount of time where services are not available. Certain versions of SQL Server 2005 allow for users to connect to and start querying databases which are done processing the redo information but which have not yet completed processing the pending undo from the failed instance. Finally, the total failover time can be made to be predictable (and can be minimized using the “recovery interval” configuration parameter).
<< back |
