Some people hate repetition. They want to avoid manual tasks at all costs.
I used to be part of the cult of automation. I thought that everything should be made efficient. Why should I do something twice, if I can figure out how to do it once and then just run a script or push a button?
I don’t feel that way anymore about a lot of things.
One thing I do often is write scripts to reproduce problems or demonstrate how something works. Very often, there’s pretty similar setup steps in these scripts. I’ve written the following code hundreds of times:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* Recreate the test database with prejudice */ USE master; GO IF DB_ID ('IAMNotAProductionDatabase') IS NOT NULL BEGIN ALTER DATABASE IAMNotAProductionDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE IAMNotAProductionDatabase; END GO CREATE DATABASE IAMNotAProductionDatabase; GO |
It’s often useful to back up a demo database and restore it to avoid a long setup process. So I also frequently retype commands like this:
|
1 2 3 4 5 6 7 8 |
BACKUP DATABASE IAMNotAProductionDatabase TO DISK=N'S:\Backup\IAMNotAProductionDatabase.bak' WITH COMPRESSION, INIT; GO RESTORE DATABASE IAMNotAProductionDatabase FROM DISK=N'S:\Backup\IAMNotAProductionDatabase.bak' WITH NORECOVERY, REPLACE; GO /* I like to do this in a separate step because I am neurotic like that */ RESTORE DATABASE IAMNotAProductionDatabase WITH RECOVERY; GO |
I could use snippets in SQL Server Management Studio to reduce the typing. I could use a third party tool which auto-completes statements.
But I don’t. I re-type it every time. I was slow at first, but I’m much faster at it now.
These are commands I care about. Sure, they’re simple, they’re basic, they’re nothing fancy. But they’re really core to my profession and my job. I like being able to type these commands quickly and not wonder for too long about the syntax. I like not having to worry if I remembered to set up snippets in whatever test environment I’m using, or if it has third party tools installed.
I’m terrible at memorizing things, and this helps.
I’m not against automation. For production monitoring and responding to incidents, setting up tools and processes that help you work faster and avoid having one person as a single point of failure is awesome. Just don’t apply that principal to everything in your work.
There are some tasks that are fine to repeat, if you want to get to know them by heart.
Brent says: to learn more, check out our list of tasks you probably shouldn’t automate, and read the comments too – lively discussion in there.
Jeremiah says: I’m a huge fan of automation when you understand what you’re automating, why you’re automating it, and what can go wrong when you’re not watching. Otherwise, you need to be really careful.
Erik says: Just about everything I’ve automated has stemmed from a .sql file full of trial, error, and documentation. Know your processes, and make sure someone is made aware if they start failing.
22 Comments. Leave new
The Paradox of Automation in manufacturing is defined something like: the more efficient the automated system is, the more essential the human contribution that is needed to run the Automation System. Humans are less involved in heavily automated systems, but their involvement becomes more critical.
This theory underlies some of the core concepts behind DevOps. People think erroneously that the Ops Guys get automated out of a job whereas the reality is the skills become different/more “advanced”. This leads to the Irony of Automation in that the more you automate the more catastrophic the failures become because latent errors are being hidden by the automation.
All of this becomes clear when you look at the Three Mile Island incident.
Automation can be your best friend or your worst enemy. I completely agree that sometimes I prefer to just write the code just to ensure I don’t “forget” simple syntax. I’ve had to automate several business processes because let’s face it, it saves money in the long run when it’s done right.
In those experiences I will say one simple thing that I’ve heard echoed among many automation professionals. A crappy process will equate to a crappy automation. If you don’t make the manual process as efficient as possible before automation your likely to have problems once you start the magic.
Another item that Jeff touched on was ROI. If the process your automating won’t save money/time (because time is money these days) then that process probably shouldn’t be automated to begin with.
Another great article Kendra!
For me, good notification is the key to automation. This makes everything even more complicated, but if something goes wrong the automation steps aside and asks me (or someone on the team) to help (by sending emails and/or displaying messages to that effect).
This often means things grind to a halt when something goes wrong, but it’s far better than having your automation continue blindly destroying things.
Of course, good notification also involves reducing false positives, so it takes a lot of tuning. Hence, if it’s not worth the time investment, you probably shouldn’t automate it.
I’d say.. Don’t automate what you don’t fully understand.
Don’t use too much scripts other people wrote without fully grasping what they do.
But yes. Everything should be automated 🙂
SSIS is great for automation, to help combat “automation hysteria” I use an SMTP task for a failure at every step. Might have some seriously crazy looking SSIS packages when I open up VS, but at least I know (immediately) where the package failed when I get the email.
Automation is great for many things. But I don’t like to automate for the sake of automating it. I want to learn how things work first. Once I understand that process, then I can automate it. But automation does nothing if you don’t know it’s working. At a previous place of employment, we had some scripts that would ‘backup’ the database. But because they were wrapped in an exe (still don’t know why that was done), it would not bubble up failures. From a sql server job standpoint… They succeeded. You must have a way of verifying that your automation is working.
Key things to understand how they work before automating:
backup
recovery
permissions
They will pay dividends when your automation stops working. Trust me… It will.
I bring a military mindset to this. There are some things you want to verify. They are important enough that as the step is performed, you want to check things over. For instance, in Active Directory, if I move FSMO roles around, I take the time to do some checks. Yes, this transfer of roles can be scripted, but should one not transfer nicely, this is something I want to know immediately. So I will perform it manually.
If it isn’t critical, and it isn’t the situation Kendra describes, where you simply want to remember the commands because they are important for the job in general, I automate.
I just made the argument today against the automation of a particular process (an admittedly brittle de-identification routine designed to run against several databases with regularly changing schema). And I love automation, so it’s hard for me not to say “yes let’s put this in a package and run it automated”. Automation is an exceptionally nice hammer but not every process is a nail.
i’m big fan of automatisation but only if have understand process. thing one off beat innovation for pass year is powershell applet added into SQL i’m prefer t-sql but for some mixed process is verrry cool .
I feel this faboulus image belongs here: https://imgs.xkcd.com/comics/is_it_worth_the_time.png
It’s actually quite accurate and handy.
Like everything else in I.T. automation can be done well & done badly. Good automation is
– documented(briefly). Where is it? what does it do? what else is needed to make a script work?
– source control, versioning.
– logging
– error trapping & alerting on error.
Unfortunately much automation lacks these basics & ironically then scales very badly & turns into a big sprawling mess….
Automation is just a fancy word for Tooling. Theoretically, everything could still be done by banging rocks together, but for some reason humans decided that wasn’t the way to go. And these weren’t highly-educated and overly-degreed DBAs, either. Just a bunch of cavemen.
DBAs don’t seem to understand that things like fulcrums are a good idea, but rather remain constantly on watch, manually keying the most mundane commands over and over. I’d hate to see a group of DBAs screw in a lightbulb.
Saying automation is another word for tooling is like saying that “tank” is another word for vehicle.
A tank is very useful and definitely has a purpose, but you don’t need a tank every time you go to the store. Some of us like driving tanks, but we’re learning that it causes less issues to just drive a car now and then.
I am not clear, though, on why you think the people most likely to use tanks (IE, DBAs) would somehow be against them. Kendra is simply saying that she is learning that she doesn’t always need to use a tank, just because they are fun to drive and get the job done.
To use your analogy: sometimes a fulcrum is not the best tool. Sometimes you need a hammer or an ax.
“Not Everything Should be Automated”
Now that I think of it, this may be best illustrated by Microsoft’s own AutoShrink option!
Hi ,
Can you please write the Queries for Restore Automation Process
Thanks In Advance
Sure, download our First Responder Kit (click Tools at the top of the page).
Hi Brent Ozar ,
The Task is
we have 500 Gb Databases in one sqlserver and we have to create Disaster Recovery (DR) for this server ,
and clint is not giving the another server for DR can please suggest root cause for this and what should we ask for the client and is ready to give another server but it have to run for some time(2 hr ) and the all the updated data should be there in the DR server when ever his primary goes down we have to take DR to Up .All the server are AWS(Amazon Web Services)
Thanks
Mahendra
Mahendra – sure, click Consulting up top, schedule a free 30-minute call with us, and bring your client. We’ll see if we can get them the help they need. Thanks!
Hi ,
My SQLSERVER.EXE is more cpu usage can you please suggest that how can i solve this issue
Thanks
Mahendra
Mahendra – sure, go ahead and click Consulting at the top of the page. Thanks!
Hi Brent Ozar ,
Please help me on this issue in linux machine ,Remove Roles and users from postgres AWS RDS can you please suggest on this issue please sent me quires for this as soon as possible
Thanks in advance,
Regards ,
Mahendra
Hello again Mahendra,
For unrelated Q&A, head over to dba.stackexchange.com for answers.
Thanks!