Happy Holidays!

I hope everyone has a very healthy, happy, and Merry Christmas.

Reporting Service "features"

I've been a proponent of Microsoft's SQL Server Reporting Services (SSRS since 2005) since it was first released in beta for SQL 2000. I thought the product was a natural, combining a report writer with the database. I especially loved the ease of deploying individual reports. The first product was rough, but I figured it would improve with time. By the time SSRS 2005 came out it was a stable product, but it lacked some features, like rich textbox formatting and exporting to MS Word. SSRS 2008 added those features, so I thought all was right with the world. Silly me.

My client has been using some reports written for SSRS 2005. Recently we began converting their existing reporting application from MS Access 97 (please don't snicker too loud) to SSRS 2008. We started with small, simple reports. These worked out nicely, and developers were able to learn how to use the report designer fairly quickly. I began converting a much more complex report that had tons of Access code-behind and sub reports. Much on the report is determined by what the user wants to see. Creating the report in SSRS 2008 was time consuming, but it worked out nicely.

The "features" came out after I created the first, main report and checked the reporting project into Visual Source Safe. After that I started running into all kinds of problems, some of which are;

○ Very often, with no reason, credentials for the data sources would change from what we use to no credentials. I first noticed this after I checked the solution out of VSS. At first I thought the last person to check out the file changed it for some reason. Then I noticed that it didn't matter who last touched the file; even files that were never checked out were affected. And this also happened to files that were never checked into VSS.
○ Another annoyance is when a dataset changed from running a stored procedure to running text. This can happen if you modify the data source (see above), and also happened in SSRS 2005 if you merely opened the dataset definition to see what it was pulling.
○ Inconsistent behavior. For instance, the main report has 2 very similar sub reports. The user can choose to see sub report A, sub report B, or neither. Simple, right? Just set the sub-report visibility to display based on the user's choice. It worked fine if the user chose A or B; the correct sub report was displayed and the correct sub report was hidden. But if the user chose to hide both, then both display. I'm not going to post the code here, but suffice it to say the code was correct; it's being used on many other sub reports on the same main report. So how did I fix it? I moved the code to a report function. The bottom line is the very same code that didn't work as an expression of the visibility property of the sub report worked correctly when called as a function!


There's more, but this is enough for this post. I'll be adding to it soon.

Object Explorer Details in 2008 SSMS

One of the things I love most in SQL 2008 SSMS is the way Microsoft redid the Object Explorer Details page. Now it shows some really useful information. You can change what columns display by right-clicking on the column bar and checking which columns you want to see.

I need to catch up

It's been quite a while since I've blogged about anything. Mostly it's because I've been busy at work; creating a new reporting interface, critical disaster at one of our data centers, busy time of year. But in reality it's because I've been too lazy. I mean, really, I've got a long commute to work (1 1/2 hours by train each way) so there's no excuse why I can't do more. If nothing else, it's a way for me to capture my thoughts/actions of the day.

So, beginning today, I (once again) vow to blog more often. And I'll try to keep this blog technical; no one really wants to read about my personal life.

SQL 2008 on August price list

OK, I guess this means we can order it next month. However this articlefrom a TechNet blog only states that 2008 will be on the August list, not that it will ship then.

But this is still good news. If it doesn't ship in August, surely it will be very soon after.

Catching up after vacation

First and foremost, I passed 70-447 on June 27th. I'm done with the 2005 exams, not going to worry about the BI track since I'm not that fluent in SSAS or SSIS (I do know SSRS pretty well, though). So I made my goal of being certified in SQL 2005 before SQL 2008 came out.

Speaking of which, Microsoft just announced yesterday that 2008 will ship in August. More on that later.

Installing RC0, Part 1

OK, so I installed SQl 2008 RC0 yesterday. It was mostly successful. But it was missing the Business Intelligence stuff. I know that there's an issue where you can't upgrade an existing BI install. But I'm running SQL2005 and I was able to install CTP6 with no problems. Unfortunately I don't remember the steps I took to install BI for SQL2008. I did uninstall CTP6 before starting in on RC0.

I've reverted back to CTP6 and I'll try again. This time I'll document the uninstall steps. I obviously missed something.

SQL 2008 RC0 is out

At least it's available through TechNet and MSDN. I'm downloading it now, and I'll play with installing it on my train rides next week. I can't really dig deep since I'm concentrating on 70-447. But once that's over...

One to go..

I passed 70-442 on 5/30. Now I've got my MCITP as aDatabase Developer. Next up, and probably my final SQl 2005 exam,will be the update exam 70-447. I've scheduled that one for 6/27. That means I'll have taken 3 exams in 2 months. It seems to be working. Scheduling the test early forces me to be more prepared. I can focus better on the study material when I have a real deadline in place.

Taking 70-442 Next

I must be crazy, or overconfident, or both. I just scheduled to take 70-442 on May 30th. I'm figuring 2 things; 1, I'm pretty sure of the test material since I work with SQL 2005 daily and I've already read the MS Press training guide and 2, Microsoft's free Second Shot voucher expires at the end of the month. So this way, even if I fail (but that won't happen, right?)I get to retake it at no extra cost. So there's less pressure.

And since I've already passed 70-431 and 70-441, this will make me MCITP as a database developer.

Passed 70-441

Yesterday I passed Microsoft's 70-441 exam, Designing Database Solutions by Using Microsoft SQL Server 2005. Since I've already passed 70-431 and I'm also a MCDBA, I just need to take (and pass) 70-442 and 70-447 to become MCITP as both a Database Developer and a Database Administrator. Maybe I can run across another free exam voucher!

I don't want to break any confidentiality rules, so I won't talk about the exam in detail. But I will say that this exam was the easist Microsoft exam I've taken to date. I've been working with SQL on a daily basis for nine years now, and 2005 since it first was a CTP. That experience, plus now being the de facto DBA on my team at work, was major in preparing for the exam. The MS Press Training Kit was good for studying areas I don't know as much about, like the Service Broker and Notification Services, and it has a good exam simulation. And of course I must thank Microsoft for having the series of 4 webcasts on 70-441 (they're available on demand, I'll post the link later), and Tim Kashani for the great job of presenting the material. Not to mention receiving the free voucher.

SQL 2005 SP3 on the way

The good news: It looks like Microsoft is finally going to release Service Pack 3 for SQl 2005. The bad news: It won't be until Q4 of 2008, after the release of SQL 2008. Aaron Bertrand on SQLBlogs talks more about it here.

This is something that was a long time coming. Microsoft has something like 7 cumulative hot fixes. Some of the things on that list are things I want to start testing. One patch is for a problem I see on occasion, where our production servers kick out a SQL dump on a non-yielding process. This was addressed in SP2, which we're running, but not fully. I've seen other blogs that referenced a fix in one of the patches.

Taking 70-441 in 3 1/2 weeks

Hopefully I'll have enough time to study, especially since I'm on vacation for the 2 weeks previous to that.

I've read the MS Press training kit for this exam, and I've already passed 70-431 last summer. I know that won't be enough to pass this one, bit at least i've got a starting point for my studying. I also watched 4 webcasts that the Microsoft Partner program had on last year; I'll re-watch those. Too bad they're not downloadable. They'd be perfect for my daily commute.

But I can't complain about those webcasts. I got a voucher to take the exam for free!. The only thing was that it expires on May 1st, hence the quickly approaching date.

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?

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.

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.

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/

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!

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.