Azure Basics: SQL Server Services

Support rapid growth and innovate faster with secure, enterprise-grade, and fully managed database services

Microsoft SQL Server is a relational database management system is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).

Characteristics

Azure SQL Database provides built-in?business continuity and global scalability?features. These include:

• Automatic backups: SQL Database automatically performs full, differential, and transaction log backups of databases to enable you to restore to any point in time. For single databases and pooled databases, you can configure SQL Database to store full database backups to Azure Storage for long-term backup retention. For managed instances, you can also perform copy-only backups for long-term backup retention.
• Point-in-time restores: All SQL Database deployment options support recovery to any point in time within the automatic backup retention period for any database.
• Active geo-replication: The single database and pooled databases options allow you to configure up to four readable secondary databases in either the same or globally distributed Azure datacenters. For example, if you have a SaaS application with a catalog database that has a high volume of concurrent read-only transactions, use active geo-replication to enable global read scale. This removes bottlenecks on the primary that are due to read workloads. For managed instances, use auto-failover groups.
• Auto-failover groups: All SQL Database deployment options allow you to use failover groups to enable high availability and load balancing at global scale. This includes transparent geo-replication and failover of large sets of databases, elastic pools, and managed instances. Failover groups enable the creation of globally distributed SaaS applications, with minimal administration overhead. This leaves all the complex monitoring, routing, and failover orchestration to SQL Database.
• Zone-redundant databases: SQL Database allows you to provision Premium or Business Critical databases or elastic pools across multiple availability zones. Because these databases and elastic pools have multiple redundant replicas for high availability, placing these replicas into multiple availability zones provides higher resilience. This includes the ability to recover automatically from the datacenter scale failures, without data loss.

Following are the different SQL Server options available in both on-premise, edge, and cloud:

SQL Server on Premise

SQL Server 2019 continues to push the boundaries of security, availability, and performance for all your data workloads, now with new compliance tools, higher performance on the latest hardware, and high availability on Windows, Linux, and containers. Enhanced PolyBase enables you to query other databases like Oracle, Teradata, and Mongo DB directly from SQL Server without moving or copying the data. And for the first time, SQL Server 2019 goes beyond relational database with Spark and Hadoop Distributed File System (HDFS) in the box—for big data capabilities built-in.

SQL Server on Azure VM

Migrate your SQL Server workloads to the cloud to get the performance and security of SQL Server combined with the flexibility and hybrid connectivity of Azure. Lower your total cost of ownership (TCO) and get free, built-in security and automated management when you register your virtual machines (VMs) with the?SQL Server IaaS Agent extension—at no extra cost. SQL Server on Azure Virtual Machines is part of the?Azure SQL family?of databases.

Azure SQL Managed Instance

Azure SQL Managed Instance is the intelligent, scalable cloud database service that combines the broadest SQL Server database engine compatibility with all the benefits of a fully managed and evergreen platform as a service. SQL Managed Instance has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, providing a native?virtual network (VNet)?implementation that addresses common security concerns, and a?business model?favorable for existing SQL Server customers. SQL Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, SQL Managed Instance preserves all PaaS capabilities (automatic patching and version updates,?automated backups,?high availability) that drastically reduce management overhead and TCO.

Azure SQL Database

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities that are built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.

Azure SQL Server Elastic Pools

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

SQL Server Stretch DB

SQL Server Stretch Database lets you dynamically stretch warm and cold transactional data from Microsoft SQL Server 2016 to Microsoft Azure. Unlike typical cold data storage, your data is always to hand. With Stretch Database, you can provide longer data retention times without breaking the bank. Rather than scaling expensive, on-premises storage, stretch data to the cloud – Azure storage can be up to 40 percent less expensive than adding more enterprise storage. Depending on how often you’ll access the data, choose the appropriate transaction level, and then scale up or down as needed. Stretch Database migrates your cold data transparently and securely to the Microsoft Azure cloud.

Stretch Database provides the following benefits:

• Provides cost-effective availability for cold data
• Doesn't require changes to queries or applications
• Streamlines on-premises data maintenance
• Keeps your data secure even during migration

Azure SQL Data Warehouse (Synapse)

Azure SQL Data Warehouse is a managed petabyte-scale service with controls to manage compute and storage independently. In addition to the flexibility around compute workload elasticity, it also allows users to pause the compute layer while still persisting the data to reduce costs in a pay-as-you go environment.

Azure SQL Edge

Azure SQL Edge is an optimized relational database engine geared for IoT and IoT Edge deployments. It provides capabilities to create a high-performance data storage and processing layer for IoT applications and solutions. Azure SQL Edge provides capabilities to stream, process, and analyze relational and non-relational such as JSON, graph, and time-series data, which makes it the right choice for a variety of modern IoT applications.

Azure SQL Edge is built on the latest versions of the?SQL Server Database Engine, which provides industry-leading performance, security, and query processing capabilities. Since Azure SQL Edge is built on the same engine as?SQL Server?and?Azure SQL, it provides the same Transact-SQL (T-SQL) programming surface area that makes the development of applications or solutions easier and faster and makes application portability between IoT Edge devices, data centers, and the cloud straight forward.

Data virtualization with PolyBase

PolyBase enables your SQL Server instance to query data with T-SQL directly from SQL Server, Oracle, Teradata, MongoDB, Hadoop clusters, Cosmos DB without separately installing client connection software. You can also use the generic ODBC connector to connect to additional providers using third-party ODBC drivers. PolyBase allows T-SQL queries to join the data from external sources to relational tables in an instance of SQL Server. A key use case for data virtualization with the PolyBase feature is to allow the data to stay in its original location and format. You can virtualize the external data through the SQL Server instance, so that it can be queried in place like any other table in SQL Server. This process minimizes the need for ETL processes for data movement. This data virtualization scenario is possible with the use of PolyBase connectors.

Comparison