Adhoc workloads

The option for optimizing adhoc workloads helps to improve the efficiency of the plan cache for workloads that contain many single user adhoc batches. 

Auto shrink

Microsoft SQL Server automatically shrinks databases to remove unused space. In some cases shrinking databases may not be the best option for your environment since it may cause fragmentation.

SQL Inventory Manager provides a Health Check to let you know if the Auto Shrink option is enabled or disabled.

Availability data

Availability Data refers to the instance status (up or down), instance response time, and database status (normal, suspect, offline, inaccessible or emergency mode). 

Capacity data

Capacity data refers to the storage capacity of your drives and databases in your environment. SQL Inventory Manager lets you know if your drives or databases are at risk of being full soon.

CHECKDB

Checks the logical and physical integrity of all the objects in the specified database. If corruption has occurred for any reason, the DBCC CHECKDB command will find it, and tell you exactly where the problem is.

Computer

Refers to the server that hosts a SQL Server, this maybe a physical or virtual server. SQL Inventory Manager gathers configuration, capacity, and performance data for SQL Server instance host servers, also known as "Servers".

Configuration data

SQL Inventory Manager performs the following configuration data checks:

  • Auto Shrink configuration
  • Xp_cmdshell configuration
  • Tempdb files configuration

Database

A database is a data structure that stores organized information. Most databases contain multiple tables, which may each include several different fields. For example, a company database may include tables for products, employees, and financial records. Each of these tables would have different fields that are relevant to the information stored in the table.

Disaster recovery

Disaster recovery is a process to help you recover information if disaster occurs. Disaster recovery planning refers to the preparation that must occur in response to a disaster. SQL Inventory Manager provides Health Checks that give you information of your latest backups, alerting you if any of your databases have not been backed up recently.

Health checks

Health checks include monitoring CPU utilization, database status, available disk space, database backup status, and other performance issues in general.

The purpose of performing Health Checks is to determine if settings are appropriate for your particular environment and if they allow you to have the highest performance and availability from your databases.

Instance

Also known as SQL Server or SQL server instance. SQL Server supports multiple “instances” or installations on the same host computer. Each instance runs independently from all others and has its own set of installation code, configuration parameters, system and user databases, memory allocation, and security configuration. 

Location

The location refers to the physical or geographical location of an instance, such as Houston for example. SQL Inventory Manager allows you to add this information to the instance that you are registering so you can later filter and get details or graph views for the instances belonging to this location. 

Owner

The owner refers to the user whom the instance belongs to. When you register an instance, Idera recommends that you register the instance owner, so later you can easily get information from this owner and his/her respective instances. 

Performance data

SQL Inventory Manager measures performance data such as CPU% usage, Memory % usage, Disk IO, and Network IO.

Service Control Manager SCM

SCM is a remote procedure call (RPC) server that allows the service configuration and service control programs to manipulate services on remote machines. The service control manager (SCM) is started when the system boots. One of the tasks performed by the SCM is enumerating installed services and driver services.

SQL Server Resolution Service

This method is used to direct client requests to the proper port when multiple instances of the SQL Server are running on the same system. When multiple instances of Microsoft SQL Server are installed, the default instance uses the standard and commonly associated TCP port of 1433. Once this port has been taken, the other installed instances of SQL Server must use an alternate, arbitrary port. This SQL Server Resolution Service instance checks UDP port 1434 to determine if any other instances of the Resolution Service are listening on this port; if there are no conflicting instances (which is the case if it is the first instance),then it binds to that port and becomes the SQL Server Resolution Service listener for all SQL Server instances on the server.

Tag

Tags help you group your instances for better management. For example, use a tag name such as "Business critical" to identify all your registered instances that are business critical to you, and have quick access to views and details from this group of instances. 

A single instance can belong to one or more tags.

TCP Probe

TCP Probe is a module that attempts to connect to a TCP/IP address on specified ports and starts the SQL Server connection negotiation packet exchange.  If the remote system responds to the initial packet correctly it records the address and port.

Tempdb files

It is important to configure the tempdb files with the same initial size and with the same growth settings so that Microsoft SQL Server can write the data across the files as evenly as possible. The data files for tempdb should all be the same size and large enough to handle workload for 24 hours. This configuration helps SQL Server distribute the work evenly across the tempdb data files. 

SQL Inventory Manager provides a Health Check to let you know if tempdb files are the same size or not.

Windows Registry

The Windows Registry is a hierarchical database that stores configuration settings and options on Microsoft Windows operating systems. It contains settings for low-level operating system components and for applications running on the platform that have opted to use the registry. The kernel, device drivers, services, SAM, user interface, and third party applications can all make use of the registry. The registry also provides a means to access counters for profiling system performance.

WMI Enumeration

WMI allows to use a simple, consistent mechanism to query for information or configure settings on computers or enumerate objects on computers across an enterprise.

Xp_cmdshell 

Xp_cmdshell is essentially a mechanism to execute arbitrary calls into the system using either the SQL Server context (i.e. the Windows account used to start the service) or a proxy account that can be configured to execute xp_cmdshell using different credentials. 

When xp_cmdshell is enabled, the user could escalate his/her privileges to sysadmin. It is important to be aware which instances have the xp_cmdshell option enabled for security reasons.

SQL Inventory Manager provides a Health Check to let you know if the xp_cmdshell is enabled or disabled in an instance. 

 

Need more help? Search the Idera Customer Support Portal.

  • No labels