SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring failover


Mirroring failover

Author
Message
SQLisAwE5OmE
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
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.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19794 Visits: 17242
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
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
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
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19794 Visits: 17242
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
Richard Fryar
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 1172
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
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
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
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19794 Visits: 17242
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
Richard Fryar
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 1172
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
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
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
Akkare
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 451
How many databases we can put in mirroring

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search