Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Challenging the Tyranny of Third-Party Vendors: A DBA’s Manifesto

Over the years, I have dealt with a lot of third-party applications (and their vendors) that use SQL Server as their back-end databases. It has often been an uneasy relationship, fraught with pain and tribulation. The overriding feeling I have gotten from most of these vendors is that they could care less about the DBAs who have to install and manage their application’s databases.

Whatever excuse third-party vendors have for ignoring the DBAs who tend to their product’s databases, I think it is time that DBAs begin to fight back, and not take their uncaring attitude any more. Below, I have proposed a DBA Manifesto, a list of demands that we need enforce among all the third-party vendors we work with. If we stick together, and take the same stance, then perhaps we can get these vendors to be more helpful and cooperative.

Here are our demands to these third-party vendors:

  • Given the thousands, tens-of-thousands, or hundreds-of-thousands of dollars we spend on your product, please document how it works. Don’t force us to take the time to figure it out for ourselves through trial and error (we are already too busy), or to call tech support to get answer that could have easily been included in the documentation.
  • When we call tech support, have somebody available who not only thoroughly knows your application, but understands how it interacts with SQL Server. When I call up, complaining about a query executed by your application that is missing a WHERE clause and is returning over a million rows to a list box on a user’s screen, I expect that person to understand what I am talking about.
  • Don’t charge us $1,000+ a day, plus expenses, to install your software, when all we really need is decent documentation.
  • If you do force a consultant down our throat (as part of the total “sales package” for your application), have the decency to send one who knows your product, and SQL Server. More often than not, the expensive consultants who have shown up at my door barely understand how their product works, let along know anything about SQL Server. I have seen many consultants spend their entire onsite time on the phone, having someone step them through the installation process of their own application. Hey, if that’s all it takes, I can talk to the same person and my company doesn’t need to waste money on consulting fees.
  • Don’t tell us that only “your consultant” has the ability to configure SQL Server for your application on our hardware. Your consultant is not touching my SQL Server. They can instruct, and watch, but not touch. My former manager actually threw a consultant out from the building once because he demanded to be the one who performed the SQL Server configuration side of the installation.
  • Have a real SQL Server DBA on your staff who can guide your developers so they write properly optimized code, and so that if we have a question, we can speak to someone who really understands SQL Server.
  • Help us size the hardware needed to run your software. Give us some guidelines so we don’t end up under-sizing or over-sizing the hardware we buy to run your application.
  • Please ensure you have tested your software so that it meets our load requirements. On more than one occasion, I have found out that we were the vendor’s largest customer, and that they had never load tested their software for a company of our size.
  • Don’t blame SQL Server or Microsoft for slow running code. You are the company who has written the application.
  • Don’t tell us that your application requires SA access to run.
  • Use a properly normalized database design, write optimized queries, and include appropriate indexes.
  • Write your code so that it is optimized for SQL Server, not written generically so that it can also run with other databases. This almost always hurts performance.
  • Don’t ever tell me I can’t add indexes or security to your database. I have had vendors tell me that if I make any change in their database, that it will void our support contract. Those are fighting words to me. The only reason I have to do this is because you don’t know what you are doing, and I have to fix your mistakes.
  • When it’s time to upgrade, provide a clear migration path, and perform the database migration automatically. Don’t expect me to manually port the data from your old schema to your new schema.

Do you agree with these demands? Do you have other demands? Will you try to enforce these upon your vendors? Tell me what you think. DBAs, we must unite against the tyranny of third-party vendors!

 

 

Share this post :

Comments

Posted by K. Brian Kelley on 6 February 2009

This isn't just a DBA's manifesto. It's pretty much a system administrator's manifesto, too, with the appropriate substitutions.

Posted by Tim Mitchell on 6 February 2009

Hear hear!!

I'll try to work the above into any future contracts.

Posted by cherie j sheriff on 6 February 2009

Enthusiastically Agreed!  I grow weary of having to send their SQL back to them to show them how it works right when written correctly.  

I also vehemently agree with not letting vendors touch my servers.  I know they can get through it all faster since they have done it a thousand times, but I am the support person and I have to know all of this information anyway.

Posted by Jeff Moden on 7 February 2009

Oh, very well done, indeed!  This is definitely a keeper.

-----------------------------------------------------

"Write your code so that it is optimized for SQL Server, not written generically so that it can also run with other databases. This almost always hurts performance."

-----------------------------------------------------

BWAA-HAA!!!!  So much for all the folks that believe in the myth of portable code!  

Posted by Hugo Shebbeare on 8 February 2009

What a sweet list for these third party persons to contemplate.  It sounds a bit like a mega list on a job description for a candidate that never exists.  Jokes aside, I'm an idealist/perfectionist as you are, but you cannot expect a poorly paid and under-trained front-line tech support guy to have all the details, do you?  Utopia doesn’t exist, and until that time comes, we’ve all still got many reasons to justify our indefatigable workload.

Agreed, the $1k a day thing sounds ridiculous, and I've seen auditors who charge that much without the slightest clue how to actually apply segregation of duties within a development environment, nor do anything to help the DBAs there dealing with the hoard of developers trying to wander freely into production; Even on one occasion (insert your nemesis accounting firm here) telling me that a DBA was intruding by locking down production database access (wtphrack!!_).

The hardware issues you've had are part and parcel for DBAs who are not involved with the architecture building process - the network/server administrator will simply make sure the disks are redundant (one raid 10 volume for example) and take no consideration with respect to the disk contention, since they've never seen the snowball effect of transaction file writes and data file read/writes occur in front of their eyes (crashing replication)…or shy away and use the database engine/application problem as a scapegoat.

Mrs. Sherrif (or Ms., I don't know sorry) if consultants are coming in to run stuff in production, at least force them to go through the hoop of a COBIT style (or ITIL equiv.) change/risk management document - hence forcing procedure on them, which they are usually adverse to...especially when it comes to security, the SA thing you mention is typical (huge wag of the finger).  A developer who controls production, will always (sorry to my dear developer buddies, however there are very few exceptions here) decide that to manage security is to give database owner access, or worse SA as you mentioned already, to the entire application, meaning, hey I'm too lazy to actually manage security. It's as if to say, 'hey give us the keys to your house and bank account, and don't worry, nothing's bad is going happen. 'Mon oeil’ as they say in French.  

Anyway, whatever the case, thanks (!) for the manifesto mate - like I mentioned when we sat down in December at SQL teach, this is all a very good therapy session for DBAs :)

Posted by YSLGuru on 10 February 2009

I use to work for a software provider who practiced every one of these points in the manifesto and I can tell you first hand that every one of these points from Brad is dead on.   Because I was on the inside I can give you an inside explanation for why software vendors (at least the ones I worked for) engage in most of these bad practices that Brad has listed:

Point 1 - Documentation “…please document how it works.”  - This seems like a no-brainer until you’ve been on the other side. When you document something you have put in writing exactly how something should work and this can be a problem.  How so?  If something is truly broken (and not just of bad/poor design), without documentation detailing how it is suppose to work it’s nearly impossible to make the vendor agree that is broken verses merely being a difference in how it was designed and how the client expected it to work. A secondary reason for this is that poor or insufficient documentation helps artificially drive up the demand for additional paid consultation/support from the software vendor.  It also helps minimize outside competition for supporting the product by keeping all knowledge of the product ‘in-house’.

Point 2 - Experienced/Qualified Support: “..have somebody available who not only thoroughly knows your application” -  Low pay levels (counting on getting new comers to the industry looking for experience and or that first job)  and misplaced priorities (SQL = NOT IMPORTANT; PEOPLE SKILLS=SOMEWHAT IMPORTANT; Programming=VERY IMPORTANT)  and in the case of software aimed at vertical markets, an expectation of keeping market share with existing clients because of limited alternatives and costs associated with software platform changes, a solid base of qualified support people falls to the end of the list of priorities.  New features and or additional products or expansions to existing products are what drive new sales; providing good service thru well qualified support persons has no monetary value in the eyes of the decision makers.  Sure they pay a lot of lip service to ‘Customer Service’ but if it were more than lip service then this manifesto wouldn’t include this point.

Point 3 - Over priced Support Costs: “Don’t charge us $1K a day plus….when all we need is  decent documentation” – The number one way to generate new/additional revenue from new and existing clients is thru support and the best way to keep the cost of that support artificially high is to restrict knowledge of the product.  NDA’s (at least when applied to non-developers) are more about protecting a vendors ability to charge high rates for additional support then protection of anything proprietary.

Point 4 & 5 - The Consultants & Their Hands On Approach to Support: “…have the decency to send one who knows your product and SQL Server.” – This covers points 4 & 5.  The best way to prevent the client from finding out just how inexperienced the high paid consultant actually is, is to have them be as hands on as possible, keeping the client out of the loop.  The additional benefit is you prevent the client from gaining any more knowledge about your product and therefore still dependent on vendor provided support.

Point 6 - A REAL DBA on Staff: “Have a real SQL Server DBA…” – Except for those vendors with high exposure like Microsoft (as well as those vendors who actually strive for true quality service & support) you can forget management paying the high dollars for a certified anything especially in an area that programmers more often than not under-value.  Because the vendor often has gone so long without proper SQL guidance, they are unable to find any one decent to work for them at anything other than top dollar because no DBA who knows what they are doing is willing to take on such a mess without being highly paid for it.  Besides any good programmer will tell you they don’t need no stinking DBA tell them how to use T-SQL; they after all do have a copy of SQL For Dummies in the bathroom that they can even take home after hours if they need any extra study/research.

Point 7,8 & 9 - Hardware Specs, Software Testing & Blame SQL for Slowness : “Help us size the hardware needed to run your software... ”; “..ensure you have tested… of our size”; “Don’t blame SQL Server..” – these 3 go hand in hand and they are all about being vague so as to provide protection to the vendor on what is expected from them and where to place fault when something is a problem.  They also help hide the lack of SQL related experience their programmers usually suffer from.  Without providing detailed specs on what exactly is required and how something should work its hard to get the vendor to take sole responsibility for dealing with the issue.

Point 10, 11 & 12 - SA Level Access, Normalized DB & Optimized Code for SQL Server: “Don’t tell me your application requires SA access to run.”; “Use a normalized DB..”; “..code optimized for SQL Server.. ”; -  Because the developers are normally inexperienced with T-SQL let alone the specifics with SQL Server security, it’s far easier for the vendor to assume they have full access and write their app to work that way then try to code with SQL Server security in mind.   Not requiring SA access would require far more testing by the vendor and most likely result in less functionality in their product since some parts of it would almost certainly fail to work in a properly secured environment.  It’s far easier for a network admin to give everyone local admin level access to their workstation and then blame the user when they download something bad then lock it all down appropriately and then deal with application specific issues where one or more part of an application needs custom work because it can’t function in a non-admin enabled environment.  In order to have a decently normalized DB you gotta have someone who knows something about normalization and that means paying more money than you would for just a programmer who is familiar with how to write a SELECT statement.  Likewise, writing platform specific code verses generic SQL means doing something multiple times that you could otherwise do just once and it also means having to provide more avenues of support since you’d be support platform specific code.  The onsite consultation would then need to be $5k a day to cover the extra costs to the vendor.

Point 13 – Customization like Custom Indexes: “Don’t tell me I can add indexes..” – Any change to a vendors DB results in something that differs from what the vendor has their support people setup to deal with and when you have support people who are basically doing support by reading aloud answers from an in house FAQ system that the vendor has setup for customer support, there is no system in place to handle out-of-the-norm scenarios.  And so the only way to keep support costs down and avoid hiring truly qualified people is to restrict what a customer can do.  A caveat to this is that while the vendor may frown upon this kind of customization, you’ll find that often don’t prohibit.  This is because a customization like this gives the vendor an out for providing support when something happens.  It doesn’t matter if the index you added is in no way related to the part of the DB you are having problems with, by changing the DB from what is ‘officially supported’ you provide the vendor with a legal out when they are unable to fix the problem.  

Point 14 – Upgrade Paths: “..provide a clear migration path..” – This is a classic example of what happens when a poorly designed database grows past it’s limit and has to be changed and the software vendor has no qualified SQL trained people in house to handle the problem.  Just as with almost every point in here it comes down to costs and lack of priority in the area of SQL and Relation Database technology in general.  

Programmers/developers are procedural oriented and the set based thinking in SQL is often like kryptonite to Superman; the vendor just doesn’t get it and therefore comes up short on how to properly deal with it resulting in the customer having to pick up the slack.

Posted by Brad M. McGehee on 10 February 2009

YSLGuru, thanks for your insight.

Posted by Daniel Kast on 12 February 2009

My sentiments exactly :-)

especially:

>>>

Please ensure you have tested your software so that it meets our load requirements. On more than one occasion, I have found out that we were the vendor’s largest customer, and that they had never load tested their software for a company of our size.

<<<

Yes, Yes, Yes

>>>

Don’t tell us that your application requires SA access to run.

<<<

Not even dbo/db_owner !!

This applies also to some of Microsoft's SQL Server based Apps (eg Sharepoint, SCOM)

Posted by Rob Smith on 12 February 2009

Whilst I do agree with many of the comments on here, I think it is harsh to put many of the points on the shoulders of the developer - the vendor, yes, but not the developer.

As a DBA, would you expect to know how to make the front end application sing and dance?  No.  That's a developers job.

As a developer, would you expect to know all the ins and outs of the backend? No.  That's a DBAs job.

There are cross-overs, obviously, but it is up to the vendor to provide the correct skills to get the job done.  

As a developer, I make the best of a bad job and try to get the functionality required in the best manner possible without necessarily having the skillset available to fully secure, optimise, and index the backend database.  That doesn't mean I don't care, it means I just do not have the time or resources to research into full detail.

Our customers are continually asking for new devlopments to be added to our software, so all resources are poured into new features to keep the customer happy whilst at the same time neglecting fundamentals.  Again, not the developers doing, more a philosiphy of the vendor the developer is working for.

That said, I personally work for a very small company with a very small complement of staff.  We are always hitting above our belt when it comes to our competitors and installations.  I would not expect the company I work for to be able to afford the staff needed to fully implement a global model of security, whilst still maintaining the speed of development required to continue to be relevant in the current economy.

That is just the opion of a developer, but what do we matter, we just make the DBAs life hard work!

Rob S.

Posted by Anonymous on 12 February 2009

Rompo il silenzio del mio blog che dura da oltre un mese (questo periodo sono stato un po indaffarato

Posted by sjm on 12 February 2009

The other point although it may be covered by migration is that the vendor should agree to certify their product to work with Microsoft SQL Server patches and security hotfixes. We often have the problem that because we are required to run certified applications, when they touch the product, that we cannot apply security fixes to SQL Server because the vendor will not certify their application to run with the patch/hotfix for months if not years. Same problem with version updates.

Posted by ronang on 12 February 2009

sjmilner, as a web developer, SQL Developer and DBA myself, I would love to know how anyone can ensure that their application WILL work with security patches that Microsoft themselves haven't released yet or when released exactly what they do? Hence the "cannot certify" rule. I definitely agree with the points made by Brad though. However, majority of them could be easily resolved if the DBA is involved with the actual purchase of third-party applications to ensure that the Vendor does not commit the  offences. So maybe this manifesto needs to be aimed at the in-house Business decision makers (who generally have no-idea about what the pitfalls might be). Just a thought.

Posted by malathi.mahadevan on 12 February 2009

I appreciate and agree with almost everything Brad has to say. I work as a DBA now and this is definitely what I face with regards to third party application vendors. I want to say one thing though, I have worked for some of these vendors a long time ago. Everything in the business world as someone said, boils down to $. Support is usually the least budgeted in vendor companies, they want to keep the product going without it as much as possible. They won't get a know-it-all guy for cheap $. Documentation...well as a DBA do you have all the server settings/stuff neatly documented? I would love to, but who has time? More often that not companies rely on what experienced people know. It is not that different with vendors. There are usually one or two guys with them who know the product including SQL Server well. When I work with some of them I use the marketing contact to get hold of 'that guy' and talk to him instead of the 800-support. It is not true of all vendors but for some it works much better.

Posted by amarshall on 12 February 2009

I agree 100%!!  We have some purchased software with a SQL backend, in which the designer thought rowguids as primary keys on all the tables was the perfect thing to do..OUCH!!!

I amend the manifesto to say that if your sql database has all it's primary keys as rowguids, then you need to change this before we even consider purchasing your software.

Posted by RichJ on 12 February 2009

Excellent points!  Some are painfully close to me...

The only thing I'd add is something danced around in multiple points -- if I can optimize the database (e.g. indexing) or your software, our company -- your customer -- will be properly compensated for the information.

And extending the contract for your, erm, "inadequate" support an extra year doesn't cut it, since it's costs us more time to call you to fix the problem than to fix it ourselves.

Man, one could rant on for days like this!

Posted by 2 Tim 3:16 on 12 February 2009

Bottom line - you're at the mercy of the vendor (That is if you still want to keep their product).  What are your options if they do not cooperate?  Most vendors are willing to help with minor issues.  I get a lot of "we'll work on that in our next release", which of course is 3 years away.

I still have Vendor Apps running on SQL 2000 because the vendor won't certify their product for SQL 2005.  Trying to apply a patch is rediculous.  My recommendation to the company was to replace the vendor.  Not likely if the product has been imbeded for some time.

the 'sa' thing drives me nuts.   I spend hours will the developer adding and taking away access to see what they "really need".  In the end, I have to give it 'sa' because everything I try breaks the product.

These are all products I took over support for when I arrived.  I'd certainly do a thorough analysis for any new vendor product and recommend against it if it doesn't meet the requirement sepcified in this blog.

Great post... thanks  

Posted by krishnaraj9 on 12 February 2009

Excellent approach as a DBA towards third party Products Support and installation.

My suggession is there will be some kind of dependable certification for all these 3rd party products for which they should follow certain procedures which will be decided by a group of well qualified Tech guys.

Makes easy to choose a product.

Not sure like is there a good one out there...

Most of the products works great if you just use it. But if you want to get 100% of it,you will face these issues.After working with a Tech Support personal resolving number of issues in a Well known monitoring software. We left with 12 issues which he said they need new developement effort for that.(Can't expect everything to work)

I really appreciate those tech support guys whois bold enough to say "Ya this is not the way our product suppose to work" rather than wasting our time to check and diaganose..

Posted by Dustin W. Jones on 12 February 2009

I agree 100%. We are the customers, we have the power!

Posted by Gaby Abed on 12 February 2009

How about redirecting the manifesto to the in house development teams as well.  For those of you who have a lot of internally created databases, put your foot down and insist, at the bare minimum, that they at least consult one of the DBA's on hand before they start AND they have some sort of documentation in place.  We have some wizards who can create complex queries that work and make my head explode when it comes time to decipher them, but when it comes to performance tweaks partitioning and indexing, we have to almost be pushy in insisting on them (for example, their idea of indexing is having a primary key, and that's it).

Posted by Eduard on 12 February 2009

a very recognizable story. i would start with microsoft with this list. cause if microsoft doesn't do that why would vendors do it ?

an recent, at work, example:

microsoft adviced (the msdn page has been changed in feb2009 though) that your applications should use windows collations in stead of the "sql 7 compatable sql collations". so we put effort to change from sql_latin1_blabla to latin1_blabla. it took some effort for our datawarehouse boys/girls also. then you see that

scom 2007 requires sql_latin1_general_cp1_ci_as collation.

(we tried latin1_general but scom gave problems with it)

ever tried to get the security of sharepoint tight ? we managed it a bit, we managed to get sysadmin of the hook unless they want to release certain things then sharepoint requires us assign sysadmin again.

Posted by Scooby on 12 February 2009

I can certainly understand all of your frustrations as I'm currently using such a product albeit not by choice.  My only question is that before spending tens of thousands of dollars did somebody not research all of the options and ask for references of other companies who use the product.  You could even create a forum to discuss issues related to certain vendors so that DBA’s would know what to expect.  It seems to me that doing a little up front legwork would save a lot of time and energy down the road not to mention money.  I agree that the demands are very important but the problems they point out can for the most part be avoided by proper vendor selection.  Take this list to the product selection meetings and demand to meet with the vendor's DBA-any vendor worth your time will agree to this.  In the case said vendor does not exist purchasing a bad product may be riskier than having it built in house.  In the best case you are aware of the issues up front.  The problem typically rests with management decisions to purchase from that specific vendor based on some gee whiz functionality that they can't live without or that they have a preexisting relationship, or worse that the vendor has accomplished the ultimate coup by becoming so ingrained in your company that you are stuck with them for the long haul.  .....These companies spend more money on sales and marketing than development and support.  That’s based on their own research and tells them it’s a more effective and profitable business strategy.  Why go to the extra effort if you can still ship the product and customers don't hold you accountable.  That reminds me that contracts should be written to include protection for your company against this kind of problem.  Also if you work for a company that really knows its stuff you should be part of the vendor selection process up front….  it comes down to upfront cost most of the time without a full understanding of TCO.  In our experience upfront costs may not indicate a good vendor, but when you know that their product is the right one and you've done your due diligence rest easy and take the plunge.  You will save thousands in wasted dollars on support nightmares and will be able to take that vacation this year without taking work with you.

Posted by Craig on 12 February 2009

I'm a development supervisor who also has had a fair amount of experience as a DBA on both SQL Server and MySQL, and I find myself in agreement with Rob Smith's views.

We already have a needless 'civil war' going on in our IT department between our DBA and our developers. One of the things our developers do is a great deal of cross-system integration - usually between commercial software packages and their back-end SQL Server databases; and we are often caught between the vendors and our DBA, trying to make our systems interoperate.

Please, Mr McGehee - don't go pouring more petrol on the fire; articles like this one are *not* helpful. People of your calibre ought to be actively seeking out ways to help all of us co-operate toward our common goals. Adopting an adverserial tone, an "us versus them" mentality, doesn't further that goal.

Posted by malathi.mahadevan on 12 February 2009

I think how much power we have as customers depends on the product and alternatives available. Even if there are alternatives, it cost a ton of money to migrate applications and train users and companies would rather keep what they have. In some ways we are at the mercy of vendores and what beats being at the mercy of MS? I have lost count of undocumented issues and bugs in interfaces that we have faced on every single one of MS products. I'd like to pass on *some* of these suggestions to vendor - like definitely SA password issue and normalization issues. We have mentioned audit issues and have them fix security before. I won't create a blame list for them though simply because one, we have all these issues internally and two, yeah as DBAs even our documentation leaves much to be desired.

Posted by Howard Perry on 12 February 2009

Hear, Hear!

Alas, it's the business people who buy the 3rd party software in the first place and don't think to raise these issues. DBAs are rarely involved in the decision and if they are, the salesman will reassure your business user, that "everything will be taken care of, so not to worry".  

The solution is that your company needs a formal process for thorough evaluation and testing of all internal and external software, before it is purchased and certainly before being put into production.   Such a process will tax all but most serious vendors, but of course, your users will resent it, eventhough you will be their first port of call, when something goes wrong - "the DBA is an IT person, so he understands these things"

This problem has been around in IT since the year dot.  It's plus ca change

Howard

Posted by Michael Osmond on 12 February 2009

I have been on both sides of the fence now (User and Vendor), currently vendor.  At the moment I'm the one building the install to do all of the things Brad asks for.  And most are simply reasonable design/architecture, or basic good practice.

From my experience I mostly sympathise, but......

I have to deal with good DBAs, bad DBAs, or no DBA and get the system installed and working.  So, we have to deal with the lowest common denominator, not just the gun DBAs.  I have had to write instructions on how to use Query Analyser to run a script in several cases.

Overtime we have built a process we know is reliable in doing installs and upgrades, and I'm happy to discuss it with the technical people.  But I still find I have to defend it from time to time against someone's pet hate, because its not the way they would do it.

And when we do have problems, can you give us the information we need?  I have cases where it took months to get a trace from the database and one day to fix it, when we had the data.

We try and get it right, the same as you.  We take pride in our work, but like you, its not always perfect.  

I'm with Craig, an 'us and them' mentality just makes it hard for both of us.  

Posted by Brad M. McGehee on 12 February 2009

Rob Smith said:

Whilst I do agree with many of the comments on here, I think it is harsh to put many of the points on the shoulders of the developer - the vendor, yes, but not the developer.

Brad Replies:

My intention is not to blame developers, but the vendors who sell the product. It is the owners and managers of these third-party companies who are responsible for most of these problems.

Posted by roger.plowman on 12 February 2009

There's a core misapprehension on the author's part I think.

The design of an application and its SQL Server database are not the DBA's purview--that lies directly with the vendor, and the DBA *can't* perform any tuning of any kind without the vendor's OK.

Period.

Now, having said that, I think it's the *timing* of the manifesto that's wrong, not necessarily the points raised. By which I mean this manifesto should actually be a buyer's checklist, not applied after the product has been installed!

For example:

#1 Totally wrong. Integration is at the *vendor's* discretion, not the buyer's. If it's a condition of sale on the buyer's part it becomes a simple yes/no checkbox. Yes we buy, no we don't buy. After the sale the DBA should say "There's a million rows on the user's combo box. FIX IT. Now." And that's that. The words "fitness to purpose" come to mind...

#2 Installation. This is another simple demand. We install it or we don't buy it. Easy. :)

#3 Consultant required to know SQL server and Vendor's product inside out and backwards. Absolutely! The buyer should demand that as part of the sales contract. Otherwise no sale, or worse, lawsuit.

#4 This one's a toss up. Yes, it's your SQL server. No, you don't know what you're doing when it comes to setting up the vendor's product. Requiring an installation checklist (and confirmation all steps have been performed) is certainly not outrageous, however. Nor is it outrageous to sit with the consultant as he goes through the checklist, so you can confirm all settings and note any unusual ones.

#5 DBA as part of Vendor Staff. This is a *Really* *Good* *Idea*. In fact, don't buy the product unless this is the case!

#6 Hardware sizing. Great idea. No question. Also, on the question of being the vendor's biggest site. Been there, done that, choked on the performance. :) If you're spending that much cash surely the vendor would be willing to build some sort of stress-test to load the system. No test, no sale.

#7 Blaming SQL Server for poor performance. Better get performance guarantees in the contract. That way you can hold them to the fire if there's a problem, because you can *prove* there's a problem. Contracts are such fun... (evil grin).

#8 No SA for running. In an application this large a granular security system is a given. No security, no sale!

#9 Proper optimization. Make the vendor *prove* it, have your DBA(s) sit with their DBA(s) and go over the design. If it's a crap design, how good can the whole system be? Why would I buy a crappy design?

#10 SQL Server Specific App. Hmm. I can sympathize with this POV because I'm a developer first and foremost. However, most optimizations translate pretty well until you get into insane loads, and even then vanilla optimization can cover an awful lot of sins! :)

#11 Here I sharply disagree. Their product, their responsibility to support it, therefore they must approve any changes. If you have a decent vendor and your optimization is valid they'll fall all over themselves to allow it--and then take your idea for their own! :) Win/win, in my book. Of course this has everything to do with the quality of the vendor--and that's difficult to establish beforehand. But, in the end, their product, their call.

#12 Automatic upgrade of schema. Damn straight! :) Now, having said that and having been the vendor that implemented the schema updates I will say this. The vendor is certainly within their rights to demand you not skip versions. It's one thing to create 5 updates to go from Version 1 to version 5, but it's quite another to expect the vendor to create umpteen different updates to go from version 1 to version 5, version 2 to version 5, etc.

As I said, not a bad list, just apply it *before* you buy!

Posted by Roger L Reid on 12 February 2009

Brother McGehee, you are righteous!  (I disagree about "optimize for SQL Server" - but no big deal).

BTW, after filling out the umpteenth form for a vendor about  what platform we were making available, I came up with my own form for them to fill out.  

About 1/3 is information I truly need; the rest of the questions are mostly so they will understand certain things - e.g. "We only support the FULL recovery model.  If that won't work for your application, please call me so we can discuss how to move forward".  

I back that up with a nightly job that tells me any databases that have switched themselves from CHECKSUM to TORN PAGE etc - and set them back.

I have some additions and corollaries -

A corollaries: Don't tell be I can't add an index - but DON'T tell me that indexing, munging distribution statistics to fake out the query engine is my job.

A big one: Don't write a service that doesn't handle a dataserver reboot or redirection.  If the dataserver becomes unreachable, keep trying - and use DNS names, not IP addrs!

I can't go around starting everyone's services after a maintenance window or failover.

Give me the specs - all of them.   Don't tell me that "your default collation is fine" for your database (because you never heard of a collation) and then tell me something is wrong with my server when your inconsistently cased table names act like they're missing.

One word: constraints.  Look it up.

Two words: parameters, and procedures

No extended stored procedures.

At the very most, dbo_role access.  Figure out how to run the app without dbo_role for the next release.  And forget about any role with any rights outside the database itself.

Handle your transactions in a way that we don't end up with 100 GB log devices for a 100 MB database, even though I'm running transaction dumps every 10 minutes.

And no, I won't reboot my server because you "hope" it will fix your app.

Last - but unfortunately not least - you can ignore any of these if my employer wants it bad enough.  But I'll bother you about it anyway.

Posted by Tom.Thomson on 12 February 2009

Gaby Abed said (of in-house developers) "(for example, their idea of indexing is having a primary key, and that's it)" - well, those must be particularly database-aware developers: they've heard of indexing; one of the stangest things I ever saw was when I asked to spend a couple of months helping one one firm set up transactional replication from a particualar fairly small DB; at first I thought the technical director (who specified the couple of months) must be mad - it's 5 minutes with a wizard -but then I found that it had primary keys on only about 5% of its tables. Sure there were columns whose current content contained no duplicates, but did the application logic require them to be capable of having duplicates? - no-one knew; I didn't want to add meaningless identity columns to every table just to enable replication, I wanted to add meaningful primary key constraints - and the only way to discover which columns or combinations of columns would be unique was to wade through a pile of truly awful VB, C++, and T-SQL and work out what it was doing to see what the possible primary key candidates were, and then understand the awful T-SQL even better so that I could see which of the candidates for a given table would be a useful index.  That was my introduction to T-SQL and to VB.  I would have been much much happier if the develoopers had been db-aware enough to define primary keys on rather more of the tables! But equally I have come across some developers who had a really thorough understanding of databases and wrote excellent SQL.

I worry a bit about this "developers don't do databases" thing that I see on this site, because in my experience many developers do - and they do SQL a good deal better than some of the qualified DBAs I've known.  People contributing here also seem to have the idea that developers/technical managers generally don't have much respect for database people. I wonder if all this is a left-pondian thing - I don't see it so much over here.

Back on the main topic: the manifesto is really good, but one thing it misses out: the vendor must not package in a third party RDBMS in such a way that it is impossible to apply database service packs or security fixes except those incorporated in a vendor upgrade and then refuse to provide upgrades to patch critical database security flaws or incorporate new database service packs.  I've come across that practise, and have no intention of ever buying or working with any such products.

Posted by jtolson on 12 February 2009

I'm assuming I'll be flamed for this, but as someone who has to live on both sides of the fence I felt the need to respond. (Background: I do Technical Support for a series of Products that run with SQL Server as their back-end database. However because a lot of our client-base is extremely small, or started extremely small, I also end up doing about 90% of our customer's DBA work. We do have large customers with their own DBAs and when those people have problems/questions I'm the person in Support who ends up working with them most of the time.)

I don't disagree with a single point you made, but almost everyone of them has a correlated responsibility that the DBA needs to adhere to if they are going to work. I didn't hit all of your and some of my bullets cover multiple points you made.

* Please actually read and check the documentation you're given. Don't force Tech Support to read back a section of the manual you have already have.

* Don't expect that every tech is going to know both the Application and Technical side of the product. Any software of real scale will likely have support personel who specialize. The first person who answers the phone most likely won't be the most senior or knowledgable person working on that product. And please keep in mind that even if this person is technical and knows exactly what you are talking about, the odds are extremely low that they were the ones who created that SQL statement that is returning a million rows.

* If you run into problems doing the install yourself, please recognize you will need to pay for the consultant to help you past the step where you're stuck. Typically it is not Support or Development's job to help with installation which is why consultants are normally sent to do the work.

* Please recognize that any SQL DBAs on staff are there to primarily support Development, not to listen to your theories about why something is not working quickly, or to be at your beck and call. If you have access to them realize this is most likely a privelage for you and is a responsibility given to the DBA beyond their "regular" job.

* Buy software that is appropriate for the business you are running. For example there are a lot of ERP packages available, make sure you get one that fits the size and requirements of your business. Don't simply focus on their feature sets and figure it can just scale up.

* If you add/remove/change Indexes or Permissions and there is an immediate performance hit or accessibility problem try undoing the changes you made before going after the vendor.

* The DBA calling needs to have at least have a moderate undrestanding of the Application running on their Database. If you call and I have to explain to you what the software does you are wasting my time. If you don't know a single Front End screen of the application, or how to find a record from the program, at the very least have someone from your company with you when you make the call.

Posted by Grenouillaise on 12 February 2009

There is a reverse view of this very important blog.

As a developer/vendor with mostly small business users,very few of our clients have any IT or db expertise. Even those who do look to us for SQL support  and we have to build this in to our service. As clients grow in connected users and db size they are unwilling to spend on upgrading SQL to meet their needs (eg from SQL EXpress to SQL, Standard to Enterprise, 32 to 64 bit) MS policy of not having upgrade pricing for SQL does not help.

I agree with most of the points in the topic letter but it all comes down to cost in the end.

Posted by Brad M. McGehee on 12 February 2009

In response to jtolson, Brad replies: I agree with you, the DBA needs to take on a certain amount of responsibility. When I wrote this blog entry, I made the assumption that the DBA would assume this responsibility, but of course, this is not always the case.

Posted by Vivien Xing on 12 February 2009

It would be a wonderful world if there were such a perfect vendor.

Two parties get involved here: customer and vendor.  Each of them should play their roles.  The topic may be something like ‘Do’ and ‘Do Not’, or DBA & Vendor relationship, etc. rather than just ask the vendor ‘Do not’ only.

I think that DBA, or any vendor product user, should do the following at least:

Do not take the vendor requirement as it is.

Do read through the documentation.  The product requirement may be not valid in your environment.  Point out those unsatisfied requirements.  

Do not rely on the vendor to provide everything for your specific environment.

Do ask questions using your DBA sense for the specific application based on your own environment.

Do not trust the demo.

Do test and verify on your own environment.

Do not blame the vendor.  

Do provide helpful information when a problem occurs.  I believe that the vendor likes positive feedback from their customers.

Posted by tfifield on 16 February 2009

I LOVE IT!  Especially #7.  I once had some 3rd party software where they provided a DB View for reporting purposes that would time out if someone tried to just open it in SSMS.  I looked at the select code and the indexes on the table and determined that it had to do a table scan of over 1.5 million records just to return several hundred.  I fought with them and finally won out, added an index and then the view would open in in a couple of seconds.

I hate it when people get uppity on me.

Posted by Nicole Garris on 18 February 2009

Brad, I agree. I've been down the road you describe. Thanks! Here's a couple more requests for vendors based on real experiences:

Don't provide an installation script that can "accidentally" install into the master database. That can be awfully messy to clean up and might impact all the other databases I've installed on the server.

When I object to your poor practices (see the rest of Brad's article), please don't respond with "just use MSDB/SQL Server Express". I'll still have to administer it, maintain it, and back it up. And what are you doing running against SQL Server Express databases that never get backed up??

Please have a SQL Server DBA on staff that I can speak to. It gets tiring explaining basic SQL Server stuff to your "expert" staff.

Posted by Anonymous on 17 February 2010

As mentioned in a previous post , between the months of January and September 2008 I was hired to be

Leave a Comment

Please register or log in to leave a comment.