SQL Monitor Hosted: keeping an eye on your servers from the cloud

Comments 0

Share to social media

M: Before we get on to SQL Monitor Hosted, could you tell me a little about SQL Monitor in its current form?

Matt: Sure. SQL Monitor is a monitoring tool which you install in your organization and point at your SQL Servers. The current architecture consists of three components: the Base Monitor, the Web Server, and the Data Repository. The Base Monitor is responsible for monitoring and alerting, and allows communication between the Web Server and the Data Repository, which is a SQL Server that stores all the monitoring data we’ve collected.

M: So is the monitoring data stored at Red Gate, or on the customer’s machines?

Matt: In the current product, it’s stored on the customer’s machines. When they install SQL Monitor they can specify the location of the SQL Server repository, and they can set that up to be anywhere. A common scenario is that those three components – Base Monitor, Data Repository, and Web Server- are all installed on one machine. They don’t need to be – they could be on three totally separate machines – but the simplest situation is for everything to be set up on one box.

M: Why did you decide it was a good idea to develop a hosted version?

Matt: The install process for the current product is fairly straightforward, but there are still a few factors that can slow things down: you have to get hold of the box, get SQL Server licensed and installed on it, make sure it has network access to the servers it’s monitoring, all of which could mean several different people have to be involved.

We want to remove these obstacles. The idea with SQL Monitor Hosted is that you install one small piece of software, which just relays the monitoring information from your local machines up into the cloud, and then we look after everything else.

We provide the SQL Server which stores the data, we’re responsible for the machines which schedule the monitoring and perform the data processing, and we’re responsible for hosting the web UI. There’s a whole set of things we now take care of which used to all be done by the user, and all they have to do is install this small local piece which enables us to have connectivity to their SQL Servers, without exposing them directly to us.

M: So all in all this makes it a lot easier for people to get started with SQL Monitor.

Matt: That’s the plan. The only new factor is in the connection to the outside world, which might potentially require opening up a port on the network – but it’s an outgoing connection which should already be allowed by most firewalls. For the majority of customers it shouldn’t be an issue.

M: So, you’re planning on using AWS. Are you using any of the other AWS tools that go along with it, like Cloud Watch?

Matt: I expect we’ll use Cloud Watch because it’s there, and it’s fairly straightforward. In the past when we’ve done a couple of things in the cloud, like monitor.red-gate.com, we’ve used SQL Monitor to monitor itself, so I wouldn’t be surprised if we did that again. One of the things we want to do is take advantage of Amazon’s services as much as possible, so potentially RDS, and SES for email in particular. In the future it would be great to use their queuing system and their workflow service rather than doing it ourselves, but for the moment we’re migrating the current product so we can’t pick those up straight away.

M: Is there a development plan at the moment? What are the foundational tasks you have to do before you move on to specific improvements?

Matt: The hosted version has a fundamentally different architecture: we have this Relay that didn’t exist before, so we’ve had to take all the logic for directly connecting to your SQL Servers out of the Base Monitor and move that into the Relay, and then enable communication between the Base Monitor and the Relay.

I guess the most interesting point to draw from that is simply that it was possible – so if you have a current on-site product or function, and you want to transition it to cloud for whatever reason, you don’t have to go wholesale. If you’re going to use infrastructure-as-a-service as opposed to platform, then it’s a perfectly reasonable option.

Looking at Azure, they started as platform-as-a-service and are now moving down the stack, and they very much see their infrastructure services as an on-ramp for their PAAS stuff. I think if you’ve got a working codebase on-site but want to make use of cloud functionality and resources, then it does seem like a sensible approach and it’s certainly possible. But there is some sense in which it’s a compromise rather than an ideal architecture.

M: Do you think it would have been easier from a development point of view, to have built SQL Monitor Hosted from scratch rather than transitioning an on-site tool into a hosted version?

Matt: We did about two months of technical planning, in which we mapped out an ideal architecture and several ways in which we could just modify the current product to work, one of which was the migration method we ended up going with. Although the ideal solution would be the way to go if we were building from scratch, finding a quicker alternative has meant we can get a working product out to users in about three months of dev-time.

If we’d built from scratch, we wouldn’t have managed to get a full-featured product up and running in that time frame. The estimates I was putting together for building from scratch were for about nine months of development rather than three, which was still optimistic.

M: What’s the timeframe for release?

Matt: We have a limited alpha out now and hope to have a beta release out at the end of September.

M: What’s the plan from here?

Matt: The key aspect, and the part that had the biggest technical risk, was allowing the communication between the Relay – the on-site piece – and the Base Monitor. If that hadn’t worked, then the entire architecture we were planning would have been out the window and we’d have had to go back to the drawing board. As of the last three weeks or so, that functionality is complete and robust.

Then there’s a bunch of UI changes, as the current SQL Monitor interface contains assumptions that are no longer true from the customer’s point of view. They shouldn’t have to care about the notion that there’s a Base Monitor in the cloud with a connection to a Data Repository.

M: People debate about the importance of having a solid plan going into these things. You had a fairly detailed plan: how worthwhile was it, and how much did it change?

Matt: It was definitely necessary to come up with a ballpark estimate for the different approaches, which let us make the decision to go with the three months of work changing the current product. We use agile processes to manage the project, and issues have come up and we’ve had to trim work from the backlog. We decided to make fairly optimistic estimates and then factored in 100% contingency, which turned out not to be enough! Hence some of the backlog trimming.

M: Has the agile process worked well for you guys? Have you been able to adjust quicker?

Matt: This is probably one of the products where I’ve known agile to work the best. We had a hard deadline of the end of August, which drove us to be very pragmatic: if something was going to take too long, it got cut. I think that’s really good in terms of getting the product to market as soon as it’s ready, and will also let us discover quickly whether this is the right or wrong thing to be doing to meet our customers’ needs.

We did have one particular painful experience – there was a story we’d estimated at one dev-day, which turned into nearly three weeks’ work. The question then is “Could we have avoided that with more planning?”

M: I’m always curious in the way planning interacts with an agile response. How did you deal with it?

Matt: That particular story got rewritten as several larger stories and we rejigged the ordering. Most of the stories we came up with matched the work we actually did. At no point did we get anything totally wrong – there were a few things that needed more clarity or breaking down into smaller stories. For a couple of reasons we didn’t end up with the manpower that we expected, so we had to trim the backlog a little, but for me that’s seeing agile work properly.

M: For customers who already have the current on-site version of SQL Monitor, will they be given the option of moving to SQL Monitor Hosted? Would it be easy to switch from one to the other?

Matt: They’re very much different offerings, so we won’t be supporting that. If you’ve got the on-site version working well for you, then you’ve probably made the right choice. Some of the appeal of SQL Monitor Hosted is in the simple set-up, which won’t apply if you’re already running the on-site version. The first release in August was an alpha, so it’ll be a while before the hosted product is available for purchase. It will also be priced using a different model, giving users the opportunity to sign up on a subscription basis.

M: So where can we go to find out more about SQL Monitor Hosted?

Matt: Head to sqlmonitor.red-gate.com to get the latest updates, and sign up to ensure you are the first to get access to our beta. We’d also love to hear your feedback.