70-457 Reviewer #15 Implement Database Mirroring

This reviewer is part of lesson in Implementing High Availability in SQL Server 2012. This covers about 12% of the certification exam.

Database mirroring is a process of creating and maintaining an always up-to-date copy of your database on another SQL Server instance. Transactions applied to principal instance is also applied to the mirrored instance.

There are two modes you can apply when setting up database mirroring in your environment.

High-safety mode enables failover to occur without data loss from committed transactions. Hot standby is possible when you are using the high-safety mode. When using this mode, transactions are committed on both partners after they are synchronised. The main drawback here is that there is increase in transaction latency. If a witness server is present you can enable automatic failover in this mode.

High-performance mode enables failover to occur, but data loss is possible. During this mode, the primary instance does not wait for the mirror instance to confirm that it has received the log.

Database Mirroring Prerequisites

When setting up mirroring, remember that you can mirror only databases that are using the full recovery model. Database mirroring supports only a single principal instance and a single mirror instance.

The principal, mirror and witness instance must all be running the same version of SQL Server. The primary and mirror instance must run the same edition, whereas the witness instance must run on SQL Server edition that supports witnessing.

When starting to setup mirroring session, do a restore of the database in the mirror instance using the NO RECOVERY option. You will need a full backup and a transaction log backup of the database from the principal instance. Then restore the full backup and followed by restoring also the transaction log backup still using the NO RECOVERY option.

After having successfully completed the above, your database in the mirror instance must be in ‘RESTORING’ status.

If you are configuring your mirroring setup to use Windows authenticated SQL service accounts, make sure that your principal, mirror and witness (if you have one) are members of the same active directory domain or trusted domains.

Configuring Database Mirroring

After all the prerequisites from above are followed, you can now start to configure mirroring in your environment.

In your SSMS, right click on the database you want to mirror, select Tasks then select Mirror. This will open the Database Mirroring wizard.

On the mirroring page, click Configure Security to open the Configure Database Mirroring Wizard. Click Next.

On the Include Witness Server Page, choose No if you are not setting up witness server, else choose Yes.

On the Choose Servers to Configure page, choose whether to save security configuration information on the Witness server instance. This is saved by default on the Principal and Mirror server instances.

On the Principal Server Instance page, verify if the default listener port or configure an alternate port.

On the Mirror Server Instance page, click Connect to open Connect to Server dialog box. You need to make this connection to the mirror instance by using a login or an account that has a CREATE ENDPOINT permission, or use a sysadmin account.

On the Witness Server Instance page, choose your witness instance and click Connect to open Connect to Server dialog box. Again for this case, when connecting to your witness server, use an account with CREATE ENDPOINT permission, or use a sysadmin account.

On the Service Accounts page, enter the domain accounts that are to be used for SQL Service on each instance. Click Next.

After verifying your setup, click Finish.

If you are setting up your mirroring using a certificate authentication, you will need to backup your certificate in the principal instance and copy them to your witness and mirror instance so you can create logins and users based on these certificates. After the logins and certificates for the mirror instance has been created, you need to grant permissions to the login on the endpoint.You may run statement below

GRANT CONNECT ON ENDPOINT :: EndpointMirror TO [login];
GO

Go back to your principal instance, right click on the database your are going to mirror, select Tasks, select Mirror and click Start Mirroring. You are now mirroring your principal database depending on your selected mirror mode. If the configuration is successful, your principal database should now be on ‘Principal’ status.

Database Mirroring Monitor

To verify your mirroring status and monitor, open your SSMS, right click on the principal database, select Tasks, and click on Launch Mirroring Monitor. This will open the mirroring status window similar to below.

Database Mirroring Monitor

From this window, you can view the status of your mirrored database, check size of unsent logs, data transfer rate and oldest unsent transaction. Database mirroring monitor can also provide the status of the connection to the witness if one is present, the witness address and the operating mode.

Alternatively, you may run the T-SQL statement below to check that status of your mirrored database.

SELECT mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc,
mirroring_partner_instance, mirroring_partner_name
FROM sys.database_mirroring;

Similarly, there are built in stored procedures in SQL Server 2012 to manage monitoring of mirrored databases;

sp_dbmmonitoraddmonitoring;
sp_dbmmonitordropmonitoring;
sp_dbmmonitorchangemonitoring;
sp_dbmmonitorresults;

Future of Mirroring

Microsoft intends to remove mirroring in the future releases of SQL Server. Plan on implementing AlwaysOn availability groups. This will be my topic in my next post :)

About these ads

One response to “70-457 Reviewer #15 Implement Database Mirroring

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server

Coding Tips

We will improve your knowledge

SQL Studies

My SQL Studies

It's Hemlock Time

We don't make sense. It's innate.

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Microsoft BI consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

AccessAdp.com

SQL Server Architect | MCTIP: Database Administrator | Microsoft Office | Hadoop

Follow

Get every new post delivered to your Inbox.

Join 46 other followers