Three Useful Features In Azure-SQL For Growing Environments

By: Tim Smith   |   Updated: 2016-08-01   |   Comments   |   Related: > Azure

Problem

We're building a small application that will need a functional SQL database, and expect that it will grow over the next five years, and possibly significantly. Since we are a small environment (or startup), what is a good resource to use and what considerations of this resource would you suggest?

Solution

For a small or moderate environment that will expect some growth in the long run, in this tip I look at some features that SQL Azure offers that may be worth considering, from scaling up or down the database, to migrating to a full service environment like SQL Server and how to know when to make those decisions.

Scaling SQL Azure

Through Azure's interface, we can scale Azure-SQL as needed. At the time of this article, Microsoft provides Developers with "Recommended" plans and the above costs reflect the current time of this article and US Dollar price. Prices will more than likely change and users of Azure can use other currencies.

Set-AzureRmSqlDatabase -ResourceGroupName "OurResourceGroupName" -ServerName "OurAzureSQLServer" -DatabaseName "OurAzureDatabase" -Edition Standard -RequestedServiceObjectiveName "S0"

Whether through the interface, or the PowerShell commands provided by Microsoft, we can scale the Azure-SQL version up or down as needed. We can commit to a higher database tier if our application requires it, meaning that we can set a schedule to scale Azure-SQL higher or lower relative to needs. In these situations, I would recommend using tools like Task Scheduler, a configuration table, or the Quartz .NET library with C# to automate scaling as needed.

One word of caution with Azure PowerShell commands: they tend to change frequently, so when wrapping the calls with functions, I highly suggest strict try-catch-finally where the catch is logged in case of changes. For this reason, if scaling up on a schedule greater than a day, such as increased traffic on weekends, be careful about automation without interface access if a PowerShell command fails. Additionally, if you do scale up or down before a time window, leave enough time for this window. One error you may see from time to time when scaling up or down and running process is, A severe error occurred on the current command. The results, if any, should be discarded. In many cases, the former error does not prevent the process that caused it, but I would be extra cautious about running transactions while scaling.

Restoring from SQL Azure to SQL Server

Through Azure's interface, we can export the data tier application in the form of a Bacpac file, and import this file into SQL Server. The file will import both the database schema and data.

Azure can be a useful place to store a small database for both mid-range profit and use cases (especially for application POCs, targeted applications/processes, average applications, etc.), but when it comes to high performance customization for a highly profitable enterprise, it does not outperform SQL Server and its lack of DBA features quickly become a weakness. When restoring from Azure to SQL Server, I use the following checklist:

1. Verify database integrity with DBCC CHECKDB.
2. Reset configuration settings to the preferred design, such as growth, recovery model, etc.
3. Add maintenance jobs, if applicable.
4. Replace some clustered and nonclustered indexes with clustered column store indexes if using a later edition of SQL Server.

Make no mistake, Azure - like AWS - offers a great way to test ideas without dropping billions in currency and evaluating if one's market is open to them. If the idea is great and is adopted, one can commit to an alternative set up that allows for customization, if a team determines that is needed. This is an example of how Azure can assist startups and there are many options that are offered for validated startups with cloud technology. In some cases, a generic set up may be useful (for an example, configuration databases seldom need specific customization or ETL staging databases). Since there are similarities between the Azure and SQL Server, one does not have to completely learn all of one or the other again.

For environments that commit to using Azure-SQL, the benefit of importing the database into SQL Server (locally) is faster functional testing. Even when test environments exist in Azure, connections can be slow, pause, or break. These are useful tests for performance, but can interfere with "Does the column appear on the screen in my application?" which only seek to test if the function of the database schema is correct. In addition, keeping local copies (or files) can provide another disaster recovery resource.

SQL Azure Alerts

Azure allows us to monitor some metrics, which we can specify for purposes such as scaling the version, changing a schedule, or possibly migrating Azure-SQL into SQL Server, if there's a strong case for it. To avoid noise, set meaningful alerts with strict conditions and precise thresholds, while delineating a time period for these. For an example, if we see one 99% DTU spike four hours in a given day, do we want to be alerted on this, if our action will be nothing? No. If we want to track it for the sake of tracking, we could set an email that only tracks that alert (or grab the data from sys.dm_db_resource_stats). These alerts can also be configured for the purpose of scaling down; we may almost never use resources, except during certain periods, so we may be able to save money on a lower cost tier.

In this tip, we looked at some of the available tools with Azure-SQL and what we might want to consider when using these tools. Azure-SQL is a great tool for beginners, startups, and moderate SQL environments.

Next Steps

• Keep tabs on the performance of the Azure-SQL databases, as this will help you decide when or if to scale them higher or lower.
• Each database that I restore locally from Azure, I have a custom script for verifying its integrity, resetting configurations, etc. If you restore on a schedule frequently, design and re-use this script when importing.
• Set meaningful alerts when considering scale and scheduling.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips