I am having some trouble using MS SQL Server 2000 log shipping. I can get the
database to fail over and become active on the secondary server, but I want
to also have the primary become immediately available for reverse log
shipping.
After I have run the stored procedures to demote the primary, I have to
detach and attach the database on the secondary to be able to run the stored
procedure to promote the secondary. If I don't the procedure fails (sorry, I
don't have the exact error, but it refers to the databse being unable to
access). I also have to manually run the last copy and restore job to make
sure the final transaction log backup gets applied. Once this is done, the
stored procedure to promote the secondary is successful. I then run the
stored procedure to change the monitor server to reflect the new server roles.
Once the database is failed over, I have to go into the maintenance plan for
the reverse log shipping and assign the new destination server. When doing
this, I receive a message stating that the database on the new destination
server is not in standby mode. If I set the database to Read Only in the
Properties dialog box, I can then configure the new destination server. When
I do this, the log backups work fine and so does the copy job. But the
restore job fails with the following error
Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
I can't find any helpful documentation of this message. Listed below are the
stored procedures I am running.
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'modelcopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 2,
@.access_level = 1
USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'Modelcopy',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null
USE master
GO
EXEC msdb.dbo.sp_change_monitor_role
@.primary_server = 'sqlserver1' ,
@.secondary_server = 'staging3',
@.database = 'modelcopy',
@.new_source = '\\staging3\logshipping'
Any help is appreciated
TIA,
Ken
Ken,
First of all, do not use the option "Allow database to assume primary role"
when setting up log shipping. It just doesn't work.
Reversing the direction of log shipping is a manual process.
1. Demote the primary database on the Main server using
msdb.dbo.sp_change_primary_role
as you have it below. This will make one last tlog backup to disk.
2. Promote the secondary database on the Standby server using
msdb.dbo.sp_change_secondary_role
as you have it below. This will take that one last tlog backup and apply it
WITH RECOVERY.
3. Remove all log shipping that remains (jobs and rows for that log shipping
pair in the ls tables - if there are any.)
At this point you can install log shipping from scratch, the new direction
now being from the primary database on the Standby server to the secondary
database (in norecovery) on the Main server.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@.microsoft.com...
> I am having some trouble using MS SQL Server 2000 log shipping. I can get
the
> database to fail over and become active on the secondary server, but I
want
> to also have the primary become immediately available for reverse log
> shipping.
> After I have run the stored procedures to demote the primary, I have to
> detach and attach the database on the secondary to be able to run the
stored
> procedure to promote the secondary. If I don't the procedure fails (sorry,
I
> don't have the exact error, but it refers to the databse being unable to
> access). I also have to manually run the last copy and restore job to make
> sure the final transaction log backup gets applied. Once this is done, the
> stored procedure to promote the secondary is successful. I then run the
> stored procedure to change the monitor server to reflect the new server
roles.
> Once the database is failed over, I have to go into the maintenance plan
for
> the reverse log shipping and assign the new destination server. When doing
> this, I receive a message stating that the database on the new destination
> server is not in standby mode. If I set the database to Read Only in the
> Properties dialog box, I can then configure the new destination server.
When
> I do this, the log backups work fine and so does the copy job. But the
> restore job fails with the following error
> Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> I can't find any helpful documentation of this message. Listed below are
the
> stored procedures I am running.
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'modelcopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 2,
> @.access_level = 1
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'Modelcopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> USE master
> GO
> EXEC msdb.dbo.sp_change_monitor_role
> @.primary_server = 'sqlserver1' ,
> @.secondary_server = 'staging3',
> @.database = 'modelcopy',
> @.new_source = '\\staging3\logshipping'
> Any help is appreciated
> TIA,
> Ken
|||Ron,
Thanks again for your help. I had just about came to the same conclusion
about the "assume primary role option" but wanted to make sure. In fact, I
was figuring I owuld have to resign myself to the option of simply rebuilding
from the start all over again.
Thanks,
Ken
"Ron Talmage" wrote:
> Ken,
> First of all, do not use the option "Allow database to assume primary role"
> when setting up log shipping. It just doesn't work.
> Reversing the direction of log shipping is a manual process.
> 1. Demote the primary database on the Main server using
> msdb.dbo.sp_change_primary_role
> as you have it below. This will make one last tlog backup to disk.
> 2. Promote the secondary database on the Standby server using
> msdb.dbo.sp_change_secondary_role
> as you have it below. This will take that one last tlog backup and apply it
> WITH RECOVERY.
> 3. Remove all log shipping that remains (jobs and rows for that log shipping
> pair in the ls tables - if there are any.)
> At this point you can install log shipping from scratch, the new direction
> now being from the primary database on the Standby server to the secondary
> database (in norecovery) on the Main server.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@.microsoft.com...
> the
> want
> stored
> I
> roles.
> for
> When
> the
>
>
|||Hello Ken
If I understand correctly, there are 2 issues that you have brought up in
this message -
1. You are unable to configure the primary server to reverse log ship at
the time of failover
2. You are having problems with running the sp_change_secondary_role stored
procedure because you get some error that mentions that the database is in
use.
Let me address these -
1. You should use sp_change_primary_role in the following manner if you
would like to leave the primary database in standby(or norecovery) state
for reverse log shipping :
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'modelcopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3 --3 = Standby, 2 = Norecovery
This performs transaction log backup and leaves the database in a state
where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
running the stored procedure again. If you continue to get errors, please
post the output that you get from this stored procedure in reply to this
message.
2. Please refer to the following KB articles to see if you are running into
one of the known issues :
294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
http://support.microsoft.com/?id=294397
308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
http://support.microsoft.com/?id=308774
300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
http://support.microsoft.com/?id=300497
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Pankaj,
You're correct in that there are two basic issues causing problems. However,
the sp_change_primary_role always completed successfully. I would run this in
Query Analyzer and would get successful messages.
Also, the original secondary server would, if I followed the steps outlined
in my message, become a primary with relative ease.
The biggest issue I ran into was that when I tried to configure the original
primary, now the secondary, to accept log shipping so that it again may
become the primary during another role change, the restore jobs would fail.
The backup and copy jobs were always successful.
Thanks,
Ken
"Pankaj Agarwal [MSFT]" wrote:
> Hello Ken
> If I understand correctly, there are 2 issues that you have brought up in
> this message -
> 1. You are unable to configure the primary server to reverse log ship at
> the time of failover
> 2. You are having problems with running the sp_change_secondary_role stored
> procedure because you get some error that mentions that the database is in
> use.
> Let me address these -
> 1. You should use sp_change_primary_role in the following manner if you
> would like to leave the primary database in standby(or norecovery) state
> for reverse log shipping :
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'modelcopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3 --3 = Standby, 2 = Norecovery
> This performs transaction log backup and leaves the database in a state
> where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
> PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
> SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
> running the stored procedure again. If you continue to get errors, please
> post the output that you get from this stored procedure in reply to this
> message.
> 2. Please refer to the following KB articles to see if you are running into
> one of the known issues :
> 294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
> http://support.microsoft.com/?id=294397
> 308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
> http://support.microsoft.com/?id=308774
> 300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
> http://support.microsoft.com/?id=300497
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
