DallasDBAs.com

Explaining SQL Server in plain english

Header Right

TSQL2sday

TSQL Tuesday: Changing your mind

by Leave a Comment

 

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Alex Yates(b|t), who has asked us to write about “Changing Your Mind“

My best technical example of this is something I wrote about in January 2017, so I invite you to read the original post:

PowerShell for DBAs – why I changed my mind.

In addition to the 2 main reasons I gave then, I am adding a 3rd:

dbatools.io has become a very solid and almost all-encompassing way to manage SQL Server and will be a required skillset in 3-5 years at any large SQL shop.

That’s it for this month!

Thanks for reading!

Kevin3NF

T-SQL Tuesday: Your Fantasy SQL Feature

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kevin Chant (b|t) who has asked us to describe “Your Fantasy SQL Feature“. (This is also the first time I’ve made a blog post for T-SQL Tuesday.)

The Problem: As a junior DBA, part of my job involves checking job failures for 2-3 clients each day, which we have a script in-house for. Every once in a while, a job actually fails! So like any DBA who knows that you can go to the Job Activity Monitor under the SQL Agent to view details on job history, i do exactly that only to find it’s a job that ran an SSIS package and gave me this very informative error message:

That’s super helpful, thanks!

So now i have to:

  • Check the step properties
  • Look at the path for the SSIS package
  • Drill down into the integration services catalogue
  • clickity, clickity
  • Wait which folder was it in?
  • Go check the path again
  • clickity clickity
  • There’s 12 different packages with very similar names:

  • Resize the window or scroll over to see which one i care about
  • Finally, open the All Executions Report to get a very helpful error message:

Gee darnit, i’m out of coffee.

The Solution: Just put a button in the failure message/job history that does all that for you.  Or a hyperlink. Whatever.

Kevin Hill (b|t) has made a script that reads the SSISDB tables, but often takes a while to run with only the existing Microsoft indexes. Especially if you are keeping crazy amounts of history…like the MS default 365 days, on a busy system.

/*Please work*/,

SQLDork

T-SQL Tuesday #118: Fantasy SQL Feature

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kevin Chant (b|t), SQL dude and fellow cyclist!  Kevin wants to know what our “Fantasy SQL Feature” is.

I asked my LinkedIn connections a very similar question in my “Question of the Week” there.  Hit the link to see a lot of different responses.

One of the recurring themes and possibly the most common was:

Load Balanced Writes.  Unless I’ve completely missed something, there is nothing in SQL Server natively that will allow you to write to different copies of the database (leaving out Merge and Bi-Directional Transactional Replication, because they suck and don’t scale).

Don’t get me wrong…I love read-only replicas in an Availability Group, replication for reporting, or even delayed Log Shipping.  They are wonderful for taking the read traffic off an OLTP box, but if you are dying under heavy load in a very optimized setup…oh well.

Bonus Fantasy: Give me a button in the Log Shipping Setup GUI (Database Properties>>Log Shipping) that just says “Re-initialize”.  Sometimes LS just falls apart and its easier to tear it down and start over.  Even easier is to just back up the db, restore over the Secondary db and go.  But, for the small business using LS for poor-man’s DR and no DBA on staff…give me a button.  Ideally that button calls sp_ReInit_Log_Shipping @DBName = ‘MyDB’.  But someone will have to write that first.  I am not that someone.  This may already be in the DBATools.io set of toys, but I haven’t looked, and again…small customers need a button in a GUI.

Thanks for reading!

Kevin3NF

T-SQL Tuesday: Fantasy SQL Feature

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t). This month’s edition is hosted by Kevin Chant (b|t), who has invited us to share our fantasy SQL feature.

From Jeff, The Ironic DBA (Dallas DBAs Apprentice):

I haven’t been poking around in production servers very long, and so far my main responsibility is customer server reviews. I’ve blogged a couple of times about tweaking existing scripts to massage the date output from the Error Logs and Job History for readability.

One of the obstacles I’ve identified working with SQL Server is the sheer depth of the product. There’s so much to learn it’s hard to get up to speed quickly. With Microsoft’s recent advances in AI, I see a potential area where they could add a nice little feature inside SSMS.

Take a look at this video from @Patrick Leblanc (b|t) (one-half of Guy in a Cube) where he demonstrates a Power BI feature called “Column from Examples”—specifically where he plays around with datetime formatting at about 2:15.

Essentially, Power BI uses PowerQuery to let you provide examples for column formatting and then uses the underlying PowerQuery AI to figure out what you want. Then, it writes the appropriate M Language formula for you.

A similar, but relevant, implementation within SSMS would be a pretty cool time saver. SSMS already writes T-SQL scripts for you anytime you use a wizard, so having it write T-SQL commands that output a desired style of formatting isn’t too far outside the realm of possibility.

What are some other ways you could see PowerQuery implemented within SSMS?

T-SQL Tuesday: Giving Back

by 2 Comments

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Riley Major (b|t) who has challenged us to “Pick some way you can help our community“…but not just give a few sentences on how you want to help, but to actually put feet, brain cells and dates to it.

Community service is something I’ve been involved in for decades in many different ways.  Within the SQL Server specific community, I got serious about it a couple of years ago when I changed the nature of this blog from “weird stuff I saw at work today” to “here is some info you can use to make your life better today.”  I also started speaking at SQL Saturdays last June in Houston.

Last year, I decided to take this a step further and set up an unofficial “Apprentice” program to help young people get into tech careers that don’t (for whatever reason) have the same opportunities most of us do.   My barrier to entry was low…others not so much.

The 1st Apprentice is ready to become a part-time Junior DBA and I couldn’t be more proud of him for his hard work.  He entered the program as a bit of a test case.  His barrier to entry – zero desire to go to a 4-year school and chase a possibly useless (for him) degree.

I will soon be looking for the next Apprentice.  Will s/he be like the first?  Maybe from a low income area?   Possibly aging out of the Foster Care system?  On the Autism spectrum?  Who knows.

For now, I will be working with one at a time.  I would like to get to the point where I have 2 or 3 that can learn together by working out solutions as a team.   This will help them not only learn Database Administration, but teamwork, trust, communication skills, etc.  Some of them may decide they hate databases but think Security is really cool.  Or Python.  Or Networking.  And that is great!  If they can find a career that they enjoy and provides a stable lifestyle financially, then I’m thrilled to be a step along a glorious path for them.

HOW YOU CAN HELP!

As these young people get to the point that I run out of things to teach them, they need real-world practice. Eventually I will have an “Apprentice” rate/offering on my Services and Pricing page for them to do the grunt work on your servers.  Part-time, with oversight from me or another Sr. Level DBA.  There are caveats and conditions on this I have not sorted out yet, but its coming sometime in the next 6-12 months.  Please keep an eye out for my posts here, on Twitter and on the Dallas DBAs LinkedIn Page.

I want your thoughts on this…privately or in the comments as you see fit.

Thanks for reading,

Kevin3NF