InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 12:11:29 GMT20http://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxIndeed obvious and yet so tricky for many to actually do....Thanks for your comments!Fri, 27 Apr 2012 05:03:38 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxGood article, but rather obvious (who would have thought - automate :-) )One sentence that has value to me is: "The key thing with automation contrary to typical DBA practices is to not necessarily use the most appropriate tool or technology for the job but to use whatever tool that you can to get the job automated and done.". That is a good point.Fri, 27 Apr 2012 01:48:06 GMTpkrudyszhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxThanks for sharing those resources Pedro! I'm sure they will prove useful to other readers.Tue, 10 Apr 2012 08:53:01 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspx[quote][b]John.Sansom (4/6/2012)[/b][hr]Wait, do people even still use Python? ;-)[/quote]I have recently come back to DBAing and I was about to use Python to automate most of the tasks. From this thread's discussion I'd better take a better look into Powershell. Currently we don't have access to SSIS (long story) and that is why a full blow language/environment may come in handy. Anyway, here is a list of Python tools/packages I gathered for the current required reporting tasks I have to attend. This might come in handy for someone.MS-SQL Server connectivity:http://code.google.com/p/pymssql/ http://code.google.com/p/pyodbc/ Data Warehouse Extract-Transform-Load (ETL):http://pygrametl.org/ Object-Relational Mapping Frameworks:http://elixir.ematia.de/trac/wiki http://www.sqlalchemy.org/ http://sqlobject.org/ Python Language Integrated Query – Expression Trees:https://github.com/heynemann/pynq/wiki (Pynq)http://www.codebadger.com/blog/post/2009/06/01/Pythone28099s-LINQ-Equivalents-e28093-filter%28%29-map%28%29-and-list-comprehension.aspx (Native Python LINQ equivalents)Reporting in Python:http://www.geraldoreports.org/docs/index.html https://github.com/lightcaster/xlrep Python integration with other languages/libraries/architectures:http://jpype.sourceforge.net/ (Java)http://pythonnet.sourceforge.net/readme.html (.NET)http://sourceforge.net/projects/pywin32/?_test=b (Win32 Extensions)http://www.scipy.org/Weave (C/C++ inline) Excel and CSV File Integration:http://stackoverflow.com/questions/4257771/python-writing-to-excel-2007-files-xlsx-fileshttp://packages.python.org/Pyvot/tutorial.html http://docs.python.org/library/csv.html Portable Apps (if you need them):http://www.portablepython.com/ http://pydev.org/ (+ Eclipse Portable http://portableapps.com/node/28647 )Tue, 10 Apr 2012 08:48:58 GMTPedro Palhotohttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxThanks for your feedback Ryan! If something is not clear then I want to know about it and I appreciate you taking the time to explain your thinking.Tue, 10 Apr 2012 07:20:19 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspx[quote][b]John.Sansom (4/9/2012)[/b][hr]Hi Ryan,Consider that the article theme is to encourage the development of a mind set that looks to automate all processes, where applicable. It is not the aim of the article to explicitly identify what those processes are, as these are too varied from one Data Professional role to another i.e. the processes are often context specific.How would you suggest that the article could provide what you would consider to be sufficient depth, whilst at the same time not being process/scenario specific? Thanks for your comments.[/quote]I understand. The article left me wondering "are you talking about me, or just system admins that do database backups and whatnot?" After re-reading the article (slowly this time), it appears to be written for DBAs, but I see now these concepts can apply to everyone in the field.Tue, 10 Apr 2012 07:04:18 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspx[quote][b]slim.richard (4/9/2012)[/b][hr]....Not saying automation is bad just that you have to be careful to not overdo it and to have a good clean out periodically where you review a scripts usefulness and if any in built sql tools would be better utilised instead of a roll your own solution. For example they had written a log shipping solution (as in built log shipping wasn't very good when they wanted it) so you could easily get rid of that for the in built log shipping.....[/quote]Well said, automation is most certainly not "deploy and forget". It's about working more efficiently and maximising your resource (time) as a Data Professional, and a key part of that involves reviewing your existing automated solutions in light of technology developments and changing business requirements.Mon, 09 Apr 2012 23:57:41 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxyes. I agree...I like this I recently saw on a job description for a mySQL DBA for Facebook:"Candidates should have extensive experience in writing efficient automation software and a visceral aversion to doing the same task twice"Mon, 09 Apr 2012 20:19:54 GMTSQL-Expathttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxI'm not sure if I agree with the automate 'everything' fanaticism. Some things just add to your technical debt as you are creating code that needs to be managed and updated. My last job I spent 90% of the time fixing the hundreds of legacy automation scripts dozens of dba's had created over the 20 or so years the systems had been operational (all using there own styles and preferred tools). Every time we patched an instance there were hundreds of scripts that needed testing to make sure they still worked!Not saying automation is bad just that you have to be careful to not overdo it and to have a good clean out periodically where you review a scripts usefulness and if any in built sql tools would be better utilised instead of a roll your own solution. For example they had written a log shipping solution (as in built log shipping wasn't very good when they wanted it) so you could easily get rid of that for the in built log shipping.Overall I agree that automation is king but due to my experiences I am choosy about what I automate and especially the tools I use.Mon, 09 Apr 2012 20:07:08 GMTslim.richardhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxdlineberry, thanks for your comments sir.It sounds as though you are well versed in the art of automation and I imagine you probably have some great code/advice you could share with us.Mon, 09 Apr 2012 14:55:17 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxHi Ryan,Consider that the article theme is to encourage the development of a mind set that looks to automate all processes, where applicable. It is not the aim of the article to explicitly identify what those processes are, as these are too varied from one Data Professional role to another i.e. the processes are often context specific.How would you suggest that the article could provide what you would consider to be sufficient depth, whilst at the same time not being process/scenario specific? Thanks for your comments.Mon, 09 Apr 2012 14:45:48 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspx[quote][b]SQL_EXPAT (4/6/2012)[/b][hr]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 principals9. confirm successfull reboot and QA principal servers10. failback all databases to principals11. QA - replication, mirroring, log shipping, application connections etc, etcNow, 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.[/quote]Good concept but the article was really thin and didn't really go into examples and depth. I like this comment because it dives a bit deeper into what "automate everything" means.Mon, 09 Apr 2012 14:22:29 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxI completely agree with automating everything and have been doing that since day one. I automate everything from data loading processes, I probably have about 200 daily and monthly, to file management to ftp using primarily tsql and dos commands and am just now getting into powershell.Due to errors which occurred when I first started, I always include steps in my jobs to make sure the data is available, make sure it is the current days' data and make sure it is not in the process of being loaded as well as other similar verification processes. I do everything I can to not only automate but make sure they run reliably without error regardless of what the source has done, at least the best we can.I love the ideas listed for other automation purposes sql_expat listed. If you have scripts to share for some of those I'd love to see them.I am also in the process of doing exactly what blackheartbilly suggested, creating a central repository of sql backups. I have plans on implementing a process to schedule automated periodic restores, logging those, and providing them via sql reporting to management and business continuance for disaster recovery verification as well as meeting the needs of auditors. Black, if you don't mind sharing your script I'd love to see that as well. I already have a plan for how I intend on implementing this but would be interesting in comparing.Good article by the way. This is something I truly believe in and would have 10 people working for me otherwise or be coming in at 3:00am to make sure data was available and being processing data. I don't even know how I would do my job without automating; it is normally one of the first things on my mind the second a new data request comes in.Mon, 09 Apr 2012 10:20:31 GMTdlineberryhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxDefinitely. I agreed 100%. That is why I titled my web site "DBA Automation". Once you see the value, you will never go back.Not only you automate your tasks, you also hand over the lower level support effort to the helpdesk 1st level support. Some people could not connect only because user errors. DBAs should not have to deal with those cases.Some scenarios take more effort to test the automated scripts, for example, in a blocking production environment that blocking cannot be re-produced by will. But it can be done.Ian, I love your book.Jasonhttp://dbace.usFri, 06 Apr 2012 10:38:07 GMTjswong05http://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxHi Greg, it sounds like you're on the right track sir!Using a "DBA" database on each instance in order to manage your environment, store your scripts etc. is a great idea. Thankfully, the performance monitoring aspect of things was given a helping hand with the arrival of the Performance Data Warehouse (PDW) feature of SQL Server.Thanks for your comments.Fri, 06 Apr 2012 09:57:41 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxJohn,Thanks for posting. I've automated as many tasks as possible using a combination of TSQL, Powershell and VBscripts. One thing I have done is built a template and rolled it out to every instance (2005 and higher) that I administer. As a DBA who supports about 100 different instances (from version 2000 to 2008 R2 and everything in between.) this became absolutely necessary.When I build an instance, I create a database on it used to gather performance statistics. It typically contains retention on the performance data of about 2 weeks. I then have a a template of queries used to analyze the data to obtain the typical load the server is incurring. Which really simplifies the process and frees up my time.Fri, 06 Apr 2012 09:46:43 GMTGreg.Jacksonhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxartilugio, my pleasure sir, I'm glad you enjoyed it.Wait, do people even still use Python? ;-)Fri, 06 Apr 2012 08:13:01 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxThanks 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).Fri, 06 Apr 2012 08:05:10 GMTartilugiohttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxStill 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 principals9. confirm successfull reboot and QA principal servers10. failback all databases to principals11. QA - replication, mirroring, log shipping, application connections etc, etcNow, 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.Fri, 06 Apr 2012 07:40:08 GMTSQL_EXPAThttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspx[quote][b]John.Sansom (4/6/2012)[/b][hr]SQL-Expat, PowerShell can certainly help you with some of these tasks.[/quote]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.Fri, 06 Apr 2012 07:06:10 GMTSimon D Richardshttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxhappycat59, 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.Fri, 06 Apr 2012 06:12:24 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxSQL-Expat, PowerShell can certainly help you with some of these tasks.Fri, 06 Apr 2012 06:09:15 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxI 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)Fri, 06 Apr 2012 05:10:56 GMThappycat59http://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxagree... 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?Fri, 06 Apr 2012 01:57:56 GMTSQL-Expathttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxHi,very nice article.There's a chapter on the same topic (called the Self-healing database) in the book [b]SQL Server DMVs in Action[/b] [url]http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730/[/url].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 [url]http://www.manning.com/stirk/[/url]EnjoyIanFri, 06 Apr 2012 01:04:25 GMTianstirkhttp://www.sqlservercentral.com/Forums/Topic1279311-1576-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Administration/88600/">The Best Database Administrators Automate Everything</A>[/B]Thu, 05 Apr 2012 22:19:19 GMTJohn.Sansom