SQL Azure TSQL
November 6, 2009
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.
After creating a SQL Azure account, youll receive a user name and password that can be used to access the Azure service. In this example, well 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 dons@o8oudehe7w -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 well 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 dons@o8oudehe7w -P mypassword -S o8oudehe7w.ctp.database.windows.net -d master -s "|" -o c:\temp\sql.txt
To begin, lets create a new SQL login. All logins to SQL Azure are SQL Logins; Windows logins are not supported. So well 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 dont 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 dons@o8oudehe7w -P mypassword -S o8oudehe7w.ctp.database.windows.net -d dbtest1
Now that were connected to the new database, well 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, its 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.
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.