December 8, 2011 at 11:40 am
I think I posted this in the wrong thread....earlier!
Greetings!
I have been giving some thought to Data compression for some tables/indexes on my databases which are not read a heck of a lot but, mostly inserts for data we want to keep around.
I have a few questions and would appreciate if someone here has already implemented something of the sort which I am trying to do. First of all I have mirroring as well as log shipping enabled on these databases. (Mirroring is enabled in the same LAN whereas Log shipping is over a WAN)
So, the questions I have are as follows:
- Is a REBUILD (Index/Table) operation BULK LOGGED? i.e. will it create a smaller TLog backup compared to a one in FULL recovery mode?
This is not a Data Compression question per se, but I would like to plan ahead if there are going to be huge TLogs after data compression to shipped over the WAN.
- Is compression enabled on table or index reversible?
- Once I issue the command ALTER TABLE/INDEX (DATACOMPRESSION = PAGE), are all new inserts going to be compressed automatically or do I need to run a job to compress the new data being inserted.
Thanks in advance to your responses.
December 8, 2011 at 11:45 am
anish_ns (12/8/2011)
- Is a REBUILD (Index/Table) operation BULK LOGGED? i.e. will it create a smaller TLog backup compared to a one in FULL recovery mode?
Yes, it is, but that results in log backups as large or larger than log backups in full recovery. It's the log usage that's smaller, not the log backups.
However if you have mirroring you can't switch to bulk-logged recovery, so the question's moot.
- Is compression enabled on table or index reversible?
Yes, completely. ALTER INDEX ... DATA_COMPRESSION = NONE
- Once I issue the command ALTER TABLE/INDEX (DATACOMPRESSION = PAGE), are all new inserts going to be compressed automatically or do I need to run a job to compress the new data being inserted.
Neither. The compression is triggered after a page gets a particular % full. Nothing you have to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2011 at 11:50 am
Thank you Gail.
Regarding the first point u made, I was going to break the mirror before switching to bulk-logged for log shipping. Since mirror is on a LAN restoring and re-creating the mirror is a snap. It's the logshipping which is a problem. Transferring a 4-5GB Tlog backup over a 3mbps link is not pretty....sometimes it takes half a day and thats just for one database. 😀
I suppose Data Compression is an option for me only if I break the log shipping....unless you can advise on another strategy.
Once again thanks for your help
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply