Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Monday, June 19, 2023

Tales From The Field Weekly Wrap Up for the Week of 06-12-2023 Cooking, Father's Day, & the Shrinking Household

 Hello Dear Reader! This past week was a busy one.  As we've started to settle into the new house we are transitioning from eating out or having meals delivered, to knowing where the pots & pans are so we can cook again.

I made a really nice beef stew and butter chicken from scratch.  My family is full of picky eaters, so the true litmus test of any meal is the answer to the question, "Is this make it again good?".  The answer to both of those dishes was a resounding yes from even my pickest of eaters.  There was one hold out on the butter chicken, but after a rousing review from the rest of the household my son Will tried it with all of the lack-luster enthusiasm of a 5 year old being told to "eat their vegitables".

I was asleep when he made it home from work and heated it up, but the lack of leftovers in the morning told me I could hang the "Mission Accomplished" banner.

This past Sunday was Fathers Day here and in many countries around the world.  It was a great day over here at Casa Ball.  My Mom, Dad, Mother-in-law, little Sister, beautiful Wife, & the kiddos all went to brunch, we watched a Cubs game (to bad they didn't win), and I grilled out burgers. Also our household and my buddy Neeraj Jhaveri (Twitter | @Neeraj_Jhaveri) put together some videos for you all.


Here's one from my family.




Here's one from Neeraj.



And a big HAPPY FATHER'S DAY to all the Dad and the role models guiding people out there!  Your impact will be felt for generations and is essential to all those that you care for.

Father's day brings the realization closer to home that our household is shrinking.  My youngest is off to visit her Mom until early August and my oldest is off to Summer classes at college.  In the Fall my daughter will be back, but my youngest son goes off to college several hours away.  There is a bitter sweet feeling to all of this.  We've done our jobs properly.  The kiddos are starting to be off in the world.  I will need to figure out how to make smaller portions or have more people over for dinner!

All this to say, I remember when they were little and they wanted your attention non-stop.  If you have kids that are those ages appriceate those times, they will pass by before you know it.  But there are many new adventures still to be had, look forward to those as well.

Alright Dear Reader, let's get to that content!

MONDAY 6/12/2023

On Monday Neeraj Jhaveri released his first SOLO video Microsoft Fabric Decision Trees, Deciding Which Service to USE!!


In this video he walks through some of the documentation that we have for Microsoft Fabric to help you understand what services you should use based on your specific skill sets!


WEDNESDAY 6/14/2023

On Wednesday we posted my latest video Microsoft Fabric, Notebooks, Spark, Writing New Tables to our Lakehouse, & Baseball! I doned my trusty Microsoft Zune t-shirt and walked through how to take data in our Lakehouse that we've created using a shortcut so we do not have data duplication, and then how I walk through some data exploration and write out a data frame as a table in my Lakehouse!


I use this to determine the best day of the week to see my favorite baseball team play, the Chicago Cubs.  The key to this is invoking your imagination to see what it possible when using the service.  If you get a chance I hope you will check it out!


TUESDAY 6/13/2023

On Tuesday we had our Azure Data Community Round Table! It was another great show with my colleauges.


As always the STAR of this show is the creators from the Azure Data Community.  Here is the content in order of appearence: 

Neeraj

Announcing Microsoft Fabric capacities are available for purchase | Microsoft Fabric Blog | Microsoft Fabric  by Mihir Wagle (he/him) Twitter @Ravskkaur

Bradley

Working with ALS – ALS Awareness Month 2023  by Steve Hughes Twitter  @DataOnWheels via @SQLServerCentrl

Dan

Hybrid Flexibility in Azure SQL Managed Instance   by Rie Merrit Twitter @IrishSQL

Josh

Foldable SQL Regions   by Jess Pomfret Twitter @jpomfret

Neeraj

Synapse Real-Time Analytics: Discovering the best ways to get data into a KQL database  by Anshul Sharma Twitter @anshuldsharma


Bradley

Azure chaos STUDIO  by Arun Sirpal  


Dan

T-SQL Tuesday #163 Invitation – What is the best piece of Career Advice you ever received  by Gethyn Ellis Twitter @gethyn_ellis


Josh

Announcing a general Availability of SQL Bindings for Azure Functions     by Drew Skwiers-Koballa Twitter @SysAdminDrew

Neeraj

Things to Do for Father’s Day: 20 Fun Ideas   by Alison Ensign  Twitter @FamilySearch

Bradley

Get skilled on Microsoft Fabric – the AI-powered analytics platform  by Shannon Lindsay Twitter  @shan_gsd


Dan

PostgreSQL Tutorial for beginners   by Daniel Calbimonte Twitter @SQLShack

Josh

Create Your Own Microsoft Fabric Environment  by Kevin Chant Twitter @kevchant



WRAP IT UP

Ok Dear Reader, don't forget we have our Azure Data Community Round Table at 1 PM EST on Tuesday, and today Daniel Taylor (@DBABulldog | Twitter) will have his latest MS Tech Bits posted at 12 PM EST today!!  SECURE Azure SQL Managed Instance Backups with Azure Storage Private Endpoints!! 

Make sure to check that out when it is LIVE.  I hope your weekend was wonderful, if you have something you did that was awesome sound off in the comments!  I love to hear from you.

As always Dear Reader, Thank You for stopping by.


Thanks,


Brad

Monday, May 8, 2023

Tales From The Field Weekly Wrap Up for the Week of 05-01-2023 A new house, a reunion, & 15 Years of SQL Saturday Jacksonville

 Hello Dear Reader, what a week!  On Thursday my wife and I closed on a new house.  We are so incredibly excited!  We owned a house for a very short period of time.  When we bought the house we did not look at the school districts.  

When the kiddos came to live with us full time during the pandemic we quickly found out the schools were..... really bad.  Great neighborhood, beautiful house, bad schools.  So we quickly came to the conclusion that we would have to move.

We found and even better neighborhood, AMAZING schools, but the cost was the house.  We hadn't owned long enough to get much equity.  As a matter of fact we lost quite a bit of money.  We sold during the pandemic before the housing market went crazy.  We thought we would save for a year and then buy a new house in this great neighborhood.  What's the old phrase?  "Man plans, God laughs".  Yeah, the housing market went nuts.  One year turned to two, two years turned to three, and three looked like it would turn into four.  With three months left before our lease expired I did a lot of calculations and found we might be able to get a house. AND WE DID!!!

We had just started to really enjoy doing the small things that make a house yours.  The best part is we only one mile away from our old house, so we get to keep the same awesome schools, we stay in the same neighborhood, and I get to keep my same running & walking routes!

That would normally be enough news for a month, but you know me there is no rest.  After closing on the house I hit the road to the 15th Anniversary of SQL Saturday Jacksonville.

Josh Luedeman (Twitter | @JoshLuedeman), Neeraj Jhaveri (Twitter | @Neeraj_Jhaveri), Daniel Taylor (Twitter | @DBABulldog), Andres Padilla (Twitter | @nodestreamio), & myself all decided we should submit to the conference.  The really amazing part, as much time as we've spent with online we've never all gathered together before.  Josh, Neeraj, & I had met pre-covid during work trips, but Andres & Dan had joined our team during the pandemic.  Our good friend Bradley Schacht (Twitter | @BradleySchacht) from the Azure Synapse Product Group joined us!

We started out with a nice dinner on Thursday and the in person preparations began for our pre-con DBA 101: A Full Day of Fundamentals.  We had a great group of gentlemen that joined us for the day.  We covered a lot of basics about SQL Server and the Cloud, specifically Azure, but we made sure people understood how to translate that skill set. 

We did a lot of group exercises, starting with building on premise, hybrid, and cloud architectures. Then applying SLA's, RTO, RPO requirements so we could discuss how to configure backups, high availability, and then disaster recovery.

We reviewed basic security within SQL Server which translates to Azure SQL, as well as some basic Identity topics covering AD & AAD.  Great group of people and a great way to kick off Friday.

Friday night took us to the SQL Saturday Speaker dinner and then a group activity we had planned together watching the Guardians of the Galaxy 3.  No spoilers.  Great movie and a really nice send off to the trilogy.

After that we headed back to the hotel, hung out, had some nice conversations, and then went to sleep so we could start the big event, SQL Saturday.

All of us who have been in the community for a while have seen the ups and downs that happened during the pandemic.  We all had a lot to deal with in that time, and there were some devastating losses for me personally.  Maybe next year I will write about it, but there are some areas that are still to raw for me.  I'm sure there are for a lot of people.  I tried to remind myself during that time that in the middle of something you cannot see the end, nor can you see the recovery.  I held fast to the idea that they would occur.

In the SQL Community I have made so many close deeply personal friendships, that calling people "friends" hardly does justice to what I feel.  Of those people Karla & Rodney Landrum are up there at the top.  The last time I saw them they came to Orlando to visit.  Karla, Rodney, Sue, and myself went to Chef's in the Paris district of Epcot.  We also had them over to the house.  I cooked, Karla mixed the drinks (which I loved still to this day!) and we had a fantastic time.  That was 2019.  There is no way we could have known how long it would be until we saw each other again, what we would have encountered.  It was all I could do not to breakdown crying as I hugged Karla.

I didn't realize she would be there, and the joy I feel even know days removed is palpable. I'm so glad we are having in person events again.  I'm so glad I get to see my friends, donate my time, and teach people.  If you have a SQL Saturday happening near you, you've got to check them out, I cannot recommend getting involved enough.

Ok, over to the recap.

MONDAY 5-1-2023
On Monday Josh Luedeman published his first video, way to go JOSH!!  Josh's video Setup Windows with Chocolatey for Data Developers walked us through using the Chocolatey packages to install and configure post-Windows installation tasks easily!


He installed Git, WSL 2, Ubuntu, Spotify, Discord, PowerShell, Azure Data Studio, SQL Server Management Studio, and Signal!  If you find yourself setting up a new Windows computer, this is a fantastic shortcut to the installation process. 

WEDNESDAY 5-3-2023
On Wedneday we released my latest video on creating a Dynamic Pipeline in Azure Synapse & Data Factory!!  This is a process that I've walked many customers through over the years.  The key is we are trying to minimize the number of Source & Sink data sets created to import data in this video.

Dynamic Pipelines are incredibly powerful and can be used in many different ways. This was just one of them, but based on the reaction from you Dear Reader, we will be showing a few additional ways this can be used!

TUESDAY 5-2-2023
We kicked off the Azure Data Community Round Table this past week discussing the weather and how much we were all looking forward to getting together at SQL Saturday Jacksonville.


Of course as we all know the star of the show is the Community.  So without further ado here are the links in order of appearance on the show:

Neeraj
The Plain-English Guide to Data Warehouses [+ Examples]  by Swetha Amaresan  Twitter @swethamaresan

Bradley

Andres
Opening the eyes of the machine: Computer vision with AutoML by Gavita Regunath & Dan Lantos Twitter @gavi_sr

Josh

Neeraj

Bradley
SQL Server Row Count for all Tables in a Database by Dattatrey Sindol Twitter @dattatreysindol via @mssqltips

Andres
Building a startup using Azure Computer Vision by Nikhil Sehgal Twitter @nikhilsehgal_ai

Josh
Chocolate Install Git Config by Gary Ewan Park Twitter @gep13

Neeraj
Best practices for Purview and a federated way of working   by Piethein Strengholt Twitter @phstrengholt

Bradley
EightKB 2023 – Schedule and Registration by Andrew Pruski via SQL Server Central Twitter  @dbafromthecold via @SQLServerCentrl

Andres

Josh
Monitoring Deadlocks in Azure SQL Managed Instance by Bradley Ball Twitter @SQLBalls


WRAP IT UP
What an amazing week we had last week, and it's only going to get better!  This week we have Andres & Neeraj presenting Computer Vision with Azure ML Studio that will be live today at 1 PM EST.

I will have another video this Wednesday, and of course our Tales from the Field Azure Data Community Round table will be this Tuesday Live at 1 PM.

I'm sure we will figure out some adventure to have this weekend, alright Dear Reader.  Time for me to head out, as always Thank You Dear Reader for stopping by.

Thanks,

Brad




Monday, October 17, 2022

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

Tuesday, July 30, 2013

Partitioning Deck, Demos, and Recording Live!

Hello Dear Reader!  This week finds me up in Jacksonville at Pragmatic Works HQ.  I'm a little behind in getting the blog up for last week Webinar on Partitioning in SQL Server 2012.  We covered a lot of great things in the webinar and I wanted to recap some of them.  This was the first time I’d given this presentation and over 300 people tuned in to watch!

I’d like to say a quick Thank You to all the people that spent their hard earned time with me.  If you would like to download anything from the presentation Click Here for the Deck, Click Here for the Demo Scripts, and click here to watch the video recording of the presentation.  All of this is also up on my Resource page.

I had a hiccup on the Piecemeal Restore Demo that I did and I wanted to review it.   Unfortunately I was playing around with the script and just to be on the safe side had backed up my data base.  At the header of the script I inserted a restore command.  I got an error running the script and wanted to fit a couple other demos in and skipped over it.  So now we can tackle it.

“So Balls”, you say, “What’s a Piecemeal Restore and WHY would I need to use one?”

Excellent question Dear Reader! The Piecemeal restore was introduced in SQL 2005.  It gave us a sequence of steps we could take in order to recover a portion of a database online at a time starting with the Primary Filegroup.  This allows us to bring critical portions of the database online for quick access.

If you had a very large database with a lot of historical data you wouldn’t want to make the business stay offline in a critical outage while all the historical data is restored.  If you have TB’s of data that could take hours!
A Piecemeal restore gives us the ability to bring a segment of the database online at a time.  A very easy way to demonstrate this is using partitioning.

DEMO TAKE II

Make sure to use 02 Demo_a Set Up demoInternals_Partition.sql to set up our demoInternals_Partition Database in the scripts above.  

First let’s take a look at our table.  We will use sys.partitions and sys.indexes to see how the data is distributed across filegroups.  

This will also let us see the Clustered Index and Non-Clustered Index we created.

SELECT
     OBJECT_NAME(sp.object_id) AS tableName,
     si.name AS indexName,
     sp.partition_number,
     sp.rows
FROM
     sys.partitions sp
     JOIN sys.indexes si
     ON si.object_id=sp.object_id AND si.index_id =sp.index_id
WHERE OBJECT_NAME(sp.object_id) ='myTable1';



You can see from the count we have 18,000 rows in our table. Now that we’ve looked at our data the next thing I need to do is backup my database.  I’m going to perform 3 types of backups.  First a Full backup, secondly I’m going to perform a Log Backup, third we’ll perform a tail of the log backup and leave our database in a restoring state. 

*NOTE* You can use a Piecemeal restore with all recovery models however I’m running in Full recovery for the sake of the demos today.

USE master
go
BACKUP DATABASE demoInternals_Partition TO DISK=N'C:\Backups\demoInternals_Partition2.bak' WITH INIT
GO
BACKUP LOG demoInternals_Partition TO DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH INIT
GO
BACKUP LOG [demoInternals_Partition] TO  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  NO_TRUNCATE , INIT, NORECOVERY
GO


Looking at our database I can see that the commands have completed and we are in the Restoring state.  Our database is completely inaccessible, I know I know snapshots…. But that’s not the point so stick with me Dear Reader.  First let’s restore our primary data file.

USE master
GO

RESTORE DATABASE demoInternals_Partition FILEGroup='primary'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH PARTIAL, NORECOVERY
GO
RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery


RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO


If I refresh my SSMS Object Explorer Window it looks like the database is back online.  However, I know better.  The only filegroup online is the Primary Filegroup.  I like to keep this filegroup small with only the metadata that is there when the database is created.  If I try to query the table dbo.mytable1 it should fail.  Let’s do that real quick.  We’ll query one of our DMV’s about our table that we cannot acces, let’s say sys.indexes.  Then we’ll do a very simple query against the database to get record 1.  Remember our 1st partition had 2000 rows in it.

use demoInternals_Partition
go
select
     object_name(si.object_id)
     ,si.name
     ,si.type_desc
     ,si.name
from
     sys.indexes si
where
     object_name(si.object_id)='mytable1'



select
     *
from
     dbo.myTable1
where
     myid=1


As you can see we got results from our DMV, but we couldn’t even access row 1 in our table.  Now let’s bring FG1 online.

use master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG1'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
GO

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Now let’s try our query again.
USE demoInternals_Partition
GO
select
     *
from
     dbo.myTable1
where
     myid=1



Success. We can get all 2000 rows in the FG1 partition. If you want to get the same error as before for FG2, just change the 1 to a 2001.  This is a very flexible process that allows you to assign Business level SLA’s to different segments of your Database.  You do not need to use partitioning to do a piecemeal restore.  You could just use separate FG’s and segment tables by business segment.

Let’s bring online FG2 and FG6, leaving FG3, FG4, and FG5 still offline.
USE master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG2'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG6'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Now’ let’s execute the following queries:
use demoInternals_Partition
go
select * from dbo.mytable1 as FG2 Where FG2.myid=2500
select * from dbo.mytable1 as FG6 Where FG6.myid=12001
go
select * from dbo.mytable1 as FG3 Where FG3.myid=4500
select * from dbo.mytable1 as FG5 Where FG5.myid=8000


The Queries against FG2, and FG 6 Succeed.  The Queries against FG3 and FG5 failed.  Okay now let’s bring all the tables online.

USE master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG3'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG4'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG5'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

WRAP IT UP

We can now query from start to finish with the entire database online.  This is a pretty simplistic demo.  It is meant just to convey the different architectural options that are available for a Piecemeal restore.  As you can imagine this is something that could be utilized in a DR scenario to meet SLA’s and RTO.

This is a very powerful tool in the arsenal of the DBA.  You want to test this, and make sure that it meets your business needs before implementing it.  Don't forget to get the demo's and scripts from the presentation.  

As always Dear Reader, Thanks for stopping by!

Thanks,


Brad