Upon a big period of self-reflection recently, it dawned on me that this year presents a huge tipping point in my life. That tipping point is the amount of time spent working in IT. This year I will have spent more time working in IT than not. During this time the “Build vs Buy” debate for monitoring SQL Server has cropped up a number of times. Having both bought and built my own monitoring solutions, I thought it was about time that I shared my own experiences.
Technology does move on, so as well as sharing my reasons to build and buy, we will also look at the latest trends. Both the latest development practices and the implementation and hosting of SQL Server databases in today’s modern world.
What should a monitoring tool do?
Before we start down the road of deciding which is the best path, you need to stop to consider what you need or want from a monitoring tool. Chances are if you are reading this blog then you are more interested in monitoring SQL Server than anything else. But, should you be monitoring other things as well? The choice is yours, but the scope of monitoring is an important topic to talk through with key business stakeholders. The more senior people you manage to engage and support you, the more chance you will have of securing budget to create or purchase a solution to monitor your SQL Server estate.
As a minimum, a monitoring tool should:
- collect data with little impact to performance;
- collect enough data to diagnose significant events;
- store the data to allow retrieval for visualization or reporting; and,
- provide real-time alerting to performance issues.
There are of course many other areas we could talk about, but we would start to stray into feature comparison and evaluation territory.
Cost and Pride
In my experience with SQL Server and working with software vendors, the main two reasons staff do not go ahead with buying an off-the-shelf solution are Cost and Pride.
The Cost argument is usually one around how much a piece of software will cost to buy, and in some cases the additional cost to configure and implement appropriately, depending on its complexity. Most companies will consider this a Capital Expenditure; an extra cost. Building a solution in-house is deemed cheaper because the cost is already being paid. Existing staff will write the solution and are being paid their standard salary. This is considered an operational cost, or Operational Expenditure.
The Pride element comes up a lot. I understand that, I really do. Many times I have attended meetings where a manager has been in favour of bringing in a solution and the existing DBA would have to relinquish a labour of love. Something that they have spent days, weeks, months, years honing and, in their eyes, perfecting. The argument is, “Why should I pay for something that I can do myself?”
Benefits – Rolling Your Own
There can be a number of benefits of rolling your own monitoring tool over buying one. The main one which most DBA’s will be able to relate to will be budget. The DBA does not own the budget, they can influence but at the end of the day it is likely to be out of their hands. However, if you have some spare time then these hands can be put to work composing or compiling scripts to collect data. There is then the small matter of attaining information from the data via custom written reports.
The next main benefit will be one of flexibility. You can collect data about whatever you need to. You don’t have to wait for a vendor to release a new version, you can do it yourself. You can tailor it to your own needs if you have your own system, as long as you have the time and the skills.
Of course, some off-the-shelf vendor systems allow you to also collect data through their framework. If it is flexibility you are after then make sure you evaluate those capabilities fully before making a decision. Evaluating products can be a tricky business, so I’ll be writing a post on that in due course.
Questions to Consider – Rolling Your Own
I’m really not against people trying to roll their own monitoring system. I have created loads of presentations around monitoring and provide information on how it can be done – for free. As I mentioned before, I rolled my own solution. The reason for me was that there was no capital expenditure budget available. This meant that, in order to do my job effectively, I had to write my own monitoring system.
Ironically, this is exactly how SQL Sentry started. SQL Sentry used to be a hosting company concentrating on the Microsoft stack. The DBAs needed a better way of managing their own SQL Servers. They tested what was on the market and, since nothing met their needs, they built their own. The tool was so good that they later decided to try and sell their product. This is how SQL Sentry was born: DBAs creating tools for DBAs.
With that pedigree I simply can’t tell people not to roll their own. I will, however, say, “think carefully.” Personally I learnt a great deal from diving into the internals of SQL Server. Pretty sure the solution I came up with did a good job too. The thing is, it took some time to design and perfect. I was busy, and the buck stopped with me; it was a hard time.
If I was going through the same situation again, these are the kinds of questions I would be asking:
- Which parts of SQL Server and associated periphery are you interested in monitoring?
- Can you, and/or should you, try to monitor the same metrics as a vendor solution?
- Can I quantify how much load my solution will place on a server?
- Will you require external access to this data through the cloud or alerting channels?
- How long do you think this project will take?
- How many members of staff will form part of this project?
- Will these members of staff need further training to complete the task?
- How many salaried hours would it take to complete?
- How much would this salary based operational cost compare to an off-the-shelf vendor solution?
- Can you keep up to date with the latest developments in SQL Server? What about Windows, Hyper-V, VMWare, the I/O subsystem?
- What would happen to the monitoring solution if you or another member of the project were to leave?
There is a lot to consider, learn, master and, indeed, to monitor.
For some of the latest information about updates to our solution line-up, check out our post, Latest builds of SQL Sentry Software. The change lists and blog posts there should give you an idea of how busy you might be trying to keep everything up to date.
Looking back, the operational cost to design, test, implement, and manage my own solution for the size of estate we had was probably way more expensive than the cap ex cost of a vendor solution. At the time I left there was no direct replacement for my position. As far as I know the system I developed languished through a lack of documentation. Yeah, that’s right. You need to write things twice. Once as code, the other as documentation. Most people never do the second part, there’s always something more important (or interesting) to do!
You may have noticed the inexplicable link between the pride and cost arguments. I’ll be writing some more blog posts on evaluating solutions, understanding feature matrices, and building business cases in due course. For now, let’s understand a little bit more about how much things might cost if we were to buy a solution from a vendor.
Licensing Models
Part of understanding the cost of a monitoring solution is understanding the licensing models available to you. From my experience, these are the most widely-used options:
- Perpetual – With a perpetual license, you own the right to use the software for as long as you want. Depending on the licensing agreement, you may be asked to pay a “maintenance” agreement on a yearly basis. This maintenance agreement typically includes support and/or upgrades, depending on the agreement and the vendor.
- Termed – With a termed license you are paying for the right to use the software for a determined period of time. You do not own the license. Think of it as a long-term, paid-for evaluation. Support and upgrades may be available to you during this period depending on the licensing agreement with the vendor.
- Subscription – This is the equivalent of a utility bill; you will pay monthly, quarterly, or yearly. You will not own the software; support and upgrades are likely to be included in the subscription price. This is a very flexible model if your estate is (or may need to become) elastic.
If you are looking at buying, then there are a few options available to you. When building your business case for software, consider how you are currently paying for other software. Does your company prefer to buy things outright (a capital expenditure), or do they prefer to rent/subscribe (an operational expenditure)?
Quantifying licenses
Licenses can be quantified in a number of ways:
- Instance – With an instance model, you pay per instance of SQL Server. Some vendors offer what is called a concurrent instance model, where you can stop monitoring an instance and move that license to another instance; others do not. Sometimes this flexibility is important. Check!
- Server – The entire server is licensed, and you can monitor a number of instances on that server. Some vendors may have a limit on that number.
- CPU Socket – With a CPU Socket license, you pay for the number of physical sockets in the server. Vendors usually allow you to license everything hosted on that server. Some vendors may have a limit on the number of physical cores that constitute a socket. For example one vendor used to say a socket should have a maximum of 4 cores. If you had 8 cores that would be 2 socket licenses even if it were one physical socket under the covers.
- CPU Core – With a CPU Core license, you license the number of physical cores on a physical machine. This model is widely used where an environment is virtualized. Vendors will typically allow you to monitor anything on this physical server, and in the virtualization case, it is often irrelevant how many guest VMs are on the licensed host.
- Node – Node licenses will only really apply to the Microsoft PDW / APS appliances, as the number of nodes will depend on your specific configuration.
- Database – Database licenses will typically only apply to the Microsoft Azure SQL Database offerings.
- Enterprise – With an Enterprise license (sometimes called a site license), you are free to monitor as many instances as you wish, for a set fee. Some vendors will offer an Enterprise agreement to large organisations.
What licensing model would best suit my business?
The answer to this depends on what kind of business you have. The main questions to ask are:
- How would they prefer to pay?
- Are you 100% virtualized?
- Do you use on-premises, cloud, or a hybrid approach to your SQL Server estate?
- Are these licenses only used for a certain project?
In my opinion, customers are generally better paying for a perpetual license if they are monitoring critical, production workloads. If the license model is concurrent rather than named, then the same license can be failed over to a standby disaster site if needed. If the servers that require monitoring only need monitoring for a short time, then this is where a term license would come in.
A good example of this is during an acquisition; Company A buys company B, and B has 10 servers not being monitored. Company A is not sure if these systems are being kept, or if they need to be monitored during a migration or consolidation exercise. They may need to be monitored to see how much extra load might be put on other production servers.
It is true that one might consider using a subscription model for this scenario too. From what I have seen, subscription models tend to be reserved for situations where work forces or utilisation of systems are elastic and do not always need to be monitored. We see this model used a lot more for Azure databases.
Once you understand the environment, and have a handle on how many SQL Servers you wish to monitor, you can ask your preferred vendor for a quote. I’m starting to stray into building a business case here. So all I will say is that the quote is not the end of the process. From here you need to look at any OpEx or CapEx savings that buying a vendor solution might provide.
Making the decision
There are lots of things to factor in whilst making the final decision:
- How quickly does the project need to go live?
- Which method would offer the most functionality?
- Which method would offer the best scalability?
- How easy will the software be to use?
- How easy will the software be to adapt?
- Would our customers benefit more from time spent creating a monitoring tool, or on the primary service?
Ultimately, the decision will come down to cost. Yes, it is a great feeling to build your system. It’s not so great trying to muster the energy to write documentation for it though! If you can write your own solution, to meet your needs, in good time and for less, then do it; it makes business sense.
If, however, you are worried about maintenance, staff turnover, and other factors such as internal costs and lost productivity during this period, then buying a vendor solution would be the smarter choice.
