Windows Services Recovery for SQL Server

Recently, I ran into an issue after applying a few security updates and subsequent reboots: a number of the SQL services did not start successfully. After running the following script, I’ve had success with SQL services starting as expected after reboot. It’s basically a built-in service start retry. You can read more on Microsoft’s docs page or for a visual guide, check out Ibrahim Soliman’s blog.

In this case, I used a script instead of the GUI because the GUI is limited. In the script above, I can specify exact times per retry whereas in the GUI I cannot.

Also, I can’t believe I never noticed this tab for years, until a friend pointed it out. It’s so useful 😁

Posted in PowerShell, SQL Server, Windows

My setup after a year of livestreaming

Back in January and February of 2019, I wrote couple posts that highlighted my journey into livestreaming on Twitch, from understanding the different services/platforms to the lighting I used. I then followed up in June with a post about what I retired and what I was continuing to use.

Since then, I actually started streaming about Cajun cooking for my website, RealCajunRecipes.com. I bought a couple cool things for that setup, including an awesome Madonna mic, which is called that because she made it famous in her Express Yourself video. I also bought an over-the-top Logitech BRIO – Ultra HD Webcam and some in-ear/mostly hidden Xiaomi Redmi AirDots ear buds to hear my guests who dialed into the stream over Skype.

This is what the Madonna mic looks like – note that this is the built-in potato quality Facetime webcam, not the BRIO.

It delivers some of the best sound because it’s good quality and the mic remains near your mouth the whole time. You’d be surprised just how close you have to get to even a high-end mic for the sound to be great.

I will say, it felt SO GOOD knowing how to do this. I applied everything I learned from my programming stream to my cooking stream. In the first video, I just used the built-in platform interface and subsequent ones used good ol’ OBS.

Retired hardware

Before I go into what I’m currently using, let’s look at what I’ve retired over the past year.

As mentioned previously, I retired the green screen because it added too much prep time and formality to my stream so I found myself streaming less often.

The cats retired the string lights for me by eating them, but in truth, I should have skipped right to the Phillips Hue good stuff. My Bose headband broke (wtf) but I AM IN LOVE with what I got as a replacement. I keep the Elgato Key Light around and don’t regret it but my setup changed and I’m just not using it right now.

Now in use

Here are the things I do still use, broken down into essential and very nice to have.

Essential

Mic

The Yeti Mic is now back in action. I just wasn’t happy with the sound of my other devices. They were either inconvenient, sounded “tinny” or had other issues. The Yeti is easy to manage and the sound is great – much better than the mics built into any of the wireless headphones I wear.

Webcam

For years, I’ve used the Facetime camera and thought it was adequate until I hooked up the Logitech BRIO that I bought for my cooking livestreams. The lighting is SO much better.

 

 

Stream Deck

Still loving my stream deck even if I use just a few combo keys.

I actually have different profiles for both my coding and cooking livestream. Coding has stayed mostly the same and the cooking stream allows me to start and stop my stream, as well as seemlessly switch between pics of me cooking and close-ups of the pot!

Mouse and Keyboard

When it comes to my keyboard and mouse, I’ve always been a fan of Microsoft hardware and that’s never changed. I love these ergo versions that keep my shoulder working just a bit better.

Very nice to have

There are cheaper alternatives to the following or they may not even be needed, but I sure do enjoy having them!

Headphones

The Sony Noise Cancelling Headphones WH1000XM are the absolute best wireless headphones I’ve ever used. I can turn off the noise cancelling if I want, too! This is not only safer for when you’re walking around a city, but some people like me are sensitive to the pressure that noise cancelling creates so in a quiet room, I tend to turn it off. On a busy train, though, it’s awesome!

The accompanying app is just phenomenal and allows me to set my EQ, control the direction of the sound and more.

Networking

I was battling with my Unifi recently and realized later that it was actually my Mac causing all of my issues. Now that I’ve fixed my Mac, I can appreciate just how useful the Unifi dashboards are. When I plugged in my Philips Hue hub, I was able to immediately find its IP address and label it.

This helped me immediately troubleshoot some issues that resulted from me not reading the manual πŸ˜…

Software changes

I haven’t changed too much, except that I switched my live captioning from pubnub to Stream Closed Captioner by talk2megooseman. Gooseman’s closed caption streamer is Twitch native and can be turned off and on.

Oh, I also changed my VS Code theme. I recently switched from Cold Snack to 1984 unbolded.

Music

I also forgot to cover my music setup in previous posts so I’ll go over it now. Ultimately, I wanted people on my stream to hear something different than what I’m listening to. This was due to a few reasons:

  • Copyright – I don’t want to deal with copyright muting out my archived livestreams
  • Volume stability – If I want to turn up my sound, the chat room is coming with me and it may be suddenly too loud or too soft or they may no longer be able to hear me
  • Mood – I like chill background vibes on my stream but may be listening to harder stuff

The way that I get around this is that I setup a dedicated feed for my stream using Soundflower and Rogue Amoeba’s SoundSource.

So I load up a YouTube in a Safari window. I don’t use Safari, just Chrome, so it’s basically a dedicated browser. Then redirect Safari to Soundflower using SoundSource.

Then I create an Audio Output Capture in OBS and point it to Soundflower’s 2ch.

I was tempted to get Rogue Amoeba’s Loopback in place of Soundflower, but it’s just so expensive considering Soundflower works perfectly.

Channel alerts

When it comes to other sounds, to ensure I can hear people following and appropriately celebrate, I also keep a Stream labels window open while streaming. This alerts me where as Twitch’s native interface does not.

Celebrating new followers is an essential part of every stream, so this capability is especially important.

I stopped livestreaming for a while because I was super burned out. I blame that absence for the fact that I’m still not good with emotes or Twitch culture in general, but I’m workin on it! I’m hoping to start livestreaming each Thursday at 7:30PM or 8:00PM Brussels time. Here’s hoping my brain stays on track πŸ”·

Posted in Livestreaming

Remove the new ad in PowerShell 5.1 on Windows 10

I really abhor the new ad in the PowerShell 5.1 console and it seems there’s no hope of Microsoft making it go away.

After a long, involved Twitter conversation with the community and the PowerShell team that confirmed it’s impossible for the advertisement (?!) to be easily removed, it looks like the only solution is to bypass it. PrzemysΕ‚aw KΕ‚ys has a great suggestion to emulate the old prompt that totally works!

First, update the PowerShell shortcut in your taskbar (you have one right? 😁) to use -nologo.

Then add the following to your profile (notepad $profile)

Clear-Host
Write-Host "Windows PowerShell"
Write-Host "Copyright (C) Microsoft Corporation. All rights reserved."
Write-Host

The result ultimately looks like the original prompt. Hell yeah.

If you’re wondering about my prompt, you can find it at dbatools.io/prompt.

Posted in PowerShell

Using robocopy to move SQL Server files

When performing file migrations, PowerShell’s Copy-Item is not ideal. I’ve since forgotten the original reasons I concluded this with the rest of the Internet, but this quote from reddit covers it pretty well.

The Copy-Item and Move-Item cmdlets are general purpose commands. They have the ability to handle many different types of structure.

Robocopy, on the other hand, is highly optimized for copy/move/delete on the filesystem. Only on the filesystem.

That specialization is why it is called Robust Copy.

There are a number of options available in robocopy, and for SQL Server’s purpose, you can probably get away with just /MIR /COPYALL /DCOPY:DAT, I ended up using a few more, as suggested by serverfault:

robocopy C:\oldmount\Data C:\Mount\Data /MIR /COPYALL /DCOPY:DAT /Z /J /SL /MT:"$([int]$env:NUMBER_OF_PROCESSORS+1)" /R:1 /W:10 /LOG+:C:\temp\robocopy-log.txt /TEE /XD "Recycler" "Recycled" '$Recycle.bin' "System Volume Information" /XF "pagefile.sys" "swapfile.sys" "hiberfil.sys"
robocopy C:\oldmount\Logs C:\Mount\Logs /MIR /COPYALL /DCOPY:DAT /Z /J /SL /MT:"$([int]$env:NUMBER_OF_PROCESSORS+1)" /R:1 /W:10 /LOG+:C:\temp\robocopy-log.txt /TEE /XD "Recycler" "Recycled" '$Recycle.bin' "System Volume Information" /XF "pagefile.sys" "swapfile.sys" "hiberfil.sys"

Here are the descriptions for each of the parameters, as described by the serverfault post.

  • /MIR – Mirror source to destination, and delete files and directories on the destination, if they are no longer present on the source
  • /COPYALL – Copy all file info: data, attributes, and timestamps, NTFS Security ACLs, Owner info, Auditing info (not all included by default)
  • /DCOPY:DAT – Copy all directory info – data, attributes, timestamps (original creation timestamp is not copied by default; normally this changes to the date that it was copied by Robocopy)
  • /Z – Use restartable mode
  • /J – Copy using unbuffered I/O (faster copy of large multi-gig files)
  • /SL – Copy symbolic links rather than the target
  • /MT – Use maximum CPU threads (better use of 10 gb Eethernet and many CPU cores)
  • /R:1 – If file access error, retry 1 time
  • /W:10 – If file access error, wait 10 seconds before retry
  • /LOG+ – Log the output to text file, append if log file already exists
  • /TEE – Print results to screen and to log file
  • /XD – Exclude directories, and everything within them. Names with spaces in them need be enclosed in quotes: “Recycler” “Recycled” ‘$Recycle.bin’ “System Volume Information”
  • /XF – Exclude files: virtual memory and hibernation files if they happen to be present on the source: “pagefile.sys” “swapfile.sys” “hiberfil.sys”

I changed a couple things from the original post, like the value for maximum CPU threads, and the quotes around $Recycle.bin because I ran this from PowerShell and I needed the value to be literal. I also removed a couple options (like preserving audit history) that I didn’t have permissions to run.

One huge huge warning about this command: /MIR mirrors directories which can result in deleted files in the destination. In the past, I’ve accidentally destroyed data in a lab, so make super duper sure of your source and destination are right and make sure you want the directories mirrored and not just appended. I am actually unsure of which parameters would just append πŸ€”

Hope that helped! I’m glad to finally get around to documenting this.

Posted in PowerShell, SQL Server

Class of 2019: 25 Microsoft MVPs you should be following

Back in 1996, as I watched a movie about Italy at the Drexel Theatre in Columbus OH, I decided that one day I’d live in Europe.

I didn’t need to move immediately. I just put it on my long-term agenda and figured things would eventually work themselves out with gentle guidance.

Part of that gentle guidance was to casually look for jobs in the UK and Europe πŸ˜… I noticed that a lot of the coolest jobs were offered by the Microsoft-centric recruitment company, Nigel Frank.

I ended up working for another company that had jobs in both America and the EU, but still follow Nigel Frank with the cool jobs to see what skills I need to stay relevant.

Considering this familiarity with Nigel Frank, I was incredibly blown away that they listed me as one of the Top 25 Microsoft MVPs you should be following in 2019. What an honor! I am included with a number of my highly regarded colleagues including Jaap Brasser, Sarah Dutkiewicz, Thomas Poppelgaard, and Paul Andrew.

In 2019, my primary focus will remain on dbatools, though I’m also throwing in a bit of effort into kbupdate and SPReplicator. I’m also continuing to work on dbatools in a month of lunches with Rob Sewell for Manning Publications. Looks like the MEAP will be coming out soon! Stay tuned for more info.

Posted in General

My Current Livestream Setup

Back in January and February, I wrote couple posts that highlighted my journey into livestreaming, from understanding the different services/platforms to the lighting I used.

Since then, I got pretty deep into the hardware part but actually returned to a simplified setup. The third iteration was pretty intense. I even purchased a professional grade microphone and a green screen.

Retired hardware

Before I go into what I’m currently using, let’s look at what I’ve retired since March.

The Yeti Mic is only semi-retired. It’s really high-quality and easy-to-use but it’s kinda in my way and the Bose wireless headphones work well enough. I use the Yeti when I get tired of wearing my headphones.

The green screen was cool af and allowed me to be superimposed on top of my screen, which you can see here on YouTube.

I no longer use it, though, because the setup is not worth the thrill for me. It was nice, but now I just light up my office with some cool blues and purples and go about my day.

I actually never used the floodlights because they were SO POWERFUL that they almost blinded me and Potato. I gave them away but now kinda wish that I still had them because I’d like to use them to make my office bright blue and purple. Before, I was trying to use them to highlight my green screen but it turned out I didn’t need it.

Now in use

Here are the things I do still use

The Bose SoundLinks are not noise cancelling which is why I like them. Noise cancelling headphones create a pressure in my ear that make them unpleasant to use. But these are so comfortable and sound so good, I bought a pair for my wife as well.

I never had time to get into the Elgato Stream Deck, but what I have programmed is super useful and I rely on it every stream. Their Key Light is also awesome and casts a soft light that looks pleasant on screen.

Livestream blue and purple

Seems like every livestreamer has these cool blues and purples in the background. I knew I’d be going away from the green screen but wanted a fun background so I got a few LED light packs.

The Luminoodles backlight my monitors. I LOVE it! But mostly it’s just me that sees it. In the back, though, are the two string light sets. The purple ones I bought are pretty weak but the ZAECANY blues are so bright!

Here’s what it looked like the first time I saw myself in Livestreamer colors.

And here’s what it looks like when I’m coding. This will likely be how my livestream looks from now on.

If you’re wondering about my VS Code theme, that’d be the best theme ever, Cold Snack.

Software changes

I haven’t changed too much, except that I switched my live captioning from pubnub to Stream Closed Captioner by talk2megooseman. Gooseman’s closed caption streamer is Twitch native and can be turned off and on.

Overlay

Oh, and I also made my own overlay! Straight-up from scratch using Paint.NET.

This is how I placed the above overlay in OBS. Super easy.

Starting Soon and Pause screens

I also updated my starting soon and pause screens. I’ve replaced the VHS 80’s theme with something that provides more information for people popping by.

Conferences

I’ve been doing some livestreaming from my phone at conferences, too! At psconf.eu, I did a session with Tyler Leonhardt and Chris Gardner about livestreaming.

This isn’t the official conference video, it was just streamed from my phone so please bear with the quality ;)

We also streamed the launch of dbatools 1.0 from the breakfast room at DataGrillen!

In case you’re wondering, those were both streamed from my iPhone. One over conference wifi and one over a 3G connection.

If you’d like to see all of my previous livestreams, I keep an archive at youtube.com/dbatools.

Livestreaming IP Cams

When I want to livestream my cats, I use the VLC Source and RTSP in OBS. It’s not super stable but generally works for several hours. Here’s a random tutorial I found. Can’t recall the one I used.

My Elgato Stream Deck interface

Haven’t made many changes because this setup works for me 😁

The deck is super useful for automation. Even if you aren’t into livestreaming, you can still automate things like dev/test refreshes with the press of a button.

Maybe I’ll ask for one at work πŸ€”

Posted in Livestreaming

My Second Twitch Livestream Setup

Back in January, I wrote an introduction to livestreaming titled My First Twitch Livestream Setup. In it, I created the how-to guide for getting started that I wish I had the day I found Twitch.

It’s now been a couple weeks and I’m becoming more acclimated to Twitch, though I still don’t get emotes and would prefer if Slack-style :emojis: were still in style πŸ˜… Oh, and in addition to PowerShellLive, I’m now a member of Live Coders! Thanks so much to Jeff Fritz for the invite 🌈

In this post, I’ll outline what I’ve figured out since writing the first post. If you’re new to Twitch, I’d suggest first reading My First Twitch Livestream Setup, doing a few streams that way, then returning to this post. All of it at once can be overwhelming, even if you’ve been doing computers your whole life.

  • Interacting with people
  • Streaming Services
  • Prettier profile
  • Music
  • Secondary camera
  • Stream Deck
  • Lights
  • What I still don’t know

Interacting with people

Twitch is awesome because it lets you “interact with the TV”. As a viewer, I’ll sometimes give a follow to see the delight on the streamer’s face or because I want to interact with them.

But as a streamer, if you don’t explicitly set things up, you can’t really see if someone followed you or if someone subscribed to your channel.

I felt bad that people were giving me follows and I couldn’t thank them, that I mentioned it during one of my streams. Microsoft’s Tyler Leonhardt of the PowerShell team was kind enough to let me know in chat when someone followed (I still don’t know how he figured that out.)

I was extra excited when I finally figured out how to create that alert. So pumped, in fact, I did a dance and posted it on Twitter:

It wasn’t hard at all, I just needed to find the time to do it. So how’d I do it? I used a free streaming service and basically just pasted a web address into OBS.

Streaming services

Streaming services provide all sorts of add-ons to your stream, including Alerts like the one above. There are a few competing streaming services, and I’m finding myself using a mix of them. Three popular ones include:

StreamLabs

One day during one of my livestreams, I was lamenting about how I couldn’t use Streamlabs because I had a Mac and Microsoft livestreamer Jeff Fritz (csharpfritz) told me that I still could! I just can’t use their beautiful OBS wrapper which currently only works on Windows, but I can use their streaming service.

Within just a few minutes of setting up Streamlabs, I had all of my alerts! It was as easy as:

  • Login to StreamLabs using your Twitch account
  • In the Dashboard, click AlertBox in your sidebar navigation
  • Copy the URL
  • Paste the URL into OBS as a Browser Source

Much easier than I expected πŸ‘ By default, there is a running zombie for new followers. It’s cute, but I like Yoshi more so I selected him from gifs included in their gallery. You can also upload your own gifs, but that’s for later.

StreamElements

StreamElements offers a lot of the same things as StreamLabs, like Alerts. It also offers overlays which is the border surrounding people’s stream that gives their social information, etc. Here’s what fellow PowerShell streamer VeronicaGeek’s overlay looks like.

Veronica was the one who told me about StreamElements after I asked how she did her overlay. I still haven’t entirely figured out the overlay thing so that’ll be included in my Third Twitch Livestream Setup.

I have not used muxy so I don’t have anything to report about that yet.

Prettier profile

I also stopped borrowing someone else’s Panel pics and made my own at nerdordie.com πŸ‘ It’s ultra easy. Here, I’ll do one right now for the blog.

Tyler took it a step further and paid someone to do his (I think). His profile is so good! Love the posh-git theme.

Music

I’ve changed my position on music, too. Mostly. I’m still not a big fan of pretzel, but I do enjoy streams like Jeff Fritz’s that use coder-centric music services.

These are playlists and sounds designed to be unobtrusive background melodies that get you in the zone. I also love lofi hip hop radio – beats to relax/study to but in my test stream, out of like 20 hours, 6 minutes did get muted.

Anyway, if you don’t care about getting muted in the video playback (live never/rarely gets muted), letting your viewers rock along with you is a lot of fun.

Secondary camera

I ended up adding a secondary camera to my stream, too. I had a Nest Cam laying around and added a Cat cam to my stream! It’s super cute.

They even have their own dedicated account, now. Check out this clip, which is a short highlight video, of Kitty and Potato.

The Nest Cam is an IP camera and I added it to OBS by following this tutorial on YouTube.

The awesomest

By far, the best addition to my Twitch stream has been the Elgato Stream Deck. This thing is SO FUN! It’s a macro automation keyboard that’s so well-made. Elgato seems to be like the Mercedes Benz of Streaming equipment. They also sell a gorgeous light called the Key Light and an elegant green screen.

You don’t need a Stream Deck to be a streamer, and you don’t need to stream to have a Stream Deck! After I posted a picture of some of the keys I’d done, my buddy Lee bought one just to make automation easier. My deck setup is still unfinished, but here’s the progress I’ve made.

The button in the middle starts PowerShell on my Mac and imports dbatools 😎 The row at the top helps me go between scenes real quick. And the steps are so easy! Check out my “Starting soon” button configuration:

It even changes my Twitter name and tweets for me! The configuration is drag and drop, too. If you want to extend it, that is also possible. I’m obsessed with this stream deck so I’ll probably write a follow-up intro article at some point.

Lights

Good lighting makes a huge difference! I turned on the lamp on the side of my desk and it was awful, like when you accidentally open the front facing camera.

Fortunately, I had a Happy Light laying around and it turned out to be perfect! You can’t even tell I have forehead wrinkles now, as can be seen in this reduced-quality Twitter video where I talk about the first time I met Jeffrey Snover.

Not saying that you gotta get a Happy Light, but if you’ve got one, it works well. There are lights dedicated to this, including the Elegato light, and also, a lot of people use webcams that have this soft light built-in, like the one Jeff Fritz mentions in his post Live Streaming Setup – 2019 Edition, the Razer Kiyo camera.

What I still don’t know

I don’t totally understand subscriptions, loot, cheers and all that. It looks like people give and get real money, but that seems outlandish πŸ˜…

I also want to learn more about bots, bot mods and emotes. Some emotes, I just don’t know what they mean. Like, what the hell is a pog champ?

Posted in Livestreaming, PowerShell

dark mode is for lovers πŸ–€

This post is about all the things I use to fill my life with dark mode. I’m mostly writing to tell everyone about a free browser plugin, Dark Reader, that’s incredible, but will include other extras as well.

Dark Reader

The browser plugin Dark Reader has changed my life and I’m obsessed with it. As I explained to a friend “I have the best plugin in the world to recommend. It automatically dark themes every website by detecting color schemes and then rewrites a matching dark theme dynamically”

It works on sites with complex code themes too! But I don’t know if you missed it in the first screenshot, you can control the brightness! The contrast! The SEPIA! What?

Dark Reader is free for Chrome, Firefox and indirectly on Opera. For Safari, it costs $4.99. I ended up donating $5 and will probably donate regularly. I’m so shocked at how great this plugin is.

HazeOver

HazeOver is a “distraction dimmer” for Mac. I originally got it to help fix the dumbest issue ever: Mac brightness controls don’t control the secondary monitor(?!) So I got HazeOver to dim my secondary monitor but it turned out to be awesome in general. It dims whatever windows aren’t active and it’s damn smooth about it.

image

It costs $9.99 but you can do a free trial. I decided to give give them my money after seeing all of the incredibly glowing reviews. Now I’m one of those reviewers being like “best app ever!”

Dark Mode Script for Slack

Adil Leghari created this awesome PowerShell script to dark mode Slack.

image

Slack is outrageous and I’ve given up on them supporting dark mode. It’s literally been 5 years since they started saying it was on their radar πŸ™„

Windows and mac OS

In better news, both Microsoft and Apple delivered on dark modes! Windows 10 October 2018 update now supports dark mode! And, of course, mac OS Mojave is downright gorgeous.

Luminoodle

For dark mode irl, check out Luminoodle TV Bias Lighting. The back of my monitors point at the wall and it creates such a nice glow that super easy on the eyes 😍

If anyone has some dark themers that they are pumped about, let me know in the comments!

Posted in General