Backups – They Are Needed, Who Knew?

CollaborateImageBackups are essential for a successful business model. That statement may or may spark some topics for debate, but at the end of the day if the data professional does not have a form of backup in place for his/her business needs you may, no you will, feel the pain. It may not happen today, tomorrow, next week, but you can with 100% certainty guarantee that at some point in ones career you will need a backup of your database.

Importance

Let me start off this way and ask a very simple question, “Do I have to take a backup?” The answer to that is yes, yes you do. If you are a data professional than you should care about your data enough to take a backup of it in some form or fashion.

Types of Backups

Full Backup – this type of backup contains all the data for a specific database.

Differential Backup – think about this backup as what it’s name states; contains only the data since it’s last differential base backup; you can find these backups to be smaller in nature versus the full backup methodology

Transaction Log Backup (T-Log Backup) -  this type of backup is a record of all transactions that have been performed against the database since the transaction log was backed up. Most often times these types of backups are taken on a more frequent basis.

**Note** the differential and transaction log backups are both dependent upon the full backup initially being executed.

Disaster Recovery

Depending on how extensive your business model is some companies will rely on backups for their disaster recovery planning. Whether you log ship, utilize always on, restore databases periodically etc. backups can and will always be an essential part of disaster recovery.

Tuning Backups

Most people don’t realize that they can tune their backups. One of the ways you can do this is by turning on some trace flags and increasing some throughput. Below are two statements you can utilize.

DBCC TRACEON (3605, -1) and DBCC TRACEON (3213, -1)

What those two statements do is tell you (in your error log) what the settings are set to

clip_image002

The buffer count and maxtransfersize are the two settings you want to check. Make note of what the settings are initially; then when backing up your database, whether by a stored procedure or method of choice, you can include the following code.

WITH COMPRESSION
, BUFFERCOUNT = 800
, MAXTRANSFERSIZE=4194304

**NOTE – never take code from the web and execute it in production. Utilize this in a testing environment to see how it performs.

This little trick was picked up by watching the mighty Sean McCown at PASS Summit 2014 in one of his sessions.

Testing Backups

Wait, what? You mean I need to test my backups. Let me pose this question another way. If you take a solid backup and you store it for a certain period of time; then how do you know if you can restore it or not? Taking backups are only half the process; I used to think early on in my career that I was golden to have a backup versus the people who don’t take backups at all. Sure that is somewhat true but the flip side to that is I was missing the bigger picture; periodically test your backups. In a perfect world an automated process would restore backups to an isolated environment then fire off an alert if you find one that could not be restored. Most shops don’t or can’t go to that extent so at the minimum periodically test your backups for validity. Not only will it prove that your backups are working but will keep your skill set honed in the restoration process.

Wrap Up

Backups – they are important. As with anything in your data professional career; take this concept to be very important. If you aren’t backing up your data than I suggest you start. If you are backing up your data; then are you sure you can restore it? Are your backups taking forever; perhaps you can tune them? I tell you what…keep reading below and you can check out what some of my colleagues have to say around backups. Enjoy

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

1 Comment

Impact Series – Part 2

ImpactIt’s that time again to add another impact player to the roster; this time for the month of February.

This month the nod goes to none other than Jason Strate (Blog|Twitter). A few years back I sat in on one of Jason’s sessions at PASS Summit. From attending that session I found my way to his blog series called Index Black OPS which helped me tremendously, and I’ve carried some of the methodology since then.

Jason works for Pragmatic Works which is in and of itself a good company; what I’ve seen over the years that resonates with me is an extreme work ethic sprinkled in with some SQL Karaoke madness. A real down to earth guy who has a genuine love for helping people.

On this note I strongly suggest you check out his blog; he has some stellar information over there around several topics:

Don’t just limit reviewing the topics; make sure you check out the resources and publications to.

Like Jason, many SQL family members contribute on a daily basis in sharing their knowledge and helping the community grow. It’s time we (myself included) start paying homage and respect to those that give selflessly day in and day out sacrificing a lot to make our community one of the best there is.

Thanks Jason for being an impact player in our community.

Stay tuned to next month to catch Part 3 in the series of Impact Players.

Leave a comment

Q and A With SQL MVP Chris Shaw

cameraThis has been a long time in the making, back in 2011 I attended a session at PASS Summit by SQL MVP Chris Shaw (B|T). Little did I know that he would become one of my mentors, good friend, and someone I’m truly thankful for. I approached Chris awhile back if he would be interested in doing a question answer session here and without hesitation he said let’s roll. So, without further ado let’s see what the mighty Chris Shaw has to say.

1. SQL Server has many facets to explore; what is one important aspect of SQL that you feel most Data Professionals tend to overlook?

I am not sure if you could call this an aspect in the traditional style.  But I think a lot of data professionals overlook the basics.  Even some of the basics of design like 3rd Normal Form,  what it is, and what are some of the ways it is different from other aspects of database design. Microsoft has done such a great job at making it easier for us to query the data, and faster for us to retrieve the data.  But my first reaction when I look into an OLTP at a 16 table join is to question the design of the database (I recall a day when it was best practice for performance that you didn’t have more than 8 joins).  I am sure there are many example where this is the best way to approach the design, I just can’t help but wonder how much thought went into a design like that.

2. SQL Saturday’s are everywhere and they are an awesome experience to attend them. Can you explain why these SQL Saturday events are so important to the community and what your favorite venue is?

Multiple aspects to this question.  First I think the SQL Saturdays are critical for a couple reasons.  The big one of course is the education that is offered at the event free of charge,  how can you beat that.  But when you look at the community as a whole I think a SQL Saturday gets people ready for A larger event such as the PASS Summit, and knowing what to expect if you have never been.  PASS Summit can be a bit overwhelming as a first timer, all the sessions, all the people and then you have the parties and the events that surround the conference.  How do you take that all in?  How do you network with 200 people on a Saturday event, what can you learn from networking with 200 people that you can apply to 4,000 people over 3 days at the PASS Summit.  Anyone can go the Pass Summit, but if go without understanding how to network, how to get involved with the event, you are missing a lot.  I pose the question to you Chris…  Did you network more during your second Summit or your first?  And was the difference because the second time around you knew what to expect?

“Second time was by far the most networking done compared to the first. Not just attending the dinners but also running into people in the hall, people coming up and talking to me off the cuff, the community zone, and the SQL Runs. I think you are correct when you say the first time you go you really don’t know what to expect. It truly is one of the best SQL conferences I’ve attended.”

I am surprised that PASS doesn’t do some sort of award for the SQL Saturdays.  I have been to 16 of them as a speaker so far and I have got to admit each one has its own flavor to it.  When I organize a SQL Saturday I try to put some sort of flare on the event, something different that you don’t get at the other events.  So SQL Saturdays that do thinks just a little bit different are my favorites.  So if you are going to hold my feet to the fire and really get me to commit to one event.  My favorite event is Kansas City.  They go well out of their way to show appreciation for the speakers.  If you are a speaker this is a can’t miss event,  the Speaker dinner is always special, and the Friday before the BBQ crawl is amazing.  Another one I would mention is the Albuquerque event, they have a very good home town feeling, and I love the home made speaker gift I received.

3. PASS Summit is a big yearly event; can you describe what the “community zone” means and why it is so important?

The community zone is a dedicated area where we as speakers and attendees can gather to just network. I believe they have always had bean bags there and it is a great time to just get off your tired feet and visit with other attendees.  What do you talk about?  Well I had a complex issue that I was working on that became the topic of discussion in the zone for a little while, I was able to come away from the event with an answer to my question.  I also learned that a couple of friends were going through a rough patch in their life, some pretty serious stuff and they still came to the conference.  I hope that I was able to lend an ear for just a while and to be a friend.

4. What’s on Chris Shaw’s book shelf?

Well due to my lifestyle I don’t have enough room for a book shelf (I am a full time camper and live in my rig).  I do however have enough digital media to make many people jealous.  Right now most of my attention when I am not working is spent on photography.  I have a lecture series from a guy name Manny, out of Florida who does this series called “Go Get that Shot”.  I spend a lot of time watching that and re-watching it.   One thing I do have on a very small book shelf is my MVP Award,  something that is very symbolic to me.

5. Most DBA’s don’t start out being a DBA, but they take various routes to get where they are at. Curious how you became involved with SQL?

Ok so this is a really long story, so I am going to shorten it as much as I can.  In 1996 I was the operational director at an antifreeze recycling company.  Our Access database went down and I had to hire a consultant that charged us an arm and a leg.  Well you know when you look at what you pay for things and you say “I am in the wrong business”, well that happened.  I started to learn Access and was talking to a guy who work in the office building next to ours.  After some discussion he told me I was asking questions that were above his skill level and he thought I could help him on this new thing they were doing.  Ended up being SQL Server version 6.0. They made me an offer that was 10k more than what I was making and bought me a personal computer for my home.  That database I designed was for Yellow Pages Inc. and the last time I looked it is still online today. Next time we are in the community zone, ask me and I will give you the full run down.

6. Like you, I was privileged to be a part of the DBA Jumpstart that John Sansom (B|T) put together last year. I’ve learned that the SQL life is a journey not a race; what do you feel a new data professional should consider most in starting out their career?

The chapter I wrote for that was telling people they need to choose between wanting a job and a career.  I am a firm believer that if you are just looking for a job, then being a DBA is not the way you want to go.  This career field requires a lot of dedication, and a lot of work hours.

7. Outside SQL we also have other hobbies; what are some of the things you like to get involved with to relax?

My favorite thing to do is take photos.  Recently I took an extra day with a friend on our way to a SQL Saturday, everyone we were traveling with wanted to go ski, but rather than do that we took a back road to Santa Fe, New Mexico.  We had a great time visiting and stopping every 20 min to go take pictures of stuff we had not seen before.  It was a great time.

8. It’s got to be asked……tacos or barbecue?

Oh that is a hard one.  Today I would have to say…  Tacos for sure.  But when the summer comes, I may be back to Barbecue.

Conclusion

A big thanks to SQL MVP Chris Shaw for taking time out of his busy schedule to drop by and answer a few questions for the SQL Community.

,

1 Comment

TSQL Tuesday #63 – Security

SQL TuesdayThis months T-SQL Tuesday is hosted by none other than Kenneth Fisher (B|T). His topic for this month revolves around security and how you manage security. There probably couldn’t be a more fitting topic; especially with the many breaches we have had lately both ones that are known and ones that are not known.

With that said I want to take this time to expound on a wider variety of topics instead of diving into specific targeted areas within SQL.

When I first heard this topic I immediately drifted to thoughts such as:

  • Afterthought
  • Responsibility
  • Lax
  • Validation
  • Vendor Apps
  • Breaches within
  • Password Strength

Afterthought

Countless times over the years I have seen, reviewed, fixed, and contemplated over security within SQL that simply was an afterthought. Security whether role based, AD Groups, etc. should be worked into any project plan. If you have ever inherited a system only to review that 600 users have sysadmin access you know how detrimental that could be to the data contained within.

Responsibility

Being a DBA means you have great responsibility. Every single database is under your care, own it. Each day someone will be trying to access that database; at least that should be your mentality especially with any production environment.

Lax

A lot of us are creature are habits. It is very easy for a data professional to fall into the trap of becoming accustomed to daily routines. Security should not fall into this category; I repeat security should not fall into this category. Do you know who has access to your databases and why? Do you know what user accounts are tied to specific groups? If you can’t answer this then you may find yourself in this category

Validation

I like this one, how many of us validate our security measures? Do we take any proactive approaches to see just how safe our data is? Maybe you rely on an outside 3rd party to see if they can hack in; whatever the case maybe it would behoove us as a group of data professionals to be actively testing our systems looking for points of entry. I will be completely honest; if you aren’t you can guarantee someone else is.

Vendor Apps

Yes, I am a vendor installing an app your company purchased and we will need sysadmin rights on the box or cluster. Um. yeah you go right ahead – NOT. I hope by now, as a DBA, you have strategies in place where you will work with the vendor directly or have some form of processes that allow for tracking of such activity. Remember, these databases are yours if you maintain them; you be the gate keeper not the other way around. Don’t let anyone on  your system without our knowledge and you better know what kind of data is on your system and who is accessing it.

Breaches Within

If you aren’t careful all your eggs will be in the “protecting from the outside syndrome”. Yes potential threats are rampant from people both stateside and abroad; with that said however have you ever thought about what maybe at risk within your own walls? Do you have safeguards in place for co-workers and fellow employees? Security cannot be just thought of with outside threats. No you need to prepare for both outside and inside threats. To make it even better if you are on a DBA team is your team being audited to keep everyone honest? The data should be your top priority

Password Strength

These little rinky dinky passwords aren’t cutting it guys. Ensure you are following best practices and standards when setting up password strength. The easier you make it the easier it is for threats and breaches to occur. Are the passwords on  your systems set to be changed every so often? But that would require a lot of work – yes and when you sign up to be a DBA or Data Professional you retain great responsibility.

Conclusion

Security is one place where you cannot be lackadaisical about. It is a crucial role within SQL or any platform for that matter that usually becomes an afterthought. If you are in a shop you should review your security guidelines and if you don’t have any I suggest that you take initiative and create some. Without proper security you ones business could be jeopardized and once  issues arise what would become of the companies reputation; or your reputation. Be proactive, make it yours, own it, and get it done.

TSQL Tuesday

This is a block party started by the renowned Adam Machanic (B|T); if you are interested in becoming a host one month and an avid blogger you can reach out to him via the methods above.

,

2 Comments

Friends Of Red Gate 2015

forg-logoI’m excited that I’ve been asked to be back involved for another year in the Friends of Red Gate program. This program allows influential community members, community bloggers,  MVP’s and intuitive minds an inside track on helping with pre-existing and existing utilities that Red Gate has in their arsenal.

I am a Red Gate user and thankful that my shop is a Red Gate shop. The tools that they provided are second to none in the life of a data professional. I always look forward to doing some sessions on some of their products at various events so if you are eager to learn then send me a note.

It’s an honor to be considered for this program and I’m humbled to be able to continue to be associated with a company as Red Gate.

Let’s get 2015 started and start making that sweet SQL music.

Leave a comment

Impact Player Series – Part 1

ImpactI wanted to start this series in regards to impact players that go above and beyond in the SQL Community. Coming from a sports background this resonates well with me and by the end of the year will have a 12 person roster.

I get asked a lot when I go to events, conferences, talks, groups who inspires me. Or I get asked where do I start. There are many fine folks in the SQL Community and I cannot encompass them all; believe me this 12 person team could easily become much more.

So, who the heck is the first impact player? Being that I am from Kentucky it pains me just a little to write this knowing that this person is an SEC rival (college sports) and is a true orange fan in Florida ~ Ed Watson (B|T).

Ed is a Microsoft SQL Server MVP who I have gotten to know over the past year. His tenacity alone has spoke volumes in how he approaches speaking engagements, obstacles, issues, SQL life, basically the things that make up a data professionals life. I would classify Ed as one of those exceptional data professionals that you hear about. If you not ever had the chance to hear him speak at an event I suggest that you keep your ears open and if he comes to a town near you attend ~ you would not be disappointed.

I’ve never one time have come to Ed and him tell me not today; he is always eager to assist and help and has been a strong voice in the community.

He is a frequent blogger over at the SQL Swampland and can be found on twitter; although don’t interrupt him while a University Florida football or basketball game is on. If you choose to do so then you have been warned up front.

Check out Ed’s blog or give him a shout on the twitter feed. Truly a standup guy and one worth following in the community.

While having a Fab Five intact I’m excited to build this out and see where it leads.  Check back next month for Part 2 in the series.

,

Leave a comment

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.

Leave a comment

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: