Friday, April 22, 2016

[WAT] The weird intersection between Foreign Keys, Replication, Unique Indexes, set-based updates, and missing rows.

We are in process of doing a migration from an ancient creaky server to a shiny new VM.  Rather than just rebuild it and restore everything, we're taking the (painful) opportunity to clean things up and improve several systems.

As part of this, we're replicating data from the old server to the new server, so that we can migrate processes piecemeal, so that rollback is not "OH CRAP TURN IT OFF TURN IT OFF ROLL BACK TO THE OLD SERVER".

But we ran into a weird problem.  On the target server, we had a many-to-many table that sits between, let's say, stores and orders.  We have a stores table, we have an orders table, and this one (call it STORE_ORDERS for simplicity) is just a linking table between the two.  ID, stores_id, orders_id.  Everything scripted identically between the two databases (aside from the NOT FOR REPLICATION flag).

We found, while double-checking everything, that there were records missing from the stores_orders table on our new box, that were just fine on the source table.  What. The. Hell.  After looking for a few, we determined that it had to be related to one of our users doing a bunch of changes.... but just to the stores table.

What could it be?  They're updating the END_ASOF field in STORES, which is part of a unique constraint

CREATE UNIQUE NONCLUSTERED INDEX [STORE_NUMBER_ENDASOF_IDX] ON [dbo].[STORE] ([STORE_NUMBER], [END_ASOF])






The table affected does have a FOREIGN KEY constraint on STORES.  Because we're trying to migrate over, the DDL is the same.

ALTER TABLE [dbo].[STORES_ORDERS]
ADD CONSTRAINT [FK_STORES_ORDERS_STORES] FOREIGN KEY ([STORES_ID]) REFERENCES [dbo].[STORES] ([ID]) ON DELETE CASCADE

And just to make it more fun, there are some AFTER UPDATE triggers involved.  (But those are unrelated).


Any ideas?  Replication, UNIQUE constraint, Foreign Keys.



Now, go read this article: https://blogs.msdn.microsoft.com/repltalk/2011/10/04/sql-server-transaction-replication-bounded-update-or-why-my-update-was-transformed-into-an-deleteinsert-pair/

I'll wait.



In the end, we figured out what had to be happening, then confirmed via trace.  Care to guess?





So our source table, when you update that unique constraint (AS PART OF A SET; doing a singleton update, or "update stores where ID IN (1,2)" doesn't do it, we tried), replication will push a BOUNDED UPDATE, aka sp_MSdel/sp_MSins.

data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJ8AAABJCAIAAACsHmOFAAADWUlEQVR4nO2aUZaDIAxF3S7ryVrYB9vgrztwPlQIJIC2dqQ5737MqRZDyANkZt6yCl6vl7wJfpFF3oK6ZoC6loG6lllewC5Yu5aBupaBupaZVN3o3bLhfHwsi0C97t/LMcWM3n19bPOqS+GuYIGYAMVFFkgVqa/ulUZK8yvq1nmG4pplHyhdU7iqbn54q32g9HGN3rFh1i2rm8tAO0Vd9vDRTfTOeU+jkIGcOyrJPzfqm0pJRKMRrSfVVWJG75hMKaZSo848CMSyid4VcZy/oC7rhMUJtCwUygzUlsWiGSHUVRdz9G5hebRL4LynfW6njzl3EZMNTU6Eoob9jrsxo3cpfIrBgskaq0PLX1STIHrnzqsbq92g2t5YYLVlXZQ+Usx9Thd3i/G0qxzI+Vj9ELlmGav5H9fO2Lv98j7UmCL5IlbxWJ1nfk5GZk/IZDrqqvKIuqstP1W36Cy/DIoCNeJvcgbaa1Ooy+NSaE2YXvKn1NVjiuTb6tZ5KoP+aO02ttY868p3h3ZEeX9njlFbLOxVX771656PE0fanbf9MOQuUilZgVnGneRP7MxqTDV5dWdW80yRb3rvrsUrX2jKX4KyZX1zPN3rPVh/Jzii+lyiZF30ltVlOWrHp9ap6urOrMeM3pEXhyrtVNXIc/+qGPdHZ+Z/49xvRPIwWRSirtLXebZ3hXnVPbHI/+MPAj/NpOqCW4C6loG6loG6loE3wzJYu5aBupaBupaZVF14M25hXnXhzcjAmwFvBrwZ8GbAm1GHhDcD3ozOYODN4MCbAW8GvBnwZsCbwZlE3ROLHN6MAZOqC24B6loG6loG6loG3gzLYO1aBupaBupaZlJ1n/Zm9P5s/RnwZszizfgG8GY86c3IhWTxtpZHBu/9V2QPCm/Gk96M9Eylbqp2GtH5/QXeDNHZM94M1p1Yu/VXWkqNwcCbUbV+xpvBrofqaik1BgNvBuc5b0buZKCunlIjAXgzOE96M45UR+qqKTVTOE5K8GbAm3ET86p7YlXAmzFgUnXBLUBdy0Bdy0Bdy8CbYRmsXctAXctAXctMqi68Gbcwr7rwZmTgzYA3A94MeDPgzYA3g/dQDhLeDDEYeDM48GbAmwFvBrwZ8GZwJlH3xKqAN2PApOqCW/gDsoUojtVSPNYAAAAASUVORK5CYII=

Which normally wouldn't be a big deal.  

Except for that foreign key.  Which has a CASCADE DELETE.

So, to recap: user does an UPDATE on the source table.  Because they updated a field in the unique constraint, the delete becomes a BOUNDED UPDATE.  Then, replication pushes this update, via a DEL/INS pair, to the target server.  That DEL hits the foreign key's CASCADE DELETE, which then DELETEs the stores_orders row.  Voila!  We updated Table A, and Table B loses records!

Moral of the story?  Beware complications and interactions, and test even when you know everything matches.

Wednesday, April 13, 2016

SSISDB - making a more useful email (additional code so you don't need SQL Sentry)

As you may know by reading http://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html, I built a process a while ago that will scan the SSISDB history, when passed a job, and send out an email with the details of the most recent failure.  I love it and it means you get a useful email instead of a "go run the Integration Services Dashboard and start hunting" that it currently sends.

However, we run a LOT of SSIS jobs, multiple per minute, and so the query now takes over 30 seconds to return.  Which sucks, because SQL Sentry's "execute SQL" has a hard cut-off of 30 seconds.  So we stopped getting the useful emails.  I wanted to fix that.

First attempt: go look in sysjobhistory for failed jobs, and then walk through the jobs that failed and run my SSISDB code explicitly.

Enjoy!



Thursday, April 7, 2016

#BacktoBasics - a list of my basic precepts/tenets/rules

So, last December, Tim Ford (aka @SQLAgentMan) posted this challenge:

tim_challenge

We're now in April and I've "been meaning to" for 3 months now.  So let's get some #backtobasics.

Here's my personal list of rules.  My goal is to expound upon them, 1 post per month.  We'll see if I hit that.

Rule 0: Backup
Rule 1: UPDATE STATS
Rule 2: Data/Log ALWAYS separated
Rule 3: Complexity is the enemy.
Rule 4: Layers of Abstraction
Rule 5: Question Assumptions
Rule 6: NULL is NULL
Rule 7: Once, Adhoc. Twice, Automate.
Rule 8: If you have time, build the framework. (See #3)

Some of the stuff to cover, off the top of my head:
0: Ola, testing, tweaks, diff/full/log
1: Why it matters, why it works, when it doesn't, alternatives, flushing the cache
2: if you can't grow one or the other...
3: find some stories. The 5 hops for replication come to mind.
4: not just views. Synonyms, CNAMEs, views, etc.
5: "it works this way in SQL 200X" "well, SP1" "well, CU5 but not SP1 CU4"
6: Find some examples about getting burned by it.
7: pretty explanatory.  You get one whack at the pie for free.
8: contradicts 3, but reinforces 7. The EPR, partitioning, EN's blacklist.

Tuesday, March 1, 2016

[Presenting] SQL Saturday Colorado Springs - April

SQL Saturday Colorado Springs 2016 


Another great SQL Saturday coming on April 2nd in Colorado Springs!




Come see me speak on my favorite topic in the whole world - Event Notifications!  LEAP tall buildings in a single bound! KNOW who altered a stored procedure 6 months ago! SEE the code deployed by someone who thought you weren't looking. LEARN to use native SQL Server features to track all your code changes, in real-time, across your entire environment!  CHECK in all the changes to a production-specific branch of source control!  WATCH dozens of awesome folks present on topics from A(vailability Groups) to R!

Monday, February 29, 2016

[Presenting] Perth SSUG presentation - video

Thrilled to have presented on WMI for the Perth SQL Server Users Group.  Worth waking up at 3am for! 

Here's a copy of the presentation - I only wish I'd known the video of me was so small, and the Skype window so large.

A copy of everything you saw (the scripts, slide-deck, etc) is available from the  SQL Saturday web site, since this is the same presentation I gave there.  http://www.sqlsaturday.com/SessionDownload.aspx?suid=11921

Enjoy!




Wednesday, February 24, 2016

[Presenting] Hello, Australia!

(Doh!  Didn't hit send before I left the office)

Tickled pink to have presented (virtually, alas... *sigh*) about 12 hours, at the Perth SQL Server User Group (http://perth.sqlpass.org/default.aspx).

I did a fairly-basic-yet-still-pretty-cool presentation on WMI and why you, as a SQL Server admin, should care.  We'll also be tackling some Powershell, though mostly as a means to an end - solving problems you didn't know you have.  We'll look at collecting Event Logs, multiple ways to be notified when things change on your server (either within the server, or within SQL Server), even how to run a one-line command that could speed up your performance by 25% ("DBAs Hate Him - use this one crazy trick..."?).

You'll also got to see how well I present at 3am.  Bonus!




Friday, January 29, 2016

[Event Notifications] Simple alert to make sure everything's working.

When I built it, I thought I'd added enough monitors to make sure it didn't break.  Nope!  Someone dropped an endpoint, so things backed up silently for a day and a half.

Add this as a job step on your repository.  For the time, YMMV - 30 is actually too short for me, but 60 should work.


Wednesday, January 6, 2016

[Presenting] SQL Saturday Austin 2016, here I come!





Woohoo!  Come join us on January 30th, 2016, at Wingate Round Rock, for another awesome SQL Saturday!  I presented last year on SQLWatchdog/Event Notifications, and I'm thrilled they chose me again.

This year: WMI! 


Practically?  EVENT LOGS AND MORE.  Your SQL Server runs on a machine.  Be it virtual or physical, there's stuff going on there that you should know about.  Let's collect them.  And then, since we're building this process out of it, what else can we do?  A BUNCH.  Fix your server's Power Plan!  Monitor for file changes! Check logins! SQL Server Changes! More! And it's all stuff I use in production, so set it up Monday!

There looks to be 36 different sessions, and I see some friendly faces (Tim Mitchell, John Sterrett, Amy Harold) some people I've wanted to see speak (Thomas LaRock, Robert Davis), and some sessions that just sound seriously cool (Regexp, "Analyze your query plan like a Microsoft Engineer", "Azure Data Factory"). It'll be a blast!  And it's FREE. 

Thursday, December 10, 2015

[T-SQL] Basic Pivot automation script

There's a guy out there who has a great dynamic pivot CLR.  This is a much simpler version.  I _think_ this is original to me.  I've been using it in various projects on this blog, but I don't see where I posted just this code.

Say you have records from your monitor that happens to Track EventLogs.
We'll call it EventLog_Tracking for argument's sake (hint hint http://thebakingdba.blogspot.com/2015/05/powershell-eventlogtracking-capturing.html) and want to look at trends over time.

First, you'd need a grouped set.

SELECT sourcename, CONVERT(DATE, TimeGenerated) AS the_date, COUNT(*) AS the_count
INTO #temp
FROM EventLog_201512
WHERE sourcename LIKE 'microsoft-windows%'
GROUP BY SourceName, CONVERT(DATE,TimeGenerated)
ORDER BY CONVERT(DATE,TimeGenerated), SourceName





And you want it to look like...
 


Here's the base pivot code.  For this example, replace down with "the_date" and across with "sourcename"

If you want a name or something instead of the number, then do something like "max(yourname)" instead of "sum(the_count)"


[Hey Kids] @@servername and BCP and ultra-long statements.

Hear me now and listen to me later.

When you take that ultra-complex 6000 character string for BCP and wonder why it's getting truncated to 4000 (which SSMS has problems showing anyhow), remember that @@servername is sysname, which is an alias for Nvarchar(256)... and adding it to a varchar converts the whole string to nvarchar. 

I'll never get those hours of my life back.  Be smarter than me.