SQL Database in Azure is essentially SQL Server 2014+ running over SSL via the Internet, a VPN, or a dedicated connection. It costs between $5 and $7,000 per month, per database, and it's awesome! It's awesome in a development environment because it can be spun up in minutes and shared by a team distributed around the world. It runs 24/7 and 365 and, thanks to your MSDN subscription, BizSpark subscription, and/or Microsoft Partner status, doesn't cost anything (unless you burn through all of your monthly credits). You also enjoy all of the features that make it a great choice for production databases. It includes free replicas and free high-availability, so you can always be online and you never have to back up. There's also free point-in-time restore, in case you have to roll something back. You get free online scaling, so you can choose any level of performance at any time and switch between levels as often as you want. It's got free near-zero administration, all the same compliance as on-premises SQL Server, and, because modern apps tend to be built on services, you can easily run your services in Azure on the same super-fast network as your database. You don't just get a database that runs 24/7; your whole application infrastructure can run 24/7, scale from single-user to massive capacity at the drop of a hat, and can be accessible wherever in the world you want to make it available. Awesome!
Additional Azure related articles: Microsoft Azure CODE articles
Performance, Cost, and DTUs
During development with a team of one-to-10 developers, the Basic service tier is good starting point. It costs $5 per month per database and supports up to a 2 GB database, handles up to 30 logins and 30 concurrent requests, and gives you a whopping 5 DTUs. What's that mean? What's a DTU? Microsoft invented the concept of DTUs to represent SQL Server performance. Five DTUs performs like an old single-core Pentium computer with a gig of RAM and a slow 10 GB hard-disk running Windows XP. With a small database, after things are cached, it performs surprisingly well on a small load. With a few developers hitting it, performance is good when testing and running an app. That's even plenty of power to run a small website for your hockey team or whatever. Five DTUs gives you a certain amount of RAM, CPU, disk performance, etc. It's a mix of all of those things.
The Standard pricing tiers range from ten to 100 DTUs, can handle up to 250GB databases, and costs between $15 and $150 per month per database. The Standard tier is appropriate for most production databases for departmental and small to medium corporate applications and some fairly large Web apps. Ten DTUs is simply twice as powerful as 5 DTUs. Database performance is different for each situation, but is usually limited by one factor whether it's CPU, disk performance, RAM, etc. Doubling DTUs doubles all of that, so for the most part, you can think of twice as many DTUs as being the same as twice as much performance. You can imagine that 100 DTUs can handle a substantial load.
With every increase in the pricing tier, more and more goodies are included. If Standard isn't enough for you, jump into the Premium pricing tier where you get 125 to 1,750 DTUs, databases can be up to 1TB, and costs between $465 and $7,000 per month per database. The high-end Premium tier configurations absolutely fly and handle crazy big workloads. For example, the Premium tier offers up to 10GB of XTP in-memory storage, active geo-replication with up to four readable hot backups, and you can restore a database to any point in time for up to 35 days. The Standard tier offers geo-replication, but no readable hot backups, and you can only restore to any point in time in the last 14 days. At the Basic tier, replicas are only kept in the same Azure datacenter region and you can only restore to any point in time for the last seven days.
An Example for Developers
Before you get started, make sure you have the right tools.
Prepare Yourself
When using SQL Server tools, make sure that you have SQL Server Management Studio 2014 SP1 or later (SSMS 2016 works great). Even the free Express edition of SSMS 2014 works great, as long as you have SP1 or later installed. If you have an older version of SQL installed, you can install a new version of SSMS alongside it and, as always, you don't have to install the entire database engine just to get the SSMS tool. For Visual Studio, make sure you have at least VS 2013 with the latest updates and patches and that you have the latest Azure SDK installed. VS 2015 is preferred, and you may be entitled to install the free Community Edition if you don't already have 2015. You can install it alongside VS 2013.
Because this isn't an introduction to Azure as much as an introduction to the SQL Database feature on Azure, I'm going to assume that you have an Azure subscription. If you don't, you can go to azure.com and start a free trial. The website is pretty helpful in getting you set up with an account and there is a lot of material available on the Internet if you need more guidance.
A Scenario for Developers
Let's create a new app with your team. It doesn't matter if it's a Web, desktop, or mobile app. In fact, let's say that you're going to build apps for all of those platforms. The team has laid out some screens and figured out the service calls that will be needed and what the data flowing through those service calls will look like. It's time to start designing the database to support all of that and putting it up for the services to start hitting. Although you could create a database in Azure and hit the ground running, you probably want to hold off and design your database locally first because, although SQL Database performs well for apps, performance within the design tools is less than stellar, especially if you're using the Basic pricing tier database. Also, there are still a couple of bits missing in the GUI design experience for Azure SQL DB that, although they're not deal-breakers, they are annoying. If you have the SQL Server 2014 or 2016 database installed locally, use that as a first step. Design the database as shown in Figure 1, set primary and foreign keys, add stored procedures, functions, views, etc. if you like. Don't bother setting up users, as they won't transfer to Azure. You can even add some sample data if you like.
On to Azure
Once you're pretty happy with your database structure and sample data, you can push it up into Azure. If you haven't created a Database Server in Azure yet, you'll need to do that now. Log into your Azure account at http://portal.azure.com and click the New button. Under Data + Storage, choose SQL Database. Choose Create a new server, as shown in Figure 2. Give your server a name. The name must create a unique domain name ending in .database.windows.net.
Make sure to choose a location (datacenter region) close to your main development team so that they'll have the fewest hops when accessing the server. You certainly could put your databases in East Asia, but performance for your team in South Texas would be much better if you put the database in South Central US (which is near San Antonio, TX). The dialog box is shown in Figure 3.
Because you're not actually going to use the empty database that this process creates, it really doesn't matter what pricing tier you choose here. Press Create and give Azure a minute or two to create your new server and database. Once it finishes, click Delete to delete the empty database, as shown in Figure 4. It's interesting to note that “SQL servers” in Azure aren't virtual machines in Azure at all. They're really just an endpoint to connect to, a little of bit of information for that endpoint, such as the firewall rules and list of databases accessible through that endpoint. A SQL server in Azure could host many databases running on different hardware at different price tiers.
Hint: Click on the “Browse >” link in the left-hand pane of the portal and find SQL servers and SQL databases in the list. Then click the star next to each one to mark them as favorites and have them show up as quick links in this pane.
Hint: Those of you who're interested in PowerShell might be interested in doing all of this with scripts because Azure can be fully controlled through PowerShell.
Hint: If team members don't have access and don't know their public IP addresses, when they try to connect with SSMS, their external IP is displayed in an error message, making it easy to add to the server's firewall rules.
Click on the SQL server link in the portal to see your servers and verify that the Status is Available and that the Location is appropriate for your team. Open the Settings for the server and choose Firewall. Click the Add client IP toolbar button to allow your current, public IP address to access the server and give it a descriptive name. You can also add IP addresses for your team members here. If your team members have permissions in the portal, they can log in and easily add their own IPs using the Add client IP button. That's all you need from the portal at this point.
Upload the Database to Azure
Back in SSMS, right-click on your database and choose Tasks > Deploy Database to Windows Azure SQL Database…., as shown in Figure 5. If you don't have this menu option, you aren't using SSMS 2014 SP1 or later. This is just a wizard wrapped around the Extract Data-tier Application… menu option, which you can also use.
Connect using the database name and admin login credentials you just created in the Azure portal, as shown in Figure 6.
After connecting to your server, name the database and keep the default pricing tier settings of Standard S2. Why do this if you want to create a Basic price tier database? The five DTUs that the Basic tier provides is painfully underpowered for operations like designing databases and deploying or retrieving databases between Azure and local computers, especially if the database is large, even for a very small, simple database. The tools are quite chatty for these types of operations and five DTUs don't work well in chatty scenarios. Even at the S2 tier, the process is not always as quick as you'd like.
Connect to your Azure SQL Database with SSMS and set up log ins and users for the database if you need them for development. Figure 7 shows SSMS connected to both a local and Azure SQL server. In addition to SQL Authentication (user name and password), SQL Database also supports oAuth-style authentication using Azure Active Directory (which could be a whole article in itself). The short version is that it allows you to use Microsoft IDs and/or custom accounts similar to Microsoft ID accounts. In this example, you'll eventually run your services in Azure and they'll be the only “users” accessing the database, so SQL Authentication works great and is plenty secure. Unfortunately, the SSMS GUI doesn't support setting up logins and users for Azure SQL Databases very well yet, so instead of a wizard dialog popping up, you'll get scripts that you'll edit and run. The other place you'll find missing wizards like this is when creating foreign keys, but other than those two instances the rest of SSMS works almost exactly the same as when you're working on a local database.
Once you're happy with your new SQL Database, go into the Azure portal, navigate to the database and change the pricing tier from S2 to Basic. The database stay online while new hardware is spun up. Once everything is ready, Azure switches any existing connections from the old hardware to the new hardware while you work. I've experienced some connection blips with persistent connections, like those SSMS uses, and have had to re-connect, but I haven't experienced issues with apps using Entity Framework or other technologies where connections are pooled and recycled. I recommend that any time you need to modify database structures, regenerate Entity Framework models, or do other resource-intensive operations, you bump the pricing tier up, do your work, and then bump it back down again when you're finished. You may end up paying $6/month instead of $5/month on the database, but your sanity will remain intact.
You may end up paying $6/month instead of $5/month on the database, but your sanity will remain intact.
Feeling the Awesome
This is where the awesome really starts. Your database is up very nearly 24/7/365, your team is sharing it from wherever they are using a secure SSL connection, you didn't need to use your own hardware resources, and the firewall is keeping everyone but your team out. This is when you'll start to notice all the things you don't have to worry about.
Rolling Back
Imagine that one of the developers on your team calls you and says, “I think I just accidentally wiped out the players table and lost all of the player information” or “We just found out that the app has been overwriting the game schedules with garbage for the last few days.” Don't panic! Log into the Azure portal, navigate to your database, and click the Restore toolbar icon. For the Basic tier database, you can go back as far as seven days. Pick any time, down to the minute in the last seven days, click Create, and Azure begins creating a new copy of the database just as it was at that point in time. Connect to the database, make sure everything is as you expect and when you're satisfied, switch over to the restored database. Renaming databases is not supported in the Azure portal or in the SSMS designer, but it is supported in T-SQL script, in the SQL Server Object Explorer inside Visual Studio and via PowerShell scripts.
Backups
You don't need to make sure that a backup is configured and verify that it's working because, even with the lowly Basic pricing tier, Azure maintains at least three copies of your database and has a highly skilled team making sure everything continues to run smoothly. Let's imagine, though, that you're not that trusting and you want to make periodic backups of your data, just in case all three replicas of it somehow go up in smoke or, if think you might want to be able to go back and look at the database as it was years ago. Log into the Azure portal and click on New >Data + Storage >Storage account. Give the storage account a unique name, like hockeyappbackups.core.windows.net. Make sure it's in the same location and resource group as your SQL Database to get the best performance. Choose a pricing tier. Because you want to be extra certain, choose Geo-Redundant, which will ensure that three copies of your backups will be stored in other geographic regions, in addition to the three copies stored in the local region. You'll be charged about $0.05 per GB of data backed up, per month, for all six copies of your backups. Alternately, you can save a few more pennies by choosing a locally redundant storage account and putting it in a different region than your database. Backups will be a little slower, but your data is safe even if one region or the other gets wiped out.
Navigate to your database in the Azure portal and click the Export toolbar button. Choose the storage account that you just created and choose to create a new container. Containers are just logical ways to group files (BLOBs really) so that everything you put into storage isn't in one gigantic pile, which can get unwieldy. Name it something like hockeyappsqlbackups. Enter the password for the SQL admin account and click Create to run the backup. After the storage account and container are set up the first time, backups and restores are a breeze.
Navigate to your storage account in the Azure portal, click on the BLOB icon, choose your container, and within a few minutes, you'll see your backup appear. You can download or delete your backups from here if you like, but there are lots of tools you can use to manage storage accounts. Because I'm a developer, I like using the Cloud Explorer tool I installed in Visual Studio 2015 when I installed the latest Azure SDK.
If you want to configure your backups to happen on a schedule, the easiest way I've found is to do that with a PowerShell script that runs automatically in the cloud. Choose New > Management > Scheduler in the Azure portal to set up scheduled jobs. The Internet has a lot of examples of PowerShell scripts for backing up Azure SQL Databases, so I'm not going to cover that here.
Performance Tuning
Performance-tuning SQL Server is as much black art as science and some people dedicate their careers to being good at it. Although I can't teach you about SQL performance tuning in the space of this article, I can show you some of the tools available to you and show you how Azure is different from working with on-premises SQL Servers.
The first thing to know is that the hardware is abstracted in Azure and probably not set up the way you're used to. Disk drives, for example, are logical drives, not physical drives and are an abstraction built on top of BLOB storage. That means that your data is spread out across several physical spindles and that your data is composed of one or more BLOBs. The SQL Server engine is more than likely running on separate hardware and which instance of the engine is serving up your data can change periodically as computers are patched, rebooted, scaled, and re-allocated. All of this is invisible to you and, for the most part, you won't need to care because you're being guaranteed a certain number of DTUs and how that's achieved is immaterial. If you don't like how your app is performing, you can always try out a different pricing tier. Jump from 50 to 100 DTUs and see what performance looks like there. Using the principle of “Throw more hardware at it,” Azure pricing tiers will let you scale up to some frighteningly powerful hardware, including SSD drives, tons of RAM, and lots of CPU.
Scaling isn't your only option. Remember the Database Engine Tuning Advisor in on-premises SQL Server? Azure has something just like it called the Index Advisor and it's already running on your database. Azure keeps an eye on your database without you lifting a finger and maintains a list of indexes that it thinks should be added or dropped to make your database run efficiently. You can decide whether or not to apply the recommended changes and Azure executes the changes for you.
But what about “real” database tuning like tuning queries with SQL Server Profiler? Although you can tune your queries on a local copy of your database and often figure out how to make the query perform well, you can't run that tool against an Azure SQL Database. In its place, Azure has Query Performance Insight. It's still in preview, but it's a very powerful tool that goes a long way toward forgetting about SQL Server Profiler forever. Unlike the Index Advisor, this tool has to be configured before you can start using it, because unlike SQL Server Profiler, it doesn't steal resources from SQL Server to do its profiling; it uses Azure Storage and separate computer power instead, and that has to be set up. The good news is that all it takes to set it up is clicking a button and agreeing to the terms. Instead of you identifying queries that don't perform well and profiling them, Query Performance Insight tracks the execution of all of your queries for the duration that you specify and keeps a list of the worst performing queries for you. You have some control over how “worst” is determined (maximum resources used, total resources used or average resources used) and over how big a list you want to keep (the default is the top five worst queries). You can log into the portal at any time to check on your database, and dive into the problem queries, as shown in Figure 8.
Click on a problem query and see the SQL statement, the CPU profile, and the DTU usage. Unfortunately, you can't yet try variations of your query in order to attempt to improve it. You still have to rely on a local copy of the database and the Profiler tool for that; the tool is integrated with the Index Advisor, which makes indexing suggestions.
Copying Data from Development to QA or Locally
When your team is ready to create test, QA, and production environments, you can use the Copy toolbar icon in the portal to copy your database to the same or a different server. From there, you can scale and configure the new database independent of the original. Alternately, you can always restore any backups you made to a new environment as well.
Copying a database from the cloud to a local computer is a little more involved. One method is to download a backup from your storage account and restore it locally. Another method uses data-tier applications in SSMS. Connect to your Azure SQL Server with SSMS, right-click on the database you want a local copy of and choose, Tasks > Export Data-tier Application… Choose a local hard disk to save the export to. By default, you get the entire database, but you can choose a subset of tables if you don't need everything. The process takes a while and if you have a large database, you'll want a fast Internet connection. Don't forget to bump up your pricing tier if necessary before doing this, as the operation can use up a lot of DTUs. When the export is complete, you'll have a .bacpac file on your local computer that you can restore to a local SQL Server. Just right-click on the Databases folder of a local server and choose Import Data-tier Application… from the context menu and point to the file.
Production
So far, I've shown you how to use Azure's SQL Database feature for development and briefly discussed testing, QA, and production environments. What's it like to use it for actual production? As you might have guessed, it's awesome! Your servers and databases are being maintained by some of the brightest people in the business and although you don't have all of the control over the hardware and configuration that a seasoned Database Administrator (DBA) might want, you have an extremely robust platform that, in many ways, is better, faster, and cheaper than what a DBA can give you.
Performance
Imagine that performance suddenly drops and the phone is ringing off the hook. In the past, you might track down a DBA and then wait to see how long it takes him to figure it out and get the system back online. Now, in a matter of minutes, you can just throw double, triple, or dozens of times the DTUs at it to keep the system functioning while you check the Query Performance Insight tool to figure out which SQL statements are killing your server. From that point, you might spot a coding issue or identify a missing index in the Index Advisor. And if you do end up calling a DBA, he won't be starting from scratch or feeling the weight of the entire company on his shoulders. If your tiny app somehow becomes a mission critical app to your business or even the next big thing on the Internet, you can scale up almost instantly to meet the demand and scale back down again just as quickly to save on costs. If you have an app that doesn't have a lot of activity all month, but gets hammered at the end of the month, you can run a beefy server for a few days each month without having to pay for it all month long.
Reliability
Microsoft guarantees 99.99% up-time at every pricing tier and reliability goes up as your pricing tier increases. The Standard tier offerings are great for most scenarios, but for mission-critical systems, the Performance tier offerings give you high availability through clustering. You can configure multiple hot-backups around the globe that can not only take over if the primary server fails, but can also be used for reading data, so you can offload things like reporting loads from your primary server. At the Standard and Performance tiers, you've always got at least six copies of your data distributed where you want it around the world at all times.
Inexpensive
If you already have SQL Server computers set up and someone to feed and care for them, and if you're not hosting data for your clients, it may not make sense to jump into Azure with both feet right away. For the rest of us, Azure is a great deal. There's no hardware investment and no hardware to fail or replace. There are no SQL licenses. There are fewer or no DBAs. It's always up to date. It's always adding more features, and always getting faster and cheaper.
Summary
For the most part, Azure is less expensive, better run, and has more features than most datacenters. The fact that everyone, from the smallest one-man shop to global enterprises, has access to all of that is a game changer and it has businesses as well as individuals moving steadily toward the cloud. Azure's SQL Database product is first class, enterprise, and relational database that's professionally maintained and available to anyone with an Azure subscription with just a few mouse clicks. I started working with databases of all kinds in Azure when Azure was a only preview product. I've been using Azure SQL Databases almost exclusively for my development projects as well as a slew of client production systems for over a year now. It has a lot to offer and opens up some really interesting possibilities that I wouldn't have even considered with the regular SQL Server product. I still have SQL Server installed on my computer and I still use it, but the lion's share of my data access is against SQL Databases in Azure and it becomes more important every day. In this article, I took you through some common use cases that are important to developers and introduced you to what's possible in the cloud and yet I've barely scratched the surface. Take it for a spin. Experience the awesome for yourself.
