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?
abhijitm00Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
How database mirroring is configured? Is it within Always On group or without.
Deepak ChauhanSQL Server DBACommented:
And which operating mode is configured.
abhijitm00Author Commented:
Without Always On Group.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

abhijitm00Author Commented:
Mirroring state description is Synchronized and mirroring_safety_level_desc is Full
Deepak ChauhanSQL Server DBACommented:
SQL server logins
Windows logins exists in local windows group and mapped with sql server
AD group and users mapped with SQL server
AD Usrs exists in local Windows group and group mapped with sql server
SQL server agent JOB
interdependency between databases Like principal DB and other DB exist on principal server.
Windows schedule task
SSIS\DTS pacjages
XP_cmdshell, DBmail, CLR configuration setting
OS level permissions such as server logins and shared folders or backupfolders

IF all set you can switch the role and simply down the node or you can first pause the mirroring session

Command :: ALTER DATABASE mirrordatabase SET PARTNER SUSPEND;

once you are done with maintenance you can resume it back

ALTER DATABASE MirrorDB SET PARTNER Resume;

However once partner is not available mirroring automatically goes into suspended state.

NOTE::
Transaction log of the database would not be truncated while mirroring session is suspended. If maintenance activity will take long time it will be good, break mirroring completely and reconfigure when partner node available.
Because TranLog will grow huge.

If partner can available soon you can put it in pause state.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
Above are the things you have to check before role switching.
abhijitm00Author Commented:
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?
abhijitm00Author Commented:
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.
abhijitm00Author Commented:
How much time has the failover taken in your experience? Our databases vary in size from 100Gb to 900GB. Thanks
Deepak ChauhanSQL Server DBACommented:
@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.
abhijitm00Author Commented:
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
Deepak ChauhanSQL Server DBACommented:
@User already exists in the database

In this case no worry about windows users they can access if exists in the database. But for SQL login.... in mirroring... here is the steps.

1 . Failover from principal to mirror.

2 Remap the login.
use <databaseName>
go

sp_change_users_login 'report'  --- This sp will show you orphan user.  Now check the login exists on the server for these users. you can chek it in server security folder. if login exists there you can remap using this command. if login does not exist you have to create that login for the database user and remap using the same command.

sp_change_users_login 'auto_fix', '<orphanUserName>'

Open in new window

abhijitm00Author Commented:
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
Deepak ChauhanSQL Server DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.