Microsoft SQL Azure Tools


Microsoft SQL Azure Enterprise Application Development

Microsoft SQL Azure Enterprise Application Development

Build enterprise-ready applications and projects with SQL Azure

  • Develop large scale enterprise applications using Microsoft SQL Azure
  • Understand how to use the various third party programs such as DB Artisan, RedGate, ToadSoft etc developed for SQL Azure
  • Master the exhaustive Data migration and Data Synchronization aspects of SQL Azure.
  • Includes SQL Azure projects in incubation and more recent developments including all 2010 updates


        Read more about this book      

(For more resources on Microsoft Azure, see here.)

SQL Azure is a subset of SQL Server 2008 R2 and, as such, the tools that are used with SQL Server can also be used with SQL Azure, albeit with only some of the features supported. The Microsoft tools can also be divided further between those that are accessed from the Visual Studio series of products and those that are not. However, it appears that with Visual Studio 2010, the SSMS may be largely redundant for most commonly used tasks.

Visual Studio related

From the very beginning, Visual Studio supported developing data-centric applications working together with the existing version of SQL Server, as well as MS Access databases, and databases from third parties. The various client APIs such as ODBC, OLEDB, and ADO.NET made this happen, not only for direct access to manipulate data on the server, but also for supporting web-facing applications to interact with the databases.

There are two versions, of particular interest to SQL Azure, that are specially highlighted as they allow for creating applications consuming data from the SQL Azure Server. Visual Studio 2008 SP1 and Visual Studio 2010 RC were released (April 12, 2010) recently for production. The new key features of the more recent update to SQL Azure are here: It may be noted that the SQL Azure portal provides the all-important connection strings that is crucial for connecting to SQL Azure using Visual Studio.


Although you can access and work with SQL Azure using Visual Studio 2008, it does not support establishing a data connection to SQL Azure using the graphic user interface, like you can with an on-site application. This has been remedied in Visual Studio 2010.


Visual Studio 2010 has a tighter integration with many more features than Visual Studio 2008 SP1. It is earmarked to make the cloud offering more attractive. Of particular interest to SQL Azure is the support it offers in making a connection to SQL Azure through its interactive graphic user interface and the recently introduced feature supporting Data-tier applications. A summary detail of the data tier applications are here:

SQLBulkCopy for Data Transfer

In .NET 2.0, the SQLBulkCopy class in the System.Data.SqlClient namespace was introduced. This class makes it easy to move data from one server to another using Visual Studio. An example is described in the next chapter using Visual Studio 2010 RC, but a similar method can be adopted in Visual Studio 2008.SQL Server Business Development Studio (BIDS).

The Business Development Studio (BIDS) would fall under both SQL Server 2008 and Visual Studio. The tight integration of Visual Studio with SQL Server was instrumental in the development of BIDS. Starting off to a successful introduction in Visual Studio 2005 more enhancements were added in Visual Studio 2008, both to the Integration Services as well as Reporting Services, two of the main features of BIDS. BIDS is available as a part of the Visual Studio shell when you install the recommended version of SQL Server. Even if you do not have Visual Studio installed, you would get a part of Visual Studio that is needed for developing business intelligence-related integration services as well as reporting services applications.

SQL Server Integration Services

Microsoft SQL Server Integration Services (SSIS) is a comprehensive data integration service that superseded the Data Transformation Services. Through its connection managers it can establish connections to a variety of data sources that includes SQL Azure. Many of the data intensive tasks from onsite to SQL Azure can be carried out in SSIS.

SQL Server Reporting Services

SQL Server Reporting Services (SSRS) is a comprehensive reporting package that consists of a Report Server tightly integrated with SQL Server 2008 R2 and a webbased frontend, client software - the Report Manager. SSRS can spin-off reports from data stored on SQL Azure through its powerful data binding interface.

Entity Framework Provider

Like ODBC, OLE DB, and ADO.NET data providers Entity Framework also features an Entity Framework Provider although, it does not connect to SQL Azure like the others. Using Entity Framework Provider you can create data services for a SQL Azure database. .NET client applications can access these services. In order to work with Entity Framework Provider you need to install Windows Azure SDK (there are several versions of these), which provides appropriate templates.

Presently, the Entity Framework Provider cannot create the needed files for a database on SQL Azure. A workaround is adopted.

SQL Server related

The tools described in this section can directly access SQL Server 2008 R2. The Export/ Import Wizard cannot only access SQL Servers but also products from other vendors to which a connection can be established. Scripting support, BCP, and SSRS are all effective when the database server is installed and are part of the SQL Server 2008 R2 installation. SQL Server Integration Services is tightly integrated with SQL Server 2008 R2, which can store packages created using SSIS. Data access technologies and self-service business integration technologies are developing rapidly and will impact on cloud-based applications including solutions using SQL Azure.

SQL Server Management Studio

SQL Server Management Studio (SSMS) has been the work horse with the SQL Servers from the very beginning. SSMS also supports working with SQL Azure Server. SQL Server 2008 did not fully support SQL Azure, except it did enable connection to SQL Azure. This was improved in SQL Server 2008 R2 November-CTP and the versions to appear later.

SSMS also provides the SQL Azure template, which includes most of the commands you will be using in SQL Azure.

Import/Export Wizard

The Import/Export Wizard has been present in SQL Servers even from earlier versions to create DTS packages of a simple nature. It could be started from the command line using DTSWiz.exe or DTSWizard.exe. In the same folder, you can access all dts-related files. You can double-click Import and Export Data (32-bit) in Start | All Programs | Microsoft SQL Server 2008 R2.

You may also run the DTSWizard.exe from a DOS prompt to launch the wizard.

The Import/Export wizard may be able to connect to SQL Azure using any of the following:

  • SQL Server Native Client 10.0
  • .NET Data Source Provider for SqlServer

The Import/Export wizard can connect to the SQL Azure server using ODBC DSN. Although connection was possible, the export or import was not possible in the CTP version due to some unsupported stored procedure.

Import/Export works with the .NET Framework Data Provider, but requires some tweaking.


        Read more about this book      

(For more resources on Microsoft Azure, see here.)

SyncFramework and SQL Azure

The ability to synchronize data between on-site data, data on SQL Azure, and data on handheld devices is an important consideration. The Microsoft Sync Framework and the power pack designed for SQL Azure supports this capability: (

The following new components in the SyncFramework PowerPack for SQL Azure November CTP are designed to improve the user experience in synchronizing on-site data with SQL Azure:

  • SqlAzureSyncProvider
  • SQL Azure Offline Visual Studio Plug-in
  • SQL Azure Data Sync Tool for SQL Server
  • New SQL Azure Components
  • Automated Provisioning

These components and the runtime components simplify synchronizing with the cloud while optimizing performance. The power pack also has a Visual Studio plugin that demonstrates offline capabilities to synchronize with a local SQL Server Compact.

The SyncFramework PowerPack for SQL Azure November CTP can be downloaded here:

In addition to the previous power pack you also need the Microsoft Sync Framework 2.0 SDK. You may download this software from here:

While the power pack provides elements specific to SQL Azure, the Sync Framework 2.0 SDK is a comprehensive platform that provides support for collaboration and offline scenarios for applications, services, and devices.

MySQL to SQL Azure Migration

The fact that MySQL and PHP are inseparable twins, and that MySQL has a large following, has persuaded Microsoft to provide support for both MySQL and PHP in its cloud offerings.

Microsoft SQL Server Migration Assistant 2008 for MySQL v1.0 CTP1 (SSMA 2008 for MySQL, 4.0 to 8 MB), which may be downloaded from here: ( provides a toolkit to effortlessly migrate MySQL databases to SQL Azure. In this way, it provides a bridge for those who want to move their MySQL-based businesses to the cloud. The program can migrate, both schema and data.

SSMA 2008 for MySQL v1.0 CTP1 is designed to work with MySQL 4.1, 5.0, and 5.1, and all editions of SQL Server 2008 or SQL Azure.

The requirements are:

  • Microsoft Windows Installer 3.1 or a later version.
  • The Microsoft .NET Framework version 2.0 or a later version.
  • The .NET Framework version 2.0 is available on the SQL Server 2008 product media. You can also obtain it from the .NET Framework Developer Center.
  • MySQL Connector/ODBC v5.1.
  • Access to and sufficient permissions on the computer that hosts the target instance of SQL Server 2008 or SQL Azure database.
  • 1 GB RAM.

Scripting support for SQL Azure

In addition to supporting T-SQL Commands through the SQL Server Object Explorer you can also do scripting, which allows you to write scripts against not only SQL Azure databases but also several other versions of SQL Server such as 2005 and 2008.

In order to create a script that you can run on SQL Azure, you will need to invoke the scripting option for the SQL Azure Database Engine and prepare the script for an object on the SQL Server 2008 R2. These can be configured in SSMS by going through Tools | Options in the main menu to open the Options window, as shown in the following image:

Microsoft SQL Azure Enterprise Application Development

In Options, click open the menu item SQL Server 'Object Explorer | Scripting as shown in the following screenshot. Click on the script for database engine type and choose SQL Azure Database:

Microsoft SQL Azure Enterprise Application Development

Click on Script for server version and from the drop-down list choose SQL Server 2008 R2 as shown in the following screenshot:

Microsoft SQL Azure Enterprise Application Development


SQLCMD is a command-line utility that is shipped with the SQL Server. With SQLCMD you can carry out the following:

  • Connect to SQL Azure database
  • Enter and run T-SQL Statements
  • Run script files and System procedures

The following are the two ways you can run SQLCMD:

  • From a DOS prompt
  • From SSMS

SQLCMD can be run from a DOS prompt or in the Query menu item SQLCMD Mode by choosing to create a New Query in SSMS as shown later in this section.

SQLCMD utility has many options and you can get help on using these options by executing the following statement from the command line C:\sqlcmd /? as shown in the following screenshot:

Microsoft SQL Azure Enterprise Application Development

Connect to SQL Azure from the DOS window

You must be running the computer from the same location for which you have set up the firewall rules. In this case, it is assumed that the Service Principal is connecting to the SQL Azure database Bluesky. The server name after the -S flag should be and the user name after the -U flag should be &ltusername>@&ltservername>. The command-line options are case sensitive.

Follow the indicated steps to run SQLCMD against the SQL Azure database. Make sure you use SQL Azure-related items specific to your provisioning:

  1. Bring up the DOS screen and enter the following at C:\ prompt:

    C:\Users\jay>sqlcmd -S tcp: -U
    -P <Password> -d Bluesky

  2. The command prompt changes form C:\Users\jay> to 1>****.
  3. Try to query a table that does not exist using the select command.
  4. The next command shows what happens when you query for a non-existent object.

    1> Select * from MyTable
    3> Go

    Msg 208, Level 16, State 1, Server XXXXXXXX, Line 1
    Invalid object name 'MyTable'.

    Querying the table MyHobbies
    Querying a table that is known to exist in the database.
    1> Select * from MyHobbies
    2> GO

    ID Name Hobby Date
    ----------- ------------------------------------------------------
    -------------------------------- ---------------------------------
    --- ----------------
    (0 rows affected)

Run queries using SQLCMD in SSMS

As previously mentioned, we can connect to a SQL Azure database and run queries in the query window.

  1. Connect to SQL Azure in SSMS.
  2. Click New Query in the main menu.
  3. The Query menu item gets enabled.
  4. Click Query and from the drop-down list choose the menu item SQLCMD Mode as shown in the next screenshot:

    Microsoft SQL Azure Enterprise Application Development

    By default this is not turned on.

  5. Click Tools | Options in the main menu. The Options window is displayed as shown:

    Microsoft SQL Azure Enterprise Application Development

  6. Choose By default, open new queries in SQLCMD mode and click OK.
  7. Create new query in Bluesky.
  8. Enter the following code in the query window:

    :setvar tablename MyHobbies
    select * from $(tablename)

  9. Notice that the first line gets a grey background. Click Execute in the query window.

The result of the query output together with the query and the objects in the Bluesky database are shown in the following screenshot:

Microsoft SQL Azure Enterprise Application Development


        Read more about this book      

(For more resources on Microsoft Azure, see here.)

BCP Utility

BCP is a bulk copying utility copying data between SQL Server instances and a data file in a user-specified format. BCP utility can be used with SQL Azure. BCP is a veteran Export/Import utility known for its ease; capable of moving large amounts of data and speed. These features testify to its survival for so long.

It is used for copying a large set of rows out of tables to data files. It is not T-SQL dependent, except when used with the Query option. However, Queryout option, which would allow T-SQL, is not presently supported in SQL Azure. You need to understand file formats and table structures to work with BCP. For more details on BCP follow this link:

To create a backup of data you must first create a file to record the data format.

The syntax of BCP utility (from MSDN) is as follows:

bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-x] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-t field_term]
[-r row_term] [-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]

For details of the various options please refer to the previous link to BCP on MSDN. The DOS help screen seems to have been created during SQL Server 2005 (90) and differs from the MSDN documentation.

BCP utility can be used with SQL Servers from version 7.0 (70) to 2008 (100) and if there is an older version of the database on the computer, you need to know which version is being used. You can find the BCP version using the following command in the DOS screen:

C:\Users\jay>bcp /v
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 10.50.1352.12

You can get oriented with calling this utility by following these steps:

  1. Create the table MyTable in the Bluesky database and populate the table by executing the following statements in SSMS:

    CREATE TABLE MyTable (
    FirstName varchar (20)
    INSERT INTO MyTable VALUES('1','John')
    INSERT INTO MyTable VALUES('2','Mary')
    INSERT INTO MyTable VALUES('3','Kristine')

  2. Execute a SELECT Statement to verify the returned data.
  3. You should see 3 rows returned, each having two columns.
  4. Bring up the DOS screen by calling the command cmd from Run.
  5. Now you will be running the BCP utility. This is an interactive utility where in, you need to have knowledge of the fields in your table; their data types, their field lengths, etc. as you will be asked to answer the questions in the interactive session.
  6. Type-in the following at the command prompt:

    C:\Users\jay>bcp Bluesky.dbo.MyTable out C:\Users\jay\testAzure.
    dat -S tcp:<Your> -U
    <username>@< Your SQL_Azure_Server_Name > -P <Your Password>

  7. Hit return.
  8. The Interactive session starts as shown here, you will then need to type-in your answers (shown in red) after the prompt:

    Enter the file storage type of field ID [int]: char
    Enter prefix-length of field ID [1]: 1
    Enter field terminator [none]: ,
    Enter the file storage type of field FirstName [char]: char
    Enter prefix-length of field FirstName [2]: 4
    Enter field terminator [none]:
    Do you want to save this format information in a file? [Y/n] Y
    Host filename [bcp.fmt]: bcpAzure.fmt

  9. After entering bcpAzure.fmt hit return.
  10. You get the following displayed in your DOS screen:

    Starting copy...
    3 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total : 125 Average : (24.00 rows per

  11. You will find two files testAzure.dat and bcpAzure.fmt in the directory you specified in the bcp utility previously. Both are text files that you can open with Notepad or WordPad.

    The next table shows the bcpAzure.fmt from the previous run. The table as well as the text in bold was added to the output (output is in a tab separated text format) to help in understanding the items:

    10.0 BCP Version
    2 No of fields
    Data Field Position Data Type Prefix Data file field length Row or field terminator Column position Column Name Column Collation
    1 SQLChar 1 12 "," 1 ID ""
    2 SQLChar 4 20 "" 2 FirstName SQL_Latin1-General_CP1_As

  12. The testAzure.dat has the following content:

    1, John2, Mary3,Kristine

The characters not in ASCII arise out of exporting data in Unicode representation: (

This can be improved by using the –w option as in:

C:\Users\jay>bcp Bluesky.dbo.MyTable out C:\Users\jay\testAzure.dat -S
tcp:<Your> -U <username>@
< Your SQL_Azure_Server_Name > -P <Your Password> -w

In this case, there are no interactive sessions and you will get the output file right away.

The output of a BCP utility depends strongly on how the questions are answered in the interactive session. For additional information on the output format follow this link here:

Only a very simple description of the usage of BCP is presented, for details please refer to the link here:

IIS7 Database Manager

Database Manager 1.0 for both x32 and x64 is an IIS 7.0 extension that supports managing SQL Servers from IIS 7 Manager. It may be downloaded from this location:

IIS 7 Database Manager can automatically discover SQL Servers (it did not in the present installation)—both local and remote—and make them available in the IIS 7 Management Console. From the IIS 7 console you can manage all server objects such as tables, views, stored procedures, and so on.

In addition to SQL Server IIS, Database Manager also provides support for MySQL, the popular database product recently acquired by Oracle. This is a nice feature because IIS 7's Database Manager connects to SQL Azure as well as MySQL, and the SQL Azure server can get exported data from MySQL through the recent introduction of SSMA.

Herein, few of the details of practically using the Database Manager are provided as it lends itself to be a nice tool, especially to those operating systems where IIS 7.0 is available by default such as Windows 7.0.

Some of the advertised features are:

  • Manage Microsoft SQL Server or MySQL databases
  • Add, rename, drop, and edit tables
  • View and manage primary keys, indexes, and foreign keys
  • Edit data
  • Establish connections to multiple databases
  • Create and execute queries
  • Create, alter, and delete stored procedures and views
  • Manage both local and remote databases from your machine
  • Backup and restore Microsoft SQL Server databases
  • IIS 7 Manager provides remote management capabilities with a clean firewall-friendly option for managing a remote SQL Server
  • Exposes a public extensibility platform that enables the development of providers to support other databases
  • Compatible with SQL 2008/2005 and MySQL

To get some working experience with this tool, follow the indicated steps. These steps will help you in connecting to SQL Azure and run SQL queries on SQL Azure:

  1. Start | Search programs and files. Enter InetMgr. Double-click InetMgr in the pop-up search list.
  2. Internet Information Services (IIS) Manager is displayed as in the following screenshot:

    (Move the mouse over the image to enlarge.)

  3. Click the Connections.
  4. Under the Management Section you will find the Database Manager, as shown in the next screenshot.


    Database Manager will not be available if you did not install the extension from the website mentioned earlier. From the download site you must install the iisdbmanager_x86_en-US.msiinstaller package 595KB.


    Microsoft SQL Azure Enterprise Application Development

  5. Double–click the Database Manager Icon to open the items as shown.
  6. The Database Manager will open to reveal a single connection to an existing LocalSqlServer.

    Microsoft SQL Azure Enterprise Application Development

  7. Double-click the LocalSqlServer. It may open to reveal the existing databases.
  8. As it happened, it may display an error (although it is supposed to discover all servers, it did not).
  9. Right-click on the icon above it (if you hover above it you can see it is an Add Connection link).
  10. The Add Connection window will open as in the following screenshot:

    Microsoft SQL Azure Enterprise Application Development

  11. Enter the credentials for the SQL Azure Server database Bluesky with the appropriate values after providing a name of your choice (herein, SqlAzure01)@:

    Database: Bluesky
    User ID: &ltUsername>
    Password: &ltPassword>

  12. Click OK.
  13. The SqlAzure01 connection gets added as shown (it is shown with the node fully expanded). You should observe that the columns are not displayed like they are in SSMS:

    Microsoft SQL Azure Enterprise Application Development

  14. The five toolbar icons from left to right above LocalSqlServer are Add Connection, New Query, Open Table Definition, Show Table Data, and Delete.
  15. In order to see the data in the MyTable, query the table as in the next step.
  16. Click MyTable and click Show Table Data icon.
  17. The MyTable data is displayed as shown in the next screenshot:

    Microsoft SQL Azure Enterprise Application Development

  18. You can delete a row if you want to by highlighting a row and clicking the delete button in the pane. In addition, you can run a query using the New Query icon.
  19. Click New Query.
  20. The SqlQuery_1 is displayed.
  21. Enter the following query and click Execute:

    Select * from MyTable for XML auto

  22. The query result appears in the bottom pane as shown:

    Microsoft SQL Azure Enterprise Application Development

The Database Manager is another useful tool in working with SQL Azure. Presumably, it can deliver most, if not all, of the items when used with SQL Azure. The connection you created can be stored in IIS by going to File | Save Connection, so that it can be readily used the next time a connection is to be made.

OData and SQL Azure

OData uses well–known, existing technologies HTTP, Atom Publishing protocol, and JSON to provide access to information from a variety of services and stores of data including relational store in SQL Azure. The OData ( Portal and Service allows publishing SQL Azure databases as OData services using simple configuration. This way, the SQL Azure data can be URL-accessed using REST-based APIs.

Brand new tools

Microsoft has since added a few more tools as well as web-based tools, which appear to be very attractive to work with SQL Azure. The following are some of the tools and links to them:


In this article, we looked at Microsoft tools that can be used with SQL Azure. Microsoft's tight integration of SQL Server 2008 R2 with two versions of Visual Basic as well as its Management Studio-related tools such as SSMS, Import/Export, and Data; SyncFramework; SSMS; scripting support for SQL Azure; SQLCMD; BCP Utility; IIS7 Database Manager and ODATA service are described, some of them with examples.

Further resources on this subject:

You've been reading an excerpt of:

Microsoft SQL Azure Enterprise Application Development

Explore Title