page contents

About the Post

Author Information

Creating a SQL Server mirror on an existing mirror

This is an article that I have been meaning to write for months. It was very exciting for me when I was trying to solve this issue and I was even more pleased with performing this task when I found a few DBA’s had never attempted this operation. I want to share that knowledge with you today.

Little background about this configuration:We had a production 2008 R2 SQL Server that was configured as Synchronous Database Mirroring (High-Safety Mode) which means we were writing to both nodes at the same time and had a witness server for automatic fail-over. Here is a great description from Books Online about our mirroring:

To achieve synchronous operation for a session, the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction’s log to disk. Note the wait for this message increases the latency of the transaction.

The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue. For more information, see Database Mirroring Sessions.

We were limited on space (isn’t that always the case?) and were only doing mirroring on key databases. However, as the application started to mature, another database was created to capture additional monitoring and a decision was made to store this database on the witness server. At the time of this decision, there was plenty of disk space.

As we know, the more time passes, the more things grow and features get added, which means the database was growing at an alarming rate. I did a lot of monitoring on the database and created jobs to purge data that was no longer required due to our retention period. For 6 months, I made sure we had enough space to prevent auto growth and that we kept the database with at least 10% of free disk space. Sadly, it just got to a point where we literally had less than a week left before we would fill up the drive.

I had to come up with a solution and it needed to be tested, plus done quickly. To make matters worse, I had a lot on my plate that was just as important and I could not engage anyone else for assistance. I need to perform 2 critical operations that same week and get this database taken care of ASAP.

My initial thoughts were that I could either do a restore or attempt to mirror the database to the new server that was built earlier. I first tried the restore method, but I had reservations about it, since I knew the backup took a very long time. The problem I ran into was the backup was timing out due to various issues. I was more intrigued by the mirroring option, so I decided to test that approach and ended up learning a lot.

More importantly, I was successful in getting this database migrated to the new server that has plenty of disk space. I would like to show you how I proceeded.

The current servers in the mirror were the following:

SQLM101 – Principal

SQLM102 – Mirror

SQLW101 – Witness server and additional DB added for monitor logs

SQLW102 – New Witness server with additional DB added for monitor logs

At first, when I attempted this process, I was not very knowledgeable about SQL Server end points and I did not understand there were certain roles for each type of end point. Luckily for me I was able to reach out to Robert Davis (Blog | Twitter) who helped me understand this topic better and then I found this great article in Books Online sys.database_mirroring_endpoints (Transact-SQL)

I was then able to come up with this script to do a test for active end points and also see how they are configured:

–Check for SQL Endpoints

select * from sys.database_mirroring_endpoints


* Not all results are shown here (please note role_desc field)

The screen shot below shows you the end points in SQL Server Management Studio (SSMS) and the results of the script.


When I ran this script, I was very interested in was the role_desc field which shows me how this end point is configured, which is only for the Witness Role. It can’t be used for any other type, such as None, Partner or All. Then I ran some more queries to get more details on the end points.

–Check for end point connections

select * from sys.dm_db_mirroring_connections

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc

FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON e.endpoint_id = t.endpoint_id

–Check for SQL Endpoints (simple view)

select * from sys.endpoints

–Check for SQL Endpoints (more options to see which ones are in the mirroring state)

select * from sys.database_mirroring where mirroring_state is not null

–to see who has granted Mirroring and the grantee

SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

I ran this script (above) on all of the current SQL Server Mirror pairs and it let me know a couple of key things: the role of the end point and the encryption method. This would allow me to create a new end point on the new SQL server. I did also check that there was no current endpoints, as seen below:


Here is the script that I ran to create the new endpoint to match the others, please note the role is all:

/****** Object: Endpoint [Mirroring] Script Date: 08/21/2012 15:17:17 ******/

CREATE
ENDPOINT [Mirroring]

    AUTHORIZATION [SMATTIE\stangsct]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

GO


This would allow me to mirror on top of the existing mirror from the stand point of the new server. I still needed to modify the existing Witness server to move the role from “witness” to “all.” Again, Books Online was very valuable and helped me get the information I needed via this article: ALTER ENDPOINT (Transact-SQL)

I ran this script one section at a time so I could see a before and after:

–Check for SQL Endpoints

select * from sys.database_mirroring_endpoints

— Change Endpoint role

Alter Endpoint [Mirroring] For Database_Mirroring (Role = All);

–Check for SQL Endpoints

select * from sys.database_mirroring_endpoints


At this point, I was ready to proceed with copying the database backups and then start the mirror. (SQLW101 and SQLW102)


Then I restored the database on the new Witness server and made sure that it was set to NORECOVERY.


It should look like this when you restore the database:


Now, I went ahead and setup the mirror (using the wizard for this demo) Click Configure Security


It will now bring up the security wizard:


We are migrating the current witness; there is no need for a new witness server


Add the Principal (primary) server


Add the Mirror (secondary) server, by selecting Connect


Then log into the server and click Next


Choose your service account:


Click Finish


Click Close, if no errors are mentioned:



Now click Start Mirroring


If all was successful, then you will see this status
listed (below) next to the database:


After the database is caught up and mirrored, then you can fail over the database to the new server and then break the mirror between the 2 witness servers. The next step is to add the new server to the mirror pair by removing the old witness server and then added the new witness server. All that is left at that point is to modify your database connection strings for your application(s).

That is how you can build a mirror on top of an existing mirror.

Tags: , , , , ,

One Response to “Creating a SQL Server mirror on an existing mirror”

  1. stuff like this should be translated and published

    June 5, 2013 at 2:14 AM
Copy Protected by Chetan's WP-Copyprotect.