SQL Server and Migration to Azure SQL for Data Marts and Data Warehouses
Microsoft released SQL Server 1.0 in 1989 as a Sybase port to Windows. Since then, Microsoft steadily enhanced SQL Server. As a relational database product, SQL Server relies on SQL (Structured Query Language) and the T-SQL programming language to store, retrieve, and manipulate data in a structured way. SQL Server has enormous presence and perhaps the richest ecosystem and vertical applications and connectivity of any data warehousing database. The SQL query language has won the query wars (SQL Server also offers MDX, an OLAP query language.) Expect the Microsoft SQL Server product to have a heavy market presence for a long time. Microsoft sees the importance and potential of the cloud software model and offers a cloud-based SQL Server product called Azure SQL Database.
If you have an on-prem data mart in SQL Server, your choices for cloud migration will be:
- Azure SQL, a simplified SQL Server implementation with size and other restrictions optimized for cost (may be lift-and-shift, there are T-SQL and other restrictions.
- Azure SQL Datawarehouse, an MPP database optimized for large data and large scanning queries. This requires migration that is functionally reimplementation in most use cases.
- Amazon RDS SQL Server, a DBaaS offering that is by far the largest SQL Server data mart host for data larger than 1TB.
- Migrate to another cloud data warehouse – AWS Redshift, Snowflake, GCP BigQuery. These are scaleup options that need to be reviewed as part of strategy and the current initiative. This requires reimplementation.
- Cloud implementation of SQL Server, where the user installs and manages SQL Server at a cloud provider. Consider this an interim approach; we (EDW.Cloud) have only seen this in a hybrid environment where the IT effort to manage ultimately leads to migration to other choices. This approach is usually driven by gaps in the hosted DBaaS product capabilities such as replication, and the usual resolution is to rearchitect the application to fit a cloud model. GCP SQL Server is an example of this, as is AWS hosted non-RDS SQL Server.
EDW.Cloud Migration Strategy Recommendations for SQL Server
|SQL Server on prem or in cloud DBaaS offerings in the cloud makes sense if it fits general IT strategy up to ~50TB/50K IOPS. SQL Server is especially strong in supporting data marts and departmental BI. SSD and large RAM will help with this. Review size limitations of Azure SQL (1TB storage) to see if this is a stopper.||Follow your overall IT strategy for legacy on-prem and DBaaS SQL Server marts that perform to requirements. The product is active and low risk.
Carefully review requirement choices between on prem and hosted. Review size limitations of Azure SQL (1TB storage) to see if this is a stopper.
|Large scale data warehousing on-prem in SQL Server can hit scaling walls. If you’ve reached that point, it is worth reviewing the alternatives – including Azure SQL Data Warehouse if it fits your cloud strategy (carefully review scaling limits such as 32 concurrent queries), and other cloud warehouses.|
An IT organization (ITO) can internally host its own on-premises SQL Server database resources. However, this means that the organization must manage, support, and maintain – and when necessary upgrade – the required hardware and software. Microsoft releases new SQL Server versions every few years and SQL Server upgrades / service packs more often. This eventually requires increased hardware resources. Combined with data volume growth, these issues lead to large and increasing overhead costs: hardware, time, labor, etc. An ITO could accept these costs when it had no alternative. Times have changed, however. An ITO can shift its database resources to a cloud vendor. The vendor will handle all the database infrastructure issues, so that the ITO can focus on its own business rules and custom database development.
An effective, reliable database migration from on-premises to the cloud requires careful planning and execution. First, the ITO must measure its existing data, hardware, software, and business resources, and then plan its future requirements. Microsoft will most likely offer and support SQL Server as an on‑premises product for “a long” time, so the organization will not have immediate pressures from an orphaned product.
An on-premises to Azure SQL Server database move could require changes in existing stored procedures, user-defined functions, etc. Additionally, since the ITO will move its database layer from on-premises to the cloud, the database connection strings that point front‑end applications to that database layer must likely change as well.
See this resource for a detailed description of the migration process. We can describe a way to approach the process here, however. First, the ITO would verify source database / Azure SQL Database compatibility with the Microsoft Data Migration Assistant (DMA) tool. This tool pinpoints compatibility problems to solve in an Azure version of the original database resources. The ITO would probably want to clone its existing development / QA / production / etc. database infrastructure in the Cloud. Azure makes this easy. First, through a local SQL Server Management Studio (SSMS) instance in the original database resources, the ITO should export all existing on-premises databases to a local “backup” file. The ITO would then build a new “skeleton” database resource in Azure. Next, the ITO would make sure to configure the firewall to allow the IP address of the local SSMS. From the local SSMS, the ITO would log into the new Azure resource with the server name, login, and password of the new Azure resource. The Azure resource should appear in the local SSMS, and the ITO would then import the exported database file built earlier. Since the on-premises SSMS can see the Azure database, development / testing / etc. can proceed as before. For Azure, review limits of Azure SQL databases here especially the storage size, and here for Azure SQL Data Warehouse.
In a real-world situation, the ITO would first design required changes, as flagged by the DMA tool, to the stored procedures / functions / etc. to cover the Azure T-SQL requirements. Then, the ITO would probably test and time the migration process in an Azure development environment, to validate everything and help estimate the time requirements. Within this expected time budget, the ITO would
- temporarily deactivate the production application
- back up the source database resources
- restore into the new Azure resources
- repoint the front-end to those new resources
- reactivate the production application
to make the formal migration happen. Then, the ITO would test all relevant database functionality – stored procedures, functions, etc. – to verify that the new Azure resources work as expected. Fix any detected bugs and apply these repairs to the new production Azure system.
Azure makes the source database to cloud migration process clean, cheap, and easy. As a cloud resource, the ITO can configure Azure to its needs, balancing cost with performance and data volume.
Microsoft Azure SQL as a Migration Target
Since Microsoft Azure SQL closely resembles the on-premises version(s) of SQL Server, Azure becomes a compelling alternative to on-premises SQL Server. Offloading a database server to the cloud cuts overhead; database expenses will match the actual use of that database much more closely.
This service is optimized for a battery of many small SQL Servers. Review carefully if this is a fit for your data mart. Microsoft recommends Azure SQL Data Warehouse instead of Azure SQL for larger implementations (and large here will seem like small to medium data marts for DW professionals.)
Amazon AWS RDS SQL Server as a Migration Target
AWS RDS SQL Server uses standard SQL Server, with some restrictions to allow offering DBaaS and effective automated management. with on-premises version(s) of SQL Server, RDS SQL Server becomes a compelling alternative to on-premises SQL Server with less restrictions than Azure. Offloading a database server to the cloud cuts overhead; database expenses will match the actual use of that database much more closely and there is much more flexibility and scaling to appropriate size is simple. Major IT costs such as DBA support, admin, backups and such are dramatically reduced.
Migrating the AWS is aided by the AWS migration services, see overview and details. Review carefully the RDS SQL Server limitations, especially 16TB storage and the likely need to install SQL Server on an additional node to support SSAS, SSIS, SSRS, etc.
Migration Support Resources
YouTube has plenty of vids to guide the ITO through the on-premises to Azure cloud migration process.
See Stack Overflow for questions about migration edge cases.
Google can help as well.
See this page for a more detailed description of the migration process.
The Microsoft Data Migration Assistant (DMA) tool verifies source database / Azure SQL Database compatibility.
Microsoft describes Azure Data Warehouse T-SQL limitations and other Azure Data Warehouse limits here. This is always going to be a migration unless basic generic SQL without T-SQL is used.