March 27, 2006 at 11:51 am
We would like to establish a warm standby server in a remote location over a WAN link, probably around T1 speeds. The standby server should have at most 1 day old data. Preferably, it would be less than an hour old during production hours (8am-10pm).
Based on my calculations, I figure we can transfer 5 gig or so per night. Our primary production databases occupy about 30 gigs of space. We've seen some third-party SQL backup products mention 90% compression is possible, which would make nightly full backups theoretically possible, but we are skeptical of the feasibility of that solution, and it doesn't provide any backups throughout the day. Perhaps those products could be used for differential backups throughout the day as well.
Log Shipping is often proposed as the best method for this situation, so we tested that solution, with a 15 minute interval. We found that throughout the day, our transaction log shipments were plenty small enough to be transferred over the WAN link. However, during our nightly maintenance, the transaction logs would balloon to be as big as the database itself, or bigger, and the total transaction log throughput to the other host ended up being double the size of the databases themselves. (This is with FULL and BULK-LOGGED recovery options.) Without compression that would be impossible over our WAN link, and it seems unlikely that even great compression would make the files small enough.
We have heard that the nightly maintenance you can set up using the wizard, which includes re-indexing and other such intensive activities, can fill up a transaction log in a hurry, even with the BULK-LOGGED recovery option. We also have a variety of internal nightly maintenance tasks to do various things to our data. So while some of this nightly activity might be able to be eliminated by different forms of index maintenance-- or forgoing it altogether on a daily basis-- it seems that there might still be a lot in the transaction log. Our next step will probably be to test the process without the nightly index operations to see what affect our internal processes have alone. If they are sufficiently light, we would consider pushing off the index operations to the weekend. The ironic thing is that the actual data changed throughout the night is almost nil.
Another option we're considering is nightly full backups using the third-party, compression-based tool, with differential backups occurring every 15 minutes or so. But that's a non-standard setup so we'd be somewhat worried about the fragility of it. The standard log shipping in 2005 sets up jobs and other monitoring services, which wouldn't exist in this custom setup.
Does anyone have any advice on the nightly maintenance, its value, and its impact on log files? Do differential backups use the transaction log to determine what has changed since the last backup, or do they use some other method? I seem to remember reading that they examine which pages have been modified since the last backup, and that it can be used in SIMPLE recovery mode, which would indicate to me it's not using the transaction log. If we're considering transferring the compressed nightly backups anyway, should we just re-initialize the log shipping nightly? That doesn't seem to be part of the automated setup, though, so it would still be a somewhat custom solution. We're considering SQL-Backup from Red Gate. Are there better alternatives for this?
Any insight on any of these questions or issues would of course be appreciated.
Thanks for your time.
March 27, 2006 at 2:23 pm
Microsoft commented on this issue in May 2003 during Q&A session, indicating that there's not much you can do.
They suggested, in a seperate MSDN post, using a third-party compression tool (from SQL Safe from Idera). A user there noted 98% compression. Perhaps that would be good enough to work even with our constraints.
===========================
Q&A
---------------------------
Host: Allan (Microsoft)
Q: We are considering using log shipping, but reindexing jobs create very large transaction log backup files. The backup SQL box would be in another city, so we're concerned about transmit time. Any suggestions?
A: Not much you can do, as transaction log backups cannot happen while you are doing a full database reindex (i.e. DROP INDEX). And at the end, your TL will be huge. So maybe consider doing the online DBCC INDEXDEFRAG, but that may not buy you anything. Sorry, it's not an easy answer as it is just the way the SQL Server engine works to ensure your data is consistent.
http://www.microsoft.com/technet/community/chats/trans/sql/sql0528.mspx
===========================
MSDN
---------------------------
"Please note that many of the internal SQL installations at Microsoft use compression when doing log shipping and backups."
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=207967&SiteID=1
================================
Others have noted this issue, with the general recommendation to just deal with it-- or do less indexy stuff. It seems rather unsatisfying.
March 28, 2006 at 10:34 am
Let me add that INDEXDEFRAG + UPDATE STATS creates even larger transaction logs than reindexing. We use log shipping and have tried both, but need to stick with the defrag. I see no other option than using a compression tool to make the files smaller.
March 29, 2006 at 3:28 am
"...which includes re-indexing and other such intensive activities, can fill up a transaction log in a hurry, **even with the BULK-LOGGED recovery option.**"
That's not true.
I'd suggest implementing database mirroring instead of log shipping.
March 29, 2006 at 6:00 am
If you are using SQL 2000, you could take a look at SQL LiteSpeed. It is a backup product that uses compression and is supposed to be able to be used with Log Shipping. You may want to download a demo version to check it out.
March 29, 2006 at 8:40 am
Microsoft Books On-line only lists three index operations which are minimally logged-- CREATE INDEX, ALTER INDEX REBUILD, and DROP INDEX. It doesn't mention DBCC INDEXDEFRAG. I'm not positive what the maintenance plans are doing each night, but now that I review them, I see a Reorganize Index Task and a Rebuild Index Task. That's probably overkill, and the Reorganize might be using the INDEXDEFRAG, which might be generating the log bloat. Recall that even with BULK-LOGGED enabled we still got 30 gigs of Tran Logs (double the DB size).
(By the way, it seems that the ALTER and DROP are new to 2005, as the 2000 docs seem to only mention the CREATE.)
If we typically don't use bulk inserts, is there much danger to us relying on a BULK-LOGGED database backup? I suppose best practice will be to convert it to BULK-LOGGED during the maintenance plan, and then back to FULL after it, just to be sure.
Thanks for your input.
March 29, 2006 at 8:50 am
If you plan on switching the recovery mode you can't use Log Shipping because it will break it. Been there 🙂
March 29, 2006 at 8:57 am
The Red-Gate product is similar but 1/3 (or less) the price. There was a cross-comparison review on SQL Server Central a while back and found that it's competitive with the expensive ones.
http://www.sqlservercentral.com/columnists/wbrown/fourofakindbackupsoftwareshootout.asp
There was also a VS forum post which didn't really have anything bad to say about the cheaper Red Gate product.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=182110
March 29, 2006 at 9:01 am
You mention implementing database mirroring. However, that hasn't been blessed by Redmond for prime time, has it? Also, I'm not sure whether it will be available-- and if so to what extent-- for SQL Server *Standard*, which we're using.
Also, isn't Database Mirroring just quasi-real-time log shipping, so wouldn't it be subject to the same issues?
Also, we want a delay of a few minutes for the backup server, rather than real time, just in case there's any major data loss; it could usually be recovered quickly from the backup server before it gets updated with the corrupt info.
March 29, 2006 at 9:23 am
After reviewing some literature from MS, I was reminded that it should be available in some form for Standard, and it looks from the grid like it will have the basic features we need (though I didn't take the time yet to figure out what the various features really meant on the grid).
One thing that struck me, though, is that the mirror copy will not be available as a read-only database. That's unfortunate. Even if it's inadvisable to use it for reporting purposes, because of intermittant availability, it brings great peace of mind that things are working if you can do spot-check queries on the mirrored server to verify that, indeed, the information is there and it's functioning properly.
"Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time. (See 'Database Mirroring and Database Snapshots' later in this paper.)"
(I don't think Snapshots are supported in Standard.)
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
March 29, 2006 at 1:16 pm
I'm skeptical of that. Perhaps it will if switching between SIMPLE and FULL/BULK-LOGGED, but from what I've read of Microsoft's site, SQL Server is pretty good about switching between FULL and BULK-LOGGED. You only end up losing the ability to do point-in-time recovery.
http://msdn2.microsoft.com/en-us/library/ms190203(SQL.90).aspx
This article below does imply, by noting a bug with a DTS task, that switching to SIMPLE will break log shipping.
http://support.microsoft.com/default.aspx?scid=kb;en-us;314515
Also, this was brought up in these forums a while back in these forums and one poster (admittedly a "Newbie") indicated it would not cause a break.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=93685
We're going to give this a try anyway and we'll see how it goes.
March 29, 2006 at 1:22 pm
This is an interesting post in this thread stating that minimally-logged acitivity, while taking up little space in the log, will still generate large log backups, which would seem to impact log shipping. That makes the BULK-LOGGED mode for log shipping (and maybe mirroring?) pretty much useless.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=265408
That seems to be bolstered by this statement:
"In addition, you can easily switch between FULL and BULK_LOGGED modes if you usually operate in FULL mode but occasionally need to perform a bulk operation quickly. You can change to BULK_LOGGED and pay the price later when you back up the log; the backup will simply take longer and be larger."
http://www.uxcess.com.my/ftp/WORDWARE/wordwareweb/oc_dev_other/0735609985_sc.htm
March 30, 2006 at 2:24 am
I was refering specifically to the rebuilding of indexes which is expedited through the use of BULK-LOGGED.
Yes, ALTER is new to 2005 - everything is done through CREATE, ALTER, DROP now, though there are back-ward compatabilities. Your reindexing should be done through ALTER INDEX and you have online options now too.
March 30, 2006 at 2:27 am
Err, I assume you mean changing mode to simple.
Log shipping can be performed under full or bulk-logged.
March 30, 2006 at 2:31 am
Yes, database mirroring is not a reporting feature - it is database snapshots, and they are enterprise level.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply