Log Shipping for replication

I need to implement a dirt cheap replication method for some dirt
cheap servers. We are using the SQL Server workgroup license. Is
there anything in this that prevents using log shipping for
replication in Workgroup versus Enterprise?

Thanks
Travis"T.Paakki" <trp24@.hotmail.com> wrote in message
news:6358a14b.0405211202.70d39540@.posting.google.c om...
> I need to implement a dirt cheap replication method for some dirt
> cheap servers. We are using the SQL Server workgroup license. Is
> there anything in this that prevents using log shipping for
> replication in Workgroup versus Enterprise?
> Thanks
> Travis

I'm not sure what you mean by a "SQL Server workgroup license" - perhaps you
mean you're using MSSQL in a workgroup, rather than in a domain? In any
case, log shipping requires copying files from one server's filesystem to
another, so you would need to use service accounts which have those
permissions.

Apart from that, there are other options, including rolling your own
solution:

http://sqlguy.home.comcast.net/logship.htm
http://www.sql-server-performance.c...og_shipping.asp

MSSQL replication might also be a possibility, depending on what you're
trying to achieve, but it's not really viable to build any sort of "dirt
cheap" highly available system, if only because of operations costs, ie.
making sure there is always someone there to respond when you need them.

Simon

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping and database backups how do they live together?

Hello,
We are planning to use log shipping as replication method for standby
database. My understanding that I have to make full database backup, restore
it on standby server (with NORECOVERY option) and then can just copy
transaction logs to standby server and apply them there periodically. I'm not
using enterprise edition so I'll use SimpleLogShipper from resource kit.
My question is what kind of backups I can make on primary server in order
for this to work? If I do transaction log backup my understanding transaction
log will be truncated and nothing will be shipped to standby server?
Second question if I may. What happens if I have tons of transactions which
modify small subsets of rows in primary database will shipping and restoring
differential backups will be a better method?
Log shipping works in the full and bulk logged recovery models. You need to
start off with a full backup, and then do transaction log dumps. Inactive
portions of the log are marked for reuse after the log is dumped, it is not
truncated. The file which you dump your transaction log is what you ship and
apply on the standby server.
If you have tones of logged activity (small subsets of rows) your
transaction log dumps will be larger and take longer to dump, copy and
restore on the standby server, but typically not appreciably longer.
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
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:B716A708-983D-4201-BA61-6AECEA33D6A3@.microsoft.com...
> Hello,
> We are planning to use log shipping as replication method for standby
> database. My understanding that I have to make full database backup,
> restore
> it on standby server (with NORECOVERY option) and then can just copy
> transaction logs to standby server and apply them there periodically. I'm
> not
> using enterprise edition so I'll use SimpleLogShipper from resource kit.
> My question is what kind of backups I can make on primary server in order
> for this to work? If I do transaction log backup my understanding
> transaction
> log will be truncated and nothing will be shipped to standby server?
> Second question if I may. What happens if I have tons of transactions
> which
> modify small subsets of rows in primary database will shipping and
> restoring
> differential backups will be a better method?
|||Can I make differential backups along with log dumps without screwing up log
shipping?
"Hilary Cotter" wrote:

> Log shipping works in the full and bulk logged recovery models. You need to
> start off with a full backup, and then do transaction log dumps. Inactive
> portions of the log are marked for reuse after the log is dumped, it is not
> truncated. The file which you dump your transaction log is what you ship and
> apply on the standby server.
> If you have tones of logged activity (small subsets of rows) your
> transaction log dumps will be larger and take longer to dump, copy and
> restore on the standby server, but typically not appreciably longer.
> --
> 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
>
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:B716A708-983D-4201-BA61-6AECEA33D6A3@.microsoft.com...
>
>
|||absolutely.
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
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:1B0EAFC0-0E1E-441A-81CE-E8ED232C0EE6@.microsoft.com...[vbcol=seagreen]
> Can I make differential backups along with log dumps without screwing up
> log
> shipping?
> "Hilary Cotter" wrote: