Back in October I wrote a post on how to connect to SQL Azure. With the release of the November CTP of SQL Server 2008 R2 working with SQL Azure from within SQL Server Management Studio (SSMS) got a whole lot better!
You can get a free copy of SSMS here:
SQL Server 2008 R2 Tools for x86
SQL Server 2008 R2 Tools for x64
Because this is a CTP you may want to install these in a Virtual PC although I haven’t had any issues.
Once you have this installed all of the issues I outlined in my previous post about the headaches involved with connecting to SQL Azure are gone.
When you launch SSMS enter your credentials.
image
Replace MY_SERVER_NAME and MY_USER_NAME with valid credentials which you can setup and find on the SQL Azure portal.
Make sure you have added your IP address to the SQL Azure firewall or you will get the following error.
TITLE: Connect to Server
——————————
Cannot connect to ‘.database.windows.net.
——————————
ADDITIONAL INFORMATION:
Cannot open server ‘requested by the login. Client with IP address ‘’ is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
Login failed for user ‘’. (Microsoft SQL Server, Error: 40615)
The New Firewall Feature section of a previous post outlines configuring your firewall.
Assuming your credentials are correct you should see Object Explorer just like you would any other SQL Server database, which is awesome!
Once you start using the tool you will notice that things are not as integrated as they are for SQL Server. For example, let’s create a database.
When I Right Click on Database and select New Database… a new SQL script is created.
I can simply replace everything between <> and execute the script.
You’ll notice that once I refreshed the Object Explorer I see this new database. Additionally, you may notice that I specified that I wanted a 10GB database with the MAXSIZE = 10GB option. If you are just building a sample you should probably leave the MAXSIZE option off and it will default to 1GB.
To round out the example I’m going to install the Adventure Works SQL Azure database following the instructions that come with the sample.
Now when I look at Object Explorer you can see I have the full set of Tables, Stored Procs, etc.
One thing you may notice is that the Adventure Works installer creates new databases AdventureWorksDWAZ2008R2 and AdventureWorksLTAZ2008R2. Since SQL Azure is priced per database I’ve created a request on the CodePlex site to allow them to be installed into a target database. If you agree that it’s a good idea please vote for the item on CodePlex.
Hopefully you found this useful. Please let me know if you have any questions.
