Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Discover and Manage your SQL Server environment with SQL Inventory Manager


By:   |   Last Updated: 2015-12-16   |   Comments   |   Related Tips: More > Monitoring

Problem

Over the years the licensing model for SQL Server has evolved in many ways. One of the biggest changes was when SQL Server 2012 was released and licensing was switched from physical processor to core-based licensing. Another big shift has been the move to virtual machines and how these servers need to be licensed. Then add the various editions into the mix and the licensing model continues to get more complex. This is why it's important to know what your SQL Server environment contains, so you can ensure you are not over-licensed or under-licensed. With the ease of deploying and decommissioning SQL Server instances your environment is in constant flux, so having a process and system to capture this information is key.

Solution

IDERA offers SQL Inventory Manager which allows you to immediately discover and inventory your SQL Server environment. SQL Inventory Manager also performs health checks on your instances and databases, gives you the ability to create various tags to assist with managing and categorizing your instances, and sends alerts for critical issues that have occurred. SQL Inventory Manager provides the tools you need to discover, manage and report on all of the instances in your SQL Server environment.

The installation only takes a few minutes. The first step is to setup the IDERA Dashboard Repository (if this has not yet been setup already) and the second step is to install the SQL Inventory Manager Repository. Once these are in place you can begin to discover and register your servers. SQL Inventory Manager is completely browser based, so there is no need to deploy and install client applications to your database team.

Registering Servers

Now that we have SQL Inventory Manager installed, we need to identify what SQL Server instances we want to manage. This is often tricky trying to identify all of the installed instances in the environment, but this is one of the areas where SQL Inventory Manager simplifies this process. Adding servers can be done in several ways.

Adding SQL Server Instances to the Inventory

During the installation process, SQL Inventory Manager walks you through adding instances to be managed. You have the ability to manage on-premise, cloud, physical and virtual servers. With the browse option SQL Inventory Manager will discover instances in your environment and you can select the servers you want to include in your inventory. You can also add additional instances at any time.

SQL Inventory Manager Discover

On the information screen, we can add items such as Owner, Location and Tags that will help when exploring and managing the installed base. Each of these items allows us to filter and group servers by these different data elements.

SQL Inventory Manager Instance Info

Another option is the ability to import SQL Server instances into SQL Inventory Manager using a .csv file. If you already have a predefined list of servers, you can use that information to populate your inventory database.

SQL Inventory Manager import instances

Options for Discovery of SQL Server Instances

SQL Inventory Manager provides several ways to discover SQL Servers in your environment. In the Administration section of SQL Inventory Manager there is a Discovery section that provides several different configuration options as shown below.

SQL Inventory Manager Discovery Options

If we select Manage Discovery Options, below we can see the additional ways that SQL Inventory Manager can find instances of SQL Server. You can scan IP ranges, scan domains, check registry settings, use a TCP probe to check specific ports for the presence of SQL Server and more. Instead of having to know the name of the instance or rely on SQL Browser to be running, SQL Inventory Manager provides many methods to discover all installed versions and editions of your SQL Server instances from SQL Server 2000 and onward.

SQL Inventory Manager Discovery Options

Auto Registration of Instances

In addition to auto discovery, SQL Inventory Manager allows you to auto register instances. This allows SQL Inventory Manager to find the instances during its ongoing discovery and then automatically register the instances without you having to take manual action. This way your inventory is always up to date with minimal effort.

If you want to limit the auto registration of servers, SQL Inventory Manager allows you exclude instances based on the edition of SQL Server. As you can see below it provides options for all of the current editions of SQL Server. By default all editions are auto registered unless specified otherwise.

SQL Inventory Manager Auto Registration

Custom Inventory Fields

In the Administration section of SQL Inventory Manager there is a Custom Inventory Fields section that let's you add your own custom fields to be added to your inventory database.

SQL Inventory Manager Custom Fields

Custom fields can be added that are applied to the Server level, Instance level or Database level. Below are two custom fields that were added at the Database level.

SQL Inventory Manager Custom Fields

SQL Inventory Manager Navigation

Once data has been collected, the dashboard provides the ability to see health check alerts, a listing of all instances in your environment and the ability to explore all SQL Server instances.

Overview

Now that we have are instances discovered and registered in SQL Inventory Manager we can see an overview of our environment. We can quickly see if there are any health check recommendations. We can also see the number of servers that are up or down as well as the total number of servers, instances and databases that SQL Inventory Manager is monitoring.

SQL Server Inventory Manager Dashboard

Explorer

The Explorer view provides the ability to see your instances and databases in many different ways as well as allowing filtering options, so you can drill down into specific areas. This is where Tags are key, so you can segment your instances into several different categories.

SQL Inventory Manager explorer

Instances

The component that will be of most help with SQL Server licensing is the instances screen. Here we can get a list of all instances, versions, editions and some additional information related to the instances. The filtering options also allow you to slice and dice the data to get better insight on installed instances based on Tags, Version, etc.

SQL Inventory Manager Instances

If we drill down into one of the instances we can see all the details for that particular instance of SQL Server as shown below.

SQL Inventory Manager Instance Details

We can also drill deeper and get details such as SQL Server configuration settings and computer settings as shown below. SQL Inventory Manager automatically collects and updates all of this information, so we don't have to manually collect data from every instance.

SQL Inventory Manager Instance Computer Details
SQL Inventory Manager Health Checks

SQL Inventory Manager provides out of the box health checks that are performed on your instances and databases to ensure there are no issues. By default these are all enabled, but can be disabled as needed.

Here are some of the issues that were found and the recommendations that SQL Inventory Manager provides for the monitored instances in my environment. As you can see, SQL Inventory Manager also offers recommendations on how to resolve the issue.

SQL Inventory Manager health check issues

Here is a closer look at all of the SQL Inventory Manager health checks and the purpose each health check.

Database at risk (Data) - Checks to make sure the data file is not running out of space. If the data file runs out of space this will cause database activity to halt.

Database at risk (Log) - Checks to make sure the log file is not running out of space. If the data file runs out of space this will cause database activity to halt.

Database backup is not current - By not having a current backup you are at risk of losing data changes. This is an issue if your database becomes corrupt or data is accidently deleted from the database.

Database consistency has never been checked - This checks for any corruption in the database. Without running these checks you are unable to know if your database has issues which means that some or all data could become inaccessible within the database if there is corruption which could lead to data loss.

Database consistency has not checked recently - Database consistency checks should be done on a periodic basis to make sure the database is not corrupt. This check will ensure that you are being proactive and running periodic integrity checks.

Database does not have a backup - This checks to see if the database was ever backed up. Without a backup you won't be able to recover from a failure due to corruption or unattended database changes.

Database has autoshrink enabled - The autoshrink setting will try to regain unused space in database files. The reason this is not a good idea is that it could create fragmentation issues within the database files.

Database is not in a usable state - There are several database states a database can have. This alerts you if there are database that are not accessible.

Limited Permissions - This is a warning message from SQL Inventory Manager letting you know that the account does not have the needed permissions to access data from an instance which means SQL Inventory Manager cannot correctly report the status.

Optimize for adhoc workloads is disabled - This setting allows SQL Server to optimize single use queries. You can read more about this setting here.

SQL Server is down - This tells you that SQL Inventory Manager cannot communicate with the instance and that it might not be running.

SQL Server is slow - This tells if the response back from the server to SQL Inventory Manager is slow and therefore there may be a performance issues occurring.

Tempdb inconsistent file sizes - If you have multiple tempdb data files setup the initial size for each file needs to be the same, otherwise the files do not grow at the same rate and you will not get the benefit of having multiple data files for tempdb. The tempdb data files should be the same size at all times, so SQL Server will make equal use of each file. Having unequally sized files can negate the benefit of having multiple data files for tempdb.

Unable to monitor SQL Server instance - This alert lets you know that SQL Inventory Manager cannot monitor the instance for some reason.

Unable to monitor with WMI - This lets you know that WMI information is not accessible to SQL Inventory Manager for an instance.

Volume at risk - This checks overall disk space to let you know if a drive volume is running out of space. If you run out of space you database activity will stop.

xp_cmdshell enabled - This alert tells you if xp_cmdshell is enabled. This extended stored procedure lets you access programs outside of SQL Server and is seen as a security risk.

You can see there are several health checks that SQL Inventory Manager performs on a periodic basis to inform you of potential issues. Whether you have monitoring tools in place or not, these health checks should be welcomed additions to your overall database administration best practices.

Recommendation

As mentioned, the licensing model for SQL Server continues to get more complex as well as more expensive, so understanding what you need to license and how to license SQL Server is an important step to keeping your costs in check. With the ability to scan and find SQL Server instances both running and not running, SQL Inventory Manager should be part of your arsenal to better manage your entire SQL Server environment. With the additional features such as the health checks and alerting SQL Inventory Manager should be a tool that all DBAs employ.

Next Steps

MSSQLTips.com Product Spotlight sponsored by IDERA makers of SQL Inventory Manager.



Last Updated: 2015-12-16


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools