Even faster PowerShell module loading

Considering dbatools now has over 500 commands, we are always eager to keep an eye on the speed of our import process.

As outlined in the blog post decreasing module import times, we’ve taken multiple approaches to reduce the import times, including combining all commands into one large allcommands.ps1 file.

Last week, I took it a step further.

Binaries?

I don’t know the in-depth internals of Import-Module, but I know that importing a DLL filled with C# cmdlets is extremely fast. For instance, Microsoft’s SqlServer module imports 100 commands in less than a second. Sometimes it’s closer to half a second!

I wondered if we could somehow compile our commands into a C# binary but that seemed far-fetched. One thing we could do, though, is use compression! It works for SQL Server backups, could it work for PowerShell?

Yes 😁

Turns out that the approach worked! I believe this is due to the performance benefits of streaming and reduced I/O. Note this technique is part of a multi-pronged approach which includes runspace and not using Get-ChildItem.

Here’s how I did it. First, each time I publish the module, I rebuild allcommands.ps1 then zip it. This reduces the size of our module on disk a bit as well, too, since the uncompressed ps1 is over 5MB and the zip is less than 1MB πŸ‘

Next, I added the following to our module file. This code is run each time the module imports. You’ll notice that it opens the zip and streams it right in as a script block.

Before

After

Trade off

What’s the trade off? More CPU usage on your part for the moment it takes to stream the file (though you’ll save on I/O) and for me, it’s an extra (automated) step.

Ultimately, this approach shaved off about a third of our import time. If you’re looking to squeeze as much speed out of your import as possible, compression can help. And don’t forget, if you have super slow imports, it may be your Execution Policy.

Posted in PowerShell

your code doesn’t suck

Recently, I had a couple colleagues tell me that their PowerShell code sucks.

One of them is a beginner who manages a large SharePoint farm and the other is more advanced and wrote a few commands that saves me hours of work each week.

See the disconnect there? Their PowerShell code, which helps manage Enterprise platforms and saves hours of work a week, not only doesn’t suck, it’s actually awesome.

 

your ps is actually awesome

Their PowerShell is awesome because it works. Their PowerShell code is awesome because they’re using it. It’s also awesome because the time they invested in creating code that:

  • Will save people time in the future
  • Helped them practice a valuable skill set
  • Keeps them relevant
  • Will look fabulous on their resume

Nobody goes from being a beginner to an expert overnight, and it’s important to allow ourselves the time to learn.

my own story

I feel like I’ve got code that sucks, too. In particular, I’m tortured by one of the commands I used to be super proud of, Import-DbaCsvToSql. I die inside when people talk to me about it, but I leave it in dbatools because people love it. It’s useful and, enough of the time, it works πŸ˜‰

I learned a ton when I was creating that command, and even did a presentation about it.

The adventure of creating that command (and Bruce Payette attending my session!) was so exciting, it prompted me to dive even deeper into PowerShell.

Yet I fight back against my impulse to remove Import-DbaCsvToSql from dbatools. I literally have to resist the urge, but I do it by reassuring myself that:

  • One day I’ll rewrite it
  • Until then, some people really find it useful

Import-DbaCsvToSql is embraced because it’s fast. But it’s fast because it has no error handling. No error handling can be okay if you’ve got some perfect input, but input is rarely perfect.

The command simply doesn’t work enough for me, yet at the same time it tries to do too much. It totally stresses me out.

And, y’all, I showed this code, along with Start-SqlMigration.ps1 to Lee Holmes of the PowerShell team, all proud. He was so kind in response. I didn’t follow best practices, but he didn’t chide me. He said I was doing great things, and should keep on writing code and continue to improve along the way.

it’s all about adoption

Ultimately, I believe that Lee cared most that PowerShell had an enthusiastic adopter, even if I still had a lot to learn.

And I now realize that adoption is what’s actually important. Adoption will lead to that extreme excitement that I feel when I execute something and announce to my coworkers that I’m chillin because PowerShell is doing all my heavy lifting. From there, I’ll use that momentum to make my code even better.

Then I’ll invite my coworkers over to see my improved code, and we’ll celebrate. Then they’ll invite me over to see their improved code, and we’ll celebrate.

And then we’ll all live happily ever after, with our awesome code.

Posted in PowerShell

a huge thanks

Yesterday, I presented at PSPowerHour and during my intro, my iPad’s volume turned up so I got distracted when talking about something that really meant a lot to me.

And I’m a bit bummed that I wasn’t able to properly convey how much it meant that I was awarded as a PowerShell Community Hero earlier this year. I was looking forward to the opportunity!

From the moment I first heard about PowerShell, I knew that it would be a game changer. It’s probably the coolest and most fun technology I’ve ever worked with.

So when I saw Joshua Corrick’s tweet that I was awarded, I literally started yelling.

I am beyond honored that the community and the PowerShell team felt my work deserved to be acknowledged among the other highly decorated community contributors. And there’s something extra magical about being acknowledged not just by the PowerShell Team, but also by my peers. It ranks in the top achievements of my life.

I keep my PowerShell Community award right next to my MVP award, and I smile every time I hold it. Thanks to all involved for the huge honor πŸ’ž.

Posted in PowerShell

T-SQL Tuesday #104: Code I Would Hate To Live Without

TSQL2SDAY-150x150

Today’s blog post is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a monthly blog party on the second Tuesday of each month. Everyone is welcome to participate.

This month’s T-SQL Tuesday, hosted by my buddy and YouTube star Bert Wagner (blog | twitter), is all about the SQL Server tools I use and love. This post is about PowerShell, of course.

Start-DbaMigration

dbatools is such a fun toolset to work on, but specifically, I can no longer live without Start-DbaMigration. Even in smaller shops, migrations are often required and they are always a lot of work.

At least they used to be, before I built the command that started it all: Start-DbaMigration. Start-DbaMigration is an instance to instance migration command that migrates just about everything. It’s really a wrapper that simplifies nearly 30 other copy commands, including Copy-DbaDatabase, Copy-DbaLogin, and Copy-DbaSqlServerAgent.

Here is what it looks like in action:

So simple. To migrate an entire instance, all I had to do was execute the following:

How cool is that?! It is such a relief that I no longer have to dread migrations. I don’t have to go find sp_help_revlogin and execute it then copy the stuff over. I don’t have to figure out the default paths of whatever. I don’t have to worry that I’ll copy over a job but not its associated schedule.

Everything is just done for me, and I can sleep better at night 😴

Bonus: dbachecks

Ever since I was a baby DBA working for a company with a small budget, I’ve always wanted something like dbachecks. dbatchecks helps automate the checklists that we gotta go through every day, week, month, quarter, year and so on.

This used to be a manual pain, but now it’s automated, open-source, free and gorgeous πŸ’….

That whole module is some awesome code. And you can read about it here.

Thanks for joining me for this month’s T-SQL Tuesday. Sorry I never write about things in-depth.

Posted in PowerShell, SQL Server

my first open source project

I was first introduced to open-source on IRC back in 1996. I joined the #toriamos channel on undernet, which was managed by an eggdrop bot named [iCKy].

[iCKy] was an open-source TCL and C-based program that did cool stuff for us, like auto-ban users if they were flooding the channel. There was even a party line that sort of worked like a Linux shell.

This all looked like hacker stuff to me, so I wanted to know how it worked. This meant I had to learn Linux, which I did. Then I had to learn about eggdrops, which I did. Then I setup a few bots on an always-online Linux server. All for $0, too awesome.

By July 1998, I knew TCL well enough to hack an IRC script together and release it. But I gave the project an awful name that would trigger spam filters so I’ll skip it and move on to my second, more mainstream open source project ;)

SPAST

There used to be this open source directory called freshmeat.net, which eventually became freecode. freshmeat was leet as hell and I dreamt of being part of it.

Finally, I had a project I thought was decent enough to add and released 1.0 on September 5, 1999. SPAST, which stood for Simple Procmail Anti-spam Template, was a procmail template that I setup to filter mail being delivered to my postfix email server.

As the name suggests, SPAST was simple, which made it easy to implement and adopt. I laughed recently when I read the archived review at porkmail.org which said

Not the best possible approach, but it makes it easy to get started.

Totally valid πŸ˜‚

Forks

I found it pretty cool that a couple other projects were inspired by or forked from SPAST, like SPASTIC. SPASTIC, which still exists at sourceforge, was maintained by a guy named Keith Winston and the project eventually grew to have 11 other developers!

Keith even wrote an article titled SpamAssassin vs. Spastic, which compared the two projects. Ultimately, he stopped developing SPASTIC and adopted SpamAssassin. By that time, I’d since migrated to Exchange in an effort to replicate my work environment.

Fighting spam was always a fun way to pass time, and years later in 2007, I wrote a tutorial on how to use Gmail to filter spam in Exchange instead of Postini (which was eventually purchased by Google anyway).

dbatools

Considering that I’ve dreamt of having a cool open source project since The Verve’s Bitter Sweet Symphony was a chart topper, you can imagine how rewarding it’s been that dbatools has gotten so popular! And it’s a project that revolves around my two favorite things: SQL Server and PowerShell.

We now have over 110 contributors to our GitHub repository and nearly 1500 people in the Slack channel. The experience as a maintainer for a relatively successful project has been everything I’ve dreamed and more 😊

Posted in Linux

My very first web site, resurrected in all its 90’s glory

Well, if it wasn’t my very first, it is close enough. Back in 1997, I was taking a computer class at a non-progressive college in Northwest Louisiana. Most of the tech classes still teaching Fortran and the faculty pushed back hard when I asked if we could work with more modern technology.

I was disappointed with my experience there overall, but did enjoy this one class by Dr. John Barber called “Computers and Composition”. Dr. Barber was cool and the first person to introduce me to cyberpunk culture. And that’s about all I remember about the class, except for a website that I worked on as part of an assignment.

The site was called “The Q Zone” and it was built with NetObjects Fusion (and optimized for NetScape, y’all). In my mind this site was HUGE, but looking at it now, it’s only five pages. Five awesome pages of sci-fi fun.

Years later, Dr. Barber ended up referencing the site, which I found quite complimentary.

The Q Zone was accompanied by a well-written piece of time travel, co-authored by my classmate and partner at the time.

Word and paper do not suffice anymore as hypertext and electronic writing infuses with the various powerful technologies that heighten the communication process. These technologies include the use of Java, Dynamic Hyper Text Markup Language (DHTML) and all types of animated graphics and embellishments.

So good. So 90’s. So Shoney’s.

This animated embellishment brought to you by giphy.

Posted in General

Remove SQL Server Management Studio / SSMS 2016

Although I’ve been using SSMS 2017 on my demo machine for a while, I kept SSMS 2016 on my primary workstation until today. I’ve been unsure of the new look of SSMS 2017, but now that I’ve fallen in love with the way it handles XEvents, I decided to make the switch.

Whaatup! That’s super handy. Unfortunately, you can’t run SSMS 2016 and 2017 at the same time, and figuring out how to uninstall SQL Server Management Studio 2016 has been a pain.

Sometimes, SQL Server Management Studio 2016 won’t show up in Add/Remove programs. So I tried to uninstall every bit of SQL Server 2016 since my local db engine was hosed anyway; that didn’t work. As you may recall, in 2016, Microsoft decoupled SSMS from the Engine installer so that wasn’t a big shock.

Ultimately, with the help of StackOverflow, I learned that you can uninstall SSMS 2016 by re-running SSMS-Setup-ENU.exe. If you didn’t keep this file, you can download it again here.

Thanks to that sage advice, I did the following:

  • Downloaded the latest SSMS-Setup-ENU.exe for SQL Server 2016 (v. 13.0.16106.4)
  • Upgraded my SSMS because it was easy enough
  • Ran the SSMS-Setup-ENU.exe again and was offered the option to uninstall πŸŽ‰
  • Attempted to uninstall and it hung
  • Rebooted
  • Attempted again to uninstall and it hung again
  • Looked in my Add or Remove programs and it was there
  • Used the uninstaller from the Settings/Control Panel/Add or Remove programs and it worked!
  • Installed SSMS for 2017

Now it’s time to play with Extended Events and PowerShell with the help of SSMS 2017, yay :D

Posted in SQL Server

TSQL2SDAY #101: Essential SQL Server Tools

TSQL2SDAY-150x150

Today’s blog post is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a monthly blog party on the second Tuesday of each month. Everyone is welcome to participate.

This month’s T-SQL Tuesday, hosted by my buddy Jens Vestergaard (blog | twitter), is all about the SQL Server tools I use and love.

Everything

I love this insanely fast file system searcher called “Everything“. Just the other day, I needed to find some XEvent DLLs, look how fast it is to search all of my disks for XEvent:

And yep, there’s a PowerShell module to go with it called PSEverything. On my local system, I aliased Search-Everything to locate. You could also do find.

Everything does not require indexing and it works as soon as you install it. Something with the file allocation table that I don’t understand, but benefit from tremendously ;)

This PowerShell prompt

When people see how easy it is to time commands, they ask about how I got such a neat prompt. I tell them dbatools.io/prompt.

Want it too? Check out dbatools.io/prompt.

SnagIt

Documentation is an important part of every DBA’s job and Snagit Screen Capture is my screenshot tool of choice. I don’t know how I ever lived without it. Snagit is awesome for both pics and videos – wayyy better than the built-in (but still useful) Snipping tool.

GitHub Desktop for Windows

Gone are the days of myquery.sql.bak -> myquery.sql.bak.bak -> myquery.sql.bak.bak2. Now I do real version control with GitHub and I love it. It makes so much sense and GitHub for Desktop gives me the GUI comfort I need when it comes to version control.

What’s cool about this client is that it eased me into learning and understanding more about git/GitHub. I didn’t have to learn syntax + the whole concept of branching, repos, etc. I only had to learn the concepts and the easy GUI interface. One day I’ll do command-line, but that day is not today.

via GIPHY

It snaps better to areas better and even comes with a built-in editor that lets me easily add arrows and do cropping. SnagIt costs money but is well worth the investment.

Paint.NET

Sometimes, I need to get a little fancier with my screenshot edits and for that, I use Paint.NET. Paint.NET is free, powerful and fun to use. I use it for all of my logo design (which is often based off of icons found at iconfinder).

Handbrake

I love handbrake because it automatically removes the black sidebars from videos if I’m doing something complex and mess up the ratio by accident.

Ethervane Echo

Ethervane Echo is a free clipboard manager that I had no idea I needed until I started using it. The fast search functionality is awesome and more than once it’s saved me after I deleted a script by accident. Once I was able to find the entire script (I have a habit of Ctrl-A, Ctrl-C) and another time, I was able to easily stitch it back together from a few different clips.

If you haven’t used a clipboard manager, give it one day and I think you’ll be addicted. The developer’s site is undergoing a redesign right now, but you can download Ethervane Echo directly from Internet Archive.

zoom.us

spaghettidba introduced me to this one. zoom.us is lightweight, reliable and cool Video Conferencing software. Installing it is quick and the quality is super high. I always try to use it now when practicing with fellow presenters, all for free.

dbatools

Of course! I use dbatools every day. Just yesterday, I had to add a Windows Group to all of my servers and LOOOOOOOVED how easy it was to do with New-DbaLogin. Currently, I’m a huge fan of all the XEvents support, too.

Also, now I don’t fear migrations or worry that my disaster recovery plan is solid. dbatools helps simplify all of that.

dbachecks

I’ve dreamed of a tool like dbachecks since I was younger.

Now, we’ve got all of our checklists in one place. And since it’s free and open-source, other DBA’s have been adding their own checks, which is amazing. I’m looking forward to adding a few more audit checks once I can come up for air, likely after dbatools 1.0 debuts.

teamsql.io

So I haven’t used this free cross-platform SQL Management client, teamsql.io, yet but it looks good AF. Not necessarily for SQL Server, but useful if you need to manage Postgres, Redshift, Oracle or whatever.

In the past, I was always horrified by how ugly other management interfaces were compared to SSMS, but this one is nice.

Thanks for reading! Gotta go.

Posted in SQL Server