Press "Enter" to skip to content

Month: November 2016

TIL – Nov 25 2016, xp_dirtree appends null bytes to filenames in SQL 2000

ck ck
November 25, 2016

I was performing some final minor fixes on Find-DbaOrphanFile(helps cleanup files unattached to your server in default directories and additional folders you specify) and ran into an issue parsing the filenames returned.

In the process of changing the code to fix some other issues, IO.Path.GetFileName was added to properly enumerate the paths returned, and when testing it in the dbatools lab (Thanks @ctrlb!!!) it was failing on SQL 2000.

After a few attempts at parsing out slashes and various .trim commands I broke out the command in the PowerShell debugger (which I will detail in another post) and I was thinking of converting everything to numeric representations to double check their character values, but there was no need, the console had done the work for me:

The Find-DbaOrphanedFile (now with an additional ltrim and rtrim in SQL instead) command now works!

As a little aside, why even test SQL 2000?

One of the first things dbatools strove to do was migrate effectively between different versions of SQL Server, and we want to support SQL 2000 up to the most recent SQL version if possible, we do this because we want to offer the cool tools as far back as possible, we know that many people will be using old versions for many years to come.

It hurts sometimes and some of the code can get a bit hacky, but I think most of SQL 2000’s differences are interesting and long term forcing ourselves to manage different versions of SQL Server is reducing the into adding new and fundamentally different databases, such as all the interesting things that are coming with vNext.

And as always, this is one of the many dangers of using undocumented stored procedures 🙂

T-SQL Tuesday 84: Exploring DBA Tools or: How I Learned to Stop Worrying and Love PowerShell

ck ck
November 8, 2016
T-SQL Tuesday 84: Exploring DBA Tools or: How I Learned to Stop Worrying and Love PowerShell

@sqlbek (Andy Yun) posted a challenge for T-SQL Tuesday 84, where he encouraged people thinking about becoming a speaker to write about their experience giving their first talk, a topic they wanted to present on in the future, or even on starting the road to creating their blog and establishing themselves in the SQL space.

I wanted to flesh out an idea I had for a my first talk that would last under 15 minutes or so. I was thinking of targeting this at a user group (hopefully with a few production DBAs where are interested in learning more about PowerShell) to foment some excitement for and share my experience with dbatools.io

who (am I)

  • I am a production DBA who focuses on performance tuning for a few thousand users.
  • I haven’t written anything you’ve read (yet).
  • I have been poking around with PowerShell for more than a year, before this experience I had written only a few small things that made it to production. Definitely beginner skill level.

have you ever wanted to…

  • Migrate your SQL Server painlessly?
  • Double check that you have the best practices configuration for component or feature X which you are not an expert in?
  • Centralize the knowledge you find scattered around on various blogs and SQL wizard cabals?

what (do I want to tell you)

I am here to introduce your to dbatools, an open source project, to talk about my experience going from pure production DBA to PowerShell convert, and to talk to you about how augmenting your skills with some of these commands can mean very little effort expended for a significant payoff.

As a production DBA, I am responsible for a host of things, and I am often a little buried under the mountain of work that piles up, along with every day work, among other things I am responsible for:

  • Backups
  • Security
  • Managing performance
  • General Administration
  • Reporting on and managing configuration drift

My environment is growing rapidly, so I started looking for tools that I could use to help me do my job in a way that didn’t leave me in a cold sweat at night.

enter dbatools

dbatools was created help busy DBAs do their jobs, hopefully encoding best practices and reducing the cargo culting in the SQL Server community.

While dbatools was originally designed to migrate your SQL Server instance’s objects from one server to another it is currently expanding into a set of commands (cmdlet reference ) that I think many of my peers in the SQL Server would find very useful.

I first heard about dbatools in the SQL Community Slack and loved the demo on the front page showing how simple a migration is:

I was a bit gobsmacked as I had just finished my own instance migration, and it certainly took a significant amount of time and effort compared to this painless invoking of this cmdlet.

A bit of hanging around and even though I felt unfamiliar with PowerShell development, I was was roped into writing my own cmdlet .

To be frank, looking back at my first draft is painful.

I was worried about my code quality given my experience, and so I asked for some feedback from the head of the project Chrissy LeMaire.

What I got back was almost unrecognizable as my code, and it was one of the first times I was able to see my feral PowerShell (that did the job, badly) turned into something that you could consider idiomatic code.

I learned more in that code review than I had in the previous year of starts and stops in PowerShell land and spent the next week picking it apart and asking questions to cement my knowledge.

In one week I had expanded my PowerShell knowledge by at least 20 times, and felt way more comfortable in working with the team to build some command or troubleshoot an issue! In the end I had a fairly robust cmdlet and with minor bug fixes it made it into the release! I was super happy to have something published in an open source project, but even more, I was happy to have found a series of mentors who wanted to help me build my skills as a PowerShell and database developer.

A few pull requests and a few months later I am confident in my PowerShell abilities to figure stuff out, work through problems, and help newbies through their first steps to contributing to dbatools.

The small scope of PowerShell I have been able to learn and utilize has made things which at one time seemed daunting now seem possible, and even easy.

Now I want to help you make my story your story, contribute to dbatools.

when

Any time you want to.

dbatools is a community that lives in the #dbatools channel in the SQL Community Slack Any time day or night people are chatting, working on code, listening on feedback to improve the tools, and answering questions for people looking for help using it.

Come and express interest in contributing, we will help you through the rest!

how (do you contribute)

  • Join the #dbatools channel in the SQL Community Slack
  • If you are just learning PowerShell, that’s ok, many of us are still puzzling things out! All skill levels welcome!
  • We need help with not just with writing code, but with documentation and testing; your first contribution could be as simple as running a command.
  • You can contribute as much or as little as you have time for, many issues are as simple to fix as adjusting one line of code.