SQLChicken.com

SQL Server DBA Tips & Tricks

Dec
28

By

Beware Vendor Databases

Category: Syndication Tags: , , 5 Comments

You're crunchy and taste good with ketchup

HERE THERE BE DBAs

This is just a quick post to serve as a warning sign to those who are DBAs in shops where you routinely get vendor pre-packaged databases (which should encompass about 99% of you). Today we had a vendor installing a new application which required a SQL database, to which I am ever thankful. Unfortunately this database, as I came to discover after a discussion with my junior DBA, that it was actually a restore of a database that was a created after mashing a bunch of data together from disparate systems. Ouch.

Well long story short after the install I took a look at the database server’s (disclaimer: I put them on a shared SQL instance since the app is relatively small) error log to make sure nothing crazy was happening since during the install they had issues with account creations. FYI that turned out to be a small “we need sysadmin” rights issue to which I said ‘NAY!’ and granted them temp rights to complete their install and then revoked those rights when it was complete. Anyhow the error log showed me something interesting:

Message
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Well that’s interesting. I quickly Banged it out and the first solution I found was a KB article (KB917828) that said these were “normal” messages you’d see in a database restored starting from SQL 2005 SP2 and upwards. I read on as I needed to know WHY these messages appeared and BAM, something hit me and sent off alarms:

The whole procedure cache is cleared when certain database level operations are performed in the following scenarios:

  • A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.

Auto_close enabled on a database on my server? NOT ON MY WATCH! I quickly jumped over the database options and sure enough not only was AUTO_CLOSE enabled but AUTO_SHRINK as well. For those of you curious as to why I’d freak out about this, these options are really resource intensive and this being a shared instance I really don’t need that hassle. If you’d like to read about how/why these two options are bad check out Tim Ford’s blog at http://www.ford-it.com (aka @SQLAgentman on Twitter) and read his post on Dealing with SQL Shrinkage.

So remember kids: check, re-check and double-check what your vendor is doing when they setup databases on your systems. Ideally they should provide scripts for you to be able to validate and evaluate before you just “slap it in” to your production environment.

Share

  • Pingback: Tweets that mention Beware Vendor Databases | The SQL UPDATE Statement -- Topsy.com

  • Kelly

    OMG!! I feel your pain. I still have a vendor crying that they need sysadmin rights on the production database. The whole thing is a port from MySQL and I don’t think any of their developers know what an index is.

    But I guess that’s what we get paid for.

    • http://sqlchicken.com Jorge Segarra

      Ha, sorry to hear that Kelly! One thing I learned from Jonathan Kehayias is that you can work with the vendor to help them figure out exactly what they really need. Not only will you be doing them (and any future customers) a favor but you’ll be honing your skills as well. Run a server-side trace on the database and take a look at exactly what the application is doing. If its only doing SELECT calls or maybe executing some stored procedures at most then they definitely don’t have a case for sysadmin rights.

  • http://www.abreuinnovations.com Joseph Abreu

    I believe this happens most often with databases developed in SQL Express by developers and then packaged without any DBA review or SQL Server quality analysis of those packaged databases. AUTO_CLOSE defaults to enabled in every SQL Express environment I’ve set up for development and I have it on my checklist of items to review whenever restoring/attaching databases provided by a developer/vendor other than myself.

    As for sysadmin requirements by vendors, haven’t developers ever heard of using roles before??? I’m sure their application went through a QA process but often the database they provide with their application doesn’t get as thorough of a QA review from the perspective of a DBA.

    • http://sqlchicken.com Jorge Segarra

      Good call Joseph (on the QA). I especially like your awareness of SQL Express’ action of turning AUTO_CLOSE to on, many wouldn’t think to look at something like that. QA review is something we should all try and practice even if you don’t have something official setup we can definitely benefit from doing something on our own (like you have!)