Keys to the Kingdom: Managing SQL Server with Dynamic Discovery

Eric Kavanagh: Alright ladies and gentlemen. Welcome back once again. My name is Eric Kavanagh. Things are hot. Things are heating up in here. I don’t know what’s going on. Oh that’s right, it’s time for Hot Technologies. Yes indeed, my name is, once again, Eric Kavanagh. You can find me on Twitter @eric_kavanagh. This is the show that’s designed to talk about what is hot in the marketplace. The title today, “Keys to the Kingdom: Managing SQL Server with Dynamic Discovery.” Good stuff. There is yours truly. Okay, that picture was from a few years ago. I’m not going to lie, I’m looking a bit older now, but that’s okay.

So, we’re talking about how technologies and SQL Server is really, really, really, really hot. We’ve got a whole bunch of content today, so I’m going to hand it off right away. Stand by, here we go. There’s our speakers. And Robin Bloor goes first.

Robin Bloor: Yes indeed. The presentation is going to go into depth into database management so I just thought I would run through database management or, you know, the database maze, in order to get people into the spirit of it. I used to be a DBA, I suppose you could say I used to be a database consultant, about 20 years ago, and the thing that actually surprises me about databases is that not a lot has changed. A lot of things have changed in terms of speed, in terms of the volumes of data and things like that, but much of it actually remains very similar to what used to happen.

A database is, in my opinion, an organized extensible collection of data that can be optimized for specific workloads and deliver data management capabilities. It came into existence primarily because if you wanted to manage data in files it was a terrifically difficult job. And the idea of putting together a piece of software that would do pretty much anything that you needed it to do took off almost instantly, as soon as we had random access on IBM mainframes back in the 1970s.

The relational database was invented in the ‘70s and came into existence in terms of prototypes in the ‘80s and kind of got its traction in the marketplace from the beginning of the ‘90s onwards. And relational databases still are utterly dominant in popularity. If you read the press you will hear an awful lot of things said about those – SQL databases and recently there’s an awful lot of noise about graph databases. And those are interesting, if you like, but actually still in the latest sales numbers, relational databases have 95% of the market. And Microsoft SQL Server which we’re going to discuss in some depth today is the second most popular to Oracle.

The thing about relational databases that makes them unusual in terms of the engines that they are is they can work on both OLTP and query workloads. You have to tune them differently if you’re going to do that but they actually are capable of both types of workload. One of which is short random transactions and the other of which is long queries spanning a lot of data. The alternative, the NoSQL database and the graph database is mainly for analytics and they’ve risen up fairly recently. NoSQL came first and graph has started to get a bit of traction in recent times. NoSQL can be used for transactional activities, but graphs are almost never used for transactional activities. The reason, I came across a stat which actually I think is at least ten years old that says most companies have at least three, actually the figure was 3.5, different brands of databases, if you look at their inventory of software.

But the reality is that most companies standardize on a specific database. And most companies have standardized either on SQL Server and Oracle as the two most popular for, if you like, standard databases. And they use the alternatives only in exceptional circumstances where, for instance, they’re getting a software package that needs a different database or they’re going after some of the big data analytics targets that have come into existence.

We’ve also got, if you like, the interference of Hadoop. Hadoop in one way or another has become more than a file system but not yet a database. However it does have SQL that sits over the top of it. But the evidence there is that it’s not really supplanting or anywhere close to supplanting the relational databases that earned the hearts and minds of the world. And the reason for that really is those relational databases took twenty years, actually longer than twenty years, to get to be as good as they are. And you don’t just build a query engine or SQL engine that’s really performant in a very small amount of time. It just doesn’t happen.

And so the conclusion of this slide is that databases are strategic and they evolve, they get better. And that’s certainly been the case with Oracle and Microsoft SQL Server. You probably, few of you remember back to the days when databases first emerged but I did, I was a boy then. The original idea was that there would be a single database and that was a conceptual idea that absolutely never took root. There was an attempt by IBM with the AS/400 to actually have a database-based file system but that didn’t dominate either. You’re left with the fact that databases naturally fragment. You actually naturally have multiple instances. There are scalability issues. Database is only scaled to a certain size, admittedly that size has increased over the years, but they had limits.

And there were workload issues, the major workload issue being that OLTP workloads and large query workloads are simply not compatible with each other. And it was impossible to build an engine that would do that. What we run into, which is kind of interesting, I came across a site recently that had over a thousand different instances of Oracle. I can’t remember exactly how many DBAs they had, but if you actually talked to them as to how many of those databases were actually being monitored by a DBA, it was something like ten. They were basically using the database as a cupboard and just throwing data into it because at least you had a scheme and it was more organized than a file system would ever be, but nobody was doing anything other than giving it a default configuration and setting it loose.

I’m not sure whether that was a good idea. It sounds bizarre to me, to be honest because, in my opinion, whenever I worked with databases, databases needed attendance and you needed to, in one way or another, know exactly what was going on out there. And an awful lot of system interdependencies mean that certain kinds of service levels absolutely have to be met or else you get problems.

There was talk recently, I’ve come across various databases that claim to be self-tuning. The ones that are column stores that are set up for query traffic are largely self-tuning because there are very two choices that you need to take in terms of the indexes. But aside from that particular area, databases need to be tuned. And they need to be tuned, certain relational databases, mainly because an awful lot of transactions involve joins. Joins are expensive activities. If you don’t put the right indexes in the right place then joins take inordinate amounts of time when they don’t need to.

The self-tuning databases currently, well it exists only in these areas where the workloads are well known. And my experience is that most companies employ very few DBAs and that’s because they’re expensive. And therefore it is better if you can to alternate what the DBA does. This is a DBA’s activities as I understand them. They do installation, configuration and upgrade of databases. Upgrade, by the way, is not necessarily a trivial activity. The reason you would upgrade a database, I mean, the rule that I always worked with is don’t touch it if it’s working, and if you’re going to upgrade a database to any particular new version, you do it in test mode first and after that you upgrade everything. You’re still always dealing with the same version. But in actual fact a lot of sites I’ve come across, that isn’t what happens. There is, let’s say, a fair degree of entropy. License management is an issue, depends upon what license you’ve got. ETL and data replication.

One of the tricks with the database is if you’ve got a query workload that needs to be split, you can create two instances and replicate and that’s often done where people are using the replica as a hot backup if need be. Then storage and capacity planning, that’s part of a DBA’s activity because of course data grows and you need to track that. And then you need to plan for various hardware upgrades or hardware augmentations. There’s troubleshooting which is a painful activity for most DBAs. Where something goes wrong and the backup doesn’t work exactly perfectly and then they have to roll up their sleeves and get down and try and recover things from log files. That happens way more often than I think, well, I remember that happening but I’ve been out of the game for at least ten years, but I remember that happening way more often than you would ever have expected. Performance monitoring and tuning is just kind of the beating heart of a DBA job. But there’s also security in terms of access management, backup and recovery, creating software test systems that reasonably parallel a live system will do. And the whole data lifecycle stuff. So that, in my opinion, is the DBA’s list of jobs aside from anything else that they might be asked to do. Operational dynamic. Ultimately data integrity and service-level management are of the prime responsibility of the DBA. And normally they’re critical. And that’s all I have to say. I’m going to hand over to Dez.

Dez Blanchfield: Thank you very much. I’m going to take us on a bit of a fun, anecdotal journey around why the whole topic that today is about and is more critical than ever. Not so long ago I was involved in a project where we migrated a state government platform that was used for license registration and vehicle registration and a whole range of things around that topic, from a Fujitsu mainframe platform running a thing called A+ Addition, which is a Solaris operating system, or in other words, Unix, running Oracle and doing a very good job of it. And the view was that this thing was getting old and it was time to move it to something else. We had a lot of fun running Unix on mainframe and it was very stable and very secure and oddly enough the SDL platform and it was just absolutely lightning fast. But wisdom was it was time to get off the mainframe and move.

This significant challenge of mapping all the systems and business logic and the SQL environment for the databases underneath and looking at how we were going to architect and engineer a new home for it. And we ended up taking it to one of these things which is a couple of years old now, but one of the top end of the Sun rack system Starfire servers. And these are probably some of the biggest tin you can buy on the planet that all lives in one big box and a symmetric multiprocessing server. It was a mid-range system in our world. It ran Unix and it ran Oracle natively and the view was, “What could possibly go wrong?” Well, it turns out, a lot.

For example, at the time, and we’re not talking about long ago, we had to go through a very manual process to discover what was on the mainframe platform and bring across. In particular the actual database environment and the SQL logic. So the view was it was going to be a fairly straightforward Oracle-to-Oracle move, database-to-database move; all the business logic would come across, most of the business logic had been written in embedded queries and triggers, and how hard could it be? But something that was supposed to take months ended up taking not quite a year. To just physically and manually go through every part of the Unix on the mainframe environment, discover where all the databases were and how many instances were running and what was running on those instances and it was a non-trivial exercise and we ended up doing it three times just to make sure that we had captured everything. Because every time we thought we had dug as deep as we needed to, under the surface it turned out there was more there.

The other challenge we had was which instances are running and in what state? Is this a development environment? Is it a test environment? Is it part of the integration process? Is it systems integration? Is it UAT, the user acceptance testing? Is it production? Is it a DR environment? Because the great thing about mainframes is you can build these little virtual environments that we all take for granted now and move things around. And you’ve got to work out is this person doing production-grade development and testing, or are they doing production production, are there actual users on this? Remembering that this thing is doing real-time issuing of driver’s licenses and car registration and things that really matter to people’s lives.

And it took a long time to run backups for this thing so we didn’t really have a window of maintenance to take the thing offline and see what happened. There was no such thing as rerouting it. We also had the challenge of not just finding which instances were running and where and who for, but then we had to work out what versions of what instances were running. And this is where I almost lost my plot. When I started realizing that we had two or three versions of the production environment running through various levels of testing and there was very little in the way of tools and systematic approaches to this. We literally had to delve into the code and into the running instance and in some cases take the risk of taking something offline for a little while. We got to the bottom of this whole thing, we mapped it out, and it was a very manual process as I said. And we finally made the whole ETL shift, dumping it from one place and moving it to another and on the whole it worked. And we were like, okay it’s functional, we’re very happy with it.

But then we ran into a number of very serious solid brick walls. In particular we found performance issues. And the sensible thinking of the day was, well it’s gone to a bigger, better, faster, harder hardware, there’s no reason why it should perform badly at the application on the database level, so let’s start looking elsewhere. So we completely re-engineered the network twice. Every router, every switch, every cable, we went from Ethernet to fiber in some cases, we upgraded software, we patched, you get the view. We essentially re-built the network twice thinking that was performance issues there. And it looked and felt like it was. We went through different security systems, different firewalls. We patched the operating system. We moved stuff from one compute blade to another. And we spent a significant amount of time looking at the infrastructure piece of it.

And then we realized that when we disconnected the servers and we ran some other applications on it that the network ran just fine. So we started pulling the operating system apart. Same issue. But interesting, the network level and the operating system level, the tools were there, it was actually relatively straightforward for us to benchmark and test and prove that each of those pieces worked. But even then, on the Solaris on mid-range on SPARC hardware platform, the tools just weren’t there for us to start to diagnose the database environment. You know, mapping whether we’d brought all the instances across. And so we had to actually build our own tools and write some and sit down and, whether it was within the database tools themselves in the native scripting languages or whether it was a series of shell scripts or in some cases a bunch of C programs.

We finally delved into some very interesting issues where the logic underneath the SQL layer, the actual database engines themselves, it turned out that when something was built a particular way for something that ran on the mainframe version of Oracle was migrated to the Solaris on SPARC version Oracle it didn’t immediately transpose the same performance. So this was quite a painful journey for us in the first place, just doing it and finding it all, but now we had to diagnose it on the new production system and again this thing blew out a month’s worth of migration to nearly a year. And it simply came down to the fact that we didn’t have the tools around. Running around doing things like trying to map metadata.

At some point we almost decided we needed a Ouija board because it was going to be easier that way to just randomly point and poke. Simple things like finding out who had access to the old systems and why they had that access. And who needed the access to the new one and confirming, getting someone to sign off and confirm that and mapping that. Even something as simple as the size of the database weren’t consistent across the two platforms. We had to build a tool to do that and do some comparison between how large is the database in tonnage, in raw megabytes or terabytes on System A versus System B. And diving into more detail around performance and the performant environment. Again, had to build new tools. There just wasn’t any off-the-shelf for us.

And you get this whole message out of this, when we got to the end of getting the thing running and we got it stable, every single piece of it was a very manual process, the only way we could automate something was if we build a new tool or new script. And if we had the tools that are available today, life would have been so much easier and so much better. And we would have saved millions on this project. But I think that what we’re about to talk about today is the fact that the tools are available now and they do make life so much easier. Many of the pitfalls still remain. Discovery of the databases that are out there and which instances are running what. What state they’re in. How many are running? Why they’re running. Whether they’re running well. Are they being backed up?

These are all things that we in many ways can take for granted now with the right tools. But there was a period in this particular anecdote as I said, where that was something that a lot of us lost a lot of hair about, we probably took fifteen years off our lives, and lament the fact that the tools weren’t there now. And I’m looking forward to hearing a lot more about that from our guest today, Bullett. So with that, Bullett, I’m going to pass to you, and I look forward to hearing how you’ve solved this problem.

Bullett Manale: Alright. Sounds great. Eric, let me take over here with the slides and talk a little bit about, real quickly, Idera, the company, before we get into the product itself. Just as an FYI, this is kind of a portfolio of different products that we have available.

Eric Kavanagh: Your audio is kind of hot so if you’re using a headset just pull that up a bit.

Bullett Manale: No problem. Is that better?

Eric Kavanagh: That’s much better. Take it away.

Bullett Manale: Alright. So today we’re going to be focusing on the Inventory Manager which is obviously aligned to a lot of these topics that we’re discussing. I just want to give you a little bit of an understanding of how this product got where it is. We started off kind of looking on a day-to-day basis with our product line, we have a performance monitoring tool called Diagnostic Manager. We have a Compliance Manager tool. So, a lot of different tools around SQL Server and inevitably we always ask the question for licensing purposes, “What is the number of instances that you currently manage within your organization?” And the interesting thing was we never were able to really get a firm answer on that. It didn’t really matter who you spoke with. It was always kind of, “Well we think it’s around this number.” Those kinds of things always came in and then we would have to go through this process of figuring out exactly what it is that they had that they wanted to license in terms of the instances that we’re managing.

We obviously figured out really quickly that there seems to be some pain associated to that with a lot of the DBAs. Obviously as a DBA one of the things that they’re responsible for is knowing that, because one of the things they have to do is worry about their licensing agreements, in our case with Microsoft and SQL Server. Obviously they have a lot of other different areas that they’re responsible for, but that is one of the ones that is kind of a big ticket item in terms of as a DBA what your general responsibilities are. With that what we kind of came to the conclusion of is we need a tool that makes it easy for a DBA to be able to really understand that number. Because you do have SQL sprawl if you want to call it that and it happens for a number of different reasons. There’s not maybe as much control around who’s installing the software and those kinds of things.

And the worst thing that can happen is somebody gets their hands on a copy of SQL Server, installs it, starts working with it without any knowledge to some of the other organizations or departments in the company, and then the next thing you know, maybe the data’s not being backed up, and those kinds of things that could happen. Where now you have another problem, where you have situations where you’re going to actually lose critical data because you don’t know that the instance even exists in the first place.

One of the things that we had to do was say let’s figure out the discovery piece of it. And then on top of that be able to organize and manage that information that we are collecting in a logical way that makes sense based on what the business is doing. And then obviously from that be able to make decisions around that information and be able to do those kinds of things. That’s kind of where the tool started and where it came from. I can tell you that in talking to DBAs on a regular basis, what we really have is that problem of not knowing how many instances that they have.

And it’s funny because, the term, you can’t manage what you can’t measure, always came up with performance tools that we have, like SQL Diagnostic Manager, but you really can’t manage anything if you don’t know that “its” even there in the first place. So that’s kind of a big part of this tool as well, is being able just to be able to know that it’s there.

Now on that note, talking to some of the larger organizations or enterprise shops with SQL Server, the interesting thing that we found with a lot of guys that we talked to was that they actually had set a time up during the course of their year where they actually physically walked from one place to another to try to determine what that count looks like. You can imagine as a DBA you’re getting paid a pretty good amount of money to physically walk from one machine to another in some cases, which was surprisingly what we would hear from some pretty large companies which I won’t name. But just kind of an interesting point that two weeks of a year might be spent doing these kinds of exercises just to find out if their license counts are correct.

This is all related to this tool and how it helps but the way we addressed that was through the ability to do discovery based on a number of characteristics of SQL Server. And so the first question is, what do you point to or what do you try to look at first? The way we did that was to say let’s do it by IP range or we can do it by the membership of the domain itself in terms of the computers that are members of the domain. That’s kind of how we addressed that part, just to be able to say this is the area that we want to focus on in terms of discovery.

And then the other part of that is based on those characteristics, the ports and other things, WMI registry keys and those kinds of things, we can gather and ascertain that SQL is likely running and installed on that instance or that particular environment. It’s obviously a much better method than the sneaker method or sneaker express method. Now the cool thing is, is that all of that information that we’re gathering about the instance is being kept in a repository and it can change as the environment changes. It’s not just about, “Hey, there’s an instance, here’s a list we found,” but it’s as the DBA, or the person managing the instances, being able to determine if they want to make that part of the inventory, and then when it’s not part of the inventory, to be able to decommission that instance. And so they have the lifecycle of the entire process of the SQL Server instance to be really easily understood within the tool.

Once we’ve discovered the instances, what do we do after that? The other thing is a lot of the information about the instance, I don’t want to have to go manually obtain it and put it into a spreadsheet or those kinds of things. And that’s another thing that was kind of interesting in talking to DBAs about the inventory process and licensing, is that you’d be surprised at how many DBAs I spoke to, when you ask them, “How do you maintain your inventories?” and we’re talking to DBAs which is the really ironic part of it, that they are keeping that and tracking that in a static spreadsheet of all things. Like I said, it’s very ironic when you think about that for a minute. But that was in a lot of cases, and still is the case with a lot of organizations how they manage that. How they keep that. It’s a master copy of an Excel spreadsheet that gets floated around and it’s got to be updated on a regular basis.

Those are the things that was a challenge and so by registering that instance and making it part of the inventory, you can do that and pick up the information. You can have it automate whether or not it becomes part of the inventory, the version, the edition, the other things you can do with it are you can manually add maybe that list or Excel spreadsheet that you have. You can import that into this tool called SQL Inventory Manager. If you already have a starting point of instances that you feel you’re pretty confident about, you can import those instances in and then make that part of your managed inventory within the product. Once we have the instance and once we know that it’s there then it becomes, okay we’ve got a lot of information that we can leverage by knowing that that instance is there, by going out and gathering that information.

And a lot of the information is going to be needed for more than just licensing purposes. A lot of it can be used for obviously just knowing where things are, being able to search through this information after it’s been obtained. But the key stuff is the server, the hardware itself. Being able to understand what kind of machine is it, maybe the model or the manufacturer, memory, the amount of memory, whether it’s a physical or virtual machine and especially the number of physical sockets or cores and CPUs and those types of things.

In terms of the number of cores, especially with SQL Server, knowing the way that they’re doing their licensing is per-core calculations now in the newer versions of SQL, that becomes a really important part of it and it’s not anything you have to go out and actually go dig for. Once the instance is identified we can provide that information and get it out and let you view it and understand it and obviously may take advantage of it.

The next layer down is at the instance which obviously you have a lot of different [inaudible] of the SQL Server instance whether it be standard or enterprise or even express for that matter, or the free version of SQL Server. Being able to understand also what applications are tied to that instance and this can be done automatically. Being able to understand the configuration settings and those kinds of things as well as other pieces of information that are related to the instance of the SQL Server itself.

Then you get down to the actual database and seeing the configuration settings, the amount of space tied to that data, where it’s located, all of this stuff gets automatically populated and so that is a huge time saver. And once again, because it’s dynamically going out and on a daily basis identifying new instances, it’s a living thing that you have in terms of your inventory. That’s kind of the goal of the product is to make it that way, is to make it something that’s dynamically changing.

Now once all of this information becomes available to us and we can pull all of this data in, then it really makes sense to start creating in some cases your own metadata associated to these instances and that metadata can be created in a way that kind of aligns to the way in which you do business.

So if you have your instances grouped by geographical location, or by application owners or by DBA owners or whatever, it might be in terms of how you want to group those instances, how you logically want to make sense of those instances, then there’s kind of two areas within the tool that’ll give you that capability.

The first is the ability to create a tag of instance, or a tag. Which is essentially creating an association to either the server, the instance or the database so that you can create views and answer questions that may come up on a day-to-day basis, that really helps you get a handle on what you have, what you’re managing and how you want to move forward with that information.

The other thing that we have is something called inventory fields or custom inventory fields and these are more specific to kind of tidbits of information that you can drill into, for example the database layer I might to decide to add a drop-down list that has all of the DBAs and I can put who’s the responsible for that database depending on that type of situation or whatever, whichever database it is with whoever’s responsible for it be able to select that so that I know that they’re the ones that are responsible and very easily just by digging into the inventory.

So these pieces of information become very valuable, especially if you have a large environment, because it just helps you to make sense of that information and knowing what you have and how you do it.

So let me go ahead and switch to the next slide here. What I’m showing you now is that all of this information we’re gathering, all this information and data that we’re collecting and applying metadata to gets you the ability to then to make a lot easier and quicker decisions when it comes to turn up your licenses with Microsoft in the enterprise volume licensing or software insurance with Microsoft.

That makes it really easy for you to do this rather than having to, have to go and do a lot of manual collection of data, a lot of manual gathering of that information which really just overall makes it a lot better of a process. So that’s kind of one of the mandates of the product, sometime to make it easier for the DBAs to make those decisions around licensing.

Now the other thing we, kind of talking to DBAs, discovered and learned really quickly is that – and it’s kind of going back to what was discussed earlier – you might have 300 instances in your environment of SQL Server but there’s really only maybe a subset of those that are being really fully monitored and managed from a traditional performance monitoring type of tool.

So if you go and you actually sit down with the DBA and you say, “Look, we know you’ve got these 20 instances or 10 instances of the 300 that are being monitored with this tool that’s designed to monitor that and conform to your SOAs and get alerts and all those kinds of good things,” what we also found is that if you asked, “Then well what about these other 280 instances that you have? Do you care about those?” And they do, they care about them, but they just don’t want to necessarily make an investment to monitor those at the level of depth that can be done with those instances versus those 10 or 20 really, really critical product instances.

So the other part of the equation with this tool is that it also helps in terms of being able to make sure that at a base level you’re covered in terms of the health of instance. Now it’s not going to tell you if you’re having a deadlock or who the victim of the deadlock is. It’s not to get to that level of the sessions themselves and the details of the queries. But at the same time it’s still going to let you know that, hey the server’s down or hey the volume is filling or you need to do backups of the database, that’s kind of an important part of being a DBA.

So those kinds of things are definitely still important and so with this tool kind of made it a way for you to have a catch-all for your really critical instances that have a lot of, a lot of worth tied to them, if they go down you need to know right away. They can have the higher level of monitoring and being able to do those kinds of things, whereas with this it’s going to be able to pick up any new instances that are added to the environment and make sure that they’re accounted for and also make sure those basic levels of health checks are being formed.

So that’s kind of in a nutshell what the Inventory SQL Import Manager’s all about. Now I’m going to show you a demonstration of it. Before we do that, just quickly I kind of show you this is the architecture slide here and just to kind of show this, the instances of SQL that we’re managing, we can discover everything from SQL 2000 all the way up to the new versions of SQL.

So we can do that without ever having to deploy agents to the instances themselves. We do it through a collection service and it’s going to go out and gather that information and put it in a repository and then from a Tomcat web service front-end console we’ll be able to then interact with that data and view it. So it’s pretty straightforward architecture.

I’m going go ahead and switch over and actually take us into the product itself so you can get a feel for it, an understanding of how it works. So the best way to do this is to first kind of introduce you to the interface itself in this is kind of a dashboard that we’re looking at here.

I can see the number of instances right now that I have under management is not quite that many. But I don’t have a whole data center in my back pocket either. So I’ve got about six instances that we see here. Now, that said, I’m, what I’m going to do is walk through the process of discovery and show how it would work.

Now the first thing you would do is in the administration section you can specify how you would want to discover your instances. You would be able to put that information in here and once again that can be done through a range of IP addresses. You can point to a domain or subdomain and be able to only on those machines that are members of that domain be able to perform those checks you would be able to choose a number of different kinds of characteristics of when SQL’s running to check for.

Then once you’ve done that and you can have it automated to run on a daily basis to go and gather that data. You’d also be able to do it on an ad hoc basis if need be. But once you start that, that process of discovery then what you’ll start to see is when you go over to the instances view over here. You have a Discover tab and the Discover tab is going to show us those instances that have recently been discovered. So in our case we have a number here. What I’m going to go ahead and do is go ahead and add the one that we’re going to use as the example. So this is a Chicago instance in this case, right? I’m going to go ahead and add that instance to my inventory.

Alright and it’s going to walk me through a couple of things here. I’m just going to go ahead and you’ll see we can set the credentials. My credentials should be good there. I’m going to go ahead and you will notice I can assign ownership of this if I want to. I can also specify a location. Now the location itself can be added too, and it’ll remember that the next time around, obviously.

Once again, I can also associate tags to this in terms of the metadata and how we would want to put these instances of SQL, particularly this one, into whichever buckets we want to put it in. So we have some current tags, popular tags, so we can look at a bunch of different tags that I might have already included. I’m just going to pick some of these at random and we can apply that.

So now when I go ahead and add this to the inventory. Now that it’s been added, we would now see it showing up under this managed view and so you can see it listed right here. So you know that’s the first step and what I just showed you was the way in which you would mainly add those instances as you go through on a day-to-day basis. In some cases you might say you know what if it’s an enterprise edition of SQL server I automatically want to add that to my inventory? I don’t need to manually go and choose to do that.

Jocelyn: I’m going to interrupt you real quick. We’re not seeing your demo.

Bullett Manale: You’re not?

Jocelyn: No.

Bullett Manale: Well that’s not good, let’s see.

Eric Kavanagh: If you go to top left-hand corner, click start, click on that.

Bullett Manale: Ah, okay.

Eric Kavanagh: And now do share screen.

Bullett Manale: Sorry about that. Yup.

Eric Kavanagh: That’s alright. Good catch there, producer Jocelyn.

Bullett Manale: Alright so is that better? Are you seeing it now?

Robin Bloor: Yes indeed.

Bullett Manale: Alright, so let’s kind of just walk you through where we were real quickly. We’ve got the discovered instances that we have had earlier. I just added the Chicago instance and so what you see now is it’s now listed here. Notice it’s already pulled a lot of additional information. If I click on the instance itself you’ll start to see all of the kind of pieces of information we’ve already collected about that instance. Now here’s a listing of all the databases that are there. We can see a breakdown of the databases by size and by activity in terms of which ones are having the most of a size and activity.

Once again, we also can tell you right off the bat which applications we see running on that instance based on the workload that we see running on the instance. So it’s kind of nice to be able to do that automatically. I don’t have to go in and tie the application to the incidence. Based on what we’re seeing we can populate that. Now if you want to manually add an application you absolutely can do that. But it’s just a nice way to be able to show the association of the instance to the database or, I’m sorry, to the application.

You’ll also notice that on the right side of the screen we have an instant summary and down beneath that we have a server summary. So we’re talking about at the instance key pieces of information here, knowing the version and not just, you know, the SQL Server 2012 but the actual version number that, including and telling us what hotfixes are tied to it, what service packs are tied to it, it can be very important to know. Obviously memory requirement’s important. Everything like that, whether it’s clustered, all of this information, I don’t have to put it in – it’s already being gathered and collected, and once we identify that it’s a discovered instance, that’s going to be part of our inventory.

The other thing that you’ll see here – and it’s going to show you – it’s under this instance view. We have these attributes that I talked about earlier, the custom attributes that can be added. So we can add open kind of text box fields, we can do yes/no in terms of, you know, a billion kinds of choices. We can even do drop-down lists. You can do that at the instance of the database or at the server level.

Then if we scroll down a little bit further we can see all the related information to the server itself. So you know all this kind of stuff is obviously really, really helpful because it’s all gathered and collected and it’s there for us soon as we make that decision to make it part of our inventory. Here we can show some of the differences in terms of the CPUs, the number of logical versus physical, how much memory. So you’re really getting at a really good and wealth of information without having to do a lot of work.

Now the other part to this, as I said, is we’re gathering this data at the instance of the server level. If we even go down to the database we can see a lot of this stuff is broken down for us as well. So if I go to my compliance repository, in this case I could say, well you know this is dealing with a, this is a compliance database in which level of compliance or regulatory requirement is it associated to and it might be, let’s say, SOX compliance or PCI compliance. So I can choose which databases have which compliance associated to them that I have to fill or make sure that I’m maintaining in terms of that regulatory requirement.

So this kind of stuff has proved to be very helpful to DBAs because there’s a place that they can centrally go to keep all of this associated metadata within their environment easily and they can make it, like I said, conform to their business as they’re doing, as the way that they do business. So if we look at all the stuff so far what we’ve seen, you’ve got obviously a pretty good overview of the instance, if I drill into it.

I can also search as well so I said let’s look for that compliance repository across my inventory. Then what you’ll see here is that I can search for these things and be able to identify them. I say that— I’m not sure what, my go button’s not working there. Okay. Let’s see, let’s try that again. There we go. So we would then be able to see a breakdown of where we see anything with we’re compliance in and I can drill down into it and see it from that standpoint as well. So you got a really quick and easy way to kind of dig into this data.

Now as we mentioned before, you’ve got a lot of different ways to create metadata against the instance server and database. The other part to that is being able to take advantage of that in the way you’ve grouped it and the way you’ve associated to it. We go to the explorer view, we can do just that. We can say I want to do a database count by locations. So the number of databases at each location of the environments that I support. Or possibly maybe it’s based on the the owner who owns the instances that I have out there in terms of maybe instance count. So we will be able to see that. So you get a really good, easy way to kind of paint these pictures for you based on whatever question it is that you’re trying to answer at the time.

Then what you have that information kind of created the way you wanted to, we can export it out to PDF or different formats to be able to leverage it and send to our colleagues or do whatever we need to there. So you know you’d be able to do those kinds of things. Let’s go back to – did I lose it? There we go. Alright so hopefully this makes sense in terms of what I’ve talked about so far. Now that the data that we’ve collected, all this is obviously really vital for a number of reasons – licensing and whatnot.

The last kind of thing just to mention is that we go over to this administration section here. This is where also you can configure your email and your alerting and be able to make sure that for the things that you would want to really know about, you can set those things up also. So we can set up email alerts, we can set up the ability to turn on certain things and turn off certain things and then be able to then determine who would be receiving those emails, and subscribing to those alerts we can associate who we would want to be, who would want to know about those kinds of things.

But like I said earlier, this is a really nice way to do, at least have overall peace of mind of knowing for your entire enterprise SQL instances – what it is that you have and also making sure that it’s running optimally even if you don’t, haven’t made the decision to make an investment for a heavy hitting performance monitoring tool to manage that instance. This is going to cover you because it is a very affordable way to go out and for a lot of instances be able to do these inventories and be able to do a kind of a very broad kind of general level of monitoring to make sure that you got that peace of mind and know what’s going on.

So hopefully that makes sense in the way we’ve described it and showed it to you. I guess from that standpoint I can go ahead and pass it back and we can talk some more.

Eric Kavanagh: That sounds great. So Robin? Dez? Any questions?

Robin Bloor: Well I’ve got questions. It’s very interesting to actually watch this, I mean I just wanted to make the comment that pretty much everywhere I’ve been, not just amongst the DBAs, but amongst the network guys, amongst the storage guys, amongst the virtual machine management guys, they’re all working off spreadsheets.

Eric Kavanagh: That’s right.

Dez Blanchfield: You kind of know that that’s, you kind of know that that’s okay until the numbers start to move. When the numbers start to move, you know that they’re going to get in trouble. So the question now I’m kind of interested in and I know it’s going to be difficult for you to answer, but what, if you go into a place where they have nothing like this in there for the working of spreadsheets, so let’s assume the DBAs are very smart guys and so on and so forth, what kind of ROI do you think you’d get from implementing something like this? Do you have any figures on that at or any guidelines on that?

Bullett Manale: It’s hard to say what the ROI is because environment’s going to be a little different. Obviously that the larger the enterprise, the larger the environment, obviously the more the ROI will probably be if they’re using, you know, manual methods now.

I do know I’ve talked to a number of – when I say large organizations in the thousands and thousands of employees and also probably the thousands of instances as well – where I have people where I show this to them and they say this will take two weeks of my time back. I’ve had that said to me more than once. So it’s hard to say in terms of the actual dollar amount from a purchase, but it’s considerable when you have the environments.

Like I said, it’s pretty consistent, it’s the people I, most of the people I talk to are keeping this stuff in a spreadsheet. So it’s just it’s a very, very subjective thing because every environment’s, it’s a little bit different in the terms of how they do their licensing and how they’re doing their licensing with Microsoft is another part of it that’s a factor. But if they’re having to do true ups every year or every three years, I think three years at the max for Microsoft that they’ll, they want you to true up at least every three years.

Then you know its considerable and it, you know it’s just something that makes a lot easier. Because it’s a dynamic thing that’s always changing, it gives a little bit more validity too in terms of what it is you’re looking at verses, well we haven’t really updated the spreadsheet in six months or a year. So how often are you updating the spreadsheet is another question to kind of understand that the answer to the ROI.

Dez Blanchfield: Yeah, I mean, SQL licensing, the licensing of this is just a goddamn nightmare, but it’s especially a nightmare because the licensing is not the same between Microsoft and Oracle and anybody else that’s out there doing database things. If you’re actually keeping things in spreadsheets which tends to be what actually happens, you know that licensing time comes around before you actually realize it and you don’t actually have the data, if you know what I mean, to easily get at that information.

Anyway, as you point out, it’s dynamic and I have no idea personally because I’ve never actually had to negotiate with Microsoft, so I’ve got no idea but presumably there are databases that people quite often take down the test data, testing environments and I would guess that those are a thorn in your side if you’re doing licensing. Is that you—?

Bullett Manale: Yes, yeah. That is the case because a lot of times that stuff is forgotten about and then we start trying to figure, okay, well okay we’ve got core licensing that we have to figure out the number of cores for each of these instances and I don’t know, in terms of the standards of what you’re purchasing hardware wise, you might as well purchase pretty good hardware then if you’re not utilizing that hardware the way it should be utilized then you’re overpaying because you’re paying for core pricing when those cores aren’t being leveraged so that becomes a problem.

So the, each version of SQL has a different way in which licensing is being applied which even makes it a little bit confusing. So you do have some challenges around that and so that’s a big part of why this information is very helpful because we can tell you which version it is, we can tell you obviously the number of cores that you have, if it’s older versions of SQL that was per-socket pricing, we can still show obviously that as well. So it just, it makes it a lot simpler of a routine that you have to go through when it does come time to true up that stuff.

Dez Blanchfield: One thing that comes to mind for me, oh sorry go—

Robin Bloor: It’s alright, you go in Dez, I was going to ask a possibly irrelevant question.

Dez Blanchfield: Just something really quickly while you’re on the topic you’re on now – we’re seeing a lot more adoption of cloud environments and if we’re running this inside our own our data center, inside our own environment, they’re crawling around and finding, discovering things is relatively straightforward.

How do we, how do we cope with the scenario where we might have three data sets, two clouds, and visibility across these environments is firewalled and often there’s a data set at the end of a pipe or a VPN. Is there away to do discover from the front end or do we need to, to start opening up ports so we can scan across certain environments between sort of a cloud and off premises where this platform’s running?

Bullett Manale: Yeah it would, there would be some consideration in terms of the ports. So it’s, unfortunately I wish I could say it’s going to break through all of those environments but there’s some different options that you could do with this. Obviously, if you’re doing something like Amazon EC2 all you would need really is the access to that environment through your connectivity, assuming your ports are open and then being able to specify your IP addresses or your domain associated to that and it can start the collection and start discovery.

So it’s, in those types of environments that’s really not a problem; it’s the more specific types of environments like RDS and where you’re just getting the database itself where it’s going to be a little bit more challenging to see and discover that type of information.

Dez Blanchfield: So following from that was there, there’s databases and databases. So for example the good old days of just sort of having a very, a very big database engine like the anecdote that I shared at the front where it’s just one massive platform and all it does is provide database. These days, databases are embedded in everything, in fact, there’s like two or three of them just running in my phone behind apps.

What kind of challenges are you seeing with scenarios where you’ve got environments coming from Lotus Notes, with apps behind them, SharePoint with the database on the various internet, and so forth? Essentially everything is powered by database at the back end. What sort of things are you seeing out there and what sort of challenges are you seeing people face just trying to map those kinds of worlds and what your tool does for them?

Bullett Manale: Well I mean that the thing about it is that what you said – everything needs a database now, so a lot of times there’s a lot of probably, there’s a lot of databases that are getting introduced into the environment that the DBA themselves aren’t even made aware of because it’s not very hard to get a SQL server installed in the environment, generally speaking.

This tool also identifies things like express databases as well, so the free versions of SQL Server. Funny enough, when you go talk to the DBAs, once again, you don’t get a consistent answer in terms of do they care about the free databases that are out there. A lot of these applications that you speak of will use the free version of the database. But the organizations themselves will have a different attitude in terms of who’s responsible for that database depending on who you talk to.

Some DBAs that I speak to, I can think of the last time I was at SQL Server PASS, which is in Seattle, you ask the question “Do you care about your express databases?” and it was about fifty-fifty. Some of the folks, they wanted to know about them as a DBA because they felt like that they’re part of their responsibilities even those expressed databases they could still contain critical information; they still need to go through the process of being backed up and still need to make sure that all things are working from a health perspective on them. But just knowing that they exist is just as important if not more important.

Whereas the other half of the folks are, “Hey, we’re not we’re not responsible for those databases and anything that they put on them is at beware of the person that installed them.” But I would say that overall what you said, everything pretty much nowadays has an application tied to it which is just contributing more to the complexity and the confusion of having to inventory that information.

Dez Blanchfield: Yeah I’ve seen some, government sites are probably my favorite but more often than not I’m seeing in enterprise environments now where is that, as you said, that people forget I even, when they install something like SharePoint or like self-exchange so you know that they do come with a free version just built in because they want, you know, install it quickly and not worry about having to go and buy licensing.

Then it gets big and then somebody starts complaining about performance and they’re like, “It’s just your old server, your storage, your network, whatever,” and then the DBA gets called and they’re like, “Well, you’ve just crammed everything into this free version of the database, which is not what you need to perform this large.”

Particularly when you got scenarios like Project Manager and Office is running hundreds if not thousands of projects across a large enterprise or a corporate and they’re using SharePoint with Microsoft Project Server and they’re dumping all their PMO stuff into this database. But at the front end they’re like, well it’s just a web interface. But really there’s databases and databases.

Bullett Manale: Yes.

Dez Blanchfield: So what are they, one of the kind of first steps that people here I guess there’s a couple questions that we might want to bring in from the audience. One of the first questions is where people start? What’s the first natural step for them to go, “Okay, we need to kind of do the Alcoholics Anonymous version?”

We’ve got more databases than we know what to do with. What is a natural sort of step look like of them to go, “Okay we need to get this thing and start running?” Do they just go cold turkey or later they really need to start small and just get some experience around mapping their environment?

Bullett Manale: Well I think that said they’ve got to map the environment. Now Microsoft offers a free tool to do that, the Microsoft Assessment Planning Tool, it’s a free tool but it’s static. You do the discovery and that’s it. You get a list of the things that are out there. We took that and said look let’s take a step further let’s do the discovery, let’s find what’s out there and let’s put it in the repository and let’s make it so that it’s dynamic and we can add to it, remove from it.

But overall the biggest first step is I think just to find out, do the discovery. Whether that means downloading our product in trial, you can download this and trial it for 14 days and you can point out to your environment and do the collection.

Now if you already have a spreadsheet with a bunch of that information in there that you are somewhat confident that that information is correct, you also have the ability to like the import to CSV that spreadsheet with all that information and make that part of what you already have. But in terms of figuring out what you don’t know, the only way to do that is to manually go out, do it or have a tool that looks for that type of thing like this one. That’s the decision that you’re going to have to at some point make is, “Do I try to automate that discovery or at least get a good basis of what’s out there first and then maybe worry about some of the exceptions?” But for the most part you probably need a tool.

Dez Blanchfield: So just quickly. Where do people go to get started on this? They hit your website? How do they reach out and get started on this quickly?

Bullett Manale: If you go to Idera, I-D-E-R-A.com, you will see, and I can actually just real quickly show it real quick. Over on the Idera website you’ll go to products, go to inventory manager. You’ll see there’s a download link right here. You’re just determining which build do you want to install on a 64 or a 32 bit, and that’ll get you going and you can start your discovery from there.

Robin Bloor: Fantastic and great, great presentation, thank you very much.

Bullett Manale: Thank you.

Eric Kavanagh: We have a couple of questions from the audience and we’ll email those to you because we have to hard stop ourselves today, but Bullett, again, great job on the demo, great job by our producer catching that it wasn’t showing.

Bullett Manale: Sorry about that.

Eric Kavanagh: No, this is good stuff, you’re giving visibility into the core of business, right? Because business runs data and you’re giving visibility right down to the core. So no more hand wavy stuff; now you can actually point at things and get that solved. So good for you.

Bullett Manale: Thank you.

Robin Bloor: But it was great to see it live too by the way, well done.

Eric Kavanagh: Yeah, we’ll archive this webcast for later viewing and then we will have it up hopefully within about an hour or two the initial archive goes up sometimes it’s a bit longer than that, but we’ll be sure to let folks know. With that we’re going to let you go, folks. Thanks again for attending the Briefing Room, we’re actually the Hot Technologies. We’ll catch up to you next time. Take care, bye-bye.