Tuesday, November 29, 2016

SQLCMD vs. Registered Servers

I always like it when my regular testing produces a usable example of something fun. For example, for a long time I have held that even on a local VM, connecting using SQLCMD is slower than Registered Servers.

I love SQLCMD, don't get me wrong, and I've written some very useful system maintenance scripts that would be very, very difficult to have gotten right any other way. That said, in my opinion, SQLCMD is just another tool for us to use when solving various problems.

For what I wanted to do with this test, though, was run a dead-simple query, SELECT GETDATE(); against all seven SQL 2016 instances on my VM.

Using SQLCMD, it took 29 seconds to run, and using Registered Servers, the same query against all seven instances took less than one second. Amazing performance.

I then added a bit of timing metrics to the output of the SQLCMD version, and figured out that SSMS, using Registered Servers, is so much faster because it's a multi-threaded, multi-connecting application. SQLCMD is, but it's very nature, single threaded, due to running all commands in one SPID.

Like I said earlier, I've had great experiences using SQLCMD and use it regularly for a whole host of things, but it's selling point isn't speed.

Tuesday, November 22, 2016

Enjoy your Thanksgiving!

If you're in the US, enjoy your time with family and friends this week for the Thanksgiving Holiday!

Tuesday, November 15, 2016

Another "Oh Nice!" Moment

I didn't realize you can log in to a remote instance using SQL Server authentication and SQLCMD from with SSMS.

The syntax looks like this:

:CONNECT servername\instance -U usernamegoeshere -P passwordgoeshere

Tuesday, November 8, 2016

Summit 2016 Wrap Up

This year, I was fortunate enough to be able to attend PASS Summit 2016.

As a first timer, I have a few post-Summit thoughts:

  1. Take hand sanitizer next time. Wound up having to buy some from a local pharmacy, and it was an easily packed item.
  2. Pack extra vitamin C + cold/flu prevention strategy. Again, I ran out, and had to buy some from a local place.
  3. Take an extra suitcase. Books, goodies, mementos for the kids all add up, and it would have been easier to pay for a second checked bag than throwing things away.
  4. Remember the kilt. It was awesome seeing the display of support for the Women In Technology lunch.
  5. Build in a weekend before or after to decompress, best spent in Seattle doing "stuff". I flew in on far too tight a timeline this time around, and missed seeing all the city had to offer due to an almost punishing schedule.

Tuesday, November 1, 2016

The type initializer has failed ! Oh Noes!

So I was trying to connect to my VM, and I got a very strange error.

The full error message says

Cannot connect to
The type initializer for "Microsoft.SqlServer.Management.Common.ConnectionInfoHelper" threw an exception. (Microsoft.SqlServer.ConnectionInfo)
AccessToken (Microsoft.SqlServer.ConnectionInfo)

As I was researching why my copy of Management Studio was unable to connect, I realized I was still on a previous version, 13.0.15700.28, and that the most current version was 13.0.16000.28.

One upgrade later to the latest release of Management Studio 2016, and I'm back in business, and able to connect successfully.