Art of the DBA Rotating Header Image

March, 2012:

So about that time management…

Yesterday I read an interesting post by the inimitable Brent Ozar (b|t) on time management.  Good stuff, much of which I already do or try to do.  Shortly after reading it, I had a brief Twitter exchange with Brent and Steve Jones(b|t), essentially on whether or not these were just rules for consultants or if they worked for us corporate folks as well.  Personally, it’s been my experience that not only can you follow these rules, but that you really should if you don’t want to burn out and still accomplish your goals.

First off, if you haven’t read Brent’s post, go read it.  I’ll wait.

<Time passes>

Cool, now that you have context, I want to basically give my thoughts on each of the rules and how I put them into practice in my own work day.

#1 Decide you want to be incredible – One of my favorite tweets from @FAKEGRIMLOCK is “BE SELF.  ITERATE UNTIL AWESOME.”  The first rule is really that simple.  You don’t have to change who you are, you don’t have tap into some hidden fount of knowledge…you just need to be yourself, awesomely.  This is something that can be done in a corporate job as easily as anywhere.

#2 Never budget less than whole day increments of time – Let’s not take this one quite so literally.  What I take from this is to devote blocks of time to my tasks and reduce distractions.  Whether it’s 4 hours or 2 or 8 (if I can get away with it), I use that block ONLY for the designated task.  Don’t multi-task, because it becomes harder to focus and get things done.  The key is to reduce/eliminate distractions.

Distractions come in many forms:  Meetings, on-call pages, emergency 20 hour conference bridges, people coming by your cube to chat, etc.  We can’t plan for them and they are probably the biggest threat to getting stuff done.  This is the balancing act, the biggest challenge for time management.  I’m actually going to steal some NoSQL terminology to outline how I approach this:

  • Map – Map out my time, schedule my work.  If I’ve got 2 hours, I plan to put 2 hours into a project.  Don’t work on anything else.  Keep in mind, sometimes the work is planning out your other work.
  • Reduce – Do work that reduces your other work.  Can you build better maintenance for your systems?  Can you communicate with people outside of meetings?  Take control of your time and put effort into removing the distractions that could disrupt you.

#3 Leave one whole day per week to do absolutely nothing – For me, this translates to one word:  disconnect.  We live in an a world and a culture where it’s easy to always be working, where we’re checking our email constantly, looking at alerts, responding to internal customers, monitoring our servers.  Why?  The world and the company will go on without you for a while, take a break for yourself when you need to.

IT has a reputation for burning people out, but I think that’s because people let the job control them instead of the other way around.  Take a break, breathe, and do something else.  Life is too short, you need to remember that we work so that we can do the stuff we enjoy.  Fortunately for many of us, we enjoy our work, but that can’t be the entire definition of who we are.  And if you’re working a job where your boss expects that?  Time to get a new job.  There are companies out there that respect the work/balance, go work for them.  You’ll feel better for it.

#4 Leave one more solid day to pounce on incredible opportunities – Not much else to say here, though I would add that incredible opportunities can happen inside your company, they don’t just come from without.  Also, some opportunities are self-generated.  An example from my work:  My job doesn’t have any formal SLA’s defined for the database team.  I saw the need and am driving that project.  My bosses and co-workers are taking note of my initiative.  Sometimes pouncing on an opportunity is simply being a leader.

#5 If the incredible opportunity runs more than a few weeks, it’s work – Maintain your balance.  When you take on additional projects or tasks, make sure they fit into the overall scheme that is your work/life pie chart.

#6 Say no early and forcefully to everything else – Much has been written about the necessity of the 40 hour work week.  Since we can’t do everything, at some point we need to be able to say “no”.  This is the hardest piece, especially in a corporate environment where you have a team that depends on you or management you want to impress.  However, you’re only human and sometimes work just isn’t going to get done.  This actually loops back to #3, because if we take on more than we’re capable of doing, everything else suffers.

Let’s not confuse this with neglecting what the company requires of us.  There are times we’re going to need to work on things we may not like (stupid SSRS).  What we need, as corporate employees, is the freedom to cry “uncle!” when there’s too much on our plates.  Sometimes it’s having a teammate take on that extra project, others it’s pushing out deadlines.  Really, all we’re trying to manage here is our overall workload so we’re not trying to do too much.

And if you’re working a job that won’t let you say “no”, that doesn’t trust you enough to accept a “no”, then maybe it’s time for a new job.  Just sayin’.

I think Brent’s rules are a great and hopefully this puts a little more perspective around them.  While my personal approach is worded differently, I think the main principles are the same.  What’s important is that there’s no reason why you can’t follow these rules in a corporate world, that these aren’t just for consultants.  Sure, you won’t follow them to the letter, but that’s not the point.  The goal is that too often we let our time manage us when it should be the other way around.  Turn that corner, take control, and you’ll find everything changes for the better.

(Yeah, cheesy end, but sometimes it’s like that.  Thanks for reading!)

Survival Monitoring

The IT world is a jungle. Countless threats lurk like predators, ready to devour us if we’re not careful. Seemingly benign events can quickly turn in to raging panic fests and danger lives everywhere that we’re not looking. To survive you need to be prepared and proactive. Unfortunately, many of us are thrown into this with little more than a pat on the back and a smile, as if we just got dropped out of a helicopter into the African jungle with little more than a pack of chewing gum and a pocket knife. Yet, we need to survive not just the next day, but the weeks and months ahead of us.

Because we need to survive, there’s some basic stuff we need to focus on in order to ensure our survival. It’s not everything we need to live happy, contented careers, but the minimum elements we need to watch in our environments to make sure we live to see the next day. If we were trapped in the wilderness, we’d first focus on shelter, fire, and food. In database terms, we need to first keep an eye on backups, services, and disk space if we want to make it to the next day.

Shelter from the storm

The most important item in a DBA’s life is backups. We can have screaming disk, tons of CPU, and all sorts of clustering, but that means nothing if our files get corrupted or the building burns down. Just like shelter in the wilderness is a place where we can always find protection, our database backups will always give us something to recover to.

Keep an eye on three things when it comes to your backups. First, make sure they’re actually occurring. Look to the backupset table in msdb for this, because it will tell you exactly when your backups are occurring, whether they’re log backups, fulls, or differentials. Next, where are your backups located? Backups won’t do much for you if they’re stored to the local computer and then that computer’s hard drive burns up or gets corrupted. Make sure that your backup files get to another location. Finally, make sure your backups work. Just because you take a backup doesn’t always mean that backup is reliable. Perform restores when it’s not an emergency to validate your backups, so you’ll know things will work when it’s an emergency.

Backups are your safety net. No matter what else happens, you should always have them to fall back on. It may not be pretty, but you’ll be glad they’re there when you need them.

Give me fuel, give me fire

Fire gives us the energy to get things done, whether it’s keeping us warm or being used to cook food. This is the same with your SQL Server services. If these aren’t running, your databases are down and your company is losing money. We can’t always prevent the interruption, but we need to be ready to respond when that interruption occurs. As DBAs we need to be proactive and watch our services.

Also, we can’t limit this just to the SQL Server database service. How many of you run SQL Agent jobs to perform your backups and maintenance? I know I do. If the Agent service is down, the databases will be working fine, but none of that other work is getting done. To boot, you probably won’t be getting any notifications about these jobs not running, so this will be one big blind spot.

We can’t take on faith that services will start automatically. Sometimes they stop for completely legitimate reasons. It’s our job to make sure they’re up and running and very few things are worse than that surprise call about something not running because a SQL related service is down (one thing that is worse is that we don’t have a backup, see above). Watch your services and you’ll sleep better and warmer.

How can you have any pudding if you don’t eat your meat?!

People got to eat. Once we have a place to sleep and fire to keep us warm, this is the next thing that we need to keep us going. For databases, this food is disk space. We could expand it out to CPU and RAM, but I’ve seen many a server that will limp along when these are consumed and stop stone cold when a file can’t grow anymore because the server ran out of space. If we want a happy database, we need to keep our database fed.

Primarily, watch the free space on your drives (wherever your files are stored), but also keep an eye on the free space within your files. You need to know when your files are going to grow and consume your space. The immediate survival goal is to make sure your server has enough disk to keep running, but you also need to monitor how that disk is getting so that you can be ready to add disk as necessary.

Getting by

Please note, doing all of the above doesn’t guarantee that your server will hum along happily. This isn’t happiness, this is survival. This is the bare minimum you want to do to ensure your company’s service and data. That’s the thing about monitoring: there are hundreds of counters and statistics you can watch, it’s up to you to figure out which of those are important. That’s why you want to start with the fundamentals first, or you could be putting your data, your job, and your company at risk.

I wanted to start with the overview of this strategy. Stay tuned my next post (might be next week or next Thursday, depends on my schedule), I will actually cover some technical solutions to this monitoring, some SQL and PowerShell scripts you can use to keep an eye on all of the above. If you want to get a head start, take a look at my post on backupset or look at Brent Ozar’s(b|t) sp_blitz.


Thursday Bonus Series – Core Mechanics, Part Three

So here we are. After installing Server Core and configuring it, we’re ready to install SQL 2012. Fortunately, this is the easy part. We only have two steps and both are things that have been common actions for many years now.

The first step is to install the .Net 4.0 libraries. SQL Server requires these and they’re currently not part of the Windows configuration/setup. We’ll need to head out to MSDN and download the standalone .Net 4.0 installer for Server Core. You’ll need to do this on a separate machine since you won’t have access to a web browser on your core machine itself, but it’s not a large file(~50 MB). Once you have it, copy it over to your core machine and run the executable. The install should take 5-10 minutes and then you’re ready for SQL Server.

Step two is something I’ve done a lot of and strive to make a habit in most of my SQL Server environments: an unattended install. For those of you unfamiliar, this is simply the practice of installing SQL Server from the command line using a configuration file. This is actually how SQL Server is typically installed, you’re just building your configuration file as you click through the GUI. Read up on it on MSDN.

For my install, I’m just going to use the sample configuration.ini file found on MSDN, with only a couple adjustments for my environment and a couple additional flags. I won’t go through the whole file, but here are a couple key options that are used:

  • QUIET=TRUE – You *must* have this set, as any other option requires GUI elements of the install and your install action will fail. Like I said earlier, Server Core hears ya’, Server Core don’t care. We’re flying without visuals here, folks.
  • IAcceptSQLServerLicenseTerms=TRUE – Typical license agreement acceptance, but since we don’t have a GUI with a checkbox, we need to indicate our acceptance in our configuration file.
  • TCPENABLED=1 – By default, SQL Server won’t enable TCP communication. So instead of going into the configuration tools after the fact, we’re going to enable it in our setup with this flag.
  • SECURITYMODE=SQL – I rarely see servers that are Windows authentication only, but SQL Server uses that mode by default. We need to use this option in order to set mixed mode authentication. This also means we MUST use the SAPWD option to declare our SA password.

Once we have this, it’s a simple matter of running setup.exe and declaring the configuration file. With my VM, I’ve mounted the SQL 2012 RC0 image to my E:. From here, it’s one line and off we go. 

Now we just wait for SQL to install, which should take 5-10 minutes depending on your system. Once it’s complete, then we can fire up SQL Server Management Studio and connect to our brand new instance.

After this, it’s just SQL Server folks. Everything we’re used to doing, whether it’s using T-SQL or the Object Explorer, we’ve done before. It’s just now we’re doing it on a box that has a smaller OS footprint, leaving us with a happier place for our SQL Instance to live.

With our install complete, I want to leave you with a couple extra tips. Keep in mind that while the server itself has no GUI, we can still use MMC snap-ins to connect to the server remotely and manage it. This way, if you need to change firewall rules, add users, or any other management task, you can still use the tools your familiar with. Also, by forcing remote administration, you’re adding an additional level of security to your systems.

While AlwaysOn and many of the business intelligence features are being highly touted, I’m pretty excited about this bit of functionality in SQL 2012’s feature set. There’s a lot of power here for automation, security, and performance (all things I love), so I’m going to be pushing this where ever I can. Hopefully you can benefit from it as well.


T-SQL Tuesday #28 (#tsql2sday): Jacks and Aces

DBA. I’ll be honest, there are times I *hate* that title. People toss it around without really understanding what it means. Heck, how many of us specialize in backups, monitoring, and high availability solutions only to get a call from a recruiter who has an “immediate need” for an expert in SSRS and cube design. Unfortunately, this is a direct result of non-database folks not really understanding what we do. They simply see “database” on our resume and figure that if a database is involved, we know how to handle it.

We’re often considered a Jack-of-all-trades, the IT handyman. This has evolved from the roles we have to play in our careers and how so many of “fell” into a database career. The rule of thumb is that the smaller the shop, the greater our breadth of knowledge needs to be. While I’ve been a SQL Server administrator for over ten years now, my job responsibilities have required me to learn something about networking, Windows server administration, .Net development, report writing, and Oracle administration (amongst many, many other topics). Because so many things touch a database, we’re expected to understand all of these different pieces as well as the database itself.

The problem with all this is that it’s more than any one person can really know. With SQL Server becoming more and more complex (a good thing, by the way), it’s hard enough for us to keep up with that platform alone. Since it’s rarely an option to tell our boss “No, I can’t do that”, there’s a couple things that I’ve found help me stay ahead of the game:

  • Building your personal network. This is more than just the SQL Server community (fantastic as it is). Sure, attending user group meetings has helped me find experts in areas of SQL Server I’m not as knowledgeable in. But you’ll need more than that. I have Oracle DBA friends, hardware geek friends, sysadmin friends… get the idea. By building out this network, I always have someone I can go to if I’m out of my depth.
  • Stay educated. Sure, we spend a lot of time learning about SQL Server, but remember why we get into this business in the first place. Technology is cool, so keep learning about it. Pay attention to trends and tech so that when your boss comes to you about something, you won’t be caught by surprise. And if you *are* surprised, don’t sweat it, but make some time that day to read up on whatever they were talking about.
  • Write it down. When you do something, document it! So many people bemoan documentation, but the cold hard truth is you’re going to forget something when your focus gets completely shifted the next day. Being a small shop DBA means you’re going to get bounced around on a lot of different things, so you need to record what you do so that you don’t have to relearn it later.

Being a small shop DBA can be a tough gig, but it’s where most of us cut our teeth. Hindsight being 20/20, there’s a lot I’d do differently. Fortunately, Argenis Fernandiz (b|t) has given us this great T-SQL Tuesday topic for us to share with our SQL family and help others learn from our experiences.

The biggest lesson I would take out of all of this is that, while our job requires us to generalize most of the time, we can really only advance our careers when we specialize. If you’re simply treading water at your current job, pick some part of the SQL Server platform that interests you and focus on it. Once you start becoming an expert at something, opportunities will open up for you along with more interesting work and learning. That will start you along the path of moving beyond being a jack of all trades and becoming an ace in the database deck.

Thursday Bonus Series – Core Mechanics, Part 2

Previously on Art of the DBA, I walked you through a basic install and setup of Windows 2008 Server Core. Now that we have our server setup with an OS, a name, and an IP, it’s time to prep it so we can install SQL 2012. Let’s get started, shall we?


Before we get too far, I have a cheat I confess to. A lot of these commands I am going to run are saved in scripts to spare myself from typing commands. As I’ve mentioned previously, I’m lazy and the beauty of automation is having scripts. So the first thing I’ll do is open up the firewall so I can copy my scripts down:

netsh firewall set service fileandprint enable

This command enables file and print sharing. Now, normally this will be off (and I tend to turn it off when I’m done), but for the initial setup I want to be able to get at the machines local drives and put my scripts there.

The second thing to do is to activate Windows. Just because we’re using Server Core doesn’t mean Microsoft doesn’t care about us registering the OS, even if they make it harder. Go ahead and run:

SLMgr.vbs -ipk <<Serial Key>>
SLMgr.vbs –ato

The first command will load your serial key into the machine, the second command will communicate with Microsoft to validate your key and activate your machine.

Great, with all that out of the way, let’s get to the fun stuff.

Configuring your Console

Full disclosure, I’m still pretty new to Powershell. Oh, I’m no stranger to scripting (VB scripts as well as Korn/Bash are in my repertoire) and, as we’ve discussed, I love the command line. But I’m still getting used to the syntax and all the different calls. This means that I want to make sure Powershell is loaded and set as my default shell for my Server Core installation. To do this, I’ll run the following script:

REM Install base packages
REM install .Net
start /w ocsetup NetFx2-ServerCore

REM Install powershell and modules
start /w ocsetup MicrosoftWindowsPowerShell
start /w ocsetup ServerManager-PSH-Cmdlets

The comments explain what’s going on, but let’s touch on a couple of things. First is ocsetup, which is a command line utility to install Windows components. What I am using it here for is to first install my basic .Net libraries, then Powershell and Powershell’s ServerManager commandlets. Once that’s all installed, I restart the machine and then fire up powershell itself (by typing ‘powershell’ and the command prompt) so I can wrap up the rest of the configuration.

Finishing touches

With powershell running, we can start on our final steps. First, I will set my script execution policy so I can run scripts (and save myself some effort):

set-executionpoilicy remotesigned

For more on what this means, here’s a link. With this in place, I can now execute my final steps, using the following script:

#Run from Powershell, configure powershell
import-module ServerManager

#configure powershell as default shell
set-itemproperty "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\WinLogon" shell 'powershell.exe -noexit -command "import-module ServerManager"'

This script will go ahead and import the ServerManager commandlets for the current session (commandlets are not loaded automatically). This makes our life easier for setup. The next command then allows for remote powershell script execution. While I don’t use it for this job, it’s nice to have. Finally, I make a registry key change so that when I log on in the future, it will use Powershell as my default shell.

Finally, we configure our Windows features and firewall:

#Install x64 components
Get-WindowsFeature WoW64-NetFx*,WoW64-Powershell | Add-WindowsFeature

#update firewall
netsh advfirewall set currentprofile settings remotemanagement enable
netsh advfirewall firewall add rule name="SQL Server(1433)" dir=in action=allow protocol=TCP localport=1433

With this, I add all the necessary .Net and Powershell libraries I’ll want for future use. Then I enable the remote management settings in the firewall, along with opening up port 1433 for SQL Server.

And with that, we’re ready to actually install SQL 2012, which I will cover in the next post! Stay tuned!