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


Dear All this post is about logshipping DC- DR Drill [switch over and switch back (or)failover and...


Dear All this post is about logshipping DC- DR Drill [switch over and switch back (or)failover and failback ]

Author
Message
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
Iam a sql server dba handling 3.5 to 5 yrs wrk
i too googled and taken feedback from so many but i did'nt got clear idea so i mergerd all into a action and really tried so many times,i worked it in my laptop after so many times then i prepared this succesfully now it is working and i hope it will be useful to many like me as in early stage.of their career.




see below in detailed for logshiping DC [primary]-DR[secondary] Drill in sql server.
-----------------------------------------------------------------------------------


--- TAKE CONFIRMATION FROM APPLICATION TEAM WHETHER ALL APPLICATION SERVICES ARE STOPPED PROPERLY-----
1. take full backup in DC FOR the DATABASE
2. run lsbackupjob and disable in DC
3. run lscopyjob and disable in DR
4. run lsrestorjob and diable in DR
5. backup log TEST to
disk=N'C:\Program Files\Microsoft SQL Server\MSSQL10.DC\MSSQL\Backup\TEST\TESTTAIL.BAK'
WITH NORECOVERY
6. copy the taillog backup from DC to DR manually
7. restore the database rightclick on databases -->restore database-->
--> IN RECOVERY STATE-->SELECT THE OPTION RESTORE WITH RECOVERY./////
8. THEN DR DATABASE WILL BE UP
9. DC DATABASE WILL BE IN RESTORING MODE
10 fix the orphan users
11.-- RECONFIGURE LOGSHIPPING BEFORE THAT REMOVE PREVIOUS LOGSHIPPING FROM DR
-- AND CONFIGURE AGAIN LOGSHIPPING MAKING DR AS PRIMARY AND DC AS SECONDARY
-- RUN THE LSBACKUP JOB IN DR[MEANS PRIMARY[DC] NOW]
-- RUN THE LSCOPY AND LSRESTORE JOBS IN DC[MEANS SECONDARY[DR] NOW ]ENABLE LS ALERT IN BOTH DC - DR
12 -- REFRESH THE DRINSTANCE AND DC INSTANCE NOW CAN SEE THE DATABASE IN DC[CURRENT DR] IN (Standby/Read-Only)mode
13.-- check the logshipping sync working properly or not
14. --------- SWITCH OVER COMPLETED--------------------
--------------------------------------------------------------------------------

"same for switch back also follow the same steps above mentioned then your DR vl be acts as DC and DC will acts as DR "

""not yet prepared for mirroring and replications" not having clear idea on that
.

Thanks
Naga.Rohitkumar
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 1623
Your plan is mostly good. delete the part about copying logins (that part is bad .... real bad).

If you want some more details on this failover process, I wrote an article on it for SQL Server Pro magazine: 3 Log shipping Techniques and have a demo of it on my website.

For the login stuff, bcp'ing or copying syslogins will not work in SQL 2005+. Just so happens, i have a script for this. In fact this script is even better because it syncs passwords and SIDs for SQL logins and also handles role assignments and explicitly granted server level permissions. You can find that script here: Copy Logins script



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Kishore-132325
Kishore-132325
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 112
You also have an opportunity to upgrade from log shipping to mirroring where the failover and tailback is pretty simpler as compared to Logshipping. Food for thought.
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
It should not prefered from my client side all my production severs running in logshipping -clustering environment

even i know sme knowledge about mirroring and replication i din't worked on it and that to iam a satater in dba L1 hadling 3 to 4 years work

Thanks
Naga.Rohitkumar
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
Hi Robert

Ineed one confirmation for the alternative step about removing bcp steps suggested by you for [delete the part about copying logins]


where should i execute this script for mapping the logins after
SWITCH OVER COMPLETE whether in primary server or in secondary server
-----------------------------------------------------
EXEC sp_change_users_login'report'

and

Exec sp_change_users_login'auto_fix',users
------------------------------------------------------

Thanks
Naga.Rohitkumar
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 1623
Delete means "remove" or "get rid of". Don't remap logins. That's bad. Create the logins correctly and there will be no need to remap anything. Use the script I gave you the link to.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
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: 19742 Visits: 17239
I agree with Robert, avoid re mapping unless necessary. Create the login with the correct SID, this script is also useful for generating CREATE LOGIN ... statements from a source server. The roles should be pretty easy for you to figure out ;-)

SELECT      'CREATE LOGIN ' + name + ' WITH PASSWORD = ' + 
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'off'
ELSE 'on'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'off'
ELSE 'on'
END
FROM master.sys.sql_logins



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
HI robert & perry
ya ivl follow the link and script also if needed. Before that i want to know one thing where should i execute this script for mapping the logins after
SWITCH OVER COMPLETE whether in primary server or in secondary server
-----------------------------------------------------
EXEC sp_change_users_login'report'

and

Exec sp_change_users_login'auto_fix',users
------------------------------------------------------

Actually when we perform dc dr dill[switch back & switch over ] it wont be much time for DBA to carry this activity and if i follow this script there is difficult to remmeber long script instead i want to go through the simple script which i provided above just need calification when and where to execute
special thanks to u both

Thanks
Naga.Rohitkumar
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: 19742 Visits: 17239
Believe me, there's nothing difficult about the script i gave you above

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 1623
I'm sorry, but which part of "remapping users is BAD" is confusing for you? Why would I help you do it the wrong way when we've already told you how to do it the right way?



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
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