Using SQL Storage Compress in the Real World – Webinar Video
You can watch the complete webinar here. The text of the Q&A is also available further down the page.
Using SQL Storage Compress in the Real World – Q&A
Can the transactional replication DB be in compression mode and the source DB in normal mode?
Yes, the compression is by database. The choice to compress is transparent to SQL Server. It doesn't matter what SQL Server is using the database for.
Can you further compress the database backup?
Maybe, but there's a limit to how compression works. There will be fewer patterns to compress, so compressing a compressed set of data doesn't help. Keep in mind that this is transparent to SQL Server. When you run a backup in SQL Server, it will read the data, uncompress it, and stream it to the backup device (disk/tape). If there is compression enabled, native or something like SQL Backup Pro, then it's compressed through that means. If you were to enable SQL Storage Compress to compress the backup file, it might get slightly smaller, but I don't know it would be worth the CPU. Like zipping a .zip.
If we use compression for a mirrored database and we end up having to fail over to it, how does it impact performance when that database becomes the live production database?
So many variables here. Is the hardware on the secondary the same or not? Other load on it? If everything is the same, then you will see increased CPU, but reduced I/O when you go to disk. Usually in terms of performance time, this means things are faster (I/O time trumps CPU time). However this does mean increased CPU load. It's not a lot, but if you are CPU bound for queries, they may run slower or faster, depending on the I/O involved.
Is this also recommended for active production systems then?
It's recommended for any system. Usually people start in dev/test, to test it. Then move it to secondary systems (log shipping/mirroring/AlwaysOn) and then to production – a natural flow.
When will the GUI have full support for SQL Server 2012?
This is on the team’s backlog. SQL Storage Compress supports SQL Server 2012 using T-SQL scripts.
Can we compress replicated destination database?