Allen White

Virtual PC - Making Life Easier

When I became an MCT (Microsoft Certified Trainer) I started using Virtual PC's as part of the classroom demonstrations and labs. Until then I'd used them on occasion, but they never struck me as very useful. In preparing for the classes and presenting in user groups and conferences I learned to appreciate how nice it is to isolate a demo or test environment from my "real" work environment.

Last year I got an MSDN subscription, which gave me a variety of operating system installation media, which I'm allowed to use for development purposes. I built a VPC image for Windows XP SP2 and for Windows Server 2003 R2, registered each one, and used Windows Update to bring them to current patch levels. Once that was done I shut down the VPC and copied the image files to separate directories (leaving the original files in place.

Now, whenever an issue comes up requiring a particular version of SQL Server (be that SQL 2000, SQL 2005 or SQL 2008, and specific to whatever patch level I need, I can copy the "backup" image files to the primary folder, start up the VPC image I need, then install whatever version of SQL Server I need to get some work done.

Today, for example. A while back I had a job on a production server that was no longer needed (per the business user), so I dropped it. Today I found out it was needed, but the backup from when that job was dropped was from SQL 2005 SP1. (I know, I should have scripted it before I dropped it.) In any event, by using my virtual machine I was able to install SQL 2005 SP1 and restore the msdb database. I was then able to retrieve the job and it's back and everyone's happy.

It's also a great environment for testing the various CTP releases of SQL Server 2008 and Visual Studio 2008. Instead of uninstalling previous versions and cleaning up registry entries, just copy over a fresh VPC image and I'm off to the races.

Good times.

Allen

Very Large Database Maintenance

I've got a third-party app that uses a SQL Server 2005 database. The database has grown to 190GB, with 150GB of that in one table (116M rows). I've been trying to get the greenlight to partition this table since June, and finally got it this weekend.

I wasn't able to start my process until Friday at noon, and had space on my backup drive to export the table data. (By law I have to keep two years worth of data in that table for auditing purposes - it has less than a month over that now.) I used bcp native mode to export the data and it took from Friday noon until 9am this morning.

Once the data was exported I dropped the table. (Until this point all tables are in the PRIMARY filegroup, and it has three physical files in the group. Now I have two ndf files of 80-90GB each that have 16-18GB of data, the rest free. I need that space to allocate for the partition files, so I need to shrink the files.

I started the shrink (dbcc shrinkfile - sorry Paul [Randal], I know, but I need the space) at 9am. After 8 hours I decided it was not the correct solution.

I then built a new database to temporarily house the data so I could drop and recreate the application database with appropriate file sizes. The copy, using a combination of "select into" for small tables, and SSIS data transfer for the larger tables, took over 11 hours. By this time it was after 4:30am and the start of business was closing in rapidly. I knew I didn't have time to build the new database and get the data back in from the temporary database, so I abandoned the task. The import of the large table was causing enough performance problems that we've decided (in a meeting with the business owners) to restore the full database from the backup I have from before I dropped the large table.

The problem we have with the database still exists. That table grows by over 8 million rows per month, increasing by as much as 10 percent each month. All tables are in the PRIMARY file group so I have to backup data that stays constant every night.

The point really is that size makes a real difference in how to solve a problem. Solutions that work well on databases of 1 or 2 GB aren't at all appropriate for databases approaching 200GB. I have a couple of ideas on how I could have done some things differently, but of course they're based on the experience I gained in attempting to perform this maintenance.

Hindsight isn't 20/20, but it's close.

Allen

SQL Server 2008 - Is It Time Yet?

At the PASS Conference in Denver the question was frequently asked - is it time yet for another release of SQL Server? Microsoft execs have said repeatedly at TechEd and at PASS that they've "heard us loud and clear" that 5 years is too long between releases. As a DBA with a company that maintains (and pays handsomely for) Software Assurance I understand that the 5 year window is too long.

On the other hand, I still have to support SQL Server 2000 because many of the applications we run are third-party applications, and the vendor(s) aren't yet ready to support SQL Server 2005. (I've even tested apps against SQL Server 2005 to show them it works fine, but they won't support it, so I can't upgrade.)

There are features in SQL Server 2008 that look like they'll save my company some significant time and effort. Things like Change Data Capture and the Dynamic Management Framework are really exciting. What I'm wondering is if it might be worth holding off a little bit to make certain that these work as they should work. It's hard to tell at this point, because of the slow release of CTP's for Katmai. (CTP5 was being called the "October CTP" at PASS. I even teased Paul Mestemaker about "October" meaning October 31. It's now November and CTP5 doesn't appear close to ready for public consumption.)

I'm well aware of the urgency at Microsoft on getting the code done and into the test cycle so the 2nd Quarter RTM schedule is maintained, but I also know that some features that should be in SQL Server 2008 won't be there because of that schedule. That's really too bad.

At this point I'm learning as much as I can about SQL Server 2008 because it's exciting, but it'll be at least two years before I'll be able to justify any upgrades here. The issue has more to do with my third-party apps than our in-house development, but it's hard enough to maintain two separate releases, much less three.

Allen