Introduction
SQL Azure is a cloud database system offered by Microsoft.
Originally, the product was called SQL Data Services or SDS. It has now been
rebranded and its features have been expanded. One of the new features, and
the focus of this article, is the ability to manage, create, and manipulate the
cloud database using TSQL. TSQL (Transact Structured Query Language) is SQL
Servers native language for database management. Originally, TSQL was not
supported on most cloud database systems, Azure included. Instead, propriety
languages or variations of LINQ were required. So TSQL is a welcome addition
because we can now leverage our exiting DBA skills to the cloud. In addition,
because the SQL Azure data types and stored procedures closely match
traditional SQL Server, it’s easy to develop locally then deploy to the cloud
like we would to any other hosting platform.
Azure
Azure is a could-computing system provided by Microsoft. Think
of Azure as a cloud operating system that provides our applications with
storage and a programming platform. Cloud in this sense of the word means
Azure sits out in the internet, on hardware managed by Microsoft. The day-to-day
management of hardware resources is controlled by the Azure system and we have
no visibility or responsibility for it. Our applications connect to Azure
through a service using SOAP or REST.
Azure itself provides storage and a programming platform.
On top of Azure, there are five extensions that provide additional services. The
five extensions are Live Services, Net Services, Share Point, CRM, and SQL
Azure. Live Services provides access to applications such as Live ID and
Windows Live Messenger. Net Services provides a “service bus” to help tie
separate modules together into one application. Share Point Services provides
a platform for developing collaborative applications. The CRM Service exposes
a base to develop customer management software. The last module, and the focus
of this article, is SQL Azure.
SQL Azure
SQL Azure is a relational database. Relational Databases in
the cloud are not common. Most are non-relational and you create highly
non-normalized tables instead. Another uncommon plus is that SQL Azure
supports Stored Procedures. Using stored procedures allows for clean
separation between database and application logic. Another feature is the wide
amount of data types supported by SQL Azure. Almost all the typical SQL Server
2008 data types are included. In addition, SQL Azure supports Transactions in
the cloud.
Getting Started
At the time of this articles publication, SQL Azure has not
been officially released. So to get started; request a CTP (community
technology preview) account from Microsoft at this URL: http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx
. Once the account is created, a set of connection strings will be generated.
These connection strings provide access to your own personal cloud database
from Dot Net, the SQL Command Prompt, or any other ODBC or OLE DB connection.
Below is the connection for Dot Net.
Server=tcp:o8oudehe7w.ctp.database.windows.net;
Database=master;
User ID=dons;
Password=myPassword;
Trusted_Connection=False;
The Server section in the sting references my personal space
in the cloud, which is “o8oudehe7w”. Each account will be unique. The Trusted
Connection is set to false because we’re not using Windows security. The
connection strings, as well as some minor database management tasks can be
accessed from a web tool shown below.
Currently, the database tasks that can be performed from the
web tool are limited to only database creation and dropping. Unfortunately,
there isn’t a SQL Server Management Studio equivalent for the cloud.
SQL Command (SQLCmd)
SQLCmd is command prompt utility included with traditional
SQL Server. It can be embedded into scripts or run interactivity. SQLCmd includes
a wide array of options such as logging, formatting, and basic error handling.
To begin the example, open a command prompt and pass the SQL Azure connection
string to SQLCmd as shown below:
sqlcmd -U dons@o8oudehe7w -P password -S o8oudehe7w.ctp.database.windows.net -d master
If the connection is successful, a 1> cursor will be
shown. We now have an interactive session with the cloud database. In the
connection string, the –U (user) name @ server syntax is used, but just the
server prefix, not the fully qualified name. The FQN (fully qualified name) is
used with –S (server) switch. The –d (database) switch connects us to the
master database. Master is automatically created for us when our CTP account
is created.
CREATE DATABASE
To create a new user database, at the 1> prompt type:
CREATE DATABASE Test1, and then press enter. A >2 prompt will appear, type
GO and then press enter. If the create is successful, the prompt will renew to
its >1 state. If there is an error during the CREATE, it will be displayed
on the dos command line. Refreshing the view of the web administration tool
shows the new database was created.
To connect to our new database using SQLCmd, first close
the current connection to master by typing QUIT to exit SQLCmd and be returned
to the command prompt. Reenter the previous connection command but change the
database to the new Test1 as shown below:
sqlcmd -U dons@o8oudehe7w -P microsoft1! -S o8oudehe7w.ctp.database.windows.net -d Test1
The prompt will change to the SQLCmd >1 without error,
showing we’re connected to the new Test1 cloud database.
CREATE TABLE
The syntax for creating a table in SQL Azure is the same
for traditional SQL Server. One note though, Azure does not support heap
tables, meaning every table must have a key or index. To create a table, enter
the following TSQL statement at the SQLCmd prompt:
CREATE TABLE table1 ( mynumber int primary key, mystring varchar(50))
GO
After the GO, the prompt will return to the >1 symbol.
Data can now be inserted into the new table using the following statement:
insert into table1 (mynumber, mystring) values (1, ‘test’)
GO
SQLCmd will confirm the successful insert by reporting “1
row affected”. The test data can be retrieved by using a standard SELECT
statement as shown:
Conclusion
SQL Azure is a Microsoft cloud database. SQLCmd can be used
to access and manipulate SQL Azure. TSQL commands common to traditional SQL
Server are supported. In addition, Transactions and Stored Procedures are also
supported.
