11. Database features

In the previous chapter, we learned various ways of monitoring and performance tuning options for Azure SQL Database and SQL Managed Instance. This chapter talks about the important database features available in Azure SQL Database and Azure SQL Managed Instance.

We will learn about SQL Data Sync, which is used to sync data between two or more Azure SQL databases or on-premises SQL servers, and we'll look at the SQL Graph capabilities and enhancements in Azure SQL Database. We will also explore newly added features, such as the Azure Machine Learning service and distributed transaction support by creating SQL trust groups in Azure SQL Managed Instance.

By the end of this chapter, you will be able to:

• Implement SQL Data Sync to sync an Azure SQL database with an on-premises database.
• Use SQL Graph queries to create and query graph tables.
• Implement SQL Graph enhancements.
• Create a model to predict future sales using the Azure...

Azure SQL Data Sync

As the name suggests, Azure SQL Data Sync allows bi-directional data syncing between one or more Azure SQL databases and on-premises databases. The Azure SQL Data Sync service is free; however, there are charges for data movement into and out of an Azure SQL database.

Note

Azure SQL Sync doesn't support SQL Managed Instance at the time of writing.

Figure 11.1 shows how data is typically synced between an Azure SQL database and an on-premises database:

Figure 11.1: Syncing between an Azure SQL database and on-premises database

Azure SQL Data Sync is based around the idea of sync groups. A sync group has a hub database and one or more member databases. The Data Sync is always from hub to member, or from member to hub. There's no data sync between two member databases.

A sync group has the following components:

• Hub database: This should be an Azure SQL database. The Data Sync happens to or from the hub database...

Online and resumable DDL operations

The online CREATE INDEX and REBUILD INDEX operations can be paused and resumed as and when required, or when killed/failed.

The operation is marked as resumable by specifying RESUMABLE=ON. For example, the following CREATE INDEX operation is a resumable operation:

CREATE INDEX IX_Orders_CustomerID_Includes ON Sales.Orders(CustomerID,Comments) 
INCLUDE(DeliveryInstructions,InternalComments) 
WITH(ONLINE=ON,MAXDOP=1,RESUMABLE=ON)
GO

To pause an ongoing online resumable CREATE INDEX operation, either kill the session or execute the PAUSE statement, as shown here:

ALTER INDEX IX_Orders_CustomerID_Includes on Sales.Orders PAUSE 
GO

To resume a paused online resumable CREATE INDEX operation, either execute the CREATE INDEX query mentioned earlier or execute the following query:

ALTER INDEX IX_Orders_CustomerID_Includes on Sales.Orders RESUME 
GO

You can also specify MAX_DURATION in minutes that the resumable operation should run before...

SQL Graph queries and improvements

A graph database consists of nodes and edges. The nodes represent entities in your graph, such as people or organizations, and edges represent the relationship between two entities. The graph databases are optimized for implementing hierarchies and many-to-many relationships, and for analyzing interconnected data and relationships. This is difficult to implement in a relational database.

Let's look at modeling a very popular use case for a graph database: a social media application. A social media application allows users to follow, like, post, comment, and tag other users. Let's look at a simple model in Figure 11.12 that allows users to do this:

Note

To find out more about graphs, refer to https://docs.microsoft.com/sql/relational-databases/graphs/sql-graph-overview?view=sql-server-ver15.

Figure 11.12: Social media model for following users

In Figure 11.12, the circles represent nodes and the lines...

Machine Learning Services

Machine Learning Services was first introduced in SQL Server 2016 (on-premises) as R Services. Machine learning is now available in Azure SQL Managed Instance. It's in preview at the time of writing.

Machine Learning Services provides machine learning capabilities for Azure SQL Managed Instance and allows in-database R and Python scripts to be run for high-performance predictive analytics. Running in-database R and Python scripts uses the data in the managed instance instead of pulling the data over the network from a different source. In the absence of Machine Learning Services, you would have to set up R and Python and get the data from a remote data source for the analysis.

Machine Learning Services makes it possible to run R and Python scripts in stored procedures or T-SQL statements.

R is a programming language that's extensively used for data analysis, machine learning, and predictive analytics. R packages provide out-of-the-box methods...

Distributed transactions in Azure SQL Managed Instance

A distributed transaction is a database transaction in which there are two or more database servers involved. In an on-premises computer running SQL Server, this is managed by the Microsoft Distributed Transaction Coordinator (MSDTC) process. Microsoft recently announced support for distributed transactions in SQL Managed Instance, and this feature is available in preview. Since the MSDTC service is not available for Platform-as-a-Service in Azure, this feature is directly integrated with Azure SQL Managed Instance.

Before you run a transaction across multiple instances, first you need to add all the instances into a mutual security and communication relationship. This can be done by creating a Server Trust Group between all the instances using the Azure portal. If the instances are not part of the same virtual network, then Virtual Network Peering (discussed in Chapter 9, High availability and disaster recovery) is required...

Summary

In this chapter, we learned about database features, Azure SQL Data Sync, online and resumable DDL operations, and SQL Graph database. We also explored Machine Learning Services and distributed transaction features for Azure SQL Managed Instance.

Azure SQL Data Sync is an easy-to-set-up process of syncing data between two or more Azure SQL databases, or an Azure SQL database and an on-premises computer running SQL Server. Data Sync can be used to support cloud migration or to offload reporting workloads.

Resumable DDL operations allow CREATE INDEX and REBUILD INDEX tasks to be paused or resumed as and when required. This helps when we need to recover from problems wherein a long-running CREATE INDEX or REBUILD INDEX statement causes blocking and slows system performance.

SQL Graph capabilities provide a flexible and easy way to implement many-to-many relationships or hierarchies.

Machine Learning Services allows you to run R and Python scripts on Azure SQL Managed...