Upgrade to SQL Server 2008 with SQL Server Management Studio (SSMS) 2008

I have installed SQL Server 2008 and SQL Server Management Studio 2008 a number of times and it has always been a snap*..until yesterday.

As it turns out, previous installations were always applied to machines (mostly VMs) which weren’t running early versions of the SQL Server and associated tools.  Yesterday, I performed my first upgrade from SQL Server 2008 and SQL Server Management Studio 2005 and it took a painfully long time to get everything in place.

There seems to be general discontent with the SQL Server 2008 installation/upgrade experience – particularly when it comes to SQL Server Management Studio. After yesterday’s debacle, I can understand why.  In retrospect, an upgrade to 2008 is simple if you follow the right steps.  If not, you’re bound to get frustrated and waste a couple of hours. 

If I knew then what I know now…

• You cannot manage SQL Server 2008 with 2005 Management Tools. Go ahead and try it. SQL Server Management Studio 2005 will bark at you.
• A stand-alone SQL Server Management Studio 2008 installer does not exist.  The Management Tools must be installed as a features available via the main SQL Server2008 installer. The Microsoft download for SQL Server Management Studio 2008, as far as I am concern is a hoax.  It is merely the SQL Server 2008 installer given a different file name. Seriously.
• You cannot run SQL Server Management Studio 2005 and SQL Server Management Studio 2008 side by side. An attempt to install SSMS 2008 will fail if you have yet to uninstall SSMS 2005.

Save yourself some trouble.  Before kicking off your SQL Server 2008 / SQL Server Management Studio 2008 installation or upgrade, uninstall SQL Server Management Studio 2005.  If you don’t, you will be able to successfully install SQL Server 2008, but the Management Tools installation will fail (gracefully.) If, per chance, you find yourself in this failure condition, continue with the SQL Server Management Studio 2005 uninstall, rerun the SQL Server 2008 installer and reattempt the Management Tools installation.  This can be done by following the “New SQL Server stand-alone installation of add features to an existing installation” option under the Installation view.  This time around select the Management Tools when the available features are presented and you should be all set.  You may find the management tools take a surprisingly long time to install, but, believe me, this is nothing compared to the time you might waste if you didn’t know the right steps to follow in the first place.

* Relatively standard installation instructions which work consistently well for me:

• Run Setup.exe
• Installation > Select “New SQL Server stand-alone installation or add features to an existing installation.”
• Select the following Features:
  • Database Engine Services
  • Reporting Services
  • Client Tools Connectivity
  • Management Tools – Basic (If SSMS 2005 isn’t installed)
  • Management Tools – Complete (If SSMS 2005 isn’t installed)
• Select the Named Instance/Instance ID based on established naming convention. For example, BGHOMESQL01
• Set the following Service Startup Types:
  • SQL Server Agent – Automatic
  • SQL Server Database Engine – Automatic
  • SQL Server Reporting Services – Automatic
• Use the same account for all SQL Server Services and provide account. On local machine, I suggest using the Current User.
• Set the Authentication Mode:
  • Mixed Mode (SQL Server Authentication and Windows Authentication)
• Provide the built-in SQL Server admin account. On local machine, I suggest using the Current User.
• Configure Reporting Services:
  • Install the native mode default configuration