Purpose

There are some excellent instructions for working with SQL Azure on the web. This is not intended to replace those, but rather to augment and consolidate instructions for connecting to and working with SQL Azure for those who are not proficient with SQL Server.

Resources on the Web

How to: Migrate a Database by Using the Generate Scripts Wizard (SQL Azure Database)

Connecting to SQL Azure

Prerequisites

In order to do this you will need to meet the following requirements

• Connect from an authorized IP address to comply with IPSec rules on the Azure SQL database
• Utilize SQL Server Management Studio r2 (You can get this by installing SQL Express if you don't want to load full SQL Server)

Steps to Connecting

1. Open SQL Server Management Studio.
2. When asked to connect, click [Cancel]. This will open SSMS with no data or connections.



3. In the upper left side on the toolbar press [New Query].



4. A new connect window will appear. Use the provided login information. Remember to set “Authentication” to “SQL Server Authentication”.



5. Press [Options >>] to provide extra connection information.
6. On the “Connection Properties” tab type in the name of the database you wish to connect to. Please note that even though the “Connect to database” field is a dropdown you will need to manually type in this name as management studio will not be able to gather a list of database names for you, and you will not be able to change this name once connected. If you are running more than one database (For example [dev] and [prod]), you will want to make sure you are connecting to the proper database during this step.



7. Press the [Connect] button.
8. You will now be able to run sql commands directly against the SQL Azure db.

NOTE: In this version of SQL Server Management Studio this is the only way to connect to the database. You cannot connect using the object explorer as it requires access to both your database and the “Master” database. Due to the way SQL Azure is implemented this is not possible and you will receive an error. Future versions of SSMS and/or updates to SQL Azure may correct this.

TIP

One of the downsides here is the lack of the object explorer. If you have a local copy of the database, you can do this little trick to help overcome this deficit.

After you have connected to SQL Azure as described above, you can connect the object explorer to your local database. As long as you spawn new query windows from your existing Azure connection, the new query windows will inherit that connection. This will allow you to view the objects in the object browser and even drag and drop table and column names over to your query window.

While this is still far from perfect, it does ease the pain.