SQL Server and SSMS introduced Data Classification feature that helps you to apply classification labels to table columns in your database. I explained this functionality in this article: GDPR in SQL Server and Azure SQL Database.
In this tutorial I will show you how you can import this metadata into Dataedo repository, browse classification labels, search for columns and share this information in HTML, PDF or Excel.
Add new database
If you want don't have database schema imported into Dataedo and want to create new database in the repository follow steps below. If you already have imported database jump to the next section.
To import database to Dataedo repository click Add documentation button on the ribbon and provide connection details to your database. Make sure to check Advanced settings option.
Define custom fields
On the next screen (if you have selected Advanced settings option) you will see a list of custom fields defined in Dataedo repository. Most likely you will have to create new custom fields for SQL Server extended properties that hold classification data.
Click Define custom fields button to open custom fields definition window.
New window will open showing custom fields defined in Dataedo repository. If you don't have custom fields you'd like to import classification metadata into, you need to create new fields. I suggest two fields stored by SQL Server (you can choose different names):
- Classification Information Type
- Classification Sensitivity Label
Click Add button.
In custom field definition form provide:
- Title - title of custom field of your choice (see my suggestions above)
- Type - choose Drop-down list (open)
- Visibility - Uncheck elements apart from Column (this step is not necessary)
Click OK and repeat for second field.
To save changes close window with Save button on the bottom of the window. Your custom fields are now added to the repository and are visible for all databases.
Choose extended properties
You should go back to previous screen and see newly created custom fields. Now you need to check both of them and type in names of the extended property so to instruct Dataedo to import them. Those names are:
- Classification Information Type: sys_information_type_name
- Classification Sensitivity Label: sys_sensitivity_label_name
Complete import clicking Next a few times and wait for metadata to be imported.
Import classification to existing database
If you already have imported database schema to the repository you can add classification fields almost the same way as you would add new database (see section above) with one difference - you need to select Reimport all included objects option on the third screen of the wizard.
To reconnect to database and import metadata you need to select database from the repository explorer (on the left) and hit Update documentation button.
Update classification from server
Dataedo allows you to refresh metadata from the server, but only for fields that are empty. Once filled Dataedo doesn't overwrite them during import. It is possible to clear field accessing repository directly.
To reimport metadata follow instructions from the previous section (Import classification to existing database).
When you successfully imported data classification extended properties to Dataedo repository you can now see this metadata in new columns that will show on table columns form:
You can also easily search for fields. Open search form with Search button or ctrl + F. Click Add field and choose one or more of your fields.
Type in value (or part of it) you are looking for and click Search. Dataedo will list tables that contain specific classification. By selecting them you can browse their columns and fields that match criteria are highlighted with orange.
You can also publish imported classifications in HTML page, PDF or Excel. To do it click Export documentation button and choose format, location and other optional settings and follow the wizard to save file.