SSMS Complains of “Inconsistent Line Endings” with PowerShell Output

If you’ve used Out-File, Set-Content or Add-Content to create a SQL file, you may notice that SQL Server Management Studio complains about normalizing the line breaks.

If you see this, simply using -Encoding UTF8 for your output instead of ASCII, the default, will solve the problem.

Posted in PowerShell, SQL Server

T-SQL Tuesday: The Cloud, Globalization and Automation

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 Koen Verbeeck (blog | twitter), is all about how technology has changed a lot in the past years and the potential impact these changes will have on SQL Server Pros.

In Koen’s invitation, he specifically called out the cloud, globalization and automation.

The Cloud

When it comes to the cloud, I think on-prem is still a safe bet and will be for at least another decade.

My first bill from Amazon Web Services arrived nearly ten years ago on June 1, 2007 (it was for $0.61, not bad). Since then, I’ve played in the cloud off and on, and was even the technical editor for a book about Automating Microsoft Azure with PowerShell. When it comes to work work, however, everything is still on-prem. Similarly, my awesome homelab is tucked safely in a storage room at my house.

Ultimately, when it comes to the cloud, I think it’ll be at least another 10-15 years before a majority of organizations fully move away from on-prem. And as they do, I’ll go along with them, but like many organizations, I’m in no rush. The “Datacenter” in my Cloud and Datacenter Management MVP is strong.

At the same time, I know that my varied systems background lends itself to the confidence that I can adjust to cloud movement with no issues. I’d encourage DBAs with a weak systems background invest more time into understanding their whole environment and not just SQL Server itself.

Globalization

Is this offshoring? If so, I’ve seen situations where it’s worked, and situations where it hasn’t. I’m not really concerned.

Automation

Y’all know what I’m gonna say here! I love automation and PowerShell. I know for a fact that PowerShell and T-SQL together are the future of SQL Server administration. As someone who often presents about dbatools, the popular SQL PowerShell community project, I’ve seen the excitement and relief that PowerShell automation brings to SQL Server Database Pros.

From making it way easier to migrate entire instances to automating backup testing and verification, PowerShell makes it straight up more enjoyable to be a DBA.

Backups

For years, I’ve used Ola Hallengren’s Maintenance Solution, but I always found it stressful to know that at each location, I’d have to spend a chuck of time working on the process to perform restores as required.


A whole instance of Ola backups

What happens if an entire instance goes down? Now, thanks to Stuart Moore, restoring the most recent database backups to another instance is as easy as

Get-ChildItem \nas\sql\sql2016 | Restore-DbaDatabase -SqlInstance sql2016a -MaintenanceSolutionBackup

What about logins with their passwords, SIDS, permissions and all that? I export every login within my estate every day to a text file using Export-SqlLogin.

What about jobs? Well, we don’t export those yet, but they’re on the list ;) Here’s a video of Restore-DbaDatabase to keep you occupied in the meantime.

SPNs

These used to be a pain, but they aren’t anymore. Now, thanks to Drew Furgiuele, dbatools offers a suite of SPN-related commands that help make Service Principal Name management way easier.

Test-DbaSpn tests for SPNs that should exist and can be piped to Out-GridView and then to Set-DbaSpn. So easy, it makes SPN management fun!

We also have a quick 5 minute video that gives a tour of the four SPN commands.

Migrations

As previously mentioned, migrations can be automated and even scheduled. Check out this video where an entire instance is migrated with a single command.

Finding things

Ever needed to find a stored procedure that contains specific text? When I did development, I had to all the time. dbatools offers an easy, awesome way to do this. You can even search using regular expressssssions! What?!

And it’s smoking fast, too. I once evaluated 37,545 stored procedures on 9 servers in less than seconds!

We’ve even made it easy to find databases, orphaned files, user owned objects, jobs, unused indexes and more. Hell, we even have a command for finding dbatools commands!

In conclusion, automation is life

Intrigued? Check out our whole catalog of commands that automate common (and not so common) SQL Server tasks.

Thanks for joining me for this month’s T-SQL Tuesday.

Posted in PowerShell, SQL Server

A Suggested Fix for Thunderbolt Display Buzzing

About a month ago, my beloved Thunderbolt display began buzzing. After some research, it seemed that there could be all sorts of solutions. The one that seemed to almost always work was sending off the monitor to get the power supply fixed.

Apparently, there’s an issue with the power supply that can be confirmed if you crank up the display brightness. I cranked up my brightness and sure enough the sound was louder.

The buzzing didn’t start, however, until I was on my computer for a while (20-30 mins?) so each day when I came in, the silence would offer me some dim hope that maybe it fixed itself.

The Solution

I figured that if it was quiet at first, the reason it began buzzing was because it got too hot. If I could lessen the burden placed on the power supply, and thus reduce the heat, it should stop. I recently started powering an old Macbook Pro with my cable (tho the display displays what’s on my Mac mini). To solve the buzzing, I stopped using my Thunderbolt display to power my laptop. That reduced the heat and there’s no more buzzing.

Note that this worked, then it didn’t for like a day or two, but then it did again for good. Now, my Thunderbolt Display hasn’t buzzed for a full week. Let’s hope it keeps going. I love this thing and I’m totally distraught that Apple discontinued Thunderbolt production.

But it stopped working when..

Maybe it’s coincidence, but I was buzz free for like a whole month, then I lit a candle near my monitor and boom. Buzzing. Next day, no candle, no buzzing. WTF.

Also

I’d suggest that, in general, people don’t use the built-in power cable if possible as it seems to lessen the lifespan of older power supplies. I didn’t have this issue until I powered that laptop for a couple months straight :(

Posted in OS X & iPhone

Loading SMO in Your SQL Server-centric PowerShell Modules

In a recent version of PowerShell, Publish-Module, which publishes modules to the Gallery began requiring fully qualified Assembly names such as “Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91”.

Previously, it was sufficient just to use short names such as Microsoft.SqlServer.Smo. This had similar behavior to LoadWithPartialName.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

Now, however, we must use the fully qualified name and this presents a problem for people who use SMO because we can’t be sure what version of SMO exists on the user’s system unless we’re specifically targeting an SMO version like Microsoft’s official module, SqlServer.

The Problem

When I tried publishing dbatools with just RequiredAssemblies = @(‘Microsoft.SqlServer.Smo’), I received the following error:

Publish-Module fails with “The specified RequiredAssemblies entry ‘Microsoft.SqlServer.Smo’ in the module manifest ‘…\dbatools.psd1’ is invalid. Try again after updating this entry with valid values.

I looked at the official SqlServer module to see how they handled RequiredAssemblies, and as mentioned previously, noticed that they addressed only the 2016 version of SQL Server SMO. I then looked through GitHub for how other people used RequiredAssemblies, both with and without SMO. Nobody else appeared to have a solution that addressed requiring one of an array of possiblities.

The Solution

I ended loading my assemblies in the psm1 file instead of using the ModuleManifest :3

What you can see in the code is that I iterate through all of the versions of SQL Server, including vNext, 2016, 2014 and so on, all the way down to 2005. If none are found, the module throws an informative exception. If SMO does load, the script then attempts to load the other associated assemblies.

Some assemblies such as Management.XEvent don’t exist in lower versions of SMO and will throw an error that I don’t care about – we handle version checks later on in our specific scripts such as Copy-SqlExtendedEvent.

Oh, and don’t worry about this loaded allll of those assemblies. It only takes 11-40 ms on my machine to run the above script.

So feel free to copy the code above to your own modules that use SMO or if you have a better way, let me know. Happy Holidays!

Posted in PowerShell, SQL Server

Find Every Parameter for Every Command in a PowerShell Module

We’re currently working on standardizing our documentation for dbatools and I needed a list of all of the parameters we use. Here’s how I did it (well, I did it an uglier way then Aaron made it prettier ;))

Get-Command -Module dbatools -CommandType Function | ForEach-Object { $_.Parameters.Values.Name } | Sort -Unique

Posted in PowerShell

dbatools to the backup/restore rescue

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.

intro

dbatools has been around since 2014. At first, it started with one contributor and was solely dedicated to migrating SQL Server instances, but now it’s grown into an awesome open source DBA project with over 30 contributors.

We have nearly 100 commands for you to enjoy and about 10 of them revolve around Backup and Restore. Even more are planned, as you can read in Stuart Moore’s blog post titled Easier SQL Server Restores using DBATools. Stuart is a long-established SQL/PowerShell powerhouse backup/restore and we’re honored to have him on board.

our backup/restore commands (present)

Want to know more about our snapshot commands? Check out team member Constantine Kokkinos‘ blog post “T-SQL Tuesday 85: Managing database snapshots with dbatools”. A snapshot may not exactly be a backup/restore but its spirit is, fa sho.

histories

Restore-SqlBackupFromDirectory is super useful in a pinch, too, but it’s not quite fleshed out to our standards, so it doesn’t have a corresponding webpage. We expect this will be renamed by the next release.

our backup/restore commands (soon)

  • Restore-DbaBackup
  • Restore-DbaSnapshot
  • New-DbaSnapshot
  • Test-DbaBackup
  • ???

Coming up, we’re going to have a fully featured Restore-DbaBackup command that can easily restore a single file, to a point in time, from a Ola Hallengren styled directory structure or a formatted hastable (like the one I’ll pass to process my Test-DbaLastBackup command).

We’re eager to keep going. Have any ideas? Let us know by filing an issue or joining our Slack Channel on the SQL Community Slack and telling us what you need.

my favorite backup/restore command

I love this entire toolset but damn, I’ve been needing a command like Test-DbaLastBackup for a long time. Testing backups can be such a pain, but PowerShell can make it easy for your entire estate. Not just one SQL instance where you’ve got some specific stored procedure installed, but all of your SQL servers — from one workstation!

I created Test-DbaLastBackup because I wanted to test all of my backups and using PowerShell was the easiest way to do it. It parses Get-DbaBackupHistory for the last full backup, restores it, runs a DBCC CHECKTABLE and then drops the test database.

Here’s a test I did on my SQL Server 2016 instance. I ran Ola Hallengren’s Full backups for both system and user, then executed Test-DbaLastBackup -SqlServer sql2016. Simple AF!

The command’s webpage is at dbatools.io/Test-DbaLastBackup. Pretty easy, eh? That goes for every command we have, too. Just dbatools.io + /The-CommandName.

Back to the command, Test-DbaLastBackup. It:

  • Restores the test databases (named dbatools-testrestore-$dbname by default) to the specified -SqlServer unless you specify a -Destination.
  • Then it’ll restore to the Destination (if remote, so long as the backups are on a shared directory — which mine always are).
  • You can even specify a MaxMB if you don’t have space for super large database restores (in that case, the -VerifyOnly switch could come in handy).
  • Want your Data and Log files to go somewhere other than default? Use the -DataDirectory and -LogDirectory params.

This command is only in its infancy, too! Next up, team member Christian Solje will be adding additional features like Point in Time restore.

get-help

Need to know more about our commands? In every release, we require some basic docs so just run Get-Help Test-DbaLastBackup -Detailed and you’ll be greeted with Examples, docs for each parameter, a synopsis a description and other useful stuff.

download

Intrigued and haven’t installed dbatools yet? Hit up our Downloads page to see how to install it on your system.

youtube and twitter

We have a YouTube channel if you want to watch some videos. That’s the YouTube channel for the SQL Community Collaborative where you can watch videos about open source, (mostly) SQL PowerShell projects like dbareports.io and, of course, dbatools.io.

We are also on Twitter as @psdbatools.

one more thing

If you think we’ve done something that sucks, let us know, either on GitHub or Slack. We welcome and even enjoy alternative approaches and civilized debate about processes. Or hey, if you love something we’ve done, feel free to drop by the Slack channel too.

We like to release early, release often which means that we’re nimble and responsive to changes and requests. (It also means that, while we are at v0.8.69, version 1.0 which is due in Summer 2017 will likely introduce breaking – but beautiful – changes. This is thanks to our Style Sheppard Klaas Vandenberghe.)

in conclusion

We’re a bunch of DBA’s who, like you, want to have an even easier time managing SQL Server. dbatools uses PowerShell to help do exactly that. Want to join the fun? We’re an inclusive group — even if you don’t know PowerShell, we’ll help you. Even if you don’t know Git, we’ve got you covered. Come with your experience, your scholarly background or your enthusiasm and passion. We’d love to hear from you.

Thanks for reading. I’m looking forward to the next T-SQL Tuesday!

Posted in PowerShell, SQL Server

This is How I Work

Inspired by Adam Bertram being inspired by Lifehacker’s How I Work series, I’m also doing a post about how I work ;) I really liked Adam’s balanced assessment so I decided to follow in his footsteps there, too.

Where are you located?

Belgium, land of the free, home of the beer. Also home of the Belgian PowerShell User Group, which I run with Luc Dekens.

bepug

I moved here back in 2012 from Washington D.C. Prior to my short stint in DC, I lived in Southern California, Northern California and Southern Louisiana.

What is/are your current gig(s)?

I’m a Systems Engineer/DBA for General Dynamics Information Technology. I’ve always heard about General Dynamics growing up because they’ve got a bunch of rocket scientists so it’s pretty cool to get to work for them.

I also have a couple other side gigs. I’ve always preferred it that way; get my primary source of income and health insurance from a corporation then do other stuff on the side. So I’ve had netnerds.net since 1997. The work I do through netnerds is generally web development or systems engineering. But between finishing my masters at Regis University and doing community work, I haven’t had much time for contracting lately.

My other paying side gig is RealCajunRecipes.com. I run this site with my mom and best friend. My mom does some pretty amazing work on Facebook. We’ve got about 90,000 likes as of today. The money we get from Google Ads helps pay for my bombass homelab.

What’s one word to describe your work?

Fun

I frikken love what I do. All of it is fun, even when it’s exhausting and even when it sucks.

What apps, software, or tools can’t you live without?

The obvious things: PowerShell, SQL Server Management Studio, RSAT, Remote Desktop Client, putty, GitHub Desktop

Not as obvious:
Spotify
Snagit 13
Notepad++
Paint.net
Handbrake
Ethervane Echo
PowerShell Studio

Snagit 13 is amazing! I used to use Camtasia and iMovie (well, I still do sometimes) but mostly use Snagit these days. I also use Handbrake to remove black bars on the side of videos and compress the hell out of videos.

I also love leather-bound notebooks and glitter pens. Oh! And I can’t live without my lil Address bar. It’s the first thing I turn on when I log into any desktop.

address

What does your workspace look like?

Here’s my home office.

desk

That flag on the right is the Acadiana (Cajun) flag. My wife is kind and keeps my space clean – I’m messy by default. Also, I love Apple products, lighthouses and Aveda candles.

The monitor looks all lit up because it is — I bought some Luminoodle bias lighting to help prolong the time I can spend at a computer ;)

What’s a typical work week look like?

My work week starts on Sunday when I spend the first half of the day procrastinating and the second half doing school work. On weekdays, I get up and get my energy from vitamins and energy drinks or tea, depending on whatever phase I’m in.

At work, I do mostly DBA stuff and try to avoid doing SharePoint stuff. Then I go home and either work or hang out. When I work, I usually do dbatools and/or host a webinar for the PASS PowerShell Virtual Chapter.

My weekly output varies depending on my level of burnout. Sometimes I have to force myself to go to bed at 4am because everything is awesome and I’ve got the Flow or I crash around 8pm, exhausted from staying up till 4am for weeks straight ;)

Reading Steve McConnell’s Code Complete helped me with accepting this oscillation between Flow and burnout, which he pointed out is normal for programmers. I’ve tried to moderate but that always just ends up in a premature onset of burnout.

Speaking of, I once had burnout for 3 years. 2008-2011 was pretty much just spent partying. So if you ever get prolonged burnout, don’t worry. Recovery is possible and things won’t have changed so much that you can’t pick it up. The basics will always remain the basics, unless you work with JavaScript.

What do you like the best about your role?

I really love that I get to work with PowerShell all the time. I get to choose the tools I want to use and I’m encouraged to automate.

Also, my role happens to be in Belgium and I love living in Europe. My wife loves it. Our cats love it. We feel very fortunate to be surrounded by beer, different languages and cool people. The chill but enthusiastic members of the European tech community are so down to earth and amazing to be around, too. It’s heaven.

What’s something about you that no one knows about?

I used to work for Ani Difranco. Well, she was my client. I wrote a really long story about it back in 2004. Warning: the writing is cheesy and reminds me of fanfic.

What do you listen to while you work?

I’m totally addicted to Spotify and have over 100 public playlists. Overall, the heaviest in rotation are the House of Cards soundtrack, Hacker’s Soundrack, Spotify’s Trapaholics and then my own Dark or Slow, which has songs that are either dark or slow (but always sexy).

What do you wish you could change about your work?

I wish that I could enjoy downtime a bit more. I often feel guilty, as though I should be doing something. Unless I’m traveling. So I guess – I wish I could enjoy downtime at home without guilt. I guess in this way, I kinda feel like Adam. There’s just so much to do.

Is there anything else you’d like to add that might be interesting to readers?

When I first won MVP back in 2015, I became totally overwhelmed and went into a 2 month depression. I was SUPER excited, but then I didn’t know how to handle the attention. It took me a while to accept that I could live up to what Microsoft saw in me.

A few things made it better. First was seeing this thread on Reddit where SharePoint MVPs pointed out that one downside to being an MVP is that people assume you know everything about the product, when really each of us have our own specialties.

I always knew people would expect me to be a PowerShell syntax and architecture expert and I’m not. That thread helped me accept this fact. I’m extremely honored that I can help make a difference in the world of SQL Server and PowerShell, however. That’s what I love and it’s my passion. But when I need syntax help, I do what other people do and ask members of the PowerShell team or other MVPs who specialize in the language itself. I usually leave it to others to debate semantics, which I totally appreciate and ultimately benefit from.

The second thing that helped was going to the MVP Summit and being surrounded by MVPs who were really supportive of my work. SQL Server MVP Aaron Nelson has always been a key person for me in this MVP adventure. He’s always so excited to introduce me to people and he nudges me along when I’m burned out or trying to avoid meeting new people because I’m shy.

And in general, the PowerShell MVPs are just an incredible group of professionals. Like, many of us have strong opinions, but at the end of the day, everyone is so respectful and accepting, even when we’re really direct or impassioned. I’m honored to be a part of such a great group; it’s been positively life changing even if my brain got off to a rocky start :)

awesome

Posted in General

PowerShell: Too Many If Elses? Use Switch.

One of my favorite things in PowerShell (and other languages) is the switch statement. It’s clean and a much better alternative to a ton of if elses. Ever had this happen?

The better way to do it is by using the switch command.

Just look how pretty that is. It gets better, though! My buddy Klaas Vandenberghe showed me a more succinct way to use switch.

Even less code and makes total sense. Awesome. There’s even more to switch — the evaluations can get full on complex, so long as the evaluation ultimately equals $true. Take this example from sevecek. Well, his example with Klaas’ enhancement.

If you weren’t familiar with switch, I hope this was helpful. I know I was excited about its elegance when I was first introduced by Lee Holmes and Klaas’ enhancement made it even better.

Posted in PowerShell