SQL Server Performance Forum – Threads Archive

Role Change and FailOver

Hi All,
I am little confused in the way how log shipping has been changed in SQL Server 2005. I read the whole of articles in BOL to find that microsoft has not given any procedures to handle the failover situation and the role change job. From my understanding i shall ask some quick questions. 1. For the failover MS says that restore any unrestored log files from the primary database to the secondary database and bring the secondary database up. My Question here is. What if my last log file is restored or the server where the primary database resides is unavailable due to some hardware or OS failure? —maybe i can restore with recovery to bring secondary online 2. I have brought my secondary up restoring the last log file from the primary server. Now MS says that to again change the primary server to what it was it says to backup the database in secondary and restore it in primary and reconfigure the logshipping. Is this true are there no other alternatives to this? 3. What happened to the old procedures that were available with SQL Server 2000 that we used to do the role change? I find they are not available in 2005. following links i have gone through, http://msdn2.microsoft.com/en-us/library/ms178117.aspx —Role change in sql 2005 http://msdn2.microsoft.com/en-us/library/ms191233.aspx —Fail over in log shipping http://msdn2.microsoft.com/en-us/library/ms175475.aspx —Troubleshooting Orphaned user looking for answers to enrich myself thanks for all. Regards
Deepak
SQL DBA

1. If you have restored the transaction logs on the secondary server then you will be able to recover until that time. Say you have restored 11:30am log to secondary server, but primary crashed at 11:40 then you may lose the transactions that are committed on the primary server, still you can try brining up the primary server to see whether it can auto recover the transactions. 2. Yes 3. The articlehttp://msdn2.microsoft.com/en-us/library/ms178117.aspx you have mentioned deals with role changes. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Sathya, Really thanks for your prompt response……….few more doubts are there ! ! ! http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx</a> —SQL 2000 role change steps… if u observe the above links,you can see the following procedures namely, 1.sp_change_primary_role 2.sp_change_secondary_role 3.sp_change_monitor_role but unfortunately no such procedures exist in SQL 2005 refer the available procs, http://msdn2.microsoft.com/en-us/library/ms175106.aspx</a> —SQL 2005 refer sp_change_log_shipping_primary_database —but it changes only the primary dbs settings ………. similarly sp_change_log_shipping_secondary_database and sp_change_log_shipping_secondary_primary are available but u can change roles…..sounds bizzare [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!] also no procedures available for changing the monitors role…….[:0] I personally feel that MS needs to enhance log shipping features in SQL 2005,coz u hav the role change features i.e SPs available in SQL 2000 but nothing in SQL 2005 usually in a latest version of a product they will enhance its features but in this case they seem to have demoted it is’nt it ??????? Pls correct me if am wrong as i am only a newbie……[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Deepak SQL DBA

quote:After you have completed the steps above for the initial role change, you can change roles between the primary database and the secondary database by following the steps in this section. To perform a role change, follow these general steps: Bring the secondary database online, backing up the transaction log on the primary server with NORECOVERY. Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server. Enable the log shipping backup job on the secondary server (the new primary server), and the copy and restore jobs on the primary server (the new secondary server).

THe specified SPs still exists on SQL 2005 too, check books online (updated) for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Sathya,
I referred MSDN ,so I strongly believe it is the latest one.Pls refer http://msdn2.microsoft.com/en-us/library/ms175106.aspx Regards
Deepak
SQL DBA

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I should have corrected in stating it, those have been renamed as different as sp_change_log_shipping_primary_database in 2005, the workout is same. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Hi Sathya,
EXEC msdb.dbo.sp_change_primary_role
@db_name = ‘Pubscopy’,
@backup_log = 1,
@terminate = 1,
@final_state = 3,
@access_level = 1 is available in SQL 2000 for performing role change……. EXEC master.dbo.sp_change_log_shipping_primary_database
@database = N’AdventureWorks’
, @backup_directory = N’c:LogShipping’
, @backup_share = N’\tribecaLogShipping’
, @backup_retention_period = 1440
, @backup_threshold = 60
, @threshold_alert = 0
, @threshold_alert_enabled = 1
, @history_retention_period = 1440
,@monitor_server_security_mode = 1 is available in SQL 2005 and it is only used to alter the primary db settings and not for role change…….refer
http://msdn2.microsoft.com/en-us/library/ms187351.aspx
Regards
Deepak
SQL DBA

Im bit confused here, let me check back and will confirm here. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Sathya thanks for responding my queries patiently…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] but i seriously feel MS has gone back and not provided any procedures……guys lets discuss this seriously ……..cool Regards Deepak SQL DBA

Sathya,
any update ? ? ? …….i am waiting for the solution…… Regards
Deepak
SQL DBA

]]>