DBAs need an in-office computer to run overnight and weekend performance logs, traces, and long-running queries. For example, let’s say you need to run a long series of maintenance queries to rebuild indexes over the weekend. You shouldn’t be remote-desktopping into your production servers to run this query: instead, you just need a simple workstation that you can leave logged-in, with the desktop locked, that runs the query.

If you were assigned a laptop as your primary workstation, then you’ll need an alternate desktop computer or an existing development server (never a production server). It doesn’t have to be 24/7 reliable or high-powered – a previous generation desktop or a virtual machine is fine. It should be a server that you and only you manage. Otherwise, someone else will restart the server at the most inopportune times, thereby shutting off your traces and performance logs.

Picking the Operating System

If your company uses SQL Server 2000, then this DBA machine should not run Windows Vista, since the 2000 tools don’t run on Vista. You can manage SQL Server 2000 using the 2005’s Management Studio, but sometimes managing a 2000 server is easier with the 2000 tools.

If the machine runs a workstation version of Windows (Windows 2000 Pro, Windows XP, Windows Vista) be aware that only one person will be able to remote into this machine at a time. If it runs a server version of Windows, then two people (or three, depending on how savvy they are) can remote into the machine. If you’ve got two or more database administrators, either use a server, or give each DBA their own workstation for management.

If the security folks ask tough questions about whether you need to be an administrator on the box, thank them for doing their job, and agree with them. (Hopefully they’ve been this tough about your production servers as well.) The only rights you need are the ability to remote-desktop in and run Perfmon and SQL. You don’t need to install anything else, start & stop services, etc.

Applications to Install

It does not need to be a SQL Server. It only needs the management tools installed, like 2000’s Enterprise Manager and 2005’s Management Studio. When you run traces (capturing queries and events from a SQL Server), you will need to save those events to another SQL Server, but it doesn’t have to be this machine. If you don’t have a development server, then – well, you’ve got other problems, but let’s start by fixing them and installing SQL Server 2005 Development Edition on this machine. During installation, change the database to point to a different drive, not the C drive. If you leave them on the C drive, and you save a large trace to this machine, it’s easy to run out of drive space and crash your machine altogether. That would not be good. If the databases are on the D drive, for example, you can still fill up the drive and crash SQL Server, but at least the entire machine won’t crash.

On this server, set up a shared folder called PerformanceLogs, and give read-only access to everyone. When you run performance logs, set them up to be saved in this directory. That way, when other users (developers, junior DBAs, network staff) need to interpret the performance numbers, you can point them to that share and they can always grab the latest copies without your assistance.

Install the free version of LogMeIn.com‘s remote control application.  This helps you remote control the machine from your house even when the company’s VPN is down or when you’re at a location where you can’t get the VPN software installed.  I can’t say enough good stuff about LogMeIn – and in fact, I wrote a separate blog post about LogMeIn.com too.

Next Steps

Once you’ve got your always-on workstation, here’s a couple of articles that you can read for your next step:

• Performance Monitoring with Perfmon – Perfmon is free, and you can use it from your always-on workstation to investigate performance problems with database servers.
• Free SQL Server tools – a list of utilities that you’ll want to install on your always-on workstation to do performance tuning, alerting, and so on.