Archive for the ‘SQLServerPedia Syndication’ Category

Are you testing your backups, If so how? Sure there is a couple options that you can use to help validate backups but does this mean you are 100% sure without a doubt that it can be restored? Have you ever seen a backup file not restore?

Last week I was working on a server that was having a little bit of a storage issue, the vendor was pretty sure after we took a couple steps that the storage would return to normal behavior. Management was not satisfied with the vendors forecast of pretty sure, and as I side note I could not agree more with them. Management knew that a restore process was going to take some time, so rather than wait for the maintenance window when the storage fix was going to be attempted a decision was made to start a restore on a different server. This restore was being done as a just in case… Just in case the fix on the storage side did not work, and they lost a database. So I was off and running with a restore database task.

About two hours into the restore I received an error that stated unexpected end of file. I wasn’t too sure what exactly caused the short file, but I did know it did not sound good, and I could not restore from it. After I did some research it looked like the backup file I was restoring from was much smaller than the backup file SQL Server had created a few days before.

So here is my point…

It does not matter what you do on the backup side of the process, if you do not restore the file, you are at risk of having a bad backup file and then what?

It is good practice to restore your backups on a frequent basis, I may even be as bold as to say that you may want to restore each backup just to verify it. Either way, when you restore these backups it may not be good enough to copy the file to another server and then restore that backup. When you do a test restore you should test it from end to end. This means if you store your backups off-site then request a backup from that off site location and restore that file. If you restore your files with that methodology, not only are you testing that SQL Server created a good backup file, but that all the other pieces work as well. I like to back up and keep only one week’s worth of backups on local storage. As soon as the backup is done, it either goes to tape or another data center. So there are a number of pieces in that backup process, those are the same pieces that are part of the restore process.

Do yourself a favor, test your backups. Do this by restoring your databases from end to end.

A couple quick notes…

I found some interesting statistics on data loss and did a short post on why these simple numbers enforce the need to complete regular Security Audits on your databases. Based on the numbers I found, completing a security audit and making appropriate changes a company can limit its risk by nearly 50%, just by following best practices internally to your organization. I hope you find it of interest, you can find the full post here on Xtivia’s blog.

My first presentation at the PASS Summit somewhere around 8 years ago was all about how to hire a DBA and how to be hired as a DBA. It was received really well and I was really amazed by the feedback. This past November in Seattle at the PASS Summit I was presented an opportunity to present on some interviewing tips. I am excited to say that on March 13th, I will be presenting this for the PASS Professional Development Virtual Chapter. The great thing about these chapter meetings is that you don’t have to leave the office or your home to attend the event, because it is all on line and it is free to attend. If you would like more information or would like to attend, you can do that here. I hope to see you there.

I started a series of blog posts a few weeks ago where I wanted to focus on my lifestyle and how it relates back to work and SQL Server. The short of it is that I live and work out of my 40 foot camper, and I wouldn’t have it any other way. In my first post I was really curious on how many people really wanted to hear more about this topic and what sort of information did people want to get from it. I was overwhelmed by the number of people that responded and the questions that came out of it. So for my first follow up post, I want to directly address Brent Ozar’s (T|B) questions. Here is a portion of what Brent had to say:

“How often do you hook up the trailer and move to another site?

Are you able to focus on work when your wife’s in the trailer, too? I have a tough time telecommuting when I can see/hear Erika, so it works really well when she’s at her day job. Once she’s home, I have to quit (or head out to a coffee shop).”

Well the first question is pretty easy to answer, or at least up to this point. How often do we move, well at this point it is only twice a year. We have what I am calling is my home base, that is Mountaindale located just south of Colorado Springs, Co. The campground itself has a really awesome history, and there is so much to just see and do, but that is a post for another time. This winter we choose to go Blythe, Ca. to a place that used to be a KOA and is now called the RivieraRVResort. We came here in the Fall about mid October, and we will head back to our home base in Colorado by April 1st. For the future, well not too sure yet. There have been some challenges with coming to Blythe, the biggest one as it relates to working from home is the internet connection. The service out here is done by accompany called SuddenLink. I have had non-stop issues with them not being able to keep my connection on-line and at the speed I pay for. In the future, I am going to have to do a lot more in depth research on internet connections before we set out on a new destination.

Brent’s second question is the one that I have really be thinking about a lot. And when I say a lot, I mean really thinking about it. One of the reasons that this questions was so hard was I really felt that for me to answer it properly I need to do a very truthful evaluation of myself.

Let me start by saying that the only way that this lifestyle works for me and my professional career, is get as much done here at home or in the camper, as I would sitting in an office. It sounds like a pretty easy thing to do, if you get a certain amount of work done in one location, no matter how you determine to measure it, that same amount if not more needs to be completed when I work remotely. I don’t think I am having too much difficulty doing that, I do think that I get more done when I work remote than when I am in the office. There are a few issues that I am working on solutions for, such as hallway architectures. When you are in an office, often you will get stopped in the hall, or in the break room, and discussions are had. Some of these are really valuable. I am working on this and I think I am making headway, there are a lot of shorter emails, instance messages, and phone calls then I had originally planned for. To directly answer the question that Brent asked, do I get distracted. You bet, there are times when I have to go kill the occasional spider, or help move a lizard out of the way so he doesn’t get hurt. Just a few nights ago, I was working on something and we heard a loud hoot noise, I spent the next hour running around watching a great horned owl. Even with those distractions, I find that I am spending more time in front of a computer than I ever have before. My concentration level has gone up, and I have been able to find the time to get some of those projects done that I have been wanting to do for years.

With that being said… I find there is a big swing in the other direction of this discussion. Because we are so limited on space, the kitchen table is my office, I have my laptop there and my wife has her laptop there. When it is 8:00 PM and dinner is done, the dishes have been cleaned and some mind numbing TV show is sucking my brain cells dry, I can see my laptop not 20 feet from me. If you are at all like I am, when you have a problem or challenge that you are working on, it is always there on your mind. It sits there when you are eating, going to sleep and even when I should be relaxing. These are the moments that distract me the most. I can see the laptop, the ideas are flowing, and it is just so easy to walk by the computer, sit down and start working on something. Next think I know, I have been sitting in front of my computer for 3 hours, the wife has gone to bed, the dog wants to go for a walk and the precious me time is gone. I think my outside of work life is impacted more than my work like.

There is a balance for sure, I have to make sure that I take time to go out to dinner, or go to the local race track for some entertainment. I have started taking a lot of pictures, and I want to really grow in the hobby, but this means that I need to set specific time for work and then for me and for my wife. The nature of what I do for a living will always have me working around the clock at time, this is something we know and expect, it happens sometimes I have to really focus to make sure that I know it is ok that if I got a good days’ worth of work in that I need to stop working, and start doing other things. When you are in an office, and you leave for the day, sure you may go home and fire up the laptop, but that specific action of leaving the office is a great mental separator between work and home. So far I think I am going well with managing this balance. Could I be better, sure. These distractions, go both ways for me. I am sure they do for Brent as well.

I was poking around today on a new tool that I have been using, Remote Desktop Manager by Devolutions. (I posted a blog about it a few months back if you are interested in seeing it.)

My Issue

I have this one server that process information and it has a critical role in the success of the company. If the database is not performing well and data is not processing as fast as it should be, there are some SLA’s with customers that might not be achieved. In normal day to day business I rely on a stored procedure that I execute in my dbutilities database, the procedure gets the status, records it and then evaluates if I need to be notified. There are times however, I am notified that something may not be moving as fast as it could and I should check on it. My reaction is to connect to the server, and run my procedure that checks on the status. With time being a critical component of this, I have found that if I was not already connected that my response is delayed due to creating a connection.

My Solution

There are a number of things that I could do to make this a quick and easy check on my server. SQL Server wise all I am doing is calling a procedure. So I could use many tools to complete the task at hand. However, I spend more of my day going from server to server and I am already working in the Remote Desktop Manager. So I thought it would be really cool if I had a link there that would give me the shortcut to the information I was looking for. I was surprised at how much of a shortcut this turned out to be.

First I created a new session. Under the “Other” section I found a Data Report link. I figured I would give it a shot.

In this screen shot case, you will notice a couple things. In the query section for security reasons I have created a very generic stored procedure in my test database that looks like this:

USE
MSDB

GO

 

Create
Procedure
EnabledJobs

AS

    Select
Name
from
sysjobs_view

    Where
enabled
= 1

 

I will call that stored procedure as an example.

Once I had selected the Data Report a new box appeared with all the info I needed to make my connection. I named the session, created a new group for it to reside and even changed the default image so I could tell that this was a report that I used on-demand not for my day to day checks. The server connection dialog box is the standard connection sting information that you may have seen many times before, In the Query section I added the execution of the stored procedure, in the case of the example it is called EnabledJobs.

The finished output?

 

I now have a new session in my tree, in a folder that I created. Each time I start the session, the window displays the results of the query. In this case it took me less than a second to connect to the server, run the stored procedure and get the results I needed. Meaning that in the original case that I started with, I either know I have an issue I need to work on, or I know my servers are fine, and I can continue on with my day.

Like I mentioned before in the last post on this tool, there is a lot more that I am not using, if you spend your day going from one server to the next, you might want to give it a try. I have been surprised so far on how I have been able to use it to make my life all that much easier.

One of the key points that I like to raise when I deliver my session about what the best Disaster Recovery solution, is making sure that you pay extra attention to not over designing a solution, and I think this is a great example of why… I was talking with a client today about a disaster recovery solution; the client had requested t-log shipping for their environment. I could not help but think about all the newer wiz bang solutions that I could provide them, and I was really excited to share all these options with them. All of these solutions I was ready to talk about with them could really enhance a disaster recovery plan, or so I thought.

During my visit with the client, I learned the client has 2 data centers; one is on the East coast and one on the West. The link between the two centers is pretty good, but not always the best. The client is a growing company that would take a big hit if their database was down for a few hours. The cost of an Enterprise deployment of SQL Server would be a challenge for them in addition to what sounds like a limitation of the hosting company to support Enterprise features (I am not sure about this aspect, but I am doing some more research).

As with most databases that are important and require a DR solution, the thought of losing any data is enough to make an executive sick to their stomach. However in this case, the cost/benefit ratio or what I refer to as the sweet spot would allow for some data loss and some time to recover. Transaction Log shipping is going to meet all the requirements that they have, and can be done with no additional costs. They are aware of the benefits of other solutions so as they grow, they know the options to shorten downtime, or decrease the risk of data loss.

End result; take time to discuss with the owner of projects about what the expectations or requirements are, and then what the real expectations are in comparison to the cost of implementation. There are some cases where a solution that is all shiny will work, but may not be the right choice. As a consultant I need to make sure that I am on the client’s side, I need to understand the box they are working in, and what is going to be the best option. Present the options and the reasons for the solutions and the reasons why they may want to steer clear of a solution.

In summary, don’t just try to implement the newest feature in the newest version. If there is a case for it, then more power to you. Put the right solution in place.

 

In the new Pro SQL Server 2012 Practices chapter 9 is all about SQL Server compliance and auditing. Written by a friend of mine Jonathan Gardner (B|T) who is based out of New Orleans, LA. So if you don’t hold that against him I think you can find some great information about working with SQL Server Auditing features. This chapter in particular sits well with me because I don’t know if DBA’s understand how helpful a good Audit can be. When I first heard the feature name, the first thing that came to my mind was a feature that would assist administrators who are working within one of the current audit guidelines. Audits like Sarbanes-Oxley (SOX), HIPAA or PCI DSS. Now, granted it can do that. But it can do so much more as well. I have even tied in an Audit to my dbUtilties database, but that is more for another time.

The second thing that came to mind is the all too often heard question that I get when I am talking about features with SQL Server, and that is “Is this only available in Enterprise Edition?” Jonathan has been careful to include not only how to set up and configure your Audit, but letting us know about the changes with 2012. So if you think that you need a copy of Enterprise Edition to make the relevant to you and the organization you work with, I think you are in for a surprise.

The chapter starts with a review on what some of the common audits are and how they impact you as an administrator, then walks though some of the configurations options that you will want to know about. He then walks you through setting up and audit. The chapter then comes full circle by ending with some tips on auditing I had not considered before. There is even a section that shows audit group areas that it a must have if you are working with the HIPAA audit.

The more I consider the audit features with SQL Server I am not sure I can think of a reason that someone might not want to use it. I see the benefits in many areas including trouble shooting. Jonathan does a great job of presenting the information and the chapter is a great addition to the book.

Whenever I get a chance to, I like to speak at local SQL Server User Groups. I like the small groups just as much as large conferences for many reasons, but one reason is to just see how the different groups are organized, watching how the meetings are run and who is involved. I think there are a number of SQL Server Leaders out there that make host some great chapters. The Arizona SQL Server Users Group in Phoenix is a perfect example. I was invited out to speak about Incident Management and my observations on how it compares to first responders. The meetings are the second Wednesdays of each month, and are easy to find as long as you get the address plugged into the GPS correctly.

I wanted to take the time to thank the group, and send a Thank You to Amy Cheek-Lewis for giving me a chance to come hang out. I know they are in the middle of planning a big SQL Saturday, and from the looks of their announcements they have quite a few people involved in getting this event ready. If the Users Group meeting, and the warm welcome the members had for me is any indication of how the SQL Saturday is going to be, I would highly recommend that you consider making it.

I took a big step in my life that my wife and I have been dreaming about for a long time, as of May 1st, 2012 we are what is commonly known as Full Timers. The dream started about 5 years ago, when we bought our first motor home. Before that we had done a lot of camping but never really considered it that we would go as far with it as we have. A Full Timer is someone who lives in their camper year around. The last 4 or 5 years we have more or less been spending about 100 nights a year in our camper, but always dreamed about what it would be like to call it home. A couple more terms that I think you might want to know before I go on…

A Worker Camper (workamper) is someone who works at the place they are staying in exchange for lot rent and such; normally we see something along the lines of 20 hours a week for the lot rent and utilities. My wife is a Work Camper but does additional hours. During the summers in Colorado I am also a Work Camper on the weekends, because I like to work with the animals and doing the social aspect of the job.

A Snowbird is someone who more or less runs from the snow. In our case, this year we are spending the winter in the desert in Southern California. During the summers we have to go back to our home just south of Colorado Springs, Co.

So you might ask why I tell you all of this… Well I have noticed over the last few conference events that I have done that many people have questions about this lifestyle. Now granted there isn’t a lot of cross over between a DBA and a Work Camper. However, there are some discussion points when it comes to how I am able to work with this lifestyle, and the challenges I face. So, I have decided to start a new category on my blog that talks about this topic. I hope you enjoy reading it, and if you are here for the technical stuff, don’t worry I will keep posting those as well. If you are reading this via SQL Server Pedia please note that this will be my last post to that syndication about this topic. Those posts will remain technical only.

Where to start

With this being a new subject that I may post about I am not too sure where to start. So I figure I will start with why… Why do I choose to live in a camper? Well life is very simple this way; I guess I got tired of being tied down to the house, having to worry about stuff that just doesn’t matter like how good the yard looks. I found out a number of years ago that I have arthritis and if I were to live at a lower elevation that I would feel better. As we talked about how to do this and what is important to us, we were able to start removing requirements for our living situation off our list. For example, I don’t have to have a yard, to me it is nothing but work. I don’t need a lot of space, because it just stores more junk for me to spend money on and then keep around until it breaks. The more we thought about when we as a family are happiest and most comfortable it kept coming back to when we are camping, or when we are on vacation. Living in our camper full time is just like being on a permanent vacation. Some people really look at us like we are a bit odd, and well that is ok. We are happy with our dream and living in a house is just not a requirement.

Where are we now?

Right now I am at the kitchen table, no different than you sitting in a home office. We are currently calling the Riviera RV Resort our home, but by May we will be back in Colorado. We are staying on the Colorado River outside of a small town called Blythe in California. We are located about 3.5 hours outside of L.A. and about 2 hours outside of Phoenix right on the boarder of Arizona and California. We have great cell service, so I am not so we have no need for a land line here. The local cable company was able to run a cable modem right to our hooks ups, where I plugged in my Wi-Fi router. So from our rig we have everything we need for us to work. I do have an air card for internet connection as a backup. I travel about once a month back to Denver.

So our rig is a 40 Foot Alpine 3555 RL. It weighs about 13,000 pounds before we put in the washer dryer combo. Here is a video of the exact model we have, including the colors. Check out the video, I think it helps explain a lot. I had to trade in my Ford because it was just outside of the tow capacity, but got a Dodge Ram in the exact same color my charger use to be.

Well that is about all I can think of posting about it now. If you have any questions please by all means let me know, and I hope to post more about our challenges with this lifestyle soon.

It is that time of the year, where everyone is starting to think about what they need to do for next year. Many of us have time between Christmas and New Year’s day, where our systems are not working as hard as they do the rest of the year. I know that I like to take advantage of that time to get a lot of my maintenance completed when the impact to the end users is even less likely to occur. One of the tasks that I ensure I complete that week is my SQL Server Health Check. There are many aspects to these Health Checks:

Backups/Restores – I validate that all the backups are working as they should, granted it is one thing to make sure the backups are still kicking off like they should be, but just as important I validate that the backups meet the current recovery requirements. I validate the max potential data loss and ensure it meets business requirements.

Growth – Often SQL Server databases are hosted by hardware that just meets the requirements for performance and redundancy. As a database grows and more users rely on it the requirements may change. It is important to validate that the current host meets the current requirements.

Performance Baselines – Something that I prefer to do during busy times on the server. Perf Mon Counters and Monitoring Products give you part of the picture, but ensure you understand the system as a whole. Talk with the end users and watch performance indicators inside the database such has how fast the stored procedures are running. It is critical to know what these indicators are, what they mean and when they are considered good or bad. If you are looking for these indicators reflect back to when someone calls you to inform you that the system is slow, what are they using to make that determination?

Alerts/Notifications – Are you watching for all the alerts that you should be, things like free disk space, database growth, severity errors over 17? What happens when these alerts are fired, are you notified via email is the email configuration correct?

Security – Has the permissions of your users changed, what about new logins, or group membership?

Documentation – One of the more helpful tools is documentation; it can help when recovering a server, troubleshooting problems along with many other uses. Yet it is so easy for this to become outdated, and forgotten about. In your health check, make sure that your documentation is current.

Configurations – Validate the current configuration, has there been any unexpected changes? Are you seeing anything that could improve or hurt your performance?

POC’s – A current list of all your points of contacts is helpful when trying to complete tasks quickly. Do you have all the information you need to have backup tapes returned from off-site storage, or do you know all the technical support numbers for all the products that you use?

Service Packs and Patches – Are you testing these, and staying on top of keeping your system updates with the most recent releases?

I spend many hours each year performing health checks for many different clients, and this is just a quick insight into how I complete these studies. Yet I am always surprised at how many problems could have been avoided just by performing these simple checks. I hope you enjoy your holidays.

 

Wow, I could not think of better timing for the question today being presented and hosted by a personal friend Chris Yates (T|B). Chris wants to know what the SQL Community means to me. Sure I agree that the end of the racing season is never a good thing, I did however miss the opportunity to go to the race last Sunday, and I am really regretting it after catching up with what happened (Chris Yates made mention that this time of year is difficult for those of us seeing our sport seasons come to an end). The timing for me however is spot on here because of a number of situations that I have been faced with over the last few weeks…

Currently I am faced with a number of design questions, questions that often are only answered by experience, trial and error, along with testing. Questions like best way to store images with the database, or should a PK have the name of ID. I am also working on the fine line between being flexible, and standing my ground when I believe that something could potentially impact a database. These are just a few of my recent challenges. A great source for helping me navigate many of the pitfalls that surround implanting such things is the SQL Community. If there is a something that I have not had the opportunity to see in production then I know I can always count on my #SQLFamily to be a great sounding board. I often come back to this train of thought, even last week when I tweeted…

There are certain things or emotions that you just cannot explain, things like love, or in an easy reference point for something us Jar Heads call esprit de corps. Words like brother, cohesion, family, morale, and support all come to mind. In my own words I explain it as the brotherhood with Marines who have come before and will come after me. It is the all for one and one for all attitude with the never ending support and trust because when your face is in the dirt and the enemy is over the hill right in front of you. Your trust and your safety lie with the Marine that is next to you. You count on them to watch your back as you sleep and they count on you to watch their back as they sleep.

The SQL Community is a group of people who have gone so far beyond just having the same career path as I do, they help me when I fall or when I need someone to cover my back. When my family is in need, the SQL Community has helped me. When I am not sure if I have the right approach to a problem, they talk it out with me. When they have a question or are looking for work, I hope they count on me for help. We raise a glass together and share the great times, we laugh together when we can. We work side by side, when the mountain is too tough to climb alone.

Outside of the Marine Corps, I have never run across a better group of people. So when it comes to my Marine brothers and sisters, sleep tight, I got my watch and you can count on me. When it comes to my SQL Family, move ahead without fear, not only will I watch behind you, you have the support of thousands of SQL Professionals around the world… United we stand and divided we will fall. Some may only see the next task as a failed job, or a slow running query. I see these as situations where a community member needs assistance, and I will be there because I know when I need assistance they will be there as well.