Use ER/Studio Governance Attachments in SQL Server (and other platforms too!)

ER/Studio Data Architect contains extensive metadata for all constructs utilized in the models and data dictionaries, whether used in logical modeling, physical modeling, or both. This includes names, descriptions, comments, datatypes, lengths and a myriad of additional properties. However, organizations often wish to define and create additional metadata to further define and classify model objects. This can be for a variety of reasons, but often it is to enable data governance. ER/Studio handles this through a construct called attachments. Attachments can be used to define additional properties for virtually any construct within data models and data dictionaries.

For the scope of this discussion, I will focus specifically on entities/tables and attributes/columns. Some common attachment uses are the assignment of data stewards, data quality parameters, master data management classifications, data retention policies and so forth. ER/Studio Security also has specialized attachments called security properties which are typically used for compliance mapping and designation of privacy levels.

The models are also used to create and update the physical database and structures. For platforms like SQL Server this is accomplished by generating DDL in ER/Studio, then executing it via direct connection, or a generated DDL script. DBAs and database developers that are working directly with the database should have access to as much descriptive metadata as possible. Therefore, implementing the attachment and security metadata as part of the database itself is very useful. With ER/Studio Data Architect, we are able to implement the attachments and security properties in SQL Server, using constructs called SQL Server extended properties. The attached tech note describes how attachments are modeled, as well as automated methods to deploy them to SQL Server (or Azure SQL Database). If you are using a different platform, this topic will still be of interest, since it also outlines a generic approach that can be used with any database.

The example macro reference in the document is also posted ER/Studio forums under the Macros Discussion

Anonymous