Free SQL Help for Devs, DBAs, and the Curious

Welcome to the latest edition of our blog posts from the Data Whisperer at SQLSupport.org. Today, we're diving into the depths of Azure Purview and its integration with SQL databases. Azure Purview is a unified data governance service that helps organizations manage and govern their on-premises, multi-cloud, and software-as-a-service data. It's a tool that can help you map your data landscape, apply data policies and compliance requirements, and get meaningful insights from your data.

Azure Purview supports various data sources including SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse. In this post, we'll focus on how Azure Purview integrates with these SQL databases to offer a seamless data governance experience.

The first step in integrating Azure Purview with your SQL databases involves registering your data sources. This process involves specifying the connection details of your data source, such as the server name and the database name. For example, to register a SQL Server 2019 database, you would use the following T-SQL code:

CREATE DATABASE SCOPED CREDENTIAL SQLCredential

WITH IDENTITY = 'SQLUser',

SECRET = 'SQLPassword';

CREATE EXTERNAL DATA SOURCE SQLServer

WITH (LOCATION = 'sqlserver://SQLServerName.database.windows.net',

CREDENTIAL = SQLCredential);

Once you've registered your data sources, Azure Purview will automatically scan these sources and catalog your data. The cataloging process involves classifying your data based on its type and content, and creating a metadata catalog. This catalog can be used to discover and understand your data. For example, if you have a MySQL 8.0 database with a table named 'Customers', Azure Purview will identify this table and its columns, and add this information to the catalog.

Azure Purview also allows you to manage and govern your data. You can define data policies and apply them to your data sources. These policies could include compliance requirements, data retention rules, and data masking rules. For example, if you have a DB2 11.5 database with sensitive data, you could create a data masking rule that masks this data in the Purview catalog. You could use the following DB2 code to define this rule:

-- DB2 code goes here

CREATE MASK sensitiveDataMask FOR sensitiveTable.sensitiveColumn

  RETURN CASE

    WHEN CHECK_DATA_ACCESS_FOR(sensitiveTable.sensitiveColumn, 'MASK') = 'Y'

    THEN '*****'

    ELSE sensitiveTable.sensitiveColumn

  END

ENABLE;

Moreover, Azure Purview integrates seamlessly with Azure Synapse, Microsoft’s integrated analytics service. It enables the discovery of data across your enterprise, which can be ingested, prepared, managed, and served for immediate business intelligence and machine learning needs. All your data workflows, including ingestion, preparation, management, and serving, are orchestrated into integrated and scalable pipelines in Azure Synapse using both on-demand and provisioned resources.

In conclusion, Azure Purview offers a comprehensive data governance solution that integrates seamlessly with your SQL databases. Whether you're using SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, or Azure Synapse, Azure Purview can help you manage and govern your data, gain meaningful insights, and meet your compliance requirements. Stay tuned for more insights from the Data Whisperer at SQLSupport.org.