SQL Server and Azure Purview work better together

Published Oct 21 2021 06:03 AM 1,486 Views
Microsoft

Azure Purview, which recently became generally available, allows governance of on-premises SQL server farms at scale, and with ease. This blog details how to register and scan a SQL server. It also describes how to restrict access and discover assets easily by grouping SQL servers into Purview collections. Using Purview, users can also gain further insights into their entire hybrid data estate.

Register and scan SQL Server
Navigate to a Purview account and click on the Data Map section to the left. Users can view their data estate maps and choose to view sources in table format as well.

MarisaBrasile_0-1634821186490.png

 

Purview now supports more than 20 source types, ranging from Azure SQL DB to AWS S3 to Oracle DB. There are two ways to register a source—by either clicking on the register button on the top left or navigating to the collection you’d like to register the source to and clicking on the register quick action icon. After doing so, click on the SQL server source tile and fill in the required details.

MarisaBrasile_1-1634821186498.png

 

As part of the required details, users can register a source to a collection of interest. In our example, we register the source to the NorthAmericaOP (on-premises) collection.

 

Once a source has been registered, the next step is to set up self-hosted integration runtime, scan rule set, and credentials. Then it’s time to set up and run your scan. While setting up the scan, fill in details for the integration runtime, database name (leaving it empty will scan the entire server), and credential. A scan can also be set up with a collection—in our example we leave the collection on the scan as NorthAmericaOP (that of the parent source). Now ensure that all these assets are scanned into the catalog with the right collection associated with them for discovery and access control.

MarisaBrasile_2-1634821186502.png

 

Users can further choose to scope a scan, set up a schedule, and review all details before running it. View the results of the scan by clicking on View details for the source.

MarisaBrasile_3-1634821186506.png

 

Trace Lineage
When Azure Data Factory is used to move and transform data, lineage relating to your SQL server can automatically be captured and surfaced in Purview. Learn how to link an ADF instance to the Purview account.

MarisaBrasile_4-1634821186512.png

 

 

Search and Browse your SQL Server Tables
Once the scan completes, you can discover assets via search or browse. To browse, click on the browse assets tile on the catalog home page, navigate to the by source type tab, click on SQL Server, and navigate to the table that you’re interested in learning more about. Users can also browse for SQL server tables by collection.

MarisaBrasile_5-1634821186517.png

 

 

MarisaBrasile_6-1634821186520.png

 

Search for a table by keyword and narrow down results by using the facet filters on the left. In our following example, we search by the keyword “employee” and narrow down our results by selecting the “firmwide” glossary term.

MarisaBrasile_7-1634821186530.png

 

 

MarisaBrasile_8-1634821186534.png

 

Add business metadata to your SQL server assets
Navigate to a SQL table and view all of its details. Add descriptions, classifications, and business glossary terms by clicking on the edit button (provided you’re a data curator in the scope of the collection the asset belongs to) to aid in discoverability and compliance.

MarisaBrasile_9-1634821186538.png

 

Gain Insights
Finally, view SQL server-related insights around assets, scans, glossary, classification, and labels by navigating to the insights section of Purview.

MarisaBrasile_10-1634821186543.png

 

 

Get started today!

  • Quickly and easily create an Azure Preview account to try the generally available features.
  • Read documentation about how to register and scan SQL Server in Azure Purview

 

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2869207%22%20slang%3D%22en-US%22%3ESQL%20Server%20and%20Azure%20Purview%20work%20better%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2869207%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20Purview%2C%20which%20recently%20became%20generally%20available%2C%20allows%20governance%20of%20on-premises%20SQL%20server%20farms%20at%20scale%2C%20and%20with%20ease.%20This%20blog%20details%20how%20to%20register%20and%20scan%20a%20SQL%20server.%20It%20also%20describes%20how%20to%20restrict%20access%20and%20discover%20assets%20easily%20by%20grouping%20SQL%20servers%20into%20Purview%20collections.%20Using%20Purview%2C%20users%20can%20also%20gain%20further%20insights%20into%20their%20entire%20hybrid%20data%20estate.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ERegister%20and%20scan%20SQL%20Server%3C%2FSTRONG%3E%3CBR%20%2F%3ENavigate%20to%20a%20Purview%20account%20and%20click%20on%20the%20Data%20Map%20section%20to%20the%20left.%20Users%20can%20view%20their%20data%20estate%20maps%20and%20choose%20to%20view%20sources%20in%20table%20format%20as%20well.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_0-1634821186490.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318941iB7B7398E4B78CA31%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_0-1634821186490.png%22%20alt%3D%22MarisaBrasile_0-1634821186490.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPurview%20now%20supports%20more%20than%2020%20source%20types%2C%20ranging%20from%20Azure%20SQL%20DB%20to%20AWS%20S3%20to%20Oracle%20DB.%20There%20are%20two%20ways%20to%20register%20a%20source%E2%80%94by%20either%20clicking%20on%20the%20register%20button%20on%20the%20top%20left%20or%20navigating%20to%20the%20collection%20you%E2%80%99d%20like%20to%20register%20the%20source%20to%20and%20clicking%20on%20the%20register%20quick%20action%20icon.%20After%20doing%20so%2C%20click%20on%20the%20%3CSTRONG%3ESQL%20server%3C%2FSTRONG%3E%20source%20tile%20and%20fill%20in%20the%20required%20details.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_1-1634821186498.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318942i4C92217EE4D28C60%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_1-1634821186498.png%22%20alt%3D%22MarisaBrasile_1-1634821186498.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20part%20of%20the%20required%20details%2C%20users%20can%20register%20a%20source%20to%20a%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fhow-to-create-and-manage-collections%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecollection%3C%2FA%3E%20of%20interest.%20In%20our%20example%2C%20we%20register%20the%20source%20to%20the%20%3CSTRONG%3ENorthAmericaOP%20(on-premises)%3C%2FSTRONG%3E%20collection.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20a%20source%20has%20been%20registered%2C%20the%20next%20step%20is%20to%20set%20up%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fmanage-integration-runtimes%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eself-hosted%20integration%20runtime%3C%2FA%3E%2C%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fcreate-a-scan-rule-set%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Escan%20rule%20set%3C%2FA%3E%2C%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fmanage-credentials%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecredentials%3C%2FA%3E.%20Then%20it%E2%80%99s%20time%20to%20set%20up%20and%20run%20your%20scan.%20While%20setting%20up%20the%20scan%2C%20fill%20in%20details%20for%20the%20integration%20runtime%2C%20database%20name%20(leaving%20it%20empty%20will%20scan%20the%20entire%20server)%2C%20and%20credential.%20A%20scan%20can%20also%20be%20set%20up%20with%20a%20collection%E2%80%94in%20our%20example%20we%20leave%20the%20collection%20on%20the%20scan%20as%20NorthAmericaOP%20(that%20of%20the%20parent%20source).%20Now%20ensure%20that%20all%20these%20assets%20are%20scanned%20into%20the%20catalog%20with%20the%20right%20collection%20associated%20with%20them%20for%20discovery%20and%20access%20control.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_2-1634821186502.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318940i5C97D271C742673B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_2-1634821186502.png%22%20alt%3D%22MarisaBrasile_2-1634821186502.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsers%20can%20further%20choose%20to%20scope%20a%20scan%2C%20set%20up%20a%20schedule%2C%20and%20review%20all%20details%20before%20running%20it.%20View%20the%20results%20of%20the%20scan%20by%20clicking%20on%20%3CSTRONG%3EView%20details%3C%2FSTRONG%3E%20for%20the%20source.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_3-1634821186506.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318943i136942775027A9BA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_3-1634821186506.png%22%20alt%3D%22MarisaBrasile_3-1634821186506.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ETrace%20Lineage%3C%2FSTRONG%3E%3CBR%20%2F%3EWhen%20Azure%20Data%20Factory%20is%20used%20to%20move%20and%20transform%20data%2C%20lineage%20relating%20to%20your%20SQL%20server%20can%20automatically%20be%20captured%20and%20surfaced%20in%20Purview.%20Learn%20how%20to%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fhow-to-link-azure-data-factory%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Elink%3C%2FA%3E%20an%20ADF%20instance%20to%20the%20Purview%20account.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_4-1634821186512.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318945i6F47F78CDE87F450%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_4-1634821186512.png%22%20alt%3D%22MarisaBrasile_4-1634821186512.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESearch%20and%20Browse%20your%20SQL%20Server%20Tables%3CBR%20%2F%3E%3C%2FSTRONG%3EOnce%20the%20scan%20completes%2C%20you%20can%20discover%20assets%20via%20search%20or%20browse.%20To%20browse%2C%20click%20on%20the%20browse%20assets%20tile%20on%20the%20catalog%20home%20page%2C%20navigate%20to%20the%20%3CSTRONG%3Eby%3C%2FSTRONG%3E%20%3CSTRONG%3Esource%20type%3C%2FSTRONG%3E%20tab%2C%20click%20on%20%3CSTRONG%3ESQL%20Server%2C%20%3C%2FSTRONG%3Eand%20navigate%20to%20the%20table%20that%20you%E2%80%99re%20interested%20in%20learning%20more%20about.%20Users%20can%20also%20browse%20for%20SQL%20server%20tables%20by%20collection.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_5-1634821186517.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318944iD76B1DE88E0F2559%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_5-1634821186517.png%22%20alt%3D%22MarisaBrasile_5-1634821186517.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_6-1634821186520.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318946iEAF6AEC1BB5C64E4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_6-1634821186520.png%22%20alt%3D%22MarisaBrasile_6-1634821186520.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESearch%20for%20a%20table%20by%20keyword%20and%20narrow%20down%20results%20by%20using%20the%20facet%20filters%20on%20the%20left.%20In%20our%20following%20example%2C%20we%20search%20by%20the%20keyword%20%E2%80%9Cemployee%E2%80%9D%20and%20narrow%20down%20our%20results%20by%20selecting%20the%20%E2%80%9Cfirmwide%E2%80%9D%20glossary%20term.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_7-1634821186530.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318948i6DD56FBEEDFA563E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_7-1634821186530.png%22%20alt%3D%22MarisaBrasile_7-1634821186530.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_8-1634821186534.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318947iEF0F54126F3D8E5F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_8-1634821186534.png%22%20alt%3D%22MarisaBrasile_8-1634821186534.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAdd%20business%20metadata%20to%20your%20SQL%20server%20assets%3C%2FSTRONG%3E%3CBR%20%2F%3ENavigate%20to%20a%20SQL%20table%20and%20view%20all%20of%20its%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpurview%2Fcatalog-asset-details%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Edetails%3C%2FA%3E.%20Add%20descriptions%2C%20classifications%2C%20and%20business%20glossary%20terms%20by%20clicking%20on%20the%20edit%20button%20(provided%20you%E2%80%99re%20a%20data%20curator%20in%20the%20scope%20of%20the%20collection%20the%20asset%20belongs%20to)%20to%20aid%20in%20discoverability%20and%20compliance.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_9-1634821186538.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318950iDEB985637AE074F0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_9-1634821186538.png%22%20alt%3D%22MarisaBrasile_9-1634821186538.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EGain%20Insights%3C%2FSTRONG%3E%3CBR%20%2F%3EFinally%2C%20view%20SQL%20server-related%20insights%20around%20assets%2C%20scans%2C%20glossary%2C%20classification%2C%20and%20labels%20by%20navigating%20to%20the%20insights%20section%20of%20Purview.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarisaBrasile_10-1634821186543.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318949iA2B2400A18D629E2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarisaBrasile_10-1634821186543.png%22%20alt%3D%22MarisaBrasile_10-1634821186543.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EGet%20started%20today!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EQuickly%20and%20easily%20create%20an%20%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fservices%2Fpurview%2F%23get-started%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Preview%3C%2FA%3E%20account%20to%20try%20the%20generally%20available%20features.%3C%2FLI%3E%0A%3CLI%3ERead%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fregister-scan-on-premises-sql-server%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Edocumentation%3C%2FA%3E%20about%20how%20to%20register%20and%20scan%20SQL%20Server%20in%20Azure%20Purview%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2869207%22%20slang%3D%22en-US%22%3E%3CP%3ELearn%20how%20to%20register%20and%20scan%20your%20SQL%20server%20in%20Azure%20Purview%20then%20dive%20in%20on%20features.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2869207%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Oct 21 2021 06:03 AM
Updated by: