Art of the DBA Rotating Header Image

Upcoming Presentations

Feb 28th, 2012
by Mike Fal.
No comments yet

More on the how later, but I wanted to let folks know about some presenting I’ll be doing over the next couple of months. I’m extremely excited for all of this speaking and the opportunities to share with the SQL Server community.

My partitioning presentation, Eating the Elephant, is now slotted for three upcoming events:

  • PASS Virtual Performance Chapter – For those following this group, Jes Borland-Schulz(b|t) did a great presentation on filegroups last week. Partitioning will be a natural follow up to this topic and I’ll be presenting to this group on March 22.
  • PASS Virtual Data Architecture Chapter  – Tom LeBlanc(b|t) asked me to give this presentation in April 19 after we talked at SQL Saturday #104 in Colorado Springs.
  • SQL Rally in Dallas – Seriously, I’m giddy about this. The SQL Community selected me as part of the Community Choice vote and I’ll be giving this presentation sometime during the conference. For those of you interested, I blogged about the first SQL Rally last year. It’s a great event, much cheaper than the Summit, and was a fantastic boost for my career. Even if you don’t come to see my presentation (I forgive you), you really should go.

I’m extremely excited and honored that people want to hear me speak. Presenting is a lot of fun for me and very rewarding, both on a personal and professional level. It’s a vicious cycle, too, because while I’m giving the same presentation three times over the next three months, I’ve already got 2-3 more presentation ideas bubbling around in my head that I plan to give by the end of the year. Stay tuned, 2012 is turning out to be pretty awesome!

Posted in: Presentations.
Tagged: · · · ·

Thursday Bonus Series: Core Mechanics Part One

Feb 23rd, 2012
by Mike Fal.
No comments yet

I’m not sure how many folks out there have heard of Server Core yet. The quick and dirty is that it’s a version of Windows Server that has most of the GUI elements stripped out. No, Microsoft didn’t develop this version for technological masochists (like me), but wanted to provide administrators with a Server installation that was leaner, meaner, and more secure. By taking out the graphical portions, you get some nice benefits for your environment, including a smaller footprint for your install, tighter security, and reduced security patching (look at how many of those updates fix bugs with IE).

Up until SQL 2012, SQL Server was too dependent on the graphical libraries in Windows to run on Server Core. Now with the next version, we get the support to install SQL Server to Core, something I’m pretty excited about it. I went ahead and created a VM installation of Core with SQL 2012 and then provide some blog posts to walk you through the process so that hopefully you can do the same in your lab environment.

First things first

Before we do anything, we must (of course) get Core installed on a VM. For information on setting up a blank VM, check out my virtualization posts. Then just fire up the machine with your OS install and make the usual selections. Well, usual until you get to the install version:

I’ve gone ahead and selected the 2008 R2 Enterprise version, Server Core installation. There’s also corresponding Core installations for the other versions, so go with what works for you. Once you click ‘Next’ here, the install will happily churn along (go progress bar, go) until it’s ready for you to enter an administrator password. Once that’s set, you’ll boot into the OS and see…

BAM! Command line! “But wait!” you say, “There’s so much to do before I’m ready for this!” Server Core hears ya’, Server Core don’t care. Fear not, though, because with Server Core, we get a utility that allows us to do some of the basic configuration of your machine. Just type ‘SConfig.cmd’ at the prompt and you’ll see:

Ah, much more comfortable, right? The utility is easy to use, just select and option and respond to the prompts. Using this utility, I’ll do the following:

  • Change my computer name to ‘ALBEDO’
  • Update Windows with all current patches
  • Go into Network Settings and set my IP as 192.168.56.102 (Static)
  • Confirm that my date and time settings are correct.
  • Create a local admin account – ‘mfal’ (Because only bad people use the default Administrator)

This is just the start, of course. We still have several things to do to prep our machine for SQL 2012. In the next post, I’ll cover the basic OS setup, including setting up Powershell as our default shell, enabling the firewall to support SQL Server, and making sure we have the correct Windows features installed.

 

Posted in: SQL Server.
Tagged: · ·

VirtualBox Networking

Feb 21st, 2012
by Mike Fal.
4 comments

There was a request over the Twitterverse for me to blog a little more on my experiences with VirtualBox, which I use for creating lab SQL Server boxes for demos and play around on. I figured I’d start with some tips and tricks on how I’ve configured networking for my virtual machines.

A brief word about my setup: I configure my environment so my virtual machines work like servers and I connect to them as if they were a remote computer. More or less how you should do it in your workplace, except that I have no domain controller. The biggest drawback so far is that I can’t do Windows authentication and I have to log into these SQL instances with a SQL login, but otherwise the setup works just dandy.

I’m going to retract some things I said in my previous Virtual Box post (almost a year ago, yeesh!). I had said that I thought bridged networking was the way to go. This would work fine in a controlled environment, but when I’m working on my laptop I like to keep my guests contained, so I go with a slightly different setup. First off, let’s review:

These are our Networking options in Virtual Box. If you want to know all the options, check out the Virtual Box documentation (it’s really quite good). You can have up to 4 different network adapters for your virtual machine, which gives you a lot of flexibility. Before I had one adapter set to bridged networking, now I make use of two adapters (before, I only used one) to give me a better setup.

The first adapter is set simply to NAT (Natural Address Translation), which allows my virtual machine to pass through my network adapter straight to the intarwebz. This allows me to get all my updates and maintaining a level of insulation from the rest of my network. It’s easy enough to set up, simply enable the adapter and set the drop down to NAT.

On the second tab, I set up a mini-network for my host computer and any other VMs I have on my host. To do this, I’ll enable the adapter and select “Host-Only Adapter”. VirtualBox has some special drivers that are installed to your host that allow for this communication. This allows me to allow for machine to machine communication without these machines interfering with my actual network.

If you’re familiar with networking, you know that you have to have something managing the IP addresses and communication protocols. This is configured and handled by VirtualBox itself. To manage this, open up the main VirtualBox console and select “File->Preferences”. Within that preferences dialog, then select network. You’ll then see a configuration screen with a listing for the VirtualBox Host-Only Ethernet Adapter. You’ll also see a little screwdriver on the right which will allow you to edit your Ethernet settings.

Within these settings, you have a couple options for your Host Only network. On the first tab is your IP address and Subnet for your host machine. This is NOT the same as the IP Address for the rest of your network, this is only how your machine is visible to the mini-network you’re setting up for your VMs. On the second tab, you have DHCP server settings for your network. Basically, you are setting up your host machine to be the DHCP controller for your mini-network.

Now, 99% of the time you will not need to change any of the settings. I use the defaults and they work perfectly fine, but knowing where these settings are gives you some options. The biggest thing you’ll see here is the IP set your network will be based on (default 192.168.56.x), so when you start trying to communicate between machines, knowing what those IPs are will make them easier to fine.

Now, what’s missing from this is some sort of DNS server so that you can reference your VMs by name. Unfortunately, to manage this, you need to go old school here and update your HOSTS file. You can find your HOSTS file buried in the Windows system directory, but once you’ve found it, it’s very simple to edit. In Windows 7, you can find it under C:\Windows\System32\drivers\etc and to view the contents, just open it up in Notepad (or be like me and use Notepad++) as Administrator.

WARNING: This is a SYSTEM file, take care in editing it and make a backup! 

Once you have file open, add a line with your guest machine’s IP and the name you will reference it by. As you can see, I have an entry in mine for KOSMOS at IP 192.168.56.101. The gotcha here is if you’re using DHCP, your guests might grab a different IP than what you have in your HOSTS file, so what I’ll typically do after I get my guest up and running is take the IP the machine grabs from DHCP and make it static.

So after all of this, I can fire up my VM hosting my SQL instance, and call the instance by name on my host. It may seem like a lot of work, but it’s not so bad for me once I get in the habit and makes my life much easier when working with my VMs.

 

Posted in: Virtualization.
Tagged: · ·

Professionals have standards

Feb 14th, 2012
by Mike Fal.
1 comment

A couple weeks ago I wrote about establishing Service Level Agreements (SLAs) before you actually start building out a monitoring solution for your environment. These SLAs help you define what you need to be monitoring and give yourself some control when dealing with the non-DBAs in the company. Now that these expectations have been set on service, your next step is to define the other side of the equation: What you should expect from the databases you must manage. After all, we can promise a level of service, but it’s difficult to meet that promise if there are underlying issues with a database. So now we need to define database standards.

Remember that SLAs are agreements, where both sides have come to an understanding about service expectations. Once we have these, we have to tell developers and third party solutions what is required of them so that we can meet these expectations. Do you want to be saddled with another poorly designed database or not have the hardware to support your transaction load? With a standards document, we can give our non-DBAs clear and certain documentation of what our systems should look like.

Feelings?

Just like many other things we do as DBAs, we know what a good database looks like. The problem is no one else does. Many developers don’t understand the impact of not having a clustered index or the value of defining foreign keys. Executives get confused by disk requirements or backup strategies. Often, because they don’t know what we know, decisions will be made without these considerations. By providing documented standards, the folks who don’t “get it” don’t have to, they can refer to the standards document. If they don’t, we have that document to show them why we can’t support an application the way they want.

Unfortunately, standards are a little harder to define because there are just so many things to go in to what makes a database. This is why we want to start with an SLA framework, so we have some targets to use for building these standards out. We also need an understanding of our environments and what sort of systems our business can and will support (after all, we can’t all get PDWs). The key is to start with what you know and, as you start to lay out these requirements, your standards will start to fall in to place. Consider these examples as bread crumbs to get you headed in the right direction:

  • Database file layouts: Require separation of your database files, so that you can have documentation to back up disk requests for servers.
  • Naming standards: Keep your developers from creating those pesky “sp_” stored procedures. Also, help keep things organized across your environments.
  • Archiving: While you may not be able to explicitly define an archiving strategy, by putting requirements on what kind of data retention you can support , you can proactively manage your disk usage and performance.
  • Indexing strategy: Keep those heap tables from cluttering your database as well as provide guidelines to your developers so that they don’t over-index.

Keep in mind this is not anywhere close to an exhaustive list of what you can build standards on. It will probably take you a fair amount of work to construct your standards document, but it is effort well spent. Remember that you’re not only trying to educate the non-DBAs in your company, but providing a safety net as well for yourself.

The difference is…

We all hate documentation. It is tedious work that keeps us away from all the fun stuff. A lot of times, it feels like wasted work because who reads it, right? The thing is, people will read your documentation, if you make it available. You read a lot of blogs? That’s just us, documenting. So if you create your SLAs and standards and publish them internally, people will pay attention. And if they don’t? Well, you will have a starting point when you say “no”. “Sorry, that design is not supported by our published standards,” has a lot more weight when you actually have published standards.

The developers, the executives…they just want things to work, but they won’t give you what you need unless they know what you need. It’s your job to be proactive and create these policies, because no one else will do it for you. You hear it a lot, how you should be a proactive DBA, not the one that’s always fighting fires, and to do that you need a plan. SLAs and standards, these are your plan, and I challenge you to start on them today so you can take back control of SQL Server and your DBA life.

P.S.  Thematic content for the post headings can be found here (PG-13ish).

Posted in: Monitoring.
Tagged: · ·

Excuse me sir, are you using that partition?

Feb 7th, 2012
by Mike Fal.
2 comments

So last week we had to puzzle out a little weirdness at work. One of our development teams is working to implement partitioning and they came to us about their scheme. They had applied a partitioning scheme, but it wasn’t being used by their queries. Their scheme was pretty simple, but the oddity was that they had partitioned on a nvarchar(10) field. Best practice is to use an integer, though I’ve seen date fields used as well. Though I knew that you could partition on a character field, I usually avoided it.

Now, using an nvarchar field shouldn’t have altered the query process that much, right? This is where it got odd, because what we were seeing was that a query on that partition scheme was still querying across all partitions. Here’s the query and what we were seeing in the query plan:

One of the advantages of partitioning is to improve query performance with range scans, so that the engine will only query across partitions. However, even though our query was specifically using the partition key, the engine was still querying across all partitions for its result. And we were specifically using a nvarchar value for the query criteria, so it shouldn’t be doing any sort of conversion, right? Well, that’s where you (and we, as well) would be wrong. We tried the query again, this time with an explicit conversion to nvarchar(10):

Bang! By converting it to the same datatype as the partition key, the query engine can now properly take advantage of our partitioning. The results are further reinforced by looking at the query costs of our two queries:

  • Query 1: 0.0131291
  • Query 2: 0.0032831

What just happened?

What’s going on behind the scenes is that in query 1 our predicate datatype does not match the datatype of our partition key. The SQL Server engine sees the query predicate of CUSTOMERID=N’2’ as an nvarchar(4000), not as an nvarchar(10). Just take away the “N” in front of our string value and have it declared as a character string and force an implicit conversion. Then, if you look at the query operator, you’ll see SQL Server do a CONVERT_IMPLICIT to nvarchar(4000), not a nvarchar(10).

I also went through this exercise using varchar as my datatype and got the same results. If I simply declared the predicate as CUSTOMERID=’2’, my query would seek across all partitions. If I used CUSTOMERID=convert(varchar(10),’2’), then I would only have the one active partition.

I’m not sure if this is a bug or working as intended, but there’s definitely a couple of lessons we can take from this:

  1. To best take advantage of querying across partitions, you need to make sure your query criteria is explicitly the same datatype as your partition key. Otherwise, the engine can’t make use of your partitioning.
  2. Obviously character strings have some weirdness about them for partitioning, so it’s best to avoid them for partition keys.
  3. Mike is weird for geeking out about this stuff. Seriously. My co-workers gave me some odd looks as we were puzzling this out.

 

 

Posted in: SQL Server, Troubleshooting.
Tagged: · ·

A Temporary Configuration

Jan 31st, 2012
by Mike Fal.
1 comment

A couple weeks ago, I participated in T-SQL Tuesday #26 with a trick to dynamically build a script for doing your restores. In the spirit of that laziness, I wanted to share with you another script I have for configuring Tempdb. There’s not much special here and you could use any other scripting language to get the same result, but the idea is to automate the bulk of the task so that you can save yourself some work as well as creating consistent processes for your database configurations.

--Configure TempDB
--Turn off nasty rowcount
SET NOCOUNT ON

--Declare some variables
declare @files int,
  @path varchar(max),
  @totalsize_gb float,
  @v_sql varchar(max),
  @filesize_mb int,
  @logsize_mb int,
  @currfile int

--Set these to configure tempdb
set @files = 8
set @path = 'T:\TempDB\'
set @totalsize_gb = 40

--script calculates individual file sizes
select @filesize_mb=FLOOR((@totalsize_gb*1024)/(@files)),@logsize_mb = FLOOR(@totalsize_gb*1024)* .25

--Build your config script
set @v_sql ='use tempdb
dbcc shrinkfile (tempdev,100)

alter database tempdb
modify file
(NAME=tempdev,
FILENAME='''+@path+'tempdb.mdf'',
SIZE='+CONVERT(nvarchar(10),@filesize_mb)+'MB,
FILEGROWTH=512MB,
MAXSIZE=UNLIMITED)

alter database tempdb
modify file
(NAME=templog,
FILENAME='''+@path+'templog.ldf'',
SIZE='+CONVERT(nvarchar(10),@logsize_mb)+'MB,
FILEGROWTH=512MB,
MAXSIZE=UNLIMITED)

'
set @currfile = 1

while @currfile < @files
begin
set @v_sql = @v_sql + 'alter database tempdb
add file
(NAME=tempdb'+CONVERT(nvarchar(10),@currfile)+',
FILENAME='''+@path+'tempdb'+CONVERT(nvarchar(10),@currfile)+'.ndf'',
SIZE='+CONVERT(nvarchar(10),@filesize_mb)+'MB,
FILEGROWTH=512MB,
MAXSIZE=UNLIMITED)

'

set @currfile = @currfile+1

end

select @v_sql = @v_sql + char(10)+'--Verify your configuration!'+char(10) + 'select name,size/128.0 [size_mb],physical_name from sys.database_files'

print(@v_sql)

PRINT '/***RESTART SERVER***/'

There are three things we want to declare up near the top, and that is number of files, location of the files, and total size of the files. Without getting into the larger discussion of how many files you should have (for those wondering, start at Paul Randal’s(b|t) blog post on it), this script will help you make all your files consistent.

Some other notes about things I have set:

  • The total file size is total data file size. Log file size is in addition to this, so plan accordingly.
  • I’ve got the log file calculation to size to 25% of the total data files.
  • I’ve got autogrowth on all files set to half a GB (512 MB).

These settings are completely personal preference, so your miles may vary. If you have different practices, by all means implement those. The point here, as with my RESTORE script, it to pre-build most of your configuration script so that you only have to tweak a couple things.

 

P.S. It should be noted that I call out a DBCC SHRINKFILE on tempdb.  The idea of this script is your running it either while configuring a server or during a maintenance period.  Shrinking a live tempdb can cause corruption, so only execute this script when you’re planning on restarting the server and rebuilding tempdb as part of your configuration.  (Thanks to Vicky Harp (b) for reminding me about that.)

Posted in: Scripts, SQL Server.
Tagged: · · ·

Monitoring – Where to start?

Jan 24th, 2012
by Mike Fal.
1 comment

It’s real easy in the tech industry to jump in and start slinging code or writing configs.  Most of us learn this way, throwing technological spaghetti up against the wall and seeing what stick.  It’s not what we want to do, though, when we’re looking to put something long lasting in place.  We need a plan for understanding and measuring the health of our SQL Servers, something we can take to the check writers to show them how we’re doing and give them measurable indicators of success.  This is where Service Level Agreements(SLAs) come in.

Ah, you were afraid I was going to say that?  I know for some people, “SLAs” is as bad as any other four letter word.  I hear it all the time “I’ve been doing for a long time, I know what I need to do.”  And it’s true, much of our work comes naturally: how much space do we have on disk, what’s our index fragmentation, is SQL Server up or down, etc.  The SLA, however, is what we can use to not only communicate to our users what we’re watching, but also help control things so we’re not waking up in the middle of the night because a developer dropped a table.

SQL Server high (level)

We need to keep in mind that most of our management doesn’t care about DBCC checks and backup strategies, they just want to know that the database is up and working.  We’ll take care of the details.  Our SLAs provide a framework that helps our users define operational standards without getting lost in the weeds.  My approach is to outline this by breaking my SLA structure into 3 general areas:

Availability: This is the easy one, are your databases or servers up and available?  When we define this SLA, we’re telling our users that their data will be available when they need it.  Some of the elements that fall under this are:

  • Is a database available 24/7 or only during business hours?
  • Do we need high availability?  What sort of service interruption can the database support?
  • In the event of a disaster, how much data will we lose (Recovery Point Objective) and how long will we be down (Recovery Time Objective)?
  • What sort of resource overhead, such as the free space on disk necessary keep the database up and running?
  • When will the database be down for maintenance and how long can that maintenance be?

Performance: Now we get into a little grey area, because we’re tell our users how well the system will perform.  It’s tough to put any parameters around “the database is slow”, but we can put some hard numbers around this, primarily by looking at the server health metrics.  Some things to consider are:

  • Acceptable query response times for standardized processes.
  • Thresholds for CPU, memory, and disk I/O.
  • Blocking process monitoring and resolution.
  • Index and Statistics maintenance.
  • Batch requests per second.

Support: And now it gets even fuzzier.  There’s always going to be drive-bys and break fix requests.  Whether or not you have a service ticket system or some other method for tracking database team requests, you’ll want to define your response and resolution times as well as work with your customers to determine what’s acceptable from a customer service perspective.  Also, if you have custom code or application procedures, you’ll probably want to have documented what level of response items like failed SQL job, ETL loads, and similar processes will get.

Not being all things

The next thing to define is your tiers of service.  Just like you shouldn’t be all things to all users, you won’t be able to support all your applications in the same way.  By defining your different application types, you can set reasonable expectations for your support.  This area is fairly open, because you might have only two types of applications, or you might have four or five.  A sample breakdown might look like this:

  • Critical 24X7 applications – Applications in this category must be up 24 hours a day, 7 days a week and be able to handle continuous transaction load.  Interruption or degradation of service is considered a priority and requires immediate response.
  • High demand business applications – Applications in this category need to be available 24 hours a day during the business week, midnight Monday to midnight Friday.  Interruption of service requires quick response.
  • Standard business applications – Applications in this category should be available during standard business hours, Monday through Friday.  Interruption of service should be limited during business hours.
  • Lower environments – All non-productions environments should be maintained and supported to allow development resources to be able to complete projects.  Downtime should be kept to a reasonable minimum during working hours.

Your tiers will likely be driven by availability needs first.  The key is being able to categorize your application needs so that you can respond appropriately.  This is also to protect you, because if you’re burned out trying to get development boxes taken care of, you won’t be able to handle things if that webserver handling your company’s sales goes down.

Agreeing on something

The key with creating these frameworks is to remember it’s an agreement between you and your users or customers.  You want to work with them to create reasonable expectations so that if something does go haywire, both sides will know how the issue will be handled and what sort of time frames are in place for resolution.

The other piece of this being an agreement is your users or customers will need to meet you part way.  If you have SLAs defined for a highly available 24X7 application, you want your developers to make sure that the application can meet those needs.  It’s key that your users understand their stake in providing good service, such as calling to report errors or ensuring that an application can handle a database failing underneath it.

It’s a start

I’ll be honest, I’m still working on fleshing much of the above out as I roll out an SLA framework at my company.  What you see here will undergo some tweaking and fine tuning over the next few months as I get some more definition around it, but the intent is to follow the basic structure I’ve laid out here.  There will be more specifics under each of the three areas that will be shared as they develop.  If you have anything to contribute or are curious about, please feel free to leave your input or questions in the comments and I will respond as I can.

Posted in: Monitoring.
Tagged: · ·

SQL Saturday #104 – Colorado Springs (#sqlsat104)

Jan 17th, 2012
by Mike Fal.
1 comment

If you’ve been reading my blog, you probably saw the posts I made about SQL Rally, the PASS Summit, and a couple SQL Saturdays.  It is the SQL Saturdays, in particular, that really show the strength of the SQL community.  For those unaware, SQL Saturday is a run of regional events, put on by local user group chapters and supported by the Professional Association for SQL Server, that provide a day of free training and networking.  It’s a great chance to connect with others who do what you do, along with learning about how to do your job better.

SQL Saturday #104 in Colorado Springs was a stellar example of what these events offer and ended up being a great way to start off a new year of career development.  Here’s a brief overview of some of what went on:

  • Scheduled networking activities included as part of the presentation tracks:  Many events will only have a dinner or some after party, but I thought it was a nice way to break up the sessions by including games and other opportunities to network with other data professionals.
  • Regional and national speakers on a variety of topics:  I thought the speaker mix was fantastic, with many “big name” speakers such as Karen Lopez(b|t), Grant Fritchey(b|t), and Tom LaRock(b|t) (amongst others), but also the local talent, including: Marc Beacom(b|t), Doug Lane(b|t), Jason Horner(b|t), and…..me!
  • Professional resume reviews: Face it, job hunting sucks.  We all have to do it sometime and it’s rare that we get a chance for someone who deals with resumes all day to help us with ours.  I think the organizers of #104 scored a coup getting professionals to come in and go over resumes with people.
  • Free precons: Thanks to the sponsors of #104 for helping out with this one.  It was great to spend an entire day learning concentrated SQL info from Glenn Berry(b|t).  I know a lot of other folks benefited from this.  Not many SQL Saturdays can squeeze these in, but I’m glad the Springs folks made it happen.

As for my experience, I had a couple great highlights.  First off, I got to present again, and with a whole new presentation.  It went very well and I got great feedback.  There were also some great sessions that I learned from (have I mentioned how awesome Grant Fritchey is?)  Catching up with those of my SQL family who flew in for the event is always great, because sometimes having friends in other states sucks (and going skiing with these folks was AWESOME!).  Finally, being immersed in the SQL community gives me such a great feeling, both from being able to contribute and all the stuff I learn from it.

If you haven’t ever been to a SQL Saturday, go.  Keep an eye on the website and if there’s an event within easy travel distance, I can’t recommend enough that you get there.  It’s more than learning about SQL Server, it is about getting connected to SQL Server and the people (just like you) who work with it day in and day out.  It is finding out the gotchas and hidden gems within the application that will make your life easier.  It’s about boosting your career, knocking yourself out of that rut, and becoming “the DBA” instead of just a DBA.

I want to thank everyone who made this event possible(such a long list!), but especially Chris Shaw(b|t) and Jeremy Lowell(b|t), the engines that made this awesome event happen.  Keep up the great work!

Posted in: SQL Saturday.
Tagged: · · · ·

T-SQL Tuesday(#tsql2sday) #26 – Lazy Restore Commands

Jan 10th, 2012
by Mike Fal.
5 comments

I missed out on T-SQL Tuesday #25 (Tips ‘n Tricks) on the first go around, so I’m glad that with #26 I get another crack at it. Mine is pretty simple, but has saved me a lot of time when doing database restores. Whether I’m getting a database back online or (more likely) moving a database to another instance, I don’t want to be bothered for digging around to get my restore going.

As a DBA, I am two things:

  1. I hate using the GUI. Clicking through countless windows is for the birds and I would much rather type three commands than click ‘Next’ three times.
  2. I’m lazy. So those three commands? I write them once and save ‘em to a file so I can run them over and over and over….etc. Automation FTW!

So, restores. We all do them and they can get irritating. I know one of the most annoying things is relocating the files with MOVE when you’re bringing the database up on a different server. This is why a lot of folks use the GUI, because SQL Server can handle that behind the scenes and, if you really want, you can script it out. Well I’ve got another way.

You know you can RESTORE WITH FILELIST to get the database files within a backup. Why not take that a step further and capture it in a table? Then, once it’s in a table, we can use a little bit of SQL coding to give us a template to generate our restore command. Take a look:

--Turn off annoying rowcount
SET NOCOUNT ON

--Some variables
declare @v_restore varchar(1000)
declare @v_backup varchar(1000)
declare @v_sql varchar(max)
declare @datadir varchar(1000)
declare @logdir varchar(1000)

--Set backup file location, database name
set @v_backup = 'C:\demo\test.bak'
set @v_restore='Test_demo'
set @datadir = 'C:\Restore\Data'
set @logdir = 'C:\Restore\Log'

--Storage table

declare @restorelist table
(LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,Fileid tinyint
,CreateLSN numeric(25,0)
,DropLSN numeric(25, 0)
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint
,SourceBlocSize int
,FileGroupId int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varchar(100)) –-Be careful, this last field (TDEThumbprint) isn’t in 2k5

--Capture the file list
insert into @restorelist
exec('RESTORE FILELISTONLY FROM DISK='''+@v_backup+'''')

--Build your restore command
select @v_sql = 'RESTORE DATABASE '+@v_restore+' '+char(10)+'FROM DISK=''' +@v_backup+ ''''+ CHAR(10)+'WITH '
select @v_sql = coalesce(@v_sql,'')+'MOVE '''+logicalname +
''' TO '''+CASE when type='L' then @logdir else @datadir end +'\'+ right(physicalname,charindex('\',reverse(physicalname))-1)+''',' + char(10)
from @restorelist

--display the restore command, trim trailing comma and char(10)
print substring(@v_sql,1,LEN(@v_sql)-2)

So that’s a lot of stuff! Well, not really. If you look at it, most of the script is taken up defining the file list table. Change your variables at the top, run the script, and copy the output into another window…BAM! There’s your restore command. And if it’s not perfect, you only need to make one or two changes to the output to clean it up.

This is not the only sproc output you can capture. The biggest lesson from this trick is you should think about other system stored procedures and look for opportunities where you can grab outputs to make your life easier. Help yourself look like a hero and get more sleep by taking advantage of what’s in SQL.

Thanks to Dave Howard(b|t) for offering folks a second chance to share for T-SQL #26!

Posted in: Scripts, SQL Server, T-SQL Tuesday.
Tagged: · · · ·

DBA Survivor: Learning how to rock out with databases

Jan 3rd, 2012
by Mike Fal.
3 comments

When we get a “SQL book” handed to us, it’s usually to solve a specific technical problem. Maybe you need to understand how to write a PIVOT statement, read a query plan, set up a fail over cluster, or automate something with power shell. It’s a rare occasion when we come across a book that steps back from the daily tactical struggles and gives us a strategic view of what it takes to be a DBA and where to start when thrown in to the shark infested waters of corporate database administration.

Arriving with only a little bit of fanfare (and maybe a 2-3 angel choir) is DBA Survivor by notable #sqlfamily member, Tom LaRock(b|t). It’s advertised by Tom as the book he wishes someone had handed to him when he first became “the DBA”. A fresh approach to career development, the book finds a nice middle ground between purely technical guides and the generic career success compilations. Sure, there’s some technical sections that talk about about Dynamic Management Views, performance metrics, and backups, but the real meat is the “fuzzy” bits, such as instruction on defining daily check lists, writing Service Level Agreements, handling the work/life balance, and getting involved in the community.

While I enjoyed the entire book (all 171 pages!), several key parts stuck out for me. First is the discussion about Mr. Right versus Mr. Right Now. We’ve all seen the superheroes on our teams, the guys who drop everything to fix a problem (but don’t always fix it the right way). A former boss of mine called these folks “White Knights” or, as Tom labels them, “Mr. Right Now”. Contrast this with “Mr. Right”, who is rarely seen because when he’s on the case, things don’t break. He’s the DBA that’s proactive about attacking problems before they become crises, and when things do break he fixes them such that things don’t break again. To often we get caught up in the moment, trying to put out the immediate fires, that we lose sight of the long term. I’m glad that Tom takes the time to delineate between these two roles and emphasizes that, while we need to be Mr. Right Now sometimes, our goal needs to be Mr. Right.

Secondly, it’s nice to see a database book that instructs you about the importance of disconnecting from work. We’ve all been there: 60+ hour weeks, all-nighters, the on-call shifts from hell. The IT industry can run people ragged and burn the tech love right out of them if they’re not careful. I’m glad that Tom includes a chapter on maintaining that balance between your job and your life, because this is another area that so often gets lost in the moment. Steve Jones(b|t) often talks about life being to short to work a job you don’t enjoy, and this is very much an extension of that philosophy. You can be a great DBA and not work yourself to the bone, it’s just a matter of understanding when you should put the Blackberry down.

Finally, it should be no surprise that Tom has a chapter on community. I’ll wager most of you reading this already understand the benefits of the PASS organizations and local user groups, but many folks picking this book up may not. By including a discussion on connecting through user groups and professional organizations, Tom offers the new DBA an avenue towards excellence. I’ve seen the benefits of PASS and keep catching myself saying “If I had only gotten in to this years ago….” If I had read Tom’s book then, I probably would have.

Now many folks probably think this is just a book for the junior DBA, for someone who’s just getting started, but I know this book has value for data professionals of all levels. This book is not a detailed guide or roadmap for solving specific problems, but a series of highway sign posts to get people headed in the right direction. Maybe you are a fresh DBA, looking to get in to the industry or just survive your first week on the job. Or maybe you’re like me, a career DBA who is looking to refocus my career and looking for that “big picture view”. No matter how you got here, DBA Survivor is an excellent starting point for the rest of your database career.

Posted in: Book Reviews.
Tagged: · · ·

← Earlier Posts Newer Posts →