Helping Software Vendors Talk to DBAs
Software vendors have a wide variety of clients:
- Some clients don’t have any IT staff at all, and just outsource everything. The client wants to hand an installation manual to their contractor and say, “Build whatever the software vendor wants.”
- Some clients have a full time sysadmin or two, and those sysadmins don’t really know anything about Microsoft SQL Server. They want a 1-2-3 checklist of what needs to be done, and they’ll follow it to the letter, but they won’t put any independent thought into it, nor will they raise any objections.
- Some clients have a big IT staff, including at least one full time DBA. Ironically, you’d think this kind of environment would be the easiest one when it comes to installations – but it’s the opposite! The sysadmins and DBAs raise all kinds of objections because they have internal standards, or they want things done a certain way.
So when I’m working with software vendors to write installation instructions for their database back end, I encourage them to think about three tiers of SQL Server environment quality:
- Good – typically a single VM, using whatever VM backup software that the client uses across all of their servers.
- Better – adds in a method of high(er) availability, typically mirroring, because it needs to be simple enough to be managed by a non-DBA. I’d typically suggest manual (not automatic) failover here, which for a single database app is easy enough that a sysadmin can do the failover while following a checklist, or can call the vendor’s help desk to be walked through it.
- Best – a complex high availability and disaster recovery topology, perhaps a failover cluster plus log shipping. This requires serious know-how on the client side, and the vendor isn’t going to be able to write instructions detailed enough for a n00b to follow along.
It might seem counterintuitive, but it’s easy to write the “Good” instructions. There just isn’t much work to be done. However, the more complex the environment is – and the more it relies on the client’s internal standards and processes – the less guidance a software vendor can give. It’s just too hard, too time-consuming, and too expensive to write a document that fully explains how to implement a multi-subnet cluster that works for every possible client.
I find that if the software vendor is honest about that, then it makes the situation easier for DBAs. The “Best”-tier installation guide starts with a simple disclaimer:
We want to be a great partner for you, so that means being flexible and working with your standards. In the next couple of pages, we’re going to describe a typical Best-tier infrastructure (a failover cluster plus log shipping) that works with our more complex and demanding clients. You don’t have to follow that scenario exactly! If there’s an infrastructure you’re an expert on – perhaps Availability Groups or SAN replication – we’d be glad to talk through that with you to make sure it works for everyone involved. Just understand that if you choose to implement your own infrastructure design, we can’t guide you on it, or troubleshoot it for you. We’re relying on you to do that part. But as long as the SQL Server service is up and we can connect to the database with SSMS, we’re happy!
That way, everybody’s on the same page: the vendor has one complex design that they’re familiar with, and they can guide you towards consultants for implementation or troubleshooting, and their support team will be comfortable working with it when the poop hits the fan. However, if you have a design that you’re comfortable supporting, the vendor is flexible and can work with that too – they just can’t do infrastructure support on that design.
11 Comments. Leave new
One big problem – you assume, that the vendor itself has good DBA guys (or even good database developers). Particularly small vendors often lacks those skills, which is a reason, why many apps wants to be sysadmin / local admin on the server – they just don’t care enough and with full access it works at least.
Because vendors can – and sometimes do – hire smart consultants to help.
Even if the vendor has a good DBA or DBA team this means very little if the consultant who is doing the the installation is not given the necessary information. I have been that consultant who had no idea that the app only needed the database owner role and not sysadmin.
and even DBO role is too much for the default daily work. When the app needs to truncate a table or recreate indexes, it should have ALTER permissions on that specific tables, otherwise just read/write permissions. You don’t want that someone uses SQL Injection to drop a table (and there will always a junior who puts it in and it may pass the code review unnoticed (when it exists at all)).
For version upgrades use either another account with extended permissions or let a sysadmin / dbo execute the upgrade (e.g. by using the account who runs the setup.exe)
As I am on the ISV side these are some of the issues I see. Vendors expectations at the enterprise level, customers have good IT, VMware and SQL Server DBAs – well not so!
Now the vendor is expected to provide the path and guide to drive success while now dealing with the politics that customer has brought us into.
I’m a data acces layer dev of an ISV. This ist especially tricky when the enterprise customer outsourced their IT to a third party. The number of times I (a C# dev!) had to tell senior system/storage admins: “No, don’t use ballooning on mission critical SQL server VMs. Don’t use the crappy standard storage drivers, use the PVSCSI and add 3 more, watch theses David-Klee-videos on how to host SQL servers in VMWare. Measure IOPS from _inside_ the VM with diskspeed and and use these parameters”.
This is highly accurate
Brent, thanks for making me smile: “Some clients have a big IT staff, including at least one full time DBA. Ironically, you’d think this kind of environment would be the easiest one when it comes to installations – but it’s the opposite! The sysadmins and DBAs raise all kinds of objections because they have internal standards, or they want things done a certain way.” Being on the Prod DBA side of things, my only real comment is we get a wide variety of vendor apps to live with and some are *ahem* designed better than others. I’ll spare you some horror stories of wide tables w/ 100 indexes, etc.
I work in an educational institution and always have battles with small vendors who are used to installing single operation products and cannot understand that a larger enterprise has complex security and procedural requirements which need to be followed. Local and domain administration and the use of ‘sa’ for all processes hits a security wall and they always say ‘we have installed this heaps of times without any issues’. The constant need to install SQL Server Express drives me crazy, because that’s how they do it.
Always seen as a installation blocker , trying to enforce enterprise security and processes.
A reluctance of vendors or onsellers to provide or discuss alternative installation processes
As devs of an ISV that supports single user SQL express to 7 TB/5000 BRperSec environments:
We give the good instructions (use Hallengren, …), and we support the customer in projects to implement the better and best scenarios. But we cannot provide full instructions for those. We do not have the expertise.
For that we would need to run those envs in testlabs with realistic loads – impossible.
In general terms: We don’t own the server, just the schema on the one DB our app uses. Give the user defined in the connectionstring dbowner and view server state and we are happy.
Anything that is transparent to our app (any HA solution, TDE, std vs enterprise) is simply out of scope for us.
Being a developer-slipped-into-performance-tuning guy, I wholeheartedly agree with how hard ‘Best’ is! The very responsible guys down at IT are very resistant to changes until they understand them in depth. The corollary being that changes that are more on the developers’ side of things can be crazy difficult to get through, until you take – steal – the time to explain in detail what a proposed change is about, what the potential impact is.
It’s good to have responsible IT stuff running our servers. Just somewhat tough from time to time…