Aaron Bertrand has been writing an excellent series about Bad habits to kick, highlighting some of the bad practices, primarily, in the areas of T-SQL coding. I’m going to steal his idea and comment on the bad practices I have seen in managing databases.
If we are going to play a phrase association game, the first thing that comes to mind after “bad database practice” is “managing databases without a central DBA inventory or repository”.
It seems to me that there is no need to argue for the necessity of a central inventory of your database assets in order to manage them effectively, just like you need to keep an inventory whether you are Wal-Mart or a corner store. Sadly though, the need for a central DBA database inventory is not universally recognized, and not all shops rigorously keep track of their database assets or take full advantage of the inventory, if they have one. .
I will submit to those who are not maintaining a central DBA inventory diligently and those who are maintaining somewhat an inventory only haphazardly that you can improve your database management significantly if you invest just a bit of time to create and maintain a central DBA inventory and integrate it into your database management practices.
Briefly, a DBA central inventory should have information on all your servers, your databases, and their configurations. This may include information that can be automatically collected such as the service accounts, the startup parameters, the collations, the sp_configure settings, the drive configurations, the filegroups and files, the most recent backups, and so on. It may also include information that you need to enter manually such as the preferred clustered nodes, who owns which database, what app uses which database, the key contact info for each database, the maintenance windows, the status of each database in its lifecycle, and so on.
For the central inventory to be useful, it is absolutely necessary that the information is comprehensive, covering everything about your SQL Server database environment across the enterprise. Essentially, it should be everything in your instances and databases minus actual user data, i.e. all the meta data. So in addition to server instances and databases, you should also record the configured linked servers including their configurations, jobs, database mail configurations.
Since most of the information can be collected automatically, the marginal cost of being comprehensive is very small, but the potential added benefits are enormous. Just consider the case when your server is trashed and you need to rebuild it, but to your horror not all the information for rebuilding the server to its exact pre-crash state is available. You do not want to be in that situation.
You can argue that a lot of meta information about the databases can be obtained by restoring the databases from their backups, and therefore the value of keeping the information in a separate central inventory is not great. Well yes, you can always try to retrieve the information from restoring the backups. It’s just that sometimes you may not have the backup (okay, that really should not happen, but it does happen). On a more practical level, it often takes time to do the restore when it would be much quicker to just query the inventory.
When you do have a central inventory in place and it is being diligently kept up to date, you can effectively drive a lot of your database administration with that central inventory. It will for sure make it that much easier to meet your management reporting needs. You never know what kind combination of aggregations your management may ask of you. Some more typical ones include summary reports on the storage space allocation and usage on various criteria (e.g. by owner, by app, weekly/monthly trends), summary reports on certain severe SQL errors that have become interesting of late across the enterprise, and certain SLA summaries covering all the servers.
A DBA central inventory can also provide much relief to your day-to-day operations. For instance, in addition to whatever monitoring/alerting setup you may have in place, often you may need to do a sanity check of your environment on demand. You can develop the checks for one server, and then just drive the same checks across all the servers with a list of the servers from the central inventory. How about getting a list of servers that are not patched to a certain level? Well, that’s just a simple SQL query away.
Moreover, a well-kept DBA central inventory can serve as an effective communication tool among the DBAs, making the work as a team more consistent. After all, you do not want critical information to be kept in some DBA’s head. While that DBA can do an excellent job, the other DBAs may struggle to get the information right, resulting in wasted time or inconsistent deliveries in meeting the customer request.
And if you are a best practice freak, you’d love to have a DBA central inventory because a lot of best practices can therefore be checked through running some queries. For instance, checking the consistency of certain database configurations across the enterprise is then just a matter of running a query.
Much more can be said about the benefits of maintaining a comprehensive and up-to-date DBA central inventory, and using it to drive many of your database administration tasks.
If you do not currently have such a central inventory in place or are not taking full advantage of it, I hope this post will get you to think about it and perhaps decide to invest some more time in it to boost your database administration to another level.