Nobody is sysadmin but me

Standard

I’m going to be very blunt here: Nobody is sysadmin but me on a SQL Server instance. With “me” I mean the DBA group that has to be able to work on the instances of SQL Server.

Security BreachDo you recognize this situation:

A company application that stores mostly private information of their customers and everything runs smoothly. Than suddenly you get a security audit and you discover that the application has GOD mode on the instance. The auditor asks you why and you don’t have your story ready. The auditor writes down something and leaves. A few hours later your manager comes along, sweaty, fear in his/her eyes and asks you why you didn’t do your work for being in control of the server. “Why didn’t you screen the database servers for security implications!”.

This is what happened to me and I was only there for about 6 months, but being the only DBA I was the one to go to. From that moment on I made it a mission on database servers to make sure that no account but the DBA group was sysadmin on the instance.

Why was the server set up this way?
What went wrong during the installation of the application was that the software vendor couldn’t give a straight answer why the application didn’t install with normal installation right like db_creator. In a perfect world the software vendor supplies a set privileges needed for the installation and a set of privileges needed for normal functioning of the application.
In this situation this was not the case obviously and the DBA at that moment just gave SA to the service account.

But why is that a bad thing? I’ll give you a few examples:

  1. Nobody on the instance need to be sysadmin except admins…. period 😉
  2. If you’re sysadmin you can do anything in SQL Server, no restriction
  3. All security check are bypassed
  4. No software is hack-free

Imagine that you come in on Monday and suddenly and due to budget cuts or other priorities your application is not up-to-date. I’ve had situations where the vendor went out of business but the application was needed for several business-critical processes. Suddenly employees come to the administrators telling them that there is no more data in the application. You look at the databases and the database is gone, or all the tables are deleted, or even worse the application could connect to the internet and suddenly all the data is available online.

But what if the software vendor doesn’t know ( or want to know ) how to make the accounts work with less rights. I assume you’re having a test-server where everything is setup as in production. Just remove all the rights and only give the minimum read ( and possible write ) permissions. Let the users test the application and adjust where needed.

But what if I have a certain group of people who need more rights and sysadmin is easiest way? Lucky for you since SQL Server 2012 you’re able to create your own server roles. That means that you can setup a role with for example the “view server state” right to let other users see what happens on the server. Mike Walsh created a nice example how to give junior DBA’s the tools to do the work they need to do.

I’ve been in situation where project managers were desperate to complete their project but with an ugly security model in place. In the past, I must admit, I sometimes caved due to the pressure to complete the projects. Don’t let this happen!
When the project is complete you’re responsible for the database server, with or without security in place like it should, and when push comes to shove you have to explain why you accepted the database server as it is and why you didn’t act to get things right.

But I don’t know this server, how do I see which users have too much rights?
I’ve used the script from the following article for a while now and it helped me a lot to get a fast overview of the database and server permissions set for each user or group.

Hope this helped you out!

 

Recruiters, don’t get me started!

Standard

Have you ever been recruited by a recruiter or a recruitment agency for IT? Than you probably know how 90% of these agencies work.
You have crappy recruiters, normal recruiters, and good recruiters. The normal en good recruiters are rare.I will start explaining why I hate the crappy recruiters/recruitment agencies.

As most the people in the IT do I wanted a new challenge, updated my CV and uploaded it to a job website like Monsterboard.
Every recruitment agency gets a message when a CV pops up with certain keywords and suddenly my mailbox was filled with dozens of requests for me to call them.how-to-avoid-bad-recruiters

In the first place, I don’t call you when you want to make money. You call me! I’ve seen cases where recruiters ask 25% of my yearly salary for placing me somewhere and I’ll make you work for your money.
Second of all don’t contact me if you found one keyword in my CV which is mentioned in the job description. Make time to read my CV and try to understand what is in it.
Third, if you call me and I’m unable to answer don’t try calling me every 5 to 10 minutes. Leave me a message and I will call you back as soon as possible. I’m probably in a meeting or something else has come up which is more important than answering a call!

I’ve had situations where recruiters ask me for exclusivity. They wanted to make sure I didn’t put some of them against eachother.
When you have a recruiter which asks this from you, ask them why you shouldn’t. If they answer agressively that this is the way they work hang up the phone. You’re dealing with a crappy recruiter. You don’t want to bet on a single horse and they should work their ass off for the amount of money they ask.

When you go to apply for a job you have the regular conversations. Tell something about yourself, how have you come to our company, why do you want to work in our company, why do you want to leave your current position? When everything goes well, and the recruiter did his/her work, they mostly of the time know your offer beforehand.
I hate this part because I don’t want to negotiate about my conditions and salary. You either want me for the money I’m worth or we go our seperate ways.

I had a situation where I got a job offer where my monthly salary was €500 below the salary I gave to the recruiter. What the recruiter did next was so atagonizing for me that I broke all contact with him.
He tried to persuate me to take the job for less but started to offer different bonuses which than make sure my salary would be higher. The thing with a bonus is, when you don’t get the bonus for some reason, either projects don’t rush in or a change in the company, you have a problem at the end of the month paying your bills.

Recruiters try to push you into a job with all they got because they live of it. They are the car dealers of the HR and put you away like a number.

What to do if you’re contacted by a recruiter:

  1. Let him/her send you the job description before you send you CV.
  2. Make sure the job description is a good fit
  3. Set your wishes what you want to see in the offer. Do you want to work based on bonuses, a car, refunds of travel expenses, maximum distance to get to work etc etc
  4. Don’t let them get exclusivity, let them work for their money
  5. Don’t let yourself get rushed into a job, sleep about the offer and than come back with the news

Offcourse I know that there are good recruiters and that they try do their work as good as they can. But unfortunately a lot of them are just in it for the money and they want to make as much as they can in a short period of time.

Unfortunately my history with recruiters has been a bad one and I hope this helps you a little by finding your dream job.

Indifference towards database administration

Standard

I got in a situation where we had a problem with database systems which luckily was easy to resolve. I didn’t know the network architecture that well and started investigating the database servers.
There was no one who had the responsibility to do the database administration as a whole. Instead the database servers where managed as good as they could do and nothing more.

indifferenceAs I came to become more known around the systems I started to as questions to the administrators why certain decisions were made.
Appearently I struck a chord because the administrators became defensive and answered my questions in short without supplying the full context.

One of the questions was: Why did you install a 32-bit SQL Server instance on a 64-bit operating system instead of a 64-bit version?
I got the following reply: We do that because the server can never use more memory than 3 to 4 GB.

A second question was: Why are all the databases set to simple recovery mode instead of full?
I got the following reply: We had trouble with disk space because the t-logs grew and it was easier to set it to simple recovery mode.

A third question was: Why is the largest database of 200GB one large data file?
I got the following reply: I don’t know it just is and multiple data files make the administration of the database a lot of work.

Seriously! Seriously! If you don’t know what you’re doing make sure you do know or don’t do it all and leave it to the people who do.

I wasn’t surprised about the fact that they installed the 32-bit instance or the other replies, but I was surprised at the indifference towards database administration. I can’t stress enouph how important database administration is and especially when you have a large set of databases which are business critical.

I’ve worked at several companies and when I come in as the DBA I’m always astonished about the indifference that administrators have towards SQL Server or any other database system.
You can’t expect the systems to keep working when you don’t have the knowledge.

What can you do to make database administration more prominent in the IT department:

  • Make sure that the CTO or th any other lead in the IT departments knows the importance of the continuity of the database systems
  • Write proposals stating the problem and what the impact is on the systems
  • Make sure you have the mandate to make the proposals
  • Make little steps. Rome wasn’t build in one day..

One last few words about this before I

Now get up and get your act together and don’t expect everything to keep working all the time. At least read into some of the basic literature of the database systems.

Discovery of my personal profile

Standard

Recently I came to the discovery of my personal profile in such detail that it shocked me.

insightMy employer wanted that our department should take a so called Insights Discovery Core skills training where everybody from the department would be part of.
The reason for this course is to create a better understanding of other people and yourslf.

Part of the course was to fill in a long list of questions which were statements which you have or don’t have.
Based on the answers a profile was made and i was suprised how much it was in common with what I know of myself.

On one hand I find it funny to read how somebody who doesn’t really know you can describe you in such detail that it’s you on paper.
On the other hand I don’t like the fact that I’m being put in a box.
Although I don’t like to be put in a box where somebody tells me what I am, my colleagues and wife thought some of the negative aspects in the profile were spot on and should be taken care of.

This was an eyeopener for me where I really discovered how other people saw me and based on the profile I could than try to tak these aspects into account.

A lot of the profile was based on models and was probbly created over years of research how people are and how they react on situations.
Still a lot of the aspects in the document were a bit over the top and should not be taken too serious. It’s still a profile based on questions and models and should not be definit truth.

It was a fun experience and I learned a lot about myself and my colleagues where I without this course should not have known what I know now and how to better myself.

Anybody who is interested can get more info on these websites:

Responsibilities of a database developer

Standard

As a SQL Server DBA I tend to keep my servers as fast as possible and try to find performance issues when they comeup. A had a situation where the responsibilities of a database developer were clear for all parties..

responsibilityA couple of weeks ago I got into a discussion with a software developer stating that the developer didn’t keep it’s end of the bargain.

In last few years we’ve been having performance issues with Microsoft Dynamics 2009 with the calculation of the invoices.
Because the developer couldn’t give a clear solution to the problem, I started my own investigation.

I came to the conclusion that we were missing some indexes due to some standard functionality of Dynamics AX 2009 which includes an extra column in an index called the DATAAREAID. SQL Server doesn’t want that for the query so the query creates a lot of table scans.

There were three queries that caused a lot of I/O which could easily be fixed adding a few indexes. Due to the new indexes created the process went from 8 hours to 1!

This raised a lot of questions from our side because in my opinion creating indexes is a developer’s responsibility.

In my opinion the responisbilities of a database developer are the following:

  • Develop ER diagrams
  • Create tables, views, indexes, stored procedures, triggers etc
  • Create custom functionality
  • Make sure code doesn’t create blocking and deadlocking
  • Develop reports
  • Develop SSIS packages

I’m sure there are a lot more responsibilities for a developer but this is my shortlist.

As it came out, the developer showed no intention to help and take responsibility for the development of the database. The reason for this was because they could only create indexes through the application.

I can understand that companies want to hold on to a certain way of developing but there is a time and place when you should look back and see if you’re still on the right road.
In my opinion a software developer is responsible for the their product from A to Z which includes development on the database level. This is besides their responsibility also their prerogative from which customers are not allowed to make changes to the software without the software developer knowing about it.
In many cases you’re not even allowed to make a change in a database because support or warrenty will void and you’ll be in a pickle when things go wrong.

I’ve worked with a lot of software developers and unfortunately there are companies that don’t take responsibility for the database development and only delivered the code.

In the end we decided to make the neccesary changes to the database and contacted the software developer that we made the changes.
The people from the business were happy and the developer had not made a response.

Seniority, are you up to it?

Standard

In the IT world you have a lot of people with loads of work experience and knowledge where most people would dream of.

At the companies where I worked in the last decade I’ve seen people become senior just by the fact that they worked in the company for a longer period of time or because they had a lot of knowledge in a specific part of their work. Another company “sold” their employees to customers as senior when they had a certain amount of certifications. Of course this is ridiculous and lots of times people got shot down because reading books and doing exams doesn’t make you a senior.

But what is a senior and when can you call yourself a senior. If I look at all the job descriptions that are made for a Senior DBA they all come done to the amount of work experience in a specific field and the size of the  databases they worked with.

Is that all a Senior DBA needs to be? Or any senior in that matter?

My description of a senior is somebody that has a good grasp of the product and a good amount of experience in their field.
But that’s not where is stops!

A senior is somebody that also knows what lies beyond his/her field. A senior is able to communicate with the business and lead a group of people in a project and get it done.

What separates the wheat from the chaff is when you accelerate in understanding the greater picture. People like to be placed in a box where they only have to do their job and at the end of the day go home to wives and children (if they have any). This is not what makes you a good senior and will not make you a rock star.

I read two articles by Brent Ozar, “So You Wanne Be A Rock Star” and “So You Wanna Be a Rock & Roll Star, Part 2“,  about this but I want to take it a little further.
Here are my thoughts on how to become a  great senior:

Expand your knowledge

Expanding your knowledge doesn’t stop about the amount of books you read and the amount of certifications you have.
Go to free webinars, go to seminars, talk to other people in- and outside your field.

Know your business

Sit down with the managers of the business and talk ask them how the process works. Where are the caveats and what could be fixed.
Expand your knowledge about he business process and try to find a way to get yourself noticed by understanding what it’s all about.

Get involved in the community

One way to get a good grasp to be a senior is helping the community. If you’re active within the community people are more likely to look at you as a senior.
Join groups and forums on the internet which are highly used in your field.

Teach others

One way to create self confidence is to teach others. When you teach you learn.
This might sound weird but when you teach others you have to take a good look at yourself before you can teach other how to do it.
You start asking yourself the questions: Am I doing it right? Is this the way to do it? How can I teach this person to understand what I’m trying to say.

At the end it all comes down  tothe amount of effort that you want to put in your career. Not everybody is made to be a senior but if you have the drive and the skill, go for it!

It will take time and you’ll think at some point, “Why am I doing this”, but in the end it will pay off.

 

 

Puzzles and Interview questions from Google

Standard

During my last holiday I read the book ‘Are You Smart Enough to Work at Google’ by William Poundstone.

I’m always interested in books that give some insight on how a company works and how it began.
When you read the book you’re confronted with all kinds of interviewing techniques which Google uses to get the smartest and brightest people.
But that’s only for the first 130 pages or so. The rest of the book is filled with all kinds of interesting puzzles and interviewing questions which let’s you really think.

An example of a question is the following:

“You have the following sequence: 10, 9, 60, 90, 70, 66. What’s the next number in line?”

Don’t try find the logic in the numbers itself because there is none. In fact if you write the numbers in text like “ten”, “nine” you’ll see that the amount of letters of the numbers are the sequence.
I like these kind of puzzles because it learns you to think outsight the box and help activate your creative thinking.

There are loads more of these kind of puzzles and many of them very subtle and sophisticated.

William Poundstone tends to explain some things a little too much, such as a prime number. If you had a little algebra in your youth you know what prime number is.

That aside I liked the book and I enjoyed doing the puzzles. I didn’t really care about the HR procedures which Google and other companies have because there loads of books about that already available. If you like puzzles and brain teasers than go read this book.

 

 

Work towards a common goal

Standard

In the past I had the sheer luck to be able to work with very skilled people on both the technical and the non-technical aspect.
I also had to work with loads of people who had the communication skill of a strawberry and had no clue about what was going on.

Common Goal

I think everybody has worked with these kind of people and the fact is they’re everywhere. Sooner or later you face the music and you have to work with people who you’d rather drop out of an 8 story building.

The thing is that you can’t ignore dumb behavior and you still have to get the work done if you like it or not.

Always keep in mind that everyone is working towards a common goal.
At least that’s what I think you have to do because otherwise you’re just pushing papers and you should think of changing jobs  😉