Log Shipping

Question

Log Shipping

elchuru

Default port

Points: 1435
More actions
March 17, 2009 at 12:12 pm

#202429

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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 SSCoach Points: 17510 More actions · Answer 2

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 Default port Points: 1435 More actions · Answer 3

Then, what about logins and Jobs running that database.Can anybody tell fully about this.

Thanks

Vijay

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 4

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 SSC Guru Points: 104210 More actions · Answer 5

for how to handle logins google 'sp_help_revlogin'

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

SUBRAHMANYA HEDGE SSCommitted Points: 1514 More actions · Answer 6

You can also use SSIS for transferring Jobs and Logins from your primary server to secondary server.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

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.

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