Azure SQL everywhere

Microsoft’s SQL Server has long been the backbone of its data story; over the years supporting more and more features, and migrating from your servers (both Windows and Linux) to the cloud as the Azure SQL Database. With managed instances and the option to run in containers, Azure SQL Database is becoming a powerful and flexible alternative to the familiar SQL Server, running in places you wouldn’t expect to find a relational store and supporting Azure’s Internet of Things services. Build 2019 allowed Microsoft to show off just where it is taking its database engine; from the very smallest to the very largest implementations.

Azure SQL Database Edge

Designed to run on edge computing devices, the new Azure SQL Database Edge is part of Microsoft’s push to drive IoT compute to the edge of the network. Why ship large amounts of data back to the cloud, when you’re limited by bandwidth and available connectivity options? An IoT installation on an oil platform has very different requirements from one running in a home, and processing data on the platform, even on the device, can result in significant savings.

Currently available in private preview, Azure SQL Database Edge brings familiar database services and capabilities to your Edge devices, whether they run on Intel or ARM hardware. By using a local store for your data, you can process it on site, sending only relevant messages and content to your applications. There’s support for streaming and for time series data, ideal for handling sensor data, and the whole thing is packaged in a single, small container, so you can run it without needing to think about how you’re going to manage it.

Running on ARM requires a reasonably powerful system; Microsoft has demonstrated it running on an ARM Cortex A72 system with 4 cores and 4GB of memory. It’s the sort of device you’d use for an IoT hub, or for more complex industrial IoT applications that might mix machine learning and data processing. Combining Azure SQL Database Edge and Azure Cognitive Services containers on this class of device can help deliver this without having to build your own application platforms. There’s even the option of running SQL Database Edge on the expanded range of secure Azure Sphere MCUs.

Azure SQL Database Serverless

You don’t necessarily need a database running all the time. Many loads are intermittent, only requiring occasional reads or writes. Some only need to be used for a few hours a day, others may have bursts of activity, with long periods of downtime. Provisioning a cloud-hosted database for 24/7 operation can be expensive, especially if you’re not using it for much of that time.

Microsoft has now launched a new version of Azure SQL for this type of workload, one that mixes serverless compute techniques with a familiar database engine when you’re using single databases or elastic pools. You’ll still pay for storage, but now you’ll pay for compute on a per-second basis, rather than per hour. Requests will restart paused database instances, with a slight delay while Azure warms up the paused instance. Azure will autoscale compute resources based on load, up to a set maximum number of cores.

There are some issues to keep in mind when you’re using a serverless database; there is some latency (anything up to a minute) on resume, so you’ll need to ensure any code auto-retries logins. You’ll also need to consider how long you’ll want your database to idle before you pause it.

Hyperscale tier for Azure Database Engines

If you’re running Azure SQL or Azure Database for PostgreSQL you’re now able to use a new Hyperscale tier, to get around the resource limits normally associated with relational databases. It’s able to take advantage of Azure features to give you scalability across all three possible bottlenecks: compute, storage, and memory.

Azure SQL Database Hyperscale is designed for rapid scale out, without pre-provisioning storage resources and with dynamic scaling of compute. Microsoft recommends it for OLTP and other analytical workloads, with up to 100TB of storage. There’s no need to change how you build and operate your database; it’s still the same familiar SQL service under the hood with additional tooling for near instantaneous backups and with separate handling for logs and for transactions.

The Postgres version of Hyperscale builds on the work done by recent Microsoft acquisition Citus Data. This scales a single database across many hundreds of nodes, allowing you to work with significant amounts of in-memory data as well as handling massively parallel queries and indexing. It’s an approach that’s ideal for near real-time access to billions of rows of data; so you can use it as a backend for applications like loyalty cards or ad networks, where fast access to lots of data is essential.

Migrate Oracle to Azure Database for PostgreSQL

Azure’s Database Migration Service handles moving on-premises data to Azure-hosted databases. It’s an important tool that can do much more than just migrate SQL Server to Azure SQL Database. You can use it to bring MongoDB data to Cosmos DB, and PostgreSQL to Azure Database for PostgreSQL. At Build 2019, Microsoft offered a new service, to move data in your Oracle databases to Azure’s managed PostgreSQL instances. When paired with the new Hyperscale option, it should support even the largest Oracle instances.

Azure SQL Data Warehouse updates

The other side of the Azure data story is its analytics capabilities. Among the services getting updates are Azure Data Factory, with additional ETL and ELT features for managing large data imports. Perhaps the biggest update is to Azure SQL Data Warehouse, which builds on existing services to improve performance and flexibility. You’re now able to define which workloads get priority, avoiding conflicts. You’re also able to load it with unstructured JSON data alongside familiar structured data, increasing your options for analysing information across all your data.

Result-sets can now be cached, so you don’t have to rerun queries every time you open a dashboard. Similarly, materialised views automatically rewrite complex queries so that they’re more efficient, running faster and using fewer system resources. The result is more bang for your buck, with increased performance at no additional cost. There’s even the possibility that using some of the new features will reduce your compute costs, allowing you to add additional queries to your data warehouse processing.

If you’d like to talk more about systems modernisation, the benefits of both on-premises and cloud-based SQL Server implementation, and how you’d migrate from your current database, please get in touch with Grey Matter’s technical experts: [email protected]