TSQL2sday

T-SQL Tuesday: Your Fantasy SQL Feature

September 10, 2019 by SQLDork 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

September 10, 2019 by Kevin3NF 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

Follow @Dallas_DBAs

T-SQL Tuesday: Fantasy SQL Feature

September 10, 2019 by SQLandMTB 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

May 8, 2018 by Kevin3NF 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

TSQL Tuesday #100: Looking to the future

March 13, 2018 by Kevin3NF 3 Comments

100 months from now:

Auto-Shrink finally removed from SQL Server source code.

T-SQL Tuesday #99 : Dealer’s Choice

February 13, 2018 by Kevin3NF Leave a Comment

Welcome to my contribution to the 99th installment of T-SQL Tuesday, where Aaron Bertrand (b|t) gives us a choice to spread our wings and talk about our personal passions OR…play it safe and talk about our favorite T-SQL bad habit. This whole T-SQL thing is all Adam’s fault, btw 🙂

If you follow me on Twitter, you probably know me more as SQL Cyclist than Kevin3NF. I grabbed that name because it was cool, and well…I’ve been riding bikes since I was 3 years old. I’m waaaay older than that now, and still riding. My racing days are pretty much over, but the passion is still there!

I started out just riding in the neighborhood as most kids do, jumping ramps, skidding, wheelies, etc. All the normal kid stuff. Did a little BMX in the 80’s. I moved into road bikes, track racing and eventually added mountain biking into the mix, which has become my favorite.

In the last 15 years I’ve gotten more and more involved:

Started road racing in 2004 (5)
Raced on the local velodrome for 5 years
Started coaching juniors racers as a certified USA Cycling coach
Became a local level USAC official
Started the North Texas chapter of an International Club
Started racing endurance mountain bike races (6+ hours)
Raced Cyclocross for a couple of years
Set a course record with a friend on the back of his tandem

I’ve had to dial it back lately for work, family and physical reasons…but the passion is still there. Come to my house in July…and if you can’t tell me who is leading each category in “The Tour” you will be relegated to snack fetching duty as punishment 😉

And now, on to a small portion of the tons of favorite pictures: