T-SQL Tuesday #110 – “Automate All the Things”

T_SQL_Tuesday

This month is being hosted by Garry Bargsley – thanks @gbargsley!

My response to this month’s topic is a personal one. The main two questions to answer are:

  1. What do you want to automate or what automation are you proud of completing?
  2. What is your go-to technology for automation?

This year my goal is to work on a double program, data science and artificial intelligence. I have chosen the Python path (or it chose me). So Python is becoming my go-to hammer in search of nails so I am all about, “Automate With Python All The Things”.

A book I am working through as I am learning Python is called, oddly enough, Automate the Boring Stuff With Python. LOTS of things I could automate that I had no idea about. Highly recommended.

Specifically, I am interested in Chapter 17, image manipulation.

Do You Take Pictures?

Please raise your hand if you take pictures. I see everyone’s hand went up. Yes, with digital cameras and smartphones, we take a plethora of pictures. Some would say too many! We don’t even take time to go back and look at all of them. Storage is so cheap we can be digital hoarders and not feel ashamed.

How Do You Backup Your Pictures?

Um, ya. About that. As data professionals we should know the importance of backups and having a restore strategy. Our backups should be local for immediate retrieval and off-site too. For personal use, backups should be off-site in some kind of cloud storage.

But how many of us regularly and with discipline and/or automation backup our pictures off of our smartphones, digital cameras, laptops and desktops? And is it a pure file-based backup or do you put pictures into a database?

Is Putting Pictures Into a Database Overkill?

Some would argue yes, that’s silly. Others would say no but how to do it? And what would the benefits be to storing images in a database?

Previously Done in R

I read this article a while back and worked through all of the examples. This was before I was bitten by Python. So I want to re-do all of this but do it in Python and store images in a database.

A Gift to My Wife

Chris and I have been married 25 years. As my gift to her, I am building an art gallery of images from the last 25 years. So far I am up to 300 images. Now, this isn’t a slide show. It is going to be a gallery built using Unreal Engine and deployed onto an Oculus Go.

But in order to properly size the images, they need to be a power of 2 as in 2048×2048. So! How do I fiddle with 300 images? Re-size, extract meta-data, store in a database, etc?

Enter Stage Right – Pillow and Scikit-image

Pillow (used in the book above) and scikit-image are both image processing packages.  Ever wonder how to count the number of marbles in a picture? Facial recognition software? These packages are amazing and they have a whole lot of really neat and cool capabilities. While some of the scientific stuff is over my head, I did pick up some things from this course- but I will need to re-take it again once I understand things better during the study of data science. Building Image Processing Applications Using scikit-image

SQL Saturday Topic

It shouldn’t be a surprise then that manipulating and storing images in a database is going to be my new SQL Saturday topic/theme for this year. It does sound like total overkill or using a cannon to shoot a fly. Some might say that is just good target practice. The technologies I am working with does sound like a lot and it is: Hekaton, FileTable, Python, Unreal Engine, and Virtual Reality for starters.

And before you ask, yes, I will be sharing all of my scripts and who knows, might even create my first community tool 🙂

 

Posted in Uncategorized | 1 Comment

2019 – The Year of Memory Recall

Nothing I create is ever perfect and I cannot wait any longer until it is- because that time will never come. Sometimes you just have to run with what you have.

So I’m going to focus on a single theme in my blog posts, presentations, and the like for 2019 – The Year of Memory Recall. All using SQL Server database(s) and technologies of course. And virtual reality. And Unreal Engine. And, and, and.

What follows is an incomplete rough draft of links and resources and comments. I had to post something in December…

NOTE!!! I am currently re-reading Total Recall and writing down even more notes, thoughts and updates since the book was published ten years ago when I first read it.

My goals and motivations will become clearer as time goes on. Memory recall is the act of recalling existing memories. The active act of doing so helps to strengthen and keep the memories alive. Memories are critical to shaping us as individuals and it is something we can control.

I am marching to the beat of a different drummer down the road less traveled. This journey will not be easy.

I am building a memory recall system which I’ll be using the rest of my life. If I live to be 100, I will be recalling memories I created in 2019 and earlier. A dual system- external memories and internal memories. Hopefully a system I can give to my kids- something tangible like my grandfather’s tool set and the memories I have of him wielding those tools building and fixing things. And versions of this system other people can use too for memory recall.

Some Sci-Fi devices (time to build a few?)

Star Trek
https://en.wikipedia.org/wiki/The_Lights_of_Zetar

Memory Alpha
https://en.wikipedia.org/wiki/Memory_Alpha

Uses a wiki encyclopedia. Created by fans, not by the creators of Star Trek.

Star Wars

Name of internal, real world tracking database (originally in FileMakerPro, one of my first database loves): Holocron; 55k entries. Clone Wars and Jedi Archives.

https://en.wikipedia.org/wiki/Star_Wars_expanded_to_other_media#Holocron_database_and_canonicity

http://starwars.wikia.com/wiki/Holocron

Jor-El; father of Superman; Fortress of Solitude; memory crystals
https://en.wikipedia.org/wiki/Fortress_of_Solitude

John Percival Hackworth; ~Nell; A Young Lady’s Illustrated Primer; nanotech; ractors; ractives
https://en.wikipedia.org/wiki/The_Diamond_Age

Aleph; Mona Lisa Overdrive
https://en.wikipedia.org/wiki/Mona_Lisa_Overdrive

Some Previous Real-World Attempts

Dymaxion Chronofile (analog scrapbook)
https://en.wikipedia.org/wiki/Dymaxion_Chronofile

Memex (analog but using electronics and hints of a digital version)
https://en.wikipedia.org/wiki/As_We_May_Think

Later Became Fully Digital
https://en.wikipedia.org/wiki/MyLifeBits

And used on the recommendation by the late computer scientist Jim Gray, “…you need to use a database like Microsoft SQL Server.”

Finally! Books!

Total Recall: How the E-Memory Revolution Will Change Everything (focuses on digital storage for EVERYTHING; of course, at a cost)
https://www.amazon.com/Total-Recall-Memory-Revolution-Everything/dp/0525951342

Going back to MyLifeBits- the problem and challenge I had was that MyLifeBits was an academic project. Absolutely nothing wrong with it, it is just that many academic projects do not easily lend themselves to be quickly implemented or have commercial applications. I have only seen screen shots of some ERD(s) and not an open source software or script library to pick up the pieces where the researcher(s) left off for others to follow. Sadly, many of the companies in the book are no longer in business.

MyLifeBits accurately predicted the falling cost of storage. For example, as of this writing, Amazon Web Services offers Glacier storage. $.004 per GB per month.
https://aws.amazon.com/glacier/

For life however… and pricing is continuing to drop, including Azure.

The problem? One could just use all the free storage, correct?

“Is digital storage a privilege or a right?”

Neither- it is still a cost, and it is NOT free. The cost however has fallen to ridiculously low levels and continues to drop. Again, if you don’t pay your bills, it WILL get deleted.

So unless you start a trust fund of some kind and choose to pay money forever (!) to keep it alive, it will die and be gone forever.

The Future of Looking Back (Developer Reference) – Microsoft Research
https://www.amazon.com/Future-Looking-Back-Developer-Reference/dp/0735658064

Why Isn’t Everyone Building Holocrons?

Because it is A LOT harder than writing and writing is already hard to do effectively. Heh, writing this blog post has taken more time and effort than I think it should.

For example, note taking. What if I told you that you had to create a video game every time you took notes? That’s crazy, no one wants to put that level of effort into note taking. But that is exactly how to create highly memorable content- a lot of work and concentration.

Software and security issues. Is the holocron a purely digital thing or is it a physical thing with digital storage? Or is it a purely analog storage device?

As a ~20 year SQL Server data professional, how can I use these ideas for myself? How can I build a holocron I can use time and time again to build new memories and re-visit old ones? How can I build something I can will as an heirloom or keepsake to my children or researchers? To build holocron(s) for my memoirs and for others.

Extreme Data Storage

Hybrid: Digital but stored using an analog medium DNA Storage

(1 exabyte per cubic millimeter!!!)
https://www.microsoft.com/en-us/research/project/dna-storage/

Analog Storage

Rosetta Project
https://en.wikipedia.org/wiki/Rosetta_Project
https://en.wikipedia.org/wiki/HD-Rosetta
http://norsam.com/rosetta.html

Conclusion

There is no conclusion. My journey to build holocrons for myself and others is just beginning in 2019. I am running with what I have. Using data science to sift and categorize and store things is a hint of where I want to go with some of these technologies.

 

 

 

 

 

 

Posted in Uncategorized | Leave a comment

T-SQL Tuesday #108 – Oculus Medium – Virtual Reality (VR) Play-Doh

This month’s topic is being hosted by @sqlmal – thanks Mal!

“So the challenge for this T-SQL Tuesday is – pick one thing you want to learn that is not SQL Server. Write down ways and means to learn it and add it as another skill to your resume. If you are already learning it or know it – explain how you got there and how it has helped you. Your experience may help many others looking for guidance on this.”

My topic is playing around with Oculus Medium.

A highly pixelated reference image imported into Medium, building a clustered index seek, exporting out from Medium and importing into Unreal Engine:

First, one will need an Oculus Rift headset, an adequately powered PC like a decent gaming rig, and some physical space to “dance” around in. Expect to spend $1000 to get into VR for the desktop and a lot more for a VR-ready laptop. This is a serious hobby for me. Second, one will need to buy the $29.99 software.

https://www.oculus.com/experiences/rift/1336762299669605/

What is it? From the Oculus website:

Sculpt, model, and play in virtual reality. Oculus Medium is an immersive VR experience that lets you sculpt, model, paint, and create tangible objects in a VR environment. Medium lets you create expressive works of art, whether you’re a total beginner, an aspiring creative, or a professional artist. Medium uses Touch controllers to enable intuitive hand gestures and movement for a natural, tactile experience.

Basic Alternative to the Expensive Stuff…

All of the desktop 3D modeling programs (Maya, 3ds Max, or the open source Blender which I like, or desktop sculpting like ZBrush or Mudbox) are either very expensive or really hard to learn or both. Think of them like a musical instrument- one has to practice every day in order to get any good. My goal is to be able to create things and import them into Unreal Engine (UE4) quickly. From UE4 I can then build or cook the project and install it on to something like OculusGo and in 2019, Oculus Quest.

https://www.oculus.com/quest/

Side note:

While Medium has not been announced for Quest, being able to take a VR headset with you anywhere and be able to create, ANYWHERE, is going to be simply incredible. I can’t wait to see what my amateur self and artists can do. Imagine an art studio that is with you all of the time. For creative people that is going to be the killer VR app- you and your studio. Being able to sculpt, draw or whatever in VR when the mood or idea hits you. Carrying multiple studios with a variety of projects in them with you. Did I mention you can cast your creations to a screen to show people what you are seeing in VR? Ya, this is going to be awesome when Quest comes out. And yes, I am thinking of how to do Quest presentations/performances as topics for SQL Server. Goodbye PowerPoint.

Getting Started

I didn’t want to wait years to create things. I wanted to play now. Much like Play-Doh or Legos one just needs to learn the basics and jump in. One uses this VR software standing up so be prepared to be standing for a while and try not to get tangled up in the headset cables or knock things over. What one creates in Oculus Medium are called sculpts.

How has it helped me?

Escape. A chance to experience Flow for hours on end. Get out of my comfort zone. Something that doesn’t involve coding.
https://en.wikipedia.org/wiki/Flow_(psychology)

Flow: The Psychology of Optimal Experience
https://www.amazon.com/gp/product/0061339202/

Flow. Ideation. Creation. Those are the goals of playing in Medium.

I have ideas for all kinds of inventions. A soft spot in my heart is about food, water, clothing, and shelter, with drinkable water being most important for survival and development. Topics for other time but being able to try and build things in my mind’s eye inexpensively is an expression of my creativity and imagination. Medium is a constructive outlet for me.

How to Learn Medium

Recently Oculus has updated Medium to 2.1 and has made a lot of great, free tutorials. Start with the tutorials INSIDE the software. Then, try these out. Even if you don’t have a VR set-up, watch these anyway to get an idea of what one can do with the software:

https://www.oculus.com/medium/learn/

It can take a while to learn the basics. Watch a short tutorial then try to duplicate what the artist is doing. Watch and try again. And again.

Note this is the first version of the genre of sculpting software using VR so it can be buggy at times. SAVE OFTEN!!!

The smallest resolution is 1 cm. Which just happens to match the smallest unit in Unreal Engine, the Unreal Unit (UU).

So if one is trying to build a fine mechanical watch using digital clay, prepare to be disappointed. One can “zoom” in super-close to the clay and do some fine-detail work but realize this is still clay, not micro precision molecular assembly like nanotechnology. Not yet anyway.

I have found the triangle/polygon count to be ridiculously high for imported sculpts. There are some tools to help with that (reduce number of triangles while maintaining visual fidelity on final exported .obj file) but I have not learned how to use those yet. Here is one I read about, MeshLab:

https://en.wikipedia.org/wiki/MeshLab
http://www.meshlab.net/

As far as exporting sculpts into UE4, I have found exporting as .obj with textures at the 2048 setting will generate good WYSIWYG. Import into UE4 then re-size as needed. Some of the colors can be a little off but I have been happy with the results. Please note VR is a resource intensive application- only have one thing running at a time. This means don’t have UE4 running while trying to use Medium and a web browser with a tutorial. Just one at a time. And yes, one will be re-starting your desktop often.

Just so happens that on the day I post this, I am also giving a presentation on using Oculus Medium at the Unreal Engine Meetup in Denver which I run. Starting a new user group is hard…

Hobbies on Resume

At what point does one add skills developed as a hobby and put them on a resume?
What I have heard is only if professionally relevant to a job one is applying for.

I don’t consider myself qualified for this position but it does have things I am interested in:

https://www.unrealengine.com/en-US/blog/jobs-in-unreal-engine—technical-artist

My dream job would be working with data and virtual reality. We’ll see what next year has in store.

I hope this post helps get some creative juices flowing. Feel free to hit me up on twitter:

@toddkleinhans

Thanks!

–Todd Kleinhans

Posted in Uncategorized | Leave a comment

And So It Begins: PROJECT SQLImaginarium

About

This is a post for T-SQL Tuesday #102. #TSQL2sday was created by @AdamMachanic. This month is being hosted by @RileyMajor – Thanks Riley! The core theme is giving back…

Oculus_Go

I am into virtual reality. Last week right after a drug test for my new job, I went straight to Best Buy and bought the new Oculus Go. Don’t I look happy?

This is a solid piece of equipment. I opted for the 64GB version at $270 but you can buy the 32GB version for $200. I have a SteelSeries bluetooth gamepad controller to use for my Unreal Engine creations. After some tweaking, I was able to get my #SQLMemorial temporarily loaded on to it which included a picture of the late Robert Davis aka @SQLSoldier

And Now the Public Commitment…

Now that the Oculus Go is finally here I want to go public with a goal I have had for a few years and with your help to hold my feet to the fire. To get it done. And to give me ideas. And help with content. And maybe some sponsorship dollars.

And So It Begins: PROJECT SQLImaginarium

Please use the hashtag #SQLImaginarium and @SQLImaginarium to tweet and publicly document your ideas and feedback.

What is it?

SQLImaginarium will be a virtual reality (VR) experience to introduce people who are new to SQL Server. It will run exclusively on the Oculus Go. A desktop version can easily be made as well.

But Why Use VR?

Several reasons and here are some.

  1. 100% attention and concentration. With your entire visual field being used, you will NOT be able to multi-task and take resources away from learning and exploring. Since it will be built using Unreal Engine we can use the full power of a video game engine. Not just shoehorning PowerPoint into VR (yes, I have done this in the past) but to take advantage of this new VR medium using decent and affordable hardware.
  2. Storytelling instead of PowerPoint to explain technical topics. VR allows one to build mind palaces to help with remembering things. Using 3D artwork, one can walk around and see things from many angles, sounds, animations, you name it- Unreal Engine can do it. Want to walk around INSIDE a flowchart? No problem.
  3. PowerPoint has reached its age. Remember a time before PowerPoint? You must be old. But there was a time before slide shows and presenter performances were all the rage… We need new and novel ways to learn things and VR can do that and more. NOTE!!! I love PluralSight and have had a subscription for several years now. Using one’s public library card, Lynda is free too. But looking at people running code and providing an audio commentary to slide shows only goes so far. One has to watch and sling the code too in order to truly understand the content. Ever seen anyone at a PASS session or a SQL Saturday event in the audience following along and running the code in sync with the live presenter? Information fades quickly. <rant>I would like to give tests AFTER a SQL Saturday event to see how well people were paying attention and taking notes</rant>
  4. The younger generation has grown up on video games. The ubiquitous gamepad is now the universal interface for many. We need to provide navigation mechanisms for them to navigate around environments and the gamepad easily does the job. While the simple Oculus Go controller is functional, a gamepad offers greater utility. More controllers are on the way but they can’t be used on a plane, train, bus or automobile or sitting down as easily as a gamepad for now.
  5. SQL Server is a vast and complex set of software parts. Being able to see how all of the parts work together and to be able to explore those parts further simply cannot be done with both more SQL Server Documentation and PowerPoint presentations. We need a new map. We need to be able to see the whole thing. And to see how it all works together.

What Will SQLImaginarium Have In It?

I want to see areas to help new people whom have never used SQL Server to begin to understand how SQL Server works.

To use VR to help train a new generation of data professionals.

To pay tribute to the fallen in the #SQLFamily and to keep their memories alive.

To build something bigger than me, by the community, for the community.

Sponsorships and Funding SQLImaginarium

If this idea gets traction, I could use some of the money to buy assets from the Unreal Engine Marketplace. Mainly architectural visualization stuff and things beyond my technical skills. Training assets. Utilize outsourcing when appropriate. While I have not done a gofundme or a kickstarter before, this would be something I would commit to getting set-up and going. Plus the money would give me a serious kick in the pants instead of trying to build all of this on my own and putting it out there for the world to see. Money would also be used to set-up subscription software tools like Trello and GitHub. I would use a lot of tools and techniques from the gamedev community like regular Game Jams as a type of sprint. I have my own GoToMeeting I can use for the core team.

Paying customers has a way of helping one to focus attention. Kinda like VR 🙂

In Closing

I cannot create SQLImaginarium alone. I need your help. Your ideas, input, feedback, and money. I believe the #SQLFamily needs to explore new ways of training up the next generation of data professionals using technologies like VR to help them learn SQL Server from scratch. So many people cannot afford the super cool and expensive in-person events (which are incredible!) but now that affordable VR technology like Oculus Go is here, the time is right to build new ways of learning SQL Server. VR is going to affect so many things and I believe training and education is one of those areas the #SQLFamily can utilize. So many possibilities await the bold!

Posted in Uncategorized | 5 Comments

T-SQL Tuesday #100 – Announcing NCDNAI – June 1, 2026

About

This is a post for T-SQL Tuesday #100 and it is going to be a mix of science fact and science fiction of what our data world could look like on June 1, 2026. #TSQL2sday was created by Adam Machanic. It is being hosted by him too. Thanks Adam!

Crystal Ball

During one of my sapho juice induced meditations before I began my daily mentat exercises, I began to play with an idea about DNA digital data storage. Even back in 2018, scientists were able to conceptually shoehorn 215 petabytes of data into a gram of DNA. Our progress has evolved since then, heh.

I knew from my previous work designing blockchains using DNA, that mankind faced a fundamental problem of using DNA for data storage. And that is slow read speeds.  Even with AWS Glacier DNA or on-prem Alephs of Azure, the speed of reading this kind of data frankly sucks.

The problem lies in the structure of DNA itself. The entire strand has be to sequenced in order to read the data. I mean, it is a full DNA scan for crying out loud!

So during my meditation I thought to my self, “Self, if you could run sp_BlitzIndex v50 from The Ozarians on these DNA strands, what would it find? Well, Brent’s avatar would probably do the missing index dance of course!”

Thus began my journey. As part of my dissertation work on biomimetics computational neuroscience on mapping both the conscious and unconscious algorithms of the brain, I knew there had to be some existing data structure I could use as an index. Today, I am proud to announce:

FOR IMMEDIATE RELEASE – June 1, 2026

Aurora, CO – Kleinhans Industries Releases the Nonclustered DNA_Store Index (NCDNAI) 

The world now has a way to quickly read data from DNA structures. Think of it like a tiny constellation of biological satellites in orbit around the strands. Each strand has its own constellation.

Kleinhans Industries is currently in negotiations with companies like Microsoft to license the technology.

“A billion here, a billion there, pretty soon it starts to add up to real money. We are hopeful that negotiations will be completed in the near future and companies can begin incorporating this ground-breaking technology into their DNA database products.”

-30-

Posted in Uncategorized | 1 Comment

Behind Door #1 is: SQLMemorial – A Tribute to the Fallen in the SQLFamily

About

This is a post for T-SQL Tuesday #99 Dealer’s Choice. #TSQL2sday was created by Adam Machanic. It is being hosted by Aaron Bertrand – Thanks Aaron!

On a somber note, I present SQLMemorial. For a short overview video, one can view it here. Names of folks are below.

Catharsis

One of our members recently passed away: Tom Roush. While his celebration of life service was going on last Saturday in Seattle, I wasn’t able to attend but I was working on building SQLMemorial back home in Colorado. People handle dealing with grief in different ways. At times for me I build stuff to honor the memory of someone. Tom was a storyteller and virtual reality (VR) is becoming a new and unique storytelling medium. So I dusted off an old idea and jumped into it full force in my spare time after this TSQL Tuesday topic was announced. My last conversation with him was about getting stories out of your head, even and sometimes especially the embarrassing ones. Even if you never publish them, get them documented. For me I will be doing this in the future using my drive time to record audio- a drivecast.

Backstory

I am into VR and I am fascinated by what it can do to help people in a non-gaming context. Unreal Engine has been my video game engine of choice- currently using version 4.18.3

When I announced on Facebook a while back that I was building a VR experience using pictures to help distract people in pain in a drug-free way, one of the people who expressed interest in it was Tom. The cost of the software, a GearVR headset, bluetooth controller and an unlocked Samsung S6 or higher phone was just too expensive- over $1000. Now with Oculus Go coming out for only $200 and just around the corner, all of my software has an upgrade path. Yay! So I think things are going to pick up on that front in a big way. Sadly, Tom passed before I had a chance to build something just for him.

At the PASS Summit in 2016 I participated in my second Speaker Idol and I showed everyone a small glimmer of a VR walk-through. Hard to do in five minutes BTW. Part of it was a type of Hall of Heroes, a short roll-call of those SQL Family members who have died. I have tried to keep track by using the twitter hashtag #SQLMemorial 

Present Day

So on my own as a one-person slightly dangerous VR game developer, I attacked this TSQL Tuesday topic as a game jam. Scale back the scope, scale back the scope again, and focus on the most important thing in both video game and software- SHIP! The goal for me was to build and deliver something that works. I got permission from a variety of folks  to use their pictures. Special thanks go out to: Argenis Fernandez, Jimmy May, Andy Leonard, Grant Fritchey, Mickey Stuewe, Julie Koesmarno and Tom’s daughter Alyssa, and Hawaii News Now for use of their pictures. Jim Gray’s attribution.

It is mainly dedicated to Tom but other people’s pictures of the fallen includes Jim Gray, Ken Henderson, Bob Rumburg, Alan Weber, Larry Toothman, Mike Wilmot and finally Tom Roush.

Please note this DOES work even if you do not have a VR headset to use with it.

Navigation

PC gamers you can skip this, except the next paragraph. Keyboard keys: WASD (W and S are forward and back. S and D are walk like a crab left and walk like a crab right). Arrow keys (up and down are like W and S, left pivots you in place as does right arrow). Mouse moves your head to look up and down.

To end the experience, type the ~ (tilde key) just under the Esc key. Esc does NOT work. You will see a small console line open up at the bottom. Type the word exit then hit return. If that opens under the taskbar or something and you can’t see it, then you’ll need to open Task Manager and kill the process. Extreme yes but user menus is on my list of things left to do.

Immersive VR Use

On my Oculus Rift I use the Windows Xbox controller and it works fine. On GearVR I have a bluetooth gamepad controller. If you have a GearVR and want SQLMemorial on it, I will need to compile a version specific to your phone. It will require you to give me your Oculus Signature File. Instructions can be found here. You can contact me via todd dot kleinhans at gmail dot com.

UPDATE (13Feb18) I became aware that the background sound is not playing on the Rift. My first bug to fix! It is odd as I hear sound when I play it using the VR Preview mode from within the game editor using the Rift and it plays fine on the GearVR deployed version. Go figure.

SQLMemorial

I offer this software AS-IS, no warranties expressed or implied. It is an .exe file so if you are super paranoid then my advice is don’t install it on a machine you can’t risk screwing up. Seriously though, I personally have never had a problem with Unreal Engine running on any of my machines including those I use for SQL presentations.

It has been tested on a five year old Windows 7 laptop and on a Macbook Pro running VMWare Fusion with a Windows 10 Enterprise (90 day eval) virtual machine. If you are using a virtual machine, be sure in install the VMWare tools as it will update the video driver to enable this to work. I had to do that on mine.

This is hosted on my OneDrive so it might take a while to download (5min for me) at 275Mb.

Here

Click Download. Unzip the SQLMemorial file. Open that folder and unzip the WindowsNoEditor.zip file. Open the WindowsNoEditor folder. Open the other WindowsNoEditor folder. Right click to launch as administrator on the SQLMemorial.exe to start the experience. It will take a few seconds to open. Once it does and on the first time, it will attempt to install the .NET framework it needs if it is missing in addition to Visual Studio C++ runtime. You have to agree to the Unreal Engine software terms. I’ve tried to keep the ambient sound to a background level without being too distracting so might need to turn your volume up or put on headphones. Best experienced w/o too much light in the room.

Future

I still have a list of enhancements for the next iteration. For example, I have tested adding audio clips of short messages in .WAV files and it works fine. Navigation beyond game pad controllers. VR style navigation locomotion. Media playback control. User menus. And then setting up a dropbox to collect more content from people who would like to add more pictures and sound. Maybe video links too as embedding video files bloats the size. Cloud based hosting like Sumerian

I’ll be updating this post so don’t freak out if you re-read this and see different things. I also want to do another blog post on the technical aspects of what it took to create SQLMemorial.

Looking at all of the pictures and reading some of the stories and building this with misty eyes was hard. The fallen will be missed but not forgotten.

I really want your feedback on this project so feel free to hit me up on twitter: @toddkleinhans

Thanks!

Posted in Uncategorized | Leave a comment

T-SQL Tuesday #98 – How I Learned to Stop Worrying and Love the Bomb

About

This is a post for T-SQL Tuesday #98 and it is about a time when you faced a technical challenge that you overcame- for me this is a story about database corruption. #TSQL2sday was created by Adam Machanic. It is being hosted by Arun Sirpal – Thanks Arun!

One of my favorite movies of all time is Dr. Strangelove Or: How I Learned to Stop Worrying and Love the Bomb. It is a dark comedy. A crazy General exploits a limitation in a nuclear attack plan and issues orders for his bombers to execute “Wing Attack Plan R”. Not to be confused with “Wing Attack Plan Python“, ha. It starts a sequence of events which should have never have happened with fail-safes, human in the loop systems, recall of bombers, arming systems- but some nuclear bombs on one lone bomber were on their way to getting dropped and thus starting a nuclear war of epic scale. And I love Stanley Kubrick films too.

Despite current rhetoric to the contrary, the POTUS does NOT have a button to push to launch nuclear weapons. He can order their use but he doesn’t have a big red button on his desk to bang away on like an arcade-style video game.

Context

I am no longer with the company who hired me for this client and the client company closed the main office which I used to work at here in Colorado. ALL of the consultants, managers, directors and other employees I worked with and knew are also no longer with this company.

It was a strange situation. I have too many stories, Good, Bad, Ugly and Fugly about this client. I was with them for several months. They had been through many full-time employees as database administrators and they also had a large developer team out in California. The company was bleeding money and filing for bankruptcy. In the consulting business, sunsets can be long and yes there is money to be made riding out into the sunset. Eliminating pain is attractive to investors. Another competitor was in the similar state of affairs and they decided to merge the two bankrupt companies together as one. When I was there, people were leaving on their own as it is easier to find a job when you still have a job than if one waits until layoffs or firings occur. I did some quick research for this post and found out that in May of 2016, they filed for their THIRD bankruptcy in seven years and their stock is no longer publicly traded- it is a private company. If you were the CEO, how would you attract the best and the brightest to come work for you in a dying industry? Hm.

How To Save Money – Get Rid of Temps, Vendors and Consultants

Yes you read that right. And so the story begins. I was brought in for performance tuning, disaster recovery, migrations and general database administration work. The main project was to migrate a single SQL2005 mission-critical instance to SQL2012 and all that it offered including failover clustering and Availability Groups (AG). Off-loading reports to the AG was going to be yuge as well as all of those jacked up ad-hoc queries Business Intelligence and Sales people like to run against up-to-the-second production database stuff. NOT hitting the main Production instance was going to make MANY executives extremely happy provided of course their queries used existing indexes. But I digress…

One of the cost-cutting measures was getting rid of another consulting company which performed many tasks including managing a messaging system that had a SQL2008 backend. They were also in the midst of migrating that system from on-prem to the cloud.

So I got pulled into a meeting and was told I was now responsible for system XYZ. No documentation, no hand-off, just here is the server name and go manage it. They were also using software to perform the migration and they were not going to spend another dime with that software vendor as it had gotten ugly with them financially.

I did a quick sanity check and look-see. Under-powered and running on an old server with 8GB of RAM hosting three instances and the messaging system migration was using the instance with a ~2TB database on it. Disk storage was on an old HP SAN. Average CPU load was in the high 70’s. SQL 2008 Standard Edition and no backup compression software either. Very few SQL jobs, just indexing and backups. No jobs to run CHECKDB and no SQL Alerts either…

Mr. Murphy Walks In Monday Waving Wing Attack Plan R

I respect Murphy’s Law but if I ever meet him in person, I might be compelled to beat him. Repeatedly, if necessary. No, I don’t have advanced alien weapons like these but sometimes I wish I did (contains foul language, NSFW): The Doctor is gonna bust a cap in yo ass

With limited disk space for backups and with management unwilling to spend money on backup compression software like we were using on the main system I was brought on to migrate, the previous consulting company had to make due with what they had and what I had just now inherited. Which was: full database backup to a share on Monday, Wednesday, Friday and Sunday. Backup to a different share on Tuesday, Thursday and Saturday. And then there was a job to delete the previous two backups for each share. I was told verbally by management that they had these contractual database backup requirements. And that if I wanted to change things, I had to prove that they worked before implementing them. But they didn’t have a dev/qa/uat/test system and what was worse, they didn’t have ANY disk space to even test a restore on. So I could not implement something like a once a week full, differential every day, and transaction log backups running on a 15 minute basis. NOTE!!! You need a restore strategy, not a blind backup strategy. So they had never tested a backup restore of the 2TB database due to lack of disk space.

First thing Monday morning someone from the messaging team asks me to look at the database as it looks like their on-prem to cloud migration process suddenly slowed WAY down over the weekend.

I login, launch SSMS and my heart sank. All of the SQL Logs showed today’s date on them. Not a good sign. I open the most recent one and saw Stack Dump on Page ID X. And Y. And many more. Log said data loss can occur and to run DBCC CHECKDB immediately. We had a meeting and after looking at msdb.dbo.suspect_pages table, I knew the system was in trouble. Management was HESITANT TO OPEN A TICKET WITH MICROSOFT BECAUSE THAT WOULD COST MONEY! I knew my consulting hourly rate and just shook my head. Instead of calling for help right away they wanted me to mess around with it for hours and/or days. I still pressed to open a ticket because I was rightfully nervous about the backups.

Guess what? I determined the corruption happened sometime Saturday. But did we have a backup from last Friday? Nope- because there was a job to delete the previous two days’ backups. So I was unable to restore a backup. The backups from Saturday and Sunday contained the corruption so they were not going to sadly be of use.

The Bomber Will Always Get Through

This was serious. I had just inherited a system that shortly went belly up and they were looking at me to not panic, fix it, and move on. I had dealt with database corruption before but never this bad. I called other consultants on my team, reached out to others, and began wielding my Google-Fu skills. In the meantime the storage team ran diagnostics on the SAN and couldn’t find anything wrong and no changes had been made.

Here was the scope of the problem. Not all corrupted pages were listed in the SQL log or were listed in msdb.dbo.suspect_pages. DBCC CHECKDB would itself issue a stack dump when it encountered the first of many corrupted pages.  Same for running WITH REPAIR_REBUILD. Consulted with management again about data loss and if running CHECKDB WITH REPAIR_ALLOW_DATA_LOSS would be acceptable. Got the go-ahead but no joy.

I could re-produce the stack dump by running a SELECT command and giving it a specific value for a clustered index on the largest table. Corruption appeared to be isolated to just this one table. Also, intentionally making a system do a stack dump is not a good idea either as I found out that if I did that repeatedly sometimes the instance would re-start itself too… I had to figure out how to 1) save the data 2) see if its possible to save the corrupted data and 3) get rid of the database corruption.

Kalen Delaney  and Paul Randal To The Rescue!!!

Well, not exactly but I did have the awesome SQL Server 2008 Internals book written by her and many other smart people including Paul. Signed copy even!  So here are the challenges. How do you write scripts to pull out the good data into another new table without causing a stack dump? And don’t do anything that isn’t Microsoft supported.

First thing I did was test the clustered index ranges. I learned SQL Server does NOT keep sequential Page IDs with a table. Meaning I somehow needed to find all of the Page IDs belonging to a table. Because by using DBCC PAGE I could “see” the clustered ID I could then run a SELECT and insert that row data into a new table. So I made a script to run through all the pages and figure out which ones were bad. From Kalen’s book to all of the posts by Paul Randal on sqlskills.com I was able to migrate as much data as I could. We ended up loosing about 20,000 rows out of 10’s of millions.

In Rides Cowboy Matt Dillon

My pleas to upper management were finally heard and they agreed to let me open a ticket with Microsoft using a manager’s credit card. Microsoft verified I had done all the right things in the right order which was nice to hear. I got an earful about NOT being able to restore from a backup and that we were NOT on the latest Service Pack for SQL2008. It was determined that the overloaded system had probably encountered a memory scribbler problem and had corrupted the metadata of several pages in memory vs on-disk which prevented reading any data from them.

In the end I had exported all the data to a new table, re-named the old, re-named the new table to the old name and re-created all of the non-clustered indexes. Double checked all of the foreign key relationships too. And management relented and I changed the backup schedule as well. Still didn’t have disk space to test a restore, even after all of that 🙂

Epilogue

After I was done at this particular client and successfully migrated that other system from SQL2005 to SQL2012, I took a Pluralsight course by Paul Randal called SQL Server: Advanced Corruption Recovery Techniques. In it, he shows how to take a peak at metadata and also even how to extract data OUT of non-clustered indexes! I wish I had watched this course before I encountered this database corruption. It could have saved me from a lot of pain, stress, and wasting time.

I hope there is NOT a next time for database corruption like this but at some point in your data career you will encounter things way beyond your level of expertise and you will need to figure things out for either your employer or for your client. Thanks again to Kalen Delaney and Paul Randal, some Rockstar authors!

Maj. T.J. “King” Kong: “…Survival kit contents check. In them you’ll find: one forty-five caliber automatic; two boxes of ammunition; four days’ concentrated emergency rations; one drug issue containing antibiotics, morphine, vitamin pills, pep pills, sleeping pills, tranquilizer pills; one miniature combination Russian phrase book and Bible; one hundred dollars in rubles; one hundred dollars in gold; nine packs of chewing gum; one issue of prophylactics; three lipsticks; three pair of nylon stockings.

Shoot, a fella’ could have a pretty good weekend in Vegas with all that stuff.” <- What does YOUR database administrator bug-out bag look like? Hehe.

Inside the Making of Dr. Strangelove

Posted in Uncategorized | 1 Comment