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

 --Execute following statement in Principal database server
 ALTER DATABASE YourPrincipalDatabaseName SET PARTNER OFF
 GO

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.

 --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

6.) Execute following commands in Principal server.

 --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

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

One thought on “Database maintenance in mirroring principal database in SQL Server

Comments are closed.