Azure Data Services: SQL in the Cloud (Part 2)

Hello and welcome to this tutorial series of Azure Data Services. Through the course of this tutorial, I will take you through what Azure has to offer in Data Services. I will start with the basics of relational databases, dig into the goodness of non-relational offerings and end with what is new and what is exciting in Azure Data Services

Part 1: An Introduction to Azure Data Services

Welcome to Part 2! Today I am going to discuss SQL in the Cloud and how one can use and access a SQL database in Azure. As discussed in Part 1, the two delivery models of this service in the cloud is either using IaaS or PaaS. In either case, SQL as a Service allows developers to use SQL as they would do in on-prem.

On one hand, Azure SQL has been designed as a fully managed database solution which is highly compatible with existing management tools with the added feature of built-in high availability and predictable performance during scale-out. While, if you decide on using SQL on VMs which is the IaaS model, then you have the choice of installing any of the many SQL Server Images on a Azure VM available on Azure as shown below. Once you have installed the SQL server you can manage it as any other traditional on-prem SQL servers.

(Note: All the screenshots are from the new preview portal of Azure: https://portal.azure.com/)

Before we dive into the PaaS offering, I have compiled the following differences between SQL on Azure VMs (IaaS) vs Azure SQL (PaaS):

SQL Server on Azure VM (IaaS)	Azure SQL (PaaS)
High compatibility with SQL on-prem	Standardized, Interoperable and highly scalable
Ideal for existing applications that require the SQL installation to be customized	Contains size limits for each edition
Requires more maintenance and customization to achieve scalability	Ideal for new applications

The biggest advantage of using Azure SQL over SQL server is that you can create and deploy at fraction of the time and cost when compared to an on-prem deployment. Azure SQL can be thought of as a subset of SQL which essentially contains all the core functionality of creating a server, using management tools such as Management Studio, ODBC, etc to manage it. So let us start with the basics of creating a server followed by creating a database on the server and show how to manage this using the common set of tools.  

Step 1: Create the Server & Database

As we can see from the picture above, we need to select SQL Database, choose a server from the existing list or create a new server on which the database will be hosted. Once the database is created it shows in the portal as:

Now that we have our database created, lets have a look at how we can connect/manage this database.

Step 2: Connect and Manage the Database

As you can see above, once we click on our DB, ‘Blade Pane 2’ opens up which contains details of the database. If you scroll down this pane, you can get all the monitored stats for the DB such as resource utilization, usage, operations and such administrative details. On ‘Blade Pane 3’ however, one of the most crucial piece of information exists: The Connection Strings.

As mentioned above that the usual way of connecting to the database, using ADO.NET, ODBC, PHP or JDBC still exists. Now lets see how to make connection to these databases.

First of all, let me allow my client to access the SQL server that I just created. This can be done by browsing SQL Servers, going to the firewall and changing the range of acceptable IPs.

Once the access is given, let us access Azure SQL from our SQL Server Management Studio desktop client. Like we accessed the Firewall properties of the SQL server, let us access the Properties of the Server in order to determine the Server Name using which we will connect..

Now let us open SQL Server Management Studio and connect to our server using the credentials entered before. After entering the requisite details and clicking on Connect, we can see the database that we created in this server.

In our Object Explorer, I right click and execute the following query to create a SQL database in this server. Once this database gets created, I can view this database from my Azure Management Portal too.

Summary

In this section, I navigate through the new portal showing how to create a server, a database in the server and then manage this database using our management tool, SQL Server Management Studio. Please note that Azure SQL can be managed through various other services as traditional on-prem SQL Servers such as Visual Studio Server/Database Object Explorer. Migrating Databases to Azure SQL describes migration techniques that can be employed to migrate data from relational databases to Azure SQL or between databases in Azure. Replication and Recovery of Azure SQL is yet another key topic which is essential for business continuity and must not be ignored. All in all, Azure SQL is a robust, scalable and holistic platform for a relational database. Some of the key guidelines and limitations of Azure SQL can be found here.

That's all for this week. In the next section I will discuss other data services such as Blob storage. Stay tuned and share your experience using Azure with me @AdarshaDatta.

Technorati Tags: Azure,SQL,Relational,Database,Cloud,PaaS,Data,Dev,CloudDev,Database as a Service,DaaS