October 15, 2001 at 3:07 pm
If your goal is to have a stand by server that can also be used for adhoc queries which is the best stradegy?
Our production box sees alot of insert / updates, and a good number of inquiries - anywhere from 60-70 users online. We also have to handle batch processing of new data which can be 1000's of inserts and updates (normally after hours, but occasionally during prime time).
Zach
John Zacharkan
John Zacharkan
October 15, 2001 at 3:45 pm
I'd vote for replication, mostly because I use it a lot. The problem I've seen with log shipping is that the db stays in standby mode. Similar question was in SQL Magazine recently, Richard Waymire from Microsoft recommended log shipping. Easy enough to configure in 2000 if you do go that way.
Andy
October 16, 2001 at 10:20 am
I'd say it depends. If you are moving every table and object and it is a true warm standby server, I'd use log shipping.
If it is a cold standby , and you want to move certain information for queries, I'd use replication.
Steve Jones
October 18, 2001 at 12:20 am
I would recommend log shipping for the reason of having a delay in the time you want the logs restore to the database. The data would be copied and be safe on the standby server but with a delay from 5min to nnnn . Then if the server crashes you run a stored procedure to bring it up to the point of failure and in 5 – 10 min depending on the size of your logs your server is up. Plus failing back to the main server is not as much of a mess as transaction replication is . I believe that replication creates timestamp columns and other objects that would need to be deleted in order to backup and restore the database to the main server. In my case if the main server is setup with transactional replication and it crashes I would need to bring the standby server online which would make the database out of sink with the backup. I would have no choice other than to use the standby server database for the restore. The standby server would need to be backup and restored to the main server when fixed. Then you need to remove all the timestamp columns and any other objects replication creates to set up replication again. With log shipping its just a straight forward backupàcopyàrestore. Also with log shipping you can set the standby to read-only so you can still run queries but you need to adjust the window when you would like to run these because it interferes with the restore of the logs. I have my servers set to copy every 5min with restores taking place every 5hrs, so in that 5hrs I can run ad hoc queries till I’m blue in the face. I presently have log shipping setup on my 30GB insurance database which ships logs over a VPN to a off-site data warehouse. Working Great…
October 18, 2001 at 10:48 am
Greg,
What would you do if you needed near real time querying ability on the standy server - say data to within 10-15 mins of live?
Andy
October 18, 2001 at 11:06 am
Andy,
I have my database log shipping to two servers one on site that is restore up to 5min of the live production and one off-site with a delay incase user error or corruption. Last year we had user error that replicated to the standby server that resulted in total restore of the database, so I like to keep a large delay window to catch any user errors or coruption. I know that not everyone can afford this solution but if you can keep a delay window open you can save yourself some time...
cheers
Greg
October 18, 2001 at 11:36 am
Sure, if you can afford to do it, why not? Assuming you don't have luxury of two spare boxes and you have the requirement to be within 5-10 minutes of live AND be able to query against it, would you still log ship? Its a valid scenario - it's mine! Slightly more complex since I have 220 db's that meet this criteria.
Andy
October 18, 2001 at 12:43 pm
If you need the real time querying, then replication is most likely better. However, in a DR situation, replication can be tricky to set back up. Not only that, what do you do when the original box returns?
Personally, after reading through the thread and examining the issues, it seems that using a DR box for replication and distributing the query load is a poor idea. You would be better served with another server that handles the queries (through replication) while allowing the second server to function as a DR box.
Of course, this brings with it license, admin, and hardware costs. It's not a perfect world.
You have to present the cases and costs to management. If you choose replication and a server fails, you likely can repoint everyone to the new server. Your failback strategy will be tricky because you need to in fact copy the backup server to the primary (probably need a window here) and then bring the main server back up. If you have truly lost the server, then hopefully you have the replication setup scripted to quickly bring this back.
If you use log shipping, then you lose the real time query capability. But do you really need real time access for every table?
Most likely not, though you may have to work with users to see what "real time" really is.
Personally, I prefer log shipping, but I don't have 200+ database, just a couple. Moving 200+ databases somewhere is more comlicated (loads servers, network, etc. Costs time). In this case I might choose replication and minimize hardware and software costs. If I could afford two servers, I'd do log shipping and everyone can deal with the 5 minute delay. The queries against this server, however, would have to be report type queries. NOT queries to determine what data to update.
Just my 2cents.
Steve Jones
October 18, 2001 at 3:17 pm
Not bad. In my case we're more concerned with reporting than DR. I've raised the issue and a cluster aint in the forecast! As far as if the main server goes, we can easily repoint the app to the subscriber. We'd be missing a few things, but nothing that couldnt be scripted back in pretty quickly.
Andy
October 19, 2001 at 9:36 am
First off I would like to thank all of you for your opinions and incite. I especially like the idea of having the logs shipped but not applied to the stand-by server. We've seen disastrous batch loads that need to be rolled out - a topic for another day.
It seems to me, based on what I'm reading here is that my best bet would be to Log ship my database to the stand-by server and to replicate to a separate server, to handle the adhoc reporting that goes on. Obviously there's a cost that needs to be justified.
Is there any issue with replicating and log shipping the one database to two separate databases on the same server? Then there would be no additional licensing cost.
Could I also not set up a scenario that if my production server when down then my stand by server takes over and have some script activate that stand-by server's database to also start replicating to the replicated database on the same server? (I know one step at a time).
If I haven't mentioned it, I have little experience with replication, and need to do a bit of reading.
Thanks again for your help
John Zacharkan
John Zacharkan
October 19, 2001 at 9:48 am
That's an interesting idea. If these are not VLDBs, then that might work. I assume that if you log ship from one db to another, that the recipient db can be setup to replicate to the same db as the sender. The trick is to have replication disabled.
Actually, now that I think of it, I'd just keep the scripts handy for setting up replication. then if the standby kicks in, you can run these scripts to start up replication. It will be tricky, and obviously something you want to test, but it may work.
Steve Jones
October 19, 2001 at 11:54 am
Not a bad idea - you're putting a little more load on the box than you normally would, but depending on volume it might not matter.
Im not sure Im for using log shipping as a way to CYA in case of a bad data load. If you've got your transaction log backups, you CAN recover from such events, painful though it will be.
Andy
October 19, 2001 at 12:47 pm
quote:
Im not sure Im for using log shipping as a way to CYA in case of a bad data load.
I agree whole heartily, but the delay in loading the transactions gives me powerful insight into looking at the data before and after the data load. The transactions we're applying are quite often changes to the data and the application does not keep the historical information. We're given the data as gospel, occasionally however, the wrong data file is given.
John Zacharkan
John Zacharkan
December 18, 2002 at 7:17 am
I have read this thread with interest, as I have a third party solution to implement, which uses replication. I asked the question why replication? and the reply was:
1) the ease of use, maintenance and set up of replication as opposed to
clustering;
2) replication is immediate where Log Shipping depends upon when the
transaction logs are backed-up and you could lose data very easily;
3) replication is much, much less costly than replication.
Not sure what no 3) means, must be a typo..
I favour log shipping, given that up to a 15 minute delay would be acceptable.
However, I am unsure why it would be easy to lose data.
The two servers in question are on the same site.
Any comments would be appreciated.
December 23, 2002 at 7:30 pm
This is a great discussion.
I am currently setting up a SQL 2000 database. I am very concerned about availability (up to 15 minutes transition time is acceptable) and redundancy. I am considering the following based on your discussion:
1 primary SQL server with mirrored 36GB SCSI drives.
1 standby SQL server with mirrored 36GB SCSI drives.
It sounds like log shipping would be the best solution for me. Additionally, I like the scenario which includes an additional delayed backup in a third location. Would it be worthwile to cluster these two servers in conjunction with log shipping? Or, is this unnecessary?
Also, what is a "hot" standby (as opposed to a "warm" standby)?
Edited by - jabbathenut on 12/23/2002 7:36:20 PM
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply