Mirroring in SQL Server 2012 R2

SQL Server 2012 Mirroring Mahesh Dahal 1/7/13 Mirroring MSSQL Server 2012

Mirroring a SQL Server Database on SQL Server 2012 R2 Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. However, this feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead. I will try to Blog the same in future Blogs. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss). The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database. Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions. Benefits of Mirroring Database mirroring is a simple strategy that offers the following benefits: Increases availability of a database. In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (without data loss). In the other operating modes, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database. For more information, see Role Switching, later in this topic.

Increases data protection. Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance. For more information, see Operating Modes, later in this topic. A database mirroring partner running on SQL Server 2008 Enterprise or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error. Improves the availability of the production database during upgrades. To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are hosting the failover partners. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade. For more information, see Install a Service Pack on a System with Minimal Downtime for Mirrored Databases. Prerequisites: 1. For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server. 2. The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring. 3. The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database. For information about recovery models, see Recovery Models (SQL Server). 4. Verify that the mirror server has sufficient disk space for the mirror database. 5. When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY. 6. It is recommended that all the primary, secondary and Witness server be on the AD.

Step 1. Confirm the Recovery Model Mirroring only works in Full Recovery Model. So it is important that you confirm or change the recovery model to FULL. 1. 2. 3. 4. Right click on the database Go to Properties GO to Options In the Recovery Model Window Change the option to FULL Step 2. Prepare the Backup 5. Take Full Backup of the Primary DB Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Step 3. Restore the Backup 6. Take a Transaction log backup of the Primary DB

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com 7. Restore the DB in secondary server with same name and No Recovery Model

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com 8. Restore the Transactional Log Backup in the Secondary DB and use No Recovery Model 9. Go to the Primary Server right Click on Database, Go to properties.

Step 4. Configure the Mirroring It is advised to connect to all the instances and server from a single machine. Here I am connecting to two server and three instances. Server1, Server2 and Server1witness Mirroring on SQL Server 2012 Server 2012 Mirroring on SQL Mahesh Dahal Mahesh Dahal maheshdahal@live.com maheshdahal@live.com

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com 10. GO to primary Server and Right Click on the Database and go to properties 11. Then go to Mirroring

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com

Click on Configure Security and Follow the Steps Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Click on Next

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Click on Yes. You can also Choose No, If you like to Setup the Mirroring without the Witness. Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Give your Domain Administrators User account for all three Instances.

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Click Start Mirroring Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com

Mirroring on SQL Server 2012 Mahesh Dahal maheshdahal@live.com Your Database has been mirrored. You can can see the status next to the database. You can also go to the Mirroring page and manually failover to confirm the Mrroring. Mahesh Dahal Microsoft Innovation Center, Nepal maheshdahal@live.com

Mirroring in SQL Server 2012 R2

More Related Content

What's hot

Similar to Mirroring in SQL Server 2012 R2

More from Mahesh Dahal

Recently uploaded

In this document

Mirroring in SQL Server 2012 R2