Automation in SQL Server | T-SQL Tuesday #015 #TSQL2sday

T-SQL Tuesday

@SqlAsylum is hosting a blog discussion on *Automation in SQL Server* so if you value your time then invest a little of it and go there to learn some tips and tricks.  Also, you cannot go wrong listening to this group of sages, @msdtjones, @kekline, @buckwoody and @way0utwest, in Panel Discussion at TechEd 2008 on Automating DBA Processes. It is forty minutes long but you should bring in a sack lunch and listen to it at your desk someday soon.  One last recommended prequel to this post, please read @JohnSansom explaining why The Best Database Administrators Automate Everything.


In John’s post he states, “use whatever tool that you can to get the job automated.”  Automation is similar to pizza in that even bad pizza is still pretty good.  Start somewhere and then let the continuous improvement process (CIP) kick in for those frequently used and high value processes. If you are ready to automate *everything*, here are some tools to get you started.

Synchronous Tools

Triggers come in three varieties which are based on three event group types: Data Manipulation Language (DML), Data Definition Language (DDL) and Logon.  Triggers are special because they run within the scope of any existing transaction which enables them to perform a rollback when one exists.  This allows automation to be preventative instead of reactive.  Trigger coded responses can be written in Transact-SQL (T-SQL) or Common Language Runtime (CLR).  DML Triggers can fire before or after data changes occur.  DDL Triggers can be created with a server or database scope. (NOTE: Policy-Based Management is a higher level technology that uses DDL Triggers for the evaluation mode On Change with Prevent.)

Extended Events are a “personal favorite amongst the new features in SQL Server 2008” for @SQLSarg and @BobBeauch says they are “one of my favorite parts of the product.”  If those quotes don’t seem profound then please trust me that they are.  Although they are listed under synchronous tools, you can collect asynchronously and with rollover and with allow event loss.  Extended Events run in process but they do not natively support coded responses so coded responses would run out of process and therefore be asynchronous.  Extended Events exposes the largest set of events.  Extended Events are special because they can be sent to Event Tracing for Windows (ETW).  ETW allows for merging Extended Events, operating system (OS) events and events of any application with supporting execution points.  (NOTE: Server Audit is a higher level technology that uses Extended Events)

Change Tracking only exposes events about table row changes.  It does not natively support coded responses so coded responses would run out of process and therefore be asynchronous.  Change Tracking does not provide much value over what you could do with DML Triggers.  @PaulRandal describes Change Tracking as a technology to help developers avoid the requirement “to create custom solutions for their applications, usually involving timestamp columns, DML (Data Manipulation Language) triggers, and extra tables.

Asynchronous Tools

Alerts are built for monitoring and responding to events.  Want an automated response to a SQL Server event such as a certain error number, a certain error message word or a certain error severity?  Use an alert.  Want an automated response to a SQL Server performance condition?  Use an alert.  Want an automated response to a Windows Management Instrumentation (WMI) event (includes DDL events and SQL Trace events)?  Use an alert.  Alerts natively support coded responses in the form of a SQL Server Agent job.  They can be written in any language you can put into a job step!  Why don’t we use these wonderful things more often?

Event Notifications is well suited to automation because it includes a queued messaging process enabling coded responses (Asynchronous Triggers).  This process is built upon Service Broker <shudder> and allows you to use T-SQL or CLR languages.  Event Notifications are like an enterprise-scale version of Alerts.  Instead of an instance responding to conditions itself, it reports them to a higher authority designated for responding to.  Event Notifications exposes many DDL events and SQL Trace events but not Extended Events. 😦

SQL Trace is similar to Extended Events.  In fact Mike Wachal stated with ““Denali” we are moving our diagnostic tracing capabilities forward by building a system on top of Extended Events.”  It looks like default trace still exists in Denali but the system_health session may overtake it in the future.  You might want to bite the bullet now and avoid automating with this tool.  SQL Trace exposes many events but is still a subset when compared to Extended Events.

Change Data Capture is basically Change Tracking except it keeps historical data and operates asynchronously.

Scheduling Tools

Agent is a service installed for SQL Server automation.  Although jobs can be coded responses to Alerts and events, Agent is listed here for its ability to run jobs at predefined scheduled intervals.  Agent is often part of database maintenance solutions like Ola Hallengren’s award winning solution “based on stored procedures, functions, sqlcmd and SQL Server Agent jobs.”  Using predefined scheduled intervals can help meet Service Level Agreements (SLAs), meet time-based business requirements and avoid resource competition with other activity. There are five Replication job step types as well as job step types for ActiveX Script, CmdExec, PowerShell, SSAS Command, SSAS Query, SSIS Package and T-SQL.  There are two event schedule types and they are: Agent service started and Central Processing Unit (CPU) idle.

On-demand Tools

This list includes applications, utilities and saved code that a person could use to manually initiate a response.  These tools enable semi-automation of a response and can provide value through consistency.  In cases where a decision cannot be codified due to complexity or storing the decision-making variables is undesirable, this is as close to automation as you can get.

See Also

Policy-Based Management (PBM)
Maintenance Plan Wizard
Server Audit
Task Scheduler
Service Broker

This post made possible by

Pat Wright
Adam Machanic
Dan Jones
Kevin Kline
Buck Woody
Steve Jones
John Sansom
Jonathan Kehayias
Bob Beauchemin
Mike Wachal
Bob Ward
Ola Hallengren


About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s