Azure SQL Database and SQL Server on Azure
Recently I ran into MS SQL Server again and I got more and more interested in what the possibilities are with Azure SQL Database and SQL Server on Azure nowadays. Shure I heard some great stories, however I never had (or took) the opportunity to dive into it myself.
With SQL Server 2014 several interesting Hybrid scenario’s are available, such as using Azure Blob Storage and making backups to Azure. Recently the Azure SQL Database V12 update was released and a key goal for this version was to improve the compatibility with Microsoft SQL Server 2014. On an Azure SQL Database V12 server, the maximum compatibility level possible for a database is now 130, this same maximum applies on Microsoft SQL Server 2016 (currently at pre-release CTP3.2). A lot of new stuff and many interesting scenario’s! Although the possibilities in Azure SQL Database greatly improved in a relative short time, there are still several differences to keep in mind when planning for moving SQL Databases to Azure or when building Hybrid environments. It is important to know the differences between SQL Server and Azure SQL Database and to know the possibilities for Hybrid Scenario’s, more on that later. This article will also share some information on the subjects for the beta exam “473 – Design and Implement Cloud Data Platform Solutions”, which will go into all the details you need to know about Azure SQL Database, SQL Server on Azure and Hybrid environments.
Application design choices: On Prem, Azure or Hybrid?
The information on SQL databases in Azure, SQL Server on Azure and Hybrid scenario’s is overwhelming. The MVA course: Platform for Hybrid Cloud with SQL Server 2014 Jump Start is a good starting point to learn more about Hybrid SQL scenario’s. The article Selecting a SQL Server option in Azure: Azure SQL Database (PaaS) or SQL Server on Azure VMs (IaaS) gives a good overview of the design choices and guidelines for choosing the right SQL option in Azure for your application. Below part of this article and a table summarizing the main characteristics of Azure SQL Database and SQL Server on Azure VM’s:
When designing an application, four basic options are available for hosting the SQL part of the application:
- SQL Server on non-virtualized physical machines
- SQL Server in on-premises virtualized machines (private cloud)
- SQL Server in Azure Virtual Machine (public cloud)
- Azure SQL Database (public cloud)
In general, in Azure there are two SQL options that are optimized for different purposes:
- SQL Database is optimized to reduce overall costs to the minimum for provisioning and managing many databases. It reduces ongoing administration costs because you do not have to manage any virtual machines, operating system or database software. This includes upgrades, high availability, and backups. In general, Azure SQL Database can dramatically increase the number of databases managed by a single IT or development resource.
- SQL Server running on Azure VMs is optimized for extending existing on-premises SQL Server applications to the cloud in a hybrid scenario or deploying an existing application to Azure in a migration or dev/test scenario. An example of the hybrid scenario is keeping secondary database replicas in Azure via use of Azure Virtual Networks. With SQL Server on Azure VMs, you have the full administrative rights over a dedicated SQL Server instance and a cloud-based VM. It is a perfect choice when an organization already has IT resources available to maintain the virtual machines. With SQL Server on VMs, you can build a highly customized system to address your application’s specific performance and availability requirements.
The following table summarizes the main characteristics of SQL Database and SQL Server on Azure VMs:
|SQL Database||SQL Server in Azure VM|
|Total cost of ownership||
Moving (on-premises) SQL Server database to Azure SQL Database
Moving on-premises databases to Azure SQL Database varies in complexity based on database and application design, and the allowed downtime. For compatible databases, migration to Azure SQL Database is straightforward and requires few, if any, changes to the schema and little or no re-engineering of applications. Azure SQL Database V12 brings near-complete engine compatibility with SQL Server 2014 and SQL Server 2016. Most SQL Server 2016 Transact-SQL statements are fully supported in Microsoft Azure SQL Database, including SQL Server data types, operators, and the string, arithmetic, logical, cursor functions, and other Transact-SQL elements that most applications rely on. Partially or unsupported functions are usually related to differences in how SQL Database manages the database or in case of special purpose features such as service broker. Because SQL Database isolates many features from dependency on the master database, many server-level activities are inappropriate and unsupported. Also features deprecated in SQL Server are mostly not supported in SQL Database. Databases and applications that rely on partially or unsupported functions will need some re-design before they can be migrated to Azure SQL Database.
The steps for migrating a SQL Server database to Azure SQL Database are:
- Determine if your database is compatible
- If not compatible, fix database compatibility issues
- Migrate a compatible database
Depending on the application and database design it can be relatively simple to very hard to migrate a SQL Server Database to Azure SQL Database. In scenario’s where, for any reason, it is not possible to move to Azure SQL Database the move from On Premise to SQL Server on Azure VM’s can also be made.
Beta Exam 473 – Design and Implement Cloud Data Platform Solutions
While I was looking into SQL Databases on Azure, I received a Microsoft invite for the beta exam “473 – Design and Implement Cloud Data Platform Solutions“. I started to map the many information sources available for Azure SQL Database, SQL Server on Azure and Hybrid scenario’s to the exam subjects. This was extremely helpful to go through the (over)load of information. I made the document available on my OneDrive, to share with anyone interested or preparing for this exam. It is still an very early draft version, so there is still work to do. Until now the information sources below are used for the document:
- Microsoft Virtual Academy
- Azure SQL Database Documentation
- Technet Library SQL Server
- Channel 9, Azure SQL Database
- Channel 9, SQL Server 2014
The Articles, MVA courses and Channel 9 video’s mentioned in the document will get you up to speed on Azure SQL databases, SQL Server on Azure and Hybrid scenario’s. As a bonus it will prepare you for the 473 exam as well.
The subjects measured for the exam “473 – Design and Implement Cloud Data Platform Solutions” are summarized below:
Design and implement database solutions for Microsoft SQL Server and SQL Database (20–25%)
- Design a hybrid SQL Server solution
- Design Geo/DR topology, design a data storage architecture, design a security architecture, design a data load strategy
- Implement SQL Server on Azure Virtual Machines (VMs)
- Provision SQL Server in an Azure VM, configure firewall rules, configure and optimize storage, migrate an on-premises database to Microsoft Azure, configure and optimize VM sizes by workload
- Design a SQL Database solution
- Design a solution architecture, design Geo/DR topology, design a security architecture, design a data load strategy, determine the appropriate service tier
- Implement SQL Database
- Provision SQL Database, configure firewall rules, configure active geo-replication, migrate an on-premises database to SQL Database, configure for scale and performance
- Design and implement data warehousing on Azure
- Design a data warehousing solution on Azure, design a data load strategy and topology, configure SQL Data Warehouse, migrate an on-premises database to SQL Data Warehouse
Manage database management systems (DBMS) security (25–30%)
- Design and implement SQL Server Database security
- Configure firewalls; manage logins, users, and roles; assign permissions; configure auditing; configure transparent database encryption
- Implement Azure SQL Database security
- Configure firewalls; manage logins, users, and roles; assign permissions; configure auditing; configure row-level security; configure data encryption; configure data masking; configure Always Encrypted
Design for high availability, disaster recovery, and scalability (25–30%)
- Design and implement high availability solutions
- Design a high availability solution topology, implement high availability solutions between on-premises and Azure, design cloud-based backup solutions, implement backup and recovery strategies
- Design and implement scalable solutions
- Design a scale-out solution, implement multi-master scenarios with database replication, implement elastic scale for SQL Database
- Design and implement SQL Database data recovery
- Design a backup solution for SQL Database, implement self-service restore, copy and export databases
Monitor and manage database implementations on Azure (25–30%)
- Monitor and troubleshoot SQL Server VMs on Azure
- Monitor database and instance activity, monitor using dynamic management views (DMVs) and dynamic management functions (DMFs), monitor performance and scalability
- Monitor and troubleshoot SQL Database
- Monitor and troubleshoot SQL Database, monitor database activity, monitor using DMVs and DMFs, monitor performance and scalability
- Automate and manage database implementations on Azure
- Manage SQL Server in Azure VMs with PowerShell, manage Azure SQL Database with PowerShell, configure Automation and Runbooks
Manage SQL with PowerShell
PowerShell is getting more and more important, if not essential, for managing your IT infrastructure. The same applies for SQL Server and Azure SQL Database. If not doing already, get to know PowerShell is the best investment you can do.
At the several PowerShell Communities a lot of information can be found on Powershell and managing SQL with PowerShell. For first hand SQL PowerShell information from Microsoft, the articles “Manage Azure SQL Database with PowerShell” and “SQL Server PowerShell” are a good start.
There are many new possibilities for moving your SQL Databases to the Azure Public Cloud and many more will come in the future. As always good preparation and planning is key for succes and for keeping the cost low. Choosing the right tier and setup is key to get the expected performance, availability and RPO, RTO. Going through the 473 exam objectives is an excellent way to get familiair with the many options that Azure has to offer for your Databases.
Good luck for anyone taking the exam!