June 27, 2007 at 6:06 am
we initially had log shipping, but we had to do DR testing, i stopped the log shipping jobs going from our production to our dr servers, then took the databases out of read-only status by promoting the databases to primary server from secondary status.
DR finished successfully, now i have to reset the database back to secondary. when i ran the script
use master
go
exec msdb.dbo.sp_change_secondary_role
@db_name = ‘dbname’,
@db_load = 1,
@force_load = 1,
@final_state = 1,
@access_level = 1,
@terminate = 1
this put the databases in single user mode, i cannot get it out of single user mode and make it multi-user? and the database has been taken out of sysdatabses
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mydbname'. No entry found with that name. Make sure that the name is entered correctly.
firstly, how do i get the my database back into Sysdatabse, i also have my backup so should i delete from enterprise manager and create and restore to start again.
Also, I noticed that at DR on the server i want to make secondary, one of the consultants have linked other DR servers would this be an issue for me not being able to put this these database back into secondary mode?
this is rather urgent i would appreciate some advice.
Thanks in advance
June 27, 2007 at 6:19 am
From yu query it looks like you have the current database running the the secondary server. You take a full backup of the secondary database and restore in the primary then remove the database in secondary server and reconfigure logshipping again.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 3:35 pm
thanks for that Sugesh, I have now got my databases out of single user mode by doing what you suggested.
All okay.
Now, i have noticed as we had consultants out on DR they have have created jobs in sql agent. After i ran the script to put back into secondary status and start log shipping from my production server, the databases went into single user mode, this probably occured as the databases sees that other processes are running.
Do you advise to stop the jobs?
then try use this script, secondly i have noticed that they have linked other DR servers, I don't think that will affect log shipping on the selected databases.
Best regards,
June 28, 2007 at 12:10 am
Amanda,
Since you have now restored the primary database you will need to reconfigure the logshipping again. Yo can delete the old jobs running for it. Also if you have any other logshipping configured that will not be affected.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 12:58 am
this is what i have done so far.
recovered the database
disabled all sql agent jobs, cannot delete as we will need them again on another DR test, and i am told they must stay there. so all jobs now disabled.
ran the following script
Use master
Go
Exec msdb.dbo.sp.change_primary_role
@db_name = mydbname
@backup_log = 1,
@terminate = 1,
@final_state = 3,
@access_level =1
ended up in single user status again.
checked in current activity processes info, saw that we had four instances of sa with no database context. have tried to do the kill process but cannot. have attempted through query analyser and through enterprise manager.
3 of the processes are system
1 is sa all in background status.
stopped and started SQL Agent, still no difference.
do you advise rebooting the server?
no other log shipping is running on this server, as this is secondary there is no log shipping monitor.
also when you say reconfigure, do you mean make the databases primary again using
do you recommend the following steps
use master
go
exec msdb.dbo.sp_change_monitor_role
@primary_server = 'oahu\sql2k_1
@secondary_server = 'oahu\sql2k_2
@database = 'mydatabasename'
@new_source = 'oahu\sql2k_2'
then run the secondary server script
use master
go
exec msdb.dbo.sp_change_primary_role
@db_name = 'mydatabasename',
@backup_log = 1,
@terminate = 1,
@final_state = 3,
@access_level = 1
or use the script promoting to primary server
Use master
Go
Exec msdb.dbo.sp_change_secondary_role
@db_name = ‘SCIA_Ctrl,
@do_load = 1,
@force_load = 1,
@final_state =1,
@access_level = 1,
@terminate = 1,
@keep_replication = 0,
@stopat = null
please clarify, i am not the DBA here, he left and I am stuck with it, and he did not leave any documentation so i have got myself in a batism of fire, so i am combining the theory i have and understanding the concept and then giving it a go.
June 28, 2007 at 1:57 am
Don't try to kill any SPIDs that have a value less than 50 as they are system related processes. Since you say that you are stuck i suggest the following
1. Bring the database to the current state in the primary server.
2. Remove log shipping between the source and the destination server
3. Reconfigure the logshipping again between both the servers.
4, Monitor the logshipping to ensure that they are configured correctly.
Then while performing DR activites make sure that you have all the documents necessary also before the DR is done prepare a step by step process that you should follow that saves mor time for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 3:05 am
Hi Kumar,
sorry it has been a while getting back to you, have been requesting for local resource to help me out, no luck as such. Just to clarify what i did for dr testing.
our primary database (production) has always remained in Primary state, as the rest of the company was working away, and only a handful of people were doing DR testing.
What I did for DR test on the Production Server was stop the log shipping disabled the sql agent jobs ie transaction log backup for log shipping.
Disabled the log shipping monitor from Backup Alerts and out of synch alerts.
No more information went across after i did this.
on the destination server only i promoted to primary to get the database out of standby mode.
situation is as follows:
1. could not demote the destination server (one used for DR testing) back to secondary read/write.
2. have taken a fresh backup from production servers.
3. restored backups in standby mode for all Destination Databases (DR Servers).
4. On production servers i want to setup log shipping again. In enterprise manager Database Maintenance Plans have already been setup for each log shipping databse including log shipping monitor also on production.
I cannot delete the log shipping database maintenance plans on all of the databases because it instructs me to remove log shipping from db. maintenance plan. So on the log shipping tab it then instructs me to register the server. But the server (Production Server has already been registered)?
Do I need to register the Destination server here?
5. Before doing step 4, do i have to remove the log shipping pair from Log Shipping Monitor, then try editing or removing database maintenance for log shipping.
the script i would be using to remove the monitor.
exec sp_delete_log_shipping_monitor_info
@primary_server_name = 'primeserver'
@primary_database_name = 'mydatabse'
@secondary_server_name = 'destinationserver'
@secondary_database_name = 'mydatabase'
regards,
amanda:
July 3, 2007 at 3:36 am
also investigated msb database
re tables = log_shipping_databases
shows the maitenance_id plans for all databases
log_shipping_secondaries
shows secondary server name RXX eg and all databases for log shipping
log shipping_primaries
shows primary server name PXX eg and all databases for log shipping
note for primary server the database maintenance plan id match the maintenance_id plans for the table log_shippping databases.
this investigation was done on the primary server.
look forward to your reply regards
July 3, 2007 at 4:04 am
Amanda what you have understood is correct. You have to remove log shipping to reconfigure it again. Removing logshipping will delete all the entries in the log shipping related tables in msdb database. Once you have removed the log shipping pair take a full backup from the primary server(before that delete the log shipping databases from secondary and monitor if they exists) and then restore it in secondary server in norecovery/standby mode. After that reconfigure the log shipping.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 6:13 am
Sugesh, so the only way to roll back to regular log-shipping with the initial primary being primary again is to set-up log-shipping again? Or did I misunderstand? Thanks.
Chris
July 3, 2007 at 6:31 am
That is the most safest way i mean. You can perform role changes with the help of stored procedures and revert back to primary again but even a slightest mistake will cause huge risk or corrupting the database so it would prefer to be careful.
But with 2005, i see that what i have said is the only solution available as there is not procedures to do the role changing.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 6:52 am
Hmm...thanks for the info.
I'm writing up my company's DR strategy now, so this is definitely helpful.
Anyone have any templates for a DR doc? I found some on http://www.sqlperformance.com, but I was wondering if anyone had any more.
Thanks...Chris
July 3, 2007 at 7:06 am
Chris,
Define all the steps, process and lead times and everything in the DR document. I wrote a DR document that had everything and was very much useful for the DBA who took over from me. Listing everything in the DR doc makes life simpler for DBAs in time of crisis.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply