- Posted by SondreB on November 30, 2009
-
Update: Some of the details in this blog post is no longer required, by downloading and installing SQL Server Management Studio 2008 R2 you get better support for SQL Azure. Read my post about simpler management with SSMS.
Here follows a step-by-step introduction to SQL Azure. With all new technologies, and especially those which are available as pre-release form, there can be a step learning curve. Additionally some of your knowledge might be wrong and irrelevant when the final product is complete. It’s hard to keep yourself up-to-date on the latest, but it’s very awarding when you master new technology which makes you more productive and capable of delivering more value than previously.
Cloud Computing is a subject which is seeing a lot of attention lately and it is a very important improvement to existing development and deployment models for service-oriented solutions.
This example is written and based on the November 2009 version of Windows Azure platform.
Create the SQL Server instance
First step to getting started with SQL Azure is to create your instance of the SQL Server. Access the website https://sql.azure.com/, login with your Windows Live ID. If you’re doing this on the CTP (Customer Technology Preview), you can use tokens to get access to the SQL Azure service. When the service goes live, there will be other options to buying service tokens.
When you create a new SQL Server instance, you will be asked to provide a username and password. I suggest using a strong password for this account, preferably use tools like KeePass to generate strong password that you can store securely.
Tip: Never use the SQL Azure administrative account in your applications, use a custom SQL login which you can store in configuration files, etc. This will be explained in further details in this post.
Create database using web interface
If you want to, you can use the web interface to create a new database. Through this interface you get option to pick between the different sizes. Remember, there is a price difference on the db-size so don’t choose more than you really need. We will later explain how you can create a new database using SQL scripts.
Open up the firewall
Before you can connect to your SQL Server instance, you need to open up the firewall. By default, nobody can connect to your instance.
Click the Firewall Settings tab on the administrative page, then Add Rule. Fortunately the webpage informs you which IP address you are currently connected from, so it’s easy to add the correct rules.
Tip: It can take a few minutes for the firewall settings to update.
Connect using SQL Server Management Studio
It is now possible to connect to your SQL Server instance using different SQL tools, one is of course SQL Server Management Studio 2008 (or R2). While you won’t get the full suite of functionality that you are normally used to, you can use it to connect and execute SQL statements against your databases.
When you first start management studio, close the Connect to Server dialog that appears. Instead click the New Query button on the toolbar. This will again display the Connect to Server dialog, only this time it will work without an error message.
Fill out the server name, which you can find on the SQL Azure web administration. It should be something like INSTANCE_ID.database.windows.net. Fill out the username and password, sometimes you might need to write the username with the following pattern, depending on which tool you use, username@INSTANCE_ID.
If the firewall updates are not complete or you have something wrong in the firewall rules, you might see a dialog like the following. This means you have network connection problems.
Create database using scripts
MSDN contains documentation on which type of SQL operations are allowed on SQL Azure, if we take a look at the CREATE TABLE statement, it contains fewer options than normally, basically only name and size (1GB or 10GB at the time of writing this post, default is 1GB).
Create database statement has to be run by itself, so type the following in the query window and execute it by pressing F5:
CREATE DATABASE TestDb
Create login and user with correct permissions
As I mentioned earlier, you don’t want to use the master login when you later on connect to your custom database. Therefore you should create an alternative login with the following command. Make sure you generate a strong password.
CREATE LOGIN TestLogin WITH PASSWORD = 'PASSWORD'
Next it’s time to change the query window to connect to our newly created database, right-click on the query window and choose Connection/Change Connection. Use the master login as before, but expand the Options and fill out the database name in the Connection Properties.
When you are connected to the right database, you can create a user on that database, which is basically registering an existing login account to a database. This user will by default have no permissions on the database, unless you already gave them one or more SQL Server roles.
CREATE USER TestUser FROM LOGIN TestLogin
Next step is to give the permissions needed for this user, to simplify we’ll give the login SELECT, UPDATE, INSERT and DELETE permissions on all tables on the database.
GRANT SELECT TO TestUser;
GRANT INSERT TO TestUser;
GRANT UPDATE TO TestUser;
GRANT DELETE TO TestUser;
Creating tables and inserting data
Without doing a deep explanation of how to create tables, here is a create table statement for a very small table. Run this in your query window.
CREATE TABLE Item
(
Id uniqueidentifier NOT NULL PRIMARY KEY DEFAULT newid(),
Name varchar(100),
Created datetime,
Modified datetime
)
Then it’s time to insert some sample data, run the following statements.
INSERT INTO Item (Id, Name, Created, Modified) VALUES(newid(), 'Test #1', getdate(), null)
INSERT INTO Item (Id, Name, Created, Modified) VALUES(newid(), 'Test #2', getdate(), null)
INSERT INTO Item (Id, Name, Created, Modified) VALUES(newid(), 'Test #3', getdate(), null)
Congratulations, you have successfully created a SQL Azure database, table and populated it with some data.
To validate that everything is working fine, run a query that selects data from your new table.
In my next post, you will learn how you can connect to the database from any website, both running locally and inside the Windows Azure cloud.