TSQL Tuesday #96: Folks Who Have Made a Difference

 

tsql2sday150x150 (1)

 

This post is part of TSQL2sday (or T-SQL Tuesday) which was started by Adam Machanic (t/b).  The topic for this month has been set by Ewald Cress‏ (t b) and is, to quote: “the opportunity to give a shout-out to people (well-known or otherwise) who have made a meaningful contribution to your life in the world of data.”

I suppose there’s no better place to start than with Adam Machanic himself, who’s T-SQL Tuesday idea has been one of my move valued resources as I’ve been learning SQL! Thanks Adam!

Context

I’m going to use this post to thank people who have contributed to getting me where I am now in my career.  For some context, before 19th August 2016 my sum knowledge of SQL had been a few modules during my Masters Degree at university back in 2006 / 7.  About all I would have been able to accomplish would have been SELECT *FROM Table at a push. Now, as I type this, whilst still admittedly very junior in the SQL world, over 50% of my main duties everyday revolve around SQL in some way or another.

Thank Yous

I want to start with one particularly large thank you. Bear with me, it’s a  bit of a long one!

At the end of July 2016, the Head of IT where I work had left the company.  I was going to be around 3-4 months until he was being replaced, which left the IT department to just me and 1 other member of staff. My infinitesimal SQL knowledge somehow left me as the expert in the field!  Little worry though, little of the work carried out by our department involved any SQL knowledge. I work in the Legal Sector and whilst our main Case Management System runs on a SQL database, we have full support from the vendors for this.

Fast forward about 6 weeks, it’s the morning of 19th August 2016 and I arrive in to work at 07:00 to find that the aforementioned Case Management System has completely and utterly fallen over.  No one can access the system at all, nothing, luckily it’s early morning but if I can’t this fixed, work will utterly grind to a halt!!! All I can figure out is that we have somehow completely run out of disk space.  After a bit of digging, I discover that, for reasons I don’t need to get into, adding more disk space to our SQL Server is not an option, and on top of that something called the Transaction Log is full, and is apparently the cause of all my problems! In absolute desparation I send out this tweet and pray :

Notice the times tamp, it’s just after 08:00 at this point, and the support company we have do not begin accepting calls until 09:00. Like I said, I was praying! Thankfully, I received a reply from Ryan Yates (t) who drops a mention to Thomas Rushton (t/b). Thomas basically spends the morning letting me message him back and forth, explaining what my problem may be, how to find out what has gone on, and once our 3rd part support finally kicks in, translates for me what they are telling me and advises me on a whole heap of things to read up on.  So, for my 1st thank yous, Ryan, thank you for replying to me that day, if you hadn’t pointed me towards Thomas I would most likely have been a crying quivering mess under my desk! And Thomas, I’ve wanted to say this for a while, thank you!!! Not just for metaphorically holding my hand over Twitter that morning, but our conversations inspired me to learn about SQL, and now, just over a year and a bit later, it has become the thing I am most focused on in my career and development and I owe it entirely to you.  I honestly would not be where I am today if I hadn’t spoken to you that morning. Thank you.

More Thank Yous:

Now that the giant one is out of the way, there’s a few more people I’d like to thank. I’ve never met a single one of these people in “real life” and I wouldn’t be surprised (or offended) if the vast majority not only didn’t realise that they have helped me, but probably couldn’t pick me out in a line-up! I can honestly say though, each one of these people is owed some sort of thank you for helping me get to where I now am:

Itzik Ben-Gan (t/b) : I have bought 3 of your books and each one has proved a truly valuable resource on my journey to learning SQL.  I am currently working through the MCSA course and your training kits and fundamental books are by far my best resource. Thank you.

Tao Klerks – PoorSQL (t/w) : The PoorSQL website (and latterly the SSMS plugin) have been immensely helpful when trying to reverse engineer existing SQL reports that are in such terrible formatting that they are a nightmare to read. This simple tool has saved me countless headaches! Thank you.

Shane O’Neill (t/b) : Shane ends up answering a hell of a lot of my questions I come up with on twitter, even joined Reddit to reply to a few on the SQL subreddit when I’ve posted some long form questions there. His advice has been unbelievably helpful, and it was him that introduced me to #TSQL2sday! He also doesn’t judge me too badly when occasionally the problems I have are ridiculous typos / logic oversights on my side! Thank you.

Assorted Other Thank Yous:

Chrissy LeMaire (t/b), Rob Sewell (t/b), Ben Weissman(t/w), Pat Phelan (t/w), Koen Verbeek (t/b), Todd James (t), Taiob Ali (t/b), Derik Hammer (t/b), Andre Kamman (t), Thomas LaRock (t/b), Kenneth Fisher (t/b), Argenis Fernandez (t/b), Allen McGuire(t/b), Mladen Prajdic (t), Jason Kyle (t), Kerry Tyler (t/b), Neil Gelder (t/b), Chris Taylor (t), Constantine Kokkinos (t/b), Andrew Pruski (t/b), Pinal Dave (t/b) and probably more I don’t remember.  Each one of these people have either answered questions online, provided links to valuable resources, or in some way have just inspired me to keep going when learning gets tough.  Thank you all!

Lastly, thanks to Randolph West (t) for encouraging me to get this blog posted today when I was whinging on twitter this morning that I completely spaced on the date and forgot to write it!

#TSQL2sday – Recovering Orphaned Files with Powershell and SQL

Being relatively new to SQL, this’ll be my first post for TSQL2sday.  For those not in the know, TSQL2sday (or T-SQL Tuesday) was started by Adam Machanic (t/b) and, I quote from this month’s host Rob Sewell (t/b), is :

“…a chance for you to join in the SQL Server community and write a blog post on a suggested topic. It makes for a great way to find a bunch of blog posts showing the same subject from many different viewpoints”

This month’s topic (if you haven’t already guessed from the title) is Powershell.

Now, Powershell is my 1st coding love. I’m not brilliant at it, but I’m definitely more than a beginner.  It’s what I spend most of my working life playing with, and it was the reason I eventually got in to trying to learn me some SQL.  Baring all this in mind, this was the first month that I’ve felt confident enough to join in, so here goes!

Creating a Scheduled Powershell Task to Clean up Orphaned Files in our SQL Database.

Catchy title, right?  Basically, what I have been playing with this month is using a combination of SQL and Powershell to help with a small mess that we have recently found ourselves in.  For this to make sense, I guess it’s going to need some sort of context.  I’ll try and be brief.

Context

I work at a relatively small but successful law firm, and our practice pretty much lives and dies with our Case Management System (CMS).  if you don’t know what that is then, suffice to say, it’s like an IT Service Desk tool on steroids, lots of steroids.  This software stores all documents, letters, email and anything else that could possibly be associated with a legal case, and organises them all in some sort of logical fashion. Suffice to say we have literally millions of these files.  One of the main ways that these files are imported into the CMS is by an Outlook plugin that files the email and / or attachments.  The users is prompted to enter where they want to file them, and off they go.

We also use folder redirection for our users, so that their files are on a server rather than on their local computers.  It works pretty well, has some issues here or there, but it’s OK. Folder Redirection isn’t the star of this show.

Over the last couple of months, there have been a couple of small issues with the server that houses the folder redirection data.  This has seemingly had a knock on effect where emails filed into the CMS during a folder redirection blip are seemingly lost.

What’s the Problem?

Well, each file in the CMS has a database entry that points to the files location on a server somewhere.  During a Folder Redirection blip, if you were to simply look at the CMS, it would appear that the files have been imported correctly. However, when trying to open them, you are greeted with “file can not be found”.  What seems to have happened is that the file path has only the file name in it, not the actually crucial path part.  After a lot of digging, these files (mostly) seem to have ended up in the root directory of the Folder Redirection share.

The first couple of times this happened, it took us ages to manually figure out where these were supposed to be filed, drag and drop the files there, and then manually updating the paths.  The first time, this was irritating. The second time, infuriating. The third time lead me to this post, I wanted to never have to do this again!

There are a few things to note about this issue. Firstly, not all files I find in the database have been located in the Folder Redirection share root. Secondly, not all files in the Folder Redirection share root belong to Database entries with missing paths.  Lastly, all files are associated with a legal case, these take the form of an entity (or “customer”) and a matter number.  An example of this would be Entity ABC123 Matter 6 or ABC123/6.  The files associated with this matter need to be stored in a path based on this, so for this matter the files would be stored in \\KSLFS01\ptrdata\docs\A\B\C\ABC123\6.  To get each file going into the correct folder is something I needed to figure out.

The solution

This is my wonderful solution.  Normally I’d use the code formatting of WordPress, but because its a Powersehll script with SQL code in there, it just didn’t format well, so excuse the picture :

code
My wonderful solution!

This solution is essentially doing 3 separate functions combined together:

  1. Query the database to find all files in the database that meet my missing path criteria.
  2. Check the content of the Folder Redirection share root folder and check to see if the files are in there.
  3. If the files from the 1st step are found in the folder from the 2nd step, migrate the file to the correct location and update it’s path in the database.

SQL was obviously used to find the relevant files and then update the the file path when required.  CONCAT and SUBSTRING were used to form the correct file path, and are things that I had not used before this,

What I like about this solution is that it is now something that can be scheduled weekly on the SQL server, and hopefully means that this problem should never really bother us again.

Now, neither the Powershell code, nor the SQL code, are all that complex here. This is the first time I’ve really tried to combine the two into some useful function.  The Powershell itself is pretty straight forward but the SQL did take me a bit of research / trial and error to get right (the evolution can be found in this Github Gist, and this Twitter thread).

Outro

Whether what I have come up with is the best way to resolve this issue or not, is not important here.  For me, this solution to me represents progress.  For the last 10 months I have been teaching myself SQL, and I’ve been trying to learn Powershell for a good 4 years (but that last 2 seriously).  This is the first time I am really seeing the fruits of my labour.  The combination of using SQL and Powershell together made what would have been hours of tedious work into a relatively quick and painless process.

Going forward, I can see how combining SQL and Powershell could really open the doors to more solutions for problems I already have, problems I don’t know exist yet, or just improving some of  my already existing solutions.  I want to start using both of these languages to the full, and have already begun looking at resources, such as DBA Tools, (t/w) a great Powershell module for SQL admins, and have started reading some books by Itzik Ben-Gan (t/b), and working towards my MCSA.  I’m really hoping that this is the beginning of something special!

1vo3u1