Door: Mark van der Haar 21-7-2023
We once attended a demo where a cloud database was deployed. Five minutes and it was there. That makes your mouth drop. Doesn't it? Or does this paint a slightly too simplistic picture of what is involved in the - proper and secure - deployment of a cloud database? In this blog, Mark van der Haar shares a number of points to keep in mind during an Azure SQL deployment.
It starts with the choice. Are you going for an Azure SQL database, a Managed Instance or SQL Server on Azure VM? If you choose a SQL Server on a VM, the migration is relatively easy, but then you don't have the benefits of the cloud. An Azure SQL database does offer those benefits. For example, you benefit from automatic patching and you spend less money on your data services. But an application cannot simply use an Azure SQL database to replace an on-premises SQL database. That requires the necessary - thorough - modifications. You may also not want to simply transfer your application to an Azure VM and want to use more cloud capabilities here as well. In doing so, you're taking on a hefty migration path. Azure SQL databases have a number of limitations. Consider the lack of an SQL agent, CLR or the ability to make a copy-only backup. Microsoft's Managed Instance is an intermediate form, combining the best of both worlds, but that solution is quite expensive.
Good. The choice is made, you go for the Azure SQL database. But then. In a demo environment, you "quickly" choose a service tier, usually the provisioned single database. In real life, that choice is a bit more complex. Of course you can adjust many things afterwards, but moving away from hyperscale - the tier for very heavy databases - that's not possible, for example. So you really have to seriously know what you're choosing. For example, there are also disadvantages to a serverless tier. Serverless has an auto-pause functionality, which can reduce costs, but serverless can only be used with the vCore purchasing model and not with the DTU purchasing model. Moreover, for smaller databases, serverless can still be a more expensive solution. In short, many choices that affect costs and there is no 123 simple advice on that.
A database obviously needs to be accessible in a secure way. If you build the application in Microsoft Azure, then in your Azure SQL database you can turn on the option "Allow access to Azure services. However, that means that all Azure services can access your database, including Azure services from other companies. To prevent that, you need to protect your database with a firewall. Keeping track of all IP address changes, requires robust maintenance of that firewall. That increases the chance of error. You avoid that by working with virtual networks (VNET) and network security groups (NSG). Then the application services are in a separate VNET and you can use VNET peering to make the VNET of the databases available. Connection from on-premises environments you control through an NSG. Finally, you need to connect to an endpoint. To do that securely, you need to use an express route or VPN tunnel.
Identity-access management also works a little differently with an Azure SQL database. The way to create a login from Azure Active Directory has a slightly different syntax. Azure SQL databases make much more use of contained users, or users without a login. This is how you increase the portability of a database. This requires a somewhat different approach to authentication. With Azure, though, it is easier to use MFA again.
With an Azure SQL database, you cannot use "USE database. Because you can't access a table with a four-part name, you also can't make a join between different databases. That makes it different for developers to connect to an Azure SQL database than to SQL Server on Azure VM. You can only pull data from one database at a time with a query, and the join of that data is done on the client. Or you have to use the elastic query feature, which does offer join capabilities. This is a feature that does need to be set up and it requires application-side modifications.
You also need to arrange something for auditing. With Azure SQL database, there are multiple ways to store auditing data. Besides the "normal" storage, in Azure you can also choose the destinations: log-analytics or event-hub. Whereas with on-premises solutions you had to use third-party software to automatically search the output for issues (e.g. ArcSight), now by sending the audit to log-analytics you can also search logs automatically already. A common problem with audit logs is the large amount of data you collect. Combined with Azure Defender for SQL - formerly Advanced Data Security, so the abbreviation remains ADS - you can use classification to limit the output. This classification of fields within the database allows you to audit only for sensitive data. This classification is also possible today with on-premises databases, but it is not yet widely used.
Monitoring of SQL Server is on-premises part of a general monitoring tool, such as SCOM or HPOM, or a SQL-specific monitoring tool is used, such as Redgate, SQL Sentry or Idera. The latter tools can also monitor an Azure SQL database. But for an Azure SQL database, you can also use an Azure-specific tool, Azure Monitor SQL Insights. Which, in turn, you can integrate into Azure dashboards. Keep in mind that a new monitoring tool often requires a lot of fine-tuning. Such a tool initially generates many false positives. The road to a situation where you only get reports that really matter is often long.
The ways to migrate your data to Azure are diverse. Think Data Migration Assistant (DMA), SQL Server Migration Assitant (SSMA), Azure Data Migration Service (DMS), Replication, BACPAC, Bulk Copy Program (BCP), SQL Data Sync and Azure Data Factory (ADF). This is not even an exhaustive list. Certain options can handle other DBMSs such as Oracle, others cannot. They are all different in terms of downtime, and only SQL Data Sync gives the ability to continue editing databases side by side. The options are also different in terms of price. DMA is the only one that can be used for pre-assessment. Thus, there are many other differences. The comparison provides enough material to write another follow-up blog about.
So a five-minute demo like this really gives a slightly too simplistic picture of setting up an Azure SQL database, with all the choices and settings you need to think about. Especially if you're transferring an on-premises SQL Server database to an Azure SQL database, there's a lot involved. The experts at OptimaData can help with this. Feel free to contact us.