Ever needed to install and configure an SQL Server instance environment, including the operating system? If you asked me to estimate the time I need to complete this task, I would say half a day’s work until I have my connection string and can start deploying my database. In Windows Azure SQL Database (WASD) it’s five minutes, but you lose some control over your environment. A quote from MSDN introducing WASD: “Microsoft Windows Azure SQL Database is a cloud-based relational database platform built on SQL Server technologies. By using Windows Azure SQL Database, you can easily provision and deploy relational database solutions to the cloud, and take advantage of a distributed data center that provides enterprise-class availability, scalability, and security with the benefits of built-in data protection and self-healing.” We’re developing an in-house finance application for our company. This application needs a basic database implementation and the database will handle no more than 1GB of storage in production. We found WASD to be the right solution for our needs for a number of reasons:
Low Pricing
WASD has a pay-as-you-go model, which means you pay only for the storage your database uses. In our case, a database no bigger than 1GB costs us 5 USD. This low pricing is due to the WASD multi-tenants environment, where a single database you create is actually deployed on a group of machines in a Microsoft datacenter, and in this group there are several other databases belonging to other clients. In this setup, you could have a noisy-neighbor problem if one client in your group of servers strains the resources for his own use and your queries suffer. So the Azure fabric uses throttling to indicate when a client is straining a resource. At first, requests will get an error stating to try again after 10 seconds. If the strain continues, no more connections are allowed until the resource is freed.
Built-In High Availability
WASD has a high-availability solution built in for every database. The Azure fabric replicates two copies of your database to another group of physical servers to maintain data availability during any hardware failure, system updates, or SQL Engine updates. The entire maintenance procedure (which you don’t have control of) is transparent to you.
WASD Takes Care of Everything For Me
The Azure fabric is the system admin in the case of disk failure, unexpected server shutdown, operating system updates, SQL Engine updates, and server overload. Microsoft guarantees that customers will have connectivity between SQL Database and their Internet gateway at least 99.9% of the time.
But there were some limitations we had to consider before deploying our database in the Azure cloud:
Cross-database querying with the USE command is unavailable. When working with WASD you can only work with one database connection.
Every database is limited to 150GB
There is no SQL Agent. If you need to schedule a task, you can set a local / virtual machine that’s connected to your WASD to perform the task with that machine’s schedule.
Having decided to go with WASD, we can start creating our database.
In the Azure Management Portal, at the bottom left corner we select New, then Data Services > SQL Database > Custom Create
At the Database create page, we specify the database name, edition (Web edition for 1-5GB and Business edition for 10-150GB), database collation and server
The server is a logical entity responsible for generating the domain name (you can’t change or choose it yourself) and configuring the firewall rules. In the menu above, you can choose to add the created database to an existing server.
On the server create page, we specify the administrator login name and password, and server region (choose among 10 Microsoft datacenters in the world).
And you’re done! The database is now being created, which will take about one minute.
Now all we have left to do is configure the firewall rules to accept connections from our IP and grab the connection string. In the left pane of the portal, we select the SQL Database and find the newly created database. Click Set up Window Azure firewall rules for this IP address.
After adding our current IP address, at the bottom of the page we see our connection string. Use that with the administrator login and password we created and you can connect from SQL Server Management Studio or you can use the New Query window from the management portal. Personally, I prefer working from already familiar environment of SSMS.
SQL Server Management Studio
New Query window from management portal
Backup & Restore in WASD
As I said earlier, the Azure fabric keeps your database in an additional two replicas, but those replicas aren’t for your use in case of user or application error, or when you want to restore your database to a point in time. For that you have the WASD automated tool for exporting a bakpac file from a temporary copy of your database containing all your schema definitions and data. This is stored in a Windows Azure storage account.
At the top of the SQL Database page, click Configure to get to the automated export page. Here you specify in which storage account to store the export file, the export frequency and start date and time, how many export files to keep, and, of course, your login name and password. Don’t forget to click Save.
In addition, you can create a new database from an existing export file in your storage account. Useful Links
A video by Greg Leake describing and introducing not only WASD but also the use of Windows Azure VM services.
A video series by Scott Klein introducing and explaining the work procedures with WASD
An MSDN article about the general guidelines and limitations in WASD
An MSDN article about the backup and restore typology in WASD
Other case studies for production SQL Databases in Azure
To Sum Up WASD is a more than enough of a solution for our database needs, from its built-in high-availability infrastructure and simplified deployment and provision to the peace of mind regarding all maintenance procedures for our environment.
Comments