Watch Out For SSIS Package Migration/Upgrade Errors

After successfully upgrading a multi-instance SQL Server 2005 Failover Cluster to SQL Server 2008 R2, I got woken up today by a page from a SQL Server Agent job failure. Knowing that the job was created by one of my colleagues, I ignored it for a while since it only runs once a week and is not really that critical since it only performs tasks for reporting purposes. However, I just can't seem to get it off my head that what seems to be a successful upgrade caused some issues on the SQL Server Integration Services (SSIS) components. I checked the SQL Server Agent Job History for more information but only found this bit of information in the error log

The package failed to load due to error 0xC0010014

After a quick search on Google, I found this blog post on the MSDN Blogs site that explains what could be a possible cause for the error. While the blog post defined a different error number from the one I had, I was sure that this was it. Knowing that I had SQL Server 2005 Failover Cluster before, I had a feeling that the DTExec.exe executable file was still hanging around. True enough, I saw the EXE file. And the SQL Server Agent job that failed points to the old version of the DTExec.exe file. I immediately changed the path from within the SQL Server Agent job to point to the new version of the DTExec.exe file. The command which points to the old path looks like this

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe

After modifying the path, I now have this

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

One thing to look for after upgrading a SQL Server 2005 instance to SQL Server 2008/R2 (whether it is a failover cluster instance or not) is to make sure that your SSIS packages are reconfigured to point to the new path of the DTExec.exe executable and test them accordingly 

Installing SQL Server 2008 Failover Cluster on a Windows Server 2008 R2?

I did a demo fest on installing SQL Server 2008 Failover Cluster on a Windows Server 2008 system a few weeks back for a user group event and the attendees requested that I post more information about how to do a slipstream of service pack in a SQL Server 2008 installation. With Windows Server 2008 R2 already released, Microsoft released KB article 955725 highlighting the need for SQL Server 2008 Service Pack 1 when installing on either Windows 7 or Windows Server 2008 R2. I wrote an article on MSSQLTips.com about it to supplement the series on installing SQL Server 2008 Failover Cluster.

This blog post came a bit late as I needed to wait for the article to be posted on the site so I can use it as a reference.

"The current SKU is invalid." error? Didn't I pay for my license?

I've done quite a few SQL Server 2008 cluster installations before which is why this struck me as a surprise. I was trying to rebuild one of my test mahines when I hit upon this error message while adding the second node on a two-node SQL Server 2008 cluster on a Windows Server 2008.

The current SKU is invalid.

After a quick search on the Internet, I found out that this is a bug (my first time to get bitten by it). There is currently a Microsoft Connect item regarding this and it mentions having Cumulative Update 1 applied should fix this issue but I wouldn't want to apply a hotfix for something like this as I normally do that after the entire installation is complete. A few more hits on Google directed me to a forum post that mentions about deleting the DefaultSetup.ini file from the installation media. Just make sure you copy the installation key from this file before deleting it or simply do what I did - move the file some place else. What I usually do is copy everything on a local disk for the installation to be a lot faster. After going thru the Add node to a SQL Server failover cluster option, I manually entered the installation key and it the installation completed successfully. I wonder why I never got this error during my previous installations

Reconfiguring your SQL Server disks - 2008 edition

This was from my old blog entry from way back November 2006 and remembered how easy it was to fool SQL Server when trying to replace disks. I was configuring a clustered SQL Server 2008 on a Windows Server 2008 and testing the disk resources for failover. I tried failing over one of the disks and it went out pretty well. The next disk resource that I failed over happened to be the one containing the system databases. To my surprise, it did not failover to the other node causing the SQL Server resource to fail as well. It took me quite a while figuring out that the disk subsystem is somehow corrupted (good thing it was just a test environment) so what I ended up doing was recreating a new disk subsystem and redefining it on my cluster using Failover Cluster Management. I made it available on my SQL Server 2008 resource. I knew I would definitely have downtime as I need to bring down the SQL Server cluster resource. Here's what I did - I brought the SQL Server resource offline, copied the contents of the original disks that contain my system databases to the new disk resource, changed the drive letter of the original disk to something else and rebooted the node that originally hosted the disk resource that is failing. Once it is up, I can reuse the same drive letter to the new disk resource which now has the original system databases. When I brought te SQL Server resource back online, it's as if nothing had happened (except, of course, for some errors in the Clustering error log).

Note that this is definitely not a substitute to a valid backup and restore process for your SQL Server databases. All I'm saying is it just works

TSQL Debugger is now on SQL Server 2008 RC0

I have long waited for this feature as I got used to Visual Studio's debugging feature where you can simply put a breakpoint beside a code line and hit the debug button. SQL Server Management Studio now has a Debug button together with the Execute button (except that in Visual Studio, your F5 key would be your Debug command whereas in SQL Server, that would be the Run/Execute command).

This will allow you to step over your code - whether it is calling a stored procedure or a TSQL script - and step thru it a line at a time or at one shot. You can even step thru nested calls, like calling a stored procedure that calls another stored procedure. It will open up a new tab displaying the codes for the procedure or function that you are calling from your TSQL code. I'm just glad it's already in RC0. Check out my video from BlogCastRepository.com on how this works

SQL Server 2008 Videos from BlogCastRepository.com

I was informed by the owner of the site that my videos have just went live. I've spent quite a number of days working on the videos to make sure that subscribers will get as much as they can in their pursuit of learning about SQL Server 2008. I would appreciate your feedback on what else you would like to see on the video list

Check out the BlogCastRepository website for my SQL Server 2008 videos. I'll come up with SQL Server 2005 videos as well in the future

Exploring Database Backup Compression in SQL Server 2008

Database backup size is always an issue in very large databases. Which is why we plan our backup and restore procedures properly to include either differential and filegroup backups. Most of the times, we use backup compression tools like LiteSpeed for SQL Server from Quest or SQL Backup from RedGate. SQL Server 2008 has a built-in compression engine for you to compress your database backups. I have written an article about this feature in MSSQLTips.com and created a video at BlogCastRepository.com

Encrypting your database backups in SQL Server 2008

I was asked a question about how you make sure that your SQL Server database backups are secured and I ended up saying, "go get a third-party tool like LiteSpeed for SQL Server from Quest Software." While it is true that third-party tools can provide additional layer of security on your database backups, budget is always a constraint. Good thing SQL Server 2008 has included a feature called transparent data encryption which you can use to secure your database backups. I have a written an article on MSSQLTips.com and created a video at BlogCastRepository.com about this feature which is worth having a look at.