 My name is Monica and I'm with the digital team at Data Diversity. I'm stepping in for Shannon Kemp, our Chief Digital Manager today. We would like to thank you for joining this month's webinar. Do you know where your databases are? It's part of our monthly webinar series sponsored by IDERA. Just a couple of points to get us started. Due to the large number of people that attend these sessions, you will be muted during the webinar. For questions, we'll be collecting them via the Q&A in the bottom right hand corner of your screen. Or if you'd like to tweet, we encourage you to share your highlights or questions via Twitter using hashtag Data Diversity. As always, we will send a follow-up email within two business days containing the links to the slides, the recording of the session, and additional information requested throughout the webinar. Now let me introduce you to our today's speaker, Scott Stone. Scott Stone manages IDERA's performance management products and has over 20 years of experience in product management and product marketing in the software and technology industry from small startups to Fortune 500 companies. For the past 15 years, Scott has focused on development of database performance and security products at various companies. Earlier in his career, Scott was a software engineer in the space and defense industry. Scott holds an MBA from Rice University, as well as a bachelor's and master's degree in electrical engineering from George's Institute of Technology. And with that, I will give the floor to Scott to give today's webinar. Hello and welcome. Thank you, Monica. I was going to say I have more experience for more decades than I care to admit, but since Monica gave my full bio, I have to just tell you that a lot of these tales, we talked that the definition of this webinar is cautionary tales and solutions. All of the cautionary tales I'm going to be talking about today are not made-up scenarios. They are real scenarios that I have firsthand experience with. I will just say that I either have firsthand experience either as I was involved directly or I was involved with a vendor that was brought in to solve the problem, or I heard this tale from a customer who was frustrated. So with that, I want to talk about the hard questions to answer. A lot of these questions might seem to be not the most important thing if you're involved as I am in performance monitoring. You're primarily concerned about performance, but if you're a DBA or a DBA manager, you're also going to be expected to answer these questions at any time. Where are your databases? Who has all the databases? Are we compliant with licenses? Are we current on backups? What are the reporting services? How many SQL servers are we using today and what's the ongoing cost of those? Could we save money on those SQL servers? There's three areas that those general questions fall under. The first is discovery or inventory management. Do you know all the databases, your environment? Do you know who to contact? If there's a problem with a given application, if you have to reboot a given SQL server for some reason, do you know who's going to be affected if one goes down? Licensing is more direct. You always need to know that you're compliant with licensing. Licensing enforcement varies, but in all cases, all vendors including Microsoft expect you to be make sure that you are compliant and it's on you if you violate that license, whether the software allowed you to do it or not. And finally, environment states. Which databases are up? Which instances are up? Which are the ones that are problems? How do I find out if there is a server out there that I don't know about and what state it's in? This is where the cautionary tales, the ghost stories start. It's not associated with Halloween at all, but it's just sort of appropriate that it's coming up right now. And the common theme for all of them is it's not your fault, but it is your problem. And most of these cases withhold the names to protect the innocent and sometimes the guilty. But in most of these cases, at least the people involved did not, it was not their fault or they did not see it as their responsibility, yet they still were gotten in trouble. So you'll see what I'm talking about as we go through some of these cautionary tales. The first one is unmanaged sprawl. This was a large distributed company with an enterprise database group. It's well managed. They thought they had everything in order. I'll explain that a little later. But they thought they had everything well under control. There were DBAs assigned for all technologies and they had their assigned databases and applications and they knew what was going on with all of those. They had a changeover and a CIO, a new CIO came on and in touring the various business units, he stumbled on what's commonly called shadow IT. A particular business unit had bought its own applications, spun it up on its own, and it was running in this case, it was SQL Server. And they were able to do that because SQL Server is relatively easy to get up and running. So they were able to get things up and running and they had this application that they were dependent upon. The CIO saw that that was a problem and he decided to resolve the problem to bring all applications everywhere under the corporation, under central management, all under him so he knew where everything was and what the state of everything was. So he ordered an audit of an inventory of all applications and databases in use. This started with just an appeal to the various business unit managers to voluntarily report what applications they were using so they could compare those with the applications that the IT department knew about and about 50 applications were voluntarily reported by the business units. That's a rough estimate. This is sort of based on my memory. It was on that order of 50 between 45 and 75. The DBA group used a discovery tool to detect all databases running. They didn't find any unreported databases for Oracle. They primarily had Oracle and SQL Server. So that's not too surprising because Oracle is typically a little more high-touch, requires a little more expertise for anybody. It's not for the idle practitioner. So they didn't find much of Oracle out there. Not at all, actually. But for SQL Server, there were over 300 reported databases that were detected. And by databases, I'm meaning instances in this type, in this case, they were unpatched, unmonitored, unmanaged. They were completely... They were in a state that no one knew and no one had... And of course, the people who had implemented it never knew how to or whether they should be maintaining the SQL Server. They just installed it and they ran it. And as long as it didn't break, they didn't worry about it. Well, the CIO, in this case, I will say, I was part of a company that was brought in to sort of help resolve this problem. The CIO basically told the database group that you need to manage these from now on. And we need to have a rule that in the future this does not happen. And we had a way to monitor to make sure it doesn't happen in the future. Cautionary tale number two, SQL Slammer. I don't know how many of you remember the SQL Slammer virus when it came out. It was one of the most well-known database viruses that came out. There was a large software development company that had to distribute the developments. It had over 13 different development labs where different products were developed in different locations all around the world. One day, everyone started noticing that the system response time was slowing. Then it's ground to a halt. Everybody was wondering what was going on. The internal and external websites were affected. That means production and sales were halted. Nobody was able to download demos. No one was able to put in a purchase request. The CEO, who was not very guarded in his communications, blasted an angry message to all the employees demanding answers. Yes, it was all. I suppose he didn't know where the responsibility lay, but he knew that he was upset and he wanted somebody to give him an answer. The CIO of the company, which is probably who you should have talked to, answered, copying all as well, probably to let everybody know that ownership had been taken, took ownership, investigated with the IT department. Hours later, the CIO reported that the SQL slammer virus was the culprit and they were working to isolate the problem and get everything back online. They did that pretty quickly. They got it isolated and we were back up and running very well. The CEO wanted to know how the virus went undetected by IT. A few days later, the CIO, it was a question here, triumphantly reported the virus did not come from any databases that IT managed. They had been dutifully maintaining all of their production databases and making sure everything was patched and making sure that everything was backed up and well cared for. It had originated in development deployments but it had spread outside of the development deployments. In the last communication that we were all able to see, the CEO was unimpressed and they declared IT responsible to keep unmanaged environments isolated or to manage them. It was pretty much a it is not your fault but it is your problem coming from the CEO. The CEO decided that from now on it is your problem to make sure that development and IT both all stay compliant and that we are not vulnerable to any viruses from unpatched machines or unpatched databases. Finally, license compliance. Again, another software development company that has an MSDN distribution. The development version of SQL Server permitted unlimited usage for development purposes. There is not a lot of restriction because for development purposes you do need to at least be able to simulate production systems. The R&D individual teams failed to religiously enforce this license definition and by that I will explain that in the next bullet here. Microsoft found out that they had failed to rigidly enforce this license definition when someone from R&D opened a support ticket on a licensed instance but it so happened that this particular license that was being used for a small production application that someone had purchased and was using again within the R&D organization and they had rationalized that they were okay with that but Microsoft disagreed that was not the purpose of the development version. In this case, Microsoft is the one that said it is your fault, it is your problem. You need to rectify the situation and purchase the licenses that you need for the applications that you're using. In these stories we've talked about a lot of vulnerabilities that are obvious. Data growth and groups are spinning up their own applications and servers and on many places you're involved with acquisitions and mergers with companies and it's hard to know what's in the new organization and how well it's controlled. Sometimes it's a matter of merging the IT department sometimes it's a matter of you may inherit something that's you have no idea exactly what it is you're getting you just have to figure it out as you go. Microsoft SQL Server license changes have been happening over the past so 15 to 20 years creates a lot of confusion for the users and who are operating under one license model and then when they go to purchase again another license model is in place and they have to figure out where they stand and do they upgrade. Additional unknown servers could result in a lot of additional licensing cost if there's an audit from Microsoft. Lack of DBA insights into servers in terms of how well are they running which ones are down used could result in servers running without patches leading to production interruptions. Again this goes back to the SQL Slammer tail just because the servers that you're directly managing in production are well maintained doesn't mean that you're not vulnerable to other databases in your environment that are not well maintained. And you can't manage it if you're not aware of it. If you don't know what's out there and if you don't know how people are using their systems then there's not a lot you can do about it. So best practices in database inventory is one have some sort of discovery tool that can locate databases or instances that you may not be aware of so you don't get surprised. You want to be able to find at least periodically go out and look and see what is being used and not be dependent totally on self-reporting from the business units. You need to stay on top of which servers are up, which serves are down which need a patch or a backup which ones are short on space. This is where we sort of overlap somewhat in the realm of performance management but before you worry about performance management you also have to worry about availability management. Is it given instance or server working well? Is it going to continue working well for the near future? You need to track server growth for future capacity needs and reallocate server resources for other applications or user groups if some application comes online you need to find out whether you can just assign that to an existing instance or do you need to extend your license? Do you need to get new machines? Do you need to if you're in a cloud environment do you need to get a new environment for that as well? So there you have to keep awareness of whether you have enough availability within your existing systems to take on needs and also be able to anticipate what the growth pattern is so that you can be get ahead of the game for future capacity in servers and storage. You want to be able to generate reports to demonstrate that SQL Server environment is up to date and current on licensing to make sure that you don't have excessive costs and again this goes back to the licensing that's a an example I actually have run into that multiple times again with development licenses being used improperly but in other cases you can just be out of compliance specifically with the latest or with the license model that Microsoft is currently using and you may be caught unaware because a prior version you were completely covered. What are the obstacles to overcome to achieve these best practices? Again these are things that we've encountered customers have told us about these things and sometimes you can overcome them sometimes you just have to work around them. One of those common is a security offer just doesn't like scanning for discovery. In these cases what we like to tell people is there's nothing that's inherently insecure about scanning as long as you know what the scan is for and you control that scan and you know that you know why it's why it's being run. Security officers don't like it security departments don't like it because it's going to trigger security intrusion detection systems or it's going to violate a current policy that's just prohibits that sort of activity but in most cases you're able to explain that you can schedule scans periodically so that you're not setting off too many alarms that during particular maintenance periods you can do discovery processes and so that you can keep aware and make sure because that also makes you insecure if you have something running out there that you don't know about and you're not managing as well. It's more insecure than just running an occasional scan to make sure that you know what's running and what's not running. Another obstacle that we've encountered is business unit shadow IT for those that are not familiar with that term it usually comes up because in a rapidly growing organization or in an organization where there are a lot of their remote offices sometimes a given business unit will take on that will support themselves or attempt to support themselves with information technology by getting their own applications and their own computers and their own databases and they do this because they think it's more efficient but in the long run it almost always is more efficient. Mainly because they don't know what they're taking on they don't know the long-term requirements to manage these systems so they've solved their short-term problem of not having to go through the process but they create a worse long-term problem. So in these cases we ask that if you're in this situation it's to help support funding for additional support for those applications so that the proper group can manage the databases manage the systems and be staffed appropriately so that they don't have the worry that they always have which is that they won't get support when they need it. Another obstacle is corporate IT doesn't have the funding for inventory control. Inventory control is something that is sometimes hard to wrap for executives to wrap their head around performance makes sense they want to make sure that systems are up they want to make sure that we're not wasting time we want to make sure that people can make purchases they want to make sure that people aren't wasting time. Inventory is a little more obscure why can't you just keep that in a notebook or a spreadsheet? Often a lot of people that I've dealt with that's exactly what they're doing they're doing it in a very inefficient way but they've realized that this is exactly what they have to do. They have to somehow keep track of who's using what and who's associated which application who's going to be affected what's more important than other applications which databases are more important and which ones do they have to pay special attention to and monitor extremely closely. So this is a little tougher because it really involves just getting the the organization to understand that the risk that is involved of having something go south is much worse than the amount that you might have to invest and you're having to do this one way or the other you're going to have to either keep tabs of it either with another application with a database of its own which means you have to maintain that on its own as well but in all cases we know that inventory management saves money in more efficient resource usage. So with that I'm going to transition to talk about the particular product that we have called SQL Inventory Manager that happens to at least it has evolved to solve these problems. To give you a little history of SQL Inventory Manager it was originally intended primarily as a lightweight performance monitor for organizations that did not want to invest in the price of a performance monitor that covered everything and did complete diagnosis they have instances they weren't that concerned worried about. But what we found among actual customers was they were mainly using it and mainly seeing a value in it as an inventory application. The main value they saw was its ability to discover things automatically and to automatically deploy monitors for given instances. So it was rebranded as SQL Inventory Manager it used to be SQL Elements. And most of the new development in that organization or in this product has been along those lines. It still has as you see in this screenshot health recommendations, health check recommendations there are about 20 of those just common things they're not deep dive things but they just give you an awareness of the most important things that you want to maintain awareness of on all of your databases and all your instances and all your applications. This particular product functionality map some of you may not be familiar with IDERA and IDERA products but this is a sort of a map that helps to resolve if you are familiar what products do what's in what area. SQL Inventory Manager covers the areas of discovery, inventory, health checks, and alerting light alerting. SQL Diagnostic Manager is our more of a deep dive performance monitoring product that picks up there and also does light and deep monitoring and also offers enterprise functionality and has deeper functionality in terms of health checks and alerting it has many more alerts and analysis and it has more detailed more configurable alerting capability. Admin Tool Set and DFRAC and SQL Check are other products that cover these areas, discovery and inventory but they are a little deeper but they're not as broad they don't provide admin tools that doesn't do health checks or alerting it doesn't do anything automatically but it's just another tool that extends your capability to do ad hoc analysis and SQL Check is our free light monitoring tool for real time monitoring. Inventory Manager helps you to discover and track your SQL Server inventory you can know what you have and who owns it auto discover any servers that are installed again to avoid that cautionary tale of a rogue server being spun up by someone in an organization that you don't know about it helps you to organize things better by tagging various instances and databases so that you can group them logically and assign those databases to your internal staff to maintain and report on more effectively it helps you can quickly deploy and access for anywhere with a web based and agentless user interface so there's no application that needs to be installed for the various DBAs who are going to be using it and even from the business unit stakeholders who are interested you can also provide access to them to get access through a browser if you would like and also you can get alerts when a server goes down, space is running low some of the basic health checks that I talked about earlier this is the architecture of SQL Inventory Manager go through this real quickly as we talked about it has a web browser that connects to a web application service we have two repositories a core repository and a SQL Inventory Manager repository that are where the data for connection information and for the basic performance information that's being displayed is kept for a short period of time there is a collection service for SQL Inventory Manager that goes through the periodic health checks, discovery processing, alerting it looks at computer availability configuration, SQL server configuration has any configuration changes been made SQL server availability which is independent of the computer availability as well and finally SQL server discovery SQL server discovery uses the browser service we also use WMI TCP scans for across an IP range we scan the windows registry and service control manager and also the SQL server service all of these can be enabled or disabled at will they can also be as I said some people have some a little bit of reluctance to have all those services being used scanning all the time you can choose to use all of them all the time or you can choose certain ones to turn on and you can also turn them on or off periodically just to get a quick snapshot of what you're dealing with on the discovery SQL server instances there's nothing installed there are no agents there are no agents there are no stored procedures we monitor both physical and virtual databases in all cases so the areas of discovery and licensing and health we talked about some of the capabilities that the inventory manager has and these are again these are all things that you're going to need to do whether you have this product or not but the product has been defined and built around the requirements of users so a lot of these needs are being met directly and because they've been built over the years by organizations that have requested them discovering SQL instances this is intelligence services it's something that was added several releases ago helps to manage SQL service for all as we talked about and this is a problem no matter what kind of database you have but we've found that it's a bigger problem with SQL server at least than any of the other enterprise databases like Oracle or Sybase or actually SAP ASE now sorry give it away a little bit there flexible discovery options as I talked about some of the options that you have organizations tagging grouping and organizing instances and they make sense license reporting awareness of the health the health status there's about 20 to 25 different health checks that are run periodically patch analysis and reporting we keep track of help you keep track of what is the latest patch that is available and which has to have been patched and again the web interface SQL inventory manager reports these are a few of the reports that are available there's of course inventory report health check report all this information is available directly in the interface but for interacting with other organizations or for passing giving a periodic check to management some of these reports can be used for that charge back report and SQL server license report to help you understand if you are in compliance all of these all of these reports use SSRS and the we have an idea of report utility to help deploy the reports but they are deployed on SSRS user access views inventory manager is not specifically a security tool but it does this is kind of where it intersects with the security world profile information for roles and users can be discovered and displayed so you keep track of who has access where you can easily see the users of new discovered databases who exactly has this and why are they using it so you can contact them and figure out what's going on you can reach out to users to figure out whether they need access continue to our need continued access or whether they're just if this is just a user that was defined and left alone you can get more information about all the database principles objects roles objects objects roles and users and who who has what permissions review gives information about all of the hosts number the number of sockets number of logical number of cores that are assigned the discapacity on those machines what free space is available what versions they're running and what versions of SQL server they're running and whether they're virtualized or not of course most things are virtualized these days it helps to give you a topology of all the servers virtual machines and instances and the host server information is available for for SQL server licensing licensing updates we talked about licensing and needing to keep track of what what is configured on what is based on the varying changing license model from from SQL server in 2016 Microsoft SQL server or Microsoft changed the model for that in the licensing view we've updated this and there's a table that defines exactly what is how these are signed by SQL server version but we'll try to give you a specific are you compliant or are you not recommendation but we give you all the information in a central place that allows you to decide where what you're using where and whether that complies with the license that you currently have coming in our next version which was SQL inventory manager version 2006 we're going to be adding expanded cloud support that should be available in the next say four weeks or so we're adding discovery of cloud instances and databases for on both Amazon and Azure that includes databases and service support for Azure DB and Azure RDS and we also have health checks available for infrastructures and service for SQL server instances that are actually running on Azure virtual machines or on Amazon EC2 I wanted to kind of give you an overview of all the products that we have if you have interest in any of these areas IDERA covers performance monitoring, security and compliance backup and administration for SQL servers you can see the products here they're categorized diagnostic manager is for performance and monitoring that's the most popular product inventory manager is also a monitor but it's also a discovery tool primarily security and compliance, compliance manager it's secure, our most often sold together compliance, secure is for specifically are you secure have you what health checks are available that can tell you whether you're using best practices to make sure that everything is locked down properly compliance manager is not about security as much as it is about compliance with typical guidelines such as HIPAA or SOX we have templates for each one of these guidelines that you can select and it will help you to determine whether your databases and instances are in compliance with that and backup the main product there is SQL safe backup we also have a job manager admin tool set and comparison tool set so I wanted to sum up by just kind of reviewing some of the areas that are important for you to maintain these are areas that SQL inventory manager in particular has been designed to meet you need to know I think I've demonstrated that there's reasons why you need to know what databases are deployed in your organization both the ones that you're responsible for overtly and the ones that are out there that you haven't discovered yet and you need to have the details for to manage these effectively auto discovery is one of the main features inventory management getting the relevant information about the deployed databases licensing of course this is a comment this is one of the most popular features people really have a tough time keeping their arms around whether they're currently compliant with licensing environment state this is both for basic performance and availability checks but also to check what SQL servers are actually being used and which ones may be able to be decommissioned organization for tagging grouping organizations and then alerting being able to get proactive notification alerts and again this is where inventory manager has about 20 of these our diagnostic manager product has 150-200 with even more recommendations that are provided by the analysis services so with that I will review oops sorry I wanted to close out and see if there's any questions questions here I'm sorry I'm not hearing you very well I heard data governance I didn't hear the rest of that I'm sorry can you hear me better now yes sorry can you speak to how a data governance or data management office would work with IT group in tracking and monitoring databases specifically ownership and response that's a good question I can't speak directly that's not an area that I have direct responsibility for we do have our data governance products our ER studio product and to a lesser degree our compliance manager product address the data governance questions I would just say that whatever you use you need the tool to be aware of what you have and how to report on it so that you can make sure that you are in compliance with whatever are required so you do recommend that maybe an IT department work with a data management absolutely absolutely yeah you have to I am assuming I am assuming that that would happen because otherwise if they're operating independently and totally without knowledge of what the other one is doing you're not you're not you're not governing anything to have any effectiveness you do need to keep communications up so we do have a question about how it compares to redgate sql monitoring redgate sql monitoring redgate redgate yeah I'm aware redgate sql monitor redgate sql monitor sql monitoring well inventory manager sql monitor compares more directly to diagnostic manager so it is a performance monitor and it is not this is designed primarily for inventory management it's designed to the systems that database had previously again several of the customers that we had were previously keeping up with this in spreadsheets it's to keep up with who is assigned to which applications who are the affected parties it does have some light monitoring it is not it is neither intended to be a performance monitor nor does it achieve that again in that map that I showed you can see that it doesn't kind of stop short it focuses more on the basic up down status is it backed up there is some light performance monitoring just to say is something slower is it fast but it inventory manager is not intended as a diagnostic tool so that would be a more of a comparison with diagnostic manager and in that case I say I think redgate is a pretty good tool diagnostic manager has been around for quite quite a long time and a little more mature has more depth of functionality but a redgate single monitor is good too so how many companies or what percent of the company large to medium do you think have this diverse databases that are throughout the company are you asking what percent of your companies have are hidden databases gosh I don't know that's a hard question to answer because if you put out a survey and you say how many of you don't know where your databases are well who's going to admit it I would imagine that a lot of the large enterprise companies anything that is distributed any company that has more than say two offices basically have these kind of we call them rogue servers where somebody somewhere has bought a tool on their own and spun up their own database and is not reporting it back probably because they see it's more efficient they don't have to report through corporate IT I would say anything that's any company that's over say five different locations more than I'm kind of spitballing here but between more 50 employees or so it's likely to be a problem if it's not maintained and not managed I mean in a lot of these large companies they are doing this proactively you either have to have a tool that does it or a person who is periodically doing a survey and making sure there's nothing out there some sort of policy in place exactly and ask these questions that's great to know well we have a very quiet audience today not many questions coming in do you think there's anything else that would recommend for no I would say if you're interested in hearing more I would I would ask you please go to idera.com and you can get trials of sickle inventory manager or or any of the other products that I free trials two week trials of any of the products that I mentioned if you have questions join our communities and submit your questions there those are at community.idera.com if you are interested especially in an inventory manager download a trial try it out and one of our sales managers will contact you and get set up for a demo thanks Scott that was a great presentation thank you for answering all the questions seems like that's it for today just to remind everyone we'll be posting the recorded webinar and slides to datediversity.net in two business days and Shannon will send out a follow-up email to let you know the links and other requested information thank you again Idera for sponsoring today's webinar as always thank you for attending today's webinar and I hope everybody has a great day you have a great day Scott thank you