Tag Archives: dba

A Healthy SQL Server

fitnessT-SQL Tuesday has come and gone and I missed the boat due to some ongoing work constraints. With that said I liked the topic and wanted to do a separate post as I hated to miss it.

Robert Pearl (B|T) was the host for January and I think the topic he chose was spot on with the time of year and well, why not just jump in and get started?

What can you do to make your SQL server healthy?

The theme is broad, and there are plenty of tips and tricks that can be said. I’ll only touch on a few that may be of some use in this upcoming year and hope they can resonate with someone in the community.

PBM\CMS

Policy Based Management and Central Management Server are two useful resources at the data professionals disposal that can aid in a multiple SQL server configuration shop.

PBM allows you to execute a set of standard and custom policies against one or a set of servers allowing you to receive custom daily automated reports. Why not have this at your disposal to see what is going on with your servers before you even get into the office.

CMS allows for a one stop shop of all your servers. One thing I like about CMS is execution of scripts against multiple servers at one time; with that said with much access such as this comes great responsibility and not for the faint of heart. It’s imperative you truly understand what you are working with before getting involved with this but is a great resource to have.

Monitoring

If you aren’t monitoring your servers then why not start today. Some ideas you can take into consideration but not limited to are:

  1. Job notifications on event of failure
  2. Space limitations
  3. Wait Stats
  4. Index Fragmentation
  5. Statistics
  6. User\Login information
  7. General baselines
  8. Backups
  9. New servers brought online
  10. Blocking

Don’t end with these; the intent is to get you to think about what might work for you at your shop.

Backups

I put this topic in here because I wonder how many people are testing their restores? Do you receive notifications in event of backup failures? Trust me on this; don’t be the one to get caught not having a backup or not knowing if your backup works.

Automate, Automate, and Automate

Look at your day to day activities and then ask yourself; can any of these tasks be automated? The idea is to become more efficient and be pro-active instead of re-active.

DBA Standard Database

Do you have a standard DBA database on all the servers that can house your maintenance stored procedures, tasks, server info (yes you need to know what is in your environment), any other pertinent documentation.

Source Control

Is your code source controlled? If not time to get in the game. One good place to start is Red Gate’s Source Control utility

Conclusion

Listen, these are just ideas and not even the tip of the iceberg. The intent is to jump start your mind and think of some possibilities that  you may not already be utilizing.

I sure hated to miss this month’s block party, but that is okay. Time doesn’t always work out in our favor, but we pick ourselves up and move on. Nothing is handed to you; work hard for it. Look at your environment and be that impact player or game changer. You be the one to make the difference.

**Always always always test new things you find on a test environment. Do not put anything straight into production.

Advertisements

Worst Day As A DBA

5Worst_FoRG-banner_613x90_ChrisYates_V1

I remember the day pretty vividly, it was in the summer months and I was as green as green can be coming into the technology field. Walking through the doors to the start of my career  I was ready to tackle the world. The training for new hire sessions had already been completed and it was game time. I didn’t know what a SQL High Five meant at the time but I would have given one to everybody as that is how I was feeling walking through the doors.

The introductions began and I got the normal “new guy”, “fresh meat”, “greenhorn” labels that most people call newbies. As we turned the corner I noticed something that looked a bit off to me. A pen cap stuck in the keyboard by one of the developers. Hmm that is odd, they didn’t teach anything about pen caps in keyboards in college. That person was not at their desk at the time but we did find them in the break room and he was introduced to me as one of the developers.

Time past and the introductions complete I went back to my desk in my little cubicle and couldn’t help but think back to that pen cap. The curiosity was too great so off I go; back over to the desk with the pen cap, mind you this is about an hour later.

Hey man, I got to ask you a question. What’s up with that pen cap?

“Oh, yeah I was building an import process and I forgot and left a MSG box in there. I started to load the files in and instead of stopping it I figured this was a quick way to get through it.”

Hmm, interesting tactic but the red flags and sirens started to go off in my head. Being the new kid on the street and the youngster I went to one of my peers and started poking around a bit. Explaining what I saw I was amazed to learn that this in fact had happened before.

“Before”, little did I know those 6 letters would start to build the foundation of my DBA career. What, wait a minute….”before” you say? Yeah, ole Billy (no not his real name) over there has done that “before”. Nice, so I go back to my desk again and sit down. I take out a pen and paper and start writing down my questions.

1. Where is Billy loading this data?

2. What kind of data is Billy loading?

3. What kind of access does Billy have?

4. Does my boss know this?

5. What method is being used to import the data?

6. Who is the business owner?

Now realize I hadn’t even turned my computer on yet to get the lay of the land. Off I go with my questions.

Um Hey Billy, just out of curiosity where you loading that data? Prod he replies

My heart sunk, I knew the writing on the walls and where this was going. What kind of data are you using? Client data for our system. Back to my desk I go; sit down flip on the computer. I started researching, digging, and sure enough my thoughts were now a reality.

Dev Ops had gone rogue and had access directly to prod. Remember the pen cap; well after realizing that the import was loading more data than the file had in it we discovered the app didn’t have a stopping mechanism and no duplicate checks. In the end we were loading a 100,000 record file 8 times!

Light bulb goes off in my head, as I turn to colleague. Hey where is the last backup? It is on x drive but it won’t do you no good. Why is that I ask; yeah it’s a week old. We run them manually before we leave for the day.

In the end that worst day started off my worst week but looking back I believe that worst day started the groundwork for a solid foundation. How or why is that you ask?

1. Security – I’m a huge proponent of it and probably rightfully so after enduring the major cleanup that ensued.

2. Documentation – no documentation was found anywhere; we all can do a better job of this; me included.

3. Don’t be afraid to speak up; if something is off to you question it. Research it. Dig in and figure it out.

4. Just because something is done one way for years doesn’t mean it is the right way. Evolve and become more efficient. Do you think having a pen cap on an enter key to load data is efficient?

5. If you are a newbie and seasoned vet review your systems on a routine scheduled basis.

6. Backups – are you taking them? If so are you in turn testing them or validating them?

Some of the things I know now that I didn’t then are handy utilities such as Red Gate’s SQL Backup Pro that could have benefited me; take a look at their arsenal for the data professional. A wide range of products that will allow you to streamline your processes and tasks.

I look back on my time there and we brought it so far. We righted the ship but it was no easy task and is not for the faint of heart. It taught me to chip away at the wrongs and turn them to rights. I speak a lot about being a game changer. That means if you see something amiss go after it. Make it right.

While I have had a few “worst” days since then, I’ve learned one thing about being a Data Professional ~ being one comes with a price tag of having great responsibility. Don’t abuse it.

 

5worst_1_300x250_NEW

Trumpet Sounds; Call To Post

ChurchillThis past weekend was the KY Derby. All eyes are on this one day of horse racing; celebrities come in to town in what seems to be a whirlwind two days. According to experts over 100 million dollars is pumped into the local economy.

I get to see all the hoopla firsthand, and throughout the week can walk down the street from the shop and see no telling who walking down the street on their way to the limos, Escalade’s, and the such.

The Race

Then comes the day of the race. The big race where people go to the track spend the day, and then in the evening for 2 minutes, which has been deemed by others as the most exciting 2 minutes in sports, the horses are loaded in and off they go. Some 160k people cheering in the stands and millions of dollars are at stake.

The horses start off in a pack going into turn 1,2, and 3. Jockey’s maneuver their way in the field to position themselves for that final stretch. Turn 4 and horses are in their stride; jockeys kick it into gear and now an all out sprint ensues. Sometimes there is a dead heat; sometimes there is a huge gap, but nonetheless it is a mad dash to the finish.

The DBA Race

As I went throughout the week leading up to the Derby my mind kept wondering to how closely it reminded me of the life of a Data Professional. Being a Data Professional means that you will be in the same space and many other fellow Data Professionals and if you pay attention closely all jockeying for that last leg to the finish line. I’ve kind of stepped back and started looking at how I was running the race.

If you go at an all out sprint in turns 1,2,3 would you really have anything left on turn 4 in the home stretch?

What if you laid back in the field and waited for the opportune time but come to find out you waited to long and the bottleneck wouldn’t give you a clear shot at the end goal?

Or you could be like the one jockey I saw get thrown from their horse during the race.

I’m finding that being a Data Professional and, to be more exact, being a DBA my journey can be at times related to a race. I remember when I first started out how excited you become; just like starting out of the gate. Then, as you go through your journey and you see some of the Data Professionals that you started out with start to fade. Whether they get burned out, choose a different career, move on, or whatever the case may be the numbers start to drop then it is another cycle where new faces come in.

The Call To Post

The trumpeter starts the call and in walk the Data Professionals, as we get ready to prepare to run our race don’t settle for the being average. What are some of the ways when you start out of the gate as a DBA you can do to prepare yourself  for the journey? I’m not sure there is any one right answer but I can share with you what has helped me along the way. It’s not rocket science and it is not something that you can snap your fingers and it be done. If this helps just one aspiring DBA then so be it; it would be worth it.

  • Community – I was late to this game and had I become more involved with the community earlier on in my career I believe it would have helped me more. I’ve already elaborated on my “Fab Five” and if you haven’t read that yet check it out. For me I looked for people in my industry who were at the top of their game and who I knew were what I considered the best of the best. Learning from others in the community is a huge positive and one can garner much knowledge from others if we would do just two things – be still and listen.
  • Initiative – how bad do you want it? If you are expecting someone to hand you success and you are a data professional then you are in the wrong business. Early on in life I was taught discipline and a hard work ethic. If you want it that’s fine; go after it and prove to yourself that you can do it.  One quote I always remember being told to me is that “Somewhere someone is practicing getting better at the skill and goals you want to achieve; how bad do you want it?”
  • The Craft – learn it; live it; breath it. If you are a DBA like myself then what are some of the things that you can do to enhance your shop? Perhaps it is implementing a maintenance strategy around index fragmentation, or is it disaster recovery? Have you restored that backup yet, do you know if you were asked for a restore from 3 days ago could you do it? How about exploring the Plan Cache and getting a feel for how your stored procedures are acting? Oh wait do you know if you have any missing indexes present in your current environment? Speaking of environment have you documented your environments yet? So much work to be done in this are that a paragraph in a blog post doesn’t do it justice.
  • Ask Questions – have you ever been in a session or speaking with a group of people and you wanted to ask a question but didn’t because you thought it would make you appear to not know what you are talking about. Guess what, we’ve all been there. The end of the day every question is valid. If you don’t know then say you don’t know; research and find the answer that is called learning. The challenge is to continually learn; go back up to my Community point. My “Fab-Five” are ones that continually learn and hone their craft and skill set.
  • Don’t be afraid to make mistakes – now don’t take this point and execute something in production that causes your company an outage and money. No that’s not what I’m saying. Think outside the box; test new alternatives and do so in a manner that meets your shops integrity and criteria with environments.  Why wait for someone else to come to a solution; everyone can bring something to the table.

The Roses

Every horse that wins the Derby is dawned with roses over their mane. Guys I don’t know when my race will end, but when that time comes I want people to realize that I gave it my all with integrity, character, honesty, and in a trustworthy manner. I want that developer to be able to come to me and not worry about me chewing his head off (yes that has happened before), or the network guy who can come talk to me about space concerns, heck why not throw in the business unit requesting some help with architect something out. No we (DBAs) aren’t perfect and days will come where we flat out fubarb and make a mess out of it, but guess what? How did you run the race?

Rest assured we’ll get to the finish line one day. Let’s have some fun along the way and realize that we won’t be perfect everyday, run a steady race, and continue to work hard day in and day out. For it is in the face of adversity when true character will shine through.

For those just starting out check out the advice in a collaboration John Sansom made happen that I was honored to be a part of. For those that have been around keep working hard. Hard work will pay off; keep fighting the good fight.

If you could give a DBA just one piece of advice, what would it be?

thinpaperback

This post is part of the SQL Community Project #DBAJumpStart by John Sansom.

“If you could give a DBA just one piece of advice, what would it be?”

John asked 20 successful and experienced SQL Server professionals this exact question. I share my own thoughts with you below and you can find all our answers together inside DBA JumpStart, a unique collection of inspiring content just for SQL Server DBAs. Be sure to get your free copy of DBA JumpStart.

Two Essential Pieces to Your Success Puzzle

“If you could give an aspiring DBA just one piece of advice what would it be?”

This is a question that has stood the test of time; one that has been asked by many aspiring DBA’s on their journey. Some might say that you need to be centralized and good at one technical aspect such as replication, ETL processing, database mirroring, disaster recovery, or log shipping. All the technical aspects that a DBA must learn and endure along their journey are all prevalent and are a must for success, but over my thirteen year career to date I can visualize taking a new DBA and, if they are adaptable, teach them along the way.

For me I see two very important pieces to the puzzle that many miss, few obtain, and those who do are humble in their practice.

Hunger

Most people will look at my title of this section and be thinking, “What in the world is this guy talking about, hunger?” No, I am not speaking of food in a literal sense or being hungry for literal food, but do you have the hunger and the drive to learn?

I’ve been around long enough to know and understand the personas of a lot of the all-knowing DBA’s; do they exist – sure they do but the more prevalent ones I’ve noticed something different about and they are the ones who have a hunger or desire to better their skill set day in and day out.

There will be many days on one’s journey that the DBA will experience frustration as the DBA will wear many hats along the way; however each challenge that presents itself can be viewed as an opportunity to learn and find a resolution. Write your own learning destiny, success doesn’t come handed to you. It is something that you have to want and strive for.

Be Exceptional

You may or may not have heard the term “How do I become an exceptional DBA?”.

Being exceptional is going above and beyond the desired task at hand. This can have a broad meaning; how are your communication skills with others such as the business teams, development teams, release management teams, QA teams? Or when you are faced with an issue do you follow it through to the end or do you wait for someone else to help? Or is there something else noteworthy that in your field you can look on and gauge where you are at professionally?

Being exceptional is, at times, doing things that no one else wants to do. For example, the places I have worked at in my career I like to come in and first thing I ask my boss is, “I’d like to have the things that no one else wants to do”. Guess what, so far a lot of times it has been documentation on the system and processes which uncovers a plethora of information about the environment.

I have been asked by many people how do I get there? How do I achieve being exceptional? Each individual is different and circumstances surrounding that individual are different.

For me, I grew up playing sports and was heavily involved with team consensus at an early age and into college that has carried over into my career – I’ve always tried to surround myself with people who have been through the trials before; having a good team. Today, like so many others, I look up to people I consider top in our industry – the Brent Ozar’s, Paul Randal’s, Glenn Berry’s, John Sansom’s, Chris Shaw’s, John Sterrett’s, and Adam Machanic’s. If you have never gotten involved with the SQL community itself start now. The men and women in our industry share knowledge like no other community and a lot of times it is free. Let me ask you this….have you ever been to a technical blog by any of them, checked out SQL server forums such as SQLServerCentral, or logged onto twitter and followed these people. The wealth of knowledge you can pick up on is phenomenal and some of the free tools they offer so you don’t have to re-invent the wheel such as sp_whoisactive, sp_blitz, or Glenn Berry’s diagnostic queries are outstanding!

Whatever the situation, issue or challenge may be, take a step back and be exceptional. Provide leadership through service, and at times put away our ego and pride and just listen – you will be surprised at what you can pick up on by just being a sponge.

Make a Commitment to Yourself

I ask this question of you today….what is your hunger level and after taking inventory are you striving to be an exceptional DBA, or are you satisfied with just showing up?

Be hungry, be motivated, and be exceptional. I will not sit here and expect you to believe that every day will be a rose garden when dealing with DBA tasks, but what I can guarantee is that each person has the ability to make a difference and impact in their respective environments. It only takes one – will you be that one?

Success Is Journey Not A……

I’ve ofSuccess or Failureten heard the quote “Success is not a journey, but a destination”. Over the course of the last few weeks that quote has taken on a life like form in many fashions. To name a few things that have come to light to make this quote a very real thing are the MCM retiring, having a VMAX overloaded by maintenance plans across the board, upcoming DR Testing, planned Power outages (better than non planned ones eh!).

All these things as I look back on them screams to me that Success is a journey, not a destination. For SQL Data Professionals our jobs are ever changing and growing as we grow with it. Each day is an opportunity to learn something within SQL that you or I didn’t know yesterday, each day presents it’s own opportunities to reach resolutions that you or I didn’t know yesterday, and each day represents that during our failures we can discover the true desire for success.

To the beginner who is starting out who doesn’t know about the sp_configure, the proper use of ORDER BY, or what Table Scan vs. Indexing; the answer is not to just ignore or give up. Dig in and discover what that solution is; it’s how we learn.

To the seasoned SQL Data Professional vet who has been putting off getting on the speaking circuit, taking a newbie under your wing and mentoring them, or simply are just comfortable; challenge yourself. Step outside that normal box and tackle new things head on.

When you have that replication issue that goes awry and you don’t have that big enough wrench to fix it (I use this analogy all the time thanks to Robert Davis a.k.a. SQLSoldier (B|T), stay the course and work your way through it.

I get asked a lot of questions when speaking, email traffic etc., where individuals feel defeated in the problems they are facing within SQL. Every problem has a solution; every solution is waiting to be found it’s up to us as SQL Data Professionals to buckle down and find the answer.

If you are not active in the community I’ve listed some of what I call top tier SQL Data Professionals on the right of this blog site that I follow who have helped me in my DBA career. Forums are also a great place to increase ones learning…to name a few you can check out these:

One last note; when is the last time you just said a simple thanks to someone who has helped you in your career? This past week that has weighed on my mind greatly. A former boss, a mentor, a person who you get newsfeeds from…..I fall in the boat where I get wrapped up in the work I do; I often overlook the simple thank you that in reality goes a long way.

Tackle obstacles, Tackle your fears, and Tackle the solution head one.

Back to Basics

BasicsI can sum up this blog post in two words: The Basics

What do you mean?

I cannot be the only one in this same boat. There have been times where I, as a DBA, have overcomplicated resolutions when there was simply a very non complex answer. Come on, you know what I’m referring to – the basics. In looking up the exact meaning of what basic says; the dictionary tells me it is the “fundamental or basic principal”. I took that meaning this week and looked deeper into how I attack some DBA related items and I keep coming back to – “keep it simple” and “get back to the basics”.

The Basics

When I say basics I’m coming at it from a DBA standpoint. Do you have your own checklist that you go by for daily, monthly, quarterly, yearly checks? If not, then this is as good as time as any to start.

What are some of the things to include in your basic checklist?

Some items to include, but not limited to are:

  • Backup processes and alerting upon failure
  • Review of jobs (if any have failed)
  • Anything in the SQL Error Logs?
  • If you use Policy Based Management (PBM) and receive reports – any action to take?
  • Security logs – have you checked?
  • If issues are found how do you handle them? Just don’t sweep them under the rug
  • Hopefully you have something in place that tells you when you are running low on space (storage)….if not might want to start thinking about getting something implemented; by the way how is the space looking?
  • You’re fragmentation process for indexes working properly?
  • Remember those backups you checked? You spot checking any restores to validate them at all?

Summary

The list above is just a simple list to get started with….get back to the basics of DBA work. It’s a fun and enjoyable ride, but keep one thing in mind as you traverse through the SQL terrain not to overcomplicate things. You will find many checklists on the web by some renowned DBA’s that one could model a standard off of if you don’t already have one. I’m a big believer in automation and automate what you can to help you become more efficient and streamlined. Get the reports emailed to you when you start your day, at best make sure notifications are set up on the jobs running in case of failure.

Keep it simple, get some standards in place, above all remember the basic principals. Don’t overcomplicate issues and when you run across them don’t sweep them under the rug and wait for the next DBA to come along to fix them; take the time to fix them correctly.

Being Hectic With The Thrill

thrillIt happens every time anyone goes to an amusement park. The one park that sticks out to me is Disneyworld just because I was there on a recent family vacation. You know what I’m talking about – the crazy rides, the masses of people, the running to and fro. Vacation is supposed to be a relaxing time yet when you get into a park of any sort, at times, one can get caught up with the hustling and bustling – I know I’ve been there done that. While being on vacation a couple of things stuck out to me so much so that I think it does apply to the DBA life or any data professional for that matter. Below is a representation of what thoughts flowed through my head.

The Hectic

Have you ever noticed when you are in an amusement park people are rushing around as fast as they can to get to the next ride before the other person? While on vacation I was enamored to watch the bustling and running of people almost to the point of it being comical. I would watch the dad’s pushing the double wide strollers around basically at a run with the two kids in the stroller and one on his shoulders as they rushed to the next ride. The families that would run as soon as the park opens to get the famous “fast pass” to secure a time frame that they would not have to wait in line for hours on end.

How does that pertain to the DBA life? Easily enough the DBA life can at times be very hectic with several things going on all around them. It is just not one thing we focus on but a multitude of things that more times than not seem to go awry all at once. How do you react when those situations arise?

The Thrill

A long with the hectic aspect comes the thrill. You get to the that ride you’ve been waiting to ride and you’re next. You get on the ride and off you go down an 80 ft. drop or into that dark tunnel you never think  you’ll come out of. The unknown aspects and the thrill of the ride keeps you coming back for more.

As I am a SQL DBA, I think that same thought process can be applied. The thrill of solving that problem that has been unknown, working with other teams to find common ground to push forward on a project, getting that new index in place that takes a process running 45 minutes down to 10 seconds. It’s the thrill of the game that keeps you coming back for more.

The End Game

Have you taken inventory lately? When you get to work are you excited about your work? Do you always have that continuous desire to improve yourself as a data professional? Is that thrill still there like when you solved your first issue and you said, “I think I can do this!” If not recapture that fire you once had – enjoy what you do. There are a lot of people that would love to have the opportunities that we as data professionals have. Enjoy the time you have with it!