Mirroring failover

Question

Mirroring failover

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
February 1, 2013 at 11:06 am

#288783

Hi All,
I have to failover couple of the prod databases.....which resides in cluster.
What are some of the things I need to do before and after failover. I know I have to check for orphan users after failover.
Does anyone have any scripts for failover....I've done it using SSMS. Also, script for checking orphan users and fixing the orphan users if you find any.
I have this script for checking orphan users. Can anyone explain what the second part of this script doing, how is it fixing the orphan users?.....I know orphan users are users without respective logins. Please adivse.
--Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)
AND suser_sname(sid) IS NULL
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

SQLCrazyCertified (2/1/2013)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)

This first part declares a cursor checking sql server user accounts in the specified database ensuring the SID is not null and the user is not dbo.

SQLCrazyCertified (2/1/2013)
AND suser_sname(sid) IS NULL

This part compares the database user SID to the server level logins and if its null no login exists.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

Perry Whittle (2/1/2013)
SQLCrazyCertified (2/1/2013)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)
This first part declares a cursor checking sql server user accounts in the specified database ensuring the SID is not null and the user is not dbo.
SQLCrazyCertified (2/1/2013)
AND suser_sname(sid) IS NULL
This part compares the database user SID to the server level logins and if its null no login exists.

Hi Perry,

So, basically what it is doing is, it is creating the login using the SID of the user? and mapping it?......If my understanding is correct, I can understand if it's Windows authentication, but if it's SQL authentication, how does this work?....Please let me know if my understanding is correct?

Thanks,

SueTons.

Regards,
SQLisAwe5oMe.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

SQLCrazyCertified (2/8/2013)
Hi Perry,
So, basically what it is doing is, it is creating the login using the SID of the user? and mapping it?..

No, it's not!

Its finding a database user and checking for a matching server login where the SIDs are different. It then uses the UPDATE_ONE parameter for sp_change_users_login which changes the user SID in the database to match the SID of the server login. If it did it the other way round it could break every other database that linked to the server login 😉

SQLCrazyCertified (2/8/2013)
I can understand if it's Windows authentication, but if it's SQL authentication, how does this work?....Please let me know if my understanding is correct?
Thanks,
SueTons.

Windows authentication does not suffer from orphaned users.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 4

Often, you find yourself failing over several databases, or several hundred in a real disaster scenario.

This script checks all databases in one go, and avoids use of a cursor.

exec sp_msforeachdb 'use [?];

declare @sql varchar(max)

select @sql = s from (

select ''use ['' + db_name() + '']; exec sp_change_users_login ''''Update_One'''', '''''' + d.name + '''''', '''''' + d.name + '''''';''

from sys.database_principals d

join sys.server_principals p on d.name = p.name

left join sys.server_principals o on d.sid = o.sid

where o.name is null

and d.name not in (''public'')

for xml path('''')) x(s);

if @sql is not null exec(@sql)

'

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

Richard Fryar (2/8/2013)
Often, you find yourself failing over several databases, or several hundred in a real disaster scenario.
This script checks all databases in one go, and avoids use of a cursor.
exec sp_msforeachdb 'use [?];
declare @sql varchar(max)
select @sql = s from (
select ''use ['' + db_name() + '']; exec sp_change_users_login ''''Update_One'''', '''''' + d.name + '''''', '''''' + d.name + '''''';''
from sys.database_principals d
join sys.server_principals p on d.name = p.name
left join sys.server_principals o on d.sid = o.sid
where o.name is null
and d.name not in (''public'')
for xml path('''')) x(s);
if @sql is not null exec(@sql)
'

Thanks Perry for explaining.

Richard, Can you please explain the script above, if you don't mind. Is it going through each database that I failover and checking for orphan users and fixing them at the same time?

SueTons.

Regards,
SQLisAwe5oMe.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Richard Fryar (2/8/2013)
This script checks all databases in one go, and avoids use of a cursor.

Have you seen the code for sp_MSForeachdb????

SQLCrazyCertified (2/8/2013)
Is it going through each database that I failover and checking for orphan users and fixing them at the same time?
SueTons.

No it's going through every user database on the instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 7

Ha ha you're right - and i have no issue with an underlying cursor for a one-off task like this, though i know there are some who disagree with me and avoid them at all costs.

I've also seen comments from people who don't like sp_msforeachdb but i find it a real timesaver.

Regarding the original question - I omitted to answer the other part. As well as fixing orphans you also need to make sure that other objects are on the mirror, such as missing logins, agent jobs, linked servers, credentials...

These cannot be left until you failover in case the principal is not available, so you need to keep the instances synchronized regularly. For some objects it can be scripted (logins, jobs) but for others you have to do it manually (you need to know the password to create credentials).

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 8

Richard Fryar (2/8/2013)
Ha ha you're right - and i have no issue with an underlying cursor for a one-off task like this, though i know there are some who disagree with me and avoid them at all costs.
I've also seen comments from people who don't like sp_msforeachdb but i find it a real timesaver.
Regarding the original question - I omitted to answer the other part. As well as fixing orphans you also need to make sure that other objects are on the mirror, such as missing logins, agent jobs, linked servers, credentials...
These cannot be left until you failover in case the principal is not available, so you need to keep the instances synchronized regularly. For some objects it can be scripted (logins, jobs) but for others you have to do it manually (you need to know the password to create credentials).

Yes, thanks....I took care about the other objects and took care of it. I had some issue with fixing orphan users and I fix it manually and found this script to fix it automatically but I wanted to understand the script before I use it in production.

SueTons.

Regards,
SQLisAwe5oMe.

Akkare SSCrazy Points: 2848 More actions · Answer 9

How many databases we can put in mirroring

Also if automatic failover happens all db failover start same time ?

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 10

Akkare (2/11/2013)
How many databases we can put in mirroring
Microsoft suggests 10 databases to be mirrored in a single instance, but I have seen more. I guess there will be some performance degradation if there are more than 10.
Also if automatic failover happens all db failover start same time ?

Regarding auto failover, it's depends on which database has issues with mirroring.....if you are asking about a scenario where the whole instance going down and each of the mirroring failover to mirror side.....then, I still think it will failover one by one. I did not encounter this scenario....as of now.

Regards,

SueTons.

Regards,
SQLisAwe5oMe.

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 11

I have come across the below nice tutorials.

Refer http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx

Automatic failover failovers all the principal databases to mirror server .

Regards,
Kumar