SQL Database

Introduction

Most web applications sit on top of data provided through a relational database. Both AWS and Azure provide database services where the infrastructure and OS are invisible, allowing the developer to focus on database design.

To get the full PaaS experience on Azure you need to run Microsoft SQL Server so that is what I have chosen to compare. Once up and running databases on both platforms can be managed using SQL Server 2014 Management Studio. This can be downloaded as a standalone tool from the Microsoft SQL Server 2014 Express download page (https://www.microsoft.com/en-us/download/details.aspx?id=42299).

Azure

Azure currently has two management options: the current default portal and a preview of a new portal. All of these steps were carried out using the current default portal but appear to be very similar in the new one.

Creation of a database service in Azure starts with selecting +NEW, then DATA SERVICES -> SQL DATABASE -> CUSTOM CREATE.

You then enter your database name, service tier, performance level, collation and the server that it is to run on. In my case I chose the cheapest level running on a new server.

The service tier sets the maximum size of the database and the performance levels available. These performance levels provide an abstract level of database power measured in "Database Throughput Units" (DTUs). Microsoft equate these to more concrete performance levels in their documentation (https://msdn.microsoft.com/en-us/library/azure/dn741336.aspx). It appears that the service tier and performance level can be updated on a running database after initial installation. 

If you elected to create a new SQL database server you then need to enter the server settings.

Submitting this form takes you back to the portal home screen where your new database is displayed with a status of Creating for a few seconds then Online.

If you are throwing something up to the cloud quickly you probably want to manage your new database from the same PC that you just created it from. To quickly gain client access for your management tools you can select the database in the home screen then click the "Set up Windows Azure Fiewall rules for this IP address" link. Maintaining this firewall rule later requires a little more navigation:

  • In the current portal select SQL DATABASES (left menu) -> SERVERS (top) -> select your server -> CONFIGURE (top)
  • In the new portal select BROWSE (left menu) -> SQL servers (slide out) -> select your server -> All settings (right) -> Firewall (right)

You can now connect to your server using Management Studio on your PC. You will need the server name which can be found by selecting the database in the portal, clearing the quick start screen and looking in the right column. 

Now you can connect Management Studio using the server name, SQL Server Authentication and the credentials entered in the database server settings. Once connected you can run SQL scripts to create your database structure and reference data. In my case I'm just creating a message table for an over engineered Hello World.

CREATE TABLE message
(
    id int NOT NULL, 
    code char(10) NOT NULL, 
    description varchar(255) NOT NULL, 
    PRIMARY KEY (id)
)
GO
insert into message (id, code, description)
values (1, 'HELLOWORLD', 'Hello World!')
GO

AWS

In the AWS console you need to select your region in the top right before starting to create services. Creation of a database service in AWS starts with selecting Database -> RDS, then selecting Instances on the left.

Click the blue Launch DB Instance button, select Microsoft SQL Server then an edition. For the purpose of cheap testing I selected SQL Server Express. This affects price and maximum performance and cannot be changed after deployment of the instance. Obviously you can, if necessary, create a new instance with a different edition later and move the database content across.

You now enter the database details. Note that you are only creating a database server instance through the AWS console. You will need to create the database itself using Management Studio later.

The details to set are more granular with AWS than with Azure. You need to select an instance class with a specified number of virtual CPUs and amount of allocated RAM, along with the storage type and allocated storage. These setting appear to be changeable on a running database after initial installation.

Now you need to configure advanced settings. Note in the screenshot that I have selected Create New Security Group. The default VPC security group will not allow any access to the database. Creating a new security group will initially generate a firewall rule allowing access from your current IP address. To maintain this rule later from the AWS console go to Networking -> VPC -> Security Groups -> select the group named rds-launch-wizard -> Inbound Rules.

Select Launch Your Instance to start your database server and return to the console home. Go to Database -> TDS -> Instances, select your new database and read the server name from the Endpoint field at the top of the details panel.

Now you can connect Management Studio using the server name, SQL Server Authentication and the credentials entered in the DB details. Once connected you can create your database and run SQL scripts to create your database structure and reference data.

Comparison

The most obvious difference between the Azure and AWS PaaS relational database offerings is in the choice of database platforms. AWS provide MySQL, PostgreSQL, Oracle and MS SQL Server as parallel offerings; Azure would really like you to use MS SQL Server. You can get an Oracle 12c under their VM/Compute offerings but then presumably tuning and high availability and so forth become entirely your problem. If you really want an open source database on Azure you can deploy a Windows or Ubuntu VM and install a database server but now you're back to IaaS and managing the OS yourself.

If you are happy with MS SQL Server then the major difference between the services is the trade-off between granular control and ease of configuration. If you have a technically skilled database administrator then they will probably prefer the extra control on specific resources that AWS offers. If your team is focussed on application development and you just want one big knob to turn for more database power (and cost) then Azure may be easier for you to manage.