SQL Server Availability Groups and SQL Server Reporting Services – A Brief Rant Triggered By Someone Failing Something Over Without Understanding The Full Implications, Or, Pay Attention To Your DBA Because He Knows This Stuff And That’s What You Pay Him For

Is the headline longer than the body? Not now I’ve included the fix for this situation as well as the initial rant. Maybe next time.

Yes, you may have an availability group – well done – and you may have installed SSRS on both servers. But you’ve only set up the reporting application to point to one of those? And you’ve given the link https://<<Listener_Name>>/reports out to the users? Head/desk. I told you at the time that SSRS doesn’t play nicely with AGs. [Nearly misposted as SSRS doesn’t play nicely with SSRS, which, while valid, isn’t the point here…]

Here’s what you need to do to fix this / make sure it doesn’t happen:

  1. Install the reports on the active server
  2. Failover
  3. Install the reports on the now-active server
  4. On each server, change the report data source to use the listener as the source, not (localserver)
  5. On each server, set up the security group(s) and permissions your users require

[Disclaimer – this worked for this situation. It might not work for yours. Support here is worth what you paid for it. Don’t Drink and DBA.]

Only then will you be able to get away with distributing the AG Listener as the SSRS URL. I said this much at the time… Yes, I know things were a bit…difficult back then, but still. Oh well.

Where’s my LART? Or my coffee? Either will do

Posted in SQLServerPedia Syndication | Tagged , , | 3 Comments

SQL Server Error 18456 Severity: 14 State: 73

A question asked on one of the forums today wasn’t easily answerable by Googling. Summary of the question “I have error 18456 State 73 – why?

Google seemed remarkably quiet on the subject of that particular state code. Even Aaron Bertrand’s list of causes of state codes for SQL Server error 18456 missed this one.

However, some searching did find a link to what appears to be some in-depth VMWare VSAN training documentation that includes that error in some logging information, which made me wonder if it was related to the error 18456 state 38 that followed. The log excerpt in question:

2017-08-17 23:16:47.690 spid52 Setting database option SINGLE_USER to ON for database for 'tpcc'.
2017-08-17 23:18:28.490 Logon Error: 18456, Severity: 14, State: 73.
2017-08-17 23:18:28.490 Logon Login failed for user 'SQL2016-SA-03\Administrator'.  Reason: Failed to open the database 'tpcc' configured in the session recovery object while recovering the connection. [CLIENT: ]
2017-08-17 23:18:31.330 Logon Error: 18456, Severity: 14, State: 38.
2017-08-17 23:18:31.330 Logon Login failed for user 'SQL2016-SA-03\Administrator'.  Reason: Failed to open the explicitly specified database 'tpcc'. [CLIENT: ]

The full text of the error in the SQL Server event log is “Failed to open the database ” configured in the session recovery object while recovering the connection”. That, combined with the error state 38 – Failed to open the explicitly specified database – led me to think that a connection had been dropped and the database dropped or made otherwise unavailable before the user tried to reopen the query session.

A test

Fire up SSMS, and create a database:


In another query window, run the following:

USE Test73;

Make a note of that SPID, and feed it into the following SQL that you run in the first window:

KILL 55;

Now go back to the second query window, and hit F5 to refresh / rerun the batch.


The error text:

TITLE: Microsoft SQL Server Management Studio

The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476


Cannot open database “Test73” requested by the login. The login failed.
Login failed for user ‘SomeDomain\RUSHTONT’. (Microsoft SQL Server, Error: 4060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4060&LinkId=20476

To confirm this series of errors, check the SQL Server Error Log in SSMS, and you’ll see:

Suspicion proved!

Now all we have to do is test to see if there are other possibilities for this issue, but I need to step away from the keyboard…

Posted in SQLServerPedia Syndication | Tagged , , | 1 Comment

Longrunning Query – xp_cmdshell?

Flicking through the ol’ Solarwinds activity monitor, I spotted this one day:

Longrunning WTF 01 - SolarWinds Top SQL Statements

That four day purple thing on the right? Not good.

(The remaining images are from a reconstruction, partly to protect the not-so-innocent, and partly because the first job was (as always) fix the finance department’s server…)

Running Adam Machanic’s (b|t) excellent sp_WhoIsActive gave us the following:

Longrunning WTF 02 - sp_WhoIsActive output

Except, in reality, the first column was over 3 days, and the wait_info & CPU fields were waaaay higher. And the login_name field gave me the name of the perpetrator.

I wanted to know what he was up to, but the sql_text field only gives “xp_cmdshell”, not anything useful that might help to identify what went wrong.

So we have to go to Taskmanager on the server. On the “Process Details” page, you can select which detail columns you want to see. We want to see the Command Line, as that’ll tell us if it’s some manually-launched batch job that’s failed or something else going wrong.

Longrunning WTF 03 - TaskManager Process Page Columns

Click on OK, and find the process (in this case, the only process) named “cmd.exe” that’s been launched by the SQL Server service.

Longrunning WTF 04 - Task Manager Process Line

Oh my. Launching “calc.exe”? Why?

But this shows just what can go wrong if you don’t think about what you’re doing when launching xp_cmdshell – it can (and does) give you the ability to launch anything – and the problem with that is that you can’t control applications with a User Interface when they’re launched from a service account on a server – all you can do is hope you find what you’ve done, and that you can kill it.

…which I could, in this case. Fortunately, the only harm in this case was a red face (not mine, for a change).

Posted in SQLServerPedia Syndication | Tagged , , , , , | 1 Comment

A Problem with Boolean Logic

Your requirement: to find the things where either A or B is true and C is true. Your first draft might be this:


(field, table, function, logic all anonymised to protect the perpetrator)

What’s the problem? Operator Precedence, that’s the problem.

Operator Precedence?

The order in which calculations are done – not just reading from left to right, but remembering that things like multiplication and division happen before addition and subtraction. My son tells me that kids nowadays are being taught something called “BIDMAS” – which stands for “Brackets, Indices, Division, Multiplication, Addition, Subtraction”. Or it can be BODMAS – Brackets, Operations, Division… (Operation is a fancy new way of describing indices – ie xy)

Unsurprisingly, there are similar rules for Boolean operators. (Boolean Operators LogicaL Order of CalculationS? BOLLOCS? So close…) And these all mix in together, with AND being at the same level as multiplication, and OR being at the same as addition. Microsoft has kindly provided a list of the operator precedence rules it uses in SQL Server.

So What? I hear you cry

Well, we had a malfunctioning server. Running sp_WhoIsActive against the server showed 400-odd queries blocked by five instances of a malformed bit of code like the statement at the top of this post.

We, like many places, have a variety of third-party applications that allow users to write their own queries at a high level, and then the applications fill in the blanks, and add all the close brackets and things, to make the query actually parse… What these apps don’t do, of course, is fix the broken logic for you. Anyway, our Joe Punter had been asked to run a query to find customer results (or something) where condition A or condition B were true, and condition C was also true. And he had come up with the A OR B AND C logic above, and was puzzled when he fired off the report that it didn’t come back instantly, so he fired it off again. And again. And a couple more times for good luck.

Reproducing the Problem For Fun & Blogging Purposes

We are using the AdventureWorks Database, as that’s easy to get hold of. (NB: MS has moved all this stuff to github – you may need to update your bookmarks accordingly.)

This query below is a cut-down hack that illustrates the problem. In the data sense, it’s garbage, but it illustrates the problem albeit with several orders of magnitude less data.

       Sales.SalesOrderDetail AS sod,
       Sales.SalesOrderHeader AS soh
       soh.OnlineOrderFlag = 1
    OR soh.BillToAddressID = soh.ShipToAddressID
   AND soh.SalesOrderID = sod.SalesOrderID;

The query plan generated is…unpleasant


As you can see, the query plan is dominated by that Nested Loop operator. Let’s have a look what’s going on there:



Let’s rewrite the query properly (ie add a bracket or two):

       Sales.SalesOrderDetail AS sod,
       Sales.SalesOrderHeader AS soh
       (soh.OnlineOrderFlag = 1 OR soh.BillToAddressID = soh.ShipToAddressID)
   AND soh.SalesOrderID = sod.SalesOrderID;

And the query plan:

Spot the difference.

Yes, the query returns different answers – that’s kinda the point inasmuch as the original query would not have returned what the user wanted… But we can now see a more appropriate join operator, and one that’s not taking all the query time.

The moral of the story? Get your logic right, and your server won’t fall around your ears.

Posted in SQLServerPedia Syndication | Tagged , , , , | 2 Comments

T-SQL Tuesday – Bad Habits – Fixed by SQLPrompt

TSQL2sDay150x150T-SQL Tuesday again. And it’s been a few months since I last blogged, let alone for this blog-party, but, hey, I’m here now. This time, Aaron Bertrand (blog|@aaronbertrand) has given two options – and, given the amount time I’ve been spending lately talking about, seeing and performing in operas, I thought I would talk about T-SQL. And, yeah it’s another redgate-related SQLPrompt Post. <disclaimer/disclosure>They don’t pay me, they’ve not asked me to write this, but they do provide me with software, and I would buy this tool myself if I wasn’t on the FoRG programme…

Bad Habits

Aaron has been busy collecting a list of many many bad T-SQL / SQL Server habits, and some of them are scary.

The one I’m writing about – Using AS instead of = for Column Aliases – is the subject of a holy war, despite being mostly seen as stylistic.

Some of you DBA weirdos insist on writing code like

SELECT x = col1...

rather than the proper, ANSI-standard form of

SELECT col1 AS x...

Even though using “AS” conforms with the ANSI standard, Aaron is against it. He’s wrong, and I guess there are plenty of other deviants out there, but there are plenty of us right-minded people who prefer the proper version.

If you’re working in a big team, though, you can run into real problems where there’s a mix of styles in use.

SQLPrompt’s “Code Smells”

Redgate’s SQLPrompt has new functionality to identify what it calls “code smells” – bits of non-standard functionality, or deprecated usages. It highlights these smelly lines of code with a green wavy line, like you see under dodgy grammar in Word.

If you put your cursor into the green wavy line, the smelly bit is highlighted and a new blue icon appears in the left hand gutter. Click on that, or press CTRL, and up comes a window that describes the problem, and gives you a link to redgate’s documentation about it. OK, it’s a bit sparse, but that’s not the point of this post.

De-stinking code

If you’ve got a lot of code to review, and want to fix this easily, fortunately you can apply this fix automatically using SQLPrompt as part of its reformat sweep.

You’ll need to check the “Apply column alias style” box, and then pick the right style (above), but that’s all you need to do. (OK, if you’re a deviant like the guy I work with, you can set it to use one of the other WRONG formats, but don’t do that because it’s WRONG…)

A quick CTRL+K, CTRL+Y later, and you get properly formatted code:

And as for my strange friend, if he wants to see it in the wrong format, he can set up his own preferences. Or he could, if he had a licence for SQLPrompt, which he doesn’t… Yet…

Posted in T-SQL Tuesday | Tagged , , , | Leave a comment

SQL Server Installation Failed Due to Pending Restart of Server?

Incidentally, the sort of problem I’m discussing here is why I (almost) always copy the patch/servicepack/media to somewhere local – each time you run a servicepack or cumulative update, the whole package decompresses to your hard drive, runs, and then deletes itself afterwards – not something that you want to wait for several times…

Patching some SQL Servers today, I had a couple of failures because there was a reboot pending (presumably a hangover from the Windows patching that had gone before).

Reboot the server, restart the installation, and get the message again. Swear. Remember there’s a registry key value you can rename to bypass these checks. (It’s “HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations” – rename it to, say, PendingFileRenameOperationsBackup and have another go. Remember to change it back when you’re done.)

Except in this case, there was nothing there… The key value didn’t exist. Or if it did, I couldn’t see it, which is a bit awkward as I’m supposed to be administering these servers. muttermutter Anyway.

If you’re running a normal SQL Server setup, you can do a lot from the command line – and this is all documented elsewhere, and I’m not going to rehash it.

However, as mentioned at the top (you did read the small print, yes?), the typical service pack / cumulative update / hotfix is distributed as a self-extracting executable, called something like SQLServer2014-KB4032541-x64.exe that launches something else.

Fortunately, if you run your executable with the /? parameter, you’ll get a helpful list of what parameters you can pass into the service pack:

C:\<<somepath>>\ >SQLServer2014-KB4032541-x64.exe /?
Microsoft (R) SQL Server 2014 12.00.5000.00
Copyright (c) Microsoft Corporation.  All rights reserved.
setup.exe /[option]={value} /[option]={value} ...
ACTION                       Specifies a Setup work flow, like INSTALL,
                              UNINSTALL, or UPGRADE. This is a required
ALLINSTANCES                 Specifies that all instances are to be included
                              in the Setup operation. This parameter is
                              supported only when applying a patch.
CLUSTERPASSIVE               Specifies that SQL Server Setup should not manage
                              the SQL Server services. This option should be
                              used only in a non-Microsoft cluster environment.
ENU                          Use the /ENU parameter to install the English
                              version of SQL Server on your localized Windows
                              operating system.
ERRORREPORTING               Specify if errors can be reported to Microsoft to
                              improve future SQL Server releases. Specify 1 or
                              True to enable and 0 or False to disable this
HELP                         Displays the command line parameters usage
IACCEPTSQLSERVERLICENSETERMS By specifying this parameter and accepting the
                              SQL Server license terms, you acknowledge that
                              you have read and understood the terms of use.
INDICATEPROGRESS             Specifies that the detailed Setup log should be
                              piped to the console.
INSTANCEID                   Specify the Instance ID for the SQL Server
                              features you have specified. SQL Server directory
                              structure, registry structure, and service names
                              will incorporate the instance ID of the SQL
                              Server instance.
INSTANCENAME                 Specify a default or named instance. MSSQLSERVER
                              is the default instance for non-Express editions
                              and SQLExpress for Express editions. This
                              parameter is required when installing the SQL
                              Server Database Engine (SQL), Analysis Services
                              (AS), or Reporting Services (RS).
QUIET                        Setup will not display any user interface.
QUIETSIMPLE                  Setup will display progress only, without any
                              user interaction.
SQMREPORTING                 Specify that SQL Server feature usage data can be
                              collected and sent to Microsoft. Specify 1 or
                              True to enable and 0 or False to disable this
UIMODE                       Parameter that controls the user interface
                              behavior. Valid values are Normal for the full
                              UI,AutoAdvance for a simplied UI, and
                              EnableUIOnServerCore for bypassing Server Core
                              setup GUI block.
X86                          Specifies that Setup should install into WOW64.
                              This command line argument is not supported on an
                              IA64 or a 32-bit system.
Press any key to exit...

However, nothing here is immediately helpful. But then there’s this post that talks about the repeated-reboot-pending-failure for a standard SQL Server setup, and I found myself wondering if you could use the same undocumented /SkipRules parameter with a value of “RebootRequiredCheck”.

C:\<<somepath>>\ >SQLServer2014-KB4032541-x64.exe /action=Patch /SkipRules=RebootRequiredCheck

You can. No reboot check performed. Patching can continue. Hurrah.


Undocumented parameters are undocumented. Which means they’re not supported. You have been warned.

Posted in SQLServerPedia Syndication | Tagged , , , | Leave a comment

Skype for Business – SQL Server Frustrations


The environment is new and secure.

Assumptions: You are a DBA who knows how to build WSFCs and AGs. If you need help with that, then you’re in the wrong place for this post.

The Windows 2016 / SQL Server 2016 environment has been built, SQL Server 2016 Enterprise Edition installed, the Windows Failover Cluster configured, HA has been enabled on the SQL Servers, the Skype for Business server software has been installed (but not configured) and all is well.

Now to configure / create the Skype For Business topology. How hard can it be?


This is being done from memory, so the actual implementation detail may be a little bit hazy, and I haven’t got any screenshots or actual error messages as this was done on someone else’s computer.

What went wrong, though – that’s real. And the reasoning is also as near as I can remember.

Creating the Topology

Configuring for Availability Groups

Launch the Topology Configuration wizard/app. By default it wants to go with mirroring, so you tell it to use Availability Groups. This causes the app to ask up front for the availability group name, which doesn’t exist, and then ask for a server name.

Don’t assume that this is a mistake and give it the server name. Alternatively, don’t get confused and give it a server name. You’ve got to get this right up front. If you don’t, you’ll have to strip out the whole thing and start over.

Use PowerShell rather than the GUI

The GUI feels nicer (barring obvious confusion above), but doesn’t adequately report error / warning / problems. We were experiencing problems with the installation, and all the messages that the skype engineers were seeing led them to view it as a “problem with the database server”.

Using the Install-CsDatabase command gives a whole heap of more useful information, including showing that a connection was made to the server and configuration settings (for database file paths) were read correctly. It’s not a problem with the server.

Oh, and use the -UseDefaultSQLPaths parameter, assuming you’ve configured your server up correctly, as it’s easier than trying to figure out the various other path options.

What $ share?

The output from Install-CsDatabase showed that the installer was attempting to copy database files to the appropriate locations, but had translated the value retrieved from SQL Server, say, E:\MSSQL\SQLData to \\SkypeServer1\E$\MSSQL\SQLData – the transaction log path had suffered similar indignities. Yes, that’s using the $ / “admin” shares. The problem is that, for various security reasons, these shares have been removed.

If you try creating the blank databases, the installer complains that the database is there, but it’s version 0, and that the installer can’t upgrade from version 0 to version 12. So drop those databases as they’re not doing any good.

The only way forward on this appeared to be to get an exemption logged and create those shares. Installation can (finally) proceed. Hurrah.

When the databases have all been created, create AG, failover, rerun topology-push to create users & jobs on the secondary, and failback.

That was a painful afternoon.

Still, all is not entirely well.

The Next Day

The following day, you’ll notice that various SQLAgent jobs have failed. Checking the server, you will notice that there are the following jobs:

  • LcsCDR_Purge
  • LcsCDR_UsageSummary
  • LcsLog_Purge
  • QoEMetrics_Purge
  • QoEMetrics_UsageSummary

These jobs have been set up in a sensible way – step 1 is a check to see if the database / server is the primary or the secondary, and step 2 actually does the work (if this is the primary). Here’s that step 1 in full:

declare @_MirroringRole int
set @_MirroringRole = (
    select mirroring_role
      from sys.database_mirroring
     where database_id = DB_ID('LcsCDR')
if (@_MirroringRole is not NULL and @_MirroringRole = 2) begin
    raiserror('Database is mirrored', 16, 1)
else begin
    print ('Database is not mirrored')

That’s great, if you’re using mirroring. But we’re not. And Skype-for-Business *knows* that we’re not, as we told it that right up front. What this check should be looking at is the sys.dm_hadr_database_replica_state DMV and working against that. This code seems to work:

declare @_AGPrimary bit
if exists (select * from sys.dm_hadr_database_replica_states where database_id = DB_ID('LcsCDR'))
    set @_AGPrimary = (SELECT is_primary_replica FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID('LcsCDR') and is_local = 1)
    IF (@_AGPrimary = 0)
        RAISERROR('Database is AG secondary',16,1);
        print ('Database is AG primary');

Your choice – either replace the existing step 1, or add this code to it, and the jobs now work. Depending on how nit-picky you are about these things, you might want to change the two references to ‘LcsCDR’ to match the database mentioned in the job… Given that the databases are / should be in the same AG, though, this might be overkill/paranoia. I’m paranoid. It goes with the territory.

Creating the Listener – Active Directory Permissions

Stepping back a bit, one other thing that bit us was that we had problems creating the availability group listener – the cluster didn’t have the rights to do this. This is fixed by either:

  1. (Preferred) granting the failover cluster virtual server object permission to create objects in the OU in which it lives.
  2. (alternatively, should work, untested by me) create the listener computer object first, and grant the failover cluster virtual server CNO rights to control / edit that object.

All done

The engineers can now carry on with configuring / building out the skype-for-business environment. Rather them than me. I need a beer after all that!

Posted in SQLServerPedia Syndication | Tagged , , , , | Leave a comment