In Recovery...

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I was asked last week whether it’s possible to create a table with a FILESTREAM column and then populate that column by copying FILESTREAM files from another directory in the FILESTREAM data container.

The simple answer is no.

In the data container, each table with a FILESTREAM column (or partition of a partitioned table with a FILESTREAM column) has a directory with a GUID name, and then there’s a sub-directory for each FILESTREAM column that also have GUID names. Then the FILESTREAM files themselves have filenames in the format 8hexdigits-8hexdigits-4hexdigits. The hex digits are the Log Sequence Number (LSN) of the log record that created the FILESTREAM file, and the data record contains that LSN as part of the in-row portion of the FILESTREAM column. (You can read a full explanation of the storage format in my post FILESTREAM directory structure.)

So, simply copying the files from one directory to another wouldn’t work for two reasons:

The FILESTREAM column in the data record of the target table wouldn’t be populated

And even if you created empty FILESTREAM columns, the LSN in the column wouldn’t match the LSN of the copied-in files

And you can’t use SELECT-INTO either, because it will not create FILESTREAM files and instead copies them and stores them in the new table as varbinary(max) with a limit of 2GB.

Bottom line: You can use INSERT INTO <target table> SELECT * FROM <source table> and that *will* populate the FILESTREAM files, but that means reading the FILESTREAM data through SQL Server’s buffer pool, which may be very inefficient for large amounts of data. So your best bet may be to write a little application that will using NTFS streaming access to directly copy the FILESTREAM data from the source table to the target table.

Azure Data Studio, which used to be called SQL Operations Studio, has a bunch of extensions available, including one called Server Reports from Microsoft. Last year they took my wait stats query (from here) and made it into a report as part of Server Reports extension. In this quick post I’ll show you how to install that extension and look at the report.

I’ve been using Azure Data Studio a lot as part of working with SQL Server 2017 on Linux in Azure and I like it a lot. It’s obviously not as comprehensive as SSMS, but for simple stuff it does the job.

You can see the pretty big list of available extensions by hitting the Extensions icon:

In the list that appears, find the Server Reports extension and click the Install button:

Once an extension is installed, you need to activate it. Without reading any documentation, the first time I used Azure Data Studio I couldn’t figure out why the extension wasn’t there once I’d installed it. Click the Reload button to active the extension:

The extension will now show up in the list of enabled extensions:

If the Server Dashboard screen isn’t showing, right-click on one of the servers you’re connected to and select Manage. All the active extensions will show as tabs at the top of the screen, so select Server Reports and then click the heart-shaped icon and you’ll see the current aggregate wait stats for the instance:

Note that the x-axis is percentage of all waits, not wait count. You’ll see that PREEMPTIVE_OS_FLUSHFILEBUFFERS is the top wait on my Linux instance – that’s by design and I’ll blog about that next. I’ve also submitted a GitHub change to add that wait to the list of waits filtered out by script the extension uses.

Anyway, you can drill in to the details by clicking the ellipsis at the top-right of the graph and selecting ‘Show Details’. That’ll give all the waits and by selecting each one you can see the usual output from my waits script. To get more information on what each wait means, select the bottom cell, right-click on the URL to copy it, and paste into your favorite browser to go to my waits library. And of course, you can refresh the results via the ellipsis as well.

There are many more useful extensions that you should check out too – enjoy!

I’m working with Red Hat Enterprise Linux (RHEL) VMs on Azure and one of the best practices that Microsoft recommends is to change the default SSH port from 22 to something else. I was looking for a consolidated set of instructions to do this and I couldn’t find one, so I thought I’d do a quick post on it.

The steps that need to be performed are:

Allow the new port in the RHEL firewall

Change the SSH daemon to listen on the new port

Add an incoming rule in the VM network security group for the new port

Remove the rule that allows port 22

Changing the SSH port has to be done after creating the VM, as the SSH daemon is set up to listen on port 22 by default, so as part of the VM configuration, make sure to allow port 22 to be opened. Once the VM is created and running, connect to it using your favorite SSH client.

To change the SSH daemon to use port 52019 we need to change it’s configuration and restart it. Edit the config file using:

sudo vi /etc/ssh/sshd_config

Now vi is not very user friendly, but it’s easy once you learn it. Use the down arrow so your cursor is over the # at the start of the line that says:

#Port 22

Hit your Insert key until the word REPLACE shows at the bottom of the SSH window. Then type:

Port 52019

Now hit Esc then :w and then Esc then :q to write the file and exit.

To restart the SSH daemon:

sudo service sshd restart

Now go into the Azure Portal and for that VM:

Click Networking in the Settings pane and click the blue ‘Add inbound port rule’ button on the right

In the wizard that comes up, change the ‘Destination port ranges’ to 52019 and the ‘Name’ to something like ‘IncomingSSH’

Click the blue ‘Add’ button

Once the rule has been added, you should be able to create a new SSH connection to the VM using port 52019. Once you’ve done that, drop the initial SSH connection that’s still using port 22 by going back to the Networking tab in the Azure Portal, finding the existing rule for port 22, click the ‘…’ on the right and selecting Delete.

… I emigrated to the United States. I boarded a plane in Glasgow with my (then) wife Sylvia, bounced in Amsterdam and then landed in Seattle, entering the US on an H1B visa after almost five years of working for DEC/Digital. We arrived in temp housing a block away from the Microsoft Campus late in the evening, and I started work in Building 1 with the SQL Server team on Monday, February 1st 1999. My first task was making the integer conversion code in BULK INSERT and bcp go faster. Microsoft was nice enough to pay for my Green Card, making us Permanent Residents on Valentine’s Day 2002, and I naturalized as a U.S. citizen on April 3, 2012.

Fast forward 20 years from 1999 and here I am today running SQLskills with Kimberly (left Microsoft on August 31, 2007), with an eldest daughter studying at U.C. Berkeley to be a trauma surgeon and the other daughter a junior in high school. (Sylvia is still here too, in case you were wondering, naturalized, remarried, and we’re good friends – the girls are lucky to have her as their mother.)

It’s been a pretty wild ride over the last twenty years, but I wouldn’t live anywhere else in the world now.

Thank you to Microsoft for bringing me here, and thank you to the United States for accepting me as a citizen.

1: the number of indispensable assistants, without whom our lives would be a distressing quagmire – Libby we love you!

Finally, the one and only best person in my life: Kimberly, without whom I would be lost…

Thank you to everyone who reads our blogs, follows us on Twitter, sends us questions, watches our videos, comes to our classes, and generally makes being deeply involved in the SQL community a joy.

I sincerely wish you all a happy, healthy, and prosperous New Year!

Cheers!

(February 3, at the old Equator museum a little south of Quito, Ecuador)

(July 13; No prizes for guessing where we were…)

(August 23, at Cook’s Chasm on the Oregon coast while driving Katelyn’s car home from Berkeley – she road-tripped down with a friend)

(August 2, Kiera and I are the founding members of The Society for the Underwater Reenactment of Matrix Fight Scenes. Here we’re at around 50 feet, in NW Papua, Indonesia, acting out the fight between Seraph and Neo in The Matrix Reloaded. We had a hard time getting the jar of pencils to stay put… Kimberly thought we were crazy, and Katelyn simply avoided us )

Back in 2009 I started posting a summary at the end of the year of what I read during the year (see my posts from 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017) and people have been enjoying it, so here I present the 2018 end-of-year post. I set a moderate goal of 50 books this year but obliterated that and broke my 2009 record of 100 books by reading 101. It was a really enjoyable reading year as I finally read a large number of classic fiction books that have been on my list for many years. Next year I’m setting myself a goal of reading 50 books again as I have a lot of lengthy biographies and other history books I want to tackle.

For the record, I mostly read ‘real’ books – i.e. not in electronic form – I really don’t like reading off a screen. Yes, I’ve seen electronic readers – we both have iPads – but I don’t like reading electronically. Having said that, I did read about 40 books electronically this year out of necessity (insurmountable luggage weight and volume restrictions on two of our trips). I also don’t ‘speed read’ – I read quickly and make lots of time for reading.

Why do I track metrics? Because I like doing it, and being able to compare against previous years. Some people don’t understand the logic in that – each to their own :-)

Last night I looked through the list of books I’d read to pick my top ones, and although I couldn’t pick a single title as the top one, there was really no contest for my favorite two books of the year: The Lord of the Rings by J.R.R. Tolkien and The Hitchhiker’s Guide to the Galaxy by Douglas Adams. I’ve read LOTR 4 times now and I don’t know how many times I’ve read the Hitch-Hikers series. If you’ve never read them, I strongly recommend you give them a try. You can read my review of them in the top-10 list below.

Now the details. I enjoy putting this together as it will also serve as a record for me many years from now. I hope you get inspired to try some of these books – push yourself with new authors and very often you’ll be surprisingly pleased. Don’t forget to check out the previous year’s blog posts for more inspiration too.

As usual I leave you with a quote that describes a big part of my psychological make-up:

Analysis of What I Read

I read 27,480 pages, which is an average of 75.29 pages a day. The chart below shows the number of pages (y-axis) in each book I read (x-axis).

The average book length was 272 pages, which is a lot shorter than previous years and that’s because I read a lot of classics on my iPad while on trips. The obvious outlier in the chart is The Lord of the Rings.

I read a lot more fiction than in previous years.

The Top 10

I read a lot of excellent books this year and once again I couldn’t whittle it down to a top-10, so here is my top 12. If you don’t read much, at least consider looking at some of these in 2019. It’s impossible to put them into a priority order so I’ve listed them in the order I read them, along with the short Facebook review I wrote at the time.

#4; The Remains of the Day; Kazuo Ishiguro; 256pp; Fiction; January 15; (Wow – what a wonderful book! I picked this up as Ishiguro won the 2017 Nobel Prize for Literature, and this particular book of his won the Booker Prize in 1989. The book is a series of reminiscences by Mr Stevens, the perfect butler, of his time working for Lord Darlington between the two World Wars as he takes a week-long trip into the English countryside in the late 1950s. There are really three stories – Stevens himself and his misguided, blinkered trust in his master, Lord Darlington’s involvement in machinations with Germany, and Stevens’ relationship with the housekeeper, Miss Kenton. It’s beautifully written, and Ishiguro captures the essence of the anachronistic, stiff-upper lip butler to perfection. *Highly* recommended. I have no doubt this will be in my top-10 for 2018.)

#11; The Old Man And the Sea; Ernest Hemingway; 128pp; Fiction; February 4; (This is the first Hemingway book I’ve ever read, and I really enjoyed it. It’s about the truly epic struggle between a currently unlucky, 80+ year old Cuban fisherman and an enormous marlin he hooks way out in the sea. Hemingway’s writing is excellent and I found it compelling and evocative. Highly recommended!)

#31; The Hitchhiker’s Guide to the Galaxy; Douglas Adams; 160pp; Science Fiction; February 26; (I haven’t read the Hitch Hiker series since 1999, so thought it about time for a reread. The Earth is blown up to make way for a hyperspace bypass, but 5 minutes before the culmination of the computer program it was running, for the mice who owned the planet… Everyone should read these – wonderful stuff! 42!)

#33; Foundation; Isaac Asimov; 320pp; Science Fiction; February 28; (Can’t believe I haven’t read the Foundation series until now! This is Asimov’s classic story of the Foundation; ostensibly created to preserve mankind’s knowledge once the Empire collapses, but actually the seed of the next Galactic Empire and the brainchild of Hari Seldon who can see the future through mathematically extrapolating from the present. Well written and gripping – highly recommended for sci-fi fans!)

#34; Dune; Frank Herbert; 544pp; Science Fiction; March 1; (I also can’t believe I haven’t read the Dune series until now, as I love the movie. The first book sees House Atreides take over the desert planet Arrakis from House Harkonnen and then lose it amidst treachery and murder. Paul Atreides then becomes the leader of the native Fremen people and becomes the legend they’ve been waiting for, plus all the drama with the giant sand worms and the spice. Well written and gripping – highly recommended for sci-fi fans!)

#45; The Handmaid’s Tale; Margaret Atwood; 325pp; Fiction; April 2; (Quite a disturbing book about the Republic of Gilead (in what was the USA) where most women are subjugated, men and women are strenuously segregated, and women are not permitted to read. The focus of the book is the Handmaid Offred (Belonging to Fred), whose job is to have monthly, clinical sex with the man whose house she belongs to so that him and his (barren) wife can have a child, although she can remember how society was before. It is a time of dangerously low birth rates due to widespread anti-fertility disease and pollution, and the Gileadean society was the result of a revolution in US. Very well written and interesting to read. Highly recommended.)

#50; Facts and Fears: Hard Truths from a Life in Intelligence; James R. Clapper; 432pp; Nonfiction; July 7; (Clapper was the Director of National Intelligence from 2010 to 2017, and is a retired Air Force lieutenant-general who spent his entire career in intelligence. The book is excellent. The first 100 or so pages details Clapper’s military career and then it gets into his experiences with and views on a variety of well-known episodes such as Benghazi, Snowden, and the Abbottabad raid that took out Osama bin Laden. The final 100 pages are about the 2016 election, and specifically the Russian interference in the election. Clapper’s views are balanced, professional, and very insightful, as one would expect from someone who was an integral part of (and eventual leader of) the U.S. intelligence community for 55 years, and he writes with a sense of humor too. He holds some of the media, some of Congress, and the current president in disdain for their willful disregard of facts, promulgation of falsehoods, and unwillingness to understand the capabilities and limitations of intelligence gathering and analysis, and considers a variety of moral and ethical questions on facets of intelligence. I found the book hugely interesting and it was obvious that Clapper wanted to write the straight up, unpartisan, and unembellished truth of what the intelligence services did and knew about all these topics, including mistakes that he and they made. Highly recommended! PS In the past when I’ve read and reviewed a book that some people disagree with, I’ve been accused of pushing a political agenda. I find that accusation highly distasteful. I read what I read because I’m interested in a wide variety of subjects and viewpoints, and I present reviews here of everything I read.)

#65; Things Fall Apart; Chinua Achebe; 209pp; Fiction; July 26; (Excellent book! It follows the life of a clansman from the lower Niger river area in the early to mid-1800s. The first part of the book illustrates how the clan hangs together, its customers and rituals and deities. Then in the second part white men come as missionaries and then as oppressors, which gradually breaks the clan system apart and destroys the old way of life. Which is exactly what happened for much of Africa under colonial rule. Highly recommended!)

#75; Dracula; Bram Stoker; 336pp; Fiction; August 8; (Excellent book! I love the Francis Ford Coppola movie (and I know many don’t) so I know the story well, but I’ve never read the original book until now. The classic vampire story is quite a page-turner, and written from the perspective of several of the protagonists through the medium of their diaries. Having the movie in my head helped me visualize a lot of the scenes from the book and I’m happy to say that although Coppola and the screenwriter(s) used a lot of artistic license, they did a good job of staying faithful to the story. Highly recommended for any fans of Gothic horror, and anyone who hasn’t read it.)

#83; The Secret History; Donna Tartt; 576pp; Fiction; September 7; (What an excellent book! It’s about a small group of students at an elite New England college who learn ancient Greek under the tutelage of an eccentric professor. They’re very close-knit and descend into moral bankruptcy when they commit a ritual murder. From that point on the group gradually fractures and they all become psychologically disturbed, with shocking results (without giving away too much). It’s extremely well-written and very engaging – I literally couldn’t put it down during the second half. Highly recommended!)

#90; The Lord of the Rings; J.R.R. Tolkien; 1184pp; Fantasy Fiction; November 13 (The link is the paperback edition, but I have a magnificent leather-bound edition that cost quite lot as this is one of my favorite books. Unless you were living in a cave in 2001-2003, you can’t have missed at least hearing about the three Lord of the Rings movies that dominated the cinema in each of those years, with the final one (The Return of the King) being one of only three movies ever to win 11 Academy Awards (alongside Titanic, and the 1959 Ben-Hur). I first read TLOTR when I was 11, read it again in college, and then again in 2000 while on parental leave from Microsoft after my first daughter was born, making this my fourth reading. After 18 years, I’d forgotten how rich the storytelling is, and how many things were left out of the movies. The story is very complicated, but can be boiled down to: an evil ring must be taken into the heart of the most dangerous place (Mordor) in the land (Middle Earth) so it can be destroyed, and the task falls to a hobbit, about the most unlikely of all the good races in Middle Earth (men, dwarves, elves, hobbits, and so on). All kinds of side stories happen, leading up to the final battles. You don’t *have* to read The Hobbit first, but it certainly helps. This is an absolutely wonderful book, and I can’t recommend it enough! And the movies are just stunning, especially in their longest Director’s Cut editions – I’ve lost count of how many times I’ve watched them.)

#96; The Restless Wave: Good Times; Just Causes; Great Fights; and Other Appreciations; John McCain; 416pp; Nonfiction; December 16; (As I mentioned in my post here after he passed away, McCain was a true American patriot, a many-times decorated war hero, served our country for 60 years, and was also the son and grandson of 4-star U.S. Navy admirals. I bought this book the day before his death, as I wanted to read what he had to say about his political life in what was probably his final opportunity. It’s a very well-written (in part no doubt due to his partnership with his long-time friend, speech writer, and ghost writer Mark Salter) and frank memoir. Given his vociferous opposition to Trump, I was expecting an excoriating attack but when he did criticize, it was subtle and classy. He actually had about as much criticism for Obama (around foreign policy weakness and mistakes) and Bush (on the war in Iraq). McCain’s outright vitriol was all directed at Vladimir Putin, who he calls an “evil man”. No matter whether you agreed with McCain’s politics or not, I think his book is worth reading, especially as a plea for America to champion human rights and democracy across the world, and for an end to the gridlocked U.S. political system. I *highly* recommend it. The Amazon precis says everything I would, so here it is: “Written while confronting a mortal illness, McCain looks back with appreciation on his years in the Senate, his historic 2008 campaign for the presidency against Barack Obama, and his crusades on behalf of democracy and human rights in Eastern Europe and the Middle East. Always the fighter, McCain attacks the “spurious nationalism” and political polarization afflicting American policy. He makes an impassioned case for democratic internationalism and bi-partisanship. He tells stories of his most satisfying moments of public service, including his work with another giant of the Senate, Edward M. Kennedy. Senator McCain recalls his disagreements with several presidents, and minces no words in his objections to some of President Trump’s statements and policies. At the same time, he offers a positive vision of America that looks beyond the Trump presidency.”)

The Complete List

And the complete list, with links to Amazon so you can explore further. One thing to bear in mind, the dates I finished reading the book don’t mean that I started, for instance, book #2 after finishing book #1. I usually have anywhere from 10-15 books on the go at any one time so I can dip into whatever my mood is for that day. Some books I read start to finish without picking up another one and some books take me over a year. Lots of long airplane flights and boat trips help too!

No only has Tim been working heavily with Azure in 2018, he’s also been diving into the various reporting solutions that SQL Server has, and he’s ready to share his knowledge with the world in his new class on Power BI!

It’s a two-day class called IEPowerBI: Immersion Event on Power BI, Power BI Report Server, and SSRS and it’ll debut in April 2019 in Chicago, as part of our usual set of Spring classes.

Note: there’s a US$100 discount for registering before 1/15/19!

The modules are as follows:

Module 1: Administration and Infrastructure

Reporting ecosystem overview

Reporting architecture

How do you get started? (installation and configuration)

Administration (PBI RS)

Report scheduling and delivery

Scale-out / HADR

Office integration

Security

Performance monitoring

Module 2: Upgrade, Migration, and Licensing

Reporting Services

Power BI Report Server

Power BI

Best Practices

Preparing for the reporting certifications and continued training

Module 3: Power BI Report Visualizations

How do you visualize?

Data sources and data sets

Analysis Services 101

Power BI Desktop walkthrough

Mobile Report Publisher

Power BI Report Server

Module 4: Putting it all together: Building a report and using embedded reports

You can read a more detailed curriculum here and all the class registration and logistical details are here.

It’s Cyber Monday today, and now that you’ve finished picking up great deals for your personal life, how about a great deal for your professional life?

From now through 12pm PST on Thursday, November 28th 2018, the first 25 people to register using a credit card can pick up our 6-course combo (covering all six of our 2019 Q1 live, online classes) for a flat US$3,000. Six live classes for only US$3000! That’s a saving of US$299 off the combo price, and makes each class only US$500, compared to the US$699 full price.

With each class you get about 14 hours of the best live training spread over three days, plus access to the recorded class.

Check out the classes here. And when you’re ready to save a bunch of money, register here (pick the bottom Event from the drop-down menu).

Due to the popularity of our live, online classes this year, we’re presenting six more in the first three months of 2019! Each class will be delivered live via WebEx or GoToMeeting over three days (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

The price of each class is US$699 (or US$599 for prior attendees of any Immersion Event). You can also select the “Pick Any Three 2019 Q1 Classes” package for US$1,749 (which works out to US$583 per class) or the “All Six 2019 Q1 Classes” package for US$3,249 (which works out to $549 per class).

You can get all the details of these classes here, or jump right to the registration page here.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).

Continuing our series of live, online classes, Jonathan will be delivering his new IECS: Immersion Event on Columnstore Indexes in March! The class will be delivered live via WebEx or GoToMeeting on March 26-28 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

Here are some select quotes from prior attendees of Jonathan’s online classes:

“Extremely pleased with the course. FAR exceeded my expectations.”

“Well worth the time and expense to attend. Would highly recommend this to others.”

“Great course – very informative – very great instructors – I am sure to be back!”

“Great course. Good new info for me, plus refresher on other info. Thanks!”

“Both Erin and Jon have a vast knowledge of not only SQL Server & tools, but also effective presentation.”

“Thanks for taking the time to better my knowledge of SQL and allow me to better my career.”

“Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”

“Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”

The modules covered will be:

Columnstore Index Basics

Columnstore Usage Strategies

Dynamic Management Views

Data Loading Patterns

Columnstore Index Maintenance

Partitioned Tables

The price of the class is US$699 (or US$599 for prior attendees of any Immersion Event). You can also select this class as part of our “Pick Any 3 2019 Q1 Classes” package (which works out to $583 per class) and it’s included in the “All Six 2019 Q1 Classes” package (which works out to $549 per class).

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).