Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 2, 2009

Cloud Database with Microsoft SQL Azure

By Don Schlichting

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.

Azure web tool

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.

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:

test data can be retrieved by using a standard SELECT statement

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.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date