SQL 2012 Mirroring
Experts, have 2 SQL 2012 SP2 servers setup in mirroring. I need to take the principal down for maintenance and have the secondary become the principal, after maintenance I need to reverse the roles as well. What is the process to do this?
Deepak Chauhan
How database mirroring is configured? Is it within Always On group or without.
And which operating mode is configured.
ASKER
Without Always On Group.
ASKER
Mirroring state description is Synchronized and mirroring_safety_level_des
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Above are the things you have to check before role switching.
ASKER
I have the same logins on the synchronized server. I was going to Failover using SQL Management Studio to secondary server and then after roles are reversed pause mirroring. Does that sound similar to what you mention?
ASKER
What do you mean by long time? We have some changes to be made to the disks so we are anticipating upto 48 hours of maintenance.
ASKER
How much time has the failover taken in your experience? Our databases vary in size from 100Gb to 900GB. Thanks
@I have the same logins on the synchronized server.
That good but after failover please check these logins are mapped with database and able to access.
In addition- login are not only the things you have to copy the agent jobs (like backup or ssis which use this database) and cross database dependency. If no dependency , we are good here.
@ I was going to Failover using SQL Management Studio to secondary server and then after roles are reversed pause mirroring. Does that sound similar to what you mention?
Yes .
@What do you mean by long time?
I meant like one week. Because transaction log will not be truncated and keep growing untill log hardening on mirror server when it comes back.
@How much time has the failover taken in your experience? Our databases vary in size from 100Gb to 900GB.
Since both partners are in sync as of now, so failover would not take time it will be immediately.
when mirror will come back it depend on the amount of 48 hrs transaction, once all transactions are synchronized you can failback to former Principal server.
That good but after failover please check these logins are mapped with database and able to access.
In addition- login are not only the things you have to copy the agent jobs (like backup or ssis which use this database) and cross database dependency. If no dependency , we are good here.
@ I was going to Failover using SQL Management Studio to secondary server and then after roles are reversed pause mirroring. Does that sound similar to what you mention?
Yes .
@What do you mean by long time?
I meant like one week. Because transaction log will not be truncated and keep growing untill log hardening on mirror server when it comes back.
@How much time has the failover taken in your experience? Our databases vary in size from 100Gb to 900GB.
Since both partners are in sync as of now, so failover would not take time it will be immediately.
when mirror will come back it depend on the amount of 48 hrs transaction, once all transactions are synchronized you can failback to former Principal server.
ASKER
Great info Deepak. We tried failing over the database last night, failover took 2 seconds and the secondary became principal but when users tried to login they got error unable to login. Once it became Principal I checked the database and it had the same login already in it. Only thing I noticed was that under Security SQL Management Studio was not allowing me to map that user to the database and got error - User already exists in the database
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Deepak, the above info you mentioned also worked perfectly. I ran the above commands and was able to get access to the database from the app. On failover back though I had to rerun the command on the primary so as to get the old primary running.
Is there a way using SQL to check the logins on both Principal and secondary before failover? This will just save us time the next time we failover
Thanks
Is there a way using SQL to check the logins on both Principal and secondary before failover? This will just save us time the next time we failover
Thanks
You will not face this problem if login SID are same on both partner. I dont think this is possible to check login mapping with database before failover because on mirror server database is inaccessible.
But you can avaiod the login issue by copying the login with SID from principal to mirror.
Generate a SQL login script on principal server and run it on mirror server, You have to match the SID of SQL login on both server. This problem will go away.
But you can avaiod the login issue by copying the login with SID from principal to mirror.
Generate a SQL login script on principal server and run it on mirror server, You have to match the SID of SQL login on both server. This problem will go away.