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


The Best Database Administrators Automate Everything


The Best Database Administrators Automate Everything

Author
Message
John.Sansom
John.Sansom
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 1558
Comments posted to this topic are about the item The Best Database Administrators Automate Everything


John Sansom (@sqlBrit) | www.johnsansom.com
ianstirk
ianstirk
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 1037
Hi,

very nice article.

There's a chapter on the same topic (called the Self-healing database) in the book SQL Server DMVs in Action http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730/.

It contains SQL code for automatically: recompiling slow routines, rebuilding/reorganizing indexes, intelligently updating statistics, implementing missing indexes, and much more.

Chapters 1 and 3 are available for free at http://www.manning.com/stirk/

Enjoy
Ian
SQL-Expat
SQL-Expat
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 57
agree... but I still have not reached the holy grail of automating the patching, database failovers, reboots and failbacks of SQL servers in a 24x7 online environment. Has anyone?
happycat59
happycat59
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6431 Visits: 3270
I got to the point where I was constantly fighting fires and never really getting anything "useful" done. So, I decided that I would stop fighting the fires. Instead, I decided that they would eventually burn themselves out whilst I was getting on with getting things in good shape (i.e. fixing the underlying causes of those fires). To my surprise, the decision did not cause as big a problem as you would think. As I fixed the causes, my life got easier and easier. It became easier to do everything I needed to do (in the end, I was the only SQL DBA, managing 40 SQL Server installation (all quite similar installations) and about 10 other SQL Server installations that were unique).

So, get the right level of automation in place and you really can do some amazing things (and go home at a reasonable time of day, every day)



John.Sansom
John.Sansom
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 1558
SQL-Expat, PowerShell can certainly help you with some of these tasks.


John Sansom (@sqlBrit) | www.johnsansom.com
John.Sansom
John.Sansom
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 1558
happycat59, Well said sir!

You're not ignoring the problem by working towards actually fixing it for good. It's called paying down Technical Debt.

Thanks for sharing your story.


John Sansom (@sqlBrit) | www.johnsansom.com
Simon D Richards
Simon D Richards
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 639
John.Sansom (4/6/2012)
SQL-Expat, PowerShell can certainly help you with some of these tasks.


Agreed, I only picked up PowerShell a few months back, but it has been a life saver.

It has allowed me to:

- Populate a central repository of completed backups from all remote servers to watch for failures using SSRS.
- Script a remote start up and shut down of all SAP services on SQL clusters for monthly patching.
- Script the remote startup and shutdown of SQL services and bouncing of servers.
- Push SQL Alerts to all SQL servers to create some standards across our global infrastructure.

If you haven't used it yet, I would begin reading. Very easy to pick up.
SQL_EXPAT
SQL_EXPAT
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 383
Still on the topic of automating maintenance tasks.. consider the following monthly security patch maintenance steps on 100+ SQL Servers with 500+ databases:

1. install monthly security patches on all mirror servers (automated tool)
2. monitor mirror servers for any security patch related issues... (semi manual)
3. reboot all mirror servers (automated/scripted)
4. confirm successfull reboot and QA mirror servers (semi automated...)
5. failover all principals to mirrors (automated/scripted)
6. monitor mirrors (new principals) for any security patch related issue (semi automated...but requires manual checks)
7. ensure applications have redirected to mirrors
8. patch and reboot principals
9. confirm successfull reboot and QA principal servers
10. failback all databases to principals
11. QA - replication, mirroring, log shipping, application connections etc, etc

Now, most of the individual steps are scripted/automated. The tough part is having a master script or control that coordinates it all - something that poll all servers and only continue the sequence when required.. Curious if anyone has reached full automation on the above...

In some cases servers require 2/3 reboots.
Sometimes servers are in pending reboot state so require additional reboot before patching.
Some servers can take 30+ minutes to reboot...
Also, there's the suppressing of monitoring alerts during the maintenance too for things like Replication errors, log shipping latency, mirroring alerts etc.

thanks

SQL_EXPAT
artilugio
artilugio
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 159
Thanks for posting this article, it describes my main drive in my work.
In the past I used vbscript a lot, but became a convert of powershell and have never looked back since. I have noticed that I use SSIS a lot less since my powershell conversion :-)
Powershell has even displaced my favorite scripting language, the mighty Python (may it live forever).
John.Sansom
John.Sansom
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 1558
artilugio, my pleasure sir, I'm glad you enjoyed it.

Wait, do people even still use Python? ;-)


John Sansom (@sqlBrit) | www.johnsansom.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search