In this blog post, Let’s see how to create a Microsoft Azure SQL database and how to connect to it from your SSMS client tool remotely.
Pre-Req: you need to have an Azure subscription. You can sign up for free (I guess, you will get 30 days for trail).
Once you login to your Azure management portal, it looks similar to this…
As you can see I’ve one Virtual machine and a storage account and “Zero” SQL Databases at this point. So, do you need a virtual machine or a storage account to create Azure SQL DB? The answer is “No”. I created them for exploring Azure IaaS(Infrastructure as a Service). Anyways…let’s start creating Azure SQL DB.
Step1: select SQL Databases and select “create a SQL Database”
Step 2: Give your database a name and choose your tier and DTUs (Data throughput Unit). I will explain what they are in another short blog post. Now…focus on the server in the below screenshot. Remember, I already have a VM but it’s not listed here and I’ve selected “create new SQL database server”. If you are wondering why, here am trying to create a Azure SQL Database as DaaS(Database as a Service), In which I’ve no control over the underlying server and hardware. All I get is to provide DTUs and a location to pick, I can’t use Virtual machines which I’ve created under IaaS for this service.
Step 3: Provide a SQL Server authenticated admin account to connect to this database(Think this as “sa” account on your traditional SQL Server Instance) and pick the server location where you want the server to be created.
Click on the “Check” button and you should have your server and SQL database up and running in matter of few seconds.
You can see I’ve got a new SQL Server database and when I go to servers tab, see the new server as well which got created.
So, how would you connect to this SQL Server from your SSMS?
You should go to servers tab and click on your server and click on dashboard. On the bottom right you will see an URL. That’s your server name which you should be using to connect from SSMS without https://.
Let’s give it a shot and see how it goes from my SSMS.
Oops! The fix? Simple, I have to go to azure management portal and select the server and go to configure and create a rule to allow my IP address where I’m trying to connect from using SSMS as shown below and click save.
Once I did that, I was able to successfully connect to my Azure SQL DB 🙂
I hope this will help setting up your first Azure SQL DB as DaaS. In an upcoming post, I will show few “GOTCHAS and limitations” you should be aware of while connecting to your Azure SQL DB from your SSMS.