How to use Minion Enterprise for the first time, part 3

This is the first article in a multi-part series to guide you through your first weeks in Minion Enterprise.

This is the third article in a multi-part series to guide you through your first weeks in Minion Enterprise.

  • In Part 1 we covered the philosophy of environment monitoring, and how to register servers.
  • In Part 2 we configured error log search terms, and got some good “navigating ME” tips.

Today let’s talk a little bit about configuring alerting, handling email alerts, and a quick note on applications and roles.

Configure Alerting

Minion Enterprise comes with a set of alerts – each with custom thresholds – all configured. Of course, each environment will have its own requirements and quirks.

You can set thresholds, deferments, and/or exceptions for:

  • Missing Backup alerts
  • Drive Space Full alerts
  • Error Log Search Term found alerts
  • Instance Configuration (sp_configure) alerts
  • Replication Latency alerts
  • Service Status alerts
  • Weak Password alerts

You can also turn data collections “off” for one or more servers, by inserting one or more rows to the dbo.CollectionExceptionsServer table.

For information on configuring alerts, see How To: Configure Alerting.

Handle Email Alerts

Inevitably, something will trigger an email alert. Alerts in ME are designed to be aggregated and actionable. For example, the following email alerts us to SQL services with “Auto” start mode, which are stopped:

In this email, we can easily see that:

  1. The alert applies to “Gold” servers.
  2. The only server we’re alerting on is P___.
  3. The service that’s stopped is “SQL Server CEIP service (MSSQLSERVER)”.
  4. We have ready-made code available to us, to defer or exclude this service from alerting.

All of the MinionWare alerts follow these conventions.

Note: We do not believe in junk alerts, inactionable alerts, or alert storming. Take the necessary actions to make sure the alerts you receive are real alerts!

In this example, I personally considered running the Setup.ServiceStatusException code, because I don’t need emails for CEIP (customer feedback program) alerts for this server, ever.

Come to think of it, I don’t need alerts for this service on any server! So I make the exception global:

For more information, see Service Down Alerts.

Set Roles, Applications, Application Owners, and/or Environments

We often refer to different servers not by their name, but by their associated function or application. Minion Enterprise provides a way to define applications and roles for your servers. For each application you wish to define, you’ll need to insert:

  • the application information
  • the server’s role for this application (e.g., Database, SSIS, SSRS, etc.)
  • the application’s environment on this server (e.g., production, test, etc.)
  • the associated SQL instance

For instructions, see How to: Define Applications.

 

Next time we’ll talk more about what you can do with Minion Enterprise. In the meantime, feel free to look around the online documentation!

How to use Minion Enterprise for the first time, part 2

This is the first article in a multi-part series to guide you through your first weeks in Minion Enterprise.

This is the second article in a multi-part series to guide you through your first weeks in Minion Enterprise. In Part 1 we covered the philosophy of environment monitoring, and how to register servers.

Today let’s start with how to configure error log search terms, and navigating ME.

Configure Error Log Search Terms

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.

For more information on error log searches, see Error Log Search Module.

IMPORTANT: 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 each 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.

To set up an error log search, insert that search to the dbo.ErrorLogSearch table.  From that time forward, search is now valid for all active, managed SQL Server instances (that don’t have an exception defined in dbo.ErrorLogSearchServerExceptions).

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:

Remember: from now on, ME will search all of your active SQL Server instances for these errors, and will log them in the Collector.ErrorLog table for alerting or reporting. But we have not set up an alert for this! You can make your own alert SP, or pull one down from the Community Zone!

Getting Around ME

The Architecture Overview article is an excellent place to start. But, to name a few salient points, we’ll tell you:

  • How to get around in ME
  • A  bit about default values
  • A couple of super important features
  • What we think about your customizations

Getting Around

Minion Enterprise is made up of SQL Server stored procedures, tables, jobs, executables, and configuration (“config”) files. Almost everything you’ll touch is native SQL!

The Minion Enterprise repository groups database objects logically into schemas. Some of the most common schemas you’ll see are:

  • Alert– The Alert schema includes alert-related stored procedures, and tables that control alert thresholds and deferments.
  • Archive– The Archive schema includes tables and stored procedures related to Minion Enterprise’s self-cleanup. For example, the Archive.Config table determines how many days’ worth of data to keep for each of the tables in the Minion database. Be sure to look at the archival settings in this table to customize your data retention.
  • Collector– Tables in the Collector schema hold collected data from managed instances. As you might suspect, Collector stored procedures play a part in the actual data collection.
  • dbo– Data in the dbo schema tables tends to be more static than in other schemas – for example, the data in dbo.Servers will change as servers are added, removed, and upgraded, but the frequency of server changes is far less than data collections or alerts.
  • History– Alert details are saved to tables in the History schema, before those alerts are emailed. For example, one row in the table History.Backups represents one detected missed backup. This allows you to (among other things) report on databases that have frequent trouble with backups.
  • Setup – The Setup schema denotes stored procedures that allow you to set up various deferments, exceptions, and thresholds (e.g., Setup.DiskSpaceThreshold allows you to create or alter a disk space alert threshold). In the future this may expand to additional setup stored procedures.

Using this, and a touch of “sys.tables”, you can find pretty much anythink you’re looking for. “Where is the lastest job history collection? Oh yeah, Collector.SysJobHistoryCurrent…”)

On Defaults

Global Default – Any time you see InstanceID = 0 in Minion Enterprise, it represents a global default. So for example, you can set an alert exception for all servers, using InstanceID=0 in that alert’s exceptions table.

Instance Default – Any time you see DBName = MinionDefault, it represents a global default for all databases on the given InstanceID.

Global Database Default – You can also set an environment-wide default for a specific database, by using the database name along with InstanceID = 0.

Super Important Features

IsActive – Many tables have an IsActive column. This allows you to “turn off” that particular row, temporarily or permanently, without deleting the row.

Each managed server in Minion Enterprise must be assigned a Service Levels – a simple label for the level of the server’s importance. (You assign a service level to a server in the dbo.Servers table, ServiceLevel column.) By default, Minion Enterprise handles three specific service levels:

  • Gold
  • Silver
  • Bronze

ME collects a lot of data, keeps it for a (configurable) amount of time, then deletes it.

Customizing – Do it!

MinionWare encourages you to create stored procedures and views as you find you need them. ME is meant to be customized.

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.

 

Next time we’ll talk more about what you can do with Minion Enterprise. In the meantime, feel free to look around the online documentation!

How to use Minion Enterprise for the first time

This is the first article in a multi-part series to guide you through your first weeks in Minion Enterprise.

Minion Enterprise lets you monitor and investigate your SQL Server environment for security and stability. It’s a big system, though, and it’s easy to get lost.

This is the first article in a multi-part series to guide you through your first weeks in Minion Enterprise. Topics we’ll cover include:

  • Getting around ME
  • Configure Error Log Search Terms
  • Set Roles, Applications, Application Owners, and/or Environments
  • Configure Alerting
  • Handle Email Alerts
  • Check High-Level Health
  • Check the Security of the servers
  • Research Index health
  • Research (and set alerts for) sp_configure settings
  • Get an inventory of clusters and Availability Groups

And of course, more. For today, let’s start with the philosophy of environment monitoring, and getting your servers registered in ME.

Monitor the environment

The database industry is familiar with performance monitoring, which tracks performance-related metrics. But the health of a server depends on so much more.

  • Are the disks filling up?
  • What service packs are installed?
  • Who has permissions to what servers and objects?
  • How well are the databases indexed?
  • Are backups and maintenance running well?
  • Have sp_configure settings been changed from the standard?

Environment monitoring covers as many aspects of database administration as possible, including:

  • Server Environment Data – Disk space utilization alerts, anyone? How about OS patch levels and service properties?
  • SQL Server Environment Data – Everything from sp_configure values to error log alerting.
  • Database Information – DB properties, files, scripts, space used, index stats, and more.
  • Maintenance and Backups – When did backups happen last? How about DBCC CheckDB?
  • Security and Encryption – All logins, users, Active Directory information (including group expansion!), role membership, and scripted permissions.
  • Replication Latency – We’ve got replication latency data for all subscriptions.
  • Availability Groups – ME gathers information on AG replicas, listeners, groups, status, and read-only routing lists.

First things first: Register servers in Minion Enterprise

You can install and configure Minion Enterprise in about five minutes. Just be sure your server meets the requirements outlined in the Quick Start Guide!

Minion Enterprise references the dbo.Servers table to determine what SQL Server instances it should be managing. So the first step after installation is to “register”, or insert, instances to that table!

Because this is a table, and we’re all DBAs, there are a number of ways you can insert servers to the table:

  • Import the list from a CSV or Excel spreadsheet.
  • Import the list from another table.
  • Enter instances using INSERT statements.
  • And so on.

Whichever way works for you, you’ll need to insert to dbo.Servers with the following information:

  • ServerName – of course.
  • Port – this must be NULL for the default port (1433), or the port number for a nonstandard port (anything other than 1433).
  • ServiceLevel – Determine whether your server ranks as “Gold”, “Silver”, or “Bronze”. This service level determines what level of service (e.g., how often collections and alerts are performed) each server receives.
  • IsSQL – This should be 1 for SQL Server instances, and 0 for non-SQL servers. Servers without SQL still get the benefit of drive space monitoring and more!
  • IsActive – This should be 1 for any server you want collections and alerts to run on, and 0 for any other server. I personally keep inactive servers in the list specifically just to keep a master list of servers!

Next time we’ll talk more about what you can do with Minion Enterprise. In the meantime, feel free to look around the online documentation!

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.3Setup.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.

A DBA’s first day on the job

Here we walk through a database administrator’s first day on the job – first without Minion Enterprise, and then with it! (Guess which day was more productive.)

minion-enterprise-01A DBA’s first day on the job

Newbie DBA: Okay, I’m ready to start. Tell me all about your environment!

Current DBA: Well, we’ve got a couple of test boxes, a few QA, and something like 40 production instances. I think most of the production instances are on SQL 2008 R2, but I’m not sure.

Newb: Ah. Okay, so which prod boxes are the important ones?

Curr: Oh, I guess Server3 and Server8 are some of our bigger ones. I’ll have to dig out my list. I put it together like a year or two ago, and a couple of things have changed since then…

Newb: Oh. Well, what do you guys use for backups around here? Third party, or home grown?

Curr: It’s a mix. Some boxes are still on maintenance plans, I’m pretty sure. Some have those free scripts from that one guy, you know, that free set. And we’ve got a couple of different versions of home grown scripts. I’m pretty sure most of that stuff works okay, but not all of it has alerts.

Newb: Not to criticize, but have you ever thought about changing all of that to one plan?

Curr: Yeah, but we don’t really have the time. We’ve got a lot of fires that spring up around here. Keeping up with disk space requests alone must take up several hours every month.

Newb: Gotcha. Well I know you wanted me to do some general index evaluations on the important prod boxes. When will my credentials be ironed out so I can get started?

Curr: Yeah, Bill’s got that on his plate. It usually takes him a few days to get a new hire’s SQL permissions worked out. Like I said, we have several dozen boxes.

Newb: ….

A DBA’s first day on the job, with Minion Enterprise

Newbie DBA: Okay, I’m ready to start. Tell me all about your environment!

Current DBA: Here, I’ve got the list of servers in the dbo.Servers table on the repository instance. Look, we have 4 test boxes, 14 QA, and 47 production. Each one of those is ranked for Gold, Silver, or Bronze level support. I’ll give you that support doc in a minute, but you get it: the gold ones are super important.

Newb: Looks like most of those servers are on SQL Server 2012.

Curr: Yeah, this software checks in on all the instances, so we always know what instances have been patched and which haven’t yet.

Newb: Awesome. What do you guys use for backups around here?

Curr: It’s a mix, but we’re moving to Minion Backup. It’s free, and ties in with this piece – Minion Enterprise. But even for the instances that haven’t moved over yet, we still get alerts from ME if a database hasn’t been backed up recently.

Newb: Okay, that’s pretty cool.

Curr: Oh yeah, you haven’t even seen the start of it. Minion Enterprise does so much that saves us heartache. Like, security audits are easy, we get reports and alerts on disk space, the thing scripts out database objects and jobs. Last month one of the managers decided to drop all of the synonyms on Server4; we didn’t even have to restore a backup. We just pulled all the synonym code from the DBObjectScripts table and ran it.

Newb: I really could have used that at the last job. Junior DBA altered a bunch of views. Okay, so I know you wanted me to do some general index evaluations on the important prod boxes. When will my credentials be ironed out so I can get started?

Curr: Oh it’s done. That’s another thing you can do with ME; I just cloned all of my permissions to your new account, across all the servers. You’re good to go.  Oh, and you should really start your index evaluations inside the ME repository; it gathers a ton of information about objects, usage, and indexes for ALL the boxes. There are stored procedures for index research, like the “clustered GUIDs” SP. You’ll see them when you get in there.

Newb: …wow.

This could be you

Download Minion Enterprise and enjoy free for 90 days. Your current team and newbies will thank you.

Minion Enterprise: SQL Version Number Update

Minion Enterprise gives you the ability to see what version of SQL Server each instance is on, and also what version they SHOULD be on. In this post, we explain the SQL Version table and how to update it.

From time to time MinionWare sends out updates to the SQL Server version table. This has no effect on the system as a whole, and is safe to run. The update is sent in clear text so you’ll be able to inspect the statements being run.

What is the SQL Version table?

Minion Enterprise gives you the ability to not only see what version of SQL Server you’re on, but also what version you should be on. We accomplish this by keeping an updated version table that contains the latest SQL Server CUs, security updates, service packs, and hotfixes. So now you can easily see how far behind you are on patches, and even setup alerts to let you know when you’re too far behind. This feature is a fabulous way to keep track of SQL Server patches across your entire enterprise, and not just a server at a time.

Download and more information

See the official SQL Version Number Update page for the SQL Version Number Update download, and for more information, including sample queries.

Giveaway wrap-up, plus a new user webinar

We gave away free licenses of Minion Enterprise for the company birthday! We are absolutely thrilled with the response. In this post is a thank you, and a link to the ME intro webinar we held after the giveaway.

Update: The webinar is past. Watch the recording!

The birthday celebration giveaway idea was a good one, apparently…a lot better than we anticipated. Between friends of the company, SQL user group announcements, our friendly neighborhood SQL Server Central, Reddit, a surprise article over on ITWire, and more, we received several hundred requests for free licenses! We are absolutely thrilled with the response.

If you’re reading this, you’re a part of what makes MinionWare a real live company: interested people. So, thank you. Thanks for being a part of the party.

Webinar

On July 20 at 11:00am Central time (1600 GMT) we’re holding we held a welcome webinar. This session is for all of proud new owners of ME, and anyone interested in a 90 day trial. Sean will introduce you to the product, show off some of the better features, and answer your questions.

Register now for the July 20 webinar. See you there!

The webinar is past. Watch the recording!

 

MinionWare releases Minion Enterprise 2.2!

Minion Enterprise 2.2 is a big jump forward, with user cloning, new collections, bug fixes, and so very much more. Download ME today!

minion enterprise-01Minion Enterprise 2.2 is a big jump forward, with user cloning, new collections, bug fixes, and so very much more. Sign up to download ME 2.2!

Here is a brief list of new features and improvements:

Continue reading “MinionWare releases Minion Enterprise 2.2!”