THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Bad database practices: managing databases without a DBA central inventory

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.


Published Tuesday, October 20, 2009 2:26 PM by Linchi Shea



Sam Greene said:

Rodney Landrum has a nice series of articles on how to achieve this using SSIS:

October 20, 2009 2:26 PM

Linchi Shea said:

Thanks Sam! I didn't get to address the question on how to build and maintain a DBA central repository because that's not the focus of the post.

There are obvious many different approaches to building a DBA inventory, and people are indeed using differet approaches. SSIS can certainly help you get some data. Where SSIS may not be most convenient, you could always use some scripts or build the entire data collection component with scripts or in a language like C#, VB, etc. Ultimately, what tool you use to build and maintain the inventory depends on where your skill set is. After all, you don't want managing the inventory itself become a full-time job when it really should just be there to help improve your real job.

October 20, 2009 2:51 PM

WIDBA said:

The DBA group at my company has a very active inventory database.  The time spent implementing pales in comparison to its value.  Even for small things like comparing permissions between to environments, or checking any DB with a high VLF count, etc. No to mention its historical data value for trending, etc

October 20, 2009 4:18 PM

jeff_yao said:

Very well written, Linchi. I cannot agree more with this central repository idea. However, sadly, most managers still do not see the benefit of this and as such, there is no time approved to work on this type of project. This is central repository project can be very comprehensive and need lots of pre-planning when the repository needs to be shared by many DBAs, and need to be udated / versioned etc. It is no small work but it will absolutely grow the most rewarding fruits.

October 20, 2009 5:11 PM

rfrf said:


December 13, 2009 5:11 AM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement