SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Database Cattle

By Steve Jones,

There's a saying in the DevOps world that we want to treat our servers like cattle, not pets. This really means that we don't want to get attached to any of them. Each is a resource, and we should be able to easily replace it with another. One of my web servers (or DNS, file, print, etc. servers) has an issue? I destroy it and replace it with another. In many cases, this makes perfect sense, though it does require adopting some DevOps approaches like treating our configuration as code and having a way to easily spin up new machines, VMs, or containers.

In the database world, at least the relational world, we've tended to treat our database instances, and really servers, much more like pets. We craft them, raise them, and lavish attention on them to ensure they live strong and long. We hate for them to die, because it's a pain to rebuild a SQL Server. I'm sure more than a few of you don't even want to attempt the process, worrying about forgetting some setting, driver, or other item that was long ago installed on your system. Even in cases where a cluster or other HA technology exists, most people dread having to replace a SQL Server instance.

That shouldn't be the case. We should treat our machines like cattle, and be ready to easily rebuild one of them at any time. We should be ready, at least for DR purposes, to reinstall SQL Server on a new host, with the same settings, restore our databases, users, linked servers, jobs, etc. That means we need to ensure we have all that information ready. While plenty of that is stored inside the various system databases in SQL Server, could you move a single database to a new instance?

Many people would struggle with this, which is not a great situation. Not for the person, not for the organization, and not for our industry as a whole. We often find ourselves ill-prepared to recover from issues when our server experiences catastrophic failures. I would hope we could do better.

As much as I appreciate the idea of DevOps helping software developers produce better software faster, I think it's just as important that operations staff adopt the practices as well. Store all configuration as code, in a version control system, and build processes to automatically apply changes to your instance. Then store that process call or code so that you are prepared to recreate an instance in an instant. If you get that working, add to your process and have a way to check that your instance actually conforms to your desired configuration. PoSh DSC, Chef, Puppet, or some other tool can get you started.

SQL Server has improved across its versions to make it easier for us to build database cattle, and get away from database pets. All you need to do is take advantage of the tools, capabilities, and best practices to do so.

Total article views: 107 | Views in the last 30 days: 1
Related Articles

Kill User Processes Per Database or Server Wide

Kill any user processes per database or for the entire server instance.


SQL Server 2005 Instance

Removing a SQL Server 2005 Database Instance


Size of database files for an instance of SQL Server

Size of database files for an instance of SQL Server


datetime - 1/1/1900 treated as blank??

(not, blank treated as 1/1/1900)


Treat the Database like Code

Steve Jones reminds us that our databases contain code, and as such, they ought to be under some typ...