Tag: SQL

SQL August 9, 2013

SSMS – Index Outside The Bounds Of An Array

Hit a strange issue this morning. I was working with one of the network guys testing out connecting to SQL behind an F5 load balancer, something I’ve done multiple times previously.

I was able to connect using SQLCMD, Invoke-SQLCmd and using a UDL source, but for some reason, every time I tried to connect using SSMS I would get an error stating that an “Index was outside the bounds of an array”.

Advanced Information for Error

A little research showed that this error cropped up when trying to connect to SQL 2012 from an earlier version of SSMS. This wasn’t the case here though, everything was SQL 2012, and I was able to connect to the server directly without any problems, it was only an error when going through the F5 VIP.

After a little work and research with the network admin we discovered that OneConnect was causing the issue. It was attempting to pool connections and was causing the problem, turning it off fixed the issue entirely.

Just something to keep an eye out for if you use F5’s to handle load balancing or DR client redirection.

SQL June 12, 2013

Formatting Number Output

I’ve been working with some large data load processes recently and have been dumping upwards of a billion records into tables. As these are not instantaneous loads I found myself wanting to keep an eye on how much data was loaded.

This is nice and easy to do using the sys.partitions system view. Here’s an example from AdventureWorks

SELECT t.name, rows
FROM sys.partitions p
   INNER JOIN sys.tables t
       ON p.OBJECT_ID = t.OBJECT_ID
WHERE p.index_id IN (0,1)
ORDER BY rows DESC;

This is all good and well, but when you start getting to large values on the tables it becomes a little difficult to tell the difference between 31 million and 312 million. That’s when I resort back to using commas. Unfortunately SQL Server does not make this easy, although it can be accomplished with a couple of converts.

SELECT t.name, substring(convert(varchar, convert(money, rows), 1), 1, len(convert(varchar, convert(money, rows), 1))-3)
FROM sys.partitions p
   INNER JOIN sys.tables t
       ON p.OBJECT_ID = t.OBJECT_ID
WHERE p.index_id IN (0,1)
ORDER BY rows DESC

This is still a little arduous to type, so I created a function to do the work. Now it’s just a case of calling that function whenever I want to have the numbers presented to me in a much more readable format.

CREATE FUNCTION dbo.fnNumberComma (@iVal bigint)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @oVal VARCHAR(30)
SELECT @oVal = SUBSTRING(CONVERT(VARCHAR(30), CONVERT(money, @iVal),1),1,LEN(CONVERT(VARCHAR(30),CONVERT(money,@iVal),1))-3)
RETURN (@oVal)
END
GO
SELECT t.name, dbo.fnNumberComma(rows) as [RowCount]
FROM sys.partitions p
   INNER JOIN sys.tables t
       ON p.OBJECT_ID = t.OBJECT_ID
WHERE p.index_id IN (0,1)
ORDER BY rows DESC;

SQL March 6, 2013

Datatypes and xp_fileexist

I was using xp_fileexist to perform a couple of file and folder checks and ran across one of the things that you have to be careful of when using undocumented extended stored procedures.

In this case when I was trying to insert the results into a table variable I was getting an error:

“Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.”

That is particularly unhelpful. You can reproduce this error yourself by running:

DECLARE @Folder NVARCHAR(MAX) = ‘C:Backup’
DECLARE @FolderCheck TABLE
    (
      FileExists BIT ,
      FileIsDir BIT ,
      ParentDirExists BIT
    );

INSERT  INTO @FolderCheck
        ( FileExists ,
          FileIsDir ,
          ParentDirExists

        )
        EXEC MASTER.dbo.xp_fileexist @Folder;
SELECT  FileExists ,
        FileIsDir ,
        ParentDirExists
FROM    @FolderCheck;

In a basic troubleshooting effort I tried to run the xp_fileexist without the insert into the table variable and got another error, this one being a little more helpful at first appearance.

DECLARE @BackupDirectory NVARCHAR(MAX) = ‘C:Backup’;
EXEC MASTER.dbo.xp_fileexist @BackupDirectory;

“Msg 22027, Level 15, State 1, Line 0
Usage: EXECUTE xp_fileexist [, OUTPUT]”

I knew that I was passing in the variable and it was valid, this just didn’t make a lot of sense. I decided to change the datatype of the variable in the hopes that it would work:

DECLARE @BackupDirectory NVARCHAR(8000) = ‘c:BackupNewFolder’;
EXEC MASTER.dbo.xp_fileexist @BackupDirectory;

“Msg 2717, Level 16, State 2, Line 2
The size (8000) given to the parameter ‘@BackupDirectory’ exceeds the maximum allowed (4000).”

So, finally an error that gave me actionable information. By changing the length of the variable to a nvarchar(4000) I was able to get the results I was looking for.

This is just one of those examples where you have to be careful what you are playing with and play around to get what you are looking for. Undocumented xp’s can be really useful, but troubleshooting them can also be a nightmare.

Installation, SQL November 15, 2012

Issues With Slipstreaming Service Pack 2 on to SQL 2008 R2

One of the great things Microsoft introduced a few years ago was the ability to slipstream service packs on to the install media of SQL 2008 and 2008 R2. It was a bit of a painful experience to do, but the results were great as it made deployment of standardized builds a breeze, and when you also added cumulative updates to the mix it became ridiculously easy to ensure every new SQL instance was at the right revision of the product.

Slipstreaming has gone by the wayside with SQL Server 2012. Now, instead of extracting the service pack and doing a bunch of file work you just have to stick the service pack into a folder and add the PCUSOURCE information in your default configuration.ini file.

How to slipstream a service pack into SQL 2008 & 2008 R2

The manual process of updating the original source media is not the worst thing in the world, but it’s not intuitive either. Peter Saddow over at Microsoft posted something last year that clearly outline the steps involved to make this happen. You can find the full details on how over at http://blogs.msdn.com/b/petersad/archive/2011/07/13/how-to-slipstream-sql-server-2008-r2-and-a-sql-server-2008-r2-service-pack-1-sp1.aspx

I used those steps for creating slipstreamed versions of SQL 2008 R2 with Service Pack1 and SQL 2008 R2 with Service Pack 2.

Testing slipstreamed service pack 2

I’ve been using the slipstream of service pack 1 for quite a while now and have never encountered an issue. When SQL Server 2008 R2 Service Pack 2 came out a little while ago I worked to get it deployed on my preprod and production machines, then decided a couple of weeks ago that it was time to slipstream my installation media so that this would not need to happen for future deployments. We have a lot of QA and test deployments of SQL Server coming up, so I felt it a good use of my time.

I followed Peter’s steps and built myself some new installation media that included service pack 2. Being the good DBA that I am I got a couple of virtual machine spun up so that I could perform some installation testing. I wanted to be sure that all the components were installed at the relevant levels and that my configuration files did not need any tweaking.

The install of just SQL Server 2008 R2 w/SP2 along with replication, full-text search and the client tools went fine. There were no problems with the install and all of the components were at the service pack 2 level when I checked. This being good I moved on to an install that included Reporting Services. This is where I started encountering problems.

Installation errors

All of the pre-installation steps went without a hitch, and the components seemed to install without any problems, but then it attempted to start the Reporting Services service, at which point things went sideways.

The install was unable to bring the service online and the installation failed. The SQL Server engine and tools all installed successfully, I was able to access those, but no matter what I could not get Reporting Services to start. I took a while to go through the install logs and found the error:

Parameter 0 : SQL Server 2008 R2@RTM@
Parameter 2 : Microsoft.SqlServer.Configuration.Sco.Service.StartService
Parameter 3 : Microsoft.SqlServer.Configuration.Sco.ScoException@1211@1
Parameter 4 : System.ComponentModel.Win32Exception@-2147467259
Parameter 5 : SqlRSConfigAction_install_Startup
Parameter 7 : ReportServer
Parameter 8 : ReportServer
Final Parameter Values
Parameter 0 : SQL Server 2008 R2@RTM@
Parameter 2 : 0xDC112D1C
Parameter 3 : 0xD3BEBD98@1211@1
Parameter 4 : 0xDC80C325
Parameter 5 : SqlRSConfigAction_install_Startup
Parameter 7 : 0x22C8A7B3
Parameter 8 : 0x22C8A7B3

Strangely it appears as though Reporting Services is running at the RTM level, and this causes a problem as everything else is at the service pack 2 revision.

I wondered if I had done something wrong, so I tried to slipstream the service pack again, just in case I missed something. I got the same result.

Then I tried putting it over the top of service pack 1 slipstreamed media, but was met with the same result.

No matter what I tried I could not get it to work. Even worse, I could not apply service pack 2 to the failed Reporting Services installation in an attempt to bring it online. At this point I decided it must be a bug and gave up.

Filing a connect item

After multiple attempts with different media on different servers and under different conditions I was no closer to getting Reporting Services installed. Not being able to figure out a way to get it on there (short of deploying media with service pack 1 and then updating it to service pack 2) I decided to use Microsoft Connect to file it as a bug.

Connect ID 771260 is open for this. I fully expect it not to get fixed, as there are only a couple of years left on the support lifecycle, but it’s important that these things are at least brought to the attention of the product team.

What you can do

If you slipstream your SQL installs and have encountered this issue please go and upvote the connect item (and say that you can reproduce it). If you slipstream and have not come across this please let me know.

Rant October 23, 2012

Do You Trust Your Application Admins?

I was sitting at my desk, happily minding my own business when an alert came through that a database backup had failed. Ok, backups fail, I just figured one of the transaction log backups hiccupped (we’ve been having some problems the last few days).

When I looked at the failure it was a backup trying to write to the C drive on the server.

I NEVER backup to C. It can easily fill the drive and take down the system.

A bigger indicator that something was up was that all of our backups are done across a 10Gb network to a centralized location for ease of tape backup. This indicated that someone, not a DBA, had the access to run a SQL backup.

I trawled through the permissions on the server and nobody has that level of access so I couldn’t figure out who had done this and how.

So What Happened?

Looking through the SQL logs I saw multiple attempts by a contractor to login to SQL, all of which failed, then about 5 minutes after the backup error came through. Interesting stuff, so I walked over to the contractor and asked what was going on.

After he was unable to login he went to the application admin who helped him out with access…using the application service account.

One of the third party applications from Microsoft some unnamed vendor has a database on that server. Due to the nature of the well designed code the database owner has to be the same as the service account of the application. The application admin knows this password (not my doing).

After logging this contractor in as the application service account the app admin walked away and left him to his own devices. As a result this contractor was dbo on a database which manages security for the entire company. We should just consider ourselves lucky all this guy did was attempt to perform a backup.

Preventative Actions

In order to try and prevent this kind of thing in the future I am looking at implementing a login trigger for the service account which checks the host and application connecting and denying access to anything not in a specifically approved list. There is also a conversation going on to possibly disable interactive logons for the service account using a group policy at the domain level.

It is a Matter of Trust

While the application admin is obviously at serious fault here it leads to a question of how well do you trust your admin team?

Domain admins will be able to access your SQL Servers (get over it, there is no way you can keep them out, if they really want in there are numerous ways for them to do so).

Anyone with a password could share that with someone else and allow them to access your servers.

Ultimately you have to trust those that you work with to do the right thing. It’s always sad when those people let you down.

SQL August 28, 2012

Extended Properties Are Your Friend

It’s nice to have friends, why aren’t you a friend of extended properties? They can make your like so much easier by helping to document your databases and objects.

Take a basic table create statement

CREATE TABLE [dbo].[EatSomeBoogers](
    [ID] [int] NOT NULL,
    [BgType] [varchar](20) NOT NULL,
    [Size] [smallint] NULL,
    [Viscosity] [tinyint] NULL
) ON [PRIMARY]
 
GO

Pretty simple and we can infer a lot of the information about the table, but we mention size, are we measuring in inches, feet, yards?

We can add extended properties that will help to provide that sort of guidance for someone to reference later on.

EXEC sys.sp_addextendedproperty @name = N'Details',
    @value = N'Size is measured in mm', @level0type = N'SCHEMA',
    @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'EatSomeBoogers', @level2type = N'COLUMN',
    @level2name = N'Size'
GO

We can also add properties at the table level:

EXEC sys.sp_addextendedproperty @name = N'Purpose',
    @value = N'Holds information about all the gold digging’,
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'EatSomeBoogers'
GO

And at the database level:

EXEC [MyDB].sys.sp_addextendedproperty @name = N'Usage',
    @value = N'Will handle all information as relates to digging' 
GO

You can even add extended properties to other objects, like stored procedures:

EXEC sys.sp_addextendedproperty @name = N'ProcUsage',
    @value = N'Gets booger sizes and types', @level0type = N'SCHEMA',
    @level0name = N'dbo', @level1type = N'PROCEDURE',
    @level1name = N'GrabBoogers'
GO

What’s great is that you can then quickly and easily query the extended properties for your objects:

SELECT  
        OBJECT_NAME(ep.major_id) AS ObjectName ,
        CASE 
        WHEN c.name IS NOT NULL then 'COLUMN'
        else o.type_desc
        END AS ExtendedPropertyType,
        c.name AS 'ColumnName' ,
        ep.name AS ExtendedPropertyName ,
        ep.value AS ExtendedPropertyValue
FROM    sys.extended_properties ep
        LEFT JOIN sys.columns c ON ep.major_id = c.object_id
                                   AND ep.minor_id = c.column_id
        LEFT JOIN sys.objects o ON ep.major_id = o.object_id
ORDER BY ObjectName, ColumnName

This give you a really quick and easy way to document your objects. I highly recommend that during your next development project that you make life easy for yourself and add extended properties to your objects (and trust me, your DBA will thank you).

SQL August 8, 2012

How Do You Provide Passwords To Your Users?

Passwords are a necessary evil and there are times when you have to hand out a password for a SQL login (because the POS application doesn’t support Windows Authentication). Traditionally I’ve done this by sending an email to the user with the login and a separate one with the password, figuring that internal security controls would be good enough to prevent anyone from accessing both emails. Recently it came to light that all emails were being siphoned off and an information security team had access to all email that traversed our Exchange servers. Now I’m not saying that I don’t trust these guys, but there’s no way in hell I would ever let them get one of my passwords.

I needed to come up with a better solution for getting passwords to users that had a pretty good level of security around it. Yes, I know that the password can easily be set to force a change at the next login, however this does not work in a lot of cases where it will be used by an application and the person doing the configuration doesn’t have the knowledge or tools to go in and change the password themselves.

I decided that I wanted to have a two-factor authentication type method that would limit the availability to a password and that would provide the information once and once only for the user so that it would never be stored for a long period of time.

First I created a table to hold the password and a unique-identifier and nothing else. I didn’t want to store a login name along with this data just for extra security purposes. This way even if someone got access to the password they wouldn’t know what login it was for, helping with additional security.

CREATE TABLE [dbo].[PwInfo]
    (
      [AuthenticationID] [uniqueidentifier] NULL ,
      [NewPwd] [varchar](128) NULL
    )
ON  [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[PwInfo] ADD  DEFAULT (NEWID()) FOR [AuthenticationID]
GO

Now I needed a quick and easy way to get a password once entered. I wrote a procedure that accepts the AuthenticationID, returns the password and then deletes the entry.

CREATE PROCEDURE [dbo].[GetPwdByAuthenticationID]
    @AuthenticationID UNIQUEIDENTIFIER
AS 
    SET NOCOUNT ON
 
    DECLARE @NewPwd VARCHAR(128)
 
    SELECT  @NewPwd = NewPwd
    FROM    dbo.PwInfo
    WHERE   AuthenticationID = @AuthenticationID
 
    DELETE  FROM dbo.PwInfo
    WHERE   AuthenticationID = @AuthenticationID
 
    SELECT  @NewPwd
    
 
GO

Finally I added a proc which would accept a password, add it to the table and then return some information to later allow the password to be retrieved.

CREATE PROCEDURE [dbo].[AddNewPassword] @NewPwd VARCHAR(128)
AS 
    SET NOCOUNT ON
 
    DECLARE @AuthIDTbl TABLE
        (
          AuthenticationID UNIQUEIDENTIFIER
        )
 
    INSERT  INTO dbo.PwInfo
            ( NewPwd )
    OUTPUT  INSERTED.AuthenticationID
            INTO @AuthIDTbl
    VALUES  ( @NewPwd )
 
    DECLARE @AuthenticationID VARCHAR(128)
    DECLARE @Msg VARCHAR(4000)
 
    SELECT  @AuthenticationID = AuthenticationID
    FROM    @AuthIDTbl
 
    SELECT  @Msg = 'Password added. Add the users AD account to the report folder (http://ReportingServices/Reports/Pages/Folder.aspx?ItemPath=%2fDBA+Reports%2fUser+Password+Information )and remove once they have pulled the data.
 
Send the following to the user:
 
For your security purposes please visit the following URL in your browser http://ReportingServices/Reports/Pages/Report.aspx?ItemPath=%2fDBA+Reports%2fUser+Password+Information%2fGet+Password and enter the authentication ID of '
            + @AuthenticationID
            + '
 
This is a one time use authentication token, if you need the password information again you will need to contact the production DBA team.
'
 
    PRINT @Msg
 
 
GO

If you read through this code you’ll see that it outputs a message that provides a couple of links to Reporting Services. This is where the extra authentication comes in.

Within Reporting Services I created a report which called the GetPwdByAuthenticationID proc and just returned the password (nothing more). This report lives in it’s own subfolder which is only accessible by the DBA team.

Here’s how it works:

A user requests a password from us, we pull that password from our secure repository (highly encrypted) and use dbo.AddNewPassword to add this password to the table. We get back a message which we then use to email the requestor; this contains the URL and the AuthenticationID that they need to enter into SSRS to get the information out. We then go to SSRS and grant the user browser permissions in the folder, allowing them to run the report and get back the password. Once they have retrieved the password we then go and remove the user from the folder, closing down access once more.

This provides several layers of security:

The user must be logged in to AD with their own account to be able to access the report
The user must have the the AuthenticationID provided by the DBA team to get the password
The password has a one time access restriction meaning the process cannot be repeated
The login is never given along with the password and never stored by the DBA team together except in a highly encrypted password vault inaccessible to anyone but the DBAs.

I feel this a much better solution than sending passwords via email, and considering it only took an hour to put together I figure it a very worthwhile piece of my time. Sure, there are improvements that could be made around automation of access and notifications to users, but as a quick win I think this does a good job.

I’m interested to know what solutions you other folks might be using for those times when you need to provide password information to users. Please comment below.

PowerShell, SQL July 25, 2012

Comparing Indexes Using PowerShell

When you have data replicated out to multiple machines or are working on migrations it can be a challenge to ensure that the indexes are in sync, thus preventing query performance issues. I found the need to check and sync up indexes myself the other day and I didn’t have any third party software installed to help me do the compare. Yet again PowerShell came to the rescue and I ended up putting together a script that just kind of grew and grew on me, so this morning I decided to stop and put it out for anyone to use.

The script accepts a few parameters:

Server1 – The source server that you’ll use as your source of index goodness
Server2 – The destination that you want to compare (this can be the same server)
Database1 – The source database that contains the tables and indexes
Database2 – (this is optional) the destination database to check, if this isn’t passed then this defaults to the same value as Database1
OutputScriptLocation – The path and filename for the output SQL script file which you can run to create the indexes
Verbose – A switch which will dump lots of information to the screen, which can be useful as it will also tell you if there are missing tables on the destination

I decided not to do the actual index creation within the script as it’s good to be able to check the output and be sure that’s the thing you want. I did however output the script in such a way to make it as easy as possible to run, and so you can just open the script, switch to SQLCMD mode and run it. The script will make the connection to your destination server, to the right database and then create the indexes. It will also output to the screen when an index is created (this is very useful for those times when you are creating a lot of indexes and some of them are large).

I’ve tested it on a few servers and it’s worked out great for me, let me know if you run into any problems running it or encounter strange bugs.

Download Compare-Indexes.ps1 or get the code below.

<#
.SYNOPSIS
   Script can be used to compare indexes on tables on different servers/databases.
.DESCRIPTION
   When executed the script will check passed in databases on different (or the same) server and output a SQLCMD script which
   can be executed to add missing indexes.
.PARAMETER <paramName>
   -Server1 The source SQL Instance that is used as the master
   -Server2 The destination SQL Instance that will be the target for additional indexes
   -Database1 The source database
   -Database2 The destination database (leave blank if the source and destination databases are the same)
   -OutputScriptLocation Path and filename for the .sql file that will be created for any indexes that need to be added
   -Verbose Output some logging information
.EXAMPLE
   ./Compare-Indexes -Server1 localhost -Server2 remotehostinstance1 -Database MyDB -OutputScriptLocation C:TempNewIndexes.sql -Verbose
#>
 
param
(
[string]$Server1 = "$(Read-Host 'SQL Instance 1 [e.g. (local)]')",
[string]$Server2 = "$(Read-Host 'SQL Instance 2 [e.g. (local)]')",
[string]$Database1 = "$(Read-Host 'Database Name')",
[string]$Database2, 
[string]$OutputScriptLocation = "$(Read-Host 'Output File Name')",
[switch]$Verbose
)
 
if (!$Database2) { $Database2 = $Database1}
 
$MissingIndexCount = 0
 
try
{
    #Initialize the output file
    $FileInitiation = @"
:ON ERROR exit 
:setvar SQLInstance `"$Server2`"
:setvar Database `"$Database2`"
:CONNECT `$(SQLInstance)
 
USE `$(Database);
 
 
"@
 
    $FileInitiation | Out-File $OutputScriptLocation
    $File = "True"
}
catch { Write-Host "Output folder does not exist...aborting script." 
    $File = "False"
}
 
 
if ($File -eq "True")
{
    if ($Server1 -ilike "**") { $BaseServer1 = "SQLSERVER:SQL$Server1Databases$Database1Tables" }
    else { $BaseServer1 = "SQLSERVER:SQL$Server1defaultDatabases$Database1Tables" }
 
    if ($Server2 -ilike "**") { $BaseServer2 = "SQLSERVER:SQL$Server2Databases$Database2Tables" }
    else { $BaseServer2 = "SQLSERVER:SQL$Server2defaultDatabases$Database2Tables" } 
 
 
    Write-Output "Please wait...comparing $Server1.$Database1 to $Server2.$Database2"
 
    $Server1List = dir $BaseServer1 | select DisplayName
    $Server2List = dir $BaseServer2 | select DisplayName
 
    #For information purposes show a list of tables missing on server2 if verbose enabled
    if ($Verbose)
    {
        $MissingTables = Compare-Object $Server1List $Server2List -Property DisplayName | Where-Object {$_.SideIndicator -eq '<=' } | Select DisplayName
        foreach ($MsTbl in $MissingTables)
        {            $MissingTable = $MsTbl.DisplayName
            Write-Output "Missing table - $MissingTable"
        }
    }
 
    #The compare uses -IncludeEqual as we only want to compare indexes on tables that exist on both servers
    $TableList = Compare-Object $Server1List $Server2List -Property DisplayName -IncludeEqual | Where-Object {$_.SideIndicator -eq '==' } | Select DisplayName
    if ($TableList)
    {
    foreach ($Table in $TableList)
    {
        $MissingIndex = "" #Initialize the variable so we can null it
        Clear-Variable MissingIndex #Null the variable as we'll be checking for a value in here later
        $TableToCheck = $Table.DisplayName
        $Table1Index = dir $BaseServer1$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name
        $Table2Index = dir $BaseServer2$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name
        if ($Verbose) {Write-Host "Checking $TableToCheck"}
        $MissingIndex = Compare-Object $Table1Index $Table2Index -Property Name -IncludeEqual | Where-Object {$_.SideIndicator -eq '<='} | Select Name
        if ($MissingIndex)
        {
            $MissingIndexCount += 1
            $Index = $MissingIndex.Name
            Write-Output "Missing Index - $Index on $TableToCheck"
            dir $BaseServer1$TableToCheckIndexes | where-object {$_.Name -eq "$Index"} |
            % {$_.Script() | out-file $OutputScriptLocation -append; "; `r`nRAISERROR(`'Index $TableToCheck.$Index created...`',0,1) WITH NOWAIT; `r`n`r`n " |
                out-file $OutputScriptLocation -Append;}
        }
    }
    if ($MissingIndexCount -gt 0) {Write-Output "File $OutputScriptLocation created. Open this file in SSMS and execute in SQLCMD mode"}
        else {Write-Output "No missing indexes found!"}
    }
    else { Write-Output "No matching tables found."}
}

SQL July 9, 2012

Changing SQL Agent Job Subsystem Queue Lengths

In the event you are running a lot of jobs on your SQL Server, or you happen to have a great number of jobs that kick off close to each other and use the same subsystem (i.e. PowerShell) then you might receive a warning in the SQL Agent Error Log stating that the job step “is being queued for the PowerShell subsystem”.

This queuing is deliberate. Microsoft put in safeguards around the various subsystems that the Agent calls in order to help prevent any particular subsystem from taking over all the worker threads (after all you wouldn’t want your 25 PowerShell job steps preventing your queries from running because they’d taken all the worker threads would you?)

This is one of the reasons that I like to use dedicated servers just to handle all jobs/ETL processes. I like to keep my local jobs to just maintenance processes (backup/reindex/checkdb) wherever possible. In doing this I need to be sure that I am able to increase the max worker threads associated to a subsystem to be sure that jobs don’t get delayed. As such I keep an eye on the SQL Agent Error Log and server resources to check that I don’t get thread starvation issues (and being just a job server that’s pretty unusual).

Today I saw a bunch of warnings in the log to do with PowerShell job steps being queued. This wasn’t a surprise as I had added a whole bunch of new jobs and had not adjusted things from the default values. First I went through and grabbed the default information for each of the subsystems so that I could easily revert if the need arose:

SELECT [subsystem_id]
      ,[subsystem]
      ,[max_worker_threads]
  FROM [msdb].[dbo].[syssubsystems]

As you can see here there are only 2 worker threads set for PowerShell. I need to bump this up to 20 so that it could handle the concurrent job load. I have plenty of memory available on the server so I’m not concerned with the overhead associated with the loading of PowerShell either. Always something you need to keep in mind (oh and you have to be a sysadmin to be able to update this table).

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 20
WHERE subsystem = N'PowerShell'  

Checking the value again yields the updated information:

SELECT [subsystem_id]
      ,[subsystem]
      ,[max_worker_threads]
  FROM [msdb].[dbo].[syssubsystems]

Now it’s a simple case of restarting the SQL Server Agent service for the change to take effect.

Note: Applying Service Packs or Cumulative Updates could potentially reset this value, so make sure you document the change and confirm it after any upgrade (for example http://support.microsoft.com/kb/972759 )

PowerShell, SQL, Windows July 5, 2012

Configuring DTC Security In A Cluster With PowerShell

The other day Allan Hirt (blog|twitter) write a fantastic post around “How to properly configure DTC for Clustered Instances of SQL Server with Windows Server 2008 R2”. He included a PowerShell script to handle all the funky setup stuff. The only downside was that after that you had to manually go through and configure Network Access and security for the clustered DTC using the GUI.

“There has to be a better way” I thought. I recalled how security back in 2003 could be set in the registry so I went digging. Eventually I was able to find the requisite keys under the Distribute Transaction Coordinator resource. From there it was just a case of tying things back so that those keys could be easily found from the SQL Resource Name and then updating them so that they matched what Allan demonstrated on his page.

I’ve tested it on a couple of my clusters and it’s worked exactly as designed, you just need to change the ServiceName variable so that it matches your clustered service name and then decided what authentication method you want to use (it defaults to the most secure level: Mutual Authentication Required).

Get the code below or download ConfigureMSDTC.ps1

<#
.SYNOPSIS
   Configures MSDTC on a cluster
.DESCRIPTION
   This configures a basic networked config for MSDTC for clustered SQL Server instances.
   Uncomment the transaction manager security setting and enter the correct ServiceName that can be found in FC manager
.PARAMETER <paramName>
   NONE
.EXAMPLE
   NONE
#>
 
$ServiceName = "SQL Server (SOX2)"
 
#--What MSDTC Transaction Manager Security setting is requested? (uncomment one)
$TranManSec = "Mutual" #Mutual Authentication Required
#$TranManSec = "Incoming" #Incoming Called Authentication Required
#$TranManSec = "None" #No Authentication Required
 
 
 
 
#Grab a list of cluster groups
$GroupPath = "HKLM:ClusterGroups"
$GroupList = dir $GroupPath
 
#Iterate through the groups to find the one matching the service name
foreach ($Group in $GroupList)
{
    $GroupChildPath = $Group.PSPath
    if ((Get-ItemProperty -path $GroupChildPath -name Name).Name -eq $ServiceName)
    {
        #we got a match! Now grab a list of the groups in this service
        $ReferencedResources = (Get-ItemProperty -path $GroupChildPath -name Contains).Contains
        foreach ($Resource in $ReferencedResources)
        {
            #Query each of the resources for their type and work with MSDTC
            $ResourcePath = "HKLM:ClusterResources$Resource"
            if ((Get-ItemProperty -path $ResourcePath).Type -eq "Distributed Transaction Coordinator")
            {
                #We found MSDTC resource for that service group, let's configure it
                $SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTCSecurity"
                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccess" -value 1
                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessClients" -value 0
                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessTransactions" -value 0
                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessInbound" -value 1
                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessOutbound" -value 1
                Set-ItemProperty -path $SecurityPath -name "LuTransactions" -value 1
 
                #Now configure the authentication method for MSDTC (defaulting to Mutual Auth as it's most secure)
                $SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTC"
                if ($TranManSec -eq "None")
                {
                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 1
                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0
                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0
                }
 
                elseif ($TranManSec -eq "Incoming")
                {
                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0
                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0
                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 1
                }
 
                else 
                {
                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0
                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 1
                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0
                } 
 
            }
        }
    }
}
 