Now that I have your attention, can you see what’s wrong with this query?
Prep
Like every food recipe out there, I’m going to make you sit through backstory before you can get to the good stuff.
One of the fun and annoying aspects of performance tuning is making sure that the results of the tuned version match the results of the pre-tuned version.
In this case, tuning went great – it’s amazing how much faster you can get things done when you don’t waste resources – but the results didn’t match.
Now, I was sure that the queries were logically the same, so I pinged a developer who had worked on the query last, and asked them to confirm what the query was supposed to do:
We want all records where the second table T2 has the specific value in ID2, or the first table T1 has the specific value in ID – Said developer
Well, great news for me! I made your query better and fixed a previous bug.
Now, can you figure out the bug?
Example
Here are your example tables: T1 and T2
create_sample_tables.sql
SQL
USE [tempdb];
GO
DROPTABLEIFEXISTS
[dbo].[T1],
[dbo].[T2];
CREATETABLE[dbo].[T1]
(
[ID]INTNOTNULL
);
CREATETABLE[dbo].[T2]
(
[ID1]INTNOTNULL,
[ID2]INTNULL
);
GO
Here’s some records for you:
populate_sample_tables.sql
SQL
USE [tempdb];
GO
INSERTINTO[dbo].[T1]
(
[ID]
)
VALUES
(1),
(2),
(3),
(4);
INSERTINTO[dbo].[T2]
(
[ID1],
[ID2]
)
VALUES
(1,1),
(1,2),
(2,2),
(3,1),
(3,2),
(4,NULL);
GO
Here’s what the expected outcome should be (and what it was post-tuning):
3 records
And here’s what the outcome actually was pre-tuning:
7 records
Buggy Code
buggy_code.sql
SQL
DECLARE @ID INT=1;
SELECT
[T1].[ID]AS[T1_ID],
[T2].[ID1]AS[T2_ID1],
[T2].[ID2]AS[T2_ID2]
FROM[dbo].[T1]
LEFTJOIN[dbo].[T2]
ON
(
[T1].[ID]=[T2].[ID1]
AND
[T2].[ID2]= @ID
)
OR
[T1].[ID]= @ID
WHERE
(
[T1].[ID]= @ID
OR
[T2].[ID1]ISNOTNULL
);
Sin É
So here’s the assignment!
Which line or lines in the code causes the bug?
Why?
It feels strange to sign off without explaining what the issue is, but I’ll return after a few days and fill this in.
Once people who love these kinds of questions get a chance to give it a go!
I filed that feat under the heading “Cool; Good to Know“, and then promptly forgot about it. Part of me thinks that I forgot about it because I didn’t understand how I accomplished it. While another part is sure I forgot about it cause “good to know” wasn’t on the list of tasks with looming deadlines.
I’m a man of many parts, but of more deadlines. Now, let’s understand it together!
First, there was the ooze
It’s a tactic of dealing with volatile amounts of data to call different stored procedures based on the number of records coming into it. I swear, I didn’t make this up!
Small numbers of records may be fine, but an odd, massive number may trigger a new plan. One that frequent executions and resources on the machine may not agree with.
So, normal, small, fast, very frequent executions get the “happy” path, but the big guy can go his own way. Free to roam, hog, and frizzle away everything he has!
We encountered this scenario once and decided to implement this tactic.
However, while reviewing this new stored procedure volatility countermeasure, I came across a temp table that wasn’t created anywhere in the procedure.
Stored Procedures are isolated. Aren’t they?
Or was it the sludge?
Let’s talk about Dynamic SQL for a moment.
I’m sure, although I won’t make up statistics, that you’ve used Dynamic SQL to query over different databases and collated their results.
Here’s a completely made-up example (Don’t do this. I’m sure it’s stupid, but it’s also very late right now)
SELECT COUNT(*) AS database_table_count FROM sys.tables
)
UPDATE #tables
SET table_count = database_table_count
FROM table_counts
WHERE tables_ID = @ID';
SELECT @max = MAX(tables_ID)FROM #tables;
WHILE @i <= @max
BEGIN
SET @context = CONCAT(
QUOTENAME(
(
SELECT TOP (1)[database_name]FROM #tables WHERE tables_ID = @i
)
),
@context_stub
);
EXEC @context @sql, N'@ID int', @ID = @i
SET @i +=1;
END;
SELECT*FROM #tables
I mean, look at line 25 in the code above. We didn’t create any temp table in that dynamic sql, but we’re updating it fine!
There was something I was forgetting: Scope!
Scopé (the é is pronounced)
I had never fully thought about this before.
I thought Dynamic SQL was just strings being executed (and it still kinda is), but it operates under a nested scope of the session.
So, when we say EXEC [sys].[sp_executesql] @stmt = N'INSERT INTO #temp…, for a temp table we’re created in the parent session, we’re ignoring scope boundaries.
The same thing happens with Stored Procedures.
Table Variables? No.
table_var_scope
SQL
CREATEORALTER PROC dbo.TableVar2
AS
BEGIN
INSERTINTO @table_variable (ID)VALUES(1);
END;
GO
CREATEORALTER PROC dbo.TableVar1
AS
BEGIN
DECLARE @table_variable TABLE(ID int);
EXEC dbo.TableVar2;
END;
GO
EXEC dbo.TableVar1;
Msg 1087, Level 15, State 2, Procedure TableVar2, Line 5 [Batch Start Line 0] Must declare the table variable “@table_variable”. The module ‘TableVar1’ depends on the missing object ‘dbo.TableVar2’. The module will still be created; however, it cannot run successfully until the object exists. Msg 2812, Level 16, State 62, Procedure dbo.TableVar1, Line 7 [Batch Start Line 17] Could not find stored procedure ‘dbo.TableVar2’.
Variables? No.
variables_scope
SQL
CREATEORALTER PROC dbo.Var2
AS
BEGIN
SET @i =2;
END;
GO
CREATEORALTER PROC dbo.Var1
AS
BEGIN
DECLARE @i int;
EXEC dbo.Var2;
END;
GO
EXEC dbo.Var1;
Msg 137, Level 15, State 1, Procedure Var2, Line 4 [Batch Start Line 0] Must declare the scalar variable “@i”. The module ‘Var1’ depends on the missing object ‘dbo.Var2’. The module will still be created; however, it cannot run successfully until the object exists. Msg 2812, Level 16, State 62, Procedure dbo.Var1, Line 7 [Batch Start Line 15] Could not find stored procedure ‘dbo.Var2’.
Temp Tables? Yes!
temp_table_scopes
SQL
CREATEORALTER PROC dbo.Proc1
AS
BEGIN
CREATETABLE #t1
(
ID intNOTNULL
);
INSERTINTO #t1 (ID)VALUES(1);
SELECT ID FROM #t1;
END;
GO
EXEC dbo.Proc1;
GO
CREATEORALTER PROC dbo.Proc2
AS
BEGIN
CREATETABLE #t2
(
ID intNOTNULL
);
EXEC dbo.Proc3;
SELECT ID FROM #t2;
END;
GO
CREATEORALTER PROC dbo.Proc3
AS
BEGIN
INSERTINTO #t2 (ID)VALUES(3);
END;
GO
EXEC dbo.Proc2;
ID 1, and ID 3
That’s what the temp table and the stored procedures were doing. Boundary violations don’t really count when it’s my nested scope!
It’s Actually a Donut
Now that that tangent is all squared away, let’s return to how I broke a First Responder Kit stored procedure.
CREATE OR ALTER PROC dbo.NormalProc
AS
BEGIN
CREATE TABLE #BlitzFirstResults
(
ID int NOT NULL
);
INSERT INTO #BlitzFirstResults VALUES (1);
SELECT ID FROM #BlitzFirstResults;
END;
GO
EXEC dbo.NormalProc;
GO
/*
DROP PROC dbo.NormalProc;
DROP TABLE IF EXISTS #BlitzFirstResults
*/
ID = 1 – happily, all day long
/*
DROP TABLE IF EXISTS #BlitzFirstResults
*/
IF OBJECT_ID(N'tempdb..#BlitzFirstResults') IS NULL
BEGIN
CREATE TABLE #BlitzFirstResults
(
BlitzFirstResults_ID int NOT NULL
);
END
SELECT * FROM #BlitzFirstResults;
EXEC dbo.NormalProc
(0 rows affected) Msg 207, Level 16, State 1, Procedure dbo.NormalProc, Line 11 [Batch Start Line 0] Invalid column name ‘ID’.
Different Columns Does Not A Stored Procedure Temp Table Boundary Violation Good Time Make
So, yeah … in truth, I didn’t so much break the stored procedure as I interfered with it…but that just doesn’t sound great when I say it like that.
Now, I know that my temp table in the parent session …got in the way… of the stored procedure’s inner temp table.
Anyway, things happened
What does this have to do with T-SQL Tuesday? Oh! The barest of passing similarities, but I’m team temp tables. They’re weird and (more) wonderful than I realised, but I’ll leave it to smarter people than myself to give you the raw numbers.
All I know is that I still have billion-row tables, procedures with multiple WHERE col1 = @param1 OR @param1 IS NULL) […] filters, and uptime requirements that make finding an outage window for UPDATE STATS more difficult than a greyscale Where’s Wally book.
Here’s my prejudiced take: if your query has WHILE loops and/or CURSORs, then I’m going to assume it’s bad.
Now, the astute among you will note what I just said with the blog post I linked to, and that’s a fair point. This is a knee-jerk reaction to opening up a piece of code and skimming its contents.
So far, in my relatively short stint as a DBA (I’m going to keep saying that no matter how long I am one, btw), I have only encountered 4 scenarios where these Row-By-Agonising-Row (RBAR) methods are acceptable.
See Hannah’s post – and it has to be commented that it’s an informed decision.
You need to call a stored procedure on the records – I don’t know how to get around that
You’re doing batching on mass writes – it’s more set-based inside RBAR than RBAR instead of set-based then
I’m normally an advocate for “write it anyway in case the way you say it gets through to someone”, but her blog posts have been on fire lately, and more people should be reading them.
Wrench and Sproc it
Apart from gutting the stored procedure and inserting its code in your set-based method, there’s no real way around this.
And if that stored procedure calls another stored procedure? Boy howdy, I ain’t touching that one with a ten-foot pole!
Turtles All the Way Down
Today is not Link to Other People’s Blog Day, but I’m going to pretend it is.
I’m going to call this Set-By-Agonising-Set instead of Row-By-Agonising-Row.
Whatever name it goes by, whatever the reason for it, e.g. transaction log space, locking, etc., batching solves issues that come around at scale.
Saltier Than the Old Man and the Sea
Temporal tables… _deep breaths, Shane_
Do you know how to query temporal tables using the FOR SYSTEM_TIME syntax?
Well, you can do:
... FOR SYSTEM_TIME ALL ...
... FOR SYSTEM_TIME AS OF '<date time value> ...
... FOR SYSTEM_TIME AS OF @<date time variable>...
This is great…for uniformity. And all of our result sets are uniform, right?
However, if you have a table of results and you want a different FOR SYSTEM_TIME AS OF value for each record in the results table…
Granted, I have no idea how to implement this, or if it’s even possible, but I’d like it!
RBAR = Crowbar
If I’m looking at a query, it’s bad if I see WHILE loops, or CURSORs, or RBAR options without a documented or commented reason why.
If you’ve come across any other scenario, please let me know. The temporal table is the most recent one for me, and I’m relying on that saltiness to keep me young and preserved. Anything extra would be appreciated.
Ever since I saw a presentation by Anthony Howell ( Blog ), aka PoshWolf, about them, read the blog post by Kevin Marquette ( Blog ) about them, and was enlightened by multiple PowerShell community members (e.g. Chris Dent, Mathias “IISResetMe” Jessen, etc.) about them, I’ve loved hash tables.
However, there is one drawback I have with hash tables: they don’t default to the order in which they are inserted. I’m OK with this since I come from a DB background, and I’m used to order not being enforced unless I specify an ORDER BY. Not everyone is as lenient as we are, though, and the vast majority of the louder masses expect this ordering.
Now, there are ways around this unordered aspect of hash tables. The main way is the [ordered] type accelerator; you can add this to the start of your hash table to keep the default orderings. I use this when I want hash tables, but also want guaranteed orderings.
Well, that was a short blog post. Thanks!
Sacrificing Idiomatic for Changeable
I know about using [ordered], and now you’ve learned about using [ordered], but what about people using my scripts that aren’t part of either knowledge group? Oh, I could add a comment, and I could train the people, but what happens if I get hit by a bus win the lottery in the morning? There will eventually be a time when I’m not there, and people will have to use my scripts.
So, [ordered] seems a bit too unknown at the moment for the people who regularly use my scripts.
Which is a pity, cause the current version uses [ordered] hash tables pretty extensively. Is there something else that we can use instead?
Yes, Arrays
Ah, the humble array. They’re not that bad once you get to know them. Once I started thinking about these little data types, I realised that they could be a potential answer to this problem.
$pscustomobjectArray = @(
[PSCustomObject] @{ ID = 0; Name = 'Test'; Type = 'Dev' }
[PSCustomObject] @{ ID = 1; Name = 'Test2'; Type = 'Dev' }
[PSCustomObject] @{ ID = 2; Name = 'PreProd_1'; Type = 'PreProd' }
[PSCustomObject] @{ ID = 3; Name = 'PreProd_2'; Type = 'PreProd' }
[PSCustomObject] @{ ID = 4; Name = 'Prod_1'; Type = 'Prod' }
[PSCustomObject] @{ ID = 5; Name = 'Prod_2'; Type = 'Prod' }
[PSCustomObject] @{ ID = 6; Name = 'Prod_3'; Type = 'Prod' }
)
for ($i = 0; $i -lt $pscustomobjectArray.Count; $i++) {
"The $i-th object is $($pscustomobjectArray[$i])"
}
Old = Left. Rewrite = Right. The default is because there’s always one person who just runs these things as is…
Thankfully, arrays keep their order, so I can rely on them in the script.
In Use
What does this mean? This means that you can use the standard input that people are accustomed to in PowerShell, such as PSCustomObject, CSV, JSON, etc.
As long as we can select the property, we can then filter it down to this specific property.
Example
For each of these examples, we’re passing in a list of choices. Then we’re filtering down the list to the option we chose.
We can do this multiple times, if we want.
Here’s our list of choices to… aha… choose from.
1…2…3, a…b…c…
Example PSCustomObject
$ChoicesCustomObject = [PSCustomObject]@{
Name = 'Test01'
Value = '1a'
}, [PSCustomObject]@{
Name = 'Test01'
Value = '1b'
}, [PSCustomObject]@{
Name = 'Test02'
Value = '2a'
}, [PSCustomObject]@{
Name = 'Test02'
Value = '2b'
}, [PSCustomObject]@{
Name = 'Test03'
Value = '3a'
}, [PSCustomObject]@{
Name = 'Test03'
Value = '3b'
}
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesCustomObject | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesCustomObject | Where-Object Name -eq $AvailableChoices[$ChosenIndex]
$ChosenOption
1a. 1b.
Example Csv
$ChoicesCSV = @'
"Name","Value"
"Test01","1a"
"Test01","1b"
"Test02","2a"
"Test02","2b"
"Test03","3a"
"Test03","3b"
'@ | ConvertFrom-Csv
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesCSV | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesCSV | Where-Object Name -eq $AvailableChoices[$ChosenIndex]
$ChosenOption
2a. 2b.
Example JSON
$ChoicesJSON = @"
[
{
"Name": "Test01",
"Value": "1a"
},
{
"Name": "Test01",
"Value": "1b"
},
{
"Name": "Test02",
"Value": "2a"
},
{
"Name": "Test02",
"Value": "2b"
},
{
"Name": "Test03",
"Value": "3a"
},
{
"Name": "Test03",
"Value": "3b"
}
]
"@ | ConvertFrom-Json
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesJSON | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesJSON | Where-Object Name -eq $AvailableChoices[$ChosenIndex]
$ChosenOption
3a. 3b.
Example Multiple
$ChoicesCustomObject = [PSCustomObject]@{
Name = 'Test01'
Value = '1a'
}, [PSCustomObject]@{
Name = 'Test01'
Value = '1b'
}, [PSCustomObject]@{
Name = 'Test02'
Value = '2a'
}, [PSCustomObject]@{
Name = 'Test02'
Value = '2b'
}, [PSCustomObject]@{
Name = 'Test03'
Value = '3a'
}, [PSCustomObject]@{
Name = 'Test03'
Value = '3b'
}
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesCustomObject | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesCustomObject | Where-Object Name -eq $AvailableChoices[$ChosenIndex]
<# Filter down to the value #>
$ChosenValue = Initialize-Choice_v2 -Title "Choose Value" -Caption "Select a value for $($ChosenOption.Name)" -Choices $ChosenOption.Value
<# Return the chosen option #>
$ChosenOption[$ChosenValue]
1b only.
Caveat
Yes, I am aware that the values are off by one; for example, the first value shows 1 on the confirmation screen but returns a 0. I bowed to requests from people who would be using the code but were not comfortable with indexes starting at 0.
Feel free to change that in your script.
Overall
This is a case of “exception making the rule”. I’ve said before that I prefer not to have user prompting in my scripts, since a script that requires human intervention is not something that can be fully automated. However, whether it’s risk-averse business leaders or novices to scripting, there are times when an expectation of human interaction is present.
If there is no getting away from this, then I can make the process easier. Here’s hoping that making it easier and easier to use will lead to more automation… or at least more.
It got me thinking, “Huh, does SQL Server not have filters? And, if not, how can we work around that?”
So, cheers, David, for the idea for this post.
The Big Set up
We’re going to use the same setup that David has in his post
USE tempdb;
GO
CREATE TABLE z (a int, b int, c int);
GO
INSERT INTO z VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400);
GO
SELECT a,b,c FROM z;
GO
I learnt how to increase font size.
Stupid, but does it work
Now, SQL Server doesn’t have the filter option, but we can do some pretty weird things, like a SELECT...WHERE statement with no FROM clause.
SELECT
a,b,c,
[filter?] = (SELECT b WHERE b > 11)
FROM z;
GO
Does it B useful though?
So, is that it? Can we just throw that into a COUNT() function and essentially have the same thing as Postgres?
SELECT
all_rows = COUNT(*),
b_gt_11 = COUNT((SELECT b WHERE b > 11))
FROM z;
GO
B better.
Workaround
We can rewrite that strange little filter we have using an OUTER APPLY.
SELECT
a,b,c,
[filter?] = bees.bee
FROM z
OUTER APPLY (SELECT b WHERE b > 11) AS bees (bee);
GO
Same, same, but different.
Now, can we throw that in a COUNT() function?
SELECT
all_rows = COUNT(*),
b_gt_11 = COUNT(bees.bee)
FROM z
OUTER APPLY (SELECT b WHERE b > 11) AS bees (bee);
GO
B-utiful!
Things I don’t know
Postgres, yet. Still learning this one.
Why the OUTER APPLY works, but the subquery doesn’t? Let’s review the plans and see if we can identify a culprit.
Does not Compute Scalar
Strangely, the first Compute Scalar (from the left, after the SELECT) is simply a scalar operator applied to the other Compute Scalar.
It’s enough to block aggregation, though, but I’ll leave it to more specialised people than I to tell me why. I’ll just store this under “errors out” for future reference.
Performance Implications This is a test on 4 rows. What would happen if this were tens of millions of rows? … I leave that to the reader.
I will discuss different ways to get the SQL Server version using PowerShell.
I’ll explore a function you can use, and how a dictionary/hash table could also work.
Finally, I’ll discuss how neither of these are needed since dbatools/SMO has a better way.
The Initial Issue
I’m sure you’ve needed the version of a SQL Server instance for a report a few times. And, surprisingly, not many people are up for parsing the output of @@VERSION.
I can’t see why not, it’s perfectly fine. It is not the easiest thing in the world, but it is also not the hardest. But, you do you.
Invoke-DbaQuery -SqlInstance localhost\SQL2019 -Query "SELECT version = @@VERSION;" | FL
No, I haven’t updated this instance in a while. Thank you for asking
My team lead has a function that he uses to get these versions, but it encountered an issue. We recently updated to a later version of SQL Server, and the function stopped working.
There’s more, but it boils down to “this is a simple function”.
Taking a look at the function, here’s the gist of the function block:
param([string] $Version)
switch ($Version) {
14 { "SQL Server 2017" }
13 { "SQL Server 2016" }
12 { "SQL Server 2014" }
default { "Unknown" }
}
This seems simple enough. Hell, it’s just a switch statement that takes the input and spits out the matching output.
Seeing something this simple I’d almost suggest using a dictionary
Using a dictionary instead
This is the equivalent code of the function, but using a hash-table.
$dict = @{
14 = "SQL Server 2017"
13 = "SQL Server 2016"
12 = "SQL Server 2014"
}
$version = $dict[15]
if (!$version) {
$version = "Unknown"
}
"Version: $version"
Still unknown, but visibly unknown.
The benefit of this approach is:
it’s easier to modify. I don’t need to update the function and either dot-source it, e.g. . <path_to_function_file.ps1>, or re-run it in memory to use it. I only need to add an entry to the hash table.
$dict = @{
15 = "SQL Server 2019"
14 = "SQL Server 2017"
13 = "SQL Server 2016"
12 = "SQL Server 2014"
}
$version = $dict[15]
if (!$version) {
$version = "Unknown"
}
"Version: $version"
Updated to know about the number 15.
The problem with this approach is:
I have to keep it updated, and I’ll find it difficult if I have to add it to multiple scripts.
Shift left?
The above approaches have common issues. They have to be ported to every script I have, run into memory, be in sync, etc., etc.
Can I do something about that? Can this be shifted left somehow? Can I remove some of the work that I’ve to do? In the words of Homer Simpson “can’t someone else do it?”.
Someone else is normally “me” though.
Why yes, yes they can.
Using dbatools
When dbatools connects to a SQL Instance, the usually returned object is a Microsoft.SqlServer.Management.Smo.Server, and that object has a few methods. The one I’m interested in is GetSqlServerVersionName.
Hey, the function is even there on Microsoft.SqlServer.Management.Smo.Database from Get-DbaDatabase, in case I get the strange notion that the version changes from database to database.
Normally, I would have skipped this month because I’ve talked about permissions before, and I feel like it’s one of the better posts I’ve written. But I’m trying to write more, so that can’t be an excuse anymore.
So, let’s talk about AGs, DistAGs, and SQL Server authentication login SIDs.
AGs and DistAGs
In work, we have Availability Groups (AGs) and Distributed Availability Groups (DistAGs), depending on the environment level, e.g. Prod, Pre-Prod, QA, Dev, etc.
When we plan failovers for maintenance, we run through a checklist to ensure we can safely complete the failover.
One of these checks is to ensure that the correct logins with the correct SIDs are on each replica. Otherwise, we could get flooded with a lot of “HEY! My application doesn’t connect anymore! Why you break my application“, when apps try and log into the new primary and the user SIDs don’t match the login SIDs.
While I don’t have a problem with doing a query across registered servers, I threw together a quick script in PowerShell with dbatools that does the job of checking for me. And, like most things that happen in business, this temporary solution became part of our playbook.
Who knows! Maybe this quick, throwaway script could also become part of other people’s playbooks!
I’m not sure how I feel about that…
Scripts
We’re using dbatools for this because I think it’s the best tool for interacting with databases from a PowerShell session regardless of what Carbon Black complains about.
Then, we can use the ever-respected Get-DbaLogin to get a list of logins and their SIDs per replica. If we have a mismatch, we will have an issue after we failover. So best nip that in the bud now (also, I had this phrase SO wrong before I looked it up!).
$InstanceName = 'test'
# $InstanceName = $null # Uncomment this line to get ALL
$Servers = Get-DbaRegServer
if ($InstanceName) {
# Find the server that ends in our instance name
$Servers = $Servers | Where-Object ServerName -match ('{0}$' -f $InstanceName)
}
# Get all replicas per Instance name that are part of AGs...
$DAGs = Get-DbaAvailabilityGroup -SqlInstance $Servers.ServerName |
Where-Object AvailabilityGroup -notlike 'DAG*' | # Exclude DAGs which just show our underlying AG names, per our naming conventions.
ForEach-Object -Process {
$instance = $null
$instance = $_.InstanceName
foreach ($r in $_.AvailabilityReplicas) { # I'd like 1 replica per line, please.
[PSCustomObject] @{
InstanceName = $instance
Replica = $r.Name
}
}
} |
Select-Object -Property InstanceName, Replica -Unique |
Group-Object -Property InstanceName -AsHashTable
# Get a list of Logins/SIDs that don't match the count of replicas, i.e. someone forgot to create with SIDs...
foreach ($d in $DAGs.Keys) {
Write-Verbose "Working on instance: $d" -Verbose
Get-DbaLogin -SqlInstance $DAGs[$d].Replica |
Group-Object -Property Name, Sid |
Where-Object Count -ne $DAGs[$d].Count |
Select-Object -Property @{ Name = 'Instance'; Expression = { $_.Group[0].InstanceName }},
@{ Name = 'Occurances'; Expression = { $_.Count }},
@{ Name = 'Login/SID'; Expression = { $_.Name }}
}
I got 99 problems, and Login/SIDs are one! Or I have 5 Login/SID problems.
Cure
They say “prevention is better than cure”, and I’d love to get to a stage where we can “shift left” on these issues. Where we can catch them before someone creates a login with the same name but different SIDs on a replica.
But we’re not there yet. At least, we can find the issues just before they impact us.
Welcome to T-SQL Tuesday, the monthly blogging party where we are given a topic and have to talk about it. Today, we have Rob Farley ( blog | bluesky ), talking about integrity.
I’ll admit that it’s been a while since I’ve written a blog post. It’s been a combination of either burnout or busyness, but let’s see if I still have the old chops. Plus, I’m currently sick and resting in bed, so I have nothing better to do.
I’m one of the few who haven’t had experiences with corruption in our database. Apart from Steve Stedman’s ( blog ) Database Corruption Challenge, that is.
With that being said, what do I have to say about this topic then? Well, let’s talk about the first version of corruption checking automation that we introduced in work.
Now, this is just the bare bones and many different iterations since then, but the essence is here.
Overview
Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.
At least this way we can test the backups we take can be restored, as well.
But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…
Limitations
A quick scour of the interwebs brought back something extremely close to what I want by Madeira Data Solutions. It had some extras that I didn’t want, though.
More importantly, it used some functions that our dreaded antivirus software still screams false positives about. So, they would stop our script from running if we even tried.
What happens when nothing changes, sp_send_dbmail gets given sysadmin, and you still can’t get emails.
Words: 817
Time to read: ~4 minutes
Invitation
Welcome to TSQL2sday, the monthly blogging party where we are given a topic and are asked to write a blog post about it.
This month we have Brent Ozar ( b ) asking us about the latest issue closed.
I don’t like posting about issues unless I fundamentally understand the root cause. That’s not the case here. A lot of the explanation here will be hand-waving while spouting “here be dragons, and giants, and three-headed dogs”, but I know enough to give you the gist of the issue.
sp_send_dbmail
Like most issues, even those not affecting production, this one was brought to the DBA team as critical and needed to be fixed yesterday.
A Dev team had raised that a subset of their SQL Agent jobs had failed. The error message stated:
Msg 22050, Level 16, State 1, Line 2 Failed to initialize sqlcmd library with error number -2147467259
That makes sense; the only jobs that were failing were ones that called sp_send_dbmail using a @query parameter. And I know that when you use that parameter, the code is given to the sqlcmd exe to run it for you.
Google fu
From research (most of the time, I ended up in the same post), the error fragment “failed to initialize sqlcmd library with error number” could be related to a number of things.
The database object not existing No, the agent fails even when the query is SELECT 1;
The wrong database context used No, SELECT 1;
sqlcmd not being installed or enabled It was working beforehand, so I would say not.
Permissions
Permissions
Well I had already tested that the query worked as long as it was valid SQL, so let’s try permissions.
I increase permissions…no luck. I grant a bit more permissions…nope. A bit more permissions…still nothing. ALL the permissions… MATE, YOU’RE A SYSADMIN! WHAT ARE YOU ON ABOUT?!! …ahem… nothing.
Workaround
Strangely enough, the post mentioned that using a SQL Authentication account worked. So we tested it using EXECUTE AS LOGIN = 'sa'; and it worked. Which was weird, but I’ll take a workaround. Especially since it gave us time to investigate more.
Thanks to dbatools, I threw together a PowerShell script that went through all of the SQL Agent jobs that contained sp_send_dbmail and wrapped them up.
EXECUTE AS LOGIN = 'sa'; EXEC dbo.sp_send_dbmail ...; REVERT
I’m not going to share that script here cause it is a glorious mess of spaghetti code and if branches. I gave up on regex and did line-by-line thanks to the massive combinations of what was there e.g.
EXEC msdb.dbo.sp_send_dbmail .
EXECUTE sp_send_dbmail.
EXEC msdb..sp_send_dbmail.
sp_send_dbmail in a cursor so we need to revert after each call in the middle of the cursor.
sp_send_dbmail where there are spaces in the @body parameter so I can’t split on empty lines.
What’s Happening?
After getting the workaround in place, the team lead and I noticed something strange.
Sure, EXECUTE AS LOGIN = 'sa'; worked, but try it as a Windows Domain login and you get something different.
Could not obtain information about Windows NT group/user '<login>', error code 0x5
Something weird was happening between SQL Server and Windows. Great for me! I get to call in outside help. Not great for ye! The remaining explanation is going to be shallower than the amount of water I put in my whiskey.
What Changed
Nothing!
Or so we were told. Repeatedly. We did not believe that. Repeatedly.
Next, we started to get “The target principal name is incorrect. Cannot generate SSPI Context” on the servers.
Not being able to send emails from a SQL Agent Job is one thing, but not being able to Windows Authenticate into a SQL Instance at all is another thing all together.
Eventually, as awareness of the issue increased, the problem was narrowed down to a server configuration on a Domain Controller. I’m assuming that the name of this server configuration is “nothing”.
“Nothing” was set on one server but not the other meaning that using one DC over another meant Kerberos did something that it was not supposed to. I’m reliably informed that “nothing” has something to do with encryption and gatekeeping. I reliably replied that Kerberos should be spelt Cerberus but was ignored.
Testing
With “nothing” in place properly, the SSPI Context errors disappeared.
I reverted the workaround EXECUTE AS wrapper on sp_send_dbmail and emails started flowing again, even without the wrapper. Even with permissions reduced back to what they were.
Research
Sometimes the problem is actually outside SQL Server. Those are the good days. Other days it is a SQL Server problem and you have to fix it yesterday.
All I can do is take what happens, do a bit more research, and learn from them. That way, if it were to happen again, I can speed up the resolution process.
At least this way, if someone ever asks me if I know anything about Kerberos, I can tell them that I know about “nothing”.
Dear Host, you don’t have to use Read-Host. There is a choice
Words: 590
Time to read: ~ 3 minutes.
Interactive
I’m an advocate of automation. I don’t think automation that requires user input is the correct way to do automation. However, there are times when user interaction is wanted.
I didn’t say “needed”, I said “wanted”.
Either a case of severe risk-aversion, or being burnt by bad bots, or skirting the fine line between wanting to be involved with the work but not wanting to do all the work.
Effectively, creating a Big Red Button, with a secondary “are you sure?” screen, followed by a “last chance” pop-up. You know, the “let’s introduce extra options to make sure things won’t go wrong” approach.
Sorry, did I say I was an automation advocate? I think I meant to write automation snob.
The Question
Most of the work I’ve been doing lately has been project-orientated, production DBA work e.g. building new servers, migrating across cloud providers, enabling TDE…
…working with Distributed AGs. I think this is the first SQL Server technology about which I swing from love to hate about in equal measure.
So, automation is nearly non-existent right now on my tasks. But, when I got asked if there was an easier way to get user input; taking into account bad answers, shortcuts, default options, etc., rather than using a plethora of Read-Host commands and double-checking the user input, well…
I didn’t know.
But, thankfully, I speak to people who are more experienced and smarter than I am when it comes to PowerShell, and I remembered them mentioning $Host.UI.PromptForChoice() before.
Initialize-Choice.ps1
Here’s the link to the code. I’m going to have to ask you to excuse the formatting. I’ve forgotten how to embed the code in an aesthetic way and have misplaced my desire to research how again.
What this provides us with is a short-hand way to generate a choice option for the user, that will not accept answers outside of the given list.
Pudding
$Choices = [ordered]@{
"Option 1" = "This is the first option"
"Option 2" = "This is the second"
"Option 3" = "The third"
"Option 4" = "You're not going to believe this..."
}
Initialize-Choice -Title 'Test 01' -Choice $Choices
Nice and simple.
The choices that we laid out have been given a shortcut number, and their ordering has been preserved. Any user input outside of the choices are automatically rejected, we can add descriptions for extra help, and we’ve even thrown in a “Cancel” choice as well.
In fact, ordering is the only thing left to mention about this function. It’s why it’s written the way it is and why it only accepts hashtables with the [ordered] accelerator on them.
Here’s how it works when I first wrote it using only hashtables.
$Choices = @{
"Option 1" = "This is the first option"
"Option 2" = "This is the second"
"Option 3" = "The third"
"Option 4" = "You're not going to believe this..."
}
Initialize-ChoiceUnordered -Title 'Test 01' -Choice $Choices
4, 2, 3, 1! Debugging this would sure be fun!
Option 1 is still option 1. But now Option 3 has gotten jealous and pushed Option 2 down. While Option 4 is just happy to be included.
Seeing as any code that parses the choice relies on the user picking the right option and the choices being in a determined order, it seemed like a bug to not have the input order preserved.
Sin é
So that’s it.
Feel free to use and abuse it. I would prefer that you use and improve it but it’s your party, you can do what you want to.
