So for 10+ years I’ve been a “vanilla” SQL Server Database Administrator. By “vanilla” I mean that I’ve always worked only with the standard out-of-the-box tools for SQL Server (e.g. SQL Server Management Studio, and a handful of custom PowerShell scripts to automate things for me).
Nowadays, with so many 3rd party tools available, it’s a bit hard to “make the right choice”. Regardless, I went on and searched around to get any recommendations from Mr. Google, and I organically discovered SQL Tools from Devart.
When you take a look at their website to get a quick glimpse at what the tool offers, it’s amazing! Out of all the tools available within the suite, one got my initial attention, and it’s the SQL Documenter for SQL Server. Why? Well, because in those 10+ years in the industry, I’ve worked in many places, and I’ve never seen a single person that does such thing; I know that it is something super important within any software development practice, but it seems more like a myth when you have certain miles traveled through this journey in the IT world. That’s why I gave it a shot and want to share with you some insights of why this particular tool can differentiate you, the star SQL Server DBA, from the competition; I’m even thinking of covering the rest of the tools in future articles, so stay tuned!
Initial consideration
Since this is my very first time giving a go to one of the products, I will be installing the whole suite to talk about SQL Documentation and have the rest of them ready for the next rounds of articles.
Installation
I went with the Professional Trial option, you know, because we are pros right!
Here you can download the bundle of SQL tools.
I guess at this point it’s a bit obvious to mention that this will be a Windows only thing, but that shouldn’t be a showstopper to give it a worthy shot.
As soon as you open the installer, this is what you’ll get.
Now, once you reach this screen, you can cherry-pick whatever you want (I went with all of course ;)).
After waiting for the installer to do its thing, you should be good to go.
If you take a look at your desktop, you’ll see a bunch of shortcuts for each tool:
*See those PowerShell scripts in there? I told you I was a “vanilla” SQL Server DBA ;)
As soon as you open dbForge Documenter, you’ll see the following screen, and since it’s the very first time we’re interacting with it, we’re left with no choice but to click on “New”:
The first thing to do is pick a SQL Server instance connection, so we’re going to have to create one by clicking the “Manage” button:
In the next screen we click on “New”:
In this screen you’ll see your classic fields to fill for a connection string, the server name, authentication type, database, and user & password if necessary. At the bottom you’ll see a dropdown with some options to categorize the connection you are creating:
But that’s not all, if you click on the three-dot button next to the dropdown, you’ll have the option to create your own category and go as wild as you like:
In the “Advanced” tab you’ll have the panel to specify any additional parameters for your connection string, for instance “ApplicationIntent=ReadOnly” to trigger read-only routing for an AvailabilityGroup, or “encrypt=true” if what you want is to work with an encrypted connection, just to name a few:
If you enter everything correctly, then you’ll see a confirmation that the connection succeeded:
With that, let’s get to the main dish of the evening:
In this screen you’ll see all the databases currently sitting within your environment, and if you click one of them, you’ll see in the left section of the screen a list of properties and toggles for the selected database:
I know that the topic of this article refers to a “documenter” thing, and so far nothing like that has been demonstrated… until right now:
If we tick the checkbox for the master database, the “Generate…” button that was previously greyed-out will be made available, so let’s click it.
You will be presented with 3 different options to generate your documentation: HTML, PDF and Markdown. For this demonstration, I went with HTML, since it’ll obviously be interactive and more fun to check around.
After generating the document, you’ll see the following, right after opening it:
You will notice that it has a similar vibe as the screens presented above, without caring too much about the watermark text of course (since we’re using an evaluation license).
Nothing too fancy here, just the description of the database along with all its properties.
Now let’s try with a user database to see what we get:
By expanding the available tables and clicking on one, we can see that it gives us extremely valuable information. Within a single view you can take a look at indexes information, the SQL script that is used to create the table, and if there are any dependencies that the table has.
Use cases
• A DBA could use this tool to generate the documentation of the desired database objects, prior to the deployment of a specific software release, as a means to have a comparison point in case something weird is reported.
• It is a great way to have at least a backup of the definition of the database objects. Imagine that, instead of a regular backup strategy, you decide to backup data using bcp, then with this you’d have the scripts to create the tables and then the bcp output files to load the data. I know that it sounds like a long shot, but not a totally impossible one.
• If you have multiple SQL Server instances under your umbrella, then this is a magnificent tool, not only to document all of them properly, but to use it as introductory material for new hires coming to your team.
• With this documentation, a DBA could easily spot users/logins, in a Production environment, that have high privileges assigned and shouldn’t.
Final thoughts
• This tool would’ve smoothen my way around many SQL Server instances across all the companies that I worked at. As I mentioned initially, I have never seen anything like this in any of the places I’ve previously been, and I’m strongly thinking that it’s never too late to start with the implementation of those best practices that we’ve always heard, but have been nothing but smoke so far.
• I haven’t gone over the rest of the tools within the suite yet, but with what I’ve seen so far, I personally think that the investment that any company could make on this product is totally worth it. Especially for teams of SQL Server Database Administrators that, in my experience (and I’d guess that statistically too), are rather small and don’t have much time in their hands to focus on important stuff like this.
Top comments (0)