Transcript SQL Server on VMWare

Automating
Common DBA
Tasks
Jonathan Kehayias
MCITP Database Administrator
SQL Server MVP
http://sqlblog.com/blogs/jonathan_kehayias
[email protected]
http://code.msdn.microsoft.com/SQLExamples/
Wiki/View.aspx?title=AutomatedDBA
Agenda
Why Automate?
What can we Automate?
How can we Automate?
Putting it all together (Demonstrations)
Monitoring your Monitoring?
Additional Tools Available
Questions
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
Why Automate?
Predictable Results
Reduce workload - do more in less time
Demonstration of Value
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
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
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
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
What to Automate (Backups and
Maintenance)
Database Maintenance Plans
Custom Scripts
Custom Schedules
DEMO BACKUPS AND MAINTENANCE
AUTOMATION
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
What to Automate (Drive Free
Space)
DTS/SSIS Package
WMI with VBScript
COM with TSQL
DEMO DRIVE FREE SPACE AUTOMATION
What to Automate (Database Free
Space)
TSQL
VBScript with WMI or DMO
SMO with PowerShell
DEMO DATABASE FREE SPACE
AUTOMATION
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
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
What to Automate (Monitoring
Performance)
SQL Agent Alerts
WMI Alerts
SQLH2 Performance Collector
TSQL DMV Queries
DEMO DATABASE FREE SPACE
AUTOMATION
Monitoring your Monitoring?
How do you know your monitoring is working?
Can you trust your monitoring?
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
Questions
Resources
The DBA Checklist (Buck Woody)
Microsoft Internal Database Operation Team Scripts
Automating DBA Processes (TechEd 2008)
Automating Common SQL Server Tasks using DMO