Inside Microsoft.com
Getting Started with Database Mirroring
Saleem Hakani
If your database goes offline, you’re probably up a creek, right? If you’re using SQL Server 2005 with SP1, however, the database mirroring feature can prevent disaster. This new high-availability technology allows you to maintain a hot standby of your database for use when your production database server becomes
unavailable for any reason. The database mirroring feature works by transferring transaction log records for a database from the primary server to the secondary server, which serves as the hot standby. With database mirroring, data changes are recorded in the transaction log before any changes to actual data pages are made, just as SQL Server™ updates always work. The log records are first placed in the principal database's log buffer in memory and then persisted to disk. Those transaction logs are copied to and replayed on the mirror server's database. This causes the principal's database changes to be duplicated on the mirror database. Note that only the principal database is accessible to client connections. When the principal database receives changes requested by clients, the principal server sends those active changes to the mirror server; the mirror does not make any of these decisions. When database mirroring is enabled and the principal database fails, the mirrored database becomes available.
The Mechanics of Database Mirroring
Database mirroring works with all the standard hardware that supports SQL Server 2005 and it ensures no data loss in the event of a database failure. The mirror database will always be updated with the current transaction that's being processed on the primary database server. Figure 1 illustrates the flow of data.
If your principal server does go down, you can rest assured that the mirror server has an exact point-in-time copy of the principal database as of the last committed transaction. Thus, the mirror is always ready to take over the principal server role.
.gif)
Figure 1 Data replication to the mirror (Click the image for a larger view)
In the database mirroring topology, you'll need a third server called the witness if you want to enable automatic failover from principal to mirror server and vice-versa. A witness server can be any computer that can support SQL Server 2005.
Operating Modes
The database mirroring topology you'll use will depend on the transaction safety and operating modes you've chosen. The operating modes that are supported by database mirroring include high-safety (with or without automatic failover) and high-performance.
High-Safety (with automatic failover) This mode supports maximum database availability with synchronous data transfer and automatic failover to the mirror database. This operating mode is best used when you have fast and very reliable communication between the principal and the mirror servers and you require automatic failover for a single database. In this scheme, the principal database waits to commit a transaction until it receives a message from the mirror server that the mirror server has hardened the transaction's log to the disk.
High-Safety (without automatic failover) This mode supports maximum database availability with synchronous data transfer but without automatic failover to the mirror database. In this mode, if the mirror server instance becomes unavailable, the principal server instance continues to function but will not be able to mirror the data. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.
High-Performance In this operating mode, the transfer of data is asynchronous. The principal server does not wait for an acknowledgment from the mirror as it does in the above two modes. The mirror server does its best to keep up with the principal, but it is not guaranteed at any point that all the most recent transactions from the principal will be hardened in the mirror server's transaction log. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.
Laying the Groundwork
Setting up database mirroring is a simple process if you establish a strong foundation using best practices:
Server Edition Double-check that both principal and mirror servers are running on the same edition of SQL Server 2005; you can use either the Standard or Enterprise edition.
Witness Server Availability If you are planning to use high-safety with automatic failover, then make sure that the witness server is available and that SQL Server 2005 (any edition) is installed. The witness server can run on any reliable computer system that can support SQL Server 2005.
Mirror Image Make sure that the mirror server instance has the identical jobs, logins, SQL Server Integration Service (SSIS) packages, disk partitions, file locations, and server configuration as the principal server instance. Configuring the mirror server exactly as the principal server will enable the mirror server to function the same way as the principal server does.
Full Recovery It's important that all the databases participating in database mirroring are set to FULL recovery model.
Master and TempDB Verify that all the server instances in a mirroring topology use the same Master and TempDB collation and code-page. Having different collation and code-pages can cause problems during database mirroring setup.
Back Up If the database to be mirrored is large, you must perform a full backup of the database first and then restore it on the mirror server instance using NORECOVERY option.
Plan Ahead Determine all the server names, port numbers, security accounts, and locations where databases would reside and document them. See the "Database Mirroring Best Practices" sidebar for a checklist.
Once you have taken care of the groundwork, you're ready to tackle setting up database mirroring in your environment.
Setting It Up
Let's set up database mirroring using the high-safety operating mode with automatic failover. (As noted, that means a witness server instance is required). For my example here I'll use the server and database names shown in Figure 2, which also specifies each server's role.
Keep in mind that because configuration can impact performance as the pending transaction log is copied from the principal server to the mirror server, you might want to perform the initial configuration of database mirroring during off-peak hours.
Setting up the mirror consists of three steps: creating endpoints on the participating servers, performing backup and restore of your principal database, and enabling mirroring sessions on all participating servers.
Before establishing a database mirroring session, you must establish the communication mechanism between all the servers participating in database mirroring. To accomplish this, create endpoints on all the servers by running this statement on both ServerA and ServerB:
Create Endpoint Mirroring_Endpoint
State= Started as TCP (Listener_Port=5001)
For Database_Mirroring (Role=Partner);
For ServerC (which will be acting as a witness), change the (Role=Partner) to (Role=Witness) and run the statement. This controls the TCP port that each instance listens on.
For the next step, you should perform a full database backup followed by a log backup of the DBM_Demo database from the principal server and then restore it on the mirror server instance using the NORECOVERY option. (Using NORECOVERY assures that the mirror database will be in the restoring state so that the transaction logs can be applied.)
Here's the T-SQL statement to perform a full database backup of DBM_Demo database from ServerA (the principal server instance):
Backup Database DBM_Demo to DISK='E:\MSSQL\Bak\DBM_Demo_FULL.bak';
If there are any changes to the database after you perform the full database backup, you may have to perform a log backup of the database; otherwise it may not be required.
If necessary, you can use the following T-SQL statement to perform a log backup of the DBM_Demo database from ServerA:
Backup Log DBM_Demo to Disk='E:\MSSQL\Bak\DBM_Demo_Log.bak';
Once you have all the backups performed, move the backup files to ServerB or to a shared location so you can restore these backups on ServerB. After you have done so, you should also restore any transaction-log backups you had performed since the last full database backup from ServerA.
You can use the following T-SQL statement to restore the full and log backups on ServerB using the NORECOVERY option:
--Restore full database backup on the mirror --server instance
Restore Database DBM_Demo from Disk='E:\MSSQL\Bak\DBM_Demo_FULL.bak' with NORECOVERY;
Finally, use the following T-SQL statement to restore the log backup on the mirror server using the NORECOVERY option:
Restore Log DBM_Demo from Disk='E:\MSSQL\Bak\DBM_Demo_Log.bak' with NORECOVERY;
Once you have finished restoring all the backups, you are ready to perform the final step-enabling your database mirroring session on all the participating servers.
Setting up a database mirroring session requires a server network address for each of the server instances. This address must identify the instance by providing a system address and the port number on which the instance is listening. The syntax for a server network address looks like this:
TCP://<System-address>:<port>
<System-address>: is a fully qualified domain name or an IP address; you can get this information by executing IPCONFIG on the local machine from a command prompt.
You established the <Port> when you created the endpoints.
You can start the database mirroring session on ServerB like so:
Alter Database DBM_Demo
Set Partner= 'TCP://ServerA.com:5001';
Then run the following T-SQL to start the session on ServerA:
Alter Database DBM_Demo
Set Partner='TCP://ServerB.com:5001';
Next, enable the mirroring session on ServerC (the witness server) like so:
Alter Database DBM_Demo
Set Witness='TCP://ServerC.com:5001';
Database mirroring is now ready to run in your environment. Any database objects that have been added or modified on the DBM_Demo database will be transferred to the ServerB copy. However, if ServerA's database becomes unavailable, a failover can occur, changing the mirrored database's role to the principal role.
Now that you have database mirroring up and running, you'll always have a hot standby ready if your production database ever goes down.
Saleem Hakani is a Senior Database Engineer at Microsoft with more than 14 years of experience with database systems. He founded and leads the Microsoft SQL Community and is responsible for delivering SQL Server standards and automation throughout the Windows Live organization. Saleem holds MCTS, MCDBA, and MCSA certifications. You can contact him at Saleem@sqlcommunity.net.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.