Database Mirroring in SQL Server 2005
----------------------------------------------------
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.
Database Mirroring Overview
In database mirroring, an originating SQL Server 2005 instance continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance. 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 all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database's log buffer in memory, and then flushed to disk (or 'hardened') as quickly as possible. In database mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records to the mirror instance.
When the mirror server receives a block of log records, it places the log records first into the mirror database's log buffer and then hardens them to disk as quickly as possible. Those transaction log records are later replayed on the mirror. Because the mirror database replays the principal's transaction log records, it duplicates the database changes on the principal database.
The principal and mirror servers are each considered a partner in the database mirroring session. A database mirroring session consists of a relationship between the partner servers when they mirror a database from one partner to another. A given partner server may have the principal role for one database and a mirror role for a different database.
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.
· 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. (See 'Database Mirroring and Database Snapshots' later in this paper.)
Note: For more information about the terms related to database mirroring, see "Overview of Database Mirroring" in SQL Server 2005 Books Online.
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.
Table 1: Database Mirroring Operating Modes
Operating Mode
Transaction safety
Transfer mechanism
Quorum required
Witness server
Failover Type
High Availability
FULL
Synchronous
Y
Y
Automatic or Manual
High Protection
FULL/Synchronous
If safety is FULL and a witness is set, synchronous data transfer will occur, and a quorum is required for database service. A quorum vote requires at least two servers to decide which role, principal or mirror, each of the two partner servers should play.
In order to explore the three operating modes in more detail, let's first take a closer look at transaction safety and the role of a quorum.
Ref: http://msdn.microsoft.com/en-us/library/cc917680.aspx
----------------------------------------------------
How to: Configure a Database Mirroring Session (SQL Server Management Studio)
To establish a database mirroring session and to modify the properties of database mirroring for a database, use the Mirroring page of the Database Properties dialog box.
Before you use the Mirroring page to configure database mirroring, ensure that the following requirements have been met:
The principal and mirror server instances must be running the same edition of SQL Server—either Standard or Enterprise. Also, we strongly recommend that they run on comparable systems that can handle identical workloads.
Note:
The witness server instance can run on SQL Server Standard, Enterprise, Workgroup, or Express.
The mirror database must exist and be current.
Creating a mirror database requires restoring a recent backup of the principal database (using WITH NORECOVERY) on the mirror server instance. It also requires taking one or more log backups after the full backup and restoring them in sequence to the mirror database (using WITH NORECOVERY). For more information, see How to: Prepare a Mirror Database for Mirroring (Transact-SQL).
If the server instances are running under different domain user accounts, each requires a login in the master database of the others. If the login does not exist, you must create it before configuring mirroring. For more information, see How to: Allow Database Mirroring Network Access Using Windows Authentication (Transact-SQL).
Ref: http://technet.microsoft.com/en-us/library/ms188712.aspx
----------------------------------------------------
Tasks
How to: Pause or Resume a Database Mirroring Session (SQL Server Management Studio)
http://technet.microsoft.com/en-us/library/ms175082.aspx
How to: Set Up a Mirror Database to Use the Trustworthy Property
http://technet.microsoft.com/en-us/library/ms345401.aspx
How to: Remove Database Mirroring (SQL Server Management Studio)
http://technet.microsoft.com/en-us/library/ms190471.aspx
How to: Add or Replace a Database Mirroring Witness (SQL Server Management Studio)
http://technet.microsoft.com/en-us/library/ms365603.aspx
Concepts
Automatic Failover
http://technet.microsoft.com/en-us/library/ms189590.aspx
Preparing a Mirror Database for Mirroring
http://technet.microsoft.com/en-us/library/ms189053.aspx
Forced Service (with Possible Data Loss)
http://technet.microsoft.com/en-us/library/ms189977.aspx
Managing Logins and Jobs After Role Switching
http://technet.microsoft.com/en-us/library/ms191458.aspx
Manual Failover
http://technet.microsoft.com/en-us/library/ms191449.aspx
Managing Metadata When Making a Database Available on Another Server Instance
http://technet.microsoft.com/en-us/library/ms187580.aspx
Other Resources
Database Properties (Mirroring Page)
http://technet.microsoft.com/en-us/library/ms183684.aspx
Setting Up Database Mirroring
http://technet.microsoft.com/en-us/library/ms190941.aspx
----------------------------------------------------
How to: Prepare a Mirror Database for Mirroring (Transact-SQL)
The mirror database must exist before a database mirroring session can begin. The name of the mirror database must be the same as the name of the principal database. The database owner or system administrator can create the mirror database from a recent full backup of the principal database and at least one subsequent log backup. For mirroring to work, the mirror database must remain in the RESTORING state. Therefore, when you restore a backup to a mirror database, you must always use WITH NORECOVERY for every restore operation.
If mirroring has been removed and the mirror database is still in the RECOVERING state, you can restart mirroring. However, first, at least one log backup must be taken on the principal database. Then, on the mirror database, you must restore WITH NORECOVERY all log backups that were taken on the principal database since mirroring was removed.
Note:
You cannot mirror the master, msdb, temp, or model system databases.
Ref: http://technet.microsoft.com/en-us/library/ms189047.aspx
----------------------------------------------------
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment