Tales From The Field Weekly Wrap Up for the Week of 03-06-2023 & Pineapple on Peperoni

 Hello Dear Reader!  Last week was an incredible week.  We dropped new content on Monday, Tuesday had the Community Round Table, Wednesday MS Tech Bits, and released YouTube Shorts all week long.

We also waded into controversy on Twitter.  It began with a seemingly innocent and funny post by Buck Woody (Twitter | @BuckWoodyMSFT) and well.... it just took off from there.

Needless to say, anyone who doesn't like Pineapple and Peperoni on their NY Style pizza clearly has never tried it and is therefore wrong.  End of discussion.

A quick note.  

*Hawiian pizza is disgusting.  Canadian Bacon is a lie.  Real Canadian Bacon should be maple coated real-bacon.  Not ham.  Ham should not be on a pizza, with the only exception being a supreme pizza.

End note.

My Mom & Dad were over for my youngest daughter's 13th birthday.  Happy Birthday Serenity!  During which my father, who ran a pizza restaurant, and spent over 25 years in the restaurant business. had never tried it.  He did, and he was surprised how good it was.  He even went for a second slice!

On to the recap!

MONDAY 3-6-2023

Andres Padilla (Twitter | @NodeStreamIO) & Neeraj Jhaveri (Twitter | @Neeraj_Jhaveri) kicked off Monday with an introduction to Azure OpenAI.  

This video has proven very popular, I'm not going to ruin it for you.  Head over and take a look.  Andres walks us through registering to gain access to & then shows us some of the Azure OpenAI playground.  

TUESDAY 3-7-2023

Tuesday is the new home to our Community Round Table.  It was a great show, but as always the star of the show is the content from the Azure Data Community.

List of content in order of appearance on the show:

Neeraj

• AWS Lambda Function to import CloudTrail Logs to Azure Sentinel by Sreedhar Ande Twitter @sreedharande

Bradley

• SQLBits Agenda and PowerShell, displaying and searching   By Rob Sewell  Twitter @SQLDBAWithBeard

Andres

• IoT Production Training Pack by Raul Alarcon  

Josh

• Farming from space: How orbital data is unlocking novel agriculture insights by Matt Jennings  Twitter @MattJenningsMS

Neeraj

• Power BI Paginated Reports - Subscriptions by Olivier Van Steenlandt Twitter @Oli_Vsteenlandt

Bradley

• Advancing Spark - Power BI Databricks Connector with Native SQL  by  Simon Whiteley  Twitter @MrSiWhiteley

Andres

• Automatic IoT Edge Certificate Management with GlobalSign EST   by John Lian 

Josh

• Using calculation groups or one to many relationships for time intelligence selection   by Alberto Ferrari Twitter @FerrariAlberto

Neeraj

• Azure Synapse Spark Notebook – Unit Testing   by Arun Sethia 

Bradley

• Provide data lake access with Azure Synapse Analytics  by Patrick LeBlanc, Adam Saxton, Guy In A Cube Twitter @PatrickDBA, @AdamSaxton, @GuyInACube

Andres

• Best Computer Vision projects With Source Code And Dataset by Naem Azam  Twitter @naemazamankon

Josh

• Announcing the retirement of Streaming Dataflows  by Jadelyn Ray Twitter @MSPowerBI

WEDNESDAY 3-8-2023

On Wednesday we released The SECRET Language of Azure SQL!!! WAIT STATS! That's right Dear Reader 3 exclamation marks. This is serious.  It's also not really a secret. Everyone wants you to know about this.  

In the comments I have links to a great script that Paul Randal (Twitter | @PaulRandal) wrote over a decade ago, to a book on the subject that Thomas LaRock (Twitter | @SQLRockstar) has written.  Check out the video then read Paul's article and order Tom's book.  This is a very critical topic for any SQL DBA or Developer to understand.

SHORTS

We release a whole host of Shorts related to the subject of interviewing for a job as a SQL DBA or for a SQL Developer.  There quality gets better as the week goes along.  I switched video editing tools and was able to improve the quality. 

Here's a list:

• SQL Interview Questions - Understand the Versions of SQL
• SQL Interview Questions - Follow Up After the Interview
• SQL Interview Questions - Understanding Wait Stats
• SQL Interview Questions - Understanding Execution Plans
• SQL Interview Questions - How do you pronounce 'SQL'?
• SQL Interview Questions- the Data Community
• SQL Interview Questions- The Worst Question Ever
• SQL Interview Questions- DON’T RELY ON 3rd Party Tools!
• SQL Interview Questions- Know the basics Clustered Indexes
• Next Week SQLBits 2023!!

 We will see how this goes, bit of an experiment on our part.  We had around 1000 cumulative views on this last week alone.  It's a difficult format because you have a max of 60 seconds to get a fairly technical point across.  If there is something you'd like to see we'd love to hear from you!

WRAP IT UP

Alright Dear Reader.  I've off to SQLBits this week.  Stay tuned to the channel.  We have a SPECIAL Monday content drop, our Tuesday Community Round table, Wednesday MS Tech Bits, Interview clips from previous interviews will be dropping, also maybe some shorts from SQLBits. 

As always Dear Reader, Thank you for stopping by.

Thanks,

Brad

24 Hours of PASS Preview: Zero to Hero (I'm the Zero)

Hello Dear Reader!  We are already away into the 24 Hours of PASS Summit Preview for 2014!  I don't know if you heard, but I have a pre-con at the PASS Summit!  More precisely SQL MVP Robert Cain(@arcanecode | Blog) had a great idea for a precon and invited SQL MVP/MCM Jason Strate (@stratesql | Blog) and myself to join him.

We decided early on that we have a great opportunity to showcase how we use PowerShell to complete tasks on the Business Intelligence, DBA, and Cloud engagements and pass on real world skills.  We also want to do it in a way that things are useful.  Our goal is to have things we can give you that will allow you to leave the pre-con and use right away.

We also realized with a bunch of smart guys (and me), presenting we had the opportunity to use Humor and a bit of stage acting.  I'll be playing the role of the Zero in our pre con.

"So Balls", you say, "What's a Zero, and how do you play one? (and why are you explaining this)?"


Great questions Dear Reader!  First let's talk about, what's a zero?  I will be pretending that I do not know how to use PowerShell.  That I don't understand how to use the verbiage, variables, function, modular code design, how to import modules, or do a lot of other stuff you need to know.  I will need to learn from the ground up as if I'm a beginner in the class.

Hopefully me at the Summit (without the awkward flying)

I will ask questions, get explanations, and help bring the audience along.  As the day progresses I'll become a hero using concepts and technology to deliver some end to end solutions.  I'll even take over the Azure PowerShell portion at the very end of the day.

Why am I explaining this?  PASS has an international audience and I'm not a professional actor.  I'll do my best but some may miss the humor in what we are presenting.  Robert, Jason, and myself spoke last night and we didn't want anyone to think that I didn't actually know PowerShell or give reason to doubt why I'm participating in the pre-con.

So sit back today, enjoy our session.  I hope you enjoy me being the Zero, and come to the Summit to find out how to be a Hero with me.

But wait there's more!! Today during out session Robert and I will do most of the talking.  Jason will be live answering your PowerShell questions using the #pass24HOP hash tag on twitter, and answering the questions in the room chat on Twitter as well.  This promises to be a fun session, hope to see you there!

Here is the link to our pre-con.   Here is a link to the 24 Hours of PASS website.  Good luck, happy learning, and as always Thanks for stopping by.

Thanks,

Brad

Powershell 2.0 Required for UCP on Windows XP

So I’m trying to install SQL Server 2008 R2 Utility Control Point, so I can take a look at all that the product has to offer and see how we can utilize it at my current place of business.

For the sake of full disclosure I’m doing this on a laptop running Windows XP SP 3, x86, Dual Core Processors and about 3 GB of memory.

I installed it about a week ago, and set up another named instance and had a SQL Agent Job running stored procedures from Adventure works every minute, to help simulate a work load on one of the databases.

I’ve read how it can take up to 45 minutes to get data posted to the dash board and so I set the jobs and got busy doing other things.   One week later I take a look at my Utility Explorer to see all the beautiful looking screens.

Needless to say this isn’t what I expected to see.  It almost looks as if there is no data.  So I check my enrolled instances to see what status they are running under.

Now I’m really bothered, apparently something has gone wrong.  So I take a look at my SQL Agent jobs to see if the jobs were created and if the history has been running properly.

I see that my sysutility_mi_collect_and_upload job has been failing.  So I isolate just that job, seeing as how collecting and uploading the data is directly related to seeing nice dashboards (or so I would guess).

And YOWZA! This has not been running at all, a quick glance back over the history and I can see this job has never run correctly. Let’s take a closer look at why this is failing.

Step 2 of the job is failing and the error is big and ugly  The important part is the following:

  (Get-Wmiobject  <<<< Win32_MountPoint) |   InvocationName   : Get-Wmiobject  PipelineLength   : 1  PipelinePosition : 1          WARNING : 11/8/2010 12:25:10 PM : 00000000000000000000000000000000000000000000000000000000000000000000000000000000WARNING : 11/8/2010 12:25:10 PM :     ErrorRecord    : Command execution stopped because the shell variable "ErrorActi                   onPreference" is set to Stop: Invalid class   StackTrace

When I set up the UCP I remember seeing one Yellow Warning/Yield Sign.  Honestly I breezed right by it, because if it’s not going to work you get a big red Error sign, right.  Right?  Wrong.

The Warning box was pointing to WMI not being set up correctly.  There are several Powershell class files that are required by UCP.  So the Question is what are they and how do you check that they are there.

They are:

Win32_MountPoint

Win32_Volume

Win32_LogicalDisk

Win32_PerfRawData_PerfProc_Process

Win32_PerfRawData_PerfOS_Processor

Win32_Processor

So how do you check for them?  Open up PowerShell and type get-wmiobject [objectname]

As you can see from above this is the error you will get if you are missing one of the classes.

A little more research shows me that Powershell 1.0 and Windows XP does not contain Win32_MountPoint and Win32_Volume classes.  This was reported on Microsoft Connect at here: https://connect.microsoft.com/SQLServer/feedback/details/540412/sysutility-mi-collect-and-upload-job-failure?wa=wsignin1.0 .

Microsoft Employee Jennifer Beckmann reported that the user was able to get everything working after installing Powershell 2.0

The link above lists a “work around”, by a user named Dryknot.  I would not recommend this solution as it recommended opening up the SQL Agent job and editing the PowerShell Code.  No offense to Dryknot I’m sure that she/he is a perfectly great person, and that they took the time to post a work around to help others shows that they are indeed a great person just trying to help others.  But there are 2 reasons I would not make this change.

1. You are changing the requirements that are being gathered from one class, Win32_MountPoint to another, Win32_MappedLogicalDisk.  So it’s like saying I want to gather information on an Orange and buying an Apple.  One thing is not like the other, even though they are both drive classes.
2. I’m missing 2 classes.  Even if I were to fix the Win32_MountPoint issue, I would still have one with Win32_Volume.  And if you use Mount Point’s you know how critical it is to gather space usage on them.  DON’T CHEAP OUT HERE.  Do not accept substitutes go for the original.

So If you are planning on implementing a UCP on a Windows XP machine make sure that you have Powershell 2.0 running or you could end up with the same results I did.

Thanks,

Brad

******DISCLAIMER********

All advice is AS IS.  There are no warranties or services provided as a result of anything you see here.