Viable Azure Options when working with SQL Server

Question

Looking for guidance with SQL Server and Azure options.

We have several different environments/processes that we are trying to replicate to Azure using whatever the flavor of SQL Server (Azure SQL, Azure SQL Database, Azure SQL MI, Azure VM with SQL, etc...).

An always-on SQL Server environment, which has one primary database. Our existing configuration has other databases, which are used primarily for staging data before it gets loaded into primary. For loading data into staging and from staging into primary, we use a combination of Powershell and T-SQL. Databases are between 10 and 30GB in size. Some loads take place weekly, others - quarterly. There are also external consumers of this data, which request the data out using Web service APIs. There are 15 subscribers, which consume the dataset once/twice on a daily basis (about 2-3M Rows per request), so outbound traffic may be a factor for consideration.

An environment that has a primary SQL Server database, where data is loaded weekly. The source of data is a set of delimited files which are uploaded to the server. We use a combination of Powershell and T-SQL for these loads. This database is fairly large in size (about 150-200G, with a potential to double in size over the next year). The primary database is then used for querying/reporting (a small group of users, not very frequently, usually after the load). Additionally, it is also used to generate subset databases (about 100G in size, several of them), which are distributed to external consumers in the form of DB backups. These are generated monthly.

From reading about Azure/SQL Server options, Azure VM with SQL Server may be a good candidate for #1?

For #2, we are looking for a solution that might not necessarily be always-on. Ideally, it would be auto-scalable in terms of being able to satisfy the needs of loads, queries, and extracts, and then spin down the unneeded resources until the next load cycle/request.

In either case, would you mind sharing your experiences, knowledge, the pros/cons of various setups? If you could, possibly suggest alternative ways of structuring our processes? Azure is new to us and there may be other ways of solving similar kinds of problems, which better yield to Azure implementations. We are not 100% locked on SQL Server, but our data is fairly structured and (relational) and our code (T-SQL, Powershell, and application) is pretty SQL-heavy.

Answer 1

Hi @Alex Vilner Thank you for posting your question on Microsoft Q&A forum and for using Azure services.

My understanding is that you are looking for guidance on what type of databases is best for your migration to Azure based on your scenarios above. Please let me know if my understanding is not correct.

In addition to @Alberto Morillo post above.

Your business might have requirements that make SQL Server on Azure Virtual Machines a more suitable target than Azure SQL Managed Instance.

If one of the following conditions applies to your business, consider moving to a SQL Server virtual machine (VM) instead:

You require direct access to the operating system or file system, such as to install third-party or custom agents on the same virtual machine with SQL Server.
You have strict dependency on features that are still not supported, such as FileStream/FileTable, PolyBase, and cross-instance transactions.
You need to stay at a specific version of SQL Server (2012, for example).
Your compute requirements are much lower than a managed instance offers (one vCore, for example), and database consolidation is not an acceptable option.

Choose SQL Server in Azure VMs if:

You are looking to "lift and shift" your database and applications with minimal to no changes.
You prefer having full control over your database server and the VM it runs on.
You already have SQL Server and Windows Server licenses that you intend to use.

Choose Azure SQL Database if:

You are looking to modernize your applications and are migrating to use other PaaS services in Azure.
You do not wish to manage your database server and the VM it runs on.
You do not have SQL Server or Windows Server licenses, or you intend to let licenses you have expired. Migrate a SQL Server Database to Azure

Also, for the migration, it’s going to depend on what you were using in SQL Server before. E.g. – do you have third party apps that need access to the OS? (Then VM), do you use SQL Server instance scoped features (Database mail, service broker, ML services, SQL Server Agent, etc.)? (Then SQL MI), OTHERWISE, the best place to land will likely be Azure SQL Database Hyperscale.

I recommend applying for the AMMP program, which will help you get a free guidance and analysis. Azure.com/ammp

Hope that helps

Please feel free to let me us know if you have additional queries.

Regards
Oury

Answer 2

For the first scenario you can use the following tools/options:

• Azure SQL Data Sync (not available for Azure Managed Instance and Hyperscale, but available for Azure SQL DTU-model and vCore Model). You can sync monthly, weekly, daily, hourly, and more). It is a free tool.
• Azure Data Factory can help you upload incremental data using watermarks and "slices"
• SQL Server replication can have Azure SQL databases involved. Azure SQL Database can be the push subscriber of a SQL Server publisher and distributor.

You can definitely schedule scale up/down of the SQL Azure resources depending of when ETL processes or users consumed data. If you can identify time windows when usage of resources drops, you can schedule scale down of resources using Azure Automation. Azure SQL Databases cannot be paused (equivalent to shut down a VM).

About the second scenario, you will need to use Azure Synapse (formerly Azure Data Warehouse) SQL Pools and get used of Azure Data Factory to transfer those 200 GB during each ETL. If you are using Columnstore indexes or In-Memory tables, this can be done fairly quick. Do not use incrementally clustered indexes with Azure Data Factory. Azure Synapse can be paused and can be scaled as needed.

You may need to install this gateway on-premises to get Azure Data Factory access your on-premises data sources.