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

Tips on Speaking

I’ve been seeing quite a bit of posts about speaking mentorships and I wanted to share a few speaking tips I recently gave to my buddy Garry Bargsley who will be speaking about dbatools at SQL Saturday Phoenix 2018.

The first time I ever spoke in front of a live audience was in Antwerp, Belgium for SQL Server Days Belgium 2015. I was petrified. Years earlier, I was on a regional television show called Good Morning Acadiana and couldn’t even speak. Fortunately, my mom was a co-presenter and picked up my slack. Remembering this, I wondered if words would successfully leave my mouth when I presented in person. They did :D

Since then, I won Best Speaker Overall with Rob Sewell and Best Lightning Talk at SQL Saturday Dublin. I was sooooo blown away, especially considering many of my SQL idols were speaking as well.

me crying tears of joy with co-presenter Rob

The good thing about my first presentation was that Cathrine Wilhemsen was there to support me. She was amazing and awesome and if you can ever have her at your first time speaking, I highly recommend it.

Tips

  1. If you’re high energy, don’t be like me on Good Morning Acadiana: don’t drink a lot of coffee before you present. It gives you dry mouth and makes you even more nervous. I, instead, opt for vodka (see point 4).
  2. In the previous months, I was having A/V issues that required me to basically give my speech about 120 times if not more. My speech became muscle memory and even my wife knew every word. It helped knowing I’d be immediately prepared if I ever lost my place.
  3. I videoed myself to see what wasn’t coming across right. Because of this, I noticed that I used my hands in a way that was distracting. I thought it was helping with emphasis, but it became a crutch and a distraction.
  4. My buddy Crawford told me he always takes two shots of whiskey to start a presentation. Whiskey is nasty, so I take 2 shots of Grey Goose vodka. My wife even packs me a tiny cute vodka bottle. The first 2 minutes are often the hardest and vodka or Belgian beer makes it easier.
  5. Cathrine pointed out that my session was fab but that it ended on a weak note – you couldn’t tell that I was actually finished because.. I never really practiced that part. Now I always say something like “My name is Chrissy LeMaire, thanks so much for joining me today”
  6. I don’t aim to teach a whole subject in 60 minutes or less. I’d bet that I suck at teaching PowerShell from scratch. My aim is to excite people into learning more. I knew I accomplished this at SQL Days Belgium when one of the organizers came to me and said that my session made him want to run home and play!
  7. Audiences generally prefer demos, so do a lot of those if you can. And keep your demo environment simple. I used to have a whole domain with clustered and stand-alone instances. It was so stressful! Nowadays, I just roll with a single VM (using Parallels) with 2-3 local instances. I rely heavily on Snapshots and update my VM regularly.
  8. When I practice at home, I always practice as if there’s an audience. I’m standing, my voice is booming and I’m looking at the wall. This, incidentally, helps me when I’m too exhausted to maintain eye contact. I look right above the audience, at the closest wall above people’s heads. This gives me the appearance of being engaged without having to exert the effort of finding the ultra-friendly face.

Something you don’t see on this list is Toastmasters. I actually went to a meeting but found that I’d potentially be picking up bad practices. Our meeting’s toastmaster played very much by the book. They had pauses, eye contact, and articulation but it felt so robotic. I opted instead in embracing myself and not thinking “Pause. 1-2-3. Speak.” in my head. Others may find it useful but it wasn’t my style.

Just do it

For the first year or so, I dreaded presenting but felt compelled to share my PowerShell story. I needed show the SQL Server community how simple PowerShell could be. I needed to change minds and wanted to excite people. So even though I was tortured up until the day I presented, I still did it anyway. And those memes you see on Twitter were true for me. The stages of presenting:

  1. Dread and anxiety
  2. Presenting
  3. Wow, that was actually super fun and rewarding! Let’s see where else I can present!
  4. Dread and anxiety

But that eventually goes away and speaking gets easy! Now, my original argument against public speaking has been invalidated. I used to say “Public speaking is like eating canned lima beans. I hate it. And no matter how often I do it, I always will.” Well, I still hate canned lima beans, but I enjoy speaking publicly about PowerShell ;)

And a wonderful side effect of speaking for me has been making new friends. Now, I don’t feel so isolated here in Belgium. I know within a couple months, I’ll be seeing my friends somewhere neat and new, like Iceland, Ireland, Vienna, Lingen or Seattle ❤️

And don’t give up

My first year or two of session submissions was filled with rejection emails. But I just whimpered a bit then continued to submit every place I’ve ever wanted to visit.

Now, I get to present precons at PASS Summit and even SQLBits! So if you get several rejections in a row, just keep refining your abstract and keep on submitting.

Speaker dinner

Oh, and don’t ever skip a speaker dinner! Like Cathrine told me, it’s one of the coolest parts of the whole experience. And the food is always awesome!

If you’re a new speaker, congrats! I hope these tips help. If not, take 2 shots and call me in the morning ;)

Posted in General

SSH Tunneling for Windows: Protecting RDP using Windows 10 Fall Creators Update

NOTE: If you’re not yet using Windows 10 Fall Creators Update, please follow this article instead.

The recent OS X High Sierra iamroot vulnerability reminded me just how many people don’t secure their remote desktop connections. While Windows Remote Desktop is more secure than VNC, neither RDP, ADP nor VNC should be directly exposed to the Internet.

Securing RDP

My favorite way to secure RDP is RD Gateway which uses SSL for encryption.

But another way to secure remote connections is SSH tunneling. SSH Tunneling is not as complex as it sounds; setup is basically this:

  1. Setup an SSH Server, be it on Windows, OS X or Linux
  2. Setup port forwarding on your router to that SSH port
  3. Setup your SSH client to forward a local port (12345) to a remote port (sql2016:3389)
  4. Connect Remote Desktop Client to localhost:12345 which connects to sql2016

In order to introduce the concept, we’ll set this up using Windows which now includes OpenSSH! Once you’re comfortable with the concept, you’re free to branch out to use other versions of OpenSSH Server, like the ones that come with OS X or Linux.

Install OpenSSH on Windows 10 Fall Creators Update

How cool, OpenSSH Server and Client is now built-in to Windows 10 Fall Creators Update! So let’s install them. Hit Start then type Optional

Now install both the Server and the Client

Once you click Install, nothing will really happen. Click the arrow in the upper left hand corner and it’ll take you back to the previous page where you can see the features being installed.

Once the Server and Client have finished installing, you must reboot.

Configure OpenSSH

Gotta say I’m super thankful for Chris K’s blog post “Enabling the hidden OpenSSH server in Windows 10 Fall Creators Update (1709) — and why it’s great!“, otherwise this would have taken me far longer to figure out.

So next, Run PowerShell As Administrator, then generate a key.

cd C:\windows\system32\OpenSSH
ssh-keygen -A

Now, we’ve got to tighten permissions and start the service. Note that if you try to skip this step, the SSH Server will fail to start with “The sshd service terminated unexpectedly.”

If you encounter issues and need to troubleshoot, Event Viewer won’t be of much help. Try C:\windows\system32\OpenSSH\Logs instead.

Security

There doesn’t seem to be a lot of documentation on the web on this specific implementation of OpenSSH Server, unfortunately. There is an sshd_config in the OpenSSH directory but I couldn’t figure out how to edit it (kept getting access denied).

That being said, I didn’t make my usual changes to the config file and, since it’s all commented it, I don’t even know what configuration it’s running with, so I’m trusting Microsoft on this for now.

Set up port forwarding on your router

An in-depth tutorial on how to do this is out of scope for this article. Note that whatever you do, don’t use the default SSH port, meaning don’t forward external 22 to internal 22.

Bots bang on port 22 all day, every day. This can fill up your logs and maybe even break a shitty password but hopefully you’re using a solid password.

Configuring OpenSSH client

Usually, I do this part with PuTTY but here, we’ll use the newly available OpenSSH Client.

Now let’s say your external IP is 24.0.175.222 (my first broadband IP back in ’97), you’ve forwarded port 22222 to your Windows 10 port 22 and you’d like to connect to domain computer sql2016’s RDP port (3389).

ssh -f [email protected] -p 22222 -L 12345:sql2016:3389 -N

Let’s break this down (thanks to Frank Wiles for the simplified tutorial).

  • -f sends the ssh command to the background so that you don’t have to keep PS open
  • “chrissy” is a local Windows account on the Windows 10 workstation
  • 24.0.175.222 is the public IP
  • 22222 is the public port you’ve published SSH to
  • -L is local port, so localhost:12345 will be forwarded sql2016:3389
  • -N instructs OpenSSH to not execute a command on the remote system

Once you’ve made a successful connection, fire up Remote Desktop Connection!

Remote Desktop Connection

Now for your RDC/mstsc, use localhost:12345 as the hostname. If you recall, this will forward the connection to sql2016:3389.

Next, the hostname will be mismatched, of course. So accept it.

And voilà! MAGIC!

Now imagine all of the possibilities of encrypting insecure protocols! You can even jump to an OSX VNC server from here. And it’s nice and secure that you just have to open one (encrypted) port to securely gain access to your network.

In conclusion

If you’ve been exposing insecure protocols to the net, please consider wrapping them in the loving arms of SSH 💞👍

Posted in Security, Windows

SSH Tunneling for Windows People: Protecting Remote Desktop

NOTE: If you’re using Windows 10 Fall Creators Update, please follow this article instead.

The recent OS X High Sierra iamroot vulnerability reminded me just how many people don’t secure their remote desktop connections.

While Windows Remote Desktop is more secure than VNC, neither RDP, ADP nor VNC should be directly exposed to the Internet.

Securing RDP

My favorite way to secure RDP is RD Gateway which uses SSL for encryption.

But another way to secure remote connections is SSH tunneling. SSH Tunneling is not as complex as it sounds; setup is basically this:

  1. Setup an SSH Server, be it on Windows, OS X or Linux
  2. Setup port forwarding on your router to that SSH port
  3. Setup your SSH client to forward a local port (12345) to a remote port (sql2016:3389)
  4. Connect Remote Desktop Client to localhost:12345 which connects to sql2016

In order to introduce the concept, we’ll set this up using Windows. Once you’re comfortable with the concept, you’re free to branch out to use other versions of OpenSSH Server, like the ones that come with OS X or Linux.

Installing the SSH Server

First, bash for Windows must be setup. This requires Windows 10 or Windows Server 2016.

Note: this was written for Windows 10 pre-1709. Apparently, the new update contains a ton of changes. Developer mode is not required and you install your Linux distro from the Windows Store. Seems that it may even include Open SSH right out the box. I’ll test on Tuesday and let you all know. Till then, here is how to do it if you’ve got Windows 10 without Fall Creators Update (FCU).

Enable Developer mode if required

If you haven’t enabled Developer Mode yet, do so now.

Hit start and type Developer

Click Developer Mode

The installation of Developer mode took about 10 minutes on my virtualized workstation.

Install Linux Subsystem

Next, install the Windows Subsystem For Linux feature. You can do so by running PowerShell as admin, then the following two commands:

  • New-NetFirewallRule -DisplayName ‘SSH Server Inbound’ -Direction Inbound -Action Allow -Protocol TCP -LocalPort 22345
  • Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux

You’ll then be prompted to reboot. Once you’ve rebooted, hit Start and type “bash”. You should see either a bash app or Bash on Ubuntu on Windows. Click and follow the instructions (and use a strong password).

Excellent, now let’s install the SSH Server.

Install, configure and restart OpenSSH Server

From bash, type the following. Note that sudo means “super user do”. It’s like Windows UAC, you will have to enter the password you created during setup.

sudo apt-get install openssh-server

Once the SSH server has been installed, you must now edit the SSH configuration file:

sudo vi /etc/ssh/sshd_config

Note that I like vi (mostly because it’s available by default on most distros). You are also free to use a more simplified text editor like nano by typing sudo nano /etc/ssh/sshd_config. Anyway, a couple things need to be changed. Two for security and one to let us login using our strong password.

Port 22345
PermitRootLogin no
PasswordAuthentication yes

Save your changes and restart the SSH service

sudo service ssh –full-restart

Set up port forwarding on your router

An in-depth tutorial on how to do this is out of scope for this article. Note that whatever you do, don’t use the default SSH port, meaning don’t forward external 22 to internal 22345.

Bots bang on port 22 all day, every day. This can fill up your logs and maybe even break a shitty password but hopefully you’re using a solid password.

Configuring PuTTY

PuTTY is an awesome open source SSH client for Windows that supports SSH tunneling. They have an installer, but I always just download putty.exe.

Now let’s say your external IP is 24.0.175.222 and you’ve forwarded port 22345.

Ok, now on the left, expand SSH then click Tunnels.

The “Source port” is the port you’ll be connecting to locally using Remote Desktop Connection. Pick a port that is free. Here, I make it 12345, which connect to my server “sql2016” on the default RDP port, 3389. Now click Add.

Ok, now on the left, go back to Session then name the session and click Save

Now Open and say “Yes”

Enter your password when prompted

Once you’ve made a successful connection, fire up Remote Desktop Connection!

Remote Desktop Connection

Now for your RDC/mstsc, use localhost:12345 as the hostname. If you recall, this will forward the connection to sql2016:3389.

Next, the hostname will be mismatched, of course. So accept it.

And voilà! MAGIC!

Now imagine all of the possibilities of encrypting insecure protocols! You can even jump to an OSX VNC server from here. And it’s nice and secure that you just have to open one (encrypted) port to securely gain access to your network.

Couple more things

Now that you’ve installed Linux on Windows, you should update it.

sudo apt-get update
sudo apt-get upgrade
sudo apt-get dist-upgrade

Also, I haven’t researched how secure Bash on Ubuntu on Windows’s OpenSSH server is, but I can’t help but think it’s easier, more convenient and potentially more secure to setup a “real” SSH server, be it Linux or OS X. Otherwise, Window’ OpenSSH server closes once you closes bash.

(To get around this on Windows, it seems that you’ll have to set it up as a service or get creative with scheduled tasks.)

In conclusion

If you’ve been exposing insecure protocols to the net, please consider wrapping them in the loving arms of SSH 💞👍

Posted in Linux, Security, Windows

TSQL Tuesday #96: Folks Who Have Made a Difference

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 Ewald Cress‏ (blog | twitter), is all about people who have made a difference in our careers. I’m listing several.

  • My mom, Kimberly Tripp, and Kalen Delaney
  • Dr. Shaw Lin
  • Fabian Dibot
  • Aaron Nelson
  • Rob Sewell
  • Cathrine Wilhelmsen
  • Jeffrey Snover & Lee Holmes
  • Brandon Abshire

My mom, Kimberly Tripp, and Kalen Delaney

I’d like to open with the women who normalized women in tech for me. Growing up, my mom was an accountant who always had PCs around the home office. We were the second family in town to get a mouse, right behind the Leblanc family. My mom knew that computers would be a big deal and encouraged her three kids to play on her Leading Edge 8088s. She even paid me $10/hr to format floppy disks. Her encouragement paid off, and all three kids ended up in tech. My oldest brother leads the Air Force Cyber Command and my other brother is a a tech-oriented hospital administrator who started his executive career at a medical software company.

Years after formatting those floppy disks, I decided to become a DBA and jumped into the world of SQL Server. I remember seeing Kalen Delaney and Kim Tripp everywhere. Their talent and advice were sought after yet there was rarely, if ever, a mention that they were women. They were simply SQL Server experts. This enabled me to relax because I knew my gender wouldn’t have to be a big deal in my career.

And that’s ultimately what I want. Sort of like how it’s not a big deal in Europe that I’m married to a woman, and there’s no “WOW!” when I introduce my wife, it’ll be a great day when girls and women in IT exist to the point that it’s just not a thing and there is no need for them to seek refuge in knowing there’s a Kim or Kalen who’s already paved the way.

Dr. Shaw Lin

Back in 1999, Shaw “please don’t call me doctor” Lin gave me my first well paying job. He also gave me career changing advice. “Chrissy, drop out of college.” This was coming from someone who had two engineering PhDs from an Ivy League school. “Wait, what?” Shaw then went on to say that this tech boom will likely only happen once in my life, and that, once it ends, the experience I can gain at his company would provide more immediate value than school. Plus, once the bubble bursts, I can always go back to school and get my next employer to pay for it.

That’s exactly what I did. I’m now almost finished with my Masters in Systems Engineering (two classes left!), don’t have any debt, and can claim nearly 20 years of experience with SQL Server. Thank you forever, Shaw!

Fabian Dibot

Back in January 2015, French PowerShell MVP Fabian Dibot nominated me for PowerShell MVP and changed the course of my life. Winning the MVP award was insanely thrilling, totally overwhelming and completely life changing. Without Fabian’s nomination, faith and encouragement, I wouldn’t have had all the opportunities that I have today.

Aaron Nelson

Aaron contacted me soon after I won the MVP, and together we helped shape the world of SQL PowerShell. Aaron can be relentlessly encouraging, which is sometimes exactly what I need.

Aaron’s excitement about all of the possibilities of SQL PowerShell has been genuinely inspiring, and it’s been a blast working, advocating and celebrating together.

Rob Sewell

Presenting regularly with Rob has been one of the most satisfying advancements in my career.

It’s positively magical to have such an ease with a fellow co-presenter. I remember Luc Dekens talking about presenting with his friend Alan Renouf saying how natural and easy it was. I thought he was so lucky to find his person and now I found mine.

It’s reassuring to know that I can call Rob up at any time and ask if he’d present at any conference with me and know that the answer will be “of course!” Rob has an ease that I admire and aim for in my own presentation style.

Cathrine Wilhelmsen

I was fortunate enough to have Cathrine sit in on my first ever public presentation. What a treat! I was a nervous wreck and confessed my anxiety to Cathrine. Even though she’s not a DBA, she offered to watch my session, give moral support and let me know if there’s anything I can improve.

Cathrine noted that the presentation went very well overall, but the end just kind of fizzled out. I agreed. Cathrine suggested that I end strong, letting everyone know that the presentation has concluded. “How about ‘My name is Chrissy LeMaire and thank you so much for joining me today'”.

I swear, that’s been some of the best advice ever! I consistently see positive responses when I end on that note.

Jeffrey Snover & Lee Holmes

Jeffrey and Lee are so 😍 to me because they are both brilliant yet remain down to earth.

I met Lee at a conference back in 2005. He was super approachable, encouraging and enthusiastic and he remains so today.

Same goes for Jeffrey Snover. Jeffrey the world famous inventor of PowerShell who became a Technical Fellow at Microsoft yet he is still super kind, always smiling and always delighted to hear your PowerShell stories. Also, he doesn’t take himself so seriously that he can’t take a pic doing the femme head tilt at PSConf.eu

Brandon Abshire

I’d like to close this post by giving a shout out to my lifelong BFF, co-founder of RealCajunRecipes.com and a fellow DBA, “Bun”.

Brandon is one of the smartest technologists I’ve ever met and I admire so many things about him. Not only did he teach me PowerShell, but he also gives me confidence as a DBA because if I ever get stuck on some issue (the more complex, the more up his alley), I know I’ll always have someone to jump in and help solve it with me.

Brandon is also funny as hell and a talented artist, as can be seen the original RealCajunRecipes.com mascot that he created, Alphonse Renee Adams.

    

I love you bawwww.

Posted in PowerShell, SQL Server

SQL Server / Windows OS losing connection to Domain Controllers

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. No authority could be contacted for authentication.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

 

A handful of my SQL Servers began losing connecting with the domain controllers after recent Windows Patches.  The only resolution was a reboot of the SQL Server, which obviously incurred downtimes.  The issue hit two non-production VMs and also a Windows SQL Server Cluster.  Oddly, both nodes in the cluster were affected simultaneously, even though SQL wasn’t running on the passive node.  After some troubleshooting with Microsoft, we identified the issue and I wanted to share it here.  A fix is pending, targeted for July.

The issue affects Windows Server 2012 OSes utilizing iSCSI storage and was introduced with KB4012216, a March security roll-up.  The total amount of ephemeral ports on the system becomes exhausted over time.  I won’t spend too much time showing you how to isolate the specific data we collected for Microsoft.  I feel that if you are experiencing this issue after a recent application of patches, and you are running Server 2012 with iSCSI, that is probably proof enough.

UPDATE:  We observed this behavior on servers not using iSCSI, but iSCSI was still enabled and causing the problem.  We also found corresponding Event IDs 4227 in the System log.

TCP/IP failed to establish an outgoing connection because the selected local endpoint was recently used to connect to the same remote endpoint. This error typically occurs when outgoing connections are opened and closed at a high rate, causing all available local ports to be used and forcing TCP/IP to reuse a local port for an outgoing connection. To minimize the risk of data corruption, the TCP/IP standard requires a minimum time period to elapse between successive connections from a given local endpoint to a given remote endpoint.

You can view some details about ports in use with the following commands, the first being a PowerShell command:

There is no permanent solution, but the following are options for workarounds until a patch is released.

  1. The most obvious would be to uninstall the patches.  We uninstalled all 3 roll-up patches that we applied, but Microsoft indicates that it is part of KB4012216.
  2. You can stop using iSCSI.  Not a viable solution for most.

Increase the number of available TCP ephemeral ports and modify TCP Time Wait Delay to increase the time it takes for the issue to manifest.  Type the following from a command line prompt and restart the server:

I hope you are able to use this information to fix any recurring issues you’ve experienced in your environments.  I spent the last 3 or 4 nights rebooting SQL Servers after hours, but not tonight!

 

Posted in Active Directory, Security, SQL Server, Windows

SQL PowerShell Pros: Submit Your Abstracts for PASS Summit!

Recently, PASS announced their first round of speakers for Summit 2017 speakers and I’m on the list! I’m particularly excited because Summit is the largest SQL Server event in the world and it’s looking like PowerShell will be getting some great exposure this year.

In addition to announcing that I’ll be speaking, PASS also declared that PowerShell is a hot topic for 2017! It’s no surprise, 2016 and 2017 have proven to be pivotal years for SQL PowerShell because of increased investment from both Microsoft and the community.

If you are a SQL Server Pro who loves PowerShell, please answer the Call for Speakers and submit an abstract! The deadline is coming up soon, on Wednesday, May 24 at 21:00 UTC. I’ve got a feeling that this year, there will be a big ol’ PowerShell Party at Summit and it would be amazing to have as much representation as possible.

If you’re concerned that you don’t have enough speaking experience, consider that earlier this year, PASS said that the selection process will be changing. I don’t know the details of the changes, but knowing PASS was going in different direction encouraged me to submit and it worked out well!

So please SQL PowerShell Pros, come to PASS Summit 2017 and represent. The community needs to hear from you!

Posted in PowerShell, SQL Server

PowerShell Gallery Metapackages

Recently, I was approached by supa-famous Joey Aiello of the PowerShell team at my favorite conference ever, PSConf.eu.

So Joey comes up and says “Chrissy, Aaron Nelson has pretty much required me to talk to you. The SQL Community has the #1 PowerShell UserVoice request. We see that – we’ve heard you, The People want Out-DataTable and we agree. Would you be happy if we added it to the PowerShell Gallery first?”

“Uh, no! I want Out-DataTable to be a first class citizen like Out-GridView.”

“But where we’re going with PowerShell — we’re going smaller – to just core files, then you add on from the Gallery as desired.”

“Oh dang, like Linux! I’m liking it, keep talking.”

“To be clear, this is post 6.0. In the 6.0 timeframe, but we want to decouple as many release trains as possible, like PowerShellGet and PSReadline. But we’ll still very well package the ‘uber-complete, awesome devops tool edition’ of PowerShell. In the meantime, you could setup a metapackage for just your database stuff.”

“So it is like Linux patterns! PowerShell Gallery does that? I’m sold.”

Why I’m really happy with this answer

I’ve used Linux for a really long time and appreciate how you install your core files, then pile other stuff on as needed. In SUSE Linux’s Package Manager (and maybe others) these are called patterns.

So for me when setting up my Linux servers, I skip on the GUI patterns and select, for instance, a LAMP pattern. That has all the stuff I need like Apache, PHP, Python, MySQL, etc — but nothing more.

If you take a closer look at the screenshot above, you’ll see some sample patterns include

  • Console Tools
  • Laptop
  • Software Management
  • X Windows System

If you check the Console Tools, you’ll find a number of packages that make it easier to work with the console including alpine, nano and ncftp.

So about Metapackages

PowerShell is similar in this way now that you can create a PowerShell Gallery module that’s made solely of other modules or packages. Recently, I created a metapackage named Database. Currently, this metapackage contains

In the future, I’ll be able to add Out-Datatable (or whatever they name it, perhaps ConvertTo-DataTable) and then any other database related module that makes sense. If you have any suggestions, let me know.

Here’s how I did it (copy/paste/change guid)

First, you must ensure that you’ve installed the modules that you want to require. Initially, I got an error when trying to Publish-Module and because I needed to install the DSC resource, xSqlServer, prior to attempting to include it in the metapackage.

Next, create new directory that’s the same name as your module, then add a psd1 to that directory. That’s all you need. Just one module manifest (.psd1) in one folder. So for the Database module, mine was C:\github\Database\database.psd1.

This is what the actual psd1 looks like – note that the important line is:

RequiredModules = @(‘sqlserver’,’dbatools’,’dbareports’,’PowerUpSQL’,’xsqlserver’)

If you’d like to create your own metapackage, you can copy/paste and then change the GUID and info. To get a new GUID, just use New-Guid or [guid]::NewGuid() to generate your own unique GUID.

Publishing

Publishing your metapackage to the PowerShell Gallery is like publishing any other PowerShell module. First, you sign up to the PowerShell Gallery, then get your NuGetApiKey key, then… Actually, just follow this tutorial by Adam Bertram :).

I stored my database.psd1 in C:\github\Database, so I published it by executing

Publish-Module -NuGetApiKey xyz-xyz-xyz-xyz-xyz -Path C:\github\Database

Installing

Once your module has been published to the Gallery, it can be installed like any other module from the Gallery.

My own package contains the SqlServer module which has a naming issue on some machines, so if you’d like to install the Database metapackage, just throw in -AllowClobber at the end.

Install-Module database -AllowClobber

Check out how cool this is — it automatically downloads all the required modules :D Nice work, PowerShell Team!

Hope that’s been helpful. If you have any questions about metapackages, hit up me or Joey on Twitter (and follow him while you’re at it. He needs a couple followers ;)

Posted in Linux, PowerShell, SQL Server