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

Dear All this post is about logshipping DC- DR Drill [switch over and switch back (or)failover and failback ] Expand / Collapse
Author
Message
Posted Friday, July 20, 2012 3:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 578, Visits: 1,280
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
Post #1332796
Posted Saturday, July 21, 2012 11:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1333438
Posted Saturday, August 11, 2012 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 28, 2013 7:46 AM
Points: 290, 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.
Post #1343772
Posted Tuesday, August 14, 2012 5:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 578, Visits: 1,280
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
Post #1344627
Posted Tuesday, December 11, 2012 12:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 578, Visits: 1,280
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
Post #1394916
Posted Tuesday, December 11, 2012 9:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1395187
Posted Wednesday, December 12, 2012 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 6,414, Visits: 13,796
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"
Post #1395508
Posted Wednesday, December 12, 2012 2:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 578, Visits: 1,280
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
Post #1395526
Posted Wednesday, December 12, 2012 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 6,414, Visits: 13,796
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"
Post #1395612
Posted Wednesday, December 12, 2012 7:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1395653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse