Sep
13
2016

T-SQL Tuesday #82 – My first steps on Azure Database

tsql2sday150x150

 
This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have the chance to write about SQL Server.
This month’s topic about the cloud
, topic where I’m absolutely a newbie, even though here is my addition.

 
As I said, I have no experience with SQL in the cloud, but that makes this week’s topic more challenging, because this is how everybody starts, so I’ll just tell you my experience trying to get started with SQL Azure.

First a quick google/bing search takes me to Microsoft Azure where I see our favourite words “Start free”, so there we go!

After logging to my Microsoft account, another form to fill and some verifications (including card registration) we get some free credit ($200 or £125) to start playing around.

01_welcome_screen

Small video to watch and some links, most interesting of course SQL Database tutorials

 
The Azure Portal

The Azure Portal gives us the possibility of managing the multiple services the platform has to offer, VM’s, SQL Databases and much more but for now and considering the situation I’ll be happy if I manage to create a database and run a query against it.

Clicking on SQL Databases -> Add will get us a form where to start configuring it.

02_create_sql_database

Interesting choices for “Select Source” which has the following options

  • Blank Database
  • Sample, where we can choose from 2 different versions of AdventureWorks, legacy and V12 (latest)
  • Backup, I don’t have any backups so it’s not a real choice for me right now

 
You need to create a new server. Name, admin login and password and location is sufficient to get the job done

03_create_new_server

 
Pricing tier, since this is a demo and I only have £125 free credit, I’ll go for the cheapest option. Price is a combination of storage capacity plus processing capacity measured in DTU’s.
How each DTU is calculated is a black box, but there is a tool which by giving some metrics of your current workload will help you determine how many DTU’s you’d need in the cloud to handle it.

As I said, gimme the cheapest!

Funny that the default collation is SQL_Latin1_General_CP1_CI_AS so be aware if you want to migrate data from on-premises to the cloud. In this case, collation is determined by the sample database I’ve chosen.

Once we click ‘Create’ the deployment of our database will start, when finishes we can see it in our list of databases.

04_database_list

 
Running my first query on Azure

This is so exciting! Microsoft has been publishing that transitioning to the cloud would be kind of seamless so most of the on-prem knowledge is an asset.

How do I query my SQL Databases on-prem? You’re right, SSMS.

05_using_ssms_to_connect_azure
06_signing_to_azure

In the process we need to sign in to Azure and add an exception on our firewall which seems pretty straight forward and we are ready.

You can see how our new server and database shows in SSMS like any of our local (or remote) SQL Servers, if we want to dig in into properties we’ll soon find the new specific settings for Azure and how some of our on-prem are greyed out, but the it’s true we can use it as any other, so I can run queries as normal.

07_querying_azure

 
Conclusion

This is soon for me to have a well based opinion about the Azure Database, but in general I found the process quite simple to start with, however I’m sure to get all the juice you need to expend quite long hours of learning.

Hopefully this is not going to stop here and I’ll try myself to keep learning as lots of people are now moving to the cloud the possibilities are huge.

Thanks for reading and thanks to Jeffrey Verheul for hosting this month’s event!
 
 

One comment
  1. Raul Fernando Gutierrez says:

    Good to see you started with Azure! What I learned right away aside from what you covered in this blog is the limitations in an SQL Azure Database. Right off the top of my head here’s a few: No SQL Agent, No Linked Servers, No fully qualified names on queries (basically no cross-database queries). There are workarounds, but they tend to raise the cost or DTU’s. To me it seems, the Azure cloud is ideal for either new applications or for existing reporting platforms such as a data-warehouse that already uses aggregated transactional data as a source. To most organizations, a hybrid implementation of both an Azure VM which are easy to spin up and a SQL Azure database is the likely popular solution. Migrating data to Azure is not much of an issue if you’re accustomed to using SSIS, wizards, replication or scripts to accomplish that. Overall it seems to be a very cool service if you’re limited in resource management, DR and database maintenance areas. Also, if you’re comfortable with Powershell, you can configure many of the things you did on the Azure portal using .ps scirpts. My free trial lasted only a few days because I did everything you could possibly do with a $200 free credit. I wish they made it more like $1000!

Leave a Reply

Your email address will not be published. Required fields are marked *