Database Mirroring
The most exciting new SQL Server 2005 high-availability feature is database mirroring. As discussed, failover clustering, which provides server redundancy, doesn't provide data-file redundancy.Although database mirroring doesn't provide server redundancy, it provides both database redundancy and data-file redundancy.
When you set up database mirroring, you use two servers with a database that will be mirrored from one to the other. The source server is called the principal server, and the database that you want to protect is called the principal database. The other server, which receives mirrored data from the source, is called the mirrored server, and the copy of the principal database on it is called the mirrored database. When mirroring is up and running, the principal SQL Server 2005 instance transmits copies of the principal database's transaction log activity to the mirror SQL Server 2005 instance. The copy of the transaction log activity is written to the mirrored database's log, then those transactions are executed on the mirror database.The result is that the mirror database executes the same transaction log activity as the principal, but slightly behind in time. It mirrors the principal's activity.
To enable automatic failover, you must specify that the transmission will be synchronous (with SAFETY set to ON) and also specify a third observer SQL Server instance, called a witness. In synchronous mode, the principal will wait for acknowledgment from the mirror that it has written the mirrored log activity to disk before the principal moves ahead with the transaction. In the meantime, the principal, mirror, and witness all communicate periodically, indicating their online status to each other.
If the principal server suddenly fails, leaving both the mirror and witness servers still functional, an automatic failover will occur. After the mirror server detects that the principal is no longer available, the mirror server queries the witness to discover whether it detects the principal. If the witness also can't detect the principal, the mirror promotes itself to the principal role and brings its database online as the new principal.The witness then records the presence of a new principal in the configuration.
If the old principal is then brought back online, the former principal finds that the old mirror is now the new principal, and that it has been "outvoted."The new principal and the witness agree that the old principal is no longer the principal server. The old principal then takes on the mirror role and starts receiving the new principal's transaction log data. A database mirroring database failover can occur in just a few seconds.
You can also enable the client to automatically redirect its connections if a failover occurs. If your application connects to a principal database using ADO.NET or the Microsoft SQL Server Native Client (SQL Native Client), the driver will automatically redirect connections when a database mirroring failover occurs. You just specify the initial principal server and database in the connection string (and optionally the failover partner server). If a mirroring failover occurs and your application attempts to connect, the driver will detect the application and redirect the connection to the former mirror server, which is now the principal.
Mirroring Restrictions
When you set up database mirroring, the principal database must be in the Full recovery model and the mirror database must be restored with NORECOVERY. Therefore, you can't read from the mirror database, although you can make a database snapshot of it on the mirror server. The principal, mirror, and witness must all be distinct-SQL Server instances: you can't mirror a database on a single SQL Server instance. Related to that restriction, the principal and mirror databases must have the same name, and you can mirror only from one principal database to one mirror database. (However, a server that's a principal for one database can be a mirror in a different mirroring session for a different database.)
Database mirroring requires either Enterprise Edition or Standard Edition for the principal and mirror servers.The witness server, which is only an observer in a mirroring session, can be any edition of SQL Server—including SQL Server 2005 Express Edition.The Standard Edition supports mirroring only in synchronous mode (with SAFETY set to ON), whereas the Enterprise Edition also supports mirroring in asynchronous mode.
What's exciting about database mirroring is that it can provide very high availability, in most scenarios failing over from one server to another in just a few seconds.This failover is automatic, just like clustering, but much faster. And, unlike failover clustering, database mirroring doesn't require additional expensive and proprietary hardware for support. Database mirroring is supported on commodity hardware and is easy to manage and monitor.As a result, in some cases, it can provide higher availability than clustering at a significantly lower cost.
Of course, database mirroring provides redundancy only at the database level. Therefore, unlike failover clustering, when you have a database mirroring failover,you must ensure that the mirror server has all the proper logins, SQL Agent jobs, SQL Server Integration Services (SSIS) packages, and other supporting components and configurations.
In addition, if you have a SQL Server instance with many interdependent databases, enabling mirroring with automatic failover might not be appropriate. If only one database fails over, you could end up with one database online on one server and all the other databases online on another server. Then, the dependencies among the databases would break.As of this release, you don't have a way to bind a set of mirrored databases so that they all fail over together (although that's a natural next step in the evolution of database mirroring).
Prev. page
1
[2]
3
4
next page 
