Author Archive for Chris Shaw


Help SQL Server Help You; by making your Backups Smaller and Faster?

Ok so I don’t think anyone will complain when I say there are ways to make your backups faster, or even smaller. But I assume that most people that work with SQL Server will right off the bat start thinking about products that compress your backups such as Litespeed for SQL Server, or RedGates Hyperbac to just name a couple of many. These tools will do the job, there is an ROI to using them and in my opinion databases that are Medium to Large should use something like this, even if your disk is a plenty. Then again, there are those groups of us that will stand up and say, heck if I want a smaller backup or a faster backup I will just use the compression in SQL Server Enterprise Edition.


What do you do if you are not getting the backups small enough? What if you need to compress it even more? Is there other ways that you can stream line out of your backups, or are compression tools it? The good news is there is even more room that you can reclaim out of your backups. Now here is the spoiler…

This is going to require some work on your side, and the chapter about Efficient backups by Greg Linwood is going to help you get there. There is some stuff in your databases that you may not even need to backup, now the question is how do you get it so that your backups know not to back up all this unneeded data? Well again I am going to refer you back to Chapter 33. I can tell you that here is yet another reason that I don’t think we have enough sessions on how you simply create a database. My next session that I am going to submit to PASS is going to be on this topic. The information that Greg is sharing with you in this chapter is what separates the good DBA’s from the Great DBA’s. It’s not the attribute of how to get more space out of your backups, but the attribute of know how to get the most of your configuration, and creating a configuration that you can get the most out of.

Enjoy the Chapter.


SQL Server can’t do that, we have to buy Oracle.

 So we all like to think, OK maybe it’s just me. I hope it’s not just me, but I can tell you this topic comes up a lot. Maybe I should back up and start again…

 Over the last year or so, I have been on a number of contracts, these often start with a list of tasks such as our performance is not what we expected, or we need to add this new feature. But often many people come back to a question that I am not sure why but it really surprised me. The question was can SQL Server handle all my data? The funny thing is that these are not clients that are on huge systems, they may have a few hundred gigs, but because of poor design, lack of indexes or ignoring maintenance on indexes all together. A month or two ago I was in a session that Kevin Cox did where he was talking about some of the 50+ TB systems that he has worked on. So I revert back to the question that I have been asked…

Can SQL Server handle all this data?

Well it is my opinion that it can without a doubt. There is a number of ways that this can be done, but a popular way today that is built into SQL Server is Table Partitions. I know that if you are not familiar with the term or if you have never done this before that it can appear to be a bit scary. Not like bad slasher flick scary, the kind of scary that would keep you up all night fighting for your job because something did not work like you had planned it to. So how do we take the scare out of table partitions? Another easy question…

Chapter 31 of the SQL Server MVP Deep Dives book. The chapter by Ron Talmage is going to point you in the right direction. The chapter in my opinion is clear and concise. Ron takes you from the basics of learning the terms that you need to know all the way up to loading your table and then some. Ron does admit that there just was not enough space to cover the whole topic. But he does give you a list of topics to research and I bet many of them appear in the next chapter. I do question if we have this in the right section, I would think we would want to talk about partitions in the database design section, but hey all that matters is it is in the book.

The point of this post is that when you are asked the question “Can SQL Server do this?” We really need to take a lot of time and figure out exactly what they want done. If someone has a bad design in SQL Server, and they take that bad design over to Oracle it is still a bad design. Learning the tools that you have at your finger tips and making sure that you use all the tools you have is the key to being able to say…

“Heck ya it can, watch this”

If you have not learned yet one of my fellow bloggers is a huge fan of partitioning and spends a number of hours working on and helping companies implement it. A while back he put in a feature add with Microsoft and well, I think it would be a good add to the feature. If you have time, you could make Jeremy happy by voting on this so I don’t have to hear about it any longer. Ron, I thank you for another great chapter in the book that I think has become the best SQL Server book out in years.


Something You can Use

I don’t want this to sound like it’s all about me. The end result is that it isn’t, I hope that when you read this that really I am trying to make it about you, something you can use or something that you may need to use in the future. I think about it this way, when I go to a conference there are so many excellent sessions that I would like to attend, some of these sessions are really deep and some are the overview. By default I want to look at sessions that I really think I am going to learn a lot, that is the whole point right? But then I have to come back to earth and realize that not only does it have to be something that I want to learn but something I think I can use soon. So what does this mean for you?

Each DBA at some point in time is going to have to deal with a space issue, and before you can do that you really need to sit down and free up as much space as you can to make sure you know what you are working with. There are issues here with just freeing space that you may want to consider, and Joe talks about these in his chapter. The end result is this is a chapter you should read and understand so when you are working with your databases, you know how to reclaim and reuse the space that you can.


Come up to Speed with DMV’s

Aaron Bertrand is always a good read no matter what he is working on, and now he is going into one of my favorite topics. I like this topic so much that I posted a few DMV’s on my blog and I plan to do another in the next few days. What makes DMV’s so great? They take information about that SQL Server and present it in a format that most everyone understands, you know the Select statement. There is so much that you can learn about your system, how it is performing and when it needs help. If you need more from your DMV there is some adjustments you can make. For example I wanted to see the worst performing procs on my system. I took a DMV ran a query on it and turned it around to insert that data in a table where I can report on it.

Why is Aaron so good? His work is clear and very easy to understand, a neat trait that I find in his work is he doesn’t just make a statement and expect you to take it as it is, he explains why, Check out the first DMV’s he has on the favorites list. Not only does he tell you what it returns, but also why this one is one he uses a lot. Aaron thanks for the great chapter.


SMOhhh It Can Do That?

I always have this re-occurring question or theme when I work and talk with other professionals. I look at the SQL Server Product and it is just growing and growing each and every release, and to be honest I don’t understand how some of the experts keep up with everything that is coming out. My knowledge base is usually defined by what I am working on or with at that time. SMO is one of these topics that I wish I knew so much more about. What I am getting out of this chapter is that a lot of SQL Server’s Management can be scripted. What does this mean to you?

Think about the power and flexibility you know have when you can connect to a system and start working with SMO and PowerShell to back up your databases. If you were to use the UI it would take forever to create 100 Maintenance Plans, but with SMO, everything is scripted. How easy is this to do? Well I cannot speak first hand because I have not used it, but MVP Allen White has. He included the script right there in the chapter for you. I know what you are thinking… Backing up the database is great and all, but how do you restore it, well Allen included a script for that as well.

With PowerShell and SMO I am having to dust off the learning cap and really address the way I am managing my servers. This chapter is going to make sure I am well on my way.


Power Shell Power

In a land not so far away in a time not too long ago we were introduced to Power Shell. A set of features that I believe started as an add on for Exchange. This tool is supposed to be so powerful that it can leap terabyte databases in a single leap. It obviously is a feature that many are now using, you can tell by just looking at the number of people working with it. Do a search on the internet and you have more information than my mere mind can handle. I have to be honest I have not started to use power shell but this chapter has motivated me to start. I think my life is going to change…


The day after I finished this chapter I sat in a session that Kevin Cox from the SQL Cat team was delivering at the local user group. He told of stories where companies have leverage the usage of power shell to the point where the install of SQL Server is a single statement. Now I may not have countless servers, and I really don’t need to single click to install, but I do need a place to start. And that place is here with the PowerShell in SQL Server chapter by Richard Siddaway. Right from the bat he has my attention, A simple call out note that lets me know I don’t have to be a .NET developer to use this power house.

AS I move down the chapter the idea of automating my maintenance via PowerShell has grabbed my attention. Richard does a good job of pointing out where I may need some extra help from books online. I have to say I believe he gave me a great place to start.


Maintenance Plans

From the way I see it… I think this is the most important chapter in the book.

From Day 1 of being responsible for a database you as a DBA need to know how to back up your database, and how to restore it. But the tasks don’t stop there. Your job is to make sure the database is online and the users can use it. This means taking care of all the aspects of the database. The same way that Tibor Karaszi points out that you have to maintain your car or your house, you must maintain your database.

If your new to SQL Server stick to Tibor’s advice. A plan will be created that you can use long term. As you feel more comfortable start working with other aspects of the chapter and Maintenance Plans, look at the schedules or handling your indexes a little differently. I think I jumped for joy when I saw in the Maintenance Plan chapter the best advice on shrinking your database. Another well done chapter for one of the best SQL Books I have read.


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers