Introduction to Windows Azure SQL Database Services (Part 1)
Windows Azure SQL Database also known as SQL Server Data Services or simply SQL Azure is a Microsoft’s cloud services that offers a transactional and relational data-storage capabilities on Microsoft SQL Server 2012 (such as Amazon S3 and Amazon Relational Database Services), as part of the Azure Services Platform. Windows Azure SQL Database uses special version of Microsoft SQL Server as its backend that provides high availability by storing multiple copies of databases i.e. flexible range and fast. Windows Azure SQL Database is hosted on Microsoft data centers on the hardware that is owned, hosted and maintained by Microsoft. Unlike other cloud-based databases, Windows Azure SQL Database allows users to write and execute queries against stored data that can either be structured or semi-structured or unstructured documents. Windows Azure SQL Database presents a subset of the full SQL Server functionality that includes specific subset of the data types (includes string, number, date and boolean), tables, primary keys, stored procedures, views and much more. For more information, see Transact-SQL Support (Windows Azure SQL Database) and Tools and Utilities Support (Windows Azure SQL Database). Applications can use Windows Azure SQL Database in the same way they use SQL Server Database Engine because Windows Azure SQL Database use T-SQL as a query language and Tabular Data Stream (TDS) protocol for accessing services over the Internet. The only difference is that Windows Azure SQL Database is a database that is delivered as a service that means its administration is slightly different from the database that is hosted on on-premise instance of SQL Server.
Windows Azure SQL Database separates the logical administration from physical administration because Microsoft handles all the physical administration of servers while you continue to perform database administration activities such as database optimisation and performance tuning, access control that includes creation of new logins and users etc.
Like on-premises architecture, Windows Azure SQL Database also provides built-in High-Availability (HA) and Disaster Recovery with no extra cost.
Architecture of Windows Azure SQL Database Services
The Windows Azure SQL database architecture consists of four different layers (i.e. the client layer, the services layer, the platform layer, and the infrastructure layer) 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 service layer:
Lets have look at these layers in more details below:
1) Client Layer – The Client layer resides closest to your application and it allows your application to communicate directly with Windows Azure SQL database. This layer can be remain on site in your datacenter or be hosted in Windows Azure platform. Windows 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.
2) Services Layer – This layer functions as a connection between the client layer and the platform layer. The services layer provides three functions: routing, provisioning, billing and metering, and connection.
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.
3) Platform Layer – The platform layer includes physical servers and services that support the services layer. This layer consists of a large number of instances of SQL server, each of which is managed by the Windows Azure SQL database fabric. The role of this layer is a Windows Azure SQL database fabric that is a distributed computing system that consists of tightly integrated networks, servers and storage. It also provides automatic failover, load balancing, and automatic replication between physical servers.
4) 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
To start working with Windows Azure SQL database, we must first sign up for the Windows Azure platform and for this you require Windows Live ID (If you don’t have a Windows Live ID then click here to create one). After you created the Windows Live ID account, you can then use this Windows Live ID to sign up for Windows Azure platform here. Once you are signed up for Windows Azure account, click here to login with that account to start working with Windows Azure SQL database. Once logged in, you will see the Windows Azure Platform Management Portal (see below):
This Windows Azure Platform Management Portal provides a user interface which enables you to quickly provision your servers and logins and databases.
Server and Database Provisioning
In this section we can use the Windows Azure Platform Management Portal to create new server and database.
Creating New Server
To create new server, click SQL Databases in left menu and then click on Server as shown in the figure below:
Next step is to launch Create Server Wizard, to do that click on CREATE A SQL SERVERS (see below):
The first screen in Create Server Wizard is Database Server Setting screen, on this screen you will specify Login name that is used to authenticate your Windows Azure SQL Database. You also need to select region where you would like to host your Windows Azure SQL Database (see below):
Note: Choosing the correct region in which your Windows Azure SQL Database is hosting is crucial. There are eight regions to choose from: Microsoft currently has four data centers in the United States, two in Europe and two in Asia. Ideally, choose region closest to where you live.
After you choose the appropriate Database server settings, select Next button sign to create the server (see below):
Also select the option, allow Windows Azure services to access the server. Once your server has been successfully created, you can then view this server in Windows Azure Platform Management Portal (see below):
Now click on the name of the server to view the server details dashboard in Windows Azure Platform Management Portal (see below):
The following information about the server is visible on the server dashboard:
1) Name – Indicates the name of the server.
2) Administrator Login – Login name of the server administrator.
3) Database Count – Total number of databases hosted on this server.
4) Manage URL – FQDN of the server i.e. link to manage the server.
Creating New Database
Now that we have created our server, let’s create a database on this server. To do that, click on Databases link on server dashboard and then click CREATE A SQL DATABASE button (see below):
This launches Specify database settings screen, in here you specify database name, edition, maximum size and collation of database, subscription and server name where this database is hosted (see below):
At the time of writing this article, Windows Azure SQL Database supports two databases "editions" (i.e. Web and Business) and various database sizes on the basis of the edition. There is no difference in the databases of editions, except the size. The Web edition contains database sizes of 1 GB and 5 GB and the Business edition contains database sizes of 10GB, 20GB, 30GB, 40GB, 50GB, 100GB and 150GB.
Click Next button sign to create database. After the database is created, it will be visible via Windows Azure Platform Management Portal (see below):
Click on database name to view database dashboard, here you can view the database configuration details such as name, status, server, collation, edition, management url and connection string:
To view the database connection string for your development environment, click on connection string (see below):
Click on SCALE button, to change the database edition and size. This is demonstrated in figure below:
Configuring Windows Azure SQL Database Firewalls
The Windows Azure SQL Database 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 Windows Azure SQL Database firewall, click on the server name and then choose CONFIGURE in servers dashboard, as shown in the figure below:
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 in the Server name box. Because Windows Azure SQL Database 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 Connect to connect to Windows Azure SQL Database (see below):
Now you are connected to your Window Azure SQL Database via SQL Server Management Studio, click on New Query window and then execute the following command to test your connectivity:
SELECT * FROM sys.databases
The following figure shows the results of the query:
This article provides an overview to Microsoft Windows Azure SQL Database, which is a cloud-based relational database platform that is built on SQL Server technologies. By using Windows Azure SQL database, you can easily provision and deploy relational database solutions to the cloud, and benefit from a distributed data center that provides enterprise-class availability, scalability and security with the benefits of integrated data protection and self-healing. In my next article, I’m going to discuss how to administer Windows Azure SQL Database.