Having played around with the Azure SQL database for quite sometime now, it always surprises me how many people I spoken to who haven’t tried using a Azure SQL database because they find the whole Azure thing to daunting.
So I thought I’d write a blog post to detail how you can get up and running with an Azure SQL Database to start looking at the features available, as it now appears increasingly Microsoft is using the Azure SQL Databases as the test bed for new features ahead of the three year release cycle for SQL Server on-premise or Azure IAAS version.
LETS GET STARTED
Firstly you’ll need to have an Azure account, I’m personally luckily enough to have an MSDN account through my employer but if not you can always signup for a free trial account just to get you started (Link)
Once you’ve created your account we’ll need to login to the Azure Web portal (Link)
Once on the azure portal you’ll see the following screen.
To start the process of creating a Azure SQL Database we need to navigate to the correct area, which is simply a case of clicking on the green cross and then selecting Data + Storage selection and then select SQL Database (See below)
This will open a new selection bar like below
The majority of these selections aren’t hard to understand, but we’ll quickly run through each with a quick description.
Name – This is simply the name of the database in this example we’ll be calling it AdminDBA
Server – Like a normal database created on a on-premises SQL Server instance you’ll need to have a SQL Server instance where the database is going to be hosted. We’ll be creating this server in a moment, but once created you can have numerous databases located on the same server so you don’t need to create a server for every database.
Source – This can be a blank database, sample database i.e. Adventureworks or database restored from a backup. If you want to have something to query instantly once we’ve completed the creation select one of the sample databases here.
Pricing Tier – This is a little beyond the scope of this blog post but this is basically the pricing structure for the Azure SQL Database which can been increased with more features like Geo replication
Optional Configuration (Collation) – Again explaining Collations is beyond the scope of this blog post , but like all SQL databases your able to select the type of collation you wish to have. This is currently defaulted to the America default of SQL_Latin1_General_CP1_CI_AS but this can be deleted and changed to a collation of your choice.
Resource Group – Resource groups are containers that help you manage a collection of Azure resources
Subscription – This is the subscription account which will be basically billed for all activity on Azure.
LETS START TO CREATE THE SQL DATABASE
So now that’s the boring stuff out of the way, let start creating our Azure SQL database by filling out the required fields.
Name – AdminDBA
Server – We’ll need to create a new SQL Server as we currently don’t have one, so click on the server option and the following slide will appear.
Here we’ll select the option to Create a New Server which again will open yet another slide.
Server name – This needs to be unique not only for yourself but the entire Azure, therefore TestServer most likely won’t be available. So for this example I’ll create a server the same as the blog site name DataDoor.
Server Admin login – This is a SQL Server login account name, basically an SA login for this example we’ll simple call it Admin_Db (You can’t have anything like SA, Admin or Administrator)
Password – Don’t think I need to explain whats required here.
Confirm Password – Same again, no explanation required.
Location – This is which data centre the SQL database will be located at, therefore its good practice to choose the data centre geographically closest to yourself, or if you have an associated app which is hosted on Azure chose the same data centre as that. In this example I’ll be selecting West Europe which is closest to my location.
And the final option Create V12 server (Latest update), we’ll leave this as the default of Yes which basically means its going to have all the spangly new features (which is what all tech geeks want eh)
Your settings should now look something like the above
Now we’ve completed the initial settings setup , which should look like the below, we can create the Azrue SQL Satabase by simply clicking on Create.
You’ll then need to wait for the database to be provisioned on Azure, during this time you’ll be able to see the progress of the creation on the Azure the home screen.
Once the database has been provisioned it will change to appear like below.
Now the Azure SQL Database has been created, we now need to configure the firewall to allow us access to the database. To carry this out we need to browse all and then select the SQL Server option (the one under the key icon)
Select the name of the server which we previously created, in this example its datadoor and on the next screen select the firewall option.
On this screen you can either type in the IP Range you wish to allow access to the Azure SQL Database, but in this instance we’ll simply going to select Add client IP and then click save, this will simply allow access to the database from our PC (Preferably Static IP)
Once we’ve completed the firewall setting we should be ready to rock.
CONNECT TO THE DATABASE
Let’s start by firing up an instance of SSMS (SQL Server Management Studio) and clicking on the usual button to connect to a server like you would any normal on premises SQL server and fill out the connection dialog box like below, remember the server name is the SQL Server which you created while creating the Azure SQL Database and the login and password are the ones which you created at the same time.
Once you’ve filled out those details, click connect and bingo your up and running and ready to start querying, that’s if you chose to select one of the sample databases, if not and you chose a blank database happy developing.
I hope this helps someone out there and makes the Azure SQL Database that little less daunting.