Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Mirroring failover Expand / Collapse
Author
Message
Posted Friday, February 1, 2013 11:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 419, Visits: 2,591
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.
Post #1414769
Posted Friday, February 1, 2013 12:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 6,418, Visits: 13,808
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"
Post #1414798
Posted Friday, February 8, 2013 8:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 419, Visits: 2,591
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.
Post #1417734
Posted Friday, February 8, 2013 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 6,418, Visits: 13,808
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"
Post #1417769
Posted Friday, February 8, 2013 11:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 AM
Points: 283, Visits: 1,118
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
Post #1417859
Posted Friday, February 8, 2013 12:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 419, Visits: 2,591
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.
Post #1417872
Posted Friday, February 8, 2013 1:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 6,418, Visits: 13,808
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"
Post #1417883
Posted Friday, February 8, 2013 3:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 AM
Points: 283, Visits: 1,118
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
Post #1417930
Posted Friday, February 8, 2013 6:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 419, Visits: 2,591
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.
Post #1417966
Posted Monday, February 11, 2013 12:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:08 AM
Points: 102, Visits: 416
How many databases we can put in mirroring

Also if automatic failover happens all db failover start same time ?
Post #1418231
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse