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

Log Shipping Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 12:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, January 26, 2012 7:26 AM
Points: 90, Visits: 787
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
Post #677775
Posted Tuesday, March 17, 2009 2:44 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,849, Visits: 12,585
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


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

Post #677896
Posted Tuesday, March 17, 2009 3:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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


Post #677930
Posted Tuesday, March 17, 2009 9:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, January 26, 2012 7:26 AM
Points: 90, Visits: 787
Then, what about logins and Jobs running that database.Can anybody tell fully about this.
Thanks
Vijay
Post #678065
Posted Tuesday, March 17, 2009 10:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 21, 2012 9:26 AM
Points: 1,861, Visits: 652
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.
Post #678103
Posted Wednesday, March 18, 2009 3:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,849, Visits: 12,585
for how to handle logins google 'sp_help_revlogin'

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

Post #678222
Posted Thursday, March 19, 2009 2:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:44 AM
Points: 206, Visits: 324
You can also use SSIS for transferring Jobs and Logins from your primary server to secondary server.
Post #679220
Posted Thursday, March 19, 2009 4:09 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,849, Visits: 12,585
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.


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

Post #679260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse