A while back a conversation on Jonathan Kehayias’ former blog (he has moved) gave me an idea for this post.
Once you have been a DBA for any length of time, you will encounter the situation of a new vendor coming in with software that has a database back end. Wherever I am working or helping, I like to interject the idea of “DBA Input” as early into the process as possible and I go through a discussion with them.
The Interview
I think of this as a job interview. You want an ideal candidate and you want the candidate to know about your expectations.
These are the questions I ask in initial conference calls or meetings with technical pre-sales or installation staff. They guide other conversations and help me better support the internal customer requesting this application.
- Which version(s) of SQL Server do you support?
- How do you test service packs/CUs?
- How many customers do you have on the setup we are planning to go with?
- How quickly do you normally certify for a newer version or service pack?
- Based on what you know about our expected usage can you tell me:
- What should I expect to see for performance characteristics? (I/O, Mem, CPU utilization)
- Will your app live in a shared instance that looks like x
- What sort of performance issues have your support folks commonly dealt with in similar environments?
- Does your application support a named instance? (sounds stupid but it isn’t.. ask any question)
- Does your application support and is it certified to communicate with a SQL Cluster? (again may sound stupid when you think about clustering and SQL with an instance only active on one node anyway)
- Can you explain your architecture? Looking for discussion about:
- Data Access methods – are they developing with performance in mind, are they using modern technology… Will you get hammered with a bunch of sp_cursorfetch statements?
- You may be “just” the DBA but we all know the DB gets blamed first
Understand how they connect, if you are making the back end Highly Available, how is everything above the DB going to be HA?
- Can you share with me all of the recommended best practices your installation and support folks suggest for database maintenance?
- For an application of this category and usage, here is our general backup/recovery strategy. Do you have any issues with that? (Are there distributed transactions that need to have coordinated backup? Do they have issues with backup methods you use? Does the DB backup need to coincide with some XML file someplace?)
- Does your support have any processes that involve recycling the SQL instance? If so, is this mandatory? Why?
- Do you have any reference sites we can talk to? If the app is large enough, try and get a customer of similar scenario. Talk to their IT staff, talk to a DBA there. Maybe overboard but can be helpful if conditions warrant.
- Security!!! I saved my favorite for last.
- Windows authentication or SQL Authentication?
- What, if any, fixed server roles do you require?
- Is that just for the duration of the installation or the life of the app?
- Why so elevated? What happens if we trim that down? (Presuming they answer with SA or something )
- How do your users authenticate? (Does each user need a SQL login, do they need to belong to an AD group which has SQL login rights? or is it an app pool with the app handling security with tables in the database?)
- What type of access does your support team need? (important if a shared instance or you are a paranoid careful DBA)
- Out of curiosity, what database rights do your database user accounts have? (I hate hearing DBO, presuming I haven’t heard SA… I LOVE hearing they create a role with the minimum necessary permissions… Not the end of the world if DBO, only hurts their database but still…)
The list is not always covered verbatim but I bring it with me to help drive a conversation flow and get to the important items that I want to know about. I’ve been burned by not asking and not getting myself involved. If you pick up nothing else from this post: get involved as early as possible.
You’ll be surprised what you learn. I was working with a building security system once. Got to security and the answer was basically that they normally like to be on their own database server, and they generally use SA with a blank password. I was floored… I said to the install team from the vendor, “you guys are in security?!?” We went with them but I worked with them to find out what permissions they truly needed, gave them those, backed them down even further after DB was created and they live happily to this day as far as I know.
What do you ask? How have you been burned by not asking? What’s your funniest/saddest experience with vendor software?
Mike
I agree with you 100%. I am currently dealing with a Vendor who has the worst security practices, a pretty bad database model and there software was just certified on SQL Server 2K5 on May 2007. This vendor is also the ones who set up the database and they recommend having one data file for a database that is 500 GB in size.
Phil
I forgot the 64 Bit question. This is important, yes it shouldn’t affect things much but there are various concerns/issues with connections to the back end, any tools they use, etc. Make sure you ask if they support the platform you are using. If you are going 64 Bit (and in this day, why not?
) make sure you ask if they support your environment and have tested with a 64bit back end.
As a vendor I think we could safely cover off all the points except maybe 1.1; we tend not to do a deal of regression testing as there are so many patches coming out of Microsoft, and either way we would recommend either our test build or the latest patch. We see very few problems in this area with 2K5.
The biggest problem we have is with one client that refuses to adhere to our standard release process which use red-gate SQL packager to create or update database builds. They insist on SQL scripts which is much more error prone and time consuming. I’d be interested to understand your views on the pros/cons of both methods.
Hey Simon -
I have worked for software vendors and I concur, 1.1 is a large investment of time and resources which can take away from more pressing items (bug fixes, priority enhancement requests, remediation releases, etc.). That being said, I generally have always tried to at least test against the major service pack levels. SQL Service packs don’t come out that frequently (there have been 3 major service pack releases since SQL Server 2005 was released, for instance.. SQL Server 2000 has essentially 4 or 5 major – depending on how one counts SP3a). I think a compromise would be certify on Service Packs eventually (after all, you want to be developing on the latest and encouraging your customer to move towards the latest when there are fixes/changes that help them). CUs (and hotfixes) may be a bit much unless there is something that is common to all of your installations.
As for the scripts. I have been on both sides of that debate and in various "sensitivity" environments. As a DBA, if I am installing an app on a shared instance (my preference if I know that the impact is within lines of the instance, the security the app requires is not out of control and I understand what is happening) I like to apply the scripts or at least watch the scripts get applied. So that being said, I like to see the SQL Scripts. That doesn’t mean I have to run them as a script. Red-Gate’s tools are great and I push for them wherever I am working. SQL Packager does make life a lot easier and takes out some of the manual steps that can increase the oops factor.
Perhaps a reasonable middle ground is to talk to the DBA and suggest that they can see the scripts that make up the package, they can run a trace when it’s running in a test environment and be sure that it isn’t hurting anything. If they are reasonable they should be fine with that. Less work for them to do in the long run
I had a vendor that requested for VPN access as they installed this application and database from their office out of state. After everthing passed testing and went to production, the vendor continued making changes in the application and the database tier without any documentation or notification. We immediately revoked the VPN access.
When dealing with a vendor who has been granted VPN access initially, try to disable that as soon as possible or immediately after going into production and have the vendor work with the DBA to get things installed.
Also, do they support Unicode? I’ve seen apps where they just migrate the old database tables from release to release and never update the app.
Mike,
This is a great list of questions! I wish I had this list available at my previous company; I ended up having to overhaul the database backend for a 3rd party app.
Thanks for a good post!
Regards,
Michelle
Great list of questions Mike. I have a questionaire that probably includes about 80% of these, and the vendor roughly completes about 80% of the questions. Unfortunately, by the time I even get input into the process as the DBA, the purchase has already been made. I may be the SQL SME, but I am only a mop boy when it comes to the purchase chain. I can never get I/O, Memory, and CPU info out of the vendor (see that word NEVER? I mean that.) I usually have to force (yes FORCE) the app team into full load testing and then pull those metrics from that. I will be blatantly stealinig from this list and using it as my own immediately. Thanks Mike, fine post!
Yeah Tim – This post really falls into the category of the Coconut meme you started… I would say that greater than 60% of the time, the purchase was made before I was involved at most places. One company I worked at had the DBA team in the loop early.
I still fight for it wherever I am and I think once you live through some failures you can get upper management to see the need to get a DBA involved earlier.
I also try to organize a call with the vendor and actually discuss the questions. I use them more as a conversation starter than a checklist. Like you, I NEVER get an accurate info on specs. On occasion I have gotten a "feeling" out of the vendor or a call with support talking about other clients.
Most vendors ignore the database. They have the philosophy that their product ends at the client and services they install. Even getting them to add an index when I tune a vendor app is a huge hassle 80% of the time.
Great questions, but look out for question number 4 and other questions that might generate "Yes man" answers from a vendor. You might want rephrase it so they have to answer the question with a number instead of a yes/no answer like "what percentage of your customers are using clustered servers and what issues will we need to be prepared for to support your product on a cluster?"
kpk – great point. I normally expand on that question when talking. If it is supported, I want to get that in writing also which does offer some protection.. But… It’s not a butt covering I am looking for as a primary goal of the questions (though it does provide that if you ask for the documentation/written responses). The primary goal is to make sure my customers (the enterprise) will be happy into the future.
I don’t necessarily ask the questions in this exact order and manner each time but my main point is to have a conversation with the vendor around this. Asking the question your way is an EXCELLENT way. Thanks!