Log Shipping vs Replication

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
"probably you might" should be "problem you might"
Here is a good "webchat" transcript with some MS engineers and the compare/contrast of logshipping and replication:

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

"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.
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.....
dianwellsAuthor Commented:
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?
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?
dianwellsAuthor Commented:
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.
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dianwellsAuthor Commented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.