Getting Started With Azure SQL - Creation And Connection

Introduction

When we have an application to design, we have its back-end or data to visualize its functional flow, dependencies etc., and when we think of databasse, 89.93 percent of developers' first choice is SQL.

For maintaining the same popularity in the Cloud, Microsoft introduces Azure SQL Database, a cloud-based service offering data-storage.

In this tutorial (Part 1), I  will cover the following -

    • How to create Azure SQL Server (only server)
    • How to connect Azure SQL server using SQL Server Management Studio

If you have just started exploring Azure data-storage services or Azure SQL, or you are planning to build an application using Azure SQL, this tutorial will help you with every aspect.

Also, I would suggest you to stay tuned to read the next parts, Part Two and Part Three of this series.

Overview

As per standard definition provided by Microsoft,
“Microsoft Azure SQL Database is a relational database service which lets you rapidly create, extend and scale relational applications into the cloud.”

SQL features like cross database queries, SSIS, and a few more, are still not a part of Azure SQL, but very soon will be included, for sure (though no such official announcements are being made). You can leverage most of the features of SQL with Azure SQL on the Cloud.

Here, let's assume that you have Azure Portal Account. If not, create your free Azure account.

Once you’re done with creating Azure account, without making this tutorial longer and boring, let’s start.

How to Create SQL Server (only server)

Now, the reason I am explicitly mentioning ‘Only Server’ is, portal has an easy approach or rather visible reach for creating SQL Database, and creating SQL server is part of its creation flow (will look in to this in later part).

Creating SQL Server (with no database) will not cost you anything.
For creating Azure SQL Server,

Click More Services => SQL servers (Type ‘sql server’ in search box for easy listing).



Click on Add as highlighted below, (Existing Server will be listed here, as we don’t have any, it’s says ‘no sql servers to display’.



Enter the following details -
  • Server Name - Your server name. Must be unique.
    ‘Name-entered’.database.windows.net as SQL server name.
  • Server admin login - Would be used as username for logging-in SQL server.
  • Password - Would be used as password for logging-in SQL server.
  • Confirm Password - Confirming password.
  • Subscription - Free or If any.
  • Resource Group - Collection of products used. Select existing or can create New.
  • Location - Region of your choice, server would be hosted.

Do remember to keep checked ‘Allow azure services to access server’, as it would allow other Azure products/ services to communicate with Server created.


For this tutorial, we have entered the below details. Once done, click => Create.



You will get a notification indicating creation is successful, as shown in the below image.



Again, click More Services => SQL servers. Our newly created server would be displayed here.



Click on ‘Server-Name’ and the details, shown in the following image, could be seen.

Go to Settings Blade => Properties.

Here, server name, login name, and other details are displayed.



Now, let’s add server level Firewall setting, the most important part of the entire exercise,

Use the following steps in the Azure portal to create a server-level firewall rule that allows connections from an individual IP address (your client computer) or an entire IP address range to a SQL Database logical server.

On the Settings blade => Firewall.

By default, it would display your computer IP address.



Important Note from Microsoft Azure Official window,

“Your client IP address might change from time to time, and you might not be able to access your server until you create a new firewall rule. You can check your IP address by using Bing. Then add a single IP address or a range of IP addresses. “

Click on Add client IP, it will create a rule for your client IP address, as shown below.

Also, to allow access to a range of IP addresses, click firewall address box (have randomly add any IP for tutorial purpose) to add or edit.

When done with adding, click on Save, in order to save the settings.



Successful addition would be notified, as shown below,



Here, we are successfully done with creating Azure SQL server, through Azure portal.

Connect to Azure SQL Server

Now, let's see how to connect Azure SQL Server.

Pre-requisite
  • SQL server management studio (will be referred as SSMS) 2012R2 or later

Ways to connect

We can connect to Azure SQL server by following ways,

  • SSMS
  • Coding ( C# and other languages)

In this tutorial, will be using SSMS 2014. You can use SQL server 2012R2 or greater version to connect.

Note - SSMS 2016 onwards will give me more authentication options like Azure Active Directory to get connected. (Not covered in this tutorial)

Most developers are familiar with SSMS window for logging in to SQL Server.

In Azure, it doesn’t make any difference, just follow the same traditional way to do so.

  • Server Name - as entered in portal at time of creating
  • Authentication - SQL Server
  • Login - username entered in portal
  • Password - password entered in portal

Click Connect,



You can also enter, ‘Login name’ and ‘Server-name’ concatenated by ‘@’ sign for connecting Azure SQL Server, as shown below,

  • Login name- codewithkasam
  • Server name- codewithkasam

Can be used as, codewithkasam@codewithkasam

Refer to below image.



Perfecto! We are now connected to our Azure SQL Server.



Summary

By now, we saw, how to create Azure SQL Server (server only), adding firewall settings through Azure portal, along with one of the ways to connect to Azure SQL Server using SSMS following traditional SQL way.

Creating Azure SQL Server with no database will not cost you a single penny. I would recommend to follow the above steps two or three times, exploring details blade in Azure portal window, for more clear understanding.