Database Mirroring

With the release of SQL Server 2005 Service Pack 1 (SP1) in April, database mirroring finally became a reality for SQL Server 2005. Implementing this high-availability feature, which is supported by both 64-bit and 32-bit SQL Server 2005 Enterprise and Standard editions, involves using three SQL Server systems: a principal server, a mirror server, and a witness server. The principal and mirror are required. The witness is needed only if you want automatic-failover capability. Although the principal and mirror must run on the Enterprise or Standard editions, the witness can be any SQL Server edition, including SQL Server 2005 Express,and none of the systems involved are required to have matching or specialized hardware.

Database mirroring doesn't share the distance limitations of Windows clustering. And, unlike Windows clustering, which runs at the system server level, database mirroring is implemented at the database level. This feature works by sending transaction-log records between the principal and mirror servers. The principal server is the source of the database changes. The mirror server maintains a copy of one or more of the principal server's mirrored databases. Note that the mirror server isn't restricted to providing just data backup services but can also actively support other unrelated databases and applications.

Working with the new SQL Native Client (SNAC), database mirroring enables maximum availability for client applications by automatically redirecting SNAC network clients to the mirror server if the database on the principal server is unavailable. Database mirroring operates in two modes, asynchronous and synchronous:

1. Asynchronous

Of the two modes, asynchronous provides the best performance but offers lower transaction consistency and doesn't support automatic failover. Database transactions on the principal server are immediately committed on the principal without waiting for the mirror server to acknowledge that it's written the data to its log. Transactions are written to the mirror server asynchronously.

2. Synchronous

Synchronous mode provides a higher level of data protection than asynchronous mode. To ensure that transactions are committed on both the principal and the mirror servers, the principal waits for an acknowledgement from the mirror before committing transactions. The wait imposes a performance overhead that's influenced by the activity of the systems and their connection speed. This mode is best suited for high-speed LAN links.

Synchronous database mirroring in turn supports two operation modes: high availability and high protection. In high-availability mode, if you have a witness server, database availability is the highest priority. The protected database will be available as long as two of the three servers (principal, mirror, witness) are present. High-availability mode is capable of automatic failover.

In high-protection mode, if you don't have a witness server, protecting the data is the highest priority. If either the principal or the mirror is unavailable, then the database is taken offline.

Provided we have SQL Expresss as a witness and assuming we have a spare server, do we need to buy another license for SQL Server as a mirror?
I seem to remember that SQL Server 2005 license allows use of its license for another backup server. Or was it Exchange.

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More