July 20, 2012 at 3:24 am
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
July 21, 2012 at 11:04 pm
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
August 11, 2012 at 4:04 am
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.
August 14, 2012 at 5:53 am
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
December 11, 2012 at 12:19 am
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
December 11, 2012 at 9:11 am
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.
December 12, 2012 at 2:06 am
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" 😉
December 12, 2012 at 2:37 am
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
December 12, 2012 at 5:42 am
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" 😉
December 12, 2012 at 7:07 am
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?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply