SQL Server

Configuring and Administering Windows Azure SQL database – Part 1

by Basit Aalishan Masood-Al-Farooq

Configuring and Administering Windows Azure SQL database – Part 1

Author: Basit A. Farooq

Microsoft Windows Azure SQL database or simply Azure SQL database is a cloud-based relational database platform that is based on Microsoft SQL Server technologies. Azure SQL database runs a special version of Microsoft SQL Server that provides the subset of SQL Server functionality as its core backend relational database management system. Azure SQL Database use T-SQL as a query language and Tabular Data Stream (TDS) protocol for accessing services over the Internet. Administration of Azure SQL database is slightly different from on-premise instance of SQL Server because it is delivered as a service. Azure SQL database instance runs on hardware that are owned, hosted and managed by Microsoft. Moreover, Microsoft handles all the physical administration of servers while you continue to perform database administration activities such as database optimization and performance tuning, access control that includes creation of new logins and users etc. Like on-premises architecture, Azure SQL database also provides built-in High-Availability (HA) and Disaster Recovery with no extra cost.

Architecture of Azure SQL database

The Azure SQL database architecture consists of four different layers of abstraction that work together to provide a cloud-based relational database services for your application. This architecture is very similar to on-premises architecture with the exception of the services layer. These layers are explained in more detail below:

Microsoft Reference: http://msdn.microsoft.com/en-us/library/windowsazure/ee336271.aspx

Client Layer

The client layer resides closest to your application. The client layer allows your application to communicate directly with Azure SQL database. The client layer can either be hosted on-site in your company’s data center or hosted in Windows Azure platform. Azure SQL database offers the same tabular data stream (TDS) interface as SQL Server, which helps users to use familiar tools and libraries to develop cloud-based applications. This layer provides data access through ADO.NET, ODBC and other vendors that give you the flexibility to manipulate data using the standard T-SQL and familiar technologies.

Services Layer

The services layer is used as a connection bridge between the client layer and the platform layer. The services layer has the following three functions:

· Provisioning – Create and provisions the databases that you specify through either SSMS or Windows Azure platform portal.

· Billing and Metering – Provides the monitoring and billing for database usage based on individual Windows Azure Platform accounts.

· Connection Routing – Handles all the connections routing between your application and the physical servers where your data resides.

Platform Layer

The platform layer includes physical servers and services that support the services layer. It consists of a large number of instances of SQL server, each of which is managed by the Azure SQL database fabric – that is a distributed computing system that consists of tightly integrated networks, servers and storage. The Azure SQL database fabric also provides automatic failover, load balancing, and automatic replication between physical servers.

Infrastructure Layer

The infrastructure layer represents the IT administration of the physical server’s infrastructure that supports the services layer.

Configuring Windows Azure SQL Database server

First, sign into the Microsoft Azure Management Portal using your Azure account. If you do not have an Azure account, you can start with a with a one-month free trial of Microsoft Azure.

Creating new Windows Azure SQL Database server

To create new server, click on SQL Databases in left menu, and then click on SERVERS on the menu:



Next, click on CREATE A SQL DATABASE SERVER. This launches Create Server window. Here you will specify the following Windows Azure SQL Database server setting:

· The login name of the SQL Login that is used to authenticate to the server.

· Region where the Windows Azure SQL Database server is will be hosted. It is very important to choose the correct region for your Windows Azure SQL Database server.

· Check the box next to ALLOW WINDOWS AZURE SERVICES TO ACCESS THE SERVER to allow Window Azure Services to connect to SQL databases on this server.

· Check the box next to ENABLE LATEST SQL DATABASE UPDATE (V12) to enable latest SQL database updates, which offers extended T-SQL compatibility, API version 12 and greater premium performance.


After you choose the appropriate Database server settings, click on the complete button to create new Windows Azure SQL Database server. Once your Windows Azure SQL Database server is created, you can view it in Microsoft Azure Management Portal, as shown below:


Click on the Windows Azure SQL Database server name to view the server details dashboard in Windows Azure Platform Management Portal.




Creating new database on Windows Azure SQL Database server

To create new database, click on Databases link on server dashboard and then click on CREATE A SQL DATABASE button:


This launches Specify database settings screen. Here you specify the following information:

· Database name

· Service tiers – Basic, Standard or Premium

· Performance level – expressed in database throughput units (DTUs), is a relative measure of the resources provided to the database.

· Collation of the database

· Server name where this database is hosted.


After making your selection, click on the complete icon to create the database. Once your Windows Azure SQL Database server is created, you can view it in Microsoft Azure Management Portal, as shown below:



Click on the database name to view the database details dashboard in Windows Azure Platform Management Portal. Here you can view the database configuration details, edition, connection string, and usage information.


To view the database connection string for your development environment, click on show connection strings under quick glance.



You can click on SCALE link, to change the database edition, max size and performance level.


Configuring Windows Azure SQL Database Server Firewalls

The Windows Azure SQL Database server firewall prevents all access to your database server until you specify which computers have permission. The firewall grants access based on the originating IP address of each request. This will help you to protect your data. To configure firewall, go back to Windows Azure SQL Database server dashboard, and then choose CONFIGURE. Specify the IP Addresses you want to allow and then save the changes.
Connecting to Windows Azure SQL Database using SQL Server Management Studio

After you created your databases and defined the firewall rules, you are ready to connect to your Windows Azure SQL Database using SQL Server Management Studio. Remember you can connect to Windows Azure SQL Database using SQL Server 2008 and above instance.

To connect to your Windows Azure SQL Database and Start SQL Server Management Studio and then type FQDN (Fully Qualified Domain Name) of Windows Azure SQL Database server in the Server name box. As Windows Azure SQL Database server only allows SQL Authentication, so specify SQL Authentication, and then enter your username and password, which you have specified during the creation of server. Click on connect button to connect to the Windows Azure SQL Database server.


Now you are connected to your Windows Azure SQL Database server via SQL Server Management Studio, click on New Query window and then execute the following command to test your connectivity:

The following figure shows the results of the query:

Author: Basit Aalishan Masood-Al-FarooqI am an internationally-known lead SQL DBA, trainer, and technical author with 12 years of experience of the Microsoft technology stack. I am an accomplished development and production SQL Server DBA with a proven record of delivering major projects on time and within budget. I am an expert at evaluating clients’ needs against the capabilities of the SQL Server product set, with the objective of minimizing cost and maximizing function through making innovative use of advanced capabilities. I have developed and implemented many successful database infrastructures, data warehouses, and business intelligence projects. I hold a Master's degree in Computer Science from London Metropolitan University and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Certification Partners, and APM, incl: MCSE SQL Server 2012, MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000, and MCTS .NET Framework 2.0 Web Applications. I also have a good understanding of ITIL principles. I am the author of SQL Server 2014 Development Essentials book published by Packt Publishing. I was also a technical reviewer for SQL Server 2012 Reporting Services Blueprints, Marlon Ribunal and Mickey Stuewe and Reporting with Microsoft SQL Server 2012, James Serra and Bill Anton, both by Packt Publishing. I have also authored approximately 500+ SQL Server technical articles on various SQL Server topics for different SQL Server community sites, including: SQLMag.com, MSSQLTips.com, SQLServerCentral.com, SSWUG.org, SQL-Server-Performance.com, and SearchSQLServer.com.