Friday, July 22, 2011

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.

(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.


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:


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 -U myadmin -P xxxxxx
Msg 40531, Level 11, State 1, Server, Line 1
Server name cannot be determined.  It must appear as the first segment of the server's dns name (
 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 -U myadmin@l0gncoxz3l -P xxxxxx
1> exit

Application Connection Strings

Below are some examples for application connection strings.

ADO.NET,1433;Database=TestDB;User ID=tom@l0gncoxz3l;Password=xxxxxxxx;Trusted_Connection=False;Encrypt=True;


Driver={SQL Server Native Client 10.0};,1433;


$connectionInfo = array("UID" => "tom@l0gncoxz3l", "pwd" => "xxxxxxxx", "Database" => "TestDB");
$serverName = ",1433";
$conn = sqlsrv_connect($serverName, $connectionInfo);

Wednesday, July 20, 2011

SQL Server Express 2008 R2 Unattended Install

Simple steps to install SQL Server Express 2008 R2 in unattended mode.

1) Download SQL Server Express 2008 R2 from Microsoft web site.
For example, SQL Server Express 2008 R2 (ENU x86) can be downloaded from

(2008 R2 installation location table can be found at )

There are two ways of installing SQL Express from here. One way is extract files from .EXE file and run setup.exe from the extracted directory.


    a) Extract files from downloaded EXE file.
        The following command will extract files onto .\SQLEXPR folder silently.
       C> SQLEXPR32_x86_ENU.exe /Q /X:.\SQLEXPR

     b) Run SETUP.EXE with unattended setup arguments.
         The following command installs SQL Server Engine only in silent mode.
         Named instance (.\SQLExpress) will be created. Setup progress will be shown.


2-2) The other way is to use all setup parameters directly in SQLEXPR32_x86_ENU.exe.


For more information about all other setup command options, you can see How to: Install SQL Server 2008 R2 from the Command Prompt.