Connecting to SQL Azure with SQL Server 2008 R2 Tools

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:

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 ‘<MY_SERVER_NAME>.database.windows.net.

——————————
ADDITIONAL INFORMATION:

Cannot open server ‘<MY_SERVER_NAME>requested by the login. Client with IP address ‘<MY_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 ‘<MY_USERNAME>’. (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!

image

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.

image

I can simply replace everything between <> and execute the script.

image

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.

image

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.

More Information

7 thoughts on “Connecting to SQL Azure with SQL Server 2008 R2 Tools

  1. Pingback: DotNetShoutout

  2. Pingback: sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,truncate s

  3. Excellent post. I used to be checking continuously this blog and I am impressed! Extremely useful information particularly the last part 🙂 I care for such information much. I used to be looking for this certain information for a very lengthy time. Thanks and best of luck.

  4. By far the most concise and up to date information I have found on this topic. I am glad that I navigated to your page. I’ll be now subscribing to your feed so that I can get the latest updates. Appreciate all the information here.

  5. Nice article Zach.

    Just in case may be you overlooked this. Though you have blacked the Azure server name in every image for security reasons, but the last image has the watermark of the server name “f8q6bc29zv”.

Leave a Reply

Your email address will not be published. Required fields are marked *