Community

#TSQL2SDAY #101 Round-Up : My Essential SQL Server Tools

Posted by Jens Vestergaard on in Community, Programming, tsql2sday 3 Comments

Tuesday 3rd of this Month I invited people in the SQL Server community to share which tools are essential to their daily work. I was really overwhelmed by the number of stories that the topic triggered. 22 in total took the time to write down and share which tools they use for their work chores.
Going through 22 posts and aggregating them has been taking more time than I had hoped for, since my trusted laptop broke down – blinking codes are well and alive I tell you!

Going through the lot, I found some similarities to the posts, and have categorized them accordingly. But first off a BIG thank you to all how participated!

Without further ado, here goes.

Relational Heavy Lifting

Kamil Nowinski (b|l|t) takes us through the classic stuff, I mean, the real classic stuff – some would call it vintage – by showing how Total Commander still has a place in the tool belt, this century 😉

Matthew McGiffen (b|l) shows how to set up a Central Management Server, in order to execute queries against multiple instances in ad-hoc scenarios, seamlessly. Very nice tip. Matthew also did a second post, lining up multiple tools he’s written about in the past, nicely aggregated in this post.

Jess Pomfret (b|l|t) does a really nice post on how Powershell and the dbatools has changed her way of working. Jess even provides some useful snippets to get you going. I share the same enthusiasm for Powershell as Jess does, and was very pleased to see homage paid to the dbatools – incredible tool. Best of luck on your speaking adventures!

Marek Masko (b|l|t) has a huge post on classic DBA tools as well as a pleasant surprise on testing using tSQLt. Also some good pointers to free community scripts and tools as well. Great read!

Tracy Boggiano (b|l|t) covers dbatools and a specific Powershell Command and T-SQL Stored Procedure, but also on Telegraf and VS Code.

Dan Clemmens (b|l|t) goes all in on DBA tools for statistics, execution plans and tracing, even including the legendary diagnostic scripts from Glenn Berry.

Steve Jones (b|l|t) has a huge list of free and paid tools, from SQL Server sentric tools to a good deal of process related tools – i.e. DevOps and such.
Also Steve manages to sneak in a reminder on the password thingy magicky, that, according to domain expert Troy Hunt we all should rely on, be it pwsafe or any other tool like that.

Doug Purnell (b|l|t) is short and concise in his praise of Ola Hallengren maintenance scripts  and Jared Zagelbaums extension of those in Powershell.

Warren Estes (b|l) is praising the usual suspects in the DBA field, but adds a couple of interesting options for productivity and benchmarking/testing and also rounds up a couple of SentryOne products.

Devon Leann Ramirez (b|l|t) is offering a thorough introduction to their free plan explorer offering. Devon also makes a good point in marking the company’s presence on the community. If you want the quick tour, head over to Vimeo.

Rob Farley (b|l|t) talks about two things I really hold dear; Coffee… and I forgot the other thing. No really, Rob has an excellent blog post on Live Query Stats (LQS), and what some of the use cases are for that feature/tool. There are more ways of using LQS than I had thought about – thanks for sharing!

Riley Major (b|l|t) share his story on how he works with Management Studio and how the cool could be improved to further support a common way of working. Besides the tips on SSMS Riley also lists his favorite (and not so favorite) tools.

The BI Power in Power BI

James McGillivray (b|l|t) is first and foremost writing about my trusted travel mate; The Kindle (App) as his favored tool of the trade. Besides that treasure trove books can be, James also has some pointers to daxformatter and a theme generator which is pretty hefty!

Community Zone

Jo Douglas (b|l|t) argues that the most important tool for any professional is networking and community, and it’s hard not to agree completely. Jo also writes about some great points of where to begin this journey.

Jason Brimhall (b|l|t) brings up the aspect of blogging itself as a great tool of the trade, and I have to agree here and couldn’t have stated it more clearly that Jason:

Blogging helps you become a better technical person.

Googlefoo is also described in Jason’s blog for this party, and he manages to sneak in a reference to his extensive work and blogging on Extended Events, which in itself is an awesome contribution.

Reid DeWolfe (b|l|t) offers a quick write up on classic DBA must haves; SQL Prompt, Plan Explorer and GitHub/SourceTree. Reid also describes some of the main benefits of the tools.

Other

Garland MacNeil (b|t) brings another perspective into the party, by writing from a borrows laptop – not sure it was intentional, but I guess the exercise is very rich in terms of learning. I know others have been there too:

https://twitter.com/_AlexYates_/status/986137414723866625

Chrissy LeMaire (b|l|t) has, surprisingly enough, not written about dbatools, and if you believe in that you may call me Bill 🙂
In Chrissys blog post you’ll find a great list of auxiliary tools for all the things you do around programming; Screen shot/Image handling, code repositories, clip board management and video editing tools.

Josh (b) gives us the DevOps perspective of a Database DBA/Developer in a not so uncommon scenario – well, I think we’ve all been there at some point. Some prominent 3rd party tooling is getting some ❤

The Other Tools

Catherine Wilhelmsen (b|l|t) offers a completely different and refreshing view on tools that were completely new to me, at least. Going from database modeling to data generators to time keeping tools and beyond.

Finally, No Tools

Hugo Kornelis (b|l|t) makes a good argument on not becoming addicted/dependent on the presence of certain tools in order to perform your job. I guess this applies in particular, when you’re a consultant and can’t always BYOD. Apart from that Hugo really likes SQL Prompt and Plan Explorer 😉

The Tools Mentioned (in no particular order)

dbatools dbatools  PowerShell  dbareports  SQL Server Management Studio
 Redgate SQL Compare  Minionware  Sentry One Plan Explorer dbachecks  SQL Operations Studio
 SQL Database Modeler  Dynamic Restore Script  Scooter Software Beyond Compare  Redgate DLM Dashboard  Ola Hallengrens’s maintenance scripts
 Trello  SQL Server Data Tools  Passwordsafe  Sublimetext  Notepad++
 Redgate SQL Prompt  Mockaroo  Dropbox Visual Studio Code  SQLCover
 Sourcetree  SQLNexus  Coblis  tSQLt  Advanced Theme Generator (PowerBI)
 DAX Formatter  R Studio  Scale SQL Clear Trace   PSSDiag  Devart’s dbForge
 Toggl  Grammarly  SCD Merge Wizard Statisticsparser  Adam Machanics whoisactive
 Winmerge  Mythicsoft Agent Ransack  Redgate SQL Search    Glenn Berry’s Diagnostic Scripts

 

Loading

TSQL2SDAY #101 Invitation: My Essential SQL Server Tools

Posted by Jens Vestergaard on in Community, Programming, tsql2sday 31 Comments

tsql2sdayThe Why

If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for ages now! Basically the host selects a topic, defines the rules (those are almost always the same), and then everyone else blogs about said topic. Once the deadline is reached, the host summarizes each of the submitted posts on their site/blog.

T-SQL Tuesday #101

This is the second time I host a T-SQL Tuesday, and hopefully not the last

The What

This month I get to pick the topic, and I am going to go with:

The Essential SQL Server Tools in my stack

Besides SQL Server Management Studio and Visual Studio Data Tools we all have our own set of tools that we use for everyday chores and tasks. But how do we get to know which tools are out there, if not for other professionals telling us about them? Does it have to a fully fledged with certification and all? Certainly not! If there’s some github project out there, that is helping you be double as productive, let us know about it. You can even boast about something you’ve built yourself – if you think others will benefit from using it.

Basically I think, that by establishing awareness about what kinds of tools that are out there, new professionals will not have as steep a curve getting the pace up, as they would have had. But I suspect that even some veteran guys could have an “a-ha” moment from reading the summary.

Additionally, you can (read: should) share how you came to depend on said tool – and of course you are encouraged to give credit, where credit is due in terms of making you aware of the tool.

Another approach for this topic, is to approach it as kind of A Day in the Life of kind of blog post, as has been done before by Erin Stellato (b|l|t). Writing with the specific angle to describing how your everyday is made easier by the use of your tool stack.

The How

There’s only a few rules for T-SQL Tuesday:

  • Your post must be published on Tuesday April 10th 2018 between 00:00 GMT  and 23:59 GMT.
  • Your post must contain the T-SQL Tuesday logo (see above) at the top and the image must link back to this blog post.
  • Trackbacks should work, but if they don’t, please put a link to your post in the comments section so I (and everyone else) can see your contribution!
  • Tweet about your post using the #tsql2sday hashtag
  • Include “T-SQL Tuesday #101” in your blog post’s title.
  • Optionally add @vestergaardj to your tweet, to make it harder for me to miss 😉

If you want to host a topic of your own:

  • Contact Adam Machanic (b|l|t) and tell him you’d like to host a T-SQL Tuesday from your blog.

Loading

Speaking at the Intelligent Cloud Conference in Copenhagen

Posted by Jens Vestergaard on in Community 0 Comment

I am super excited to have been selected to speak at the Intelligent Cloud Conference in Copenhagen, May 29th-30th this year.

The full schedule for the event can be viewed here.

My talk will be on Power BI, or to be more precise, a talk on tips and tricks to get you started ingesting data with the Power BI Desktop application. So if you are a veteran SSIS developer, tag along to get your mind blown by the simplicity of Power Query. Or maybe you are a prospect Data Scientist that needs something like Excel on steroids. This will be a session for you.

I will start of by demonstrating how easy it is to import data straight into Power BI from csv or xls(x) files that have a simple structure. Then moving on to scraping data from a web page. Gradually making the tasks more and more complex, as we move on to header/footer issues or what about variable number of columns for multiple files for the same data set? Once we have established the elegance of Power Query in Power BI, I will demonstrate how easy it is to Enterpricylize (yep, made up that word for this situation) your data discoveries by exposing the newly created model in an Azure Analysis Services, with a few clicks in a wizard.

However, if you are not into Power BI or Business Intelligence, the conference has a lot more to offer. It’s a combined Data Platform and Azure conference, meaning there will be an equal number of tracks divided among the two topics, filled with super interesting sessions such as “Failure prediction for manufacturing industry with SQL Server & R”, “Maturing IoT solutions with Microsoft Azure”, “How to make hundreds of servers work for you” and the list could go on and on.

I am really looking forward, not only to speak at this conference, but also listen in on some of the other great sessions that are being presented.

More information here: https://intelligentcloud.dk/

Loading

Azure Saturday hos Microsoft i Kgs. Lyngby

Posted by Jens Vestergaard on in Community 0 Comment

En lørdag i Kgs. Lyngby, i selskab med 35 talere, der kommer med massiv viden på hver deres område, for kun en plovmand. Det lyder næste for godt til at være sandt – men det er det ikke, og faktisk er det ikke kun om lørdagen der sker ting og sager.

Der vil være fire (4) forskellige workshops, for en merpris, d. 30. og 31. august og selve konferencen er d. 1. og 2. september. Forvirret? Interesseret?
Så se mere på http://azuresaturday.dk

Her er et lille udsnit af de indlæg der vil være at vælge imellem:

 

Loading

TSQL Tuesday #93 – My Interviewing Experiences

Posted by Jens Vestergaard on in Community, tsql2sday 1 Comment

Kendra Little (b|l|t) is hosting this months TSQL2SDAY which is on the topic of:

TSQL Tuesday #93: Interviewing Patterns & Anti-Patterns

What advice do you have for people preparing for or going through an interview?

I got of to a late start, and I apologize in advance for what may be a bit messy blog post.

Background

A couple of years ago, when I was working at a pretty large blueish container shipping company, we got the opportunity to interview candidates for our off-shore team. This was, as far as I know, a first off in the company, and only came to be because my manager at the time was adamant about it.

We quickly decided, that I was to take on the technical part of the interview and my manager the more person/team oriented aspects. So I devised a good number of questions, so I could accommodate for any specialization the candidate may have. We had to cover a lot because our landscape was, well, let’s just put it: Not so single vendor-ish.
To make a long story short, the setup in the company at that point in time was that the Data Warehouse (DWH) was situated on a Teradata box. On top of that there were several options for business reporting. One such option was Microsoft Reporting Services (SSRS), which was done in the department where I was sitting. The reports were however not sourcing directly from Teradata, but from one of many Analysis Services (SSAS) cubes we hosted as well. All data was sourced using Informatica (into the DWH), in yet another department, so Integration Services (SSIS) was officially (and unofficially) not allowed as sourcing tool, which is why no questions reflect that aspect of the Microsoft BI Stack.

So, we were looking for strong profiles in SSRS and SSAS as well as having Teradata knowledge. Along with a general understanding of Data Warehouse fundamentals of course. In other words, not junior profiles.

Preparation

In order to lock in on the level of the one being interviewed I prepared a set of questions with increasing technical difficulty. Since my knowledge on Teradata was limited I asked around among the DBA’s and decided after some contemplating to completely avoid checking for technical Teradata skills. We would have to train them, if need be.
The list of questions evolved during the whole process. I honed in on the  general level of applicants, after firing questions East and West. Me being more precise gave everyone a much better experience. Conversations tend to stop, when no-one understands you and, you don’t look like a Donkey asking about simple stuff.
So hopefully you get the opportunity to tune into the level of the applicant before you unload your tech quiz.

The Interviews

I wound up having three categories of questions:

Proficiency

Do they have any strong sides? Usually people tend to have a favorite tech in which they excel. So for us it was important not to hire only SSRS Wizards/Ninjas or whatever it’s called nowadays. We wanted a fair mix.

I would ask the interviewee to rate them selves in the different tools and languages and use their rating as pointer to the question I would ask on the technical stuff. If they rated themselves > 7/10 in MDX and are having trouble naming at least five (5) frequently used functions, you know something is rotten.

Something I notoriously have gotten wrong would be:

  • Q: Explain what you get from having Enable Visual Totals checked?
  • A: When Enable Visual Totals are checked, the Grand Total reflects the total you are allowed to see. The Grand Total is not a Grand Total, but your Local Total. At least that works in my mind!
Data Modeling

Data modeling was usually the area where the interviewee’s were least knowledgeable. So the nature of the questions in this category was on a very high level.

  • Q: What’s the difference between a data mart and a data warehouse?
  • A: A data mart is a subset of the data warehouse.
  • Q: Do you know any architectural principles of data warehousing
  • A: Inmon & Kimble
  • Q: Delivery Date and Shipping Date in a Fact table, how would you model that?
  • A: I would use a role playing dimension, Time.
Optimization

In this category of questions, I’d ask them to explain how they would optimize say a poorly performing dimension in SSAS.
What about query performance tuning – what would they look for and how would they attack the issue?

Final Question

But, the one question I always looked forward to was:

Tell me about a Business issue you solved, which you are particularly proud of.

This question I grew fond of, because it allows for the interviewee to brag about how cool they were doing this and that awesome thing to save the business a Centillion Dollars. This allows the person really to shine and at the same time, you get a feeling for their level of engagement and technical flair, not their skill set as such. Don’t you want to know how ingenious they can be a their best? This will give you a hint in that direction, or at least it did for me.

Tests

Back in the days before the big blue container hauling company, I was also involved in the hiring process. I was in fact tested myself, when I first began working there and in my experience tests can have two immediate take-away’s:

  1. It was all talk, and no walk.
  2. Does the person think and act like you expect them to?

1)

I’ve had a number of people skipping the test after we had a good 1-1½ hour talk. Excuses flying around like butterflies in the summer.

“Oh, I forgot! It’s my Grandmother’s birthday, I have to leave urgently. Bye Bye!”

“My car is double parked… I need to move it immediately!”

“Is that the time?”

– Never to be heard from again.

For this kind of situation, a test is very handy – apparently there are a fair amount of bul$hitters out there and fortunately the test prevented you from hiring them.

2)

In my mind, a test should display how the person got to the result, rather than just showing a number. I want to see the T-SQL code, the MDX, M or whatever they used to solve the puzzle. This tells me way more than a result and time.

With the actual code in hand, you can go into discussion (something a lot of coders dislike) about how they came up with what they submitted as final answer. By discussing the code, you get a sense of how their personality is when dealing with their own code. Expect a lot of back-paddling, as time, room temperature, humidity and other factors can have a high impact on the delicate nature of some coders 😉

Summary

There are a lot of ways to conduct an Interview, and I bet my humble contribution to this tsql2sday is one of the more novice ones, but I hope I have allowed you to see some of the thought processes I’ve had, when having to interview people for a position. I am very much looking forward to seeing the other contributions to this blog party – can hardly wait.

Thanks Kendra for hosting!

Loading