Mar 122018
 

I think one of the reasons I like computers is that they’re so much easier to deal with than people. They’re predictable, emotions never get in the way, and there’s always a logical explanation for everything if you dig deep enough. All that being said, I am grateful for my hobbies that don’t involve sitting in front of a computer all day. Thinking through the things I enjoy in my spare time, three central themes that seem to keep popping up are art, architecture, and railroads. Here’s two examples of that:

Turner Family Christmas Cards

The University of Illinois will always be a special place to both me and my family. I graduated from there, was a proud member of the marching band, and got my start in public speaking there. It’s also the place where I met my wife, and to top things off we were married on campus. We have plenty of Illinois memrobilia of varying degrees of rarity, but the collection I’m most proud of is something so obscure that most Illini fans won’t even know they exist.

Fred Turner spent nearly 50 years of his life at the University of Illinois, beginning as a student in 1918, and ending with his retirement in 1966 as the university’s first Dean of Students. Turner loved Illinois and its history, and in 1946 he and his wife, Betty, decided to highlight these topics in Christmas cards they sent to their friends and family. Turner had recently picked up the Japanese art of woodblock printing, and decided to share it by creating a hand-carved woodblock print of a historical site in Illinois each year as the cover of their Christmas card. For an extra tie to campus, the blocks for printmaking were carved from wood salvaged from one of the University’s earliest buildings. They printed 260-275 by hand at their kitchen table each year until 1974.

The cards contain a main image on the front, a brief description and Christmas message from the Turners on the inside, and a fact sheet with more information on the historical building or site chosen. Here’s what they are like:

Turner Christmas Cards
I can only imagine that most of these cards have been lost to a combination of time and people just not knowing what they are. I know of only two complete sets of all 29 cards. As for my own collection, I’ve got a long way to go – I only have three. I found my first one five years ago, and have only come across two more since. Our trips to Central Illinois frequently involve trips to flea markets and antique shops, and now you know exactly what I look for.

If you’re curious to see what more of these cards look like, the university archives has a website showing cards from different years.

Railroad Photography

I’m guessing you’ve never heard of O. Winston Link. He was a civil engineer by degree and a photographer by trade, who in 1955 undertook a personal side project which would end up becoming his life’s work. The Norfolk and Western Railway was the last major railroad in the United States to still use steam locomotives at that point, and Link obtained full permission from their president to photograph the last days of the steam era on their railroad, primarily in Virginia and West Virginia coal country. He was even given a key to the railroad’s switch boxes, allowing him to phone dispatchers to obtain exact arrival and departure times for trains and, occasionally, the ability to request a train be held so a photo could be taken at a particular moment.

Hawksbill Creek Swimming Hole. Luray, VA. August 9, 1956.

Link’s photos were not ad-hoc snapshots in any way. The majority of them were taken at night (making him one of the first to popularize nighttime photography), and illuminated by enormous flash arrays he built himself. Link said that not only did nighttime photos romanticize the trains further, but steam locomotive smoke appears white, and therefore not dirty, when lit by flash. Utilizing his background in advertising and marketing photography, his photos were carefully composed to tell a story, and almost always incorporate people to add a humanizing element.

I wish I could tell you I collect his photos, but that’s not possible. I can only afford to be an enthusiast. Original prints typically sell for thousands of dollars at auction. I do own all of his books, which are absolutely beautiful. The tales he tells in black and white are richer than anything I could ever imagine in color.

Link lived in relative obscurity, his photos were popular with railfans but didn’t start to gain mainstream popularity until decades later. You won’t find many photos of him – he preferred to stay behind the camera, but he did have a cameo appearance in one of my all-time favorite movies, October Sky. If you’ve never seen that movie, add it to your list!

I like to say that if you watch October Sky, and you recognize the train engineer to be O. Winston Link, you’re really smart and observant. But if you’re a true rail nerd, you’ll also know that the locomotive he’s driving is a 2-8-2 “Mikado” #4501, which was actually owned by the Southern Railway and painted to be a Norfolk & Western engine just for the movie. The N&W never used 2-8-2 locomotives.

If you made it this far, thank you for reading about my hobbies. I promise to return to my regularly scheduled technobabble in short order.

This month we celebrate the 100th edition of T-SQL Tuesday, originally created by Adam Machanic back in November 2009. Adam had asked us to write about what topics we might be covering 100 months into the future. I’m not good at predicting the future, but in the spirit of time travel, I am going one month into the past and writing about last month’s topic which was hosted by Aaron Bertrand.

Mar 072018
 

If you’ve done any amount of work in Linux from Windows, chances are pretty good you’ve used PuTTY at some point. PuTTY is a free and open-source terminal emulator that supports a variety of protocols, including SSH. I’ve been using it since college and have always been very happy with it. It’s free, it’s tiny, and it just works.

With the advent of SQL Server 2017 on Linux I find myself using it rather frequently once again, but this time around I’m not just using PuTTY for myself. Now I’m trying to take screenshots of it for slide decks, and doing demos in presentations. In a world of flashy graphics and high-resolution screenshots, PuTTY’s simplicity can become a problem. Remember, it’s a terminal emulator. Its job is to display text. By default it’s 80 columns wide* and 24 rows high.

A PuTTY window doesn’t take up a whole lot of screen real estate at this size, especially for modern HD monitors.This is great for everyday use, and terrible for teaching others. Screenshots end up tiny, and if you scale the image they tend to look horrible and pixelated.

A default size PuTTY window.

It might look great on your screen right now, but being projected on a wall this becomes a problem.

Fortunately PuTTY has an option to fix this, which has evaded me for the better part of the 18 years I’ve been using it. It’s not quite as flashy as “Presenter Mode” that SQL Server Management Studio released a few years back, but it is incredibly helpful. On the configuration screen, click the “Window” category and select the radio option to “Change the size of the font.” That’s all there is to it.

Select the “Window” category

 

Set the font size to change when the window is resized

 

Now connect to your server of choice and resize the window as you see fit. Instead of the window getting larger and the text staying small, the text will now grow with the window. Screenshots can be much clearer, and on-screen demos are delightful!

(click to view full size)

 

*Why do terminal windows tend to be 80 columns wide by default, anyway? Probably because the IBM punched card format, introduced in 1928, used cards that had 80 columns. When it came time to ditch the cards and edit them on a screen instead, 80 columns seemed like a good enough size to stick with.

Feb 282018
 

Months are funny. Unlike other parts of a date, they vary in length:

  • The last second of a minute is always 59.
  • The last minute of a hour is always 59.
  • The last hour of a day is always 23.

But the last day of a month? Well that depends on what month it is. And the year matters too because a leap year means February gets an extra day.

This used to be somewhat tricky to calculate, but then for SQL Server 2012 Microsoft added the EOMONTH() function (End Of MONTH) to T-SQL to help with this. EOMONTH() takes a DATE or DATETIME, and it will return the DATE (with no time component) of the last day of that month. Here’s a basic example:

SELECT EOMONTH('20180219');

 

It also accounts for leap years. Let’s try a date in February 2016:

SELECT EOMONTH('20160224');

 

What about other months?

In the case that you need the last day of a different month than the current one, you can make use of EOMONTH()‘s second parameter. This allows you to add (or subtract) months from the date you specified:

-- Second parameter adds that number of months to the date
SELECT EOMONTH('20180220') AS CurrentMonthEnd,
       EOMONTH('20180220', -1) AS PreviousMonthEnd,
       EOMONTH('20180220', 1) AS NextMonthEnd;

 

What if I need the first day of the month?

There’s no similar function to calculate the first day of the month, but think about it: the first day of the month is very predictable. It’s always day 1. You can also use EOMONTH() to find the first day of a month by adding one day to the last day of the previous month:

SELECT DATEADD(DAY, 1, EOMONTH('20180220', -1)) AS FirstDayOfMonth;

 

What if I don’t have SQL Server 2012?

If you’re still running SQL Server 2008R2 or older, my first piece of advice is to please look into upgrading to something more current soon. As of this writing, SQL Server 2008R2 Service Pack 3 is only supported until 9 July 2019. If you’re not running Service Pack 3, you are already unsupported.

Without the EOMONTH() function, calculating the end of the month gets a bit more complex. There are several ways to do it in T-SQL, but this is my preferred method:

SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS DATE)

This works by utilizing some creative math with dates. I’ve highlighted the different sections of the statement to explain what’s happening:

What if I don’t have a computer?

I’ve got a solution for this one too! Ever heard of the knuckle trick?

Make two fists and put them next to each other so your knuckles are lined up. Each high and low point along your knuckles represents a month, and the high points represent months with 31 days. Low points are months with less than 31 days (28/29 for February, 30 for the rest).

Source: Wikipedia

Feb 152018
 

I am extremely fortunate to have been named a Friend of Redgate for a fifth year!

If you’re not familiar with the Friends of Redgate program, we work with the Redgate¬†development and product teams to provide feedback on existing products, new tools, and feature enhancements. You can also find us doing things like speaking at events, writing articles on Redgate tools, and participating on the Redgate forums.

Redgate is a wonderful organization to be associated with. Not only do they make great tools and publish some very helpful books, but they’re also extremely community-oriented. They sponsor many functions such as user group meetings and SQL Saturdays, and also put on their own events, such as SQL in the City Streamed. These events used to be on-premises, but now they’re in the cloud and you can attend from anywhere! The next SQL in the City Streamed is taking place on Wednesday, 28 February. If that sounds awesome to you, you can find out more here!

Thank you so much, Redgate. I’m looking forward to another great year!

Feb 132018
 

SQL Server Mirroring has had a rough time the past few years. It was deprecated in SQL Server 2016, and now it’s more or less being replaced by Basic Availability Groups, (which I love to refer to as “BAGS”). Database Mirroring hasn’t gone the way of the Dodo yet though. Perhaps it won’t be quite so popular in new deployments anymore, but there’s plenty of existing ones out there. For this reason I don’t expect Microsoft to remove the feature for at least a few more years.

A few weeks ago I had a reminder of one of the finer points of the requirements for mirroring: mirrored servers need to be running not only the same version of SQL Server, but the same edition as well.

I hadn’t thought of this in a while, but it makes sense. Asynchronous Database Mirroring (also known as “High-Performance Mode”) is only available in Enterprise Edition, while Standard Edition only supports “High Safety Mode”, which is synchronous. If the primary server in a mirroring topology was Enterprise Edition, but the mirror server was Standard Edition, how would that work? What if the Enterprise Edition server had features enabled that don’t exist in Standard Edition? One way might be to disable features where conflicts occur, but that would probably lead to more issues as well as confusion. The simple solution is “just don’t let that happen” and that is what Microsoft opted for here. If you try to configure mirroring between instances running different editions of SQL Server, you won’t get very far before you see this:

Microsoft makes no secret of this in their documentation. The second listed prerequisite is “The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.”

So to sum things up, you may never have to touch a mirrored database again, but if you do, remember that editions matter!

UPDATE: While editions must match, it should be noted that versions of SQL Server need not match, and this exception can be particularly useful for using mirroring to perform upgrades, as mentioned in this blog post by Glenn Berry. However once you failover a mirroring session to a newer version of SQL server, failback to the older version is no longer possible. This is perhaps why Microsoft states in their documentation that versions must match – they will need to for any kind of long-term arrangement.