Solved

Log Shipping vs Replication

Posted on 2003-11-25
10
847 Views
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.

0
Comment
Question by:dianwells
  • 6
  • 3
10 Comments
 
LVL 34

Expert Comment

by:arbert
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?
0
 
LVL 34

Expert Comment

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

Expert Comment

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

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itcommunity/chats/trans/SQL/sql0528.asp
0
 
LVL 3

Expert Comment

by:htarlow
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.
0
 
LVL 34

Expert Comment

by:arbert
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.....
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dianwells
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?
0
 
LVL 34

Expert Comment

by:arbert
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?
0
 

Author Comment

by:dianwells
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.
0
 
LVL 34

Accepted Solution

by:
arbert earned 350 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?
0
 

Author Comment

by:dianwells
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.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replbackup_3js7.asp

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.

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now