The Current Situation

This week marks the end of the third week of isolation at home for me. As our technical work is primarily remote anyway we began this practice 1 week before the UK was put into lockdown. Now that situation is government mandated that means both myself and my colleagues are at home until UK.gov say so.

Not all people have the luxury of staying at home, getting paid and keeping out of harm’s way. I’d like to thank people in my local community for keeping me running during the lockdown: Haigh’s Farm Shop, the three local Co-Op stores in Mirfield, the local Hermes delivery driver and I’m sure many more to come. It’s far too easy to take people for granted and I hope that at least one positive outcome of this lockdown is that we learn to appreciate what these people do for the community.

In terms of work nothing’s really slowed down for me as of yet. I still have upcoming projects to plan, projects going live, live-running systems that need some technical assistance. Should that dry up the plan will be to do some level of self-training to top up our skills. I haven’t had the time to even think this through yet but I suppose brushing up on SQL Server, T-SQL and maybe even learn some Python for fun in my spare time.

It has certainly been interesting watching the sudden adoption of “WFH” (Working From Home) practices. There will of course be long-term ramifications of the COVID-19 pandemic and not every change inflicted will be positive. I’ve certainly not missed the mass-migration that is the 9-5 rush hour and hopefully new working practices can begin the long and arduous process of finally killing that off. Surely there will be many environmental, social, health and economic reasons for that.

Stay safe and keep observing lockdown.

This Week In SQL Server

Oddly enough nothing much to report on this week. It’s been mostly about planning, fixing and patching. Nothing wrong with that every so often.

Of note Cumulative Update 2 for SQL Server 2019 was posted this week with a notably long list of fixes. Not had any deployments of SQL Server 2019 so far but looking forward to doing so. The memory optimised TempDB feature will likely be extremely beneficial for the applications that I deploy and my clients will surely benefit greatly for it.

Where Did 2019 Go?!

It’s been a busy few (many) months at my consultancy job handling the influx of work that has come in. This is thanks to the retirement of Windows Server 2008 & 2008 R2 and also Windows 7. All of those releases were really solid ones (for Windows 7 perhaps the last great Microsoft desktop OS?) and even though they have been surpassed they were very capable OSes for their time.

In saying that it’s most definitely time to move away from them now that extended support has run out. A lot of my time recently has been an effort to get clients from the legacy OS based builds onto something newer. For some clients that means upgrades of the business applications as well as new server builds, for others patching their current applications before moving it to a new server and for the fortunate few just a patch to install. Things like this exemplify the reasons why it’s important in IT to maintain systems and plan for end of support. There is arguably more cost incurred by doing nothing then having to do essentially a project in order to end up with a supported set of OS and applications versus constant planned maintenance.

For the next few months we are concentrating on implementing a new version of the business intelligence tool we resell. It has moved from a direct database connected Excel add-in to one that communicates to a server over HTTP/S. There are many pros and cons to this change and we continue to explore these as we implement with our clients. This will surely keep us busy for a while.

This week in SQL Server largely was the release of Cumulative Update 19 for SQL Server 2017 which I am sure will find its way to a UAT or support server near me soon.

SQL Server Updates

Updating SQL Server is a topic that infrequently gets sent to the support desk which is surprising given the importance of the applications that depend on a SQL database.

The questions usually being:

Is it safe to apply a SQL Server update?
Are you responsible for the update or are we?
What SQL Server updates are certified/supported with our software?

As of SQL Server 2017 the service pack is no more and the cumulative update now has the same level of validation as what the service packs used to have. This simplifies the update process somewhat. Generally it is safe to install these CUs and Microsoft even encourages you to do so on a “proactive” basis.

Installing an update to SQL Server should always be done with a get-out plan in case something goes wrong during the install. That means a properly tested backup and recovery plan.

I have a default rule that when I approach a job and the client asks me to install SQL Server for them I always apply the latest available cumulative update for testing. After that point the client is handed the responsibility for maintenance of SQL Server including the updates.

Most clients I’ve worked on do not patch SQL Server however most have unknowingly applied a SQL update through some form of Windows update mechanism. It’s not always clear if this is WSUS managed or not.

In an ideal world there would be user-acceptance testing done prior to applying SQL Server updates but many businesses do not have the time or facilities to do so. Regardless of the testing done a cumulative update should not break an application and I would always expect the author to be working with the latest service pack or cumulative update during development and support.

In conclusion patching SQL server is something that should be done on a regular basis and an activity that IT administrators need to be confident about handling.

SQL Server 2019 Released

This week Microsoft released SQL Server 2019 RTM.

I’ve not had much time to play with it so far. The headline new feature of SQL Server 2019 appears to be support for Big Data clusters which is not something any of my clients have asked about. However I still think they’d benefit from the in-memory tempdb and some new performance tweaks so I’m keen to check these new features out and see for myself.

I did have issues upgrading from SQL Server 2017 on my desktop PC. I had a Developer edition instance that was previously upgraded from SQL Server 2016. The 2019 installer was failing on the Client Connectivity SDK. I ended up removing as much as I could but was unable to clear away the SQL Server 2016 Setup files. To resolve this I did an install of SQL Server 2016 again and uninstalled everything. This then cleared away the troublesome setup files and I was able to install SQL Server 2017 and SQL Server Management Studio 18.4. If anything this reinforces the best practice of not upgrading SQL Server instances but installing as new.

Some Things I Never Knew In SQL Server

Over the course of the last couple of months I’ve come across some things in SQL Server that I’ve never really thought about.

“..” implies the default schema for A user.

If you have a user in a database with say a default schema of “Production” writing this:

SELECT ProductCode, ProductName, ProductColour
FROM CompanyDB..Products

Actually implies the Production schema.

The TempDB can really cause problems if it’s not configured correctly.

More of a reminder this one. I’ve recently worked on a client who was having some performance issues with a reporting tool. When they ran extracts from a SQL database it wasn’t very fast. I tried it myself and agreed it wasn’t performing as well as it should.

The reporting tool is heavily driven by cursors but let’s not get started on those. I looked in Activity Monitor and noticed quite a lot of contention on the TempDB. A further investigation revealed that there was a total of 1 TempDB for a Octa-core server.

After adding a further 7 TempDB row and log files we found that the reporting tool ran much much quicker and the contention was gone.

You can alias without using “As”

This one probably isn’t “best practice” but you can write:

SELECT C.CustomerID, C.Name, C.Address1, C.City
FROM Customers AS C

As:

SELECT C.CustomerID, C.Name, C.Address1, C.City
FROM Customers C

I don’t like doing this because to me that’s breaking a general principle of keeping script readable plus the reason I’ve never known about this is because all the T-SQL books and resources I’ve read have NEVER mentioned this is correct syntax so they must also consider it a no-no.

You stand a good chance of recovery if your server doesn’t boot but you’ve still got the SQL data.

I recently helped a client that lost a SQL data warehouse server to a Windows update that somehow caused it to not boot. They managed to pull all the files off the volumes so we had all the user databases as well as master and msdb databases.

The client was on a tight budget so we raced against time to get the server going as quick as possible. I was surprised to see that simply dropping in the master and msdb databases over the ones installed on the new server (shut the SQL Server instance down first) and putting the user databases at the original location restored pretty much all settings and data.

Of course you’ll run into problems if there was some encryption in use and you’ll need to use the same SQL Server release but other than that it proved a seemingly OKish way to restore the client’s data warehouse quickly.

What Do SQL Server Consultants Use (Aside from SSMS)?

We’ve been really busy in the office recently doing many installations and upgrades for a whole host of cool customers. Not so much to post on SQL recently (learning Data Warehousing) so instead here’s the software championships…

LastPass – Like all peeps in the consultancy game I’ve got a zillion passwords to remember. Keeping them all in a secure vault I can get anywhere saves a massive amount of time and frustration. Give it a try on me.
Notepad++ –. Venerable text editor fluent in a million languages and talented in many life skills. Can confirm the Compare plugin is a must.
SumatraPDF – You wouldn’t read documentation with Adobe Reader now would you? This one is lightweight so it launches fast.
Microsoft OneNote – Searching emails for crucial information is so 00s. It’s also the leading cause of bleeding eyes and crushed souls. Be a hero: put your notes in a modern notebook (and search later!)
7-zip – Zip it. Ship it. Some bugs were fixed.

Other brands are available of course.

Failing 70-411: Administering Windows Server 2012/R2

On Friday 3rd November I failed Microsoft Exam 70-411: Administering Windows Server 2012 R2. First attempt: 580. 2nd attempt: 540. Absolutely gutted to have failed it a second time as I was a lot more confident this time around having revisited everything I struggled with on the first exam. Seems like there’s some wide gaps in my knowledge that I haven’t plugged.

Make no mistake: 70-411 is a difficult exam. It is a very broad test covering a variety of scenarios and tools.

It’s also come to my attention that Microsoft no longer allow you to substitute 70-463 (Implementing a Data Warehouse) with other exams such as 70-411 to contribute towards the SQL MCSA which means a pass would have been a hollow victory.

What’s Worked?

Course 20411D – Delivered by QA Ltd in Leeds. The instructor was very knowledgeable having real-world knowledge and exam experience. Notes taken around this proved useful.

Previous Job – lots of experience in managing Windows Server 2008. IT strategy would have seen the business go to Windows Server 2012 R2 and implement some of the new technologies introduced in the newer OS. The interest alone was inspiration to boot up some test VMs and make an effort to learn it.

Exam Ref 70-411 Administering Windows Server 2012 R2 Book – Compared to the SQL training kit books it’s a bit thin but it’s still covers enough of the key topics to be useful.

But What’s Not Worked?

MeasureUp practice exam – although this has been useful for 70-461 (Querying SQL Server 202/2014) and even more so for 70-462 (Administering SQL Server 2012/2014) this has not helped much for 70-411. I was getting 80% and above in the practice exam. In 70-461 and 70-462 once I started hitting 80% (which in MeasureUp terms is a pass) I felt confident I’d hit “the barrier”. The exam difficulty result wasn’t hugely off the experience in the practice exam.

With 70-411 I think that the MeasureUp exam is very far off. Some questions came through on the actual exam that covered topics I’d just not hit in the MeasureUp exam. I also noticed some terminology differences and obvious typos which given how much these exams cost is a bit disappointing.

Job Change – whilst I love my new job it did have an unfortunate side effect: I use Windows Server much less regularly. I therefore don’t have to continuing real-world experience building up. Whilst I do interact with Windows Server every day as a SQL technical consultant it doesn’t cover things like managing Active Directory or setting up Windows Server Update Services. I felt I was getting caught out by the more in-depth questions particularly where PowerShell administration is involved.

To summarise I think my approach had gaps due to real-world experience, dependency on practice exams and the choice of subbing exams out. My next steps towards netting that most coveted MCSA will be to examine Data Warehouse exam 70-463 and (most likely) make an attempt on that.

Please Consider the Environment Before Printing This Blog Post

Email thread gone out of control because someone spun it off? Long email trail forwarded from a colleague with the words “see below” and nothing else right at the top? A colleague asking for help because they have 100 folders, can’t find an email and can’t use search? I’ve even suffered reading through a LinkedIn article discussing best ways of signing off an email (apparently just use “Best”). Been there, done that, clicked the unsubscribe link at the bottom. The question really is not when can we replace email? more “how long is it actually going to take for us to make it happen?”.

Email really adds to people’s day. Email inboxes end up becoming a vastly over/under organized trove of information. Most of it pointless but some of it potentially treasure.

Office Communication – My office uses Skype a lot because we can work anywhere, share desktops, make it a video call. Otherwise we go and talk to each other (but not before asking if the other person is busy). There’s also the phone system your office has had around since the 80s but you need to work out how to dial it first.
Client Communication – Skype. Just do it. Video call people and watch their facial expressions as you let them down (gently). Phone them if you must but NEVER use email to arrange a call. It’s sad. So very sad.
Knowledge – Nobody should be using email as an authoritative store of information. Full stop. It was never built for such a purpose. Deploy a Wiki, use Evernote or OneNote, find yourself a document management solution or make a network share to store stuff if you have to. Watch mishaps & misinformation fall and knowledge be recorded forever (or at least until ransomware nabs it).
File Sharing – I get it. You need to send that critical file to colleagues before you send to the client. Or upload it to OneDrive and work on it together. Accept no print outs.
Credential Sharing – IF YOU DO THIS PREPARE TO BE BOARDED BY PIRATES. Phone exchange works best but some SIP systems send traffic unsecured (!!!).

To summarise: you can make your life and others a lot easier just by ditching your 100 a day email habit. Let’s all get cracking before Inbox Zero makes a return.

Ransomware

Last week I was at a customer site when NotPetya hit. I was working in the company’s IT Ops room when news broke. All of a sudden people went from worrying about an AD user surname change to contemplating moving their patch schedule forward.

Toward the weekend I tried and failed to help another customer who had been hit by the ransomware. Somehow the application server had become infected. In the end there was no other option except to reinstall the application. Thankfully the databases were safe and the customer is back to running.

NotPetya targets vulnerabilities in the ancient SMB1 protocol. I recently disabled SMB1 on my desktop PC at home. Save for not seeing my NAS and Router appear as objects in Windows File Explorer there were no adverse affects. Microsoft have recently announced that future Windows 10 builds will not have SMB1 installed by default. The IT community should really be working on consigning SMB1 to the bin alongside SSL 3.0.

Speaking of which years ago when the Heartbleed vulnerability broke out I ran a test in production: I disabled SSL 3.0 without telling anyone that I’d done it. The only known site that broke was – very shockingly – a major UK bank partly owned by UK.gov. Why is there such inertia behind retiring old and broken protocols?