T-SQL Tuesday #45 – Follow the Yellow Brick Road

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Follow the Yellow Brick Road”. If you want to read the opening post, please click the image below to go to the party-starter: Mickey Stuewe (Blog | @SQLMickey).



 
When I read this months subject, I didn’t know what to write about, until I read one of the other blogs. So I’m sorry Mickey, but I’m using the subject this month as a guideline, and extend it a little bit. I’m glad I know Mickey, and I’m guessing she doesn’t mind me getting a little creative.

The writer of the post I read, talked about index fragmentation and data growth, and then I remembered a situation I encountered a while back.

When you come in to a company, you’d really like to see (and maybe even expect) to walk into a well organized team. A team that has procedures that work for them, and team members that agreed to solve problems in a specific way. But sometimes, it’s not…

 
Here you go!
On my first day as DBA at the company, I walked in and after a few conversations booted up my computer. Once it was booted, they gave me the name of a server, and wished me good luck. No documentation, no explanation of the databases, and no mention of who worked on the server. The only thing they said when they “handed over the key”, is “Here you go! From now on it’s your responsibility to keep the environment running and healthy!”.

So, there I was… No documentation, no health check, no one to tell me what the status of the server was…

 
Taking that first step is always the hardest
So the first thing I did was check out which databases were on the server. There were a lot: 70 in total. Then I checked the user permissions: most users were a member of the db_owner role. Great! Asking which users really needed all that permissions was useless. Everyone needed full access to everything.

So then I took the step to tell them that I was in charge of the server from that moment on, and that if they did something without my permission, I’d remove their access to the environment. Looking back, that was easier said than done.

But the first step that was really hard to accomplish, was reporting of the server health. No one thought that we needed that, and if there was something went wrong we would notice in an instant. And because I knew we really needed that, I just cleared my schedule, and created a few reports.

 
Being right is always a fun thing
A few weeks later, they finally saw I was right. One of the databases filled up a disk, and now we had issues. The only way we could see what went wrong, was looking at the data growth over the previous weeks in my reports. So now I could show them how wrong they were. It’s nice to be right once in a while!

 
Audit
This is were this months topic comes into play. A clear indication that you lack an audit trail, is when there’s an issue with one of your processes, databases, or even as small as a specific dataset, and you don’t know where the issue came from. Following your data flows from beginning to end is always a good idea. And if you work at a larger company, you’re (at least in the Netherlands) required to document your data flows for a financial audit every year.

But not only your datasets need audit trails. Your databases are in dying need of an audit trail as well. Because what happens if your database increases 50% in size over the course of a week, and you don’t track those kind of numbers? Your disk is filling up, your database stops working (if you haven’t got the space to extend your datafiles), and eventually your world is becoming a dark and evil place if you can’t fix it.

 
Conclusion
Even if you don’t have a full front-to-back audit trail of your data and processes, at least try to monitor and audit the key point in your data flow. That helps you debug excessive data growth, and helps you when (for example) a user creates a new database without asking your permissions. Even small audits help you out when you’re in a world of pain and trouble.

T-SQL Tuesday #44 – The second chance

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “The second chance”. If you want to read the opening post, please click the image below to go to the party-starter: Bradley Ball (Blog | @SQLBalls).



 
This months topic isn’t easy for me. Even while I’m writing this, I’m still thinking about what that means to me personally. Second chances sound to good to be true. Doing something for a second time, and trying a different approach then the first time, in order to succeed…

Normally I try not to be a person that looks back at previous mistakes, but sometimes that’s easier said than done. But I must say, there are not a lot of things I regret in my life. Only one that’s really close to my heart, and nobody probably knows about. But I won’t bother you with that story…

 
Deserving a second chance
People always say: everybody deserves a second chance. But I don’t completely agree with that. Everyone makes mistakes, and that’s not something to be ashamed of. But if you make the same mistake twice, you need to start wondering if there’s something you could have done to prevent it. But even then, you deserve a second chance.

The people that actually know me, know I’m a nice guy, I’m honest (sometimes even a little bit too honest), and normally you can’t get me angry. But if you screw the same things up over and over again, I’m not that friendly anymore. Let me explain that with an example.

 
No, that wasn’t me!
A while ago I worked with someone who thought he was really good at his job. Personally, I had some issues with that opinion, but I gave him the benefit of the doubt. On a number of occasions he screwed things up, and I thought he should have known that what he was doing was never ever going to work. But still, I was willing to give him that second chance. But then he got me angry. And you won’t like me when I’m angry.

There were a number of SQL Server Agent Jobs running, and they locked up some of our tables and databases. When I asked him to look at that, he said he didn’t run those jobs, and focused on his screen again. So I asked him again, nicely, to look at it. He replied with the same answer.

A little bit angry, I told him the jobs were started on the server, and that he was the only one that was logged on to the server. Then he “suddenly” remembered he started the jobs, and said the locking wasn’t that bad. As a DBA, I took a deep breath, and counted to 10, and waited for him to fix the issue. But if you’re that stubborn, you’re clearly lying to me, and don’t even have the courage to tell me you screwed up, you don’t deserve a second chance in my opinion. At least be honest with yourself and to your colleagues!

 
Honesty get’s you a second chance
At this and previous companies I worked for, I always tried to teach the student and interns they need to be honest and listen to people with experience. Even if things go wrong, and you’re the one to blame, at least tell the truth. For me, that’s the difference between fixing the issue together and moving on, or letting him take the fall all on his own. But this is also an experience I got handed down to me by my colleagues a few years back. This is what happened to me, as I remember it:

When I started my first job in IT, I was offered a job as SQL Server Consultant. That meant that I was responsible for data conversions from different systems to our core system. When I took the job, I had never written a query before. But by listening to colleagues and my mentor (a good friend of mine who worked for the same company), I made it into a development team about 1.5 years after I started my first job.

That meant I was able to access the production system (yes, that’s where the problems began!). These permission were given to me, so I could solve data related issues in production. Until the day they asked me to update 5 rows in production. I checked and double checked the T-SQL statement I wrote, asked a colleague to take a look at it, and then took a break away from my computer so I could totally focus on this task when I got back.

I sat down again, looked at the query one last time, and pressed F5… One minute passed… Two minutes passed… And then the query finished… 50.000 rows affected… I slightly panicked, and noticed I only selected the update, a half “WHERE” clause, and no “BEGIN TRAN”… My heart started racing, and I picked up the phone and asked the system administrator (a good friend of mine, who worked at a different location) if he could restore the last backup for me, because I screwed up. After some questions, and some explanations about my mistake, the last thing he said, before he hung up the phone in anger, was “Which backup? The one that didn’t ran for the last few weeks?”.

I didn’t know what to do. How could I ever fix this? Almost every record in the table was updated, and there was no way of knowing what he old values of the updated records were. So it took all my courage to pick up the phone, and ring the system administrator again. All I heard on the other side of the phone was his evil laughter. Before I could ask him what was going on, he told me: “I’m glad you were honest to me. But don’t worry, I’m restoring the backup that was taken an hour ago. There’s no data lost”.

At that moment, I didn’t know what to think or feel. At first I wanted to slap him silly, but a few minutes later I wanted to thank him for his wonderful help. He probably saved my ass, and he never told anyone except my mentor (who also was my direct manager back then, and also a good friend of us both). A few days later, the three of us talked about it face to face, and eventually all laughed about the situation.

 
A wise lesson
But if I learned anything from that situation, besides never running an update without transaction or “WHERE” clause, is to be honest. Even though you might think the company will fire you for the mistake you made, it’s always better to tell them then letting them find out themselves. And that’s what I try to tell the students, interns, and junior colleagues I work with. Be honest, and then you earn a second chance…

What do you want to be when you grow up?

Last week I told you that I’m about to change jobs. Because the contract wasn’t signed yet, I could tell you what my next challenge was going to be. But now I’m glad I can tell you where I’m going to end up next: I’m going to become a DBA!

I’m so glad I can finally tell you the good news! I’m so excited to start my next challenge! From next week, I’ll be a Microsoft SQL Server DBA. And if all things work out in the first few months, the planning is that my job will also include Oracle, MySQL, and MongoDB later on.

But for now, my challenge is to become a DBA with expert knowledge of Microsoft SQL Server, and all the features that ship with it. As far as I know now, my planning for the upcoming year is to build a test environment for the SQL Server environment they have now, and a migration of the system to SQL Server 2012. That sounds like a hard work for my first year, but I’m really excited!

Until now, I only worked as a SQL Server developer with a few DBA tasks. The last years I started thinking about my future, and what I really wanted. The only thing I wanted to try out but never had the chance to, was becoming a DBA. And now I get a chance to prove myself as a fulltime DBA. This might end up getting my head blown off the first few months, but I’m excited to test my knowledge and skills, and to expand them.

Thinking about next week makes me both nervous and happy. Feeling happy because I can prove myself (and others) that I can actually do the things people told me I would never accomplish, and nervous because I’m stepping out of my comfort zone. But I’m totally convinced I get all the help that I need to succeed from my new colleagues, and my SQL Family.

Last year I experienced the commitment and dedication to help one another in the SQL Server community. That’s why they call it the SQL Family. Because it’s a hardworking community, that’s always willing to help you if you need them. They helped me out on several occasions, and I try to help them as much as I can. And with them on my side, the upcoming period is going to be a success! :)

Without pain, without sacrifice, we would have nothing…

The last few days flew by for me. After 2.5 years of working for my previous employer, I decided it was time for a new challenge. In the past period I’ve learned a lot, and had the opportunity to learn and research a lot of new things and techniques.

In 2010 I started working for the company, and began as a Data Warehouse (DWH) developer. Before my first day on the job, I had pretty much no experience with a DWH. That was quite a challenge, especially because there was no documentation at all. So I needed to find out everything myself, because there wasn’t a real DWH developer before I started there.

This also gave me a chance to dive into Reporting solutions. The company ran a Reporting Services machine with tons of Management-, Marketing-, Sales- and Development-reports. These reports varied from showing turnover information, to showing the IT department the (partial) morning check.

Eventually I ended up building reports, cubes, and developing and maintaining the DWH. This was my daily job for almost a year, next to helping out my team, consisting of about 10 to 15 .NET developers.

After about a year, management decided to let an external company develop our DWH. That gave me more time to focus on building reports, and doing more development work for our test- and production environments. That year I started with spatial data in SQL Server. This was one of the coolest things I got go do in the 2.5 years I spent there.

The hardest thing to give up wasn’t the company or the job, but it was the team. It was a pretty young team of developers, and most of the guys (and the one gal we had) were pretty awesome. There was always someone who could help you out, and the team members had no problems with working late to help a colleague out. The team was amazing!

But like I said, after more then 2 years, it was time for something new. While reading a blog post by SQLRockstar (Blog | @SQLRockstar), I read a beautiful quote that convinced me I made the right decision:

“Without pain, without sacrifice, we would have nothing”

This is a quote from the movie Fight Club. It tells us that nothing comes without sacrifice. You can’t achieve anything without taking risks, and sticking out your neck. This is what I’m about to do…

I can’t tell you yet what my next challenge will be, but I’m very excited to start my new job. I’m hoping to write a blog post next week, telling you more about that… :)

Follow

Get every new post delivered to your Inbox.

Join 34 other followers