Go Premium for a chance to win a PS4. Enter to Win


Log Shipping vs Replication

Posted on 2003-11-25
Medium Priority
Last Modified: 2012-06-27
We have a production database on SQL 2000 Enterprise running a 3rd party application.  

Users imput data using the application and reports are run against it using custom written reports in VB, Access, and Crystal Reports.  

We would like to add a second server as a reporting server and an online backup in case of a server crash on the primary.  I have never used Replication in a production environment (have configured it in a test environment) and have never used Log Shipping.  

Which would be the best reporting, which would be the best for online backup and what are the pros and cons of each?

This is my first question so feel free to let me know if I should give more info.

Question by:dianwells
  • 6
  • 3
LVL 34

Expert Comment

ID: 9822864
First, I'm only guessing, but since it's a third party application, they probably don't want you changing the structures of their tables.  Chances are, it might even invalidate their support to you.

So, in order to implement replication, SQL Server makes all kinds of structures to the tables.  This also creates some overhead.

Log shipping is a good alternative that doesn't change the structure of the tables.  About the only probably you might encounter is the latency.  You can set the "backup" to process every minute or so, it really depends on the connection speed between the two servers.  Now, if you actually want the second server to be online as well, log shipping won't really work.  SQL Server puts the second server in warm-standby and it's not usable until you apply the last log file.

have you thought about DTS and just shipping changed data across to the second server periodically?

If you really need a second server, have you thought about a Cluster configuration?
LVL 34

Expert Comment

ID: 9822868
"probably you might" should be "problem you might"
LVL 34

Expert Comment

ID: 9822876
Here is a good "webchat" transcript with some MS engineers and the compare/contrast of logshipping and replication:

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.


Expert Comment

ID: 9826795
"So, in order to implement replication, SQL Server makes all kinds of structures to the tables.  This also creates some overhead."

Transactional replication will not change database structures.
LVL 34

Expert Comment

ID: 9827009
This is true and also possibly a hindrance....Usually to implement replication you plan it out--you don't just add it to an existing application.  If there are any identity fields created on a table without the NOT FOR REPLICATION option, you could run into problems with overlapping ID #s.

However, since you only want the other server to be a standby, you probably wouldn't (I would  hope) choose a transactional replication strategy anyway.....

Author Comment

ID: 9827152
I thought that with log shipping we could run the server in read only mode for reporting.  Is this not true?

I think I see your point about Transactional Replication.  Are you saying it would be good for reporting but if the database is not explicitly designed for this it is likely to not work for a failover and if so is there a type of Replication that would?

I'll check out the web chat.

Also the servers are connected with a 1000 MB connection on a second NIC so network bandwidth shouldn't be an issue.  The database is currently only 26GB.

I'm open to any ideas but the goal is one secondary server to provide reporting and also be a manual failover by either changing DNS or doing an auto reconfig of clients.

From what I'm reading here it sounds like we may need a reporting server using replication and a seperate server as a standby using Log Shipping.

Is there no way to do both on one box?
LVL 34

Expert Comment

ID: 9827232
Yes, I was kind of incorrect on that.  Usually, if you're using your second server purely as a backup, you will leave the server in "warm standby" and it will always be waiting for a log file backup to ship from the main server.  You can change this to automatically perform recovery though and apply the last logfile.

Transactional replication is usually used in the situation where you have many machines that need to keep up to date with each other.  Since you only have one machine that needs a copy of data from another machine (one way), you could just set up a publisher, create a subscription and subscribe to it.  HOWEVER, non-transaction replication will add all the extra crap to your tables.

If you really want reliable failover and you have a bit of money--look at clustering and creating a active/active setup.

Do you guys have a SAN/NAS, or are the database located on local drives?

Author Comment

ID: 9827737
Thanks for the feedback.  I think I understand the differences and where they would be used now.

The data is on local attached storage and we do not have a SAN or NAS.
The two servers are directly connected with second NICs with 1000MB Adapters.

We don't have the money for clustering and frankly I've heard nothing but bad things about it's reliablilty and the amount of support it takes to get and keep it running.  Maybe you can give me some good news on clustering.

I'm leaning toward Log Shipping for failover and Replication for reporting.  
For an interesting twist could I do this on the same box with two instances of SQL and would it be suggested or do you think that would overtax the hardware or have any other problems?  

The databases would be on seperate physical arrays on seperate controllers.  We can put it on two boxes but it would be cool to run it on one if the hardware can be configured to handle it.

It's a quad 900mhz processor box w/ 5GB of RAM.
LVL 34

Accepted Solution

arbert earned 1400 total points
ID: 9828004
You can also implement replication with logshipping.  Personally, I would try and keep it as simple as possible.

We've actually had really good luck with clustering--no problem to install.  You install the windows clustering first, and then when you install SQL Server, it automatically recognizes the cluster and installs accordingly.

Does the "reporting" database have to be totally in-synch with the production database?

Author Comment

ID: 9828192
Reporting database can be out of sync by a set amount of time, say 15 minutes.
If I implemented replication with logshipping wouldn't I still run into the problems with the database not being designed for replication?

"This is true and also possibly a hindrance....Usually to implement replication you plan it out--you don't just add it to an existing application.  If there are any identity fields created on a table without the NOT FOR REPLICATION option, you could run into problems with overlapping ID #s."

Let me know if this is what you are talking about.

In this model does the distributer need to be running SQL or is it just a file store?

I think this might be just what we are looking for.
Let me know if this is what you would do if you couldn't afford clustering.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question