What I Do

Knowing that I’m putting myself on the job market, I was recently asked to describe what I do. As a DBA, I realize that every DBA’s job is different. So it was an interesting exercise to put together a few paragraphs explaining what my current role is.

My company is rapidly growing. We’re in the process of replacing our current ERP system with a shiny new Oracle-based one in the next 10-12 months. While that happens, the company will grow another 40 percent.  Our mandate from management is “Make it scale!” We have existing applications that need to grow and new applications that need to be put in place.

A friend referred to me as a jack-of-all-trades DBA. I think he’s onto something. As the only DBA in the company, I do it all. Or at least most. That means everything from managing applications to performance tuning to server management to scaling applications that we’ve outgrown. I’m also working on a consolidated reporting instance as well as designing a short-term data warehouse solution.

Let me explain that our entire IT infrastructure team is four people, and that includes our Director of IT. It means we all wear several hats. We have a network/sever guy, security/network guy, and a DBA. If it touches a database or database server, I’m involved.

Part of this growth (and a looming data center move) required virtualizing everything. And I mean everything. The only database server that hasn’t been virtualized is more of an appliance that runs on vendor-provided hardware. Every other sql server is virtual. During our virtualization project, we consolidated 14 servers down to four primary servers.

Another thing I’m doing is making systems scale that were never meant to. We have a homegrown Access “application” used by our manufacturing organization. It was this little database that is used to capture data for hardware testing. Like most applications of this vein, it grew into a beast. And somehow, it grew into a business critical beast. It had become the authoritative data source for products shipped. Recently, while looking at the design of the thing, I realized that the DBA who upsized the thing didn’t use clustered indexes. It was a HEAPing mess.

Most recently, I’ve been tasked making this database talk to its little brothers at some of our business partners’ locations. These remote locations need to get updated information about customer orders, parts, and BOMs.  Not only that, we need to remote locations to report back to the mother ship.Historically, someone established a VPN connection and did this manually. Considering our tight network policies, automating this is quite a challenge.

Our existing collaboration/content management tool just isn’t going to work. We’re implementing Sharepoint. And we’re estimating the content databases to reach 500 GB within the next 18 months. I’ve had to prepare the database servers accordingly. This means content databases have to span multiple data files and multiple disks.

On top of all of this, we’re publicly traded and bound by Sarbanes Oxley. That means a good chunk of my time is spent satisfying auditors.

Using RUNAS to Clear a Hurdle

Like most companies, mine has to contend with some form of regulatory compliance. In our particular case, it’s Sarbanes-Oxyley 404, better known as SOX.  We have some network policies to enforce compliance with the implementation of SOX. One of those policies is that people with elevated access to key systems have a seperate account for administrative tasks. This means I have two Active Directory accounts, usermike and dbamike. Oh, and of course, I’m not supposed to be logged into my regular workstations with my administrative account. Just to make life a little more interesting, we have a policy that says we don’t do administrative tasks with service accounts, such as SA.

usermike doesn’t have access to crap, especially database servers. Let me put it this way, Claire in Marketing has more access on the network than usermike.

As a DBA, this situation can be hell. When I first started, I was launching a remote desktop session to my SQL servers in order to run SQL Management Studio. The only thing worse than running a remote desktop session to a SQL server is running SSMS in that remote desktop session. When we bought a new SQL tool, I realized that keeping RDC sessions open all day just wasn’t the solution. I went looking for a better solution.

One day, I stumbled accross the RUNAS command.  Basically, from a command line, I can launch an application and state the user that should be used to run the application. In my case, dbamike runs the application. It will prompt me for that user’s password, and off we go.  It looks something like this for SSMS.

runas /user:domain\dbamike "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

After putting this in a batch file on the desktop, life became a lot simpler. My productivity soared.

Like most things, there is a gotcha. When I attempt to save a file, it will save it in dbamike‘s My Documents folder. That’s because the application is running as dbamike. It also means I can’t open files directly from usermike‘s Outlook mailbox. I have to save it to c:\mike first. It’s an extra step, but the trade-off is wel worth it.

Exploring my Options

I’ve been presented with a rather unique problem. And the right solution had left me a little perplexed.

My company has a SQL server at a business partner’s manufacturing site. The server is in what our network guys would consider a less than secure environment. They’re in the middle of setting up a permanent VPN tunnel, but they’re being incredibly tight with what traffic they let through.

The parameters in which I have to work are pretty specific:

• Users need to run reports against data that’s on the remote server.
• The data does not need to be real-time. Reports only run once a day
• Any connections must originate on our side of the tunnel, not the remote
• The solution needs to be fairly bullet-proof because we may not have a full-time SQL DBA in the future
• The remote database is small, less than 2GB
• The remote server is running SQL Server 2005 Express
• The remote server is not on our AD domain
• The remote site’s server will most likely be eliminated when we migrate to our new ERP system

I’ve looked at several options.

• Mirroring is out because both need to be online at the same time.
• Log shipping is out because of the complexity and potential lack of DBA.
• Clustering won’t work without shared disk, nor will it work over a VPN

It finally hit me that I was making this much harder than it needed to be. a simple backup and restore will completely fit the bill. Once a day, we will back up the remote server (assuming it’s not being backed up now) and then have the server on my side initiate the connection for a restore. Hopefully we can get the remote server on the domain to simplify authentication.

It’s potentially the simplest solution and the easiest one for a non-DBA to fix when something goes bump in the night.

Transitions

My current employer has made the decision to move to an Oracle-based ERP system. And the decision has been made to outsource DBA services.  The only major system remaining on SQL Server will be Sharepoint. So my boss and I have agreed that I will be leaving at some point in the next six to twelve months.

I think this is a smart business decision for the company, and I’ve declined my boss’ offer for Oracle training. The company’s technology path and my career are going in opposite directions. I don’t want to be an Oracle DBA, and I don’t want to be cutting my teeth on a new technology during an ERP implementation. It takes years of experience with Oracle to manage their ERP system, and that’s why I think it’s a good idea to outsource that role.

This is an exciting time for me. The potential for a new opportunity is a bit overwhelming. Knowing that I will be out of a job is scary. At the same time, this is really good for me because a lot of the work I’m doing right now is application management and not that of a senior DBA. I have the time to really get it right.  To use a baseball analogy, I’m going to be cherry picking my opportunities.

Speaking at SQL Saturday last week gave me a new confidence. I’m good at what I do, and now I’m waiting for the right pitch to come across the plate.  In the meantime, I need to get my resume in order. I also need to get my work in a place where a more junior DBA can pick it up. And I need to start doing some serious networking to help provide a good supply of opportunities.

The real issue for me to figure out now is just what I want to do next in my career. Being in Boston gives me access to a lot of financial companies, which gives me an opportunity to grow in high availability and recovery. At the same time, those companies tend to be incredibly conservative. Being in Boston also gives me access to the high tech corridor and tons of startup companies. I’m not sure I’m ready to take on the growing pains of a rapidly growing company again. The last two taught me a lot about scalability and managing growth.

Consulting is an option, but I need to make sure I have access to good benefits. That leads me thinking that going solo isn’t a very good choice for me. Working for a small “boutique” consulitng shop would be incredibly appealing. I’d love to go out and do the tech work yet have someone else manage the business development end of it.

The good news here is that I don’t have to make any decisions right away. I have time to figure it out.

My First SQL Saturday #sqlsat71

Yesterday was SQL Saturday 71 Boston. It was held at the Babson College Executive Conference center, and it was a pretty amazing experience. Not only were the venue and food fantastic, but the event itself was pretty amazing.

SQL Saturday actually started on Friday evening with the speaker’s dinner. We met at the Top of the Hub in Boston for cocktails. I got to meet a ton of really smart people. We socialized and just had a great time. The sheer brainpower was unbelievable.

Saturday’s event was great. All of the sessions I attended were fantastic. I got to hear Andrew Kelly, Aaron Bertrand, and Adam Machanic all speak. These are incredibly smart people who really know their stuff. And getting to hang out in the speaker’s room, was amazing. Just listening to some of these guys and gals talk was… well… I just can’t articulate it.

My session was in the afternoon, and it went well. I was speaking on virtualization of SQL Server. What I had expected was an audience who didn’t have a lot of experience with virtualization. What I found was that I had a group who had virtualized their test environments and were starting to virtualize their production environments. I walked in there pretty calm and then found myself incredibly nervous when I had a more seasoned audience than expected. After I calmed down and slowed down my speaking a bit, I got into it.

Because my session was the success story of how we virtualized SQL Server in my company’s environment, my presentation wasn’t very visual. Now I realize that it needed something more appealing. And I should have been prepared with more technical details of what we did and how the environment looks today.

I found myself wrapping up my presentation 30 minutes into a 60 minute session. What did I to? I took the opportunity to turn it into a roundtable/Q&A session. And you know what? It worked!

Still the feedback was pretty good. And it helped me realize that I know what I’m talking about.  I think I want to keep doing this. I know some good stuff, and I want to continue to present it. Right now, I’m putting together my presentation proposals for two sessions for SQL Saturday 79 in South Florida.

And this has certainly cemented that I will be attending PASS in October.