How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017
The following is reprinted with permission. For more on this topic, check out Programming Microsoft's Clouds: Azure and Office 365
The Difference Between SQL Server and SQL Azure
Unlike SQL Server where your Databases are the only ones on your Database server, SQL Azure may use a single physical server to host Databases from many different customers. This difference in approach is fundamental—SQL Azure is inherently multitenant, and it needs to share physical resources among all clients of the service. This fact underlies many of the feature differences between SQL Server and SQL Azure; although, a tremendous overlap exists in functionality and compatibility between the two.
SQL Azure is specifically intended to feel familiar to developers using SQL Server, because it takes a recognizable approach to communication, authentication, and development. In many cases, the difference between programming against SQL Azure and SQL Server is the value placed in the connection string. Using the Tabular Data Stream (TDS) protocol for communication, using SQL logins for authentication, and programming with Transact SQL (T-SQL) are familiar to any SQL Server developer.
It is helpful to understand the differences between the on-premises SQL Server and the cloud-based SQL Azure by comparing the communication architecture of each one side-by-side, as shown in Figure 11-1. For an on-premises SQL Server, you typically have your applications talking directly to SQL Server across your local area network using the TDS protocol over TCP/IP or via HTTP endpoints. This is different from how your on-premises applications communicate with SQL Azure. As you can see, your applications must now call out, possibly through your own network’s firewalls, and reach the SQL Azure Gateway via the Internet, and they can use only TCP/IP.
In addition, unlike for SQL Server, which can communicate on a configurable port without transport security, communication with SQL Azure must happen using SSL, and it must occur via port 1433. Furthermore, it is already at the Gateway that validation of your login occurs. SQL Azure, unlike SQL Server, supports only SQL Server Authentication (using a login and password, not Windows Integrated Security).
The Gateway is also where another firewall enters the picture—the SQL Azure Firewall. This firewall enforces IP-level security, catering only for IP addresses and address ranges that you explicitly define to be allowed access to your SQL Azure Server.
Once past the Gateway, a connection to the backend data node hosting your SQL Azure Database is made, using the Gateway as a proxy. Each backend data node runs a single instance of SQL Server containing its own Database, where this database is divided into partitions. Each partition contains a SQL Azure user Database (e.g., your database).
Aside from this single instance, each SQL Azure user Database is replicated a further two times, on two different backend data nodes. These replicas provide no load-balancing services to handle user queries—they are solely to provide SQL Azure’s very high availability by enabling failover to one of the two replicas should the node hosting the primary node go down.
However, an element of load balancing does exist in this picture. If one backend node becomes overloaded by the workload of its shared tenants, then the underlying infrastructure (called the SQL Azure Fabric) may change the primary replicas of some users’ Databases to one of the backup replicas found on a different backend node, converting this primary into a secondary replica for those Databases.
Beyond providing relational Database capabilities to your on-premises applications, observe how SQL Azure is well positioned to provide support for off-premises applications (like partners) and, more important, applications hosted within Windows Azure.
Aside from the architectural differences, numerous feature differences become apparent during development on, administration of, and licensing for SQL Azure that you should be aware of. The following sections explore the notable differences for these.
In the spirit of maintaining a familiar feel, SQL Azure has a high degree of feature parity to SQL Server with respect to the needs of the developer. In terms of support for T-SQL grammar for Data Definition Language (DDL), Data Manipulation Language (DML), and general programmability, you can find a mixture of levels of support that vary between full parity with the SQL Server; to partial parity (where some options are omitted, or added uniquely to support SQL Azure); to data types, functions, operators, statements, procedures, and system tables/views that are flat-out not supported at all.
DDL encompasses the features you need to define your Database schema and objects. For DDL, you can find near full-feature parity for DDL statements like Create/Alter/Drop affecting Database objects such as Tables, Views, Stored Procedures, Functions, Triggers, User Defined Types, and Indexes. There are some notable differences though, in that they generally lack support for some specific features that exist only in the on-premises versions of SQL Server. The following are a couple of stand-out differences:
- The Common Language Runtime (CLR) integration is not supported within SQL Azure, which means stored procedures, triggers, and user-defined functions written in a .NET language are not supported in SQL Azure. This limitation has its roots in the multitenant nature of SQL Azure, aiming to protect tenants from the accidental or intentional misuse of CLR objects by other tenants on the same server.
- Extended Stored Procedures, such as xp_sendmail, are not supported. This, too, is a limitation designed to ensure tenant isolation.
- Table partitioning (horizontal partitioning that spreads table/index data across multiple file groups within a single Database to improve performance on large datasets) is not supported.
- A clustered index is required on all SQL Azure tables. SQL Azure does not support heap tables, and INSERT operations will fail until a clustered index is created.
- Snapshot isolation is enabled and cannot be turned off at the Database level.
SQL Azure supports almost all the system data types found in SQL Server from numeric types such as int and real, date and time such as datetime, character strings such as char and nvarchar, and binary strings such as varbinary and image. It even supports some of the more specialized data types such as the spatial data types geography and geometry, hierarchyid, and xml. Although the XML data type is supported, typed XML and XML indexing are not.
Data types that are not supported include: CLR user-defined types and user-defined aggregates, and the FILESTREAM attribute on varbinary(max) typed columns is not supported.
Data Manipulation Language (DML) encompasses the features for performing CRUD (create, read, update, delete) operations against your Database data. The standard querying of tables and views accomplished using the SELECT clause, TOP, and the FROM statement, and optionally WHERE, ORDER BY, GROUP BY, or HAVING statements function exactly as they do in SQL Server. In addition, you can find support for the following:
- EXCEPT, INTERSECT, and UNION
- Common Table Expressions
- FOR XML and FOR BROWSEs
- MARS (multiple active result sets)
SQL Azure supports all the same Query, Table, and Join Hints as the on-premises SQL Server, with the exceptions of MAXDOP (which it always treats as 1) and PAGLOCK and REMOTE (neither of which it supports).
The largest difference to query support is that Full-Text search (for example, using CONTAINS, FREETEXT, and so on) is not supported by SQL Azure. Character-based searches with LIKE are supported.
For data modification, UPDATE, INSERT, DELETE, and MERGE are all fully supported, but BULK INSERT is not supported.
In terms of programmability, consider the T-SQL statements you typically use to define logic within stored procedures and functions. The following are all supported:
- IF. . .ELSE, BEGIN. . .END, DECLARE
- Exception handling with TRY. . .CATCH, as well as THROW, is fully supported.
- CAST and CONVERT
- Use of tempdb and creation of temporary tables
In addition, SQL Azure provides full support for all aggregate functions (AVG, MAX, and so on), all ranking functions (RANK, ROW_NUMBER, and so on) and all ODBC scalar functions, as well as most scalar functions found in SQL Server. Only the row set functions CONTAINSTABLE, FREETEXTTABLE, OPENDATASOURCE, OPENQUERY, OPENROWSET, and OPENXML are not supported.
The feature most likely to catch you unaware as a developer is SQL Azure’s transaction support. Local transactions that work with only the current Database (such as those using BEGIN_TRANSACTION. . .END, COMMIT, and ROLLBACK) are fully supported, but distributed transactions that span multiple Databases are not.
Aside from the lack of physical server and application management and patching, hosting a relational Database in the cloud naturally introduces changes to how Database administration is performed. SQL Azure has quite a few changes from SQL Server that you should be aware of.
The architecture of a SQL Azure deployment is logical, rather than physical. When you create a SQL Azure Database, what you actually manage are three logical entities: Subscription, Server, and Database (see Figure 11-2).
A Subscription encompasses all your Windows Azure platform services, such as hosted services, storage, caching, and SQL Azure, and provides the context under which use of these services is metered and billed.
Unlike a SQL Server, an Azure Server does not represent a physical or virtual machine running the Database services. In Azure, a Server is a logical grouping of Databases sharing a common datacenter region and firewall configuration. Databases grouped beneath a single Azure Server may run on different physical data nodes in the SQL Azure infrastructure.
A Database is a logical representation of the familiar SQL Server user Database. Recall from the introduction that in the SQL Azure topology your Database actually exists as a partition within a SQL Server Database instance. Each Database is created with a specific maximum size and edition. Currently, sizes and editions are available, as shown in Table 11-1, but the maximum database size is 150GB.
It used to be that the edition selected would restrict which sizes you could select, or switch between. For example, if you selected Web, then you could switch between only 1GB and 5GB Databases; switching a Web edition Database to a 10GB Business edition Database was not possible. This originally was designed to encourage you to commit to a certain maximum size (which presumably helped behind the scenes with resource allocation). Thankfully, as of the latest versions of SQL Azure, you can switch between sizes without restriction. You must ensure you never reach the configured maximum size because at that point you will receive errors when trying to create objects or insert or update data.
When a Server is created, a master Database is created along with it that is used to store metadata about the Server and the Databases it contains. This master Database cannot be deleted.
A single Server can manage 149 user-created Databases plus the required master Database for a total of 150 Databases. A Server can manage any mixture of Database editions and sizes. By default, a single Subscription can create up to six Servers. In addition, a Subscription defines a global quota across all Servers of 15 Business Edition Databases or 150 Web edition Databases.
Security and Access Control
Access to SQL Azure is controlled by two devices: a firewall and SQL logins. A SQL Azure Server enables you to specify Firewall rules allowing access from ranges of IP addresses, as well as from Windows Azure platform resources. When a SQL Azure Server is created, an administrative login referred to as the Server-level principal is also created. This login is conceptually equivalent to the “sa” account on SQL Server. SQL Azure does not support Windows Integrated authentication, so all logins are SQL Logins having a username and password.
At the Database level, SQL Azure security has a great deal of parity to an on-premises SQL Server. You create additional logins that enable connection to the Server, and associate those with Database users who have permissions within a specific Database. The T-SQL for access control–related statements, such as Create/Alter/Drop Login; Role, User or Grant/Revoke Database Permissions; Database Principal Permissions; and Type Permissions or changing entity ownership with Alter Authorization, is supported.
SQL Azure requires that all connections use Transport Layer Security (TLS) by allowing only connections that happen across SSL. Clients of SQL Azure should validate the Certificate provided by SQL Azure to prevent man-in-the-middle attacks when using SSL.
When it comes to encryption of data at rest, SQL Azure lacks a few of the features found in SQL Server. For example, SQL Azure does not support Transparent Data Encryption nor the Extensible/External Encryption Key Management features of SQL Server. If you need to encrypt portions of your data, you must implement your own solution for key management and for encrypting/decrypting data.
SQL Azure provides for high availability with an infrastructure delivering automatic replication across three replicas and automatic failover from the primary replica to one of the two backup replicas. However, this replication is specific to SQL Azure and is not to be confused with SQL Server Replication. In addition, SQL Azure does not provide support for Database mirroring or log shipping.
Backup and Restore
Backup and restore of production Databases is vital to any Database. Although SQL Azure provides the aforementioned replication to provide high availability, this protects you only against failures within the datacenter (such as hardware problems, data corruption, and so on). It does not protect your data against user errors.
With SQL Server, you would typically create a backup to the filesystem and restore from the same. With SQL Azure, this is not allowed because that filesystem for an SQL Azure data node represents a potentially shared resource across all the tenants who have Databases on that node. This translates to there being no support for BACKUP/RESTORE statements or attaching/detaching Databases; instead you must take an alternative approach, such as the following:
- Database copy: With SQL Azure you can run a CREATE DATABASE statement that creates a copy of a SQL Azure Database in another Database within the same or a different Server.
- Data Tier Application Import/Export: You can export entire SQL Azure Databases, schema, and data, as Data Tier Applications (BACPAC) and import from them as well via the Azure Portal.
- Scripts: You can use the Generate Scripts Wizard within SQL Server Management Studio to export schema and data from your SQL Azure Database to SQL script files, which you can run to re-create your Database.
- Data Sync Services: Enables you to run regular synchronizations between SQL Azure Databases or SQL Azure and SQL Server Databases.
- SQL Server Integration Services: You can use an instance of SSIS running on-premises against a SQL Azure Database.
- Bulk copy: Using the bcp utility or SqlBulkCopy class to bulk load data.
- SQL Azure Migration Wizard: Among other features, enables you to create backups of SQL Azure Database schema and data, or migrate between SQL Server and SQL Azure.
Backup to Azure Blob Storage via BACPAC is currently available in CTP form within the Azure Portal and is planned for a future release. That said, there is also a small community of third-party tools to help you with your SQL Azure Backup needs, such as SQL Azure Backup from Red Gate Software.
Diagnostics and Monitoring
Because SQL Azure instances are multitenant, many of the diagnostic and monitoring features you may have been accustomed to accessing as a system administrator really no longer apply and are not available. These include the SQL Server Utility (for holistic monitoring of resource utilization across servers), SQL Trace & Profiler, Extended Events (for example, ETW traces written to Event Log), the Data Collector, and many system tables. Instead, most diagnostic and health monitoring is performed by querying a small set of dynamic management views.
Many of the features for automating administration are still available with SQL Azure, with the caveat that there is no direct remote access to the server. As a result, any automation tools must be run from on-premises or, for instance, from a Windows Azure-hosted role that targets SQL Azure. SQL Server PowerShell and SQL Agent (and Jobs), for example, do not run within SQL Azure but can run from remote machines that target your SQL Azure instance. Furthermore, SQL Azure does not support the use of Service Broker (which is often used in the absence of SQL Agent) or the Policy-Based Management available since SQL Server 2008.
Other Unsupported Features
Depending on your specific requirements of SQL Server, you may find additional features missing in the current release of SQL Azure, including the following:
- Master Data Services
- Change Data Capture and Data Auditing
- Resource Governor
- Data compression
- SQL Server Browser
Unique to SQL Azure—Support for Federations
As previously mentioned, table partitioning is not supported. However, similar performance gains for handling large data can be achieved with SQL Azure Federations, which effectively spreads your data across multiple SQL Azure Databases. This is a powerful feature update to SQL Azure that makes it easier to leverage SQL Azure’s scalability.
In general, working with SQL Azure enables you to leverage the tools familiar to you from working with SQL Server, such as SQL Server Management Studio (this includes the Express Edition, but requires 2008 R2 with Service Pack 1 and upward and Visual Studio 2010 with Service Pack 1).
Beyond these, SQL Azure provides some of its own additional web-based tooling for development and management in the Azure Management Portal and the Database Manager.
In addition, a preview of a cloud-based version of Reporting Services, called SQL Azure Reporting, provides report hosting and management as a service. Reports published here can be created with SQL Server Business Intelligence Development Studio or Report Designer and, when published, viewed directly within the web browser or via other formats such as Excel and PDF. These reports can report only on data sourced from SQL Azure Databases.
Because SQL Azure is provided as a service, its licensing model is naturally quite different from SQL Server. In the latter, you buy licenses for servers or clients, whereas with SQL Azure you pay a monthly fee for the Databases that you use by the maximum size used, prorated by their usage in the month. For example, in the current pricing scheme, a single Database using 10GB costs $45.954 per month, and you would be charged that much if you had such a Database created for the whole month (and less than that for each day the Database was not created). In addition to size, you are also billed for the amount of data transferred out of the Azure datacenter.
Observe that there is a special pricing tier for web edition databases created with a MAXSIZE of 1 GB, when the actual space used is between 0 and 100 MB. In this case, you still create a 1GB database, but assuming you use less than 100 MB over the month, you pay half of the rate charged for a 1 GB database per month (for example, $4.995 instead of $9.99). In a similar fashion, databases with an actual space used in the 1-10 GB range, are billed for the first GB, with a lower cost per additional GB. Databases with actual space used between 10-50 GB are billed for the first 10 GB, with an even lower cost per additional GB. Finally, Databases with an actual space used between 50-150 GB are billed for the first 50 GB, with the lowest cost per additional GB.
Another substantial difference is that SQL Azure provides a service-level agreement (SLA), which defines a baseline 99.9 percent availability for your Databases over the course of a month, below which Microsoft issues credits applied to your next month’s bill.
With the differences between SQL Azure and SQL Server under your belt, it is time to dive into creating and working with your first SQL Azure Database.
This was from Programming Microsoft's Clouds: Azure and Office 365 by Tom Rizzo written for John Wiley & Sons, Inc. To see how to build your first SQL Azure Database, pick up a copy of the book from your favorite book store on Amazon.com. ISBN: 1118076567