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


Log Shipping


Log Shipping

Author
Message
elchuru
elchuru
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 789
Hi,
I am new SQL DBA.Can anybody tell me how to bring secondary server online for production, if primary database goes crash/damage, which is participating in log shipping.
How do we know log shippping failure occured?
Thanks in advance
Vijay
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23530 Visits: 13698
ensure the latest tranlog backup you managed to copy over to the secondary server is restored to the secondary database. Easiest way is to see what the latest file is in the directory you are copying to and check in errorlog to see if that has been restored

Bring the database online with command

restore database yourdatabase with recovery

You may need to sysnch orphaned userids (only applicable if using SQL authentication)
in your database run
sp_change_users_login 'report'

if any rows returned run
sp_change_users_login 'update_one','loginname','userid' --check that order don't have BOL here
for each orphaned id.

The need to do this can be avoided by using sp_help_revlogin to copy logins over.

The application will also need to be repointed, easiest way to do this is a DNS alias.

If log shipping is failing usually one of the SQLAgent jobs involved will be failing (they all start LS) or warnings will be appearing in the errorlog of the secondary that logshipping is out of synch

---------------------------------------------------------------------
maechismo_8514
maechismo_8514
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4030 Visits: 2228
1.) If your Primary Database is accessible, backup the last portion of the log WITH NO_RECOVERY which makes to stall any updates to primary database.
2.) Copy over the uncopied tlogs from the primary to your secondary
3.) Apply them sequentially to the secondary datbase using WITH NO_RECOVERY leaving the last one to apply WITH RECOVERY
4.)Fix the orphaned users
5.) Redirect your clients
elchuru
elchuru
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 789
Then, what about logins and Jobs running that database.Can anybody tell fully about this.
Thanks
Vijay
SQL_DBA_3
SQL_DBA_3
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 658
You can script out the logins and database jobs and run them in master and msdb databases respectively on the secondary server(which is now online)

For user mapping use sp_change_users_login which maps the SIDs of the logins and users.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23530 Visits: 13698
for how to handle logins google 'sp_help_revlogin'

---------------------------------------------------------------------
SUBRAHMANYA HEDGE
SUBRAHMANYA HEDGE
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 406
You can also use SSIS for transferring Jobs and Logins from your primary server to secondary server.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23530 Visits: 13698
SUBRAHMANYA HEDGE (3/19/2009)
You can also use SSIS for transferring Jobs and Logins from your primary server to secondary server.


be warned - if you use the SSIS transfer logins task it encrypts and CHANGES the password for SQL authenticated logins. which is done for security reasons but makes it pretty useless for transferring logins.

---------------------------------------------------------------------
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