Databases need more than performance monitoring

There is more to databases than performance monitoring. So why are the most popular DBA tools, performance monitoring tools? They don’t even begin to cover the vast majority of DBA responsibilities. What we need is environment monitoring.

Image: performance monitoring pressure gauge
You’re gonna need more info

When you read a job spec for “database administrator”, it does not simply say:

  • Performance Monitoring

It says something like:

  • Database Performance Tuning
  • Database Security
  • Promoting Process Improvement
  • Problem Solving
  • Presenting Technical Information
  • Database Management
  • Data Maintenance
  • Operating Systems

(List taken in part from Hiring.Monster.com.)

So why are the most popular DBA tools, performance monitoring tools? Those are great, sure, but they don’t even begin to cover the vast majority of DBA responsibilities. What we need is environment monitoring.

Environment Monitoring

I don’t have a website I can link to, to give you a definition of SQL environment monitoring. That’s because we’ve been defining it ourselves, for the past nine years.

An environment monitor is a system that allows administrators to examine the overall and specific health of database instances.

An environment monitor should touch on performance, yes. It should also:

  • Manage and monitor security
  • Speed audits
  • Make a majority of common DBA tasks effortless
  • Collect and present as much system information as possible, including service packs, disk space, errors, and more
  • And more
  • And also, lots more

Minion Enterprise is far more than glorified maintenance

This is exactly what we built Minion Enterprise for: to monitor and manage the environment. To take away the Server-By-Agonizing-Server aspect of administration by introducing the “set based enterprise” approach. To automate everything that can be automated, and to make data available to the DBA on everything else.

Get a trial and a demo, and you’ll see exactly what we mean. Monitor your environment, not just your performance.

Five minutes to freedom: Installing Minion Enterprise

Get your repo server, your Minion Enterprise download, and your license key (trial or permanent). Install and config take just five minutes, and your entire enterprise is in order.

Let’s take five minutes and get our entire enterprise in order.

Get your repo server, your Minion Enterprise download, and your license key (trial or permanent). Install and config take just five minutes.

02-Install1. Install

Extract the MinionEnterprise2.2Setup.exe and run it on your repository server.  Give the installation “localhost” for the instance name.

2. Install the license key

Once you receive your license key – trial or permanent – from MinionWare:

  1. Copy License.txt to C:\MinionByMidnightDBA\Collector on the repository server.
  2. Rename the file to License.dll
  3. From Powershell, run the command:
    C:\MinionByMidnightDBA\Collector\License.exe Install

3. Configure email for alerts

Connect to the repo server and insert your alert email:

4. Configure servers

Insert (or bulk insert) server to the repo:

5. You’re done!

Jobs will begin kicking off to collect data within the next hour. Some jobs run hourly, some run daily or weekly or monthly. You’ll start getting tables full of useful data, and email alerts that actually mean something.

If you’re impatient to start getting some of the good stuff right away, kick off the CollectorServerInfo% jobs manually. These populate data in the dbo.Servers table, which other jobs need to run. You’ll start noticing data in the Collector.DBProperties, Collector.ServiceProperties, and Collector.DriveSpace tables first, as these jobs run most frequently.

While ME is taking care of your shop for you, you can get to know it better with some of our better resources:

Download our free maintenance modules:

  • Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
  • Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
  • Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.

Minion Enterprise Trial Newsletter

This is the 13 part newsletter series for users of Minion Enterprise, the SQL Server management solution.

1 - Index Stats 

In this introductory newsletter:

  • Pointing out what “Quick Start” sections to walk through to complete your ME setup
  • Introducing the Collector schema, which holds all the goodies
  • Your first module: Index Stats, which allows you to research index issues across the whole enterprise

 

2 - Database Files and Script Schemas

  • The Database Files Module, which you can use for tracking and projecting file growth over time, among other things
  • The Script Schemas Module, which will save you at some point, from objects dropped or modified or somehow lost

 

3 - Logins module and Clone Users utility

  • The Logins module, which is the basis for a lot of amazing security functionality
  • The Clone Users utility, which makes account creation and management effortless

 

4 - Schemas in Minion Enterprise, and the Drive Space module

  • Minion Enterprise Schemas, which (as we explain) logically group ME functionality
  • The Drive Space Module, which allows you to deeply customize “drive space is running out” alerts

 

5 - Service Properties

  • The Service Properties Module, which is super cool. It gathers information on SQL services on all your servers! Service down alert, anyone?

 

6 - Instance Config

  • The Instance Config Module, which collects and manages sp_configure settings centrally

 

7 - Error Log Search

  • The Error Log Search Module, which lets you set up error log search terms and find them wherever they occur in your SQL environment

 

8 - Wait Statistics and FAQ

  • The Wait Statistics Module, which gathers wait stats info for your research and alerting purposes
  • Frequently Asked Questions, which we answer

 

9 - Databases, and Custom Objects

  • The Database Module, and along with that the New/Retired Database alert
  • Custom Objects, and whether you should create them in ME (spoiler answer: Heck yes.)

 

10 - Database Properties, and “To Autogrow or Not?”

  • The Database Properties Module, which gets a ton of information, including the date of the last full, log, or differential backup
  • To Autogrow or Not?, which is a blog of Sean’s discussing autogrow, especially in the context of Minion Backup and Minion Enterprise

 

11 - Objects, Tables, and Columns (and top 20 features!)

  • The Objects, Tables, and Columns modules, which are three separate but similar modules that collect data on…can you guess what?
  • Top 20 Features, which is a pretty subjective, but pretty good, list

 

12 - AD Group Members and Login Access Method Audit utility

  • The AD Group Members Module, with which you can audit your servers, see who has access where, and on and on
  • Utility: Login Access Method Audit, which shows you how users are gaining access to SQL, and whether they are getting in through multiple methods

 

13 - Last week!! Work like a DBA

This last newsletter explains why “Work like a DBA” is the ME motto, and introduces you to the idea of the set-based enterprise.

 

Write to us at MinionWareSales@MidnightDBA.com for a demo and a quote. Write to us at https://minionware.desk.com/ for help and suggestions. Write to us wherever you can find us!

ME Trial 13 – Last one!! Work like a DBA.

minion enterprise-01

A quick glance behind, and ahead

It’s the final email of yourMinion Enterprise trial information series!

So last time, we introduced the AD Group Members module, and the Login Access Method Audit utility.

This time we’re actually not going to look at a specific module. Today, we’re just going to talk a bit.

“Work like a DBA”

We’ve chosen “Work like a DBA” as the main slogan for Minion Enterprise…though believe me, we still come up with new slogans on a regular basis, from the sublime to the ridiculous. So why “Work like a DBA”?

First: code. Database administrators, by and large, work in code – not in a GUI. We know that code is something you can save, test, streamline, automate, repeat. You can show the boss the code you ran, thus proving it wasn’t you who dropped that table, whereas Pat over there can’t prove the same, because Pat uses the GUI. Even more, the GUI is slow. (“Click, click, click, click, click, click, click…sigh…click, click, click…”) It’s non-saveable. It’s non-testable, non-streamlineable…you get the idea. So to work like a DBA, we think, is to work in the code.

Second: SBAS. DBAs also understand the idea of RBAR: Row By Agonizing Row. Here at MinionWare, we’ve expanded that idea to SBAS: Server By Agonizing Server. Sure, Microsoft has attempted to ease the headache of managing servers one-by-one-by-one, but they haven’t done a really comprehensive job. With ME, you can standardize backups and maintenance from a central location (using Minion Backup and Minion Reindex – and soon, Minion CheckDB). You can standardize sp_configure settings. You can audit and clone logins. And, so much more.  MinionWare has created the set-based enterprise, because being a DBA should not mean “hours and hours of busy work, server by agonizing server”.

Third: Know thy system. Minion Enterprise collects data using SQL Server jobs. It runs SQL stored procedures to accomplish most tasks. It provides SQL Server Reporting Services reports. Data is stored in tables, not flat files. We have the unique opportunity to provide a force multiplier to technologists, using the exact technology that they already work with. Mange SQL with SQL. Why not?

That last point means that all the mechanisms that the system uses are already familiar to you. It means that the data you need isn’t hidden behind some .NET front end; it’s all there, waiting for you to query it any which way you want. Need an alert based on log file sizes? Go for it. Want to search all your error logs for a particular trace flag? Sure. Almost anything you can think of, you can do, because the data is there.

So, go wild. Minion Enterprise makes your enterprise into your enterprise, whether you have 2 instances or 20,000. And if you run into a snag, seriously: write us. We simply love hearing about how people are using the product, what else you want in it, what wild edge cases you run into.

And, you know. Work like a DBA.

Write to us at MinionWareSales@MidnightDBA.com for a demo and a quote. Write to us at https://minionware.desk.com/ for help and suggestions. Write to us wherever you can find us!

ME Trial 12 – AD Group Members and Login Access Method Audit utility

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Objects, Tables, and Columns modules. This time we’ll look at the AD Group Members module, and the related Login Access Method Audit utility. This is one collection that we are super, totally, and completely in love with.


The AD Group Members Module

It is very common to have Windows groups as logins in SQL Server. The problem for DBAs is that they then have no idea who has access, because you can’t see who is a member of which Active Directory groups.  Now you can, and quite easily. Minion Enterprise pulls in all of the relevant AD information to repository tables, and also provides you with a set of stored procedures for analyzing and reporting on the data.

What can we do with this? First, of course, we can list out every individual user that has access to SQL Server, whether they have direct access, access through an AD group, and/or access via nested AD groups. You can use this data to audit your servers – for example, getting a list, or even a simple count, of sysadmins on each server. (One client found to their surprise that they had over 500 sysadmins on a single server!)  And of course, anything else that you can think of. The data is all there, waiting for your query.

Objects in the AD Group Members Module

Tables

  • Collector.ADGroupMember – Holds collected information about AD group membership from each managed server.

Views

  • Collector.ADGroupMemberCurrent – Holds the most recent collection of AD group membership.
  • Collector.ADGroupMemberPrevious  – Holds the next-to-most recent collection of AD group membership.

Notable Stored Procedures

  • Report.ADAcctsInSQLAll – This procedure gets an expanded list of Active Directory groups for all servers. Minion Enterprise comes with a utility script that allows you to save the results of this SP to a temporary table, and thereby limit the result set by any criteria (e.g., by sysadmins): \ManualScripts\Logins\Report.ADAcctsInSQLAll.sql
  • Report.ADAcctsInSQLByApp – Gets an expanded list of Active Directory groups, filtered by a specific AppID  (as defined in dbo.Application).
  • Report.ADAcctsInSQLByEnviro – Gets an expanded list of Active Directory groups for a specific environment (as defined in dbo.ApplicationEnvironment).
  • Report.ADAcctsInSQLByID – Gets an expanded list of Active Directory groups for a specific Server by InstanceID.
  • Report.ADAcctsInSQLByServerName – Gets an expanded list of Active Directory groups for a specific Server by ServerName.
  • Report.ADAcctsInSQLBySLA – Gets an expanded list of Active Directory groups for a specific SLA (e.g., Gold).

Check out the Active Directory Group Expansion video on our YouTube channel, which demonstrates finding what users are in which Windows group. And, see Passing Your Security Audits for quite a bit more!

Utility: Login Access Method Audit

This utility is an auditing feature that shows you how users are gaining access to SQL, and whether they are getting in through multiple methods.  This is an extremely powerful module; only Minion Enterprise brings you this type of functionality.

SQL logins can be AD accounts, SQL accounts, or AD groups.  When a login is an AD group, there is no way for the DBA to know who is in that group.  This is why Minion Enterprise takes a look at the AD groups that are registered as logins on each managed SQL instance, and queries Active Directory for all of the members of those groups.

This is data is saved to the Collector.ADGroupMember table.  Look at this table, and you may notice that there is no InstanceID.  This is because Active Directory group members themselves have nothing to do with SQL. An AD group can be a login on multiple SQL instance, so saving the AD data independent of any server information is the way to go.  To match AD accounts with logins on a specific box, join Collecgtor.ADGroupMember with Collector.Logins – or let the following procedure do it for you.

Procedures:

  • Audit.LoginPermAccessMethod – This stored procedure shows the path by which an account obtains access to SQL Server.  This procedure can be called with or without an account, and with or without a server name.

 

NOTE: There is one limitation to the AD data. If a user has an Active Directory account with a completely different name from their SQL login, there is simply no way ME can know those two accounts are the same person.  They are independent, unrelated accounts.  So if you call the audit procedure above with a user account in mind, and that account doesn’t match any other accounts, you won’t see it in the results.

As always, feel free to write your own queries to find what you need. Minion Enterprise is meant to be customized.

This utility depends on “current” views from two collections:

  • Collector.LoginsCurrent
  • Collector.ADGroupMemberCurrent

 

NOTE: If for whatever reason the Logins or AD Groups data is not up to date, run the jobs associated with these collections:

  • CollectorLoginsGet-GOLD (and/or the Silver or Bronze job, as appropriate)
  • CollectorADGroupsGet

AD Group Members is a powerful module – especially when you count in the Login Access Method Audit utility – and it’s already doing a lot of shops quite a lot of good. Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

The next article is our very last one in this series!

 

ME Trial 11 – Objects, Tables, and Columns (and top 20 features!)

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Database Properties module, and had an op-ed on autogrow.

This time we’ll look at the Objects, Tables, and Columns modules. These are three separate modules – with their own jobs, tables, and so on – but they’re similar enough to talk about together. Our newsletter, our rules, right?

We’ll also quickly cover the top 20 features in Minion Enterprise.


The Objects, Tables, and Columns modules

The Objects module collects sys.objects data from each database.  Get the most recent collection from the Collector.SysObjectsCurrent view. (Note: The Objects Module does not collect data on system tables.)

The Tables module gathers data space used, index space used, rowcounts, and much more for every table, in every database, in every managed instance. For the most recent collection of table data, query Collector.TablePropertiesCurrent.

The Columns module collects data on table columns, including all of the type information, nullability, whether the column is computed or sparse, and so on.  (Note: By default, Minion Enterprise only logs column data for Gold level servers.)

What can we do with this?  The collection for SQL objects is extremely useful for determining when objects come and go from databases, and to investigate when an object might be missing from certain servers.

You can use collected table data to track specific table growth over time, see when the table was last modified, report on file groups, search for triggers, find replicated tables, and so on.

Use table column data to, for example, detect changes to tables over time, compare the data types for similarly named columns across your enterprise, or search for common issues (such as VARCHAR(1), or deprecated data types).

Objects in these modules

Tables

  • Collector.SysObjects – Stores the collections of database objects data.
  • Collector.TableProperties– Stores the collections of table data.
  • Collector.TableColumns – Stores the collections of column data.

Views

Each of the tables above have a %Current and a %Previous view, for the most current collection, and the next-to-most current collection.

Top 20 Features

Minion Enterprise (or just, “ME”) by MinionWare, LLC is an enterprise management solution for SQL Server.  ME gives a database administrator an unprecedented amount of power over the enterprise, through extensive data collection, smart alerting, and the means to easily configure important settings across multiple instances. The DBA stands at the center of a wealth of information, with the levers to configuration close at hand.

Once installed and configured with a server list, ME begins to collect data about each server right away, and begins alerting as needed. Changes in settings, database lists, stopped and started services, and more are automatically pulled into the Collector tables.

Furthermore, Minion Enterprise allows you to configure settings on the local repository, and then it automatically pushes those changes out to the appropriate instances. Run a single UPDATE statement to set file growth rates for a single instance – or across the enterprise – or to enable the sp_configure option ‘optimize for ad hoc workloads’.

For a one page PDF of Minion Enterprise highlights, visit www.MinionWare.net/Enterprise.

Twenty of the very best features of Minion Enterprise are, in a brief:

  1. Fast, simple setup and configuration – We have designed Minion Enterprise to set up as quickly as possible.  You’re up and running in less than ten minutes: collecting data, alerting, and scripting out schemas automatically.
  2. Central Server List – Keep a central list of SQL Server servers, and which applications they belong to. This central list is beneficial for tracking your enterprise, auditing, and onboarding new staff.
  3. Central Configuration – Centrally configure and manage SQL Server within Minion Enterprise. Make your changes inside Minion, and it then pushes your changes to all of the managed servers. ME even provides you the option of enforcing server-level (sp_configure) configuration values. If someone makes a change to the servers, ME will change it back to conform to policy.
  4. Windows Group Expansion – See exactly who has what permissions via all Windows groups, even when users are nested several levels deep in Active Directory.  ME makes it easy to display AD users and permission inheritance across one or more servers; and to research SQL Server access for just one user, across multiple servers.
  5. Service Down Alerting – Alert on SQL Server services that have stopped, across the whole enterprise.
  6. Disk Space Tracking and Alerting – Report on the disk space usage across your enterprise, and define space alert thresholds for specific disks.
  7. Routine Database Object Scripting – Script out all database objects regularly in order to retrieve schema changes. For example, if someone makes a change to a stored procedure on the wrong server, you have a record of the script before the change.
  8. Consolidated Alerts – Centralized, consolidated alerts for all servers. This prevents “alert storming” support personnel, so your alerts are truly meaningful again.
  9. Replication Tracking – Track replication latency, and alert when it passes your custom threshold.
  10. Track Table Sizes –  Query for data space used, index space used, and rowcounts for every table, in every database, in every managed instance.
  11. Central Index Research – ME collects index information from all managed servers. So, it’s easy to see which databases have, for example, the most clustered GUIDs, the most impactful missing indexes, and so on.
  12. Search Error Logs – Set up ongoing search terms for SQL Server and SQL Agent error logs, across the entire enterprise.
  13. Low Footprint – Minion Enterprise is a management system with central configuration, data collection, reporting, and alerting. It is not a traditional monitoring solution, and has very light resource overhead. Even better: ME drops absolutely zero objects on managed servers!
  14. Logical Server Grouping – Group servers by applications, and by service level (for example, Gold, Silver, and Bronze). This allows you to perform actions against only one application’s servers, against only that application’s production servers, against only Gold level servers, and so on.
  15. Backups Alerting – Minion Enterprise alerts on missed backups, even when a backup job didn’t run. For example, if SQL Server Agent is down on a managed instance, ME will still alert on missed backups.
  16. Alert on Database Changes – Query and alert on any database property change.
  17. Database Create/Drop History – Retain history of database creation and deletion.
  18. Centralized SID Server – Keep a central list of SQL Server logins, and their assigned ID number (“SID”). Standardizing login SIDs across your enterprise prevents the common orphaned users issue.
  19. Integrates with free modules – Minion Enterprise integrates very easily with the free MinionWare modules: Minion Backup, Minion Reindex, and Minion CheckDB (coming early 2016).
  20. Data Archiving – Minion Enterprise includes a process to archive out collector data, and any other data that grows over time.

Objects, Tables, and Columns – this is a prime example of our philosophy, “Log everything, report on anything”.  Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

You only have a few days left of your Minion Enterprise 30 day trial!
Write to us today at
MinionWareSales@MidnightDBA.com for a demo and a quote.

Next time we’ll talk about AD Group Members.

ME Trial 10 – Database Properties, and “To Autogrow or Not?”

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Databases module, and whether you should write your own custom objects.

This time we’ll look at the Database Properties module, and talk a bit about autogrow.

The Database Properties Module

For each database, the Database Properties module collects a significant amount of data, including:

  • the database owner
  • date of the last full, log, or diff backup
  • which databases have auto-shrink enabled
  • database collations
  • case sensitivity
  • database size on disk
  • and, more

 

What can we do with this? Audit your database owners (exactly how many databases are owned by our ex-DBA, anyway?)  Alert on missing backups. Track database size on disk (or in use, or index size, etc.) over time. Find ALL of the autoshrink enabled DBs across your enterprise. Determine which databases are on a nonstandard collation. Check recovery model on all Gold servers. See which databases are offline, or suspect. And so on, and on, and on.

Objects in the Database Properties Module

Tables

  • Collector.DBProperties– Stores the collections of database property data.

Views

  • Collector.DBPropertiesCurrent – Provides the most recent collection of database property data.
  • Collector.DBPropertiesPrevious – Provides the next-to-most recent collection of database property data.

To Autogrow or Not?

Note: This is a repost of the introduction to one of Sean’s blogs that’s still applicable. We’ve updated it with a note or two on how Minion Backup  and Minion Enterprise can help.

I just got this question in the user group and thought I’d write a blog instead of just answering a sub-set of users who could benefit from it.  The question was:

I have customized the values of the Auto growth according to the size of the database and the rate at which it grows. I have noticed that Auto growth kicks in about every 3 months – 6 months on an average. Is that OK? I have read articles where the advice on it ranges from “Auto growth is OK” to “Auto growth should kick in only during emergency”.

This is one of those topics that comes up again and again, unlike AutoShrink which I hope is settled by now.  I suspect it keeps coming up because there’s no real solid answer.

Ok, so whether or not to AutoGrow your files.  I’m going to talk about both data and log files together unless there’s a difference.  So unless I call one out over the other, I’m talking about them both.

Yes. And, no.

You should definitely use AutoGrow.  And you should definitely NOT use AutoGrow.  That’s my way of getting around saying “it depends”.

It depends on a few factors really.

  1. What you’re going to do with the files.
  2. How big your environment is.
  3. How many other files are on the drive.
  4. How much activity is on the files.
  5. Monitoring method

Maybe there’s more, but that’s all I can think of right this second, but you get the idea.  Ok, so let’s go through them one at a time….

You can read the rest of this article here: http://www.midnightsql.com/tech-corner-to-autogrow-or-not/


And there we have Database Properties, and an op-ed on autogrow. Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

Next time we’ll talk about Objects, Tables, and Columns, and the top 20 features in Minion Enterprise

ME Trial 9 – Databases, and Custom Objects

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Wait Statistics module.

This time we’ll look at the Databases module, and talk about writing your own custom objects.


The Database Module

The Database module collects database information from managed servers. This gives you a record of all databases – and their database IDs, and the database size on disk – for the entire enterprise, and allows you to track databases as they come and go, grow, shrink, and change names.

What can we do with this? The biggest thing that the database module gives you is the new/retired database alert, which tells you when databases come and go. And, you have a history of database CREATE / DROP activity over time.

Objects in the Database Module

Tables

  • Collector.Databases – Stores the collections of database data.

Views

  • Collector.DatabasesCurrent – Provides the most recent collection of database data.
  • Collector.DatabasesPrevious – Provides the next-to-most recent collection of database data.

Stored Procedures

  • Alert.DbsNewRetired –This procedure alerts and reports on databases that have appeared or disappeared (as compred to the previous collection). This is scheduled by default for Gold and Silver servers.
  • Report.DBNamesLatestGet – Returns a list of all database names from the current collection, for a given InstanceID.

New/Retired Databases Alert

The Databases module provides for a unique bit of functionality: an alert on new databases, and on retired (deleted) databases.

The stored procedure (Alert.DBsNewRetired) that comprises the alert, maintains a master database list in dbo.DatabaseList.  This table does nothing but hold the InstanceID and DBName.  The alert compares the last run of the Databases collection with the dbo.DatabaseList table.  It then sends a report based on its findings.

IMPORTANT: The first run of this alert may be quite large, because there are no rows in the DatabaseList table.  So this alert should insert all of those rows for you.  From then on, it will maintain the list and any alerts you receive will be much more reasonable.

There is nothing for you to do when you get this alert.  It is merely informational so you’ll know when databases come and go from your systems.


Custom Objects

MinionWare encourages you to create stored procedures and views as you find you need them – not just for Database module information, but for any information collected in the repository. ME is meant to be customized. Even with the huge amount of time and experience we have dedicated to creating this management software, there is no way to anticipate every alert or query your organization will need. Feel free to create your own alerts, reports, views, and so on. You can even create your own collections to merge with the data we collect for you to fully customize your environment.

We further recommend you create your own schemas to organize these custom objects. For example, if your company name is ABC, you might create the schemas ABCAlert and ABCReport.

As long as you do not modify existing Minion Enterprise objects, there should be no ill effects. And, Minion Enterprise upgrades will not remove or modify your custom objects and schemas.


And now you know about the Database module, and whether you can write custom objects for ME (answer: yes).  Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

You only have about 10-12 days left of your Minion Enterprise 30 day trial. Write to us today at MinionWareSales@MidnightDBA.com for a demo and a quote!

Next time we’ll talk about Database Properties.

ME Trial 8 – Wait Statistics and FAQ

minion enterprise-01

A quick glance behind, and ahead

Last time, we introduced the Error Log Search module.

This time we’ll look at the Wait Statistics module, and cover some frequently asked questions about Minion Enterprise.


The Wait Statistics Module

The Index Stats module collects detailed index information into the Minion Enterprise repository. It gets all data from sys.indexes on your managed servers; plus, the list of indexed columns (and included columns); and more. This allows you to perform detailed, enterprise-wide index analysis.

The Wait Statistics module collects key wait statistics from each managed server, saving them to the Collector.WaitStats table.

The Collector.WaitStatsCurrent view shows the latest collection of wait stats data (from sys.dm_os_wait_stats), including the percentage of the WaitType for each collection period.

What can we do with this? Use the PercenResourceWaitTime column in Collector.WaitStats (or the Collector.WaitStatsCurrent view) to see the percentage of the WaitType for each collection period.  You can use this column as a basis for your custom alerts.  If a performance condition arises, a specific waitType is likely to increase in percentage from one collection to the next, so look for those increases.

Objects in the Wait Statistics Module

Tables

  • Collector.WaitStats – Holds the collected wait stats data pulled back from each server.

Views

  • Collector.WaitStatsCurrent – Provides the most recent collection of wait statistics results.
  • Collector.WaitStatsPrevious – Provides the next-to-most recent collection of wait statistics results.

ME Frequently Asked Questions

How is Minion Enterprise licensed?

Unlike other repository products, the ME repository is free. Licensing is only for each managed instance of SQL Server or for each Windows server you manage.

Why doesn’t ME have a GUI?

In short, GUIs slow you down. ME was specifically designed to be as fast as possible and to help you manage your entire environment. And you simply can’t do stuff like that in a GUI. Developing a GUI for ME would greatly slow you down.

What’s required to install ME on each managed server?

Nothing at all. Minion Enterprise does not install any objects on managed servers.

What is the benefit of managing a Windows server that doesn’t have SQL Server installed?

The short answer is: operating system data, drive space, drive shares, and service properties.

For Windows servers, Minion Enterprise collects operating system data in the Collector.ServersOSDetail table. This table includes some really useful data, including:

  • Windows version
  • Install date
  • Last boot up date
  • Free physical memory
  • Free space in paging files
  • Total virtual memory size
  • OS architecture
  • Serial number
  • System directory

Drive Space: ME also collects disk space information in the Collector.DriveSpace table, and alerts on low disk space.  For these alerts, you can change fine grained exceptions and deferments.  Note that Minion Enterprise collects data for mount points in addition to drive letters.

Drive shares: ME collects information about shared drives – including name, path, user limit, state, and access data.

Service properties: The ME Service Properties module collects information about service properties, including the startup account, service status, and the start mode. Minion Enterprise alerts on stopped SQL services as part of this module, but you can easily set up custom alerts on other services. You can also use this information to assess the impact of an account password change.

Can I write my own custom modules?

Yes. You can most certainly develop your own processes and fold them into our repository. You can write your own alerts, views, stored procedures, etc and look at the data and report on it any way you like. And if you want to write your own collections, then you’re welcome to do so.

If there’s something you can’t do, and you think should be part of the product, write us at https://minionware.desk.com/.


Wait Stats and FAQ…done! Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

Next time we’ll talk about the Databases module.

ME Trial 7 – Error Log Search

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Instance Config module.

This time we’ll look at the Error Log Search module.


The Error Log Search Module

The Error Log Search module allows you to set up specific error log search terms in the dbo.ErrorLogSearch table.  This module automatically gathers any search term matches, and logs them in the Collector.ErrorLog table for alerting or reporting.

NOTE: If you want to be alerted on error log search hits, you must set that up yourself. In future releases, Minion Enerprise may have a generic error log search alert; but for now, feel free to use a scheduled alert, report, or to simply use the collected data ad hoc.

What can we do with this? Well, you can very easily set up custom error log searches and alerts to notify you of specific issues. This is another seriously flexible module. You can search for anything in the SQL Server error log, or in the SQL Agent log. Want to know whenever User1 appears in a log? How about SSPI handshake errors? Or corruption errors? You’ve got it.

Objects in the Error Log Search Module

Tables

  • Collector.ErrorLog – Holds the collected error log data pulled back from each server.
  • dbo.ErrorLogSearch – Holds the searches to run on each instance.
  • dbo.ErrorLogSearchServerExceptions – Holds records of servers that have specific error log search term exceptions. If a server doesn’t have an entry here, then it will automatically take part in all the log searches defined in dbo.ErrorLogSearch.  For example: Let’s say we have 20 error log searches defined in the dbo.ErrorLogSearch table, but for one particular server, we only want to run 5 of the searches.  In this case, our exception server would have 15 entries in this table: one for each search it doesn’t want to take part in.

Views

  • Collector.ErrorLogCurrent – Provides the most recent collection of error log search results.
  • Collector.ErrorLogPrevious – Provides the next-to-most recent collection of error log search results.

Jobs

Like most collections in Minion Enterprise, the Error Log Search jobs are divided up by Service Level. By default, Gold level jobs run more frequently than Silver or Bronze jobs.

  • CollectorErrorLog-BRONZE – Calls the error log search executable ErrorLogGet.exe for all servers with ServiceLevel = ‘Bronze’.
  • CollectorErrorLog-GOLD – Calls ErrorLogGet.exe for all servers with ServiceLevel = ‘Gold’.
  • CollectorErrorLog-SILVER – Calls ErrorLogGet.exe for all servers with ServiceLevel = ‘Silver’.

Setup

To set up an error log search, just insert that search to the dbo.ErrorLogSearch table.  From that time forward, search is now valid for all active, managed servers. (You can make exceptions, but we’ll cover that in the next section.)

Take the example of an enterprise-wide search for corruption errors. DBCC CHECKDB detects corruption, and logs it in the SQL Server error logs in the form of Error 823 and Error 824. So we will define one search for 823, and one for 824:

 

Let’s go over each value of the insert statements. dbo.ErrorLogSearch holds control columns, the xp_readerrorlog parameters, and a couple of columns useful for reporting and documentation:

  • LogNumber – This determines which of the several error logs to search on each instance. 0 is always the current error log, 1 is the next most recent, and so on. For our 823 and 824 search, we definitely want to search the current log. We highly recommend adding a LogNumber=1 search for each of these errors; you never know when a new error log was created, and you don’t want to miss an error this serious.
  • LogType –  LogType = 1 is the SQL Server error log. 2 is the SQL Agent log. We use 1, as corruption errors won’t show up in the Agent log.
  • Class – This is a name you give the search, to categorize them for your own purposes. Logon searches might have a class of “Security”. Here we used “Corruption”.
  • Search1 –  The first search criteria. The collector looks for this exact string within the specified error log.
  • Search2 –  The second search criteria. These are ANDed together. We could have specified Search1 = “Error”, and Search2 = “823”, which would return all entries with both “Error” and “823”. While that would get us all the corruption errors, it would also return any Error that happened to have the number “823” within the string. So, we search for “Error: 823”, as it will appear in the error log, and leave Search2 NULL.
  • BeginDate –  This is the earliest date to log collected error log search results for an instance. In our example, we are searching only the SQL Server error log, and we set the BeginDate NULL, meaning ME would return all found instances of the error within the current error log. If we set BeginDate to one week ago, ME would only log errors found in the current log that are dated within the last 7 days. Note that this only applies to the first time the search (collection) runs for a given instance. Subsequent collections use the most recent date for that instance in the Collector.ErrorLog table.
  • EndDate –  This is the latest date to log collected error log search results for an instance. Both BeginDate and EndDate are meant to be used for targeted searches (where you want to examine a specific date range). For long term searches, both values should be NULL.
  • SortOrder –  Valid values are ASC and DESC.  This orders the error log search results coming from the servers.
  • IsActive – You can turn searches on or off using IsActive. This is a great way to keep a list of searches and only use certain ones every now and then.
  • Comment –  A description of the search, for your own use.

IMPORTANT: By default, there is no alert on collected errors. There is no way to determine what one SQL Server shop will be interested in. However, it is a simple matter to set up an automatic alert or email to notify you of important collected items. For example, you could set up a weekly email to summarize all found error log searches for Class=“Security” and Class=“Corruption”.

A note on performance

SQL Server error logs are simply text files; they aren’t indexable. So, any search on an error log file must (by definition) search the entire file.  It’s possible, then, that you could see some performance lag during error log searches if the SQL Server error log is extremely large.

To minimize this effect, set up a nightly job to cycle the SQL Server error log on every instance, and configure SQL Server to retain 30 days of logs.  This is good log management that we recommend in any case; it has the added benefit here of helping the performance of this process.

Except or disable log searches

To exempt (or except) a server from one or more specific error log searches, insert a row for the server-error search pari, to the dbo.ErrorLogSearchServerExceptions table:

The dbo.CollectionExceptionsServer table allows you to turn off a single collection (module) for a particular instance. So, to turn off error log searches for an entire server, insert a row into dbo.CollectionExceptionsServer for that particular instance, using “Collector.ErrorLog” as the CollectionName:

An example: We have 10 error log searches defined in the dbo.ErrorLogSearch table, and we only wants to run 3 of those seraches on Svr9.  So, we should insret 7 rows to dbo.ErrorLogSearchServerExceptions: one for each search that should NOT run for Svr9:

Furthermore, Svr20 should never be searched for any errors. So, insert a row for Svr20 to the dbo.CollectionExceptionsServer table:


That’s a pretty comprehensive guide to the Error Log Search module. Of course, write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

Next time we’ll talk about Wait Statistics, and frequently asked questions!