Tales From The Field Weekly Wrap Up for the Week of 10-10-2020

 Hello Dear Reader! We had a big week on Tales From The Field!  We've got some great stuff planned for you this week, but before we get to the Live shows tomorrow 10/18 & 10/20, I wanted to do a quick recap of last week.  

Ignite is always an exciting time, announcements are in the air, and we have truly arrived at what I like to call Fall Conference season.  This is a lot like pumpkin spice season, but technology flavored. ...it still has a lot to do with Starbucks as we are all in line to get coffee.

Ignite returned to an in-person gathering at the Washington State Convention Center for the first time since 2019 when it was in Orlando.  I always love when it is in-person.  Hopefully, we have more in person with a streaming component next year.  It is really impowering to allow the millions at home who cannot attend in person to attend remotely. 

All right enough talk, let's get to the shows!

TUESDAY 10/11 SHOW

Last week was Microsoft Ignite and we kicked off our show with TWO big guests.  We had Niko Negebauer Sr. PM from the Azure SQL Managed Instance Product Group, (Twitter | @NikoNeugebauer) join us on the show!    For years Niko was a Microsoft MVP, contributing to the community as a chapter leader, Presidente of the TUGA in Portugal, a PROLIFIC & amazing blogger.  Presenter at many international conferences, and user groups.

We also had Anna Hoffman, (Twitter| @AnalyticAnna) Sr. PM from the Azure SQL Data Platform join us!  Anna is a Data Scientist, The Host of Data Exposed, the co-host of Something Old Something New which is a series about the math behind data science & how it applies to Azure ML, presenter at International conferences, one of the most recognizable faces in the world of Azure Data. 

I was the host!  Neeraj won!!  We discussed our favorite Ignite experiences and Niko flipped the scripted and asked us questions.  Check it out at the link above.

THURSDAY SHOW

On the Thursday show we had the Community Round table and covered Community & Ignite content all in on location.  Here's a summary of what we covered on the show:

Josh

a.10 for 10: My 10 Favorite SQLPerformance Posts  by Aaron Bertrand Twitter @AaronBertrand

Bradley

a. On the Floor of Microsoft Ignite: Day 1 Announcement Thoughts by Joey D’Antoni Twitter @jdanton

Neeraj

a. Microsoft Ignite 2022: What to Expect from This Year’s Event by Joe Kuehne Twitter @BizTechMagazine

Andrés 

a. Azure IoT Edge Integration with Nvidia Deepstream by Emmanuel Bertrand Twitter @emmanuel_B_V

Josh

a. The Dangers of Dynamic SQL and How to Avoid Them by Rob Farley Twitter @rob_farley

Bradley

a. Microsoft Ignite 2022 – Azure Data Platform Update by Wolfgang Strasser Twitter @wstrasser

Neeraj

a. Ignite 2022- New Features and Updates for Ignite 2022 by  David Allen Twitter @onmsft @davidpaj1978

Andrés 

a. High Performance Real Time Object Detection on Nvidia Jetson Tx2 by Prof. Lee Stott Twitter  @lee_stott

Josh 

        a. Stop Using Production Data for Development by Thomas LaRock Twitter @SQLRockstar

Bradley

a. Azure SQL and Azure SQL Managed Instance - Backup retention polices Loading …  by Paloma Garcia Martin Twitter @PalomaGarcia40

Neeraj

a. Introducing-rankx-in-dax by Marco Russo Twitter @marcorus

Andrés 

a. Microsoft Ignite Big Book of News 

Josh

a. New PowerBI Implementation Guidance by Melissa Coates Twitter @SQLChick

Neeraj

a. Incremental Refresh and Hybrid tables in Power BI: Load Changes Only by Reza Rad Twitter @Rad_Reza

Bradley

a. Introducing assessment tooling for Oracle database migration to Azure SQL and PostgreSQL- Preview  by Neel Ball 

WRAP IT UP & SHOUT OUT 

We love our Sr. Escalation Engineers at Microsoft.  These folks are our hero's and our firefighters.  When you have a big issue, you call and put in a ticket.  These are the folks that come in when that ticket is escalated.  One of the blogs that we featured was by Paloma Garcia Martin and she was so kind to give us another Shout Out on their team blog last week!  

Speaking of, what a GREAT TEAM BLOG!!  It is the Azure Database Support Blog and you should go and check it out!

More guests and more great content to come this week, hope to see you there!  And as always, thank you for stopping by.

Thanks,

Brad

T-SQL Tuesday #46 Rube Goldberg Machine aka Automating Deleting Older Certificates

Hello Dear Reader!  This is the second Tuesday of the month and you know what that means, T-SQL Tuesday the largest blog party on the Intrawebs.  T-SQL Tuesday is the brain child of SQL Community member extraordinaire Adam Machanic(@AdamMachanic | Blog), also the inventor of the word “Twote”  as in “To misquote a Tweet”, when used in a sentence it sounds like “He Twoted me wrong”.  This month our host is Rick Krueger(@DataOger | Blog).  So Rick what’s our topic?

“My first exposure to Rube Goldberg Machines was playing the game Mouse Trap as a child. I work almost exclusively on the SQL development side of the house, where we sometimes build crazy creative solutions to solve business problems. We generally know the ‘right’ way to do things, but pesky issue like budgets, personnel, and deadlines get in the way. So, we channel our inner MacGyver, grab a handful paper clips and some duct tape, and then do things with SQL Server that we know shouldn’t be done (in an ideal world). And we hope nobody ever finds out how we bent the rules, because we know they will judge us (as we would judge them) and call our work a <gasp>HACK</gasp>.

So, if you would please, dust off one of those skeletons and tell us how you got really creative with SQL Server, instead of doing it ‘the right way’. In other words, tell us about your ugly SQL baby. If you’re worried about saving face, feel free to describe how you would have implemented the solution if you lived in that ideal world.”

I love mouse trap and MacGyver!  Over the years as a DBA sometimes you have to work with what you’ve got.  Other times your boss says do A, you say the best way to achieve A is by doing B & C and they say do A.   I’ve got two of these that I can think of off the top of my head.  One we used Change Data Capture in lieu of Auditing (don’t ask me why, because that version of SQL also had Auditing.  Oh Hello A…..).  The other may actually prove useful.  Which one to choose from?

“So Balls”, you say, “What’s the useful one?”

Good call Dear Reader, we’ll go with the useful one!

OUT OUT D@MN SPOT CERTIFICATE

When you are using Transparent Data Encryption one of the most important things is the certificate.  Once you enable it on a production database that certificate is just as important as your database backup.  Why?  Because in case of a catastrophic failure that backup is dependent on the certificate.  If you cannot restore the certificate to a new instance your backup is useless.  *There are some work arounds to this using backups of the Master DB, but we’ll save that for another day.*

When you look at setting up maintenance plans for your server you should create a job to back up your certificate daily.  A certificate is only 1 KB in size.  Very tiny file.  If you use a private key to encrypt your certificate it is only 1 KB in size as well.  So if you leave a year of them on your hard drive you haven’t taken up 1 MB.

As a DBA sometimes you can be anal retentive a neat freak.  I don’t keep a year’s worth of backups on hand, why would I keep a year’s worth of certificates on hand?  I’d like a process to automatically delete them and only keep the last two weeks on hand, or month on hand whatever matches up with my backup retention policy.

The problem is the automated cleanup task doesn’t work.  Sure you can go in the maintenance plan wizard, make one that looks in a directory for a .CER file, but the true problem lies in the data storage.  You have to custom script out the certificates.  If you didn’t think to add a line to the backup set history table with the extension of .cer and .key and the path to your Private Key or Certificate backups then the job won’t work.

Inserting records into the MSDB tables could work, but as a DBA new to TDE that thought hadn’t crossed my mind.  I wanted a way to back up my certificates and delete my old ones.  So I built one.

MY RUBE GOLDBERG MACHINE

This is a demo I do in my TDE presentation.  It’s up on my Resource Page and has been for some time.  Today I realized I’d never blogged about it.  My scripts heavily use XP Command Shell.  I had an audit setting in my environment that wouldn’t allow that to be on my servers.  So in this script I turn it on in the beginning and off at the end.  The nice thing about the script is I unit tested it and even if there is an error in the script the sp_configure settings are server level commands that occur outside of transactions, so they run no matter what.  The script runs quick, but it will make logged entries in the SQL Server Error log stating that XP_Command shell was turned on and off.  My audit team could live with this so I was able to implement it.

I also like to use a private key and a password for my TDE Encryption.  I don’t want the password sitting around in plain text in the job either.  So I make a database called TDE.  In it I have one table called tdeKeys.  I put two columns in there one is the name of my certificate that a private key will be created for the other is the password to use for that private key.  In secure environments you could set up column level encryption to ensure the password is not in plain text even in the table field.  The demo scripts I’m going to give you doesn’t use column level encryption.  It contains a function that retrieves the Password for the Certificate Name.

PART 1 Create TDEDB Attachment

Next we will create the dynamic script to back up the certificate.  Note that I backup the Master Key as well.  If you are using column level encryption you’ll want a copy of the Master Key.  You’ll need to specify the path that you want to back up the certificates.  Also you will need to specify the certificate name.

PART 2 Create Dynamic Backup Script Attachment

Finally we will create the script that will use xp_cmdshell to transverse directories to manually delete our backups.  You will need to edit the file path in this script and insert the Master Key and certificate names in line 74.  Finally on line 103 you will need to alter the DATEADD function.  Right now it would only keep 4 days of certificates on hand, you’ll need to edit the DATEADD to match up your backup retention policy.

PART 3 Create Dynamic Certificate Cleanup Script Attachment

Want to see the whole presentation live?  I’ve done this for Pragmatic Works Training on the T’s, click Here to watch.  You’ve got to sign up for a Pragmatic Works account if you don’t already have one, and you’ll get free info on all the free training we do monthly!

OVERKILL

“So Balls”, you say, “Is this over kill?”

<soapbox>

Well Dear Reader it depends on your environment.  You must consider Level of Effort and Level of Response, LOE and LOR.

LOE is one part you one part the hacker.  The more secure you make something the less likely that a hacker will keep going for it, or how far they will bother to go.  On your part it is how far you are willing to go to do your job.  We can also get dissuaded from going the extra mile sometimes.  Your LOE should be governed by your organizations LOR.

LOR is the response that your organization will have to the event.  One thing I like to tell folks is that if you are ever in the position that your security has been breached, and you are then talking to your boss, his/her boss, the CIO, a high ranking officer in the military, or a/multiple high ranking government official(s).  Trust me when I say that you want to be able to say you took every last step possible to protect the data under your care.  The more detail you can provide the better.  So overkill?  Maybe.  CYA.  Absolutely. Thankful that no fault on your part was found and you still have a job? Yep.

Having been in this position trust me take the extra couple steps, if you ever need it you’ll be glad you did.

</soapbox>

Thanks to Rick for hosting this today, and as always Thank You Dear Reader for stopping by!

Thanks,

Brad

The 200 Billion Dollar Tweet

Hello Dear Reader!  Ever try to convince someone that they should be paying attention to Twitter?  A co-worker, Vice President of something or other, CMO, CEO, or CIO?  

I’ve had conversations over the years with many people as to why Twitter is important to their business.  If they don’t have a dedicated “Social Media” guru or their Corporate and Legal Policies do not yet “allow” an official Twitter presence, they can still effectively market, and monitor marketing, using Hash Tags.  It’s a way of dipping your toes in the water without really diving in.

Alas most of the conversations revolve around what “real” results they can gain from Twitter.  Most lack the vision or understanding to see how it could benefit them.  Tying a dollar amount to Twitter can be difficult.  Most companies don’t track or publish earnings related around a Twitter campaign.  They do with traditional advertising, but with social media it’s hard to tie a dollar amount to it.

I was a little busy yesterday with work.  Like most days I poked my head out on Twitter and checked several new sites.  I even received some push updates to my phone via USA Today, Fox News, and the New York Times.  So Imagine my surprise when this morning while standing in line at the Starbucks in the Charlotte airport that I see this.  Twitter Terror Hoax Rocks Wall Street, AARRRUUUU?

Learning about social media occurrences the next day via the new paper.  How old fashioned.  Loosing 200 Billion dollars over 80 typed characters, not so old fashioned.

Twitter first came out I laughed at it.  Why would anyone want to have a website version of Instant Messenger, remember AOL, limited to 140 characters?  Turn around several years and I have firmly flip flopped, Oxymoron, and have drunk the Twitter Kool-Aid.

What changed my mind?  Becoming a presenter in the SQL Server Community.  I went to my first SQL Saturday and caught the bug, read TSQL Tuesday #41 Becoming a SQL Server “Presenter” GETINVOLVED!  One of the things I did was examine the really good speakers and see what they were doing.  They blogged, they were on Linked-In, they were on Twitter.  So I did all of that. 

Only after the fact did I find #SQLHelp, the hashtag that allows people to ask whatever SQL question they have and get free advice from some of the Top SQL Talent in the world.  There were situations where I had production outages and we, my fellow DBA’s and managers, used Twitter to post questions and get deep technical answers quickly.

“So Balls”, you say, “YAY you’re on Twitter.  Now what about this 200 Billion dollar Tweet?!”

At 1:07 pm, East Coast Time, the Associated Press Twitter account, currently suspended, was hacked.  The 80 characters contained within the Tweet typed by the hackers seemed particularly potent given the turmoil of the last week.  “Breaking: Two Explosions in the White House and Barak Obama is injured”.  I’m not alone in my conversion, apparently the people trading on the New York Stock Market follow the Twitter as well.

By 1:09 pm the Dow had fallen over 137 points.  While it rebounded quickly, 1:12 pm when Sam Hananel (@SamHananelAP) Tweeted, “Please ignore AP Tweet on explosions we’ve been hacked”.  The market corrected.  But did it?

My Dad does a lot of day trading.  He’s studied up on it, and I’ve learned a tiny (very tiny) little bit about it vicariously through him.  You can take options out on Stocks termed calls, where you bet they will over or under perform.

Based on their performance you get your money back and a little extra.  The most notorious case of this came during 9-11 where some nefarious people had bet on the airlines to lose money.  In regular terms though this is used daily, sometimes you win, sometimes the company you bet on wins.  It's part of our system.

*It’s a lot more complicated than that, but we’ll stick with the very basics.*  Think betting, but using the stock market.  There are also automatic options that you can set through e-investing web sites.

So it could have affected Calls.  People could have lost money.

Another way that people could have lost money on this?  Using automated software to monitor stocks.  

Quick example, I like Disney.  So I go buy some Disney stock.  Disney is a non-essential good (basically).  They make entertainment, theme parks, dresses that my daughter likes to wear, movies my kids like to watch (DAD include on this).  I don’t NEED Disney stuff.  We get it with disposable income.  We NEED food.  The mortgage HAS to be paid.  Electricity, need that too!  So when there are hard times disposable good take a hit.  Look at the stock market when 9-11 happened, the Mortgage Crisis, and other such events.

So I buy 100 Disney stock at $35 a share for $3,500.  If something happens and Disney stock drops $10 per share, I could set up a pre-arranged sell order.  At $25 sell it all.  Let’s say the stock market tanks Disney stock because a Hacker gets into their official account and announces “Disney to go bankrupt, 10,000 employees to be laid off immediately”.

My Stock dips down to $23, while I’m at work.  My e-banking software kicks in and sells all my Disney shares.  My money is down to $2500 and I’ve just lost $1000.  When it is correctly reported that Disney is not going bankrupt and is having a record Quarter beating all earnings estimates, and the stock now corrects to $40 a share, I’m no longer a stock holder.  Not only did I take a loss, but I was out on the Win too.

I just lost $1000.  I just missed out on gaining $500.  There is no customer service department.  No receipt to be returned.  I’m out.  The market was correctly reported.  Based on my input a sell order happened.  Potentially A LOT of sell orders from all sorts of people happened. 

WHAT DOES THIS MEAN

Security and Reality.  First Security.  It means some real people lost real money yesterday.  We’ll have to see what the fallout of this is.  It also means that if you do have a Corporate Twitter account you probably need to look at changing the password every 90 days, and using something like KeePass to manage your password.

Warnings aside it also means we can now put a dollar amount on Twitter.  USA Today estimated that $200 billion dollars in broader market capital was lost when the Tweet occurred.  The market rebounded and that capitol was regained, with some people suffering losses.

Before I could even finish writting this blog, there was a new story.  SEC, FBI probe fake Tweet that rocked stocks.  And another Twitter working on two-step authentication. 

Now Reality.  The financial world is following Twitter and most other forms of social media.  The importance of effectively using this channel to communicate with customers and the world at large is only going to gain importance as time goes on.  The most expensive Tweet in the world so far is now worth $200 Billion dollars.  How much will the next one be worth?

As always Thanks for stopping by.

Thanks,

Brad