Cloud Database with Microsoft SQL Azure
October 2, 2009
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, its easy to develop locally then deploy to the cloud like we would to any other hosting platform.
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 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.
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 isnt 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.
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 were connected to the new Test1 cloud database.
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:
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.