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

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Database maintenance in mirroring principal database in SQL Server”

Comments are closed.