Do you buy software from independent software vendors? Does it run on SQL Server? Are you responsible for supporting that software? Here are some questions that you can ask a software vendor about the way they use SQL Server. It’s best to ask these questions before you are a customer, too; you’ll have a bit more leverage. (This post was originally written in 2010)
The Problem with Software Vendors and SQL Server
Once you have been a DBA for any length of time, you’ll encounter the situation where a new software package comes in with a database backend. At many clients and companies I’ve worked for in my 17 years of working with SQL Server, this is a foreign concept. All too often, the package is selected, purchased, and, often, installed without a DBA being involved at all. This means SQL Server sprawl has continued. It means installation, maintenance, management, security, etc. best practices may not be me. This leads to a half-joking frustration. DBAs have for ISVs. I blogged about this once in this frustrated post, “Hey software vendors!! Get a clue!” I even delivered a presentation at the SQL PASS Summit based on this frustration alongside a DBA who worked for a software vendor.
A Solution
So this isn’t a one sized fits all solution. But it helps to get involved. Think honey and bees instead of vinegar. Try and get involved in the purchasing and selection decision. Being involved earlier means your feedback is more likely to be addressed. Sometimes it is easier to get a vendor to do something before the sale than after, sadly. But get to know the project managers, the business systems analysts, the folks who select and authorize software purchasing. Let them know that the DBA team being involved earlier means their lives are easier. It means the business spends less money. It means fewer headaches for all. So. Get Involved. Be a steward of your SQL Server environment. Be an advocate for your business. And ask some questions.
The Vendor Interview
I think of this as a job interview. You want an ideal candidate, and you want the candidate to know about your expectations. It’s a bit different, though; you want to know what to expect and watch out for also. Below are some questions I tend to ask a vendor before coming on board or as early as possible. The questions are from the original post, but I’ve added some comments in italics to describe things a bit more. And I’ve added a couple of other questions since I wrote this post in 2009. I’m staying away from query performance a bit here, but SQL Server MVP Kendra Little has a great post she put up recently that gets at some of those questions. It would be best if you also clarified those questions she asks.
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.
- Do you know about SQL Server 2016 SP1 and the support of enterprise features in Standard? – I want a vendor to be in touch with the latest and greatest developments coming out. I want to know they could use compression in SQL Server Standard for me.
- Which version(s) of SQL Server do you support? (I want to work with a vendor who likes me to update and upgrade and one who tests their application on SPs and also keeps up to date as they go)
- 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?
- What’s your policy on CUs?
- 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 our environment? (I HATE SQL Server sprawl. I hate seeing many installations of SQL server, each with a license cost, each with administrative overhead, just sitting there supporting a small application because the vendor said “has to be that way”)
- What sort of performance issues have your support folks commonly dealt with in similar environments to ours?
- Does your application support a named instance? (Sounds stupid, but it isn’t. ask any question. They should. But if they don’t, and you do? You’ll have to figure that out. It may be that they have no clue what you mean. That’s a sign for you also.)
- 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)
- Does your application work in a SQL Server Availability Group?
- I want to put this in the cloud in AWS EC2 or Microsoft Azure on an IaaS deployment – do you have issues with that?
- For a smaller DB – are there any plans to move to SQL Azure?
- 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? Do they care about performance?
- 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? It’s great to have a cluster or availability group but useless if there is a single app server.
- Can you share with me all of the recommended best practices your installation and support folks suggest for database maintenance? I like to roll my own – but I like to hear what the vendor says. Do they have bad advice? Do they have special things you should do regularly? If you don’t ask, you’ll never know.
- 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 the backup methods you use? Does the DB backup need to coincide with some XML file someplace?) – Again – you own this, but it is good to know about exceptions.
- Does your support have any processes that involve recycling the SQL instance or rebooting the server? If so, is this mandatory? Why? This is good to know if you are trying to cut down on sprawl and reduce licenses – you don’t want Vendor X restarting the SQL Server that applications A-J are on. And you probably don’t even want them restarting their own. But if you don’t ask, you’ll be surprised sometimes.
- Do you have any reference sites we can talk to? If the app is large enough, try and get a customer of a similar scenario. Talk to their IT staff, talk to a DBA there. Maybe overboard, but it can be helpful if conditions warrant it.
- Security!!! I saved my favorite for last.
- Windows authentication or SQL Authentication? If SQL – how do you enforce the policy? Do you clean up and encrypt passwords?
- 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 with 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…)
- Do you use some encryption?
- Are you certified by this compliance standard that we have to meet? (You don’t want to be surprised by all of their worst practices come audit time!)
The list is not always covered verbatim, but I bring it 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. I 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?
Find these questions helpful? I’m adding this post to the category of Straight Up SQL Server Tips; I think you’ll like the whole category. In that series of posts, you’ll find tips that I think all beginner or intermediate DBAs should know the answers to. Check out the index post for that series.
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!
our director wanted to consolidate all of our backups including sql server backups onto a third party backup solution. i quickly found out their program requires local admin group AND sysadmin SQL Server permissions on the domain account their service uses. i presented this to my director and he is cool with it… so we’re rolling it out. sigh** yea, believe it! 🙁
Hi ZenDBA – first off – big apologies for your comment getting lost in spam all these months. I hear where you are coming from. One thing to throw out to you – is sometimes the backup and the monitoring software we use will require more elevated permissions and I am generally okay with this. If I am using a tool I know and trust and I am leaning on the tool to provide my mission critical backups or enterprise monitoring it means I am trusting them with a lot. Now a backup tool could get by with less rights certainly but it is one that I’ll give the vendor a bit more latitude with as long as I know who has control over the account and that I’m not creating a bunch of back doors. Or I’ll just do a test with the necessary permissions to perform backups (and restores if I really want the software to do those for me 😉 )
Couple of things to add…
I ask what for an estimate of how big the database is likely to be to start with and how much it’s likely to grow (perhaps you’ve already got this and I missed it)
I’d add in asking what edition the vendor supports as well as what version i.e. express, standard, or enterprise
I ask what components are needed. i.e. is it just the Database Engine or do they need SSRS/SSIS/SSAS
Also….and this is a slightly odd one perhaps….. how their software is physically going to get onto our servers.
At my work, all USB sockets and CD/DVD drives are disabled by default, so if the vendor is bringing physical media then I need to arrange to get these temporarily re-enabled. Alternatively, if the software’s going to be downloaded then I like to know in advance, so I can check the site’s not blocked by our web-filters and perhaps schedule the download out of work hours
7+ years later and this article is still great. It’s nice to see the attention given to SQL Server security.
Thanks Dave 🙂 Should probably edit it a bit to reflect changes in versions and make it look a bit more modern 🙂