SQL Agent Connection Failure after Cluster Reconfiguration

We had some unfortunate event befall one of our SQL clusters lately, and it’s led to some interesting fallout.

First, the TempDB LUN was unpresented accidentally during some SAN maintenance.  As I’m not a SAN admin, I’m not yet sure what all happened with that, but of course SQL Server crashed immediately.  It also appears that the other DB LUNs were briefly unavailable to the server, which is where things get interesting.

We had a new TempDB LUN presented in reasonably short order, and SQL Server was running again without any apparent problems.

Then patch week came.  When the active node rebooted after patching, SQL Server would not start on the secondary node.  Which meant SQL Server was offline pending manual intervention.

It took awhile to figure out why SQL Server wasn’t starting:  all of the role resources were online, and everything looked fine.  These errors in the event log gave us some clues:

[sqsrvres] CheckAndChangeVirtualServerName: Could not obtain the Virtual Server Name (status 138f)
[sqsrvres] GetVirtualServerName, Unable to obtain next Resource from Cluster Resource Enumerator. Error: 0.

Those are both event ID 19019.  So I wondered:  what might have changed about the cluster resources?

The answer?  Dependencies.  When the LUNs were unpresented, all of the dependencies for those also disappeared.  Add those dependencies back, such that the SQL Server service resource depends on the appropriate resources (drives, for instance):

You’ll also want to make sure the appropriate network names are set as dependencies.

Who is using this database? Is it in use at all?

This is a question we get as SQL Server DBAs all the time:  is this database in use?  Most of us are in the habit of putting this question back on the application owners, or of using a simple scream test to see if it’s important to anyone. 
Until SQL Server 2008, there weren’t a lot of great options:  triggers would work, but they have a lot of downsides, and they’re reasonably resource-intensive for busy servers.
Extended events offer a much better solution, and we’ll use this question as a way to ease into their use; this will be a beginner’s introduction to SQL Server extended events (on both SQL 2008 R2 and SQL 2012).  We’ll end with a pretty cool (and portable) solution to the question, “is this database in use?”

SQL Server Reporting Services Service Start Timeout

SSRS will often timeout when starting under Windows Server:

The error is:  Windows could not start the SQL Server Reporting Services (MSSQLSERVER) service on local computer.  Error 1053:  The service did not respond to the start or control request in a timely fashion.

This problem is detailed here: http://support.microsoft.com/kb/2745448, and it's a result of the SSRS service checking for certificate revocation lists (CRL).  If you're like most people, you've got a firewall blocking Internet access from your server, so each call for a CRL has to time out before SSRS will start.

The above KB article mentions a handful of ways of dealing with this problem; the most-frequently used method is to increase the service start timeout in the registry.  This works, but it requires a server reboot.  And it doesn't really get to the root of the problem, that SSRS is taking a long time to start.

Now, generally a CRL is a good thing, even if only in theory.  But if your server can't get to the Internet, anyway, there's precious little danger of it getting to a revoked certificate.  After all, you're not using the server to browse the web.  Right?

So:  disable CRL checking within SSRS.  This is done with the generatePublisherEvidence element in the ReportingServicesService.exe.config file.

By default, this file is in the <drive>:\Program Files\Microsoft SQL Server\MSRSxxx.MSSQLSERVER\Reporting Services\ReportServer\bin directory.

To disable CRL checking, add the following just before the </runtime> tag at the bottom of the ReportingServicesService.exe.config file:

<generatePublisherEvidence enabled="false"/>

If SSRS is slow to start as a result of CRL checking, this will dramatically speed the service startup.  One benefit to doing this, too, is that it doesn't require a server reboot or anything to take effect:  it'll work at the next SSRS start.

Creating a Self-Signed SSL Certificate in Windows without IIS (for SSRS, for instance)

Sometimes you have need for a SSL certificate on a Windows server when you don't have IIS installed.  Like when you want to install SQL Server Reporting Services (SSRS).

You could use OpenSSL, or download the 1+GB Windows SDK to get the makecert utility.  But those are troublesome, each in its own way.

Happily, it's still possible to use the IIS 6 resource kit's SelfSSL, even without having IIS installed.  And that's only a 5MB download.  Plus, you can install only the SelfSSL piece, which is something like 50kB.  Much, much better.

So, download it from this link.  Install it on the server you want, and then run selfssl as below:

Note two things:

1. Run the command prompt as administrator
2. You'll get an error, on account of not having IIS running on the server.

The error is OK:  the certificate is installed in the computer personal certificate store.  You can view it this way.

If trust is important on the server, you can use the /T switch for selfssl, and it will automatically add the certificate to the trusted root CA certificate folder, as well.

0x80004005 error when launching Windows Store in Windows 8.1

I ran into a problem recently that really had me scratching my head for awhile:  when I launched the Windows Store on Windows 8.1 with a new user, the store itself failed with an error.  The error message in the Store app read, "We weren't able to connect to the Store.  This might have happened because of a server problem or the network connection timed out.  Please wait a few minutes and try again.  (0x80004005)."

The annoying thing about this (besides it not working) was that it pretty clearly wasn't trying to access the store:  the app itself was failing before even having the chance to time out.

This error persisted through reboots, and it only affected a new user.  Creating another new user (local or MS account) had the same effect:  the store errored out.  New apps that attempted to install as part of a sync from another computer too, would fail in installing.

Scripting SQL Server Error Log Location in TSQL Queries

While it's possible to view the SQL Server error log location in the SSMS GUI, sometimes you want to build a script that'll write a log to the same directory as the error log; we don't want to have to modify our script for every server it runs on.

Enter the ServerProperty function.  There is at least one undocumented server property 'errorLogFilename' that will give us the full path to the SQL ERRORLOG.  This is nice, as we can simply run this to return that:

SELECT SERVERPROPERTY('ErrorLogFileName');

Executing SQL Server Batch Statements Within One Exec(sql) Call

When you need to GO in your batch

Sometimes you'll run into a situation where you want to script a series of SQL batch statements dynamically.  For instance, you might want to create a schema, then create a user to use that schema, then populate that schema with tables, and do that in each database that runs on a SQL instance.


If you weren't wanting to do this dynamically, you'd simply do something like this:

use [dbname];
go
Create schema [schema_name];
go
create user [username] for login [loginname] with default_schema=[schema_name];
go

That's not that big of a deal, really.  But if we want to create a cursor to loop through all databases and run this SQL dynamically, we have a problem:  we can't have the create schema statement in the same batch as the USE command, so we don't end up creating the schema in the correct database.

Configuring a SQL Server Email Alert for SA Login Failures

SA Login 

SA Login authentication failures, essentially, should never happen. Mostly that's because we generally shouldn't be using the SA account, but rather should have dedicated application logins with appropriate privileges. Having those logins use Windows authentication instead of SQL authentication further removes SQL Server from the authentication process, which is even better.

So if the SA account isn't disabled, I want to know when there's an SA login failure.
[Generally, I want to know when there's an SA login success, as well, but we'll focus on the failures, here, because it's really low-hanging fruit.]

SQL Server Alerts

SQL Server Alerts are a very light-weight notification tool for lots of SQL Server events.  Brent Ozar has a nice run-down of some basic alerts that you ought to consider implementing; we'll add this one as another.

It's possible--and frequent--to address this problem by using login triggers.  That is certainly a valid, functional, and well-worn method.  Login triggers can introduce problems, though, especially when we have forgotten that they exist.  Moreover, this is what alerts are for, and there's something appealing to the simplicity of it all.

Importing a .SAS7BDAT file into SQL Server Using SSIS

I was tasked with importing a .SAS7BDAT file into SQL Server the other day, and there were enough non-intuitive pieces to it that I thought I'd write down the steps while they still were fresh.

Some searching shows it's a problem with several solutions, but most of what shows up seems unnecessarily complicated, at least, for someone who's used to using Integration Services.  It turns out that there's a very easy method for accessing the data in one of these files, and it doesn't require a SAS client.  It does require a download from SAS, but that is freely available.

Scheduling a SQL Agent Job to Run on a Calculated Day that isn’t included in the Regular Scheduler Options

I’ve had a hard time coming up with a title for this post, because while the concept is easy to understand, it’s difficult to summarize.
We refresh one of our development instances on the first Monday of each month.  That’s an easy thing to schedule, as it’s an option within the SQL Agent scheduler:

We want to send a notification to the affected users beforehand, however, and since the refresh occurs in the wee hours of the morning, we can’t send out that alert on Monday.  It has to occur on the Friday before the first Monday. 
That, friends and neighbors, isn’t a built-in option.
It’s a little trickier (though in the end, not terrible) because the Friday before the first Monday could be in this month or in next month.