Important:
Microsoft support policies only apply to the database mirroring feature as delivered with SQL Server 2005 Service Pack 1 (SP1) onwards. If you are not running SQL Server 2005 with SP1 or later, database mirroring should not be used in production environments. Microsoft support services will not support databases or applications that use database mirroring from the RTM release.
Introduction
Database mirroring is a new SQL Server 2005 technology available for review for increasing database availability. Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server. You can code client applications to automatically redirect their connection information, and in the event of a failover, automatically connect to the standby server and database.
Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Database mirroring, however, can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage.
Overview
The originating database and server have the role of principal, and the receiving database and server have the role of mirror. The principal and mirror servers must be separate instances of SQL Server 2005. In addition to the two partner servers (principal and mirror) a database mirroring session may have an optional third server, called the witness.
The witness server's role is to enable automatic failover. When database mirroring is used for high availability, if a principal server suddenly fails, if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available within a few seconds.
Some important items to note about database mirroring:
· The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database. (command )
ALTER DATABASE [] SET SAFETY FULL;
· The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.
· The mirror database must have the same name as the principal database.
Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time.
Operating Modes
There are three possible operating modes for a database mirroring session. The exact mode is based on the setting of transaction safety and whether a witness server is part of the mirroring session. I will only discuss two cause I dont recommend the high performace mode cause data could be lost.
High Availability (Recommended)
The High Availability operating mode supports maximum database availability with automatic failover to the mirror database if the principal database fails. It requires that you set safety to FULL and define a witness (any version of SQL 2005, including express edition, free) server as part of the database mirroring session.
The High Availability mode is best used where you have fast and very reliable communication paths between the servers and you require automatic failover for a single database. When safety is FULL, the principal server must wait briefly for responses from the mirror server, and therefore the performance of the principal server may be affected by the capability of the mirror server. Because a single database failure will cause an automatic failover, if you have multi-database applications you want to consider other operating modes. (See "Multi-Database Issues" in the Implementation section later in this paper.)
In the High Availability mode, database mirroring is self-monitoring. If the principal database suddenly becomes unavailable, or the principal's server is down, then the witness and the mirror will form a quorum of two and the mirror SQL Server will perform an automatic failover. At that point, the mirror server instance will change its role to become the new principal and recover the database. The mirror server can become available quickly because the mirror has been replaying the principal's transaction logs and its transaction log has been synchronized with the principal's.
Also, SQL Server 2005 can make a database available to users earlier in the recovery process. SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase. In SQL Server 2005, a newly recovered database can become available for use as soon as the redo phase is finished. Therefore if a database mirroring failover occurs, the recovered new principal database can become available for use as soon as it finishes the redo phase. Because the mirror database has been replaying transaction log records all along, all the mirror serves has to do is finish the redo process, which normally can be accomplished in seconds.
High Protection
The High Protection operating mode also has transactional safety FULL, but has no witness server as part of the mirroring session. The principal database does not need to form a quorum to serve the database. In this mode only a manual failover is possible, because there is no witness to fill the tie-breaker role. An automatic failover is not possible, because if the principal server fails, the mirror server has no witness server with which to form a quorum.
Since there is no witness server defined, automatic failover cannot occur and a principal server which suddenly loses its quorum with the mirror does not take its database out of service.
Database Snapshots
Because the mirror database is in a recovering state, it is not accessible and not readable. With SQL Server 2005 Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time. Database snapshots provide a read-only view of a database, exposing data that is consistent at the point of the snapshot creation.
You access the database snapshot just as though it were another database. When you query a database snapshot, you read original versions of any database data that has been changed after the snapshot's creation from the database snapshot's file, and you read unchanged data from the original database. The end effect is that you see database data current at the point in time that you created the snapshot. (See the topic "Using Database Snapshots with Database Mirroring" in SQL Server Books Online for more information.)
Because database snapshots do require some overhead on the mirror server, you need to be careful about how they might impact database mirroring performance. You can only mirror to one database, so if you need to scale out to many read-only reporting servers, transactional replication is a better choice. (For more information, see "Database Mirroring and Replication" in the Implementation section later.)
Client-side Redirect
In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string, and optionally the failover partner server.
There are many ways to write the connection string, but here is one example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"
The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.
Setup and Security
Once you have identified the principal, mirror, and, optionally, the witness servers, setting up Database Mirroring consists of essentially three steps.
1. You must make a backup of the database and restore it to the intended mirror server without recovery.
Note: The principal database must be in the FULL recovery model before you make the database backup to restore to the mirror server. Database mirroring will not work if it must transfer Bulk-logged records in the transaction log. The mirror server must have sufficient free space for file growth as does the principal database. If you want to create database snapshots on the mirror, you must provision additional free space for them as well.
If your backup, copy, and restore takes a relatively long time, you may need to take transaction log backups on the originating database in order to keep the log size under control. However, database mirroring will not be able to initialize if transaction log records have been truncated from the log by log backups. Therefore you must restore those transaction log backups in sequence to the intended mirror database with norecovery before database mirroring will be able to initialize.
2. The servers involved in the database mirroring session must trust each other. For local communication such as a domain, trusting means that each SQL Server instance login must have rights to connect to the other mirroring server, and do its endpoints. You establish this first by using the CREATE LOGIN command on each server, followed by the GRANT CONNECT ON ENDPOINT command. (See "Example of Setting Up Database Mirroring Using Windows Authentication" in SQL Server Books Online.)
For communication across non-trusted domains, you must use certificates. If you use the CREATE CERTIFICATE statement to create a self-signed certificate, most of the database mirroring certificate requirements will be met. You must also make sure that the certificate is marked ACTIVE FOR BEGIN_DIALOG in the CREATE CERTIFICATE statement.
3. The next step is to establish database mirroring endpoints. Establishing endpoints requires that you have system administrator rights to the SQL Server instance. You must set up endpoints on each server that are specifically created as database mirroring endpoints. The easiest way to set up endpoints is to use the Configure Database Mirroring Security Wizard, which you can invoke by clicking the Configure Security button on the Mirroring page of the Database Properties dialog. The Configure Security dialogs will prompt you for computer names and port numbers, and optionally logins, before constructing and executing the CREATE ENDPOINT commands. You can also execute the CREATE ENDPOINT command using Transact-SQL. (See "How to: Create a Mirroring Endpoint (Transact-SQL)" in SQL Server Books Online.)
If you are setting up database mirroring on a domain, and all SQL Server instances use the same service login and password, you do not need to create logins on each server. Similarly, on a workgroup, if all SQL Server instances use the same service login and password, you do not need to create logins on the servers. Just leave the logins blank on the Configure Database Mirroring Security Wizard when setting up endpoints.
Each database endpoint must specify a unique port on the server. When working with SQL Server instances on separate machines, these port numbers can all be the same and the Configure Database Mirroring Security Wizard will automatically suggest port 5022 as the port. If any of the SQL Server instances are on the same machine, each instance must have a distinct port and the port numbers must be unique.
Suppose you want to have three servers in a High Availability mirroring session. Server A will be the principal, server B the mirror, and server W the witness. For server A, the following command will create an endpoint on port 5022:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);
Note that the role has been specified as PARTNER, so that this server may take on the role of principal or mirror for any given database mirroring database. The same command is issued on server B. Since server B is a SQL Server instance on a distinct physical machine, the port number is the same. Then for server W, you can issue
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = WITNESS, ENCRYPTION = ENABLED);
Note that for server W, the role is specified as WITNESS.
By default, the endpoint is not started. You can next start each endpoint using the following query on each server:
ALTER ENDPOINT [Mirroring] STATE = STARTED;
Optionally, you can insert the STATE option in the CREATE ENDPOINT command. This process is repeated on each server.
When you create an endpoint using CREATE ENDPOINT, you can restrict access by IP address using the protocol specific arguments. You can restrict access to a particular set of IP addresses by combining the RESTRICT_IP with ALL option, and the EXCEPT_IP with the list of just those special IP addresses you want. (See "CREATE ENDPOINT" in SQL Server Books Online.)
You can inspect the database mirroring endpoints on a server by querying the sys.database_mirroring_endpoints catalog view:
SELECT *
FROM sys.database_mirroring_endpoints;
4. To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is:
-- Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER =
N'TCP://B.corp.mycompany.com:5022';
The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.
The fully qualified computer name of each server can also be found running the following from the command prompt:
IPCONFIG /ALL
Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com
Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ': ' and you then have the partner name.
On the mirror server, you would just repeat the same command, but with the principal server named:
-- Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER =
N'TCP://A.corp.mycompany.com:5022';
On the principal server, you next specify the witness server:
-- Specify the witness from the principal server
ALTER DATABASE [AdventureWorks] SET WITNESS =
N'TCP://W.corp.mycompany.com:5026';
You do not need to execute any additional commands on the witness server after the initial CREATE ENDPOINT.
Finally, you specify the safety level of the session, on the principal server:
-- Set the safety level from the principal server
ALTER DATABASE [AdventureWorks] SET SAFETY FULL;
At this point, mirroring will start automatically, and the principal and mirror servers will synchronize.
You can adjust the timeout value for determining partner outage, using the TIMEOUT parameter to ALTER DATABASE. For example, to change the TIMEOUT value to 20 seconds (the default is 10), on the principal server issue:
-- Issue from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER TIMEOUT 20;
Finally, you can adjust the size of the redo queue on the mirror by issuing the ALTER DATABASE with the REDO_QUEUE option on the principal server. The following query will set the redo queue to 100 megabytes on the mirror:
-- Issue from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER REDO_QUEUE 100MB;
Original Document from
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/DatabaseMirroring.doc
Error 1418
I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating
Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.
This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command
master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]
I had to do this even though all SQL Services were running as a network service.