SQL Server 2016 Stretch Database

New to SQL 2016 is the ability to send archived data off-premises to MS Azure storage, in the form of a "Stretch Database."  Sounds like a great idea in theory but do your testing and bust out the calculators before you put production data in the cloud. 
Things I like:
Not fond of:
I'm pretty sure I could roll my own "stretch" function into a Azure SQL Database, and I'm an admin much more than a developer.
Maybe down the road this will be better, but right now its an expensive alternative to a USB drive from Fry's, or a NAS/SAN upgrade.
Are/were you planning to use Stretch?  Have a differing opinion?  Let's hear it!



I was participating in an email interview about my SQL career and opinions and one of the questions was basically "What's the worst SQL thing you've inherited?"

I gave them this list:

o   SQL 2008 R2, RTM
o   Incorrect Memory configuration
o   Full recovery model on data that only changes once a week at most
o   ZERO documentation
o   New data is imported to a new table and a UNION ALL is modified to add that table
o   ZERO documentation
o   Stored Procedures have no comments, poor formatting and developer names in the name of the sproc
o   Autogrow is 1MB, data imports are hundreds of MB each
o   Everyone is sysadmin, probably including you…
o   Change control process is intentionally shortcut and ignored on this internal production system

o   Ownership changed to me in December, then was yanked back 3 weeks later with developers overwriting my fixes in prod.

Really....all that on one server!

Upgrade your fries broseph!!!



SQL 101 Blocking vs. Deadlocking - in English for the Non-DBA

Plain 'ol English....


You walk into Starbucks and there's 1 guy ordering.  He finishes, then you order.  That's blocking, and its perfectly normal, expected and acceptable.

You walk into Starbucks and there's 1 guy ordering for 15 people in his office.  You're willing to wait, but not forever.  That's moderate blocking.

You walk into Starbucks and there's 1 guy ordering, 7 people in line and they are all ordering drinks with names too long to remember....you walk out.  That's excessive blocking with a failed transaction (you).

Blocking in SQL Server is just transactions that need the same resource lining up to take their turn.  Its by design and is a good thing.   Excessive blocking needs to be fixed.


You have 2 children.  Each has a toy, and at the same time they each grab for the toy the other one has, but won't let go of the first one.  This is a Deadlock.   SQL Server has a built-in process to resolve this.  They gave it an official name, but I call it "Mom."   Mom steps in and breaks it up...automatically.  This is a code issue 99% of the time...profile it, trace flag it, whatever..but find and fix your application code.

Does that help?

They are NOT the same thing...Deadlock is closer to Locks than blocking.

Next up (maybe): Replication and Log Shipping are not the same thing...quit using them interchangeably, especially when you mean "Clustered"

Have a nice day!



Yes, please...ignore my advice....

Short one today:

I tell the upper level manager that ALL of his data is at risk due to one root issue.   Got him good and scared...he totally believed me.  And did absolutely nothing to fix it, or even explore possible fixes, which would have been a good exercise.

Swept it under the rug.

Seriously...one disgruntled employee with minor hacking skills and this place goes away.


No fries for you.

**  This was a long time ago, so no...you can't guess who I'm talking about by reading my LinkedIN profile :D


Unsupportable...thats what you are....

With apologies to Nat King Cole :)

Database backup failing

Write on "D:\backup\MyDatabase\MyDatabase_20150208104103883_D.bak" failed: 665(The requested operation could not be completed due to a file system limitation) [SQLSTATE 42000] (Error 3202) 

Hey, customer...let not put backups on O/S compressed drives please.

Oh...and why do you have your MDF/LDFs on the same drive...also compressed?

Resolution - punt it back to them and tell them SQL Server on compressed drives is unsupported config by Microsoft and by me.   Uncompress and expand or add a drive and uncompress.

Sheesh.  How many worst practices can you get in one server?


Monitor for blockings...

Short and sweet...

Ticket for error 1105.   Teammate response: No blockings on server, monitor 4 days.

Ticket for error 601.  Teammate response: No blockings on server, monitor 4 days.

Ticket for login failed.  Teammate response: No blockings on server, monitor 4 days.

I.  Kid.  You.  Not.

Someone needs to be fired.  Or congratulated for making me look like a SQL SERVER GENIUS!!!! by comparison.

That is all.



Wow...its been a long time since something went so sideways I felt compelled to write about it....

But don't mistake that for there not being any craziness!


If you are going to do a major upgrade to your very large database, please consider the following:

Not only do you not get fries, you now owe me some! (Waffle fries from Chick-Fil-A will be just fine, thanks.)

That is all.

This page is powered by Blogger. Isn't yours?