SQL Azure - From Setup to Client Connection

This post simply introduces some basics for SQL Azure. It won't mention about Cloud or any theory, it simply walks you through how to initally setup SQL Azure Database and how to connect to the Azure database from SQL client.

A. HOW TO CREATE SQL AZURE INSTANCE
(1) Sign up Windows Azure Subscription and activate your subscription.
(2) Login with your account at Windows Azure Platform Management Portal
(3) In Windows Azure Platform Management Portal, click [Database] (See left-bottom)
(4) Click [Create a new SQL Azure Server].

(5) Select your Subscription. Click [Next]
(6) Select your Region

(7) Add SQL login (server-level principal). You cannot use well-known logins such as sa, admin, etc.

(8) Next you set up firewall rules. If you don't do this, you won't be able to connect to SQL Azure database.
You can add multiple IP addresses or IP ranges to allow SQL connection.

You can check [Allow other Windows Azure services...] check box if needed.

(9) Click [Finish]. Then data center will prepare your SQL Azure.
(10) After a few mins, the SQL Azure server will be added to your Subscription.



B. HOW TO CREATE SQL AZURE DATABASE

master database is created when SQL Azure instance is created. User database can be created just like on premise SQL database.

(1) In Windows Azure Platform Management Portal, Click [Create] button

(2) Type user database name and select database size.

Edition is kind of category for db sizing.

Web Edition can choose 1G or 5G db size. Business Edition has 10G, 20G, 30G, 40G, 50G.

NOTE: Another way of creating DB is to use TSQL after connecting to master db.

(3) For testing connectivity, select [Test Connectivity] on top menu in Windows Azure Platform Management Portal.

(4) To create database objects such as Table, View, Stored Procedure, you can use Web-based SQL management system called Database Manager. To use it, click [Manage] button right after [Test Connectivity]. Then you will see Database Manager as follows:

C. HOW TO CONNECT TO SQL AZURE

Using SSMS

Database Manager (Silverlight application) has some limitation in terms of database management. So you might want to use SSMS for more functionality. Connecting to SQL Azure is simliar to normal SQL connection except server name.

(1) Run SSMS.exe
(2) In Connection dialog, type FQDN (fully qualified domain name) in Server name. Select SQL Authentication and type SQL login and password.

(3) If you didn't add your local IP address to Firewall Rule correctly during SQL Azure setup (A. 8th step),  you will see the following error message. 

(4) Add your IP address (ex: xx.xx.74.38) to Firewall Rule.

(5) Once new Firewall Rule is defined, run SSMS and try to connect again. Now if you are using SQL Server 2008, you will see the following error: Invalid object name 'sys.configurations'

This occurs because SSMS Object Explorer is using sys.configurations table but SQL Azure does not have this table. SQL Server 2008 R2 solved this problem, so if you want to use Object Explorer for SQL Azure, you have to upgrade SQL 2008 to SQL 2008 R2.

As a workaround in SQL 2008, you can connect to SQL Azure server by using Query Editor in SSMS. The steps for this workaround are: 

• Run SSMS
• Cancel Connection dialog (since typically this is for Object Explorer connection but can be changed in Tools->Options)
• Click [New Query] which will bring up Connection dialog again.
• Connect with your SQL login info.
• Use TSQL statement in Query Editor

However, even with this workaround, you will still lose full UI functionality that are provided from Object Explorer. So using SQL 2008 R2 or later version will be a better option.

Using SQLCMD (or OSQL)

Unlike SSMS, SQLCMD requires different SQL login name. If you use the same SQL login name as SSMS, you will get the following error.OSQL is basically using the same connection method as SQLCMD.

C:\>sqlcmd -S l0gncoxz3l.database.windows.net -U myadmin -P xxxxxx
Msg 40531, Level 11, State 1, Server l0gncoxz3l.database.windows.net, Line 1
Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net
 libraries do not send the server name, in which case the server name must be included as part of the user name (username@server
In addition, if both formats are used, the server names must match.

So the -U syntax should be like <loginname>@<servername> as you can see below:

C:\>sqlcmd -S l0gncoxz3l.database.windows.net -U myadmin@l0gncoxz3l -P xxxxxx

1> exit


Application Connection Strings

Below are some examples for application connection strings.

ADO.NET
Server=tcp:l0gncoxz3l.database.windows.net,1433;Database=TestDB;User ID=tom@l0gncoxz3l;Password=xxxxxxxx;Trusted_Connection=False;Encrypt=True;

ODBC

Driver={SQL Server Native Client 10.0};Server=tcp:l0gncoxz3l.database.windows.net,1433;
Database=TestDB;Uid=tom@l0gncoxz3l;Pwd=xxxxxxxx;Encrypt=yes;

PHP

$connectionInfo = array("UID" => "tom@l0gncoxz3l", "pwd" => "xxxxxxxx", "Database" => "TestDB");
$serverName = "tcp:l0gncoxz3l.database.windows.net,1433";
$conn = sqlsrv_connect($serverName, $connectionInfo);