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


Log Shipping


Log Shipping

Author
Message
elchuru
elchuru
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 792
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 Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58354 Visits: 13706
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
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9778 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
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 792
Then, what about logins and Jobs running that database.Can anybody tell fully about this.
Thanks
Vijay
SQL_DBA_3
SQL_DBA_3
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3274 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 Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58354 Visits: 13706
for how to handle logins google 'sp_help_revlogin'

---------------------------------------------------------------------
SUBRAHMANYA HEDGE
SUBRAHMANYA HEDGE
SSC Eights!
SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)

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

Group: General Forum Members
Points: 58354 Visits: 13706
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