All posts by Sean McCown

I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to SQLServerCentral.com as well as SSWUG.org. I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.
Admin

Cannot Generate SSPI Context

1 Comment

I woke up pretty early this morning and decided to get something in my dev environment worked out that I’ve been meaning to do for a long time now. I needed to put my main Minion dev box on a domain acct for SQL. It was currently running under local system. So I switched it to use the domain acct SQLSvc. The second I restarted SQL with the new acct I got the dreaded “Cannot Generate SSPI Context”.

Here’s my fix and I’m hoping it’ll help someone in the future.

First let me say that SSPI errors can be caused by a number of things. This one turned out to be an SPN error, but it doesn’t have to be. Among other things it could be:

  1. DNS lookup.
  2. SQL Server time out of sync .
  3. Client time out of sync.
  4. Anything else that keeps Kerberos from working.

 

Now there are also things that can keep the SPN from being created or used. The account could not have the perms it needs to create the SPN in AD, or there could be duplicate SPNs. You can only have 1. Or you could simply have the wrong SPN. Make no mistake, this issue had caused many gray hairs in many DBAs, and I personally always sigh when it comes up because it can be fixed in 5mins or it can take 4 days and 3 MS support techs getting involved. Most of the time though, it’s more straight forward than that. I’m not going to even attempt to make this a treatise on troubleshooting every aspect of this error though. I’m just going to show you how to diagnose the SPN portion of it and quite often that’s the issue so if it doesn’t work for you it’ll at least eliminate the SPN and you can concentrate your efforts on something else.

OK, that’s enough prelim stuff, let’s get to it.

First, we need to see what SPNs we have on the box. There are 2 ways you can do that depending on what rights you have in the domain. I’m going to show you both of them so you can have a visual guide as well as a cmdline guide.

To see what SPNs you have on the box, go to the cmdline and type the following:

SETSPN –L MinionDevCon

What I’m saying here is to list (-L) the SPNs registered for the SQL box I’m interested in. In this case, MinionDevCon.

See below for the query and the results.

SPN1

Also, notice that the SETSPN command isn’t case sensitive.

So above you can see that I’ve got 2 SPNs for my SQL acct on that box.  Now we just have to delete one of them.  However, before we fix this issue, I’ll show you another way to view the same info.

If you have access to AD, then you can also see the SPNs in the computer object properties.  So go to AD and navigate to the computer object for your SQL box.  Again, in my case this is MinionDevCon.  Then go into the properties of that computer object.  You’ll want to go to the Attribute Editor tab.

SPN2

Now find servicePrincipalName and either double-click it or highlight it and hit Edit.  This will show you the list of SPNs for that computer object.

Here you can see my 2 SPNs just like at the cmdline.

SPN3

Deleting the extra SPN:

Deleting the bad SPN is a fairly straight forward operation.  And again I’ll show you 2 ways.

At the cmdline, simply use the SETSPN cmd again and pass in the delete flag.

SETSPN -D MSSQLSvc/MinionDevCon.MIdnight.DBA:1433 MinionDevCon

So again, that’s SETSPN <SPN> <Computername>
The SPN in the cmd above should be exactly the way it was represented in the list when it was queried.  Here’s the command in the query window so you can see what it looks like:

SPN4

Ahhh, see there, my regular acct doesn’t have rights to do this.  I wanted you to see what happens when you don’t have rights.  You will quite often need to get your AD guys to do this for you.  So if you just prepare the cmd and send it to them they’ll be able to make it happen.  Here’s what it looks like when I log in under my domain admin acct.

SPN5

And again, that cmd is simple.  Call SETSPN with the -D flag, which tells it to delete.  Then the exact name of the SPN followed by the name of the server.

Now let’s do the same thing in AD.  So you’re still in your computer object properties like above.  Simply highlight the SPN you want to drop and then click the Remove button.  It’ll put it in the text box above, but just click OK and it’ll be gone.

SPN6

Ok, that’s it.  I didn’t have to restart my SQL service, but I’ve heard of people who have.

Now, this was just a quick tutorial on how to manage SPNs.  This hole can go pretty deep.  Here’s a decent link on MSDN for troubleshooting SPNs.  I don’t think I like their troubleshooting because they don’t really do a good job of showing you the commands, but it’s a good explanation of the problem, what an SPN is, etc.  If I remember correctly it’ll also help you choose the right SPN.

Admin, Minion by MidnightDBA, Powershell

Security Theater

7 Comments

The biggest question I get about the Minion products is about security. People constantly tell me that a shop can’t implement Minion because it enables xp_cmdshell, whereas the SQL Server Maintenance Solution by Ola Hallengren doesn’t, so it’s naturally more secure. So in this post I’m going to show you, using both Minion and Ola’s routines, that what most people consider “more secure” is really just security theater. Now since the names of Ola’s routines change slightly, and the entire solution has kind of a long name, I’m going to refer to them collectively as SQLMaint. As well, I’m going to refer to the Minion suite of maintenance routines collectively as Minion.

For those of you who don’t know, Security Theater is when you have the appearance of security, but in reality you’re not secure at all. Your security measures are just for show. We see this everywhere, and it’s probably one of the main reasons that we’ve got so many data breaches happening all over the world. We’ve got admins making security decisions that they never bother testing, or never bother even questioning so while their intentions are good, they wind up with just the appearance of security. You’ll never be smarter than every hacker on the planet, but don’t get in your own way either.

So here I’m going to compare and contrast the methods that both Minion and SQLMaint use to perform their tasks and see which one is honestly more secure in your environment.

Let’s start by looking at how each product does its job.

Minion: Powershell and xp_cmdshell

First let’s look at Minion. The Minion tools use a combination of Powershell and xp_cmdshell inside SPs. Now, I’ve heard screams from some admins that Powershell isn’t secure because it could allow someone to do something inside SQL or the OS that they don’t have rights to do. This is called privilege escalation and it’s outright false. Powershell is a product like anything else. It has to follow security protocols the same as any other program. That means that there are no hooks inside Windows that notice when a Powershell script is running, and give it extra access. It simply runs under the context of the account that started the script. So Powershell is like any other scripting language from that respect. And if you’re accessing SQL Server from Powershell, then you’re still limited to your rights inside SQL itself. Powershell has no native way to access SQL so there’s no way for SQL to notice that Powershell is trying to connect and give it extra privileges. You access SQL from Powershell from one of 3 different methods: .net, sqlcmd, or invoke-sqlcmd.

Nobody has accused .net of privilege escalation, so making this call from Powershell wouldn’t do it either as you’re literally creating a .net framework object and connecting to SQL through any of those methods. And nobody thinks that sqlcmd gives you any extra rights either. You just connect to SQL and either use your AD account or your SQL account and you get the perms you’d have anyway. And of course, invoke-sqlcmd is just a Powershell wrapper for sqlcmd so there’s no extra security stuff going on in there either.

This is a good time to mention that Powershell is just a SQL client like anything else. SQL itself doesn’t even know Powershell exists. So when you connect to SQL through Powershell, as far as SQL is concerned, it might as well be SSMS, or Excel, or a website, or VBScript, or Python, etc. They’re all just clients and SQL doesn’t do anything special for any one of them. So the idea that Powershell leads to unintended privilege escalation is just that… it’s an idea. But it’s completely false. It has to follow the same security rules everything else does. And believe me, I really wish it would give me the rights to do things my personal account can’t.

Now does that mean that someone can’t fool Powershell into running under a different account so that it has more rights? Of course not. Hackers are smart. They’ve got all kinds of ways to get around things. But Powershell isn’t any less secure than VBScript, which comes on your box by default. So if they exploit a security mechanism they can use that exploit against Powershell or VBScript or even just batch file commands.

Second, the Minion tools use xp_cmdshell. By many admins this is considered to be a huge security hole. Why? Well in short the issue is definitely with privilege escalation. The issue isn’t with what they’re afraid the Minion tools are doing; they’re afraid of what someone else could do with xp_cmdshell enabled. Because with this enabled, someone could fairly easily access the OS layer and do all kinds of things under the SQL service account credentials.

SQLMaint: sqlcmd

SQLMaint works by calling sqlcmd from a job step. This is considered more secure because you don’t have to enable anything special outside of the default security configuration. However, I’m going to show you that this actually isn’t more secure, it can actually be considered less secure. So let’s get into it and I’ll show you what I mean.

xp_cmdshell is limited to sysadmins by default

Ok, we’ll start with xp_cmdshell. Let’s look at how you enable xp_cmdshell and the implications it has.

You implement xp_cmdshell by turning it on in sp_configure. This is an instance-level config so once it’s on, it’s on for all the DBs on that instance. Here’s the code you run to turn it on:

First you have to have ‘Show Advanced Options’ enabled.

Sp_configure ‘show advanced options’, 1

RECONFIGURE

Sp_configure ‘xp_cmdshell’, 1

RECONFIGURE

*Here’s something few people know. You actually only have to specify enough letters to make the option unique. So in the first command above you really only have to do this:

Sp_configure ‘show’, 1

If there were two options that started with ‘show’ you’d have to put enough letters in there to make it unique.

 

Now that it’s on, by default it’s only available to sysadmins. Nobody else can access xp_cmdshell in any way unless you give them access. And what does this mean in reality? It means that sysadmins have rights to do through xp_cmdshell what they’ve already got rights to do anyway. They’re really not getting any extra rights since most DBAs have full rights on the box anyway. And since xp_cmdshell runs under the context of the SQL service, then they’re running under whatever rights it has. This is one of the big reasons why it’s important to follow the least privilege rule for your service accounts. The other reason is because someone who knows the service account password could login under that account and do stuff and have their tracks completely covered. The threat doesn’t have to come from outside the company.

How to grant access to xp_cmdshell for non-sysadmins

You can give non-sysadmins rights to xp_cmdshell, but it takes an extra step. Since you don’t want just anyone running with full access, you have to define a proxy account. This proxy account provides the security context for all xp_cmdshell executions performed by non-sysadmins. This means that your non-sysadmins don’t automatically have unfettered access to anything on the OS or the network, because you’re going to make sure that the proxy account has only the rights it needs. You’re not going to make the proxy account an admin on any of your boxes. Here’s how you create the proxy:

EXEC sp_xp_cmdshell_proxy_account ‘Domain\ProxyLogin,’$$$$###MyStr0ngPassw0rd!@#!@#!!!’

And yes, it has to be an AD account… or at least a local Windows account (I would imagine). And the reason is simple. The only reason for running xp_cmdshell is to access OS-level things. The OS has nothing to do with SQL security so you need to pass it a Windows account. Now you can grant any non-sysadmin execute rights on xp_cmdshell.

The question is do you need to give non-sysadmins access to xp_cmdshell? I have to say that in my 20yrs in SQL, I think I can remember needing this only like once… maybe twice. The point is, that this is a lot like linked servers. The answer is almost always NO, unless there’s such a compelling reason that can’t be gotten around any other way. And that’s almost never. So in all but the strictest of circumstances, xp_cmdshell is only going to be available to your admins who have OS rights to do what they need anyway. Xp_cmdshell just makes it easier for them to code it.

The dangers of sqlcmd

Now let’s look at the method SQLMaint uses to launch its routines. Like I said, SQLMaint calls sqlcmd, which is an OS-level cmdline executable. So you have to have a way to make an OS-level call. And the way this is done is by using the command line job step type. So this job step type allows you to write any cmdline operation you need and it’ll run it for you, you guessed it, under the service account credentials. So by default this functionality is turned on and most people don’t even know it. And if you don’t know it’s there by default then how can you lock it down? The good news though is that only sysadmins have access to this type of job step by default. But anyone who has rights to alter jobs can make changes to the step.

So what does this mean for security vs security theater? Well, with xp_cmdshell you know you’re turning it on and you have to go out of your way to create the proxy and give non-sysadmins rights to it. So at every step of the way you’re aware of what’s going on. But with the job step you’re accepting the default config so anyone with the rights can come in and create a job with this job step and do something they’re not supposed to, or better yet, just alter the current job step in SQLMaint’s call.

Here’s a call I took for SQLMaint’s backup routine directly from its website:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’, @Directory = ‘C:\Backup’, @BackupType = ‘FULL'” –b

The above command backs up the user DBs and this is the code that’s inside his command line job step. Now, what if I wanted to be malicious? I could easily open the job and alter it to do this:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “CREATE LOGIN [MyNewSALogin] with password = ‘StrongPassword’; EXEC master..sp_addsrvrolemember @loginame = N’ MyNewSALogin ‘, @rolename = N’sysadmin’; EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’, @Directory = ‘C:\Backup’, @BackupType = ‘FULL'” -b

Ok, so I just gave myself sysadmin on this instance. And I know what you’re thinking. You have to have admin rights to be able to make this change. Well, that’s not even close to accurate. Not only can you have job manager perms, but you don’t have to have any of those perms. In fact, a regular user can make these types of changes with very minimal rights. Let me give you a scenario that’s not only possible, but is highly likely.

You have a production box that has lots of jobs on it. You have different groups that need to create, alter or manage these jobs because as the DBA team you don’t want to have to get involved every time one of these many jobs has an issue, or every time they need an update… because it happens a lot. So you give that app team rights to manage jobs. Here’s where the fun begins. There are multiple ways this can go wrong, but I’ll go with the simplest. All your user needs is 3 permissions inside msdb, and here they are:

grant select on sysjobs to [SecurityTest]
grant select, update on sysjobsteps to [SecurityTest]

 

I created a SecurityTest user and gave him access to msdb. Then I granted the perms above. Now the only thing the user has to do to recreate what I did above is run a simple update.

update sysjobsteps
set command = ‘CREATE LOGIN [MyNewSALogin] with password = ”StrongPassword”; EXEC master..sp_addsrvrolemember @loginame = N” MyNewSALogin ”, @rolename = N”sysadmin”; ‘ + command
where job_id = ‘0C06625F-F518-4D86-9E5A-063AE8B9C4E4’
and step_name = ‘BackupDBs’

 

He can query sysjobs to get the list of jobs and find the one he’s interested in, and then update sysjobsteps to make the change. Now, the next time that job runs, he’ll have a sysadmin account. He’s then free to cover he tracks by removing the changes, and even give himself a couple backdoors in case someone discovers his new account. This can even include adding this type of code inside of other SPs or jobs so that if his rights are ever removed, they’ll be put back. And depending on how many instances run off of the same SQL service account, he could easily spread his access to every other server in your shop. And he doesn’t even have to be that smart to do it. This isn’t all that clever of a hack.

But you see what’s happened here, right? You wanted to give him rights to manage his jobs and you ended up giving him sa. And he didn’t need that many rights to do it. See, one of the misunderstandings is that the msdb tables are system tables. They’re not. They’re regular user tables, so you can easily update them directly.

Lock down sqlcmd!

But how do you protect against this? Well, the same way you did for xp_cmdshell. You create a proxy account and run those command line job steps under an account with much lesser rights. This way, even though someone might change the code the job runs, it’ll fail because the account won’t have the rights to make the change.

Security is a complicated animal with many facets, twists, turns, and pitfalls. And to say that one solution is more secure than another just because one of them uses a feature that’s turned off by default simply isn’t the case. I hope I’ve proven that turning on xp_cmdshell isn’t inherently bad, and I really hope you understand now that simply leaving it off doesn’t mean that you’re secure. In fact, I’d say you’re at greater risk because you’re not likely to have addressed this issue in your jobs. You’re merely engaging in Security Theater. You have to be a sysadmin to turn on xp_cmdshell and you have to give someone specific rights to run it after creating a proxy. But you could innocently give someone perms in msdb and give them the keys to the kingdom.

So I hope you start looking at the xp_cmdshell issue differently because it’s a wonderful feature that allows you to do some very cool things. And it lets Minion tools give you some really awesome functionality that you wouldn’t have otherwise.

Admin, Development

XP_CmdShell isn’t Evil

1 Comment

I’ve been hearing it more and more the past year.
“XP_cmdshell should always be turned off.”
“Whatever you do, don’t turn on XP_cmdshell!”
“We can’t do that, it requires XP_cmdshell!”
“You’ll fail your audit if XP_cmdshell is turned on.”
And all the other variations.

And I suppose I’ve been hearing it more and more lately because Minion Reindex requires it and Minion Backup will require it even more so.

However, I’ll tell you I’m getting pretty tired of hearing it so true to my blog I’m going to rant.
XP_cmdshell has been around forever. And way back in the day, like 15-20yrs ago, it was installed wide open to the public. This is where the problem started. This was back in the day when SQL’s GUI allowed way too many people who had no idea what they were doing to create and manage DBs. That ease of use was a huge part of SQL Server taking hold in the industry. However, with the product being that easy to use, a lot of these untrained DBAs had no idea XP_cmdshell was even there, so their instance was completely vulnerable and they didn’t even know it. Honestly, this was Microsoft’s fault. They should never have packaged up something that dangerous completely open to the public. But you know what, back then they were also installing sa with a NULL password by default too. And Oracle had their scott\tiger username\password combo, so MS wasn’t the only one doing dumb security back then.

However, now XP_cmdshell comes turned off and when you enable it, it’s not open to public anymore. So seriously, what are you still afraid of? I understand that you used to be scared of it because there was no way to lock it down back then. In fact, Microsoft didn’t provide a way to lockdown XP_cmdshell until somewhere in the neighborhood of version 4.2. So back when it was open to public I can see how writing a DENY statement would be really taxing to you as a DBA.
But these days you don’t have any excuses. You have to go out of your way to open it up to public. XP_cmdshell is still really useful and I’m personally able to create many excellent solutions using it… things that would be much more difficult otherwise. And do you know what I tell people who tell me how dangerous it is? I ask them why they don’t lock it down.

Think about it… there are many dangerous features in SQL. And they’re all kept in check by controlling permissions to them. You don’t see anyone screaming that those other features should be allowed on the box because they just say, we use it but we keep its usage controlled pretty tightly. So why doesn’t that apply to XP_cmdshell? Do you think that SQL all of a sudden forgets how to deny execute perms when that gets called? Do you think that SQL honors all security except that one? Do you think XP_cmdshell is powerful enough to override SQL security and just do what it wants anyway?
Of course not. So what are you afraid of?

The truth is that XP_cmdshell can do a lot and in the wrong hands it can make a royal mess of things. Then again so can DELETE and UPDATE. So can SHUTDOWN. So can CLR. So can DROP DATABASE. So can Dynamic SQL. And you don’t see anyone saying that all of those should never be allowed on any server for any reason. And I would honestly venture to say that Dynamic SQL has been the cause of far more security breaches than XP_cmdshell ever has. I don’t have any numbers to back me up, but I bet if you look at the number of security issues caused by XP_cmdshell, they’re far out-weighed by other features.

And it’s not like people have to way to get that functionality just because XP_cmdshell is disabled. There are still cmdline job steps and cmdline SSIS tasks. And of course, you’ve got CLR. All of which can be just as dangerous as XP_cmdshell yet they run on systems all the time. And I know what you’re thinking… “But Sean, we control those through permissions so they can’t do anything really bad.” Yeah, so you’re making my point for me. But do you think that if an SSIS guy wanted to do something bad to your box that he couldn’t find a way if he weren’t locked down? Of course he could.

The cool thing about the cmdline task in Agent jobs is that they can be run via proxy. You can setup a proxy user to run that step under so that its Windows perms are limited and it can’t run haywire. You wanna hear a secret? There’s a built-in proxy mechanism for XP_cmdshell too. I could tell you how to do it, but DatabaseJournal has already done such a fine job. So here’s the link to setting up the cmdshell credential.

I don’t want you to just turn on XP_cmdshell on all of your systems for no reason. But I don’t want you to completely rule it out as a solution just because you’re afraid of it. Tell your Windows admins who are afraid of it to mind their own business and stick to what they know. You’re a DBA and it’s time for you to take back your SQL instances. Lock them down. Don’t be afraid to use cool functionality because so many people refused to read the documentation 20yrs ago. You know better now. So go out there and do the right thing. Lockdown XP_cmdshell, but use it.

Admin

Complete Troubleshooting Guide for SQL Server Database Mail

13 Comments

Today I had DBMail go south on me on a QA box and it took the better part of the day to get it back. This is a box that I’ve never touched before so I didn’t do anything wrong, but someone did. This promises to be kind of a long post but hopefully it’ll be very complete. And if there’s anything I forget then let me know and I’ll add it.
I’ll attempt to take you through my process more or less. There will be 2 sections: Expert and Beginner. The expert section will just list the item to check and will skip the directions.
And the beginner section will explain the steps to take to perform each task.

Let’s start with the problem.
Symptoms:

  • Mail used to work, but just quit a couple weeks ago.
  • Mail sits in the queue unsent.
  • There are no errors being logged for the messages. Normally you would expect to have an error of some kind to tshoot.
  • There are not messages of any kind about the mail operation at all.

Ok, now that we know the symptoms we can start to tshoot. Now, you may not have these exact symptoms, but you can still use this as a tshooting guide for your scenario.
I strongly urge you to read your error messages. Getting a specific error then chase that down. This is more for those times when you have no idea what’s wrong and you need to troubleshoot DBmail from end to end.
It may make sense to do some of these steps in a different order. Use your best judgment on that.
Also, at the end of the post, I’ll discuss the mail tables we’ll be using to troubleshoot our process. These tables are mostly easy to read so I’m not gonna go into tons of detail, but I’ll walk you through what you need to know.

Expert Guide:

1. Check the DBMail log for any errors.
2. Check Mail XPs are enabled.
3. Telnet to your mail server.
4. Test SMTP Relay through telnet.
5. Send test email through vbs or Powershell.
6. Check Firewall blocking application or port.
7. Check anti-virus blocking application or port.
8. Run DatabaseMail.exe manually.
9. Check DatabaseMail.exe permissions.
10. Stop/Start DBMail.
11. Change service account to Network Service and back to the domain account it was using.
12. Make sure msdb is owned by sa.
13. Check there isn’t a space after the profile name or the SMTP server name in the mail config.
14. Test different authentication methods in the mail config.
15. Make sure the profile is set to Public.
16. Make sure the user sending the mail is either an admin or is in the DatabaseMailUserRole.
17. Check Service Broker is enabled in msdb.
18. Check that DatabaseMail.exe is in the Binn folder.
19. Check for Aliases that don’t belong or are misconfigured.
20. Check the hosts file for entries that may be misconfigured and messing things up.
21. Re-issue the Service Broker GUID for msdb.
22. Using GMAIL with MFA.

Ok, I think that pretty much covers it for the Expert section. If you can make it through each of those w/o any help then you don’t need to read on. However, if you’d like discussion and help with them then keep reading because the full discussion will follow. I’ll stick with the same numbers so you can follow along easily.

Beginner Guide:

This is where I’m going to give full explanations of the steps and some of the theory as to why you’re doing what you’re doing.

Troubleshooting Theory:
Ok, so basic troubleshooting here. The way you want to tackle any tshooting problem is to isolate the general cause of the problem and then narrow in on the exact cause. So you’ll see that our first order of business is to find out where the problem lies. Does it lie on the SQL side, or the Windows side, or the Exchange side? You’ll cut your tshooting time drastically in any scenario if you can breakdown the general cause. So in other words, pick a side of the equation and work your way to the other side. That’s what we’re going to do. We’re going to test the SQL side, then the Windows side and then make some decisions based off of what we find.
So w/o any further ado, let’s get started.


1. Check the DBMail log for any errors.

The first thing you ALWAYS do is look for error messages. With DBMail you can either check the log through the GUI or you can check the table directly. We’re going to check the table directly because it’s easier to type a query and to create a screenshot.
So open a query window and run this query:

SELECT * FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC

Notice that I’m ordering it descending by date. That’s so the newest ones are on top and it keeps the scrolling down so you can easily see new entries. If you’re lucky you’ll have a nice error message you can troubleshoot. In this case there’s absolutely nothing and in fact there hasn’t been a new message in several days. So we’re flying blind here. But checking for errors is always the first place to start. So since there are no errors, we have to decide where the issue lies. It could be an issue with SQL itself, or something could have happened on the Windows or the Exchange side.
As long as we’re already in SQL though, let’s check some of the low hanging fruit while we’re here.

2. Check Mail XPs are enabled.

We need to check whether the Mail XPs are enabled. There will be no email until these XPs are enabled. So open a query window and run this query:

sp_configure 'Database Mail XPs'

sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Run line 1 above to query the status of the mail XPs. 0 means they’re disabled. 1 means they’re enabled. Run the rest of the code above to enable the XPs.

After you enable the XPs, try a test email. You can right-click on Management\Database Mail and choose ‘Send Test E-Mail’ and send it that way, but you’re likely to be sending lots of test emails so let’s use code instead. It’s faster.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@recipients = 'email@domain.com',
@subject = 'Test Email 1',
@body = 'Hey, I''m finally working!!'

I like to number my email subjects so I can see which one has finally come in when it starts working.

Now, we’ll assume that didn’t work, and that you checked the log again and there still aren’t any error messages. We need to check the Windows side now to make sure we’re not just spinning our wheels in SQL. That’s what we’ll do now.

3. Telnet to your mail server.

If you don’t have telnet installed you’ll need to go into the Control Panel and install it as a Windows Feature. I can’t tell you exactly how to do it because it’s slightly different in different versions of Windows. But what you want to install is called Telnet Client. It doesn’t require a reboot.

Once you’ve got telnet installed, open a cmd prompt and type the following syntax.
telnet SMTPservername 25

To break that down, telnet is the name of the program you’re running, SMTPservername should be replaced with the name of the SMTP server you’re trying to reach, and 25 is the port. So a real cmd could look something like this: telnet smtp.minion.com 25

You should get a response back right away.
Here’s what it looks like when the call fails:
TelnetFail
So why did this call fail? Because you have to use the FQDN (Fully Qualified Domain Name) of the smtp server.
Here’s what it looks like when the call succeeds. I doesn’t show the call itself because that goes away as soon as you connect. But the call is this: telnet mailcon.midnight.dba 25
Notice the 3 part name of the server… host.domain.top-level-domain.
Now, strictly speaking it doesn’t HAVE to have the FQDN. In my experience you can use either the servername or the FQDN but you usually can’t use just the host.domain. But there are so many variations in networks I can easily see it be possible that yours is setup to be able to resolve host.domain. Anyway, whatever smtp address they gave you is what you should use.
TelnetPass
Telnet is one of those very unfriendly programs because underneath that 1st line you’ll just have a blinking cursor… not even a cmd prompt, just a cursor. So you have to know what to do.

However, let’s mark what telnet has told us so far. So far by being able to connect to the server, we know the following:
1. There’s nothing wrong with the network between the 2 servers.
2. Port 25 isn’t being blocked by anything.
3. The smtp server is running and active.

Now, this is a simple port test so we still don’t know if we can send mail to that server. We just know that physically there’s nothing standing in our way. Had this step failed, we would proceed with testing the network connection, firewall, anti-virus (AV), and Exchange. You won’t be able to test Exchange itself probably, but you can ask your Exchange guy if it’s up. And you can ask your network guy if he knows of anything wrong with the network between the 2 servers. At this point though, chances are it’s a firewall issue. That could mean a local Windows firewall, or maybe your AV has a firewall, or it could be an external firewall sitting between the 2 servers. But usually when you can’t connect you’ve either got a firewall issue, or you’ve typed something wrong in the cmd.

One more thing on this before I move on. If it appears to hang instead of returning an error it’s highly likely that it’s a firewall issue. This is the #1 sign that you’re being blocked. So if you hit enter on your cmd and it just doesn’t return, or takes a long time to return, then start looking at firewall issues before you do anything else. Otherwise the cmd should return fairly quickly… usually within 1-2secs.

Ok, we’ve verified basic connectivity, now we need to see if we can actually send mail through that host. We’re going to physically test that in a min, but for now let’s stay with telnet and do a couple tests.
Let’s start with a simple HELLO cmd. In smtp world, we’re going to use EHLO, which means Extended Hello.
First though you’ll need to reset with RSET. Then you’ll run EHLO, then you’ll get your results.
*Note that after each cmd you’ll be greeted by the same unfriendly cursor with no cmd prompt. It’s not thinking, it’s waiting for a cmd from you. Here’s the entire session:
TelnetEHLO
You’ll see that all the responses start with 250. 250 means OK.
For further reading here’s a piece on Extended SMTP: http://en.wikipedia.org/wiki/Extended_SMTP

*NOTE: Of course, you could just have the wrong smtp server name… wouldn’t it be great if it were that simple?

4. Test SMTP Relay through telnet.

Now we’re going to start our relay tests. This will tell us if the server is setup to be able to send mail through the smtp server.
Before we get into the actual cmds though, I want to take a couple mins and explain why we have to do this. This is a beginner tutorial after all, so I like to explain things.

What is SMTP Relay?

Ok, so here’s the scoop, and I’ll be as brief as I can. The spammers of this world like to send emails to as many people as they can. They hide viruses in their emails, porn, and sometimes their just nonsense. Regardless of their goal they have this funny thing about getting caught. It’s actually illegal to spread viruses intentionally through email spam. So what these dastardly doers of dirty deeds do is they look for an open email server they can use to send the spam for them. Once they find an unsuspecting email server out on the internet, they use that server to relay their spam for them. So the way companies fight this is they lock down their email servers so that only certain servers can use them as an SMTP relay. I’m pretty sure MS Exchange comes locked down out of the box these days, so good for MS. You have to specifically open up SMTP relay for any server you want to allow to send mail through your Exchange server. And what we’re going to do now is test whether our server can send email through our email server.

Before each set of cmds you have to reset. So you’ll notice I always type RSET before each set.
TelnetRelayTest

In the above pic, the greens are your reset cmds. Notice there’s one after each set of cmds?
Also, I didn’t EHLO first so I had to do that before I could do anything else. And once I got my response back I ran RSET and then my yellow cmds. My yellow cmds are the ones that actually test the relay. They pretty much explain themselves so I won’t go into any detail.

If your relay cmds fail then perhaps you should talk to your email admin to make sure your server is setup as an SMTP Relay. You’ll send him your IP and he’ll make it happen.
To get out of telnet type QUIT.
And just so there are no misunderstands, here’s the list of cmds from start to finish for this operation.
telnet smtp.domain.tdl
rset
ehlo
rset
mail from:FromEmail@domain.com
rcpt to:ToEmail@domain.com

If everything succeeds then we know that our server is setup with smtp relay through the email server.

5. Send test email through vbs or Powershell.

Now we’re going to complete out smtp test at the Windows level by sending a couple emails without SQL in the picture. Remember, this whole thing started because DBMail doesn’t work, but we still haven’t ascertained whether the issue is with SQL or with Windows. So far though it’s none of these things:
1. Network
2. email server
3. firewall
4. SMTP Relay

Now we’re just going to test email through a mechanism that isn’t SQL so we can make sure there’s nothing wrong with Windows communications or something else we didn’t test for specifically.
I thought about providing both the vbs and the powershell code, but I’m just going to give you the powershell. Seriously, if you’re still using vbs then you deserve what you get. Learn something.
So here’s the powershell.

$smtpServer = "emailserver"
$smtpPort = 25
$emailFrom = "from@domain.com"
$emailTo = "to@domain.com"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "subject" 
$body = "body " 
$smtp.Send($emailFrom, $emailTo, $subject, $body)

I’ll be honest, I pulled that off the internet somewhere a while back but it’s pretty straightforward so I don’t think there should be any licensing issues.

The reason we do this step is because you can test one thing or another all you like, but for me, mail isn’t working until I get an actual email. The previous step didn’t actually send an email. This one does.
OK, so if that worked, then you’ve actually got email flow from your server to Exchange, and from Exchange to your mailbox.
You can stop tshooting Windows now. We’ve verified everything we need and we can now concentrate on this being a SQL problem.

Intermission

Real quick, before we move on to SQL tshooting, I need to go back and cover a couple places errors could have occurred in some of the previous steps. Specifically, firewalls. If you weren’t able to telnet to the smtp server at all, then I said the chances are it’s probably a firewall issue. So I just wanted to discuss firewalls briefly with these next 2 steps. I had to put them somewhere. However, if you were able to connect with telnet then you can skip these steps.

6. Check Firewall blocking application or port.

Briefly, a firewall is an app that controls which ports you can communicate with servers on. In this case, port 25. It’s very likely that your NT guys have locked down the server and are blocking port 25 because they didn’t anticipate you needing to send mail through there. You’ll probably need their help to unblock it because if you do it yourself, it could be part of a policy somewhere and it would just be reset in the future and you’d be right back where you are now. So work with your Windows guys to unblock the port. It’s also possible that the email guys have smtp on another port, so talk to them to and make sure they haven’t switched ports on you.
I’m not really interested in talking you through an intimate tutorial on how to configure Windows Firewall because there’s plenty of that out there. So if you don’t know how to check it out for yourself, then go to your Windows guys. They should know.

Also, the firewall might not be in Windows at all. It could be anywhere between the 2 servers. So you may have to talk to your router guys too to make sure there isn’t an appliance in between somewhere. Remember, telnet doesn’t know where it’s being blocked, just that it’s happening. And to tell the truth, it doesn’t really even know that, it just knows it can’t reach its destination and it ends up timing out.

7. Check anti-virus blocking application or port.

This really belongs with the above step but I thought I should call it out specifically. Many AV vendors have started including their own firewalls that can block apps and ports. So you may see that there’s nothing wrong with Windows Firewall and that there are no appliances in the mix, but you’re still being blocked. So it may be an AV firewall. Chances are you won’t have rights to change the setting and you may not even be able to view it either. So you’ll most likely have to go to your Windows guys for help with this one. And even if you could see it, a lot of times they’re configured at an enterprise level through the AV mgmt. software so again, you won’t be able to do this on your own. I just wanted you to know that this is out there.

8. Run DatabaseMail.exe manually.

We’re at another crossroads here. We’ve decided that there’s nothing wrong with Windows, firewalls, the network, or the email server. The problem is definitely somewhere in SQL. So how do we know where to begin looking? We’ve got to decide if this is a config issue, or something deeper. This step will take us a good way to deciding that.
Let’s take a quick look at what happens. When you send an email through DBMail, it gets put into a table. We’re going to investigate the tables later. That’s why you get a notice that says ‘Mail queued.’. Then SQL calls DatabaseMail.exe to actually send the mail. So we’re going to send the mail manually using the same method so we can get around any config issues that may be getting in the way.
You’re going to go to your Binn (pronounced Bin-N) folder and run the DatabaseMail.exe app. Your Binn folder is in your SQL install folder. I can’t tell you exactly where it’ll be because different versions are slightly different and install locations aren’t all the same. But here’s where mine is: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn

You can double-click the exe and it’ll open a cmd window. There won’t be anything in the window, it’ll be completely blank. And it’ll stay open until you close it. What should happen though is you should see your email queue starting to be processed. If you do start seeing emails being sent then you know for a fact that the issue is something in the config. Email itself is working just fine. What’s broken is how SQL is calling it. Maybe it’s a security issue, or maybe a routing issue.

It happens from time to time that something happens with DatabaseMail.exe. It’s possible it’s not even there. These things don’t happen to themselves so somebody had to delete it. I had a client quite some time ago who deleted it because they wanted to lockdown the instance and they didn’t want even the smallest chance that an email could be sent through DBMail on that box. Then that DBA left and the next guy was left to figure out what happened. SQL is expecting DatabaseMail.exe to be in the Binn folder of the SQL install directory so if it’s not there, then you need to take it from an instance of the same version and put it in Binn.

There could also be permissions issues, which we’ll cover more in detail in the next couple steps.
Of course, if the window just opens and closes really quickly then call it from the cmd line and you’ll get the error.

**I’ve known guys who couldn’t get DBMail working in SQL so they opened DatabaseMail.exe and just left it open to process email. Let me tell you that this is 100% the wrong approach. It can be a stop-gap in a pinch, especially if it gets mail flowing again, but you should never use this as a permanent solution. But you can use it to get you out of the fire while you troubleshoot. You can run this manually to process the email, then troubleshoot for a while, then run it manually again to clear the queue again, and so on. So it’s a good way to keep the lights on.

9. Check DatabaseMail.exe permissions.

If you’re unable to manually send through DatabaseMail.exe then it may be a permissions issue. Your account may not have rights to run the program. And depending on how Windows is setup and your perms on that box, it may or may not be an easy fix. So try to give yourself rights to execute DatabaseMail.exe and if you’re not able to, then you may need to get your Windows guys involved. Of course, if you give yourself perms and you still can’t run it then you may need to logout and login again.

All the same, you shouldn’t really go any further until you can run it manually and get email to flow.

10. Stop/Start DBMail.

Sometimes simply stopping and starting DBMail can make things work again, especially after a config or permissions change. So you’ll probably run this stop and start code after everything you try. Most of the time it won’t make a difference, but I would hate to be tshooting something for 2hrs when I fixed it and didn’t restart DBMail to have the change take effect.

USE msdb ;
GO
EXECUTE dbo.sysmail_stop_sp ;
GO
USE msdb ;
GO
EXECUTE dbo.sysmail_start_sp ;
GO
--Check the status of DBMail
USE msdb ;
GO
EXECUTE sysmail_help_status_sp ;
GO

These are all self-explanatory because they’re named well enough. However, you should note what stopping DBMail does.
Per MSDN:
“This stored procedure only stops the queues for Database Mail. This stored procedure does not deactivate Service Broker message delivery in the database. This stored procedure does not disable the Database Mail extended stored procedures to reduce the surface area.”
Here’s the link to the article:
https://msdn.microsoft.com/en-us/library/ms173412.aspx

11. Change service account to Network Service and back to the domain account it was using.

This is a really big one, and it’s one of those permissions issues I’ve talked about.
What happens is someone (typically a DBA) will go in and change the account that the services are running under and instead of doing it through SSCM (SQL Server Configuration Manager), they’ll do it through Service.msc in Windows. This is the wrong way to change a service account for SQL.
The reason you should go through SSCM is because it’s smart. It knows things about SQL and what the services need, and the state of things. For instance, it knows when SQL is clustered. But for our purposes, there are permissions that an account needs to be able to run SQL effectively, and SSCM puts those permissions in place. You can go directly through Windows if you like, but then you’ll have to go give all the permissions manually, and that’s a ridiculous amount of work when you can just use the tool they gave you.

So if this is the case, if you’ve possibly had a service account change, then that could have stopped DBMail because the new service account doesn’t have the permissions it needs to access DatabaseMail.exe. The easiest way to handle this is to give the Agent service account the proper permissions. And the easiest way to do that is to set the Agent service to run under a different account first. Use something like Network Service, or local system. Then restart the Agent. The Agent service should now be running under the new account. Now, once that’s done, set it to start under the account you had already had in place before and restart the Agent service again. What this does is give you a way to let SSCM set the proper permissions when you setup the start account.

In my years of supporting DBMail I’ve only had to do this step like 3 times. 2 times, this worked exactly as planned. But 1 time, I had to perform this step on the SQL service itself as well as the Agent service. I’ll be honest, I don’t know why, but I wanted you to know that it’s a possibility.

**If you try to use Network Service to startup the Agent service, and you’re unable to, it may be a permissions issue within SQL. So give the Network Service sa in SQL and it should startup just fine. Once you’ve got the services working under the proper accounts again you can remove the permissions from Network Service.

You may need to stop/start DBMail after you bounce the Agent. Mail should start flowing right away if the issue has been cleared.

12. Make sure msdb is owned by sa.

I’m not going to pretend to know what this is about. In my research for my issue I came across a few forum posts that suggested that this may be the issue. In fact, the forum posts say to make sure all system DBs are owned by sa. I’ve never had a system DB not be owned by sa, but I wanted to throw this in there to be complete.

13. Check there isn’t a space after the profile name or the SMTP server name in the mail config.

Something like this should produce an error that the profile doesn’t exist. However, if you’re not paying attention to errors at this stage, which you should be, then you may miss this. But I’ve seen misspellings of the profile name many times and this falls under that category.

14. Test different authentication methods in the mail config.

Depending on how the email server is configured you may need to try different authentication methods to the smtp server.
SMTPAuth
To get to that screen follow this path in SSMS:
Database Mail\Configure Database Mail\Manage Database Mail accounts and profiles\View, change, or delete an existing account

You may need to work with your email admins to make sure you’re authenticating to the smtp server correctly. They may have something special setup.

15. Make sure the profile is set to Public.

You’ll need to make sure the profile is accessible to the public. That’s assuming you want this to be a public profile that is. Even if you’re not using this as a default profile (you’re calling it specifically in your email call), it’ll still need to be a public profile unless you go out of your way to set it up as a private profile for that account.
Here’s where you can manage these aspects of your profile:
Database Mail\Configure Database Mail\Manage profile security

16. Make sure the user sending the mail is either an admin or is in the DatabaseMailUserRole.

The user has to have permissions to call the sendMail SP. So make sure the user account sending the email is either a sysadmin (reserved only for DBAs), or is in the DatabaseMailUserRole role in msdb. There isn’t too much more to say about this really.

17. Check Service Broker is enabled in msdb.

When all is said and done, controlled by Service Broker (SB). So you have to make sure that SB is enabled for msdb.

USE master ;
GO
ALTER DATABASE msdb
SET ENABLE_BROKER ;
GO

Now, you have to have exclusive access to msdb. So you may need to turn off the Agent service. Just don’t do it on a prod box while there are jobs running that you really need.
**NOTE: If by chance msdb were restored from another box, you won’t be able to enable SB like this. You’ll need to create a new SB GUID using the directions in #21 below.


18. Check that DatabaseMail.exe is in the Binn folder.
We mostly covered this in an above step, but it’s worth repeating in case you missed it. DatabaseMail.exe should be in your instance’s Binn folder. If it’s not there then someone has removed it and it needs to be put back. So go to another instance of the same version and copy this app to the Binn folder. Sometimes it’ll be called something different. SQL 2005 comes to mind where it’s called DatabaseMail90.exe. You may need to set permissions for the Agent account once you replace the app, but it depends on your Windows security config.


19. Check for Aliases that don’t belong or are misconfigured.
This one is obscure, but definitely worth checking. This one was the final piece to fixing my issue. Here’s what happened. I’m still trying to figure out why this was done, but still.

The box I had this issue on was a QA box. We’ll call it QA1. There was an Alias setup with the same name, QA1, but it was pointing to the prod box. I don’t understand why someone did this, but it’s there. Removing this Alias allowed mail to flow again. Actually, this is that routing issue I mentioned in one of the steps above. The request is being routed to a different box. There were some other hints that this was the issue. I saw some login failures in the Agent log. I didn’t think anything of it at the time, but now that I see the Alias the picture is clear. The Agent was being routed to a different box where of course you’re going to have login failures. I don’t know if you’ll get the login failures in the log every time you have this issue, but it’ll definitely keep mail from flowing.
Aliases are well-documented so I’m not going to bother explaining it here, but don’t leave this out of your investigation, especially if you’re still not getting an error message.


20. Check the hosts file for entries that may be misconfigured and messing things up.
This one is even more obscure but I’ve seen it once. If the subject of your email is coming from a SQL query, then you may get an error message about it not being able to resolve the loopback or something to that effect. This could easily be because of the Alias issue above, but it could also be that you need to add 127.0.0.1 to your HOST file. The chances that this is your problem are pretty slim, but I’m trying to be complete and list everything I can think of.


21. Re-issue the Service Broker GUID for msdb.
Since we’re dealing with Service Broker (SB) here, then things can go wrong. This one too is obscure but keep it under your hat just in case. Sometimes a DB doesn’t get a unique SB GUID. In this case you can replace the GUID.
You’ll need to stop the Agent service so you can have exclusive rights to the DB so don’t do this at any time when you need jobs to run. But the operation itself only takes a few secs so it’s not too bad.

ALTER DATABASE msdb
SET NEW_BROKER

22. Using GMAIL with MFA enabled

*Thanks for Andrew Coble for this one.

  • First, you need to enable ‘Allow less secure apps’ on your Gmail account. To do this, go to your Google account settings and click on ‘Security’ in the left menu. Scroll down to ‘Less secure app access’ and turn it on.
    • NOTE: If you have 2-factor authentication (2FA) enabled on your Gmail account, you will need to generate an App Password to use with SQL Server Database Mail.
    • Instructions for this are at the bottom of the page
  • Enter the following information when configuring outgoing mail to Gmail:
    Server name: smtp.gmail.com
    Port number: 587
    Enable SSL
    Authentication: Basic Authentication
    User name: your Gmail email address
    Password: your Gmail password OR App Password (required if 2FA enabled on account)
  • Test your email profile by sending a test email. You can do this by right-clicking on your email profile and selecting ‘Send Test E-Mail…’. Enter your email address and click ‘Send Test E-Mail’.

Here are the steps to generate an App Password:
Sign in to your Google Account and go to your Security settings.
Scroll down to the ‘Signing in to Google’ section and click on ‘App Passwords’.
If prompted, enter your Google Account password.
Under ‘Select app’, choose ‘Mail’ and under ‘Select device’, choose ‘Other (Custom Name)’.
Enter a name for the custom device and click ‘Generate’.
Google will generate a unique 16-character password for you. Make a note of this password as you will need it for SQL Server Database Mail.

DBMail System Objects

sysmail_delete_mailitems_sp — Deletes messages from the queue. This is very handy if you’re pushing a lot of test messages into the queue and they’re getting stuck in there. By clearing the queue you can make sure you’re not flooded with 200 messages all at once when you get email flowing again. If I were you, I’d run then when my queue gets too big unless you just can’t afford to lose some of them. Here’s the MSDN documentation for this SP: https://msdn.microsoft.com/en-us/library/ms190293.aspx
** NOTE: You can delete individual messages out of the queue manually.

These are the DBMail tables we’re interested in. Rather than define each one, I’ve included the MSDN doc for them.

sysmail_allitems — https://msdn.microsoft.com/en-us/library/ms175056.aspx

sysmail_event_log — This is where you’ll see your error messages for individual emails that have failed to send. https://msdn.microsoft.com/en-us/library/ms178014.aspx

sysmail_faileditems — https://msdn.microsoft.com/en-us/library/ms187747.aspx

sysmail_sentitems — https://msdn.microsoft.com/en-us/library/ms174372.aspx

sysmail_unsentitems — https://msdn.microsoft.com/en-us/library/ms187817.aspx

Admin

Replication Error: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint.

2 Comments

I was initializing a transactional replication with a snapshot today and I got the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_Table_xxxx”. The conflict occurred in database “DestDB”, table “dbo.MyTable”, column ‘MyColId’. (Source: MSSQLServer, Error number: 547)

This stopped the snapshot from being applied.
I searched for that FK in the DB and it didn’t exist… not on that table, not anywhere.

I’m going to keep this short. The answer is that SQL keeps a copy of FKs in dbo.MSsavedForeignKeys.
So if you get this error, you can go to the subr DB and delete the row from that table and you should be fine.
I had the problem where it kept finding new FKs to error on.
I finally just deleted all the rows out of that table and everything was fine after that.
The actual delete stmt I ran was:
delete dbo.MSsavedForeignKeys where constraint_name = N’FKName’

I hope this saves you a lot of looking around.

Admin, MidnightDBA News, Minion by MidnightDBA, Time-savers

Allow_Page_Locks for Reorgs

minion reindex-01There are many settings that get set one way or another in DBs and in tables. Allow_Page_Locks is one of them that you may not be able to do anything about because your vendor may require it and your situation may require it as well. Normally it’s set to true, but it does get set to false and when it does, it typically needs to stay that way.
The problem is that when you reorganize these indexes that have allow_page_locks = false, then the reorg will fail. But the problem is that if you change it, you may see increased blocking issues. So what is there to do?

Well, the answer is Minion Reindex. We allow you to define pre and post code at the table-level that you can use to switch this option on and then off again when the table is through. And better yet, we even give you the code to discover all of these issues in your database and fix them. In the Minion Reindex download folder you’ll find a Queries folder. This folder has a sql file that you can run and it will insert the table-level exceptions with the proper pre/post code. The precode sets allow_page_locks = true and the postcode sets allow_page_locks = false. This way you can still have the setting the way you need it, and perform your index maintenance too.

And of course, Minion Reindex is completely free so download it now and you won’t be sorry.

Admin, MidnightDBA News, Minion by MidnightDBA, Time-savers

129 of 635

This stat is one of the coolest things you’ll see in Minion Reindex.
FragStats

Gathering fragmentation stats in large databases can take a long time and you have no insight into what’s going on.

With Live Insight, Minion Reindex allows you to see what’s going on every step of the way, including how many indexes you have left to gather stats on.

And we even give you the name of the table and index that’s currently having its stats gathered.  (Our reindexing operations aren’t black boxes. I use this almost every day and I honestly don’t know what we ever did without it.)

NOTE: This feature is turned on by default. Just in case you’ve accidentally turned it off, just make sure that for the current database you have LogProgress = 1 in the Minion.IndexSettingsDB table.

Live Insight…by specific table

It gets even better. You may not be interested in Live Insight for every table.

In this case you can turn off Live Insight for certain tables, or turn it off for an entire database and then turn it on only for certain tables.

This is just one of the many useful features we have in our log.

And don’t forget: you can configure at the database level specifically or use the default DB settings by configuring the MinionDefault database in the Minion.IndexSettingsDB table.

I hope this helps you configure and use Minion Reindex better.

Admin, MidnightDBA News, Minion by MidnightDBA, Time-savers

Order by Index Usage in Minion Reindex

Last week, a user asked if he could order index maintenance operations at the table level, based off of index usage. And of course, the answer to that is definitely yes. We designed Minion Reindex to have flexibility to address custom preferences like this without a lot of hassle, and with no extra jobs.

Minion Reindex already allows table ordering: you can give individual tables a heavier ReindexGroupOrder “weight” (higher numbers are reindexed first). All you have to do is change the order based off of your criteria. We’ve written a script –  MinionReindex-OrderTablesByUsage – that updates the ordering for all tables in a database, based off of usage. You’re welcome to customize this script and change the criteria to whatever you like.

A couple notes on the script:

  • Read the introductory comments for instructions and notes. And of course, review the script well.
  • This might have been easier with a MERGE statement, but we wanted it to be compatible with all versions that Minion supports. The script does an insert or an update based on whether that table is already listed in the “Minion.IndexSettingsTable” table.
  • This script can clearly be modified to meet any number of needs, like ordering tables by row count, or even excluding tables that don’t have enough reads.
  • To use this script, you can either add a job step, or encapsulate it in a stored procedure and add it as a DBPreCode in the Minion.SettingsDB table. It’s that easy.

Feel free to use this script or alter it as you need. And if you make it do something really cool, send it back to us and we’ll add it to the community of scripts on the site. We’ve done some initial testing of this script, but nowhere near the level of testing we put the product through so if you find any bugs let us know. MinionReindex-OrderTablesByUsage

Download Minion Reindex at MidnightSQL.com/Minion

Development

Priority vs. Weight

2 Comments

Ok, so this is actually Sean writing this time.
I thought I’d hit the ground running with a great topic that came up just this morning.

The issue is how do you prioritize tasks in code? So let’s say you’re going to process a list of DBs for processing, but you also want to do them in a specific order. You’ve got 2 choices really when deciding on how to do this: Priority and Weight.

Priority:
This one is probably the most common. This is say when you have a list of objects and you rank them from say 1-10. In this case 1 is the top priority with 10 being the least. This is how a priority system works. Items move up the list to the top position.

Weight:
A weight system works just the opposite way. You give more weight to items by giving them a higher number. This extra weight makes them more important so they get processed first. So in the 1-10 list, 10 would be processed first because it has more weight.

So what’s the difference between these? Well a priority system I think is easier with fairly static priority code. You’re not going to be making any real changes to the priorities or the objects very often.

A weight system is better when your list and priorities are more dynamic.

Let’s take a look at an example:
You want to process some DBs for something. You pull in the list of DBs and you want them in a specific order. So you put them in #DBs with an Ordering col.
If there won’t be any new DBs in the list for a long time, and you’re not likely to change the order, then a priority system is good for you. However, if you’re going to be adding more DBs, and those DBs will possibly even be higher priority than the current ones, then a weight system is best. The reason is that if you’ve already got the number 1-10 populated, and something new pops up in the list that’s higher priority than anything else, you’d have to set that to 1 and then shift all the others down by 1. So what used to be 1 is now 2, what used to be 2 is now 3, etc. However, with a weight system, you can add as many new items as you like, and you just keep increasing the number. So in this case the new item would be assigned 11 because it’s more important than anything else and has more weight.

Of course if you have to arrange the weights of any of the lower ones it does become just a bit more tedious. In this case you’ll have possibly make another sub-group column. This way you can keep your major groups and then just change priorities or weights in the sub group. So let’s say you’ve got DBs grouped from 1-100 using either method, but we’ll say it’s by weight for now. If you have to make one DB a higher weight by one you’d have to shift everything else up one.
However, if you have that DB in a major group of say 10 DBs, and they all have weights within that group then you only have to change the weights within that one group. So whereas you would before have had to change the weights of 30 DBs, now you only have to change under 10.

I tend to use a weighted system more, but I have nothing against a priority system. And I prefer the 2 group system even more.

MidnightDBA News

Well Wasn’t that FUN!!!

2 Comments

Imagine my surprise this evening when Jen got a txt from Denny Cherry asking if we were ok after my latest blog post here. I said, what blog post?
Apparently my account has been hacked and someone thought they were being funny. However, looking at the wording in that post, it sounds like a bot to me.

So anyway, I’m fine, nothing has happened, except I about broke a rib running over here to change my password.