Press "Enter" to skip to content

Category: Documentation

Documenting SQL Server Tables

Phil Factor has a way to create table documentation in source control and propagate it to the actual database:

It has always been a problem that documentation in the source, where it should be, is not then passed into the live database when the build script is executed. In a table, you have columns, constraints and indexes that you are likely to document using line-ending comments and block comments. You probably have a big block comment at the start, explaining the table. This information should be available in the live database. Microsoft don’t have a good answer and vaguely go on about adding comments in extended properties. Well, that’s fine but it hasn’t happened, unsurprisingly: Have you ever tried to do it? It is an almost impossible task, even with SQL Doc.

My solution is to execute my finely-documented build script as usual to create the latest version of the database, and then process the same script in PowerShell to add all the comments and documentation as extended properties in the right place in the live database.

It’s an interesting approach to a classic problem.

Comments closed

Fun with asciidocs

Sheldon Hull explains the value of asciidocs:

Documentation is such an important part of a developer’s life. I think we often take it for granted, and it’s an afterthought in many projects. However, as I consider my work, I know that I’m not reinventing the wheel very often 😀. Most of what I do is built on the back of others’ work. When I use tooling, I’m reading the documentation and using it as my basis to get work done. When I use my notes and blog posts as a reference, I’m using my informal version of knowledge gathering.

INVEST in documenting your work as you go, for the person behind you. You don’t find time to do it, you make time to do it while you work, as a first class citizen of your work, not an after-thought. Think of all the times you’ve had to dig for answers and save someone else that experience.

Sheldon is not wrong.

Comments closed

Managing SQL Server Documentation with JSON

Phil Factor gives us the gloop:

Metadata extract files are handy for documentation, study, cataloguing and change-tracking. This type of file supplements source because it can record configuration, permissions, dependencies and documentation much more clearly. It is a good way of making a start with documenting your database.

Here is a sample of a json metadata file (from AdventureWorks 2016). It was generated using GloopCollectionOfObjects.sql that is here in Github, and is being viewed in JSONBuddy. I use this format of JSON, a collection of documents representing SQL Server base objects (no parent objects) when I need to read the contents into MongoDB. The term ‘Gloop’ refers to a large query that, you’d have thought, would be better off as a procedure. Here is a typical sample of the output.

This is an interesting approach to documentation. I’m not totally buying into it, but that might just be due to my not having tried it.

Comments closed

Database Restoration and the Plan Cache

Andy Mallon has some tests for us:

If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine)

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

Yikes. That first sentence sounds like it is going to clear the cache for the entire instance.

Read on as Andy tests this and (spoiler alert) changes the documentation.

Comments closed

Documenting SSIS Catalogs

Dave Mason continues a series on documenting Integration Services:

In the last post, we looked at query options for documenting SSIS packages that are deployed via the legacy package deployment model. What about SSIS packages deployed to a catalog via the project deployment model? Is the package XML accessible so that we can query and shred it? I don’t know with certainty, but I think it is not.

However, there are .NET Framework interfaces we can use to programatically obtain SSIS catalog metadata. So for this post, I’ll soldier on without my dear friend T-SQL and proceed with PowerShell. Here’s a look at the projects and packages I was working with as I developed and debugged the script code. It’s a short list with just two projects, having one package each. The “DailyETLMain.dtsx” package is a sample from Microsoft’s GitHub repository.

Click through for Dave’s explanation and a link to the script.

Comments closed

Documenting Integration Services Packages

Dave Mason continues a quest for documentation:

The output is a tabular result showing each SSIS package, their Names and Descriptions, and the top-level task Names and Descriptions of each subplan. (In addition to the “DailyETLMain” package, we see metadata for some other plans related to the Management Data Warehouse Data Collector.) Note the 16 rows of metadata for the “DailyETLMain” package correspond to the 16 top-level objects of the package–the query doesn’t recurse into containers to obtain their object metadata. I decided not to attempt that–it seemed like overkill for documentation purposes. Another caveat: the results order may not match the order that’s mandated by Precedence Constraints in the Visual Studio designer.

Click through for the full example.

Comments closed

Documenting Maintenance Plans

Dave Mason wants to document each SQL Agent job, including maintenance plans:

I’m not a regular user of Maintenance Plans for SQL Server, but I run into them from time to time. I had a task to document all of the SQL Agent jobs, which for a number of environments, included some Maintenance Plans. This became a more time consuming task than I had anticipated!

I had known beforehand that Maintenance Plans were SSIS packages under the covers. So I started with a query on msdb.dbo.sysssispackages. I also knew that SSIS packages are essentially XML data. 

Take advantage of the bulk of time Dave had to sink into this.

Comments closed

dbatools: the Book

Chrissy LeMaire has an exciting announcement:

After nearly 10 months of work, early access to Learn dbatools in a Month of Lunches is now available from our favorite publisher, Manning Publications!

For years, people have asked if any dbatools books are available and the answer now can finally be yes, mostly. Learn dbatools in a Month of Lunches, written by me and Rob Sewell (the DBA with the beard), is now available for purchase, even as we’re still writing it. And as of today, you can even use the code bldbatools50 to get a whopping 50% off.

They’re in active book development, so buy a copy now and watch as the book evolves.

Comments closed

Self-Documenting Power BI Apps

Matthew Roche wants to build self-documenting Power BI applications:

Power BI is constantly evolving – there’s a new version of Power BI Desktop every month, and the Power BI service is updated every week. Many of the new capabilities in Power BI represent gradual refinements, but some are significant enough to make you rethink how you your organization uses Power BI.

The new app navigation capabilities introduced last month to Power BI probably fall into the former category. But even though they’re a refinement of what the Power BI service has always had, they can still make your apps significantly better. Specifically, these new capabilities can be used to add documentation and training materials directly to the app experience, while keeping that content in its current location.

Click through for an explanation.

Comments closed

Deep Thoughts on SLAs

Ben Slater has a great post on service level agreements:

It should set the customer’s expectations, be realistic and be crystal clear, with no scope for misinterpretation. Well, that’s how they are meant be. But unfortunately, in the quest for more sales, some MSPs tend to commit themselves to unrealistic SLAs. It’s tempting to buy into a service when an MSP offers you 100% availability. It is even more tempting when you see a compensation clause that gives you confidence in going ahead with that MSP. But hold on! Have you checked out the exclusion clauses? Have you checked out your responsibilities in order to get what you are entitled to in the SLA? Just as it is the MSP’s responsibility to define a crystal-clear SLA, it is the customer’s responsibility to thoroughly understand the SLA and be aware of every clause in it. That is how you will notice the shades of gray!

We have put together a list of things to look for in an SLA so that customers are aware of the nuances involved and avoid unpleasant surprises after signing on.

This is important to know when you’re using a managed service but it also works internally: as a developer or manager on a product that people (presumably) rely upon, how you deal with outages and issues matters a lot.

Comments closed