Have SQL Server questions? Paul Randal has answers. Here’s what you can find in the February 2009 installment of the TechNet Magazine SQL Q&A column, which is available online now.
Question: We’re going to be upgrading most of our servers to SQL Server 2008, and one of the features that I’m looking forward to putting into production is backup compression. I know that I can turn it on by default for all databases on each server, but I’ve also heard that I might not want to do that. I’m not sure why I wouldn’t want to have the feature enabled by default, as it seems like I’ve got nothing to lose. Can you help explain the reasoning behind what I’ve heard?
Question: Last year we upgraded our databases to have database mirroring so that if a failure occurs, we can failover to the mirror and the application continues. While we were designing the system, we practiced doing failovers of the database and everything worked fine. Last week we had a real failure and the database failover occurred, but all of the application transactions stopped and the application didn’t connect to the failover server. In the future, how can I set up SQL Server so that it doesn’t drop the application connections during the failover so the transactions can continue?
Question: When we upgraded to SQL Server 2005, we redesigned our large tables to be partitioned so that we could take advantage of partitioned maintenance and the sliding-window mechanism. You described this in the August 2008 installment ("Partitioning, Consistency Checks, and More"). But we’ve encountered a problem. Occasionally, concurrent application queries are experiencing blocking across the whole table when the queries aren’t even accessing the same partitions. I’ve heard that SQL Server 2008 fixes this problem—can you please explain how I can stop this blocking?
Question: One of our servers had some issues with the disk holding the transaction log for a database, and the database became suspect. The most recent full backup was from five weeks ago, and it was going to take too long to restore all the log backups, too. It was out of hours when the problem occurred, so we rebuilt the broken transaction log to avoid the downtime. Under some circumstances, this could cause problems. But if nothing was accessing the data, then I think we’re safe. Did we do the right thing?
And be sure to visit the TechNet Magazine SQL Server Tips page.