The Chamber of SQL – The Chamber of Secrets

1, 2, 3, do you count with me.
A couple of months after the breakdown of one of their databases in production, Stephanie, Marc and Richard worked out a very good plan. The plan was set in motion days after the data loss the company suffered. Although not much, losing data still means losing valuables assets.

Stephanie created a restore script that would automatically be fired on a weekly base. Basically the script would pick a database backup and restore it onto a small SQL server instance they created only for the purpose of restoring databases. The script already proved its values once or twice. Peace returned in the cubicle of our 3 characters and Stephanie was able to continue her project: migrating their current SQL server environment to the latest SQL server version.

Always be prepared
After a long and nice sunny weekend, Stephanie entered the office and went to the coffee machine. First thing in the morning. Without her black gold she was not able to do anything. Taking her first sip, it reminded her of a scene from Twin Peaks. ‘This is some damn good coffee Diane!’. She chuckled, until Richard came running around the corner waving like a madman. OhOh, was Stephanies first thought… Did we lose another SQL server over the weekend? Impossible! Out of breath, Richard started what sounded like some sort of strange dialect… Slow down Richard. Take a deep breath and tell me what’s wrong.

After a couple of minutes, Richard pulled himself toghether and launched into his story. Well, there is not much wrong, but I have to give this presentation to the board of directors. Not that I’m too nervous about it, but it will be the same old story. CEO asking our CSO (Richard now pretending to be the CEO, majestically said) ‘Well Frank, tell me how much we have in our pipe line and what is going on in our sales department’. He will continue to ask the same question of each division and they will all come up with a fancy spreadsheet, juggling their numbers. And then he will look at me, already knowing what I’m going to anwser most of the time. ‘And Richard can you tell me what’s going on in IT.’
I hate it when he does that. He is not aware of how much work we have and what of kind of magic we need to pull out of our hats to keep this place running. Yesterday, one our ESX server went crazy, resulting in a Domain Controllor going loose…

Stephanie interrupted Richard. She said, ok no time to loose then. I think I can fix something. When is your meeting scheduled? Richard, still thinking about the ESX, came back to reality. Huh, oh well Friday at 11h. Stephanie smiled, plenty of time left.

Lightweight BI
While starting at the customer, she actually automatically had some scripts in place, she always used. This was to make her own little Sequelanie as she would call it. She gathered all sorts of information about the infrastructure, all placing it into a database she created. Her own little DBA toolbox. While this was not intended to be published, she could use this information to create a small PowerBI dashboard. She would start doing BI for IT, the liteweight way.
On Wednesday evening she had a first rough draft of her dashboard. She had worked toghether with Marc to fetch data from the DC, DNS, active directory, to make sure other systems than SQL server where in the dashboard as well. To her amazement, she actually liked the sight of dashboard. Richard would no longer be the only person in the room without a fancy dashboard and he would actually know what the infrastructure looked like.

On Friday morning, the dashboard (which was a POC) was ready. Richard smiled when he saw it. Unbelievable! All the information about the IT department combined in a dashboard, using the latest technology. Wonderful!! He went to the meeting. The CEO was impressed by the accuracy and the look ‘n feel of the dashboard. He wanted a training and an implementation of this throughout the whole company.

Stephanie smiled, picked up her phone and called her boss. Walter, I think we need a BI expert here..

Know your infrastructure
The point that I’m trying to make is this. It is of the utmost importance to know what your infrastructure looks like. Many companies I see do not have a list of the databases running (only focussing on SQL here, but probably other systems as well). Sometimes there are still servers without anyone knowing they are still running.

System engineers, developers are always busy trying to get the most out of the systems. There is no time, nor budget to assemble all this valuable information, while in other departments this is one of the main drivers. Start documenting your systems now, step by step, Rome wasn’t built in a day either. Create a phased approach and a good plan that is clear for everyone. Try to work on this for a couple of hours a week and your environment will become more transparent.

Creating a small dashboard with Powerbi can be done quite fast and you will be amazed what a treasure of information that gives you. Happy dashboarding, until next time.

The Chamber of SQL – part I

Frederik Bogaerts is SQL server DBA bij Kohera.