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.
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.
6 en 7 oktober 2020 Het Logical Data Warehouse, een door Gartner geïntroduceerde architectuur, is gebaseerd op een ontkoppeling van rapportage en analyse enerzijds en gegevensbronnen anderzijds. Een flexibelere architectuur waarbij sneller nieuw...
2 - 4 november 2020Praktische driedaagse workshop met internationaal gerenommeerde trainer Lawrence Corr over het modelleren Datawarehouse / BI systemen op basis van dimensioneel modelleren. De workshop wordt ondersteund met vele oefeningen en prakti...
10 november 2020 Praktische workshop Datavisualisatie en Data-driven Storytelling. Hoe gaat u van data naar inzicht? En hoe gaat u om met grote hoeveelheden data, de noodzaak van storytelling, data science en de data artist? Lex Pierik behandelt de s...
11 en 12 november 2020 Organisaties hebben behoefte aan data science, selfservice BI, embedded BI, edge analytics en klantgedreven BI. Vaak is het dan ook tijd voor een nieuwe, toekomstbestendige data-architectuur. Dit tweedaagse seminar geeft antwoo...
23 - 25 november 2020Praktische driedaagse workshop met internationaal gerenommeerde spreker Alec Sharp over het modelleren met Entity-Relationship vanuit business perspectief. De workshop wordt ondersteund met praktijkvoorbeelden en duidelijke, herb...
1 en 2 december 2020 Correcte informatie die in de juiste vorm en op het gewenste moment beschikbaar is lijkt een vanzelfsprekendheid. Dit doel kan alleen worden bereikt met een consequent beleid, dat doordacht alle fases van de levenscyclus van info...