1 / 19

Automating Common DBA Tasks

Automating Common DBA Tasks. Jonathan Kehayias MCITP Database Administrator SQL Server MVP http://sqlblog.com/blogs/jonathan_kehayias jmkehayias@sqlclr.net http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA. Agenda. Why Automate? What can we Automate?

urielle-horton
Download Presentation

Automating Common DBA Tasks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Automating Common DBA Tasks Jonathan Kehayias MCITP Database Administrator SQL Server MVP http://sqlblog.com/blogs/jonathan_kehayias jmkehayias@sqlclr.net http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA

  2. Agenda • Why Automate? • What can we Automate? • How can we Automate? • Putting it all together (Demonstrations) • Monitoring your Monitoring? • Additional Tools Available • Questions

  3. Who am I? SQL Database Administrator OSI Restaurant Partners SQL Server MVP MSDN Forums Moderator Founder SQLCLR.net Member Tampa SQL Users Group PASS Member

  4. Why Automate? • Predictable Results • Reduce workload - do more in less time • Demonstration of Value

  5. What can we Automate? • Installation and Configuration SQL Server Software • Backups and Database Maintenance • Log Checking • SQL Server Error Log • Server Event Logs • Check Drive/Database Free Space • Check Job Success/Failure History • Monitor Performance • Multi-step operations

  6. How can we Automate? • SQL Server Agent • DTS/SSIS packages • Stored Procedures • VBscript/ActiveX • CmdExec Operating System processes • Windows Task Scheduler • VBScripts • .NET Applications • Powershell • Dos Batch Files • Multistep processes

  7. SQL Server Agent • Pros • Advanced Scheduling • Built in Alerting • Built in Logging • Detailed Execution History • Easily scripted against • Cons • Dependency on SQL Services • Non-SQL Admins require SQL Access to view schedules • Tasks Execute under the Service Account unless setup with a proxy

  8. Windows Task Scheduler • Pros • Does not depend on SQL Services • Non-SQL Admins can see schedules • Reduced Security through use of Run-As • Cons • Lacks Detailed History of SQL Agent • Logging must be done by operation being run • Requires additional steps to connect with SQL • Not easily scriptable

  9. What to Automate (Backups and Maintenance) • Database Maintenance Plans • Custom Scripts • Custom Schedules DEMO BACKUPS AND MAINTENANCE AUTOMATION

  10. What to Automate (SQL Server and Windows Logs) • Log Rollover and Retention • Exception based Alerting • Aggregation of Events (Multi-Server) DEMO SQL ERROR LOG AUTOMATION

  11. What to Automate (Drive Free Space) • DTS/SSIS Package • WMI with VBScript • COM with TSQL DEMO DRIVE FREE SPACE AUTOMATION

  12. What to Automate (Database Free Space) • TSQL • VBScript with WMI or DMO • SMO with PowerShell DEMO DATABASE FREE SPACE AUTOMATION

  13. What to Automate (SQL Agent Job History) • TSQL • Adhoc Queries • Trigger on sysjobhistory • VBScript with WMI or DMO • SMO with PowerShell DEMO SQL AGENT JOB HISTORY AUTOMATION

  14. What to Automate (Multi-Step Operations) • TSQL • VBScript with WMI or DMO or TSQL • SMO with PowerShell/.NET • DOS Batch with osql and sqlcmd DEMO MULTI-STEP OPERATIONS AUTOMATION

  15. What to Automate (Monitoring Performance) • SQL Agent Alerts • WMI Alerts • SQLH2 Performance Collector • TSQL DMV Queries DEMO DATABASE FREE SPACE AUTOMATION

  16. Monitoring your Monitoring? • How do you know your monitoring is working? • Can you trust your monitoring?

  17. Additional Tools • Free Tools Available • SQL Server PowerShell Extensions • SQL Server Health & History (SQLH2) • Recently Released Open Source • SqlMonitoring Tool • Non-Free Tools • Quest • Red-Gate • Idera • SQL Server 2008

  18. Questions

  19. Resources • The DBA Checklist (Buck Woody) • Microsoft Internal Database Operation Team Scripts • Automating DBA Processes (TechEd 2008) • Automating Common SQL Server Tasks using DMO

More Related