On Tuesday, the CTP for SP2 was released for people to begin testing against. We actually heard about it from an announcement sent to Brian on Wednesday, but the newsletter was already queued up and so we didn't announce it. Since it's not a final product as well, I'm not sure how many people I want to download, install, and then complain to me about 🙂

However, we're trying to be a great source for SQL Server DBAs to learn and better understand their systems, so we did do a preliminary test on the product and wanted to share some information with you.

Readme

The first thing I usually do with a patch or Service Pack is check the readme file to be sure that I'm not going to blow up a system doing something stupid. I'm kind of hoping that Microsoft (or any vendor) is putting the warnings and big problems up front and clearly in the Readme. So far I haven't seen anything buried, but you do have to take some time and look through the document. For SP2, it's here

One of the first things that struck me with this document is the requirements for disk space. There are 4 requirements, 2 temporary and 2 permanent. The temporary ones are no big deal, but the two permanent ones struck me as a little crazy.

You need about 2GB to install the SP, but just about 1GB after installation. Not that 1GB is a large amount of space these days, but it's still a lot for a Service Pack. However it's not the total size that is surprising, it's the distribution.

Many people do not install SQL Server on the C: drive because of standards or the desire to separate out the application from the OS. This Service Pack detects the SQL Server installation directory and requires 50MB of space. But it then asks for 1GB on the system drive.

Again, 1GB isn't a huge amount, but for people that subdivide their arrays and still often install the system drive with 8GB or even less, this can be a problem. I'm surprised that 1GB is needed on the system drive and more surprised that it's REQUIRED on the system drive. I think it's silly and unnecessary for Microsoft to require space on the system drive. Perhaps there are .NET upgrades or something in the registry, but 1GB is a lot. Hopefully SP3 won't add to that requirement and cause space issues on too many servers.

Other than that, the Readme has a section devoted to Vista, which is nice given that the people that mess with CTPs might be messing with Vista as well. The standard distributor-publisher-subscriber order is repeated for replication configurations. They mention your Add/Remove might fail and give you instructions for fixing various components along with the locations of the MSI files.

Also kudos for listing important items from SP1, since there's the possibility that some people may be skipping SP1, especially for installs of new servers.

Installation

Installation is straightforward. It's the typical Next, I agree, Next, ... process. I was glad to see the first page link to the SP2 KB article for changes in case you wanted to check. Or forgot to and needed a reminder.

It detected both instances on my Windows XP laptop, and I deselected one in case of issues. I could be up and running quickly with a quick attach of databases. It also detects which additional systems you have installed, such as SSIS, the Native Client, SQLXML4, etc. It lets you know which are required and which are already updated if you click on one. Even though you see checkboxes, you can't really pick and choose. The entire instance is upgraded, probably a very good design decision.

Next you are asked for authentication information. You can choose one account for all instances or set separate ones for each instance.

Lastly you get the choice to send information to Microsoft for errors and exceptions. After that the install starts and then a reboot is required to get the service pack completely set up.

Changes

After a reboot, everything on my instance seemed to work ok, though to be honest I don't have that much running on it. My packages work, my maintenance plans run, and queries seem to be ok.

One of the interesting gotchas is that maintenance plans cannot be edited unless your tools are updated to SP2 as well. While this seems like a big deal, it comes with some interesting enhancements (or bug fixes, depending on your point of view).

You no longer need to install Integration Services for maintenance plans to work. They are not natively supported with the Database Engine. Multiserver administration has been expanded as well, allowing logging to remove servers and multiple schedules are allowed.

There is a new datatype as well: the vardecimal format. This allows more efficient storage of decimal and numeric data. This is similar to the smalldatetime to allow less storage for data that require less precision.

There are many more changes, login triggers, SSIS changes, SSRS changes and more. You can get a list of changes in the What's New in SP2 document.

Conclusion

When you get your instances upgraded the new build number shows at 9.0.3027 as opposed to 9.0.2047 for SP1. A whole new set of numbers to remember for this version.

Overall the setup for this is very smooth and easy to get through. The options to hit all your instances at once is very useful and handy once you've completed testing.

This is a pre-release patch, so you want to be careful about installing this on production servers. However this is a great place for you to do a little advance testing if this patch might impact you. I'd recommend you check the list of changes and if any of them will impact your environment, give this Service Pack a test and see if it helps.

Or more importantly if it breaks anything. This is a great chance to let Microsoft know if your environment experiences issues and have them correct the service pack before it releases. While the Service Pack has been tested and is feature complete, the CTPs give Microsoft a last chance to catch any bugs that they haven't been able to simulate in their labs.