The following excerpt is a preview of SQL on Azure Succinctly, written by Parikshit Savjani. Look for this upcoming title to be added in early December to the Succinctly series—Syncfusion’s free library of pithy technology books.
E-book Release in Early December
In recent years, we have witnessed how information technology has contributed to the growth and reach of businesses around the globe. Companies which are primarily technology companies, like Amazon, Facebook, and Uber, have built their businesses solely on the basis of information technology and have grown unimaginably. In today’s world we cannot imagine a business without a website, app, or web presence, but does having a website, app, or web presence guarantee a business will be successful? Until a business is successful, companies aren’t sure whether their investment in information technology and IT infrastructure is fruitful or not.
Setting up and maintaining a website, app, database, or data warehouse incurs a lot of capital and operational expenses (CAPEX and OPEX) for a company whose core business may not be IT. As a result, small and medium business around the world are looking towards cloud solution providers to offer IT as a service and pay only as they use with elastic scale up-down flexibility. Even large enterprises who can afford their own datacenters and infrastructure are looking towards cloud providers since data is growing at a rapid pace and investing in new hardware and infrastructure for new projects, development, and testing may not make sense until the project becomes critical and revenue generating.
Cloud services allow big enterprises to innovate and research at an optimized cost without investing upfront on the infrastructure in their datacenters. Further, when products are designed to run as a service, the product release cycle can be agile and fast. All of these benefits make cloud services an attractive offering and optimal alternative for businesses today.
In this book, I will introduce you to Microsoft SQL Server (RDBMS) Offerings on Azure and talk about the use cases, considerations, configurations, and design optimizations for running SQL Server on Azure to give you predictable performance at optimized cost. To better understand and follow along with the book, you should obtain an Azure Platform subscription. You can purchase an Azure subscription or sign up for an Azure free trial subscription. Prior knowledge of Powershell will make it easier to understand the scripts used in the book. You can use Windows PowerShell to perform a variety of tasks in Azure, either interactively via a command prompt or automatically through scripts. Azure PowerShell is a module that provides cmdlets to manage Azure through Windows PowerShell. You can download and install the Azure PowerShell modules by running the Microsoft Web Platform Installer. The scripts used in the book can be downloaded from the following location: https://bitbucket.org/syncfusiontech/sql-on-azure-succinctly.
This book is primarily focused on instructing individuals with prior knowledge of SQL Server how to manage, optimize, and design applications running SQL Server on Azure. Covering the basics of SQL Server management and administration is outside the scope of this book.
Azure is Microsoft’s cloud computing platform that provides a collection of integrated service offerings, namely analytics, computing, database, mobile, networking, storage, and web. Microsoft is making huge investments in Azure to ensure its cloud offerings provide a similar or even better set of functionality compared to its on-premise or boxed product offerings. Microsoft’s Azure offerings are primarily organized into three categories: Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). The following figure best describes the differences between IaaS, PaaS, and SaaS.
Figure 1: Microsoft’s Azure Offerings
SQL Server on Azure is available in two offerings: SQL on Azure VMs (IaaS) and Azure SQL Database (PaaS). Let us examine each of these offerings and how they are different from their on-premise counterparts.
SQL Server on Azure Virtual Machine (VMs) is no different from SQL Server running locally except for the fact that SQL Server will be running on a VM in a Microsoft datacenter. Azure VMs support running all supported on-premise versions of SQL Server. This also means the application code, scripts, and monitoring running on on-premise versions of SQL Server are also compatible with and can be easily migrated to Azure VM and vice-versa with minimal hassle and modifications. We will discuss more on considerations for running SQL Server in Azure VM and contrast it with its on-premise counterpart in later sections and in more detail in later chapters.
Azure SQL Database, which is the Database as a Service offering of SQL Server, is a newly designed cloud service in Azure suitable for cloud-designed applications. In this offering, the database is provisioned and hosted on Microsoft datacenters, and you do not need to worry about setup, configuration, resource tuning, high availability, or backups for SQL Server. The Azure portal provides the connection string to connect to the SQL Database, which is used by the application/DBAs to create the schema and to insert, manipulate, query, or administer the data. When this service was first introduced, it was still catching up with its on-premise counterpart in terms of functionality and features, but the latest version of the service exceeds SQL 2014 with support for columnstore indexes, row security, query store, and more. Azure SQL Database is better suited for SaaS applications using scale-out patterns. We will discuss use-case scenarios for Azure SQL Database and its design consideration in more detail in following chapters.
The following table compares the SQL Server on Azure VMs (IaaS) versus Azure SQL Database (PaaS) in terms of use-case scenarios, features and cost. It can be viewed on Microsoft’s site at the following link: https://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-iaas/.
Table 1: Comparing Azure SQL Database (PaaS) and SQL Server in Azure VM (IaaS)
| Azure SQL Database (PaaS) | SQL Server in Azure VM (IaaS) | |
| Best for | New cloud-designed applications that have time constraints in development and marketing. SaaS applications. | Existing Dev Cert and test environment applications that require fast migration to the cloud with minimal changes that do not need on-premise nonproduction SQL Server hardware. SQL Server applications that require accessing on-premise resources (such as Active Directory) from Azure via a secure tunnel. Rapid development and test scenarios for disaster recovery for on-premise SQL Server applications using backup on Azure Storage or AlwaysOn replicas in Azure VMs. |
| SQL Support | Near SQL-like functionality in preview. Application should be supported on Azure SQL Database. Existing environments cannot be migrated to Azure Database unless the application is rewritten. | All SQL versions (SQL 2005 – SQL 2014) are supported, similar to on-premise functionality. Existing applications can be migrated with minimal coding effort. |
| Size Constraints | Databases of up to 500 GB in size. | Unlimited database size. |
| Total cost of ownership | Eliminates hardware costs. Reduces administrative costs. | Eliminates hardware costs. |
| Business continuity | In addition to built-in fault tolerance infrastructure capabilities, Azure SQL Database provides features, such as Point in Time Restore, Geo-Restore, and Geo-Replication to increase business continuity. For more information, see Azure SQL Database Business Continuity. | SQL Server in Azure VM lets you to set up a high availability and disaster recovery solution for your database’s specific needs. Therefore, you can have a system that is highly optimized for your application. You can test and run failovers by yourself when needed. For more information, see High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines. |
| Hybrid cloud | Your on-premises application can access data in Azure SQL Database. But from SQL Database we can’t access resources on-premise | With SQL Server in Azure VMs, you can have applications that run partly in the cloud and partly on-premises. For example, you can extend the on-premises network and Active Domain Directory to the cloud via Azure Network Services. In addition, you can store on-premises data files in Azure Storage using the SQL Server Data Files in Azure feature. For more information, see Introduction to SQL Server 2016 Hybrid Cloud. |
With the addition of SQL on Azure Offerings, enterprises today have four options for running SQL Server in their hybrid cloud environments:
It is important to understand what the merits and demerits are for running SQL Server on Azure for organizations to be able to decide which scenarios are suitable for running SQL Server on Azure versus another solution.
The following figure compares the above options for running SQL Server in an environment in terms of infrastructure and administration cost. It can be viewed on Microsoft’s site by using the following link: https://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-iaas/.
Figure 2: Motivation for Running SQL Server on Azure
As we move from physical to virtual to Azure, the infrastructure and administration cost of SQL Server is reduced. Running in VMs also gives us elastic scale up/down flexibility depending on the user workload, which gives us additional benefits in terms of cost since we pay only for the resources which we consume and can scale down or shut down when they are not in use.
Elastic Scale up/down can be particularly useful in applications which have skewed workloads, such as software for retail stores which see higher sales during Thanksgiving or holiday seasons. Such workloads create higher demand for computing power, and servers need to be scaled up during the applicable time period. Another good example would be test or cert servers, which need to be of equivalent capacity to production servers to provide accurate application behavior, but only need to be active during the testing phase and scaled down or turned off when not in use.
Another benefit of running SQL Server on Azure is lower turnaround time for setting up and configuring SQL Server or databases. The turnaround time is quickest for Azure SQL Database since you do not need to worry about the server, OS, setup, or configuration, and within a few minutes a database is provisioned and available to accept new connections. For SQL Server on Azure VMs there are images available in Azure gallery which automate the entire installation and configuration of SQL Server, allowing for minimal steps and reduced time for spinning a SQL Server instance.
The primary motivations for moving SQL Server workloads to Azure are:
• Low hardware and data center costs.
• Low administration costs.
• Elastic scale.
• Low turnaround time for setup and configuration.
About the Author
Succinctly series author Parikshit Savjani is a Microsoft Certified Solution Expert and Microsoft Certified Trainer working as a Premier Field Engineer with Microsoft, which includes consulting, educating, mentoring, and supporting the premier customers of Microsoft. He specializes in SQL Server and business intelligence (SSAS, SSIS, and SSRS). His blogs can be found at sqlserverfaq.net and MSDN Blogs.