November 20, 2009 at 1:31 pm
chk2009 (11/20/2009)
george sibbald (11/20/2009)
you will be fine if you if you:run sp_help_revlogin against primary server and save output to file
copy file to secondary and load into SSMS query window
edit to remove those logins you do NOT want to replace on secondary (e.g. builtin\admins, service accounts)
drop the users logins from secondary
run the sp_help_revlogin output
this will tie the logins on the secondary to the userids in the database and therefore logon should be successful
Still SID is different for 2 logins....how to map SID?
within the user database run exec sp_change_users_login 'report'
any rows returned map those users to login with
exec sp_change_users_login 'update_one','username in database','login to map it to'
this is an update though so you can only do it if you bring the database online which you dont want to do until you failover. You shouldn't need to test connectivity until you do a full failover test, app is not likely to work anyway with database read_only,.
---------------------------------------------------------------------
November 20, 2009 at 3:05 pm
george sibbald (11/20/2009)
chk2009 (11/20/2009)
george sibbald (11/20/2009)
you will be fine if you if you:run sp_help_revlogin against primary server and save output to file
copy file to secondary and load into SSMS query window
edit to remove those logins you do NOT want to replace on secondary (e.g. builtin\admins, service accounts)
drop the users logins from secondary
run the sp_help_revlogin output
this will tie the logins on the secondary to the userids in the database and therefore logon should be successful
Still SID is different for 2 logins....how to map SID?
within the user database run exec sp_change_users_login 'report'
any rows returned map those users to login with
exec sp_change_users_login 'update_one','username in database','login to map it to'
this is an update though so you can only do it if you bring the database online which you dont want to do until you failover. You shouldn't need to test connectivity until you do a full failover test, app is not likely to work anyway with database read_only,.
I did't get you...
Now i disabled logshipping...db's are recovery mode..Now i just want to make sure secondary server should be same as primary..I tested routing application to use secondary ...but throwung error login failed cannot connect to database
I have removed all logins in secondary server and ran sp_help_revlogin output(primay server) in secondary server. then why my appln is not working for secondary server??
Why SID is different for 2 logins?
November 21, 2009 at 11:30 am
application won't be able to connect if databases are in recovery mode.
you still haven't posted the actual error you are getting.
---------------------------------------------------------------------
November 21, 2009 at 12:37 pm
chk2009 (11/20/2009)
Now i disabled logshipping...db's are recovery mode..Now i just want to make sure secondary server should be same as primary..I tested routing application to use secondary ...but throwung error login failed cannot connect to database
I have removed all logins in secondary server and ran sp_help_revlogin output(primay server) in secondary server. then why my appln is not working for secondary server??
Why SID is different for 2 logins?
Your Database is in recovery mode, so bring to normal mode.
Use this script
RESTORE DATABASE database_name WITH RECOVERY
Once this command is run,you should be able to you see the Database objects in your secondary database and then try the application to connect to the secondary database
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 2, 2009 at 9:24 am
Thanks to everyone. Finally I have tested DR test in test servers, it was successfull. I did below things. Before doing in production, I want to check with you people whether I am in right direction.
1.Setup logshipping between servers
2.Restore last log (tail tlog) with recovery in secondary server
3.Route the application users to connect to secondary server.
It worked , but I have a question here,
restoring secondary databases with recovery and route the users—with in this time users will do some transactions in primary server, because primary databases are also in recovery mode(accessible). So I believe primary and secondary are not in same state, how to avoid this??
December 2, 2009 at 9:35 am
It worked , but I have a question here,
restoring secondary databases with recovery and route the users—with in this time users will do some transactions in primary server, because primary databases are also in recovery mode(accessible). So I believe primary and secondary are not in same state, how to avoid this??
In a DR test its vital that the database on the primary be quiesced, so before the tail log on the primary is taken stop the application\kill users, whatever it takes. Once there are no connections take the tail log backup, the primary database will then be inaccessible anyway.
---------------------------------------------------------------------
December 2, 2009 at 9:45 am
sorry mixing up my threads here.
did you backup the log on the primary with the norecovery option? In that case the database will be in a restoring state and inaccessible anyway to further updates.
If you are just doing a normal log backup before failover, offline the primary database once all users are off
---------------------------------------------------------------------
December 2, 2009 at 9:56 am
george sibbald (12/2/2009)
sorry mixing up my threads here.did you backup the log on the primary with the norecovery option? In that case the database will be in a restoring state and inaccessible anyway to further updates.
If you are just doing a normal log backup before failover, offline the primary database once all users are off
Great, this is the exact answer i needed, you are awesome 🙂
A big thank to you
December 23, 2009 at 2:06 pm
kareem007 (12/2/2009)
george sibbald (12/2/2009)
sorry mixing up my threads here.did you backup the log on the primary with the norecovery option? In that case the database will be in a restoring state and inaccessible anyway to further updates.
If you are just doing a normal log backup before failover, offline the primary database once all users are off
Great, this is the exact answer i needed, you are awesome 🙂
A big thank to you
Hi George sibbald,
Next week we have shedule cutover for moving prod server to another datacenter.
Sofar I did below tasks in DR server(next week this should be production server)
1. Transfer logins and permissions.
2 Tested user connections (disabled logshipping and had set db;s in recovery state)
3 set up logshipping between Prod and DR server.
Next week cut over.
1. stop/kill appln users connect to prod server.
2. take tail backup putting prod databases in norecovery state.
3. apply tail backup in DR server with recovery.
4. allow connections
Please let me know if I am wrong.
And one more do i need to restore system databases in DR server?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply