Log Shipping with clustered database.

Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.
Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:

> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>
|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:

Log Shipping with clustered database.

Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The only
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:
> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:
> Yep...you can. Just like how you do it in a non-clustered instance. The only
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
> > Hi,
> >
> > We are running SQL Server Failover cluster in Active/Passive mode.
> >
> > Now, we want to setup DR server for this clustered database using
> > Log shipping.
> >
> > But my concern is, can we do log shipping with clustered database ?
> > and the answer of this question is Yes then Please let me know How ?
> >
> >
> > Thanks & Regards,
> > Sajid C.
> >
> >

Log Shipping with clustered database.

Hi,
We are running SQL Server Failover cluster in Active/Passive mode.
Now, we want to setup DR server for this clustered database using
Log shipping.
But my concern is, can we do log shipping with clustered database ?
and the answer of this question is Yes then Please let me know How ?
Thanks & Regards,
Sajid C.Yep...you can. Just like how you do it in a non-clustered instance. The onl
y
problem you will have in here is if you want to be notified thru email if
your restore jobs fail as MAPI is not supported in a clustered environment.
--
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
"Helping people grow and develop their full potential as God has plan for
them"
"csajid@.gmail.com" wrote:

> Hi,
> We are running SQL Server Failover cluster in Active/Passive mode.
> Now, we want to setup DR server for this clustered database using
> Log shipping.
> But my concern is, can we do log shipping with clustered database ?
> and the answer of this question is Yes then Please let me know How ?
>
> Thanks & Regards,
> Sajid C.
>|||Hi,
Thanks for your reply.
Can you please provide me some document or any link for the same.
What are the necessary steps which should i take to do this task.
Thanks & Regards,
Sajid C.
bass_player wrote:[vbcol=seagreen]
> Yep...you can. Just like how you do it in a non-clustered instance. The o
nly
> problem you will have in here is if you want to be notified thru email if
> your restore jobs fail as MAPI is not supported in a clustered environment
.
> --
> MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA
> "Helping people grow and develop their full potential as God has plan for
> them"
>
> "csajid@.gmail.com" wrote:
>

log shipping between sql 2005 clusters

hi

I am working for a small govt dept.We are planning to implement sql 2005.

We will be having sql 2005 instance in active/passive cluster mode

We will also have a standby sql server 2005 in active/passive cluster mode.

i need to know how to configure log shipping between my primary and secondary?

Is logshipping the best option?

Thanks

Is logshipping the best option for what? Logshipping is a fine technology but there are other ways to get data to a secondary server. You can use database mirroring in SQL Server 2005. For failover purposes this is a great option. You can also use database snapshots on the same server for various reasons to. Logshipping is perfectly viable too. What are you trying to accomplish? Having redundancy outside of the cluster? Read Only reporting copy of data?

Having that info can help us help you make your choice. I would say look up in Books Online Logshipping, Database Snapshots and Database Mirroring.

|||

Thank you very much for the reply.

I need to have an exact replica of the live enviornment (production cluster) to be switched to (standby cluster) in case of any probs to the primary. We also do switching between primary and standby every week. Basically the standby is like having a disaster recovery site. Its having Redundancy outside the cluster.

Please suggest....

Thanks in advance

|||

Options are

Database Mirroring

Can be operated in Synchronous mode which means your standby will be an exact replica of your primary database. Switching between the two is easy.

However it doesn't support replication and in synchronous mode the data has to be written to the standby and so may impact performance of your app if your network connection isn't great.

You can work in Asynchronous mode in which case the data is written to the standby at some point after the application has finished the transaction, i.e. no impact on the application. However in this mode you may loose data if your primary goes down and the transaction hasn't made it to the standby.

Database Mirroing has automatic failover in the event of a failure (when used with a witness server)

Log Shipping

Very much like asynchronous mirroring however it supports multiple targets i.e. you could have 2 standby servers, you can't do that with mirroring. Also the mechanism for applying the logs is via a copy and restore model which is slightly different to mirroring.

Replication

Much more complicated than the two above but allows for your production to be in read/write mode (although changes are not replicated back to the primary). This is a great option for maintaining a reporting server as you can have different indexes on your reporting database.

Personally if your two clusters are connected by a highspeed network (Gb/s) I would go for synchronous mirroring.

More details can be found here

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/udb9/html/b2eda634-0f8e-4703-801b-7ba895544ff5.htm in BOL under the heading "Configuring for high availability."

Log Shipping between 2 Clusters

Hi
I'm trying to setup log shipping between two 2-node Active/Passive clusters and I keep getting an error "Unable to copy the initialization file to the secondary server".
I've search KB, to find an article KB/811890, but both clusters are in the same domain, and thus this article shouldn't apply. However, I'll try anything, so I have re-registered the second (log ship recipient) cluster as per the recommendations (SERVERN
AME.DOMAIN), but still get the same message.
I know its probably me that doing something wrong, but I'm stuck at what.
I have tried registering the instances in the following ways.
- Using the Cluster name (as opened within Cluster Administrator)
- Using the Virtual Server Name (as provided within the SQL Install)
- Using the Cluster IP Address
- Using the Virtual SQL Server IP Address
In all cases, I either get the same error, or don't get that far.
Can you please assist?
Thanks, in advance for your assistance.
Bev
Try the following to see if it helps
1)Run the stored procs (http://support.microsoft.com/default...&Product=sql2k) to clean up log shipping.
2)Verify that the SQL Server and SQL Server Agent services on primary and secondary SQL Servers are running under a domain account
3)Verify with your NT Administrator that there is a two way trust between the domains.
4)Log on to primary server using the domain account used to start SQL Server services on primary server.
a)Open Query Analyzer and use xp_cmdshell command to copy a file (any file will do) from the share on primary server (i.e. the share on primary server where you
want the transaction log backups to be stored) to the share on the secondary server. If you get error like access denied then we will need to fix this first.
b)Open Enterprise Manager and verify that you can register the primary and secondary SQL Servers.
c)Verify that the database you want to use for log shipping does not have SIMPLE Recovery model.
d)Verify that that the domain account that is used to start SQL Server services has access permission to the database that you want to use for log shipping.
e)Configure Log Shipping.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Log shipping and error 22029

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.
>

Log Shipping "best-practices" question

hello,
I have a SQL2000 server with circa 20 databases. I am setting up Log
Shipping for these DBs. These DBs are not very active so I want to replicate
every 2 hours. I can schedule all three steps separately:
- backing up the log on the source server
- copying the log-backup from the source server to the destination server
- restoring the backup at the destination server
My question is: should these steps happen the same time for all the DBs or
should I distribute them over a larger time period. For instance I can do all
20 DBs at 2pm, (then 4pm, then 6pm etc), or I can do the first at 2:00pm, the
second at 2:05pm, the third on at 2:10pm etc, (and then 4:00, 4:05, 4:10 etc)
so I don't put too much load on the server.
Is there a "best-practice" related to this? How about copying the logs and
restoring them?
thanks
christos
If these databases are large you should stagger the dumps and copies to
prevent network saturation. If they are small this shouldn't be a problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Christos Kritikos" <ChristosKritikos@.discussions.microsoft.com> wrote in
message news:FFBC1E97-0E92-43BF-943A-6971664FFE94@.microsoft.com...
> hello,
> I have a SQL2000 server with circa 20 databases. I am setting up Log
> Shipping for these DBs. These DBs are not very active so I want to
replicate
> every 2 hours. I can schedule all three steps separately:
> - backing up the log on the source server
> - copying the log-backup from the source server to the destination server
> - restoring the backup at the destination server
> My question is: should these steps happen the same time for all the DBs or
> should I distribute them over a larger time period. For instance I can do
all
> 20 DBs at 2pm, (then 4pm, then 6pm etc), or I can do the first at 2:00pm,
the
> second at 2:05pm, the third on at 2:10pm etc, (and then 4:00, 4:05, 4:10
etc)
> so I don't put too much load on the server.
> Is there a "best-practice" related to this? How about copying the logs and
> restoring them?
> thanks
> christos
>
>