CTP6 issue with leap year

I just ran across this, ironic since I just updated a test computer from CTP5 to CTP6and wondered why it wasn’t working;

“We have recently discovered an issue with SQL Server 2008 CTPs that result in SQL Server 2008 not starting or installing on Feb 29 GMT only. We recommend that you do not run, install or upgrade this CTP on Feb 29 GMT to minimize any impact in your environment. You can install starting on March 1 GMT. If you have an immediate issue that cannot wait until March 1st GMT contact csskat@microsoft.com before taking any further steps.”

I mean, I know it’s pre-release and all but come on – leap year?

CTP6 – Backup compression


The Enterprise Edition of SQL 2008 now offers the ability to compress database backups. If you use the GUI for the backup, the compression option is the last choice on the Options page

Once there you’ll have 3 choices; Use the default server setting (set to 0 after installation, use sp_configure to change it), Compress backup, and Do not compress backup. If you use a script, just add the COMPRESSION keyword:

BACKUP DATABASE [AdventureWorks] TO DISK = N’C:\ADWorks.bak’
WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

I tested backing up the AdventureWorks sample database (always a good idea if you’re going to play with it). The uncompressed file size is roughly 175 MB; the compressed is about 40.25 MB. There’s not much free space in the database, less that 1 MB, and the size of the database matches the uncompressed size, which is what I expected.

What I didn’t expect is that the compressed backup appears to have run faster than the uncompressed backup. When testing I ran each backup 3 times and I only used the default settings (other than the compression option). Both backed up to the same disk drive and nothing else was running at the time that would have affected the backup.

Here’s a sample output from the uncompressed backup:

BACKUP DATABASE [AdventureWorks] TO DISK = N’c:\ADWorks_Uncompressed.bak’
WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Processed 21864 pages for database ‘AdventureWorks’, file ‘AdventureWorks_Data’
on file 1.
100 percent processed.
Processed 1 pages for database ‘AdventureWorks’, file ‘AdventureWorks_Log’
on file 1.
BACKUP DATABASE successfully processed 21865 pages in 20.349 seconds (8.394 MB/sec).

And here’s a sample from the compressed version:

BACKUP DATABASE [AdventureWorks] TO DISK = N’C:\ADWorks.bak’
WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Processed 21864 pages for database ‘AdventureWorks’, file ‘AdventureWorks_Data’
on file 1.
100 percent processed.
Processed 2 pages for database ‘AdventureWorks’, file ‘AdventureWorks_Log’
on
file 1.

BACKUP DATABASE successfully processed 21866 pages in 13.003 seconds (13.137MB/sec).

But then I remembered Idera’s SQLSafe program. I ran the evaluation version a while back. I thought the speed increase there was due to some trick of Idera’s. That’s not really the case. Read this from the CTP’s BOL:

Performance Impact of Compressing Backups
Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

That makes sense. It takes less time to write a smaller file than a larger file. So I was looking in the wrong area. I should have been checking the CPU counters.

SQL 2008 February CTP is available

CTP6 is available at http://www.microsoft.com/downloads/details.aspx?familyid=749bd760-f404-4d45-9ac0-d7f1b3ed1053&displaylang;=en&tm;

I’ve just finished installing it and I’m starting to poke around a bit. Now I haven’t been keeping up with the new or changed features in SQL2008, and I havn’t played with the previous CTP’s. So I was surprised to see that the Area Configuration Tool is not there. What’s replaced it? Or is it just missing from the CTP and it will be in the final rollout? Definitely something to look into.

The installation went pretty smooth. SQL 2008 is running next to SQL 2005 on this computer and there were no conficts during the installation. I did have one issue, though. When I tried to set up the accounts to run the different SQL services, it told me the password was wrong. No, I wasn’t typing it incorrectly, I tried many times. I finally got past that by creating a new administrator account and used that to run the services. Later I’m going to go back and change what account they run under, to see if it will allow me to do that.

I’ll be back when I poke around a bit more.