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.
