SQL Azure TSQL

Introduction

Microsoft now offers a “cloud” version of SQL Server called
SQL Azure. Cloud in this context means SQL Server lives out on the internet,
on equipment managed by Microsoft. We access SQL Azure as a service over the
internet either programmatically with Dot Net, or through the command line
using SQLCMD. SQL Azure is very similar to traditional SQL Server and now
supports many standard TSQL commands. This article will demonstrate the use of
TSQL commands to create SQL Azure objects.

Connecting

After creating a SQL Azure account, you’ll receive a user
name and password that can be used to access the Azure service. In this
example, we’ll connect to SQL Azure from the SQL Command (SQLCMD) prompt.
SQLCMD is a utility included with traditional SQL Server. To begin, open a DOS
prompt and enter the following command string:


sqlcmd -U [email protected] -P mypassword
-S o8oudehe7w.ctp.database.windows.net -d master

If the connection is successful, a 1> prompt will appear.
The connection string consists of calling the SQL CMD application with the –U
user name, the –P password, -S for server name, and –d for the database. Note
that the user name is the admin login, “don” in this case, plus the unique
server name “o8oudehe7w”. The unique server name is automatically created by
Microsoft when your Azure account is created. The –S server name is the fully
qualified server name consisting of your unique database plus the Azure
internet location. The “master” database is automatically created for us when
the Azure account is activated.

SQLCMD offers extensive configuration and formatting
options. For a complete list, see Books On Line (BOL) for “sqlcmd utility”.
Often the output of the SQLCMD is not the most readable due to poor text
formatting, so we’ll change the connection string to include command switches
to separate each column with the pipe “|” character and to log all output to a
text file as shown below:


sqlcmd -U [email protected] -P mypassword
-S o8oudehe7w.ctp.database.windows.net -d master
-s “|” -o c:tempsql.txt

Logins

To begin, let’s create a new SQL login. All logins to SQL
Azure are SQL Logins; Windows logins are not supported. So we’ll be passing a
username and password string each time we connect to SQL Azure. Creating
logins and users in SQL Azure is similar to traditional SQL Server. First, a
login is created, then a user is derived from the login, and lastly permissions
are assigned to the user. Use the TSQ below to create the login, the password
must be strong.


CREATE LOGIN login1 WITH password=’test’; GO
CREATE USER user1 FROM LOGIN login1; GO
EXEC sp_addrolemember ‘sds_securityadmin’, ‘user1’; GO

The sds_securityadmin role used in the above example is SQL
Azure’s equivalent to the traditional SQL Server role of “securityadmin”. All
roles must be granted while in the master database. SQL Azure comes with two
primary roles, the “loginmanager” and the “dbmanager”. The “loginmanager” role
grants your new user with the permissions needed to create logins, while the “dbmanager”
allows for database and table creation. The dbmanager role in SQL Azure is
similar to the dbcreator role in traditional SQL Server. For additional
details on Azure logins, see the Microsoft article “Managing Logins and Users
in SQL Azure” at http://msdn.microsoft.com/en-us/library/ee336235.aspx
.

Creating Databases and Tables

Creating a database in SQL Azure is similar to creating
databases in traditional SQL Server. One difference is we don’t specify
physical locations for the MDF and LDF. All hardware interactions for SQL
Azure are taken care of by the system. Use the TSQL below to create a
database:

CREATE DATABASE dbtest1; GO

By default, our database will be created with a maximum size
of 1 GB. Currently, there are two size options available, 1GB or 10GB. Also
noteworthy is that database chaining and TRUSTWORTHY options are not
available. In traditional SQL Server, these options allow a user to execute
code that accesses data from multiple databases simultaneously.

With the new database created, lets logout of master and
login to the new dbtest1 database. Logging in and out is the only way to
change databases in SQL Azure. The traditional SQL Server command of “USE” to
change working databases is not supported. Enter QUIT in SQLCMD prompt to exit
back to the command prompt. Use the same login string as in the earlier
example but change the –d switch to the following:


sqlcmd -U [email protected] -P mypassword -S o8oudehe7w.ctp.database.windows.net -d dbtest1

Now that we’re connected to the new database, we’ll create a
table. Creating tables in SQL Azure is very similar to creating tables in
traditional SQL Server. One difference though, is that SQL Azure does not
support heap tables; so all tables must have an index. This leads to a
discussion about the best part of SQL Azure tables, which in my opinion, is not
only the ability to have indexes, but have foreign key references. This means
SQL Azure is relational, like traditional SQL Server. So when we retrieve data
from tables, we can create joins and return data from multiple tables in one
statement. While this is nothing new in traditional databases, it’s very
uncommon in Cloud database platforms. Most Cloud databases require us to have
very flat unrelated tables, and data can only be retrieved from one table at a
time, requireing a complete architecture change for many applications. To
create a table, enter the following TSQL:


CREATE TABLE testtable1 ( mycol1 int PRIMARY KEY); GO

This command created our table with the “mycol1” column
being a Primary Key. A Key has to be specified during table creation.

Conclusion

The support for traditional TSQL commands in the SQL Azure
cloud means we can leverage our existing DBA skills and with a very short
learning curve, create cloud-based objects. Some key ideas to keep in mind
include the fact that SQL Azure is relational and supports JOINS and that
Tables must be created with an Index.

»


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles