Setting Up SQL Server Management Studio with SQL Server Express 2008 Installed

One of the things I’ve been playing with lately is SQL Azure.  I’ll post about my experience using Windows Azure and SQL Azure together shortly, this post is all about setting up SQL Server Management Studio (SSMS) with SQL Server Express 2008.

In order to use SSMS with SQL Azure, you need to have the 2008 version of SSMS installed.  I had the 2005 version and it failed to connect.

The reason I’m posting about this is that I ran into a few non obvious things.  Now the thing is, I’m not that savvy with SQL Server and I’m sure that contributed to my confusion.  On the other hand, I figure other folks may be in my situation and could find this to be useful – if I can save a couple of people’s time, then I’m happy :)

From what I understand, because there are so many different SQL Server installs and because how you install SQL Server can also impact how you can add or even if you need to add SSMS, this set of instructions is far from complete. 

If my scenario below is different for you, I found a lot of good information in the comments of this article.  You just have to sort through them and figure out what all the options/dialogs everyone is talking about.

Back to my scenario -- I specifically had SQL Server Express installed through my Visual Studio installation and I wanted to add SSMS.

There are two ways I can get SSMS for SQL Server Express 2008, one is with a download  of SQL Server 2008 Express that includes SSMS, the other is through the standalone installer.

Because i already had SQL Server Express 2008 installed, I downloaded and ran the standalone installer.  Upon running, I received this dialog:

Which scared me into thinking that I didn’t have SP1 of SQL Server installed.  Turned out I had so I could safely ignore this dialog.  Key point here is not to let this dialog mislead you.

Upon hitting “Run”, I was brought to the SQL Server Installation Center:

Again I’ll remind you that I’m not much of a SQL Server guy, so I really had no idea what to do next.  I expected to just click next through a bunch of installer screens then go to my start menu and find SSMS – so what is this I see?

After some experimentation and trial and error (too much trial and error in fact), I found I needed to click the “Installation” link on the left then click the “New SQL Server stand-alone installation or add features to an existing installation” link.

Great, after hitting next through some setup files, a rule check and a EULA, I was brought to the following dialog:

Here I initially chose to “add features” – seems like that would make sense but it turned out to be a dead end. 

I went back to “Perform a new installation of SQL Server 2008” and that led me to the next screen which allowed me to select “Management Tools – Basic”.  This is the one you want to select. (it’s already selected in the screen shot below because SSMS was installed at the time of this screen shot).

After clicking through the remaining dialog, I was happy to see SSMS under my start menu, Microsoft SQL Server 2008 folder.

Now I can connect to SQL Azure through the Query editor (not the connect dialog that comes up when you start SSMS or from the object explorer).  Only a connection from the Query Editor is supported. (a later version of SSMS, for SQL Server 2008 R2 is coming that will support SQL Azure)

More to come on my SQL Azure + Windows Azure experience soon.

Hope this was helpful for someone.