Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.

Self-Blocking SPIDs

My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).

There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.

DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.

SystemCenterReporting Reindex Script

Can be found here. It was referred to here and here, however, the links within those pages are no longer extant.

You'll have to register, and I give no warranty as to how workable this is: however, it's taken me half an hour to find the scdw_reindex1.zip or the scdw_reindex.zip file, so I thought I'd link.

State 38

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

And the Application Event log detail generally shows something like:

In Bytes

0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..

Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.

The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.

A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.

"I can't take this database offline!"

An anguished cry, which I have heard twice today. The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database." One generally ends up with 'Database is in transition...Error 952' when trying to ascertain what the databases is up to, and very little seems to have an effect, including restarting SQL in some cases: and that assumes that restarting SQL is even an option.

Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.


USE master

GO

ALTER DATABASE databasename

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional

SQL Configuration Manager Suddenly Unavailable

Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.

Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"

Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.

They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

And running this command in cmd fixed the problem. Phew.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"

Oooh! Squee! New Tool

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer.

Tells you what could be improved in your installation, and how to go about it

• Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
• Determines if the configurations are set according to the recommended best practices
• Reports on all configurations, indicating settings that differ from recommendations
• Indicates potential problems in the installed instance of SQL Server
• Recommends solutions to potential problems

Sweet. Note: there have been some problems with installing it owing to Workgroups problems and Kerberos issues. Solution here.