Mirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

noWaitMirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

Few days back one of the DBA in our company setup mirroring for one of our production database hosted in SQL Server 2008 R2. After setting mirroring up, our peak business hour started and we have started facing heavy blocking issues. Thanks to customized monitoring application we have developed which shows me that mirroring was in “Synchronizing” status not “Synchronized” and there were so many wait type “DBMIRROR_DBM_EVENT” which was blocking many sessions. Apart from that I have found that mirroring was having “High Safety” mode.

In highly transactional database, I prefer to have asynchronous mirroring “High Performance”. As soon as I changed the mirror safety mode and kill some heavy blocking process everything was up and running fine.

As per MSDN, High-performance operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.

So, High-performance comes at the cost. If it would be “High Safety” it wouldn’t release until data successfully reached to mirror but it take lot of resources so I used to keep “High Performance” for one of our highly transaction database only.

BTW, for checking mirroring safety mode and status, I have already written one script earlier in my blog post which is give here again:

[sourcecode language=”sql”]
SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
mirroring_state_desc,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_role_desc,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring
GO
[/sourcecode]

For wait_type, you can execute following DMV, which I have already explained in my blog post “CXPACKET wait stats in SQL Server”.

[sourcecode language=”sql”]
select * from sys.dm_os_wait_stats where wait_type=’DBMIRROR_DBM_EVENT’
GO
[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Database maintenance in mirroring principal database in SQL Server

Database maintenance in mirroring principal database in SQL Server

We have had certificate mirror setup in one of my earlier article along with mirroring monitor and mirroring status by script. I have one more topic to share with my blog reader for mirroring. After mirroring you may need some regular maintenance in principal database and you have to pause/stop mirroring for time being.

You have two choices in this situation.

1.)  Remove mirroring

2.) Pause mirroring for sometime

First choice should be avoided as long as possible as after removing mirroring, we have to set up mirroring all from scratch which may consume few hours if database is big so second option is obvious choice.

I had recent requirement in one of my production server which hosted SQL Server 2008 R2. I had to move my database file to faster IO subsystem and hence I had to take my database offline and move datafile to new IO subsystem. If database is mirrored, we can’t take it offline and hence I have to pause mirroring.

Here are the steps which I have used:

1.)  Stop transaction log backup, if it is setup

2.) Set your mirror partner off by following TSQL

[sourcecode language=”sql”]
–Execute following statement in Principal database server
ALTER DATABASE YourPrincipalDatabaseName SET PARTNER OFF
GO[/sourcecode]

3.) Now take your database offline and do maintenance you wanted to do

4.) Once your maintenance is over and your database is back online, takes transaction log backup manually and restores it in mirror (Slave) database in NORECOVERY mode

5.) Set your endpoint from mirror (Slave) instance. If you don’t know information about port and other details of endpoint, get that information from sys.tcp_endpoints DMV from mirror server.

[sourcecode language=”sql”]
–execute following SELECT query in mirror instance
SELECT state_desc,type_desc,port FROM sys.tcp_endpoints;

–set database partner with principal server.
–Following command should be run in mirror server
–I am using the same "TCP" ENDPOINT I can created in mirror article earlier
ALTER DATABASE YourMirrorDatabaseName SET PARTNER =’TCP://SQLX64.EDISON:5022′
GO[/sourcecode]

6.) Execute following commands in Principal server.

[sourcecode language=”sql”]
–execute following SELECT query in principal instance
SELECT state_desc,type_desc,port FROM sys.tcp_endpoints;

–set database partner with mirror server from principal server.
–Following command should be run in principal server
–I am using the same "TCP" ENDPOINT I can created in mirror article earlier
ALTER DATABASE YourPrincipalDatabaseName SET PARTNER =’TCP://MARS.EDISON:5022′
GO[/sourcecode]

This is how I do maintenance for my principal database.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles

Find mirroring status by querying sys.database_mirroring in SQL Server

Find mirroring status by querying sys.database_mirroring in SQL Server

I had already written articles about how to set mirroring and how to monitor mirroring with “Mirroring Monitor”. Today this is time to query “sys.database_mirroring” system table which contains record for each database available in SQL Server instance with NULL value in all column which starts with “mirroring_” if mirroring is not setup for the database.

sys.database_mirroring” gives your important information about principal/mirror database/server alongwith its status whether it is synchronized or not and what safety mode mirroring is configured on.

[sourcecode language=”sql”]
SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
mirroring_state_desc,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_role_desc,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring
GO
[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Check mirroring status from mirroring monitor in SQL Server

Check mirroring status from mirroring monitor in SQL Server

Mirroring is one of the best and easy to maintain high availability feature as long as SQL Server version is 2008 R2 or earlier. I wrote article about how to set certificate mirroring in SQL Server sometime back. Once you setup mirroring, you have to keep your eyes open about the status of mirroring whether it is “Synchronized” or still in “Synchronizing” mode, status is on or off etc.

SQL Server providing one powerful tool to keep watch on mirroring status, named “Mirroring Monitor” which you can ope from pop-up menu come from right click on Principal/Mirror database. Have a look at following screen capture for further information.

Once you click on “Launch Database Mirroring Monitor”, you have to add your server in the monitor and it will look like following screen capture:

This monitor will provide you with important information like send rate, restore rate, commit overhead etc along-with whether you principal and mirror and completely synchronized or still synchronizing.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Setup Certificate mirroring in SQL Server 2008 R2

Setup Certificate mirroring in SQL Server 2008 R2

Mirroring is my one of the favorite high availability feature till SQL Server 2008 R2 edition. I used to setup mirroring for every important production database I have in our production system if version is SQL Server 2008 R2 or less.

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.

Mirroring can be easily established between servers when they are in same network domain with fully trusted environment but what, if server are in different network or located at different places? Can’t we setup mirroring between those? Answer is yes, still we can setup mirroring if both servers are able to connect each other via any media like VPN or anything else.

Please note that mirroring is deprecated feature from SQL Server 2012 and will be removed from next version so it is good practice to use Always On Availability Group feature in SQL Server 2012.

I will have two servers with SQL Server installed.

Server 1 instance name is SQLX64 which is SQL Server 2008 R2 Enterprise edition on Windows Server 2008 Enterprise OS. This is primary (Principal) server.

Server 2 instance name is MARS which is SQL Server 2008 R2 Enterprise edition on Windows Server 2008 Enterprise OS. This is mirror server.

Both servers are located in same datacenter within same domain name Edison.

I want our database “SMXP” to be mirrored.

Before you start actually implementing steps given here, please confirm that your principal database is on full recovery mode.

Take full and latest transaction backup of your database and restore it to secondary/slave/mirror server with NORECOVERY option.

Execute following code in primary (Principal) server. In my case, it is SQLX64.

[sourcecode language=”sql”]–execute following script in SQLX64 which is principal server

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPa$$word’;
GO

–Create certificate in principal server and keep it safe for future use
CREATE CERTIFICATE SQLX64_mirroring_cert
WITH SUBJECT = ‘SQLX64 certificate’,
START_DATE = ‘2012-12-01 00:00:00’,
EXPIRY_DATE = ‘2025-12-31 00:00:00’
GO

— backup certificate which we have created in previous step
— and manually copy it to MARS which is our mirror server (Secondary server)
BACKUP CERTIFICATE SQLX64_mirroring_cert TO FILE = ‘D:\Database\SQLX64_mirroring.cer’;
GO
–creating endpoing for mirroring in principal server
CREATE ENDPOINT Mirroring_Endpoint
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLX64_mirroring_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
GO

— Create a login for MARS which will be used to connect MARS to SQLX64 server

CREATE LOGIN MARS_mirroring_login WITH PASSWORD = ‘StrongPa$$word’;
GO
— Create a user for new login we have created above
CREATE USER MARS_mirroring_user FOR LOGIN MARS_mirroring_login;
GO
[/sourcecode]

 

Execute following code in secondary (mirror/slave) server. In my case, it is MARS.

[sourcecode language=”sql”]–execute following script in MARS which is mirror server

USE master
GO

— setting up master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPa$$word’;
GO

— creating certificate, keep it safe for future use
CREATE CERTIFICATE MARS_mirroring_cert
WITH SUBJECT = ‘MARS certificate’,
START_DATE = ‘2012-12-01 00:00:00’,
EXPIRY_DATE = ‘2025-12-31 00:00:00’
GO

— Backup certificate and coppy it manually to SQLX64
BACKUP CERTIFICATE MARS_mirroring_cert TO FILE = ‘D:\Database\MARS_mirroring.cer’;
GO

–creating endpoint
CREATE ENDPOINT Mirroring_Endpoint
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE MARS_mirroring_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
GO

— Create a login for the SQLX64 in MARS
CREATE LOGIN SQLX64_mirroring_login WITH PASSWORD = ‘StrongPa$$word’
GO

— Create a user for the new login we have created above.
CREATE USER SQLX64_mirroring_user FOR LOGIN SQLX64_mirroring_login;
GO
[/sourcecode]

After restoring full and latest transaction backup of principal database to mirror database and performing all of the above given steps.  we are just few steps away now.

We had created certificate in principal as well as in mirror server and I have mentioned that copy certificate backup to other server. Hope you have copied Principal server’s certificate to mirror server and vice versa.

Execute following code in primary (Principal) server. In my case, it is SQLX64.

[sourcecode language=”sql”]

— Associating MARS’s certificate in our primary server SQLX64

CREATE CERTIFICATE MARS_cert
AUTHORIZATION MARS_mirroring_user
FROM FILE = ‘D:\Database\MARS_mirroring.cer’
GO

–we have to grant permission to login for connecting with ENDPOINT
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [MARS_mirroring_login]
GO
[/sourcecode]

Execute following code in secondary (mirror/slave) server. In my case, it is MARS.

[sourcecode language=”sql”]

—- Associating SQLX64’s certificate in our mirror server MARS

CREATE CERTIFICATE SQLX64_mirroring_cert
AUTHORIZATION SQLX64_mirroring_user
FROM FILE = ‘D:\Database\SQLX64_mirroring.cer’
GO

–we have to grant permission to login for connecting with ENDPOINT
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLX64_mirroring_login]
GO

— Setting up partnership between principal and mirror
–provide with fully qualified server.domain name or IP.

ALTER DATABASE SMXP SET PARTNER =’tcp://SQLX64.Edison:5022′
GO

[/sourcecode]

 

Execute following code in primary (Principal) server. In my case, it is SQLX64.

[sourcecode language=”sql”]

— Setting up partnership between principal and mirror
–provide with fully qualified server.domain name or IP.

ALTER DATABASE SMXP SET PARTNER =’tcp://MARS.Edison:5022′
GO

[/sourcecode]

After performing all steps given above, mirroring will be ready and looks something like this:

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.