Mitch's Pitch

The copy-only backup is actually a very important tool in all production DBA’s arsenal. A copy only backup is essentially a ‘snapshot’ of the database at that point in time that is completely independent from any sequencing of backups used for a restore.

Why is it important some might ask, well, let me explain. Almost all production SQL environments have some sort of back up strategy which includes some combination of full backups, differential backups and/or Transactional log backups. For instance, a full backup is taken on a database every weekend. This creates a starting reference point for a restore.  After the full backup, differential backups are taken nightly with additional transaction log (TLog) backups taken at specified intervals throughout the day to create what is called a log chain. All of these differential and TLog backups are tied directly to the preceding full backup. A Log Sequence Number (LSN) is created to keep all the backups in order. When a restore is required, the full backup must be restored with all the proper differential and TLog backups. If one is missing, the restore will fail. The important thing here is that after a full backup, all of the differential and TLog backups following it are tied directly to that Full backup.

So, assuming we are all very organized and keep all of our backups automated and documented properly, we should be able to perform a point in time restore without issue. But what happens if one of your co-workers takes an AdHoc full backup of a database to restore to a development environment without telling anyone. Later that day you need to restore the database to a point in time after the AdHoc back up was taken by your co-worker? The restore will fail since the Log Chain was broke by the AdHoc full backup and the LSN’s are no longer in sync with the original full backup. Basically, any TLog backup taken after the AdHoc backup will now be associated to that backup.  In this case, had the person simply taken the AdHoc backup using the Copy-Only option, there wouldn’t have been any issues with the restore since the chain of backups wouldn’t have been broken.

So how do we take a copy-only backup of a database? It is as simple as just adding a ‘with’  statement to the backup command as seen below.

– Create full backup with Copy Only option
BACKUP DATABASE MyDataBase
TO DISK = ‘D:\MyDatabase_CopyOnly.bak’
WITH COPY_ONLY
GO

The copy-only feature has been available since SQL 2005, but had to be written in the T-SQL backup statement. In SQL2008, the Copy-Only feature is available in the GUI for ease of use.

Keep in mind that the copy only feature is not available for Differential backups and the option in the GUI will actually be ‘greyed’ out.  The copy-only feature should always
be considered for any AdHoc backup that is not part of the normal backup schedule in your environment.

I Just got back from Orange County on Sunday and SQL Saturday #120. It was a Great event with tons of people and over 50 sessions. I heard the final count of attendees was around 250 people. There were 8 tracks with 6 time slots in each, not to mention the 3 lunch time presentations. There was never a dull moment at the event.

I gave 2 sessions, the first one on Networking and Self-promotion in the community. I was excited to have it as one of the first sessions of the day, I tried to focus on networking and meeting people in the SQL community, specifically that event. I had a good amount of people in the session and lots of good questions and discussions. I really tried to encourage
people to go out of their way to meet new people and exchange business cards. The only thing I forgot to mention is to take those business cards and follow up with people through twitter or LinkedIn.

My second session was on Central Management Server and Policy Based Management. It never surprises me that no matter how many times I give this presentation, I always get new questions. I started my session by asking how many people have ever used PBM and to my surprise, only 2 people in the whole room. This might discourage some people, but I see it as a great opportunity to really teach something brand new to people who are interested in the subject. I met some great people in the session and look forward to talking with them again in the future.

By lunch time, all of my sessions were done. I spent the rest of the day hoping from room to room trying to catch some good sessions. Caught one session by Eddie Wuerch (t|b)on Temp DB Tuning and one by Warren Sifre (t|b) on AlwaysOn in SQL 2012. I managed to take away quite a bit from these sessions and look forward to using that info in the future.

The after party was great. It was a very laid back, old school sports bar that had a huge section for all of geeks to sit around and talk about geek stuff, have some beers and continue to network with each other. I am already looking forward to next years event.

Huge thanks to Andrew Karcher (t|b) for the event and all the great info for our upcoming SQL Saturday 144 here in Sacramento, CA!!

Have you ever been tasked with writing up documentation for a task you perform so people can just step in while you’re out? Almost all of us have. I have created both written instructions as well as in-depth instructions with pictures of every step. It can be tiresome to do this, but I just discovered an easier way to do it. In the past, I have always used Snag-it and have been very pleased with it. However, it can become a pain to stop at every step, take a snapshot, save it and then continue. Well, now I don’t have to. Enter Problem Steps Recorder! Maybe this has been around for a while and I just didn’t know about it, but using the Microsoft built in Problem Steps Recorder is super easy!

If you go to your windows menu, type in PSR and hit enter:

You will see this window pop up.

All you have to do is hit Start Record and it will record a snapshot of every click you make as well as describes everything the user does (the best it can).

Problem Step 1: (3/19/2012 9:58:45 AM) User mouse drag end on “How do I use Problem Steps Recorder? (pane)” in “How do I use Problem Steps Recorder? – Windows Internet Explorer”

A few things to remember with PSR:

* Make sure you minimize the Problem Steps Recorder, otherwise it will be in every snapshot taken.
*By default, the Problem Steps Recorder has a limit to 20 clicks/snapshots. You will have to change the settings for PSR and set it to 100, which is the max.

*Make sure you stop it when you are done, otherwise things will get overwritten. It is a rolling window of snapshots, so at your 101^st click; you will lose your 1^st one.
*Make sure you are running it as an administrator!
*PSR will not record typing, but it does give you the ability to add comments by clicking on the Add Comment.

Hopefully this helps you out and saves some time with documentation and problem solving.

I have been seeing a lot of chatter lately on twitter about the new ACE program recruitment and people showing a lot of interest in it. This is great! I can’t express enough how awesome this program is and encourage everyone to apply for it.

I am coming up on the closure of my active part of the Idera Advisor and Community Educator (ACE) program. Looking back at what I have done and accomplished in the last 9
months has been amazing. I have met and networked with people I never thought I would have, presented to people I never thought I would and I am learning new things that I never thought I would get the opportunity to learn. If you would have asked me a year ago, where I would want to be in 2012, I would have told you out of technology all together. I was getting to the point that I wasn’t enjoying myself and my work anymore. I felt like the life was being sucked out of me, mainly due to where I work. I was turning into exactly what I didn’t want to become; an average everyday DBA with no desire to achieve more. I had two major turning points in my career in the last year, the first being the SQL Cruise I attended with Tim Ford (b|t) and Brent Ozar (b|t) and the second, being selected for the ACE program. Since that time, I have found a new born energy and love for SQL that I never knew I had. In all actuality, I think I have learned more in the last year than I had in the previous five.

Now that my time as an ACE is starting to wind down, I thought it might be a good idea to
tell people who are curious, exactly what I have gotten out of this program thus far. Since its inception, I have had the opportunity to work with 5 other extraordinary people; people I might have felt were untouchable or unreachable in the past. I was not a tweeter, blogger, researcher or expert in anything (at least I didn’t think so). With the help of this program, I have gotten outside my comfort zone and forced myself to meet new people and try new things. I have never been afraid of speaking in front of people or making a fool of myself (as was evident with my SQL Cruise video). My philosophy has always been that you don’t need to be the smartest person in the room, but rather the best at finding the right answers. We wouldn’t be blogging and talking on twitter if we didn’t want to learn and better ourselves. There are only a few people in this community that I think DO know almost everything, but the rest of us are still trying to learn and keep up to date with the constantly changing technologies.

With the ACE program, I have been able to travel to different parts of the country to attend
and present at SQL Saturdays. Not all of us have travel budgets that allow us to get out to other parts of the country like this.  I have made a ton of great connections in many different states now. I have new SQL friends in Colorado, Utah, Washington, Florida and Oregon that if it wasn’t for this program, I would never have met. I have grown my SQL Family into something I am proud of and can depend on. I have a new found love and faith in the SQL community and what it stands for.

If you are on the fence about applying to become and ACE, I would be more than happy to
push you in the right direction. What is the worst that can happen? You don’t get the spot? You name is now in the pot for future ACE spots. The last year has been filled with a ton of new experiences and new beginnings for me. You will never achieve greatness if you don’t at least try!

Get in on the ACE recruitment period, you won’t regret it!

After just finishing up my first ever SQL Risk Assessment Program (SQLRAP), I felt it would be a good time to do a little critiquing on it. Most people don’t even know what a SQLRAP is, so let me start by explaining that first. No, I am not referring to an actual SQL Rap like this. However, that did give me a good laugh this morning. Rather, it is the Risk Assessment Program offered from Microsoft through their premier support. There is a whole variety of RAPs for all different flavors of MS products.  It is a package deal where you get an assessment application/program, training and knowledge transfer. What happens is a Microsoft SQL expert comes on-site and shows the application they have developed to pull perfmon counters, ask questions and do a general baseline and best practices check on a SQL server. At the end, a bunch of nifty reports and a PowerPoint
presentation are given to everyone (whether interested or not) to show the overall risk and issues that need to be followed up on. At this point, you get a license for 18 months that can only be used to gather stats on a single instance in your environment.

What did I take away from this three day engagement?

A few things I guess. I wasn’t really sure what the SQLRAP engagement was going to entail, so I tried to keep an open mind about it. The MS SQL expert we had on-site was good. He knew his stuff and was not here to try and sell us on anything else (which is really what I expected). He did a great job with explaining things to make sure everyone understood. The RAP was performed on our newly (rushed) installed SQL2008 R2 cluster. Come to find out the cluster install and configuration was pretty much flawless, which
is awesome. The first new item had to deal with the server power options mode and how to set it for high performance rather than power saving which ‘can’ increase performance. Is this a game changer, probably not and in fact, based on our current run performance, he estimated no change at all. The other new recommendation is for snapshot isolation to be enabled. Our SQL rep did a great job explaining the different kinds of locks and how dirty reads occur with our development team. It was good to have the developers in the room to take it all in. Since this application was developed long ago, many coding practices have evolved and many new techniques are not well known.

The other issues that were found were all known. Things like missing recent security patches, some security best practices that were not followed, compatibility level not set to 100 (I claimed fault on this one since I was trying to get out the door to Tahoe the day I did the migration and just missed the step), no documented Disaster Recovery plans, SLA’s, OLA’s and a few high waits for CXPACKET and ASYNC_NETWORK_IO. All of which we can fix with some fine tuning of problematic queries (hopefully!!!)  and some balancing the MaxDOP setting we currently have set at 0 for a 16 proc server.

Really, there were only a handful of issues that needed to be addressed. Overall, our SQLRAP was below the customer average for issues normally experience in these engagements with Microsoft which was exciting for us. I think it was a good experience, but without knowing the cost, I can’t say that it was completely worth it yet. Most of the issues that came up were either already documented by our team almost a year ago. I guess the biggest benefit was that Microsoft validated to our senior management what
we have been saying for the last 10 months. The only difference is that it is now coming from the mouth of the Microsoft experts which seems to hold a lot more value to some; however, this is a whole other issue. I can only hope that this will add more creditability to us in the future as experts in SQL Server.

What I didn’t like about it?

I didn’t like the fact that if you want to use the tool for other instance, you can’t just purchase licenses for more, rather you have to create a whole new engagement, bring an MS rep on-site for 3+ days, just to go over all the same stuff you did for the first one. Seems like a waste of time and money for the customer. If you really want to get an overview of best practices on your server, there are plenty of SQL help sites and white papers out there to review. I think it would be more beneficial to bring someone on-site to help with performance tuning instead. This engagement barely scraped the surface of tuning the SQL code and focused more on the server/instance configurations. If there is a next time, it think it would be beneficial to lay out the ground work with MS first by saying  “here are our current known issues, now tell us something we don’t know” and hope that it will make them dig a little deeper and maybe use the resources in a more efficient manner while on-site.

All in all, this process is only worthwhile if you take the recommendations and do something with them. If no recommended changes are implemented, then having a baseline really won’t do any good if no changes are made to try and improve that baseline performance.

We have now entered a new year and according to the Mayan calendar, it could be the last. So I decided to set some goals, both easy and aggressive. I have decided to mix and mingle both personal and professional. I will leave it up to you to decide which is which. I figure if I put all of this out on the Interwebz, I might actually be held accountable for it all. So here
it is…

One SQL Saturday per Quarter – Attend and speak at one SQL Saturday per quarter this year. Of course, being in the Idera Ace program makes this easy for the first 2 quarters, then it will be up to me when Idera dumps us for a group of great new Ace’s. BTW, Idera has been awesome with this entire ACE program; I cannot express enough gratitude to them for all they have done for us.

HAM Radio License – This is 2 fold. I figure if I am going to truly embrace my geek-ness, I mine as well go all the way, get my HAM (short wave radio) license, build a 100ft antenna in my backyard and start talking with other people across the globe. It will also come in handy with my search and rescue efforts with Search and Rescue activities

Marathon by May – Never in my life have I wanted to run a marathon. However, after attending the SQL Run at PASS and listening to what people talk about on twitter, I figure, why not. If for nothing else, it will force me to get back into shape. I hope to do this by my birthday in May.

SQL Saturday in Sacramento – I have been working with some great SQL professionals here in the Sacramento to try and plan a SQL Saturday here. We are hoping to have some really exciting news in the first or early second quarter of this year. Keep your eyes peeled for the upcoming event.

SQL DBA Certification – I will, finally get off my rear and get my SQL DBA certs completed this year. Of course, by the time I get them, it will be time to upgrade to SQL 2012. Anyone think it might be a better idea to just wait and do the 2012 exams? Wait, don’t answer that, it just gives me more reason to procrastinate. Why not get them all anyways!

A Blog a Week – Is this too much to ask? It is only 52. The key is getting a good list of ideas, start writing and create a backlog of posts. The last few months of 2011 were not very productive for me on the blogging front. I am hoping to really kick this year up a notch and get some good posts out. Everything from real life lessons to some general things that I have researched for my own knowledge or studies.

Partner with RDP Streamline – This is big for me. I am really looking forward to trying to create a good partnership with RDP Streamline this year. Angel Abundez (t|b), has got a great business plan set up and I am hoping we can do some great things this year together.

50 Mile Endurance Run – Why stop at 26.2 miles. I would already be over half way to 50 miles. Go big or go home! I found a 50 mile endurance run in the Auburn, CA area in September. Dare me? Lol.

I am sure there will be more, but this was my first pass at goal setting for the year.

Well, 2011 is over and 2012 is here and I am welcoming it with open arms! This last month of 2011 has been crazy. Where do I start…

How about getting thrown into 2 major projects with super tight, end of the year deadlines and both claiming to have priority over the other. I felt like I was in new Barbie doll on Christmas that my girls were fighting over (Thank goodness is still have both my arms, unlike her). One of the projects included building out 2 clustered virtual environments, one 2005 and one 2008 in a few days. I got to say that the cluster install on 2008 is a
major improvement to 2005! Once I had them built out and configured, we discovered that the max processor’s allowed on the current version of VM we had were 8. After load testing, it was decided that this wasn’t going to fly and 16 procs, which was what had been originally spec’d in the design documents (excellent research work done here), was required. So a brand new physical environment had to be built. Not sure where the servers came from, but we got the physical servers up and running in short time, rebuilt and configured with about 2 days left for testing. I spent a good part of a week assisting the developers in troubleshooting application and BizTalk connectivity errors. Every log they showed me had the letters S-Q-L in it, so they immediately thought it was SQL errors and I had a bad configuration in the cluster. In the end, it was firewall misconfiguration. All in all, we got the system up and running on the 29^th, 2 days before the deadline which allowed us to be on-call, rather than on-site for the New Year’s weekend. The other project, due to a losing legal battle, had to be created from the ground up in 2 weeks. Thankfully, most of the work was on the web/app front end, so all I had to do was merge data from 4 different sources into a few different tables. The problem was the output was only as good as the input and needless to say the comma delimited files for input were as clean as a newborns diaper. It took a while to find the data issues, but once I did, the data loading process went smooth.

On top of all of this, one of my peers, who happens to be my other technical cohort in all of this, ups and leaves for India for a month (well deserved trip) and left me to deal with all of this. Keep in mind that had the tables been turned, I would have left too!  My manager went on vacation for 2 weeks as well and put me in charge of any issues that arose in her absence. That left me here alone to administer 180+ SQL instances, hosting 850+ DB’s and the entire daily and weekly maintenance routines as well, not to mention trouble tickets, code reviews, code promotions and any other issues that came up.

Needless to say, my blogging, twitter’ing, emailing, reading and general personal life went out the window. The big bonus was the weeks went by incredibly fast and I got my PASS Summit dvd’s as a reward! In all honesty, I enjoyed the months’ workload.  I think it is important to show that even when down a man/woman, you can step up to the challenge, fill the voids as needed, make the deadlines and still, for the most part, enjoy the work that you do.

So what did I learn for all of this? Be prepared for anything, accept new challenges and use them to learn and always get your vacation request in early (or earlier than you coworkers).

Oh, and I won the office mustache contest! Now, back to blogging.

Since I got back from SQL Pass in October, it seems like I have barely had time to breathe. I have been having a really hard time balancing work, consulting, blogging and family. I understand that this time of year, our schedules really begin to fill up quickly and I have been falling behind on a of lot activities. I was trying to accomplish too many things in
too short of a time. My general rule of thumb is that my family will always come first. My family is the most important thing in the world to me. I can always find another job, but I can’t find another family. However, coming home at night worrying about what I didn’t finish that day was getting old.

I started thinking; most of us SQL Admins have daily checklists that we use when we get to work first thing in the morning to check our environments. Why not do this for myself, but on a more extended time frame.  So I started making a list of what I wanted to accomplish over the next 3-6 months. Once I had it all written down, I started to prioritize the items based on what was most important, what had hard deadlines and what I would like to do. Once I had them prioritized, I started to break them down into what I can accomplish
quickly and what is going to take a significant amount of time. I figured that if I can complete one easy thing a day from the list on top of everything else that pops up during the normal work day, I would at least leave with a sense of accomplishment for that day. However, I also wanted to try and chip away at some of the bigger and more time consuming projects.

I have since been keeping track of everything I work on during the day so when I come in the next morning, I know exactly where I left off the day before and can quickly jump back in. This has really forced me to stay on task and not get as distracted by everyday issues. If something new comes up, I add it to the list and re-prioritize if needed.

I feel like now I am better able to balance my work day and not go home at the end of the day feeling stressed and worried about what I need to do the next day.

What better way to come back from PASS than to sit in a room with 13 others for 6 days listening to attorneys argue about a something that happened over 3 years ago. Finally I am free from my civic duty as a Juror and can get back to things that are important to me like family, work and blogging.

As I was sitting in the court room, I couldn’t help but notice the way the attorneys, bailiff’s, court reporters, the judge and the jury members were all so respectful of one another. It made me think, why can’t this kind of respect be rolled over to the IT world and into our meetings and everyday tasks? I have been to way too many meetings lately where co-workers feel the need to use vulgarity and loud voices to get their point across when quite frankly; it just makes them look dumb. If you can’t get your point across by just articulating the facts and your findings, then sit in the corner of the room and let someone else talk. I am not saying that I am a saint, I often times talk like a sailor (ask my wife), but I try not to do it in meetings. Especially in meetings where there are people that I might not know who they are or what their role is. It is very easy to be overly passionate about something and let your mouth run in a direction that you may regret later. I am not saying that you shouldn’t stand up for your ideas or just rollover, but rather deliver them in a manner that is respectful and not demeaning to others. My goal is always to leave a positive impression on people so they know they can approach me, ask me a question and no matter how dumb it might be, still walk away with their head and all limbs intact. Cooler heads will always prevail. Keep this in mind next time you are in a meeting.

SQL Saturday 92 in Portland, Oregon was Awesome. Not only was I excited to speak, but I also managed to meet a ton of great people. The sessions were awesome, the networking unbelievable and the overall organization of the event was outstanding. Arnie Rowland (t|b) and his Portland contingent did such a fantastic job of organizing not only the sessions, but the speaker dinner and after party were incredible as well. I think everyone there learned a lot in addition to meeting some great new friends in the SQL community.

I started off my Saturday by catching a bus in the wrong direction and ended up in, what I was told as, the worst part of Portland. By the time I realized my mistake, I was already 30 blocks in the wrong direction. Long story short, what should have taken me 40 minutes to get to, ended up taking me almost 2 hours.

Anyways, I made it just in time to hear Harry Chandra (t) speak about mirroring and log shipping. It was his first SQL Saturday as a presenter and he killed it. He gave great overviews on both topics and led a great Q&A session on them.

The second session was hard to choose, but I decided to step in and listen to Buck Woody (t|b) talk about SQL Azure. If you have never heard Buck talk, you need to sit in one of his sessions. Not only is the information super valuable, but the non-stop comedy sessions would make even the boring-est of topics interesting.

The Women in Technology (WIT) session was a packed house and chalk full ofgreat dialogue between the audience and the WIT panel. Jes Borland (t|b), Yanni Robel (t|b), and Kendra Little (t|b) did a great job of discussing subjects of interest and engaging the crowd in discussions.

After lunch I presented, to a much larger crowd than I expected, on self-promotion in your job and in the community. It turned out to be a great session with lots of great discussions on blogging, white-papers, social media and tweeting. All the attendees seemed to be very receptive. Thanks again to everyone that attended, you can download the presentation file here.

My last session of the day was Robert Davis (t|b), fellow Idera Ace, and his discussion on the top 10 things all BI administrators should know. He had some really great commentary with regards to implementing SSIS and SSAS in a production environment. Got a good list of items to look at when I get home.

The after party was great. We all went to Duff’s Garage for some appetizers and drinks. It was a very informal and wonderful opportunity to meet and network with a lot of SQL folks. Overall, the Portland SQL Saturday was a fantastic event. I am really looking forward to, hopefully, being invited back to the next one.