Log Shipping vs Replication

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:

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

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

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?

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.

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.