Category Archives : Database



Modernize index maintenance with Resumable Online Index Rebuild

As we know, the index is one of the most powerful tools for optimizing database performance. As you insert, update, and delete rows, indexes become more fragmented and less effective. Because of this, DBAs regularly do index rebuild operations.

With the growing sizes of databases, index rebuilds can take a very long time. Combine that with the business needs for your applications to be always available and performant and this can be an issue. Big OLTP environments with busy workloads often have very short maintenance windows with some too short to execute large index rebuild operations.  

Fit rebuild operations into limited maintenance windows

With ROIR (Resumable Online Index Rebuild), you can configure a rebuild to execute only during a maintenance window of defined length. Previously, if an index operation ran longer than the maintenance window the DBA would have to abort the index operation and run it again from the start later. If an index operation regularly took longer than the desired maintenance window there was no way to work around this. With ROIR, the user can simply define a max duration for the rebuild according to the maintenance window.

ALTER INDEX [ix_CustomerIDs] ON [ContosoSales].[ConstosoTransactionData] REBUILD WITH (ONLINE =



Introducing SQL Information Protection for Azure SQL Database and on-premises SQL Server!

We are delighted to announce the public preview of SQL Information Protection, introducing advanced capabilities built into Azure SQL Database for discovering, classifying, labeling, and protecting the sensitive data in your databases. Similar capabilities are also being introduced for on-premises SQL Server via SQL Server Management Studio.

Discovering and classifying your most sensitive data, including business, financial, healthcare, and PII, can play a pivotal role in your organizational information protection stature. It can serve as infrastructure for:

Helping meet data privacy standards and regulatory compliance requirements, such as GDPR. Data-centric security scenarios, such as monitoring (auditing) and alerting on anomalous access to sensitive data. Controlling access to and hardening the security of databases containing highly sensitive data. What is SQL Information Protection?

SQL Information Protection (SQL IP) introduces a set of advanced services and new SQL capabilities, forming a new information protection paradigm in SQL aimed at protecting the data, not just the database:

Discovery and recommendations – The classification engine scans your database and identifies columns containing potentially sensitive data. It then provides you an easy way to review and apply the appropriate classification recommendations via the Azure portal. Labeling – Sensitivity classification labels can be persistently tagged on



Azure SQL Database now offers zone redundant Premium databases and elastic pools

Azure SQL Database Premium tier supports multiple redundant replicas for each database that are automatically provisioned in the same datacenter within a region. This design leverages the SQL Server AlwaysON technology and provides resilience to server failures with 99.99% availability SLA and RPO=0. With the introduction of Azure Availability Zones, we are happy to announce that the SQL Database now offers built-in support of the Availability Zones in its Premium service tier. By placing the individual database replicas to different availability zones, it makes the Premium databases resilient to the much larger set of failures, including catastrophic datacenter outages. The built-in support of Availability Zones further enhances the High Availability (HA) solutions in Azure SQL Database. For more information see High-availability and Azure SQL Database.

To take advantage of this capability, you simply select the zone redundant option during the database or elastic pool creation. You can also enable it for existing databases or pools. If the availability zones are supported in the region where your database or pool is deployed, Azure SQL will automatically reconfigure it without any downtime.

You can use the Azure portal to enable zone redundant database configuration as illustrated on the following diagram.




Making Azure the best place for all your applications and data

The confluence of cloud, data and AI is driving unprecedented change. The ability to utilize data and turn it into breakthrough actions and experiences, is foundational to innovation today. Organizations are using cloud technologies to accelerate the innovation that drives their business—migrating applications and data to the cloud is a key initiative within this strategy.
Only Microsoft delivers a consistent and global platform optimized for hybrid cloud for all applications and data. Microsoft provides the flexibility to maintain consistent hybrid cloud environments, while also providing rich choice of migration and modernization options. This consistency enables you to distribute your applications and data workloads on your terms, without the complexity of having to maintain different skillsets, systems, and tools while extracting the insights from data to drive innovation.

Today, we are excited to announce investments that dramatically expand the choice and ROI of moving your SQL Server and open source applications to Azure. SQL Server customers can now try the preview for SQL Database Managed Instance, Azure Hybrid Benefit for SQL Server license benefit which can help customers save up to 30%*, and Azure Database Migration Service preview for Managed Instance. Additionally, we are excited to announce the preview for



Migrate your databases to a fully managed service with Azure SQL Database Managed Instance

This blog post was co-authored by Eric Hudson, Senior Product Marketing Manager, CADD & AI.

We’re excited to announce the preview of Azure SQL Database Managed Instance, a new deployment option in SQL Database that streamlines the migration of SQL Server workloads to a fully managed database service. This new Managed Instance deployment option provides full SQL Server engine compatibility and native virtual network (VNET) support. 

“SQL Managed Instance is that happy medium we were looking for. We needed the power and compatibility of SQL Server, but without the management overhead and cost that comes with running VMs 24×7. Not only will we get that power and ease of management, we’ll also be able to use the Azure Hybrid Benefit, which allows us to use our existing SQL Server licensing through Software Assurance. Developing, deploying, and managing our application is getting a whole lot easier and cheaper with Azure and SQL Managed Instance.”

Robert Shurbet, Senior Software Development Professional, Pivot Technology Solutions

Migrate your databases to a fully-managed service

Azure SQL Database is a fully-managed database service, which means that Microsoft operates SQL Server for you and ensures its availability and performance. SQL Database also includes innovative



Updates to Azure Database for MySQL and Azure Database for PostgreSQL

Azure database services for MySQL and PostgreSQL are fully managed, enterprise-ready services built using community version of MySQL and PostgreSQL database engines respectively. These services come with built-in high availability and ability to elastically scale compute and storage independently in seconds, helping you to easily adjust resources and respond faster to market and customer demands. Additionally, you benefit from unparalleled security and compliance, Azure IP advantage, as well as Azure’s industry leading global reach.

Since we announced these services in preview last year, users have been providing feedback helping drive product improvements and new features. As part of executing on customer feedback, I am really excited to announce the changes to the pricing model that will provide customers with more flexibility and help optimize costs.

Pricing tiers

Since the preview launch, we have been offering the Basic and Standard pricing tiers. We are continuing with the Basic tier, re-naming Standard to General Purpose and introducing a new premium tier called Memory Optimized to cater to workloads requiring faster in-memory performance. For more information about the General Purpose and Memory Optimized tiers, and when to use them, visit MySQL and PostgreSQL documentation.

Changing from “compute units” to vCores

Beginning today you



VNet service endpoints for Azure SQL Database now generally available

This blog post was co-authored by Anitha Adusumilli, Principal Program Manager, Azure Networking.

We are excited to announce the general availability of Virtual Network (VNet) Service Endpoints for Azure SQL Database in all Azure regions. This ability allows you to isolate connectivity to your logical server from only a given subnet or set of subnets within your virtual network. The traffic to Azure SQL Database from your VNet will always stay within the Azure backbone network. This direct route will be preferred over any specific routes that take Internet traffic through virtual appliances or on-premises.

There is no additional billing for virtual network access through service endpoints. Current pricing model for Azure SQL DB applies as is.

VNet service endpoints for SQL Data Warehouse (DW) continues to be in public preview, for all Azure regions.

Firewall rules and VNet Service Endpoints can be used together

Turning on VNet Service Endpoints does not override Firewall rules that you have provisioned on your SQL Server or Database. Both continue to be applicable.

VNet Service Endpoints don’t extend to on-premises. To allow access from on-premises, Firewall rules can be used to limit connectivity only to your public (NAT) IPs.

To enable VNet



Get started with Azure Cosmos DB through this technical training series

Are you building a new application which requires low latency at any scale? Or are you in the process of migrating your NoSQL databases to the cloud? Or looking for the right resources to help you get started with Azure Cosmos DB?

Join us for one or all of a seven-week Azure Cosmos DB technical training series, which explores the capabilities and potential of Azure Cosmos DB. Whether you’re brand new to Azure Cosmos DB or an experienced user, you’ll leave this series with a better understanding of database technology and have the practical skills necessary to get started.

Azure Cosmos DB is the world’s first globally distributed, multi-model database service with native NoSQL support. Designed for the cloud, Azure Cosmos DB enables you to build planet-scale applications that bring data to where your users are with SLA-guaranteed low latency, throughput, and 99.99% availability.

In this training series, you’ll learn everything necessary to get your cloud database up and running. In the first session, we covered a technical overview of Azure Cosmos DB, and, in the following weeks, we’ll progress into deeper topics like migrating Mongo DB applications to Azure Cosmos DB, building serverless applications, and enabling real-time analytics with



Unlock Query Performance with SQL Data Warehouse using Graphical Execution Plans

The Graphical Execution Plan feature within SQL Server Management Studio (SSMS) is now supported for SQL Data Warehouse (SQL DW)! With a click of a button, you can create a graphical representation of a distributed query plan for SQL DW.

Before this enhancement, query troubleshooting for SQL DW was often a tedious process, which required you to run the EXPLAIN command. SQL DW customers can now seamlessly and visually debug query plans to identify performance bottlenecks directly within the SSMS window. This experience extends the query troubleshooting experience by displaying costly data movement operations which are the most common reasons for slow distributed query plans. Below is a simple example of troubleshooting a distributed query plan with SQL DW leveraging the Graphical Execution Plan.

The view below displays the estimated execution plan for a query. As we can see, this is an incompatible join which occurs when there is a join between two tables distributed on different columns. An incompatible join will create a ShuffleMove operation, where temp tables will be created on every distribution to satisfy the join locally before streaming the results back to the user. The ShuffleMove has become a performance bottleneck for this query:




Sync SQL data in large scale using Azure SQL Data Sync
Sync SQL data in large scale using Azure SQL Data Sync

Azure SQL Data Sync allows users to synchronize data between Azure SQL Databases and SQL Server databases in one-direction or bi-direction. This feature was first introduced in 2012. By that time, people didn’t host a lot of large databases in Azure. Some size limitations were applied when we built the data sync service, including up to 30 databases (five on-premises SQL Server databases) in a single sync group, and up to 500 tables in any database in a sync group.

Today, there are more than two million Azure SQL Databases and the maximum database size is 4TB. But those limitations of data sync are still there. It is mainly because that syncing data is a size of data operation. Without an architectural change, we can’t ensure the service can sustain the heavy load when syncing in a large scale. We are working on some improvements in this area. Some of these limitations will be raised or removed in the future. In this article, we are going to show you how to use data sync to sync data between large number of databases and tables, including some best practices and how to temporarily work around database and table limitations.

Sync data