Best practices: How to Document

With SQL Server, it is strange how some of the most radical improvements that have been introduced over the years have been slipped in almost unnoticed. Conversely, the features that were plastered all over the marketing brochures are sometimes the ones that turn out to be dead ends.

A feature that fits into the former category is the extended properties of databases. Introduced quietly with SQL Server 2000, one could have easily missed them but they have proved, after all, to be of great value to the developer. Extended properties are the route to creating self-documenting databases. I use the term ‘self-documenting’ in the sense that one can attach the documentation directly to the database itself and its objects, rather than create a separate document. Basically, you use the extended properties of data objects to apply your own properties to the metadata.

The advantage of using the extended properties is that the documentation, notes, and so on stay with the databases and can be used as a “live documentation” mechanism. They are backed up with the database, and scripted out with the build scripts.

In Your Company SQL Server Landscapes, we must follow below Practices to Create a Standard, Self-Updating, Ready-to-go and Single Interface Visibility Documentation for All Teams. To achieve this Purpose, All Teams Must Follow Below Steps

1. Get Yourself Conversed with SQL Extended Properties. Click here to know Commands
2. Always Run All the SQL Commands in Following Sequences: Dev-->Test(QA)-->Prod and Verify with Visual Studio Database Schema Comparison Tool (Open .scmp file in VS and compare. For more info about VS-SCMP Click here or Google) VS-SCMP is a part of VS-SSDT Tools and we already have the License
3. Whenever you Add/Modify SQL Objects(Like SP/Views/Function/Tables etc), always Append Extended Properties Command with original Script like:

   USE [YourDatabasename]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE PROCEDURE [dbo].[Usp_MyStoredProc]
       @paramList INT
   AS
   BEGIN
       SET NOCOUNT ON;
   	/*
   		Business Logic Goes Here
   	*/
   END;
   GO
   /* Following Extended Properites need to be Added  */ 
                                   
   
   
   
   
   EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'Purpose of Usp_MyStoredProc' , @level0type=N'SCHEMA',@level0name=N'dbo'
   				, @level1type=N'PROCEDURE/VIEW/FUNCTION',@level1name=N'Usp_MyStoredProc'
   GO  
   
                                 

Verify Documentation with Architecture-->Documentation in SQL RealTimeDocs™. Kindly check below

First Phase: Documentation is Incomplete

Second Phase: Documentation is Partial

Third Phase: Documentation is Completed

What it can't Do

SQL RealTimeDocs™ is a tool to extract Real time information from Microsoft SQL Server System Catalog Views. If the catalog views doesn't have information which user expects then this tool will not be able to answer those queries

For Eg.:

1. List down all Stored procedure which is unused since last 1 month
   • Because this information is retrieved only if SQL profiler is turned On in Production for 1 month which is a resource intensive activity. However sys.dm_exec_procedure_stats catalog view will display this information since last server Cache refreshed. These activities can flush it: Restart/Recompile/DBCC FREEPROCCACHE/Low Server Memory etc .It display that information which is useful but it is incomplete. Kindly check with admin team for more information.
2. List down all the SQL objects(SP, Views, Function etc.) managed by other teams which refer my SQL object in my database.
   • Basic Idea is to find out how many teams will be impacted if a particular object is modified. For simplicity, let's focus only on Callee SQL objects which are either
     • Local to your database server
     • Remote SQL Objects which are connected via Link Server.

   It is possible to fetch Cross-Database and Cross-Server reference in our Database which is described as remote objects in our Database but not the other way round. It is equivalent to Finding whom I know [Easy] Vs Who Know me [Very Difficult] . Kindly check sys.sql_expression_dependencies for more details.

   To Find Cross Database dependencies, we have to search sys.sql_expression_dependencies of all Database except your database [MyDB] and find cross database reference of MyDB in them and compile the list. It is doable but Highly Resource Intensive . Current version have Linkserver and Database reference check in all databases of selected database server.

   To Find Cross Server dependencies, we have to search sys.sql_expression_dependencies of all Databases of all Linked Servers except your MyServer-->[MyDB] and find Server reference of MyServer-->MyDB in them and compile the list.
   Since Link server is a one way Route, so if your transactional server doesn't have link to Source Server where callee Object exists, then it will never be shown in your list

3. Able to get 1-click PowerPoint Presentation of my entire database
   • You reap what you sow is the philosophy of documentation. Server Architecture and Database objects list can be retreived from Architecture and SQL Objects Dropdown respectively. But to define and describe those objects, it require storing that Description/Process-Mapping in Extended Properties. If you have added Extended Properties With Correct Data (Purpose ,Process Mapping and last modified By etc.) , then you'll get tablar format of all the Objects like Database(yes it is also an object), Stored Proc, Views, Functions, Tables etc. with their Key-Value Pair. The tablur format has 2 classification with Search Functionality:
     • Type of Object
       • Database Level
       • Interface Level(Stored Proc, Views, Functions etc.)
       • Object Level(Table, Columns, Index etc.)
     • Type Of Documentation [Key]
       • Purpose
       • Process Mapping
       • Last Modified By(Not applicable for all objects)
   Use that data to prepare your presentaion.