VMWare Certification – VCA-DCV

VMWare was recently doing a free promo of their new certification track ‘Associate’ and it caught my interest.

I decide to take the exam and now I am a VMware Certified Associate – Data Center Virtualization (VCA-DCV).

It was actually trickier than I envisioned. I would like to think I know a few things about VMWare, however I really haven’t used it for too long.  Overall, I would say that the exam does a pretty good job of assessing basic fundamental applications knowledge of the VMWare environment and what it means to virtualize servers.


Deadlocks — Are you watching them?

One of the strangest things I think is that SQL Server does not alert you to deadlocks by default.  I recently deployed some deadlock monitoring out so that I could be more aware of what exactly is happening on my servers.  Those of you with third party monitoring tools would already have similar functionality — this is mostly for those that are not spending a lot of money on monitoring software.  The foundation for this is available at http://msdn.microsoft.com/en-us/library/ms186385.aspx.

Things to watch for:

Third party applications seem to just throw deadlocks all of the time.  Some of them, if you google, simply suggest turning on snapshot isolation so that you get versioning.  Please make sure your tempdb situation is under control before taking this advice.

Sharepoint still sucks and the crawls seem to throw a lot of deadlocks.

And now to the fun part, the scripting!

I put this table in master, feel free to put it somewhere else.  I do most of my administration through my central management server so it makes deployment very simple.  As always, never deploy anything without knowing exactly what it is doing and always always always test on a development server first.

USE [master]

/****** Object: Table [dbo].[DeadlockEvents] Script Date: 10/30/2013 2:06:06 PM ******/


CREATE TABLE [dbo].[DeadlockEvents](
[AlertTime] [datetime] NULL,
[DeadlockGraph] [xml] NULL



Next up create a SQL Agent Job to that will be called by the alert you are going to create in a little bit.
USE [msdb]

/****** Object: Job [[Monitor]] Capture Deadlock Graph] Script Date: 10/30/2013 2:06:33 PM ******/
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/30/2013 2:06:34 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'[Monitor] Capture Deadlock Graph’,
@description=N’Job for responding to DEADLOCK_GRAPH events’,
@category_name=N'[Uncategorized (Local)]’,
@notify_email_operator_name=N’SQL Server Notifications’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Insert graph into DeadlockEvents] Script Date: 10/30/2013 2:06:34 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Insert graph into DeadlockEvents’,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData)))”)

EXEC msdb.dbo.sp_send_dbmail
@body = N”$(ESCAPE_SQUOTE(WMI(TextData)))”,
@subject = ”DeadLock Occurred On Server”,
@importance = ”High” ;’,
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave


And now you will create the alert:
USE [msdb]

/****** Object: Alert [DeadlockWMI] Script Date: 10/30/2013 2:07:17 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’DeadlockWMI’,
@job_name=N'[Monitor] Capture Deadlock Graph’

Note:  This uses WMI.

Finally — you will want to turn on a little check box in SQL Agent, but since we like to make as many changes through queries [CMS] as possible:

USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1

Now the final step is that you do need to restart SQL Agent in order to get these working properly.  Make sure you do not skip this step.

Extra fun — what about blocking?

Here is the alert:

USE [msdb]

/****** Object: Alert [BlockingWMI] Script Date: 11/13/2013 7:28:29 AM ******/
EXEC msdb.dbo.sp_add_alert @name=N’BlockingWMI’,
@wmi_query=N’SELECT * FROM Blocked_Process_report’,
@job_name=N’Put Your Job Here’

A small addition for blocked processes — you will want to use sp_configure and determine what triggers a blocked process:

blocked process threshold (s) is the configuration item, so if you want it to trigger a blocked process after twenty seconds of blocking then do:

sp_configure ‘blocked process threshold (s)’, 20

Questions? Comments?  Leave a message!

SQL Enterprise Policy Management

I implemented the Enterprise Policy Management a long time ago but ran into some issues that some other people may be having similar issues.

The EPM can be found at: http://epmframework.codeplex.com/

The problem I had occurred after it had been running for a few weeks and was due to the process timing out.

The actual error is:

The corresponding line is ‘Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail”  -ErrorAction Stop’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  The statement has been terminated.  ‘

There are two ways to fix this depending on what you want to do — the easiest way is to open up the powershell script and scroll all the way to the bottom and find this line:

Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail”  -ErrorAction Stop

and replace it with:

Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail”  -ErrorAction Stop -querytimeout 0

If that doesn’t work you may need to put a real value in for querytimeout.  I believe this may be the case for servers before SQL 2012.

The other method is to remove that line from the powershell script [or just comment it out with the #] and add a new step to your SQL agent job that has a target database of your warehouse and use:  exec policy.epm_LoadPolicyHistoryDetail.

Voila, you are back in business!

Questions?  Comments?  Leave a note!

Replication – Adding an Article

I have encountered this problem multiple times and it seems that a lot of people think that you have to recreate the publication / subscription in order to add articles.  This is *NOT* the case.

It is actually a very simple task!

Right-click on your publication and click ‘Properties’

Go to the articles listing and uncheck the ‘Show only checked articles in the list’ box.

Now you can add a new table to your replication.

Next: The key step — go to the Agents tab inside of replication monitor for the publication in question.  Right click on the Snapshot agent and select ‘Start Agent’.  This will snapshot the table and send it across!

Pretty easy!

Questions? Comments?  Leave a note!

Availability Groups – SQL Agent Jobs

One thing that was strange to me is that with the advent of Availability Groups in SQL Server 2012 is that there isn’t a built in way to have SQL Agent Jobs run specifically for the active AG.

The workaround I have gone with is just adding a statement around the Job Step and then deploying the new version to both [all] nodes.

The script is very simple:

IF( select CASE
WHEN primary_replica = @@servername Then 1
from sys.dm_hadr_availability_group_states) = 1
–Here is where you would put the code for your job step

And that is it!

Deploy to all of your nodes and you are set moving forward no matter which node becomes the primary.


Questions? Comments?  Please post!

SQL Saturday Denver!


For those of you in the Denver area — a reminder that SQL Saturday is this weekend!



SQL Policy Management – Compatibility Level

After implementing Policy Management company-wide I decided to expand the built in set of policies that comes from the Microsoft offering.

Implementing a check against compatibility level differences is a little beyond the normal scripts but still relatively easy once you figure out some of the minor issues.


Create a new condition.

You want to use the facet type of “Database”.

In field you will want to paste:

ExecuteSql(‘Numeric’, ‘
SELECT compatibility_level
FROM sys.databases WHERE [name] = ”model”’)  since model is where the server compatibility level should be configured.

Operator will be “=” and the value will be @CompatibilityLevel.

Name your condition and save.

Next up — create your Policy.

Name it whatever you want and then use your newly created check condition.

Check against targets ‘online user databases’ and evaluate on demand.  Save it and then evaluate against some test servers!

Comments?  Questions?  Drop a message!