Friday, February 29, 2008

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?

Wednesday, February 27, 2008

Microsoft 2008 Launch Event

Today is Microsoft's launch event for Windows Server 2008, Visual Studio 2008, and SQL 2008. If you haven't already signed up, you should do so at Heroes Happen Here. Beside's today's launch in Los Angeles there is a list of cities where the event will take place. There are a number of different track you can attend based on your interests. And the content should also be on-line for a while. The event is free to anyone who registers. Sign up soon - spots are going fast!

SQL 2008 won't be released until the 3rd quarter of this year, but I still plan to attend the launch when it comes to Chicago on March 11th. It's a good idea to get a head start on the new features. I'm still working my way through all the new features in 2005! But the good news is that most, if not all, features introduced in 2005 will stay in 2008. The one noticeable difference is Notifications - that's gone. And I still need to see what happened to the Surface Area tool introduced in the last release.

Thursday, February 21, 2008

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.

Saturday, February 16, 2008

New SQL 2005 training resource

Kalen Delaney, the author of the Inside Microsoft SQL books, is publishing a set of DVD's relating to SQL 2005. The content is also valid against the upcoming SQL 2008. You can either view the content on-line or buy the DVD. Both are great deals and far cheaper than other training videos.

Check out the site at http://sqlserverdvd.com/

Friday, February 15, 2008

Introduction, Part 2

I guess it's time I give a little info about myself. I currently working as a consultant for a company in Chicago (my client is based in Nashville). Most of my duties nowadays are DBA tasks, specifically performance tuning Microsoft SQL 2005 databases.

I've been with the same company since January 2000, working for the same client the whole 8 years. I started in an entry level position, mostly writing and maintaining MSAccess 97 reports running against Sybase databases. Right after I started we migrated the databases to SQL 7, keeping Access for the reports.

We're now in the final stages on migrating all the SQL 7 individual servers into 4 regional servers, 2 here in Chicago, 2 in Nashville. In addition to merging the data, we're also upgrading to SQL 2005. It's kind of ironic that we're just finishing up when SQL 2008 is right around the corner!

This last 8 years has been a huge learning experience for me. When I started, I barely knew T-SQL and I had never worked with a true relational database before. But I slowly started getting more involved with SQL, reading everything I could get my hands on, going to user group meetings, attending different seminars, whatever would teach me about SQL. And while most of it was over my head at first, gradually some of it started to sink in. I was able to remember enough to become a MCDBA a few years back. And I recently passed Microsoft's 70-431 exam. So I'm getting there.

But the more I learn the more I realize how much I don't know. Especially in the area of performance tuning. Right now for instance I'm investigating the CMEMTHREAD wait type, something that's starting to affect us. I'll be posting a more detailed explanation when i understand exactly what's going on.

In the meantime, I'm using this blog as a place to clear my head, "talking" a problem through so I understand it better. I'll put up some neat things I learn as I go. I'll try not to be too dry, though I'm definitely not a writer of any sort.

In summary, welcome to SQL Servings!

Friday, February 1, 2008

Introduction

Hi All

This is another blog about Microsoft's SQL Server products. There are numerous, more knowledgable blogs out there, and I'll reference them. This blog is more about my learning path, things I've learned about SQL, questions I have, rants I want to get off my chest.

I'll put up a bit about me later.