How to Connect to SQL Azure through SQL Server Management Studio 2008 (SSMS 2008)

Most developers on the Microsoft stack that use SQL Server, and who are migrating their databases to SQL Azure, likely work in SQL Server Management Studio 2008 (SSMS 2008), Visual Studio, or a mixture of both, for DBA and data management tasks. Fortunately, SQL Azure has two options for those who need to administer, create, maintain, and develop using SQL Azure: The SQL Azure Management Portal and SSMS 2008.

Before you start — If you want to try out the online tools or see take SQL Azure for a spin with SSMS 2008, sign up for a 90 day free trial.

Connect to SQL Azure from SSMS 2008

You can use SQL Server Management Studio (including, and especially, SQL Express) to connect to SQL Azure, but you first need to have some information handy that you can find in the SQL Azure Online Management Portal. Once there, you can view your subscription information including the information you need to connect to a SQL Azure server or database.

image3

Here is the information you need:

  1. The fully qualified server name. See the blocked out, red, parts of above image, as to where you can locate your server info.
  2. Valid credentials that you have already setup via the online SQL Azure Management Portal (of course, the password is not available for viewing, as it should be memorized anyway)

Enter this information into the SQL Server 2008 Connect to Server dialog, and click the Connect button to authenticate. Don’t forget you must choose SQL Server Authentication before you may enter credentials.

image16

Upon successful authentication, SQL Server Management Studio opens. This is the exact same SSMS you are familiar with, with the only difference being that you have connected to SQL Azure instead of a SQL Server on your LAN.

SNAGHTMLa64d4e1

From here you can run queries, manage tables, and do all the SQL administrative tasks you need to. Note there is a SQL Azure Database node in the Template Explorer that you can access from the View menu. Of course if you are using SQL Azure you’ll want the SQL Azure SDK for Visual Studio 2010.

Connect to SQL Azure from Visual Studio

The same credentials and authentication happen in both tools, SSMS and Visual Studio. This means all that you need to do is open the SQL Server Object explorer and connect exactly as you would any SQL Server in your LAN. Once connected, you can enjoy administering and working with SQL Azure inside of Visual Studio.

SNAGHTMLcf0cc28_1

Troubleshooting SQL Azure Connectivity

The Firewall check failed error is very common, as you need to enter an IP Address range to connect to SQL Azure from various client programs (i.e., SSMS). Here’s the error text:

Firewall check failed. Cannot open server ‘SERVERNAME’ requested by the login. Client with IP address XXX.XXX.XXX.XXX 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.

If you get this error you can reset it from the Windows Azure Mgmt Portal. Just navigate to the server you need access to, then add in the IP Range, as shown in the image below:

image_4

Alternatively, you can use SQLCMD in the Windows command prompt which will look something like this:

C:\>SQLCMD –U<user>@<server> -P<password> -S<server>.database.windows.net

exec sp_set_firewall_rule N’Allow Windows Azure’,’0.0.0.0′,’0.0.0.0′

SQLCMD Azure commands: http://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx

NOTE: Only the server-level principal login (this is the primary/master login that you use to connect to the Windows Azure Portal online, a Windows Live Id), while connected to the master database, can configure firewall settings for your SQL Azure server. Also, check out the SQL Azure troubleshooting for other common errors, troubleshooting, and help.

Use Windows Azure Mobile Services to power Windows Store and Windows Phone apps

New to the Azure family, Windows Azure Mobile Services is that all-in-one place for everything backend – data storage, authentication, push notifications, jobs. WAMs provides the tools to build and target cross platform apps for the Windows Store, Windows Phone, iOS, Android, and even plain old web sites.

Why Azure Mobile Services?

Here’s what you get upon creating your WAMS service:

  • Underlying SQL database, but you can also do NoSQL (BLOB storage, etc..)
  • A REST API, along with managed and JavaScript client libraries.
  • Administration via the Azure portal, command line, or SQL Management Studio
  • Server side logic via JavaScript or SQL
  • Push notifications

and…

A bunch of big-name authentication providers:

  • Microsoft acct (aka Windows Live)
  • Twitter
  • Facebook
  • Google acct

WAMS contains basically everything necessary to power an app or web site quickly. It doesn’t matter what you’re developing on the front end, it’s great to have a single point for all things backend.

Connect to Azure Mobile services

Once you have installed the Azure Mobile Services SDK for Visual Studio and setup and configured your mobile service at the Windows Azure Portal, you can download complete project templates or just blocks of the required code to connect and access WAMS on any platform. This is the same portal for all of Azure’s other services. 

Find your URL and key in the Azure portal by selecting the cloud icon next to the Dashboard option (see image below). This page allows you to choose your platform and download complete Visual Studio 2012 projects for Windows Store or Windows Phone in the language of your choice. There are also templates for iOS and Android available at the Azure portal. If you are integrating WAMS into an existing project there are code snippets for the MobileServicesClient (more on this below) you can copy and paste. The below image shows the key highlights:

 image_6

  1. Quick Start menu option.
  2. Choose your poison.
  3. Get the code.

The downloadable projects from the Azure portal contain the appropriate client lib references and code to connect to WAMS, depending on the language. For C#/VB you need a reference to the “Windows Azure Mobile Services Managed Client” extension. For JavaScript, reference the “Windows Azure Mobile Services Javascript Client” extension, as well as a script reference to MobileServices.js in the default.html file. Both dependencies ship with the Azure Mobile Services SDK.

In WAMS projects, you must create an instance of a MobileServiceClient that acts as a proxy to the Azure service, somewhat like a connection that manages itself.

C#

public static MobileServiceClient MobileService = new MobileServiceClient(
    "https://your-site.azure-mobile.net/", "your-key-here"
);

and Javascript

var client = new Microsoft.WindowsAzure.MobileServices.MobileServiceClient(
    "https://your-site.azure-mobile.net/", "your-key-here"
);

Your site URL and key, both MobileServiceClient constructor arguments are available at the Quick Start page (above image) in the Azure portal. Now that you have a proxy to a mobile service, you can perform all CRUD operations as well as some DDL code locally on the client by populating a IMobileServiceTable<T> with a call to MobileService.GetTable.  Once you do that you can then perform data binding and other CRUD operations.

Work with data in Windows Azure Mobile Services

In C#  you must use a class to represent the data item, while in JavaScript you do not. For example, the below code shows populating & data binding to TextBox inside a XAML ListView control.

<ListView Name="ListItems" Margin="62,10,0,0" Grid.Row="1">
    <ListView.ItemTemplate>
        <DataTemplate>
            <StackPanel Orientation="Horizontal">
                <TextBox Name="NameTextBox" Text="{Binding Name}" />
            </StackPanel>
        </DataTemplate>
    </ListView.ItemTemplate>
</ListView>

The C# code, below.

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Birthday { get; set; }
}

private IMobileServiceTable<Person> peopleTable = App.MobileService.GetTable<Person>();
private void RefreshTodoItems()
{
    var results = await peopleTable.ToListAsync();
    persons = new ObservableCollection<Person>(results);
    ListItems.ItemsSource = persons;
}

Additions and updates (but not deletions) to properties of the Person class in client code affect the underlying SQL database. This means that if you add another property to the Person class, such as a FavoriteColor property, the WAMS libraries will automatically infer the type and create the underlying SQL entities. Of course not everything can be done in client code so there are server side alternatives (see below, under Manage WAMS Data Stores)

Below is the equivalent code in Javascript, starting with HTML for data binding.

<div id="TemplateItem" data-win-control="WinJS.Binding.Template">
    <div style="display: -ms-grid; -ms-grid-columns: auto 1fr">
        <input class="itemCheckbox" type="checkbox" data-win-bind="checked: complete; dataContext: this" />
        <input type="text" data-win-bind="text: Name" />
    </div>
</div>
<div id="listControl" style="-ms-grid-row: 2; margin: 20px 0px 0px 0px; -ms-grid-row-align: stretch"
    data-win-control="WinJS.UI.ListView"
    data-win-options="{ itemTemplate: TemplateItem, layout: {type: WinJS.UI.ListLayout} }">
</div>
var peopleTable = client.getTable('People');
var peopleList = new WinJS.Binding.List();
var refreshData = function () {
    peopleTable.read()
        .done(function (results) {
            peopleList = new WinJS.Binding.List(results);
            listControl.winControl.itemDataSource = peopleList.dataSource;
        });
};

As you can see, you do not need to setup a class since Javascript is not strongly typed but WAMS will still create objects and SQL mappings as needed. However, you can use something like TypeScript to create classes to be more like classes in C#. Either way, once you have a reference to the MobileServiceTable, all required functionality is accessible through that object. For example, insertions are just a simple call from the MobileServiceTable object:

C#

private async void InsertTodoItem(Person person)
{
    await peopleTable.InsertAsync(person);
}

Javascript

var insertPerson = function (person) {
    peopleTable.insert(person).done(function (item) {
        peopleList.push(person);  // push items onto List object for binding
    });
};

The other CRUD methods are just as easy and also belong to the MobileServiceTable object. Notice that C# uses the await/async keywords and JS uses the done function to implement asynchronous operations (async is 1st class citizenry in Windows Store & Phone apps).

While the Javascript code will not run on Windows Phone, the C# code does and is easily ported between Windows Store and Windows Phone. You can download templates at the Azure portal specifically for Windows Phone (iOS and Android too).

Of course you need to also manage the data on the backend, regardless of the type or number of front end clients.

Manage WAMS data stores

While there are benefits to automatic creation, mapping, and type inference between client objects and WAMS databases, you can perform any sufficiently advanced db management using Javascript on the server such asvalidation, constraints, etc…. Here’s an example of server side validation in WAMS:

function insert(item, user, request) {
    if (item.text.length > 10) {
        request.respond(statusCodes.BAD_REQUEST, 'Text length must be under 10');
    } else {
        request.execute();
    }
}

This is straight-up Javascript. You can run it at the portal or upload the .js files directly to WAMS by using the WAMS command line tools:

azure mobile script upload <service-name> table/<table-name>.<operation>.js

azure mobile script upload NotablePeople people/people.insert.js

This makes it easy to incorporate scripts into source control, and using Javascript on the backend makes it easy to develop cross platform solutions.  

Summary

WAMS is a backend to everything, and is for the most part a turn-key operation, and using WAMS is really as simple as setting up the WAMS service, referencing the proper Visual Studio client libs, and coding against a small and clear set of APIs. Despite all this goodness, WAMS is still a young technology so it does need some polish. I’d like to see server side JS debugging and more SQLesque type utilities, as well as additional data types like binary/BLOB support (which I am told is coming soon, yay!).