SQL Server DBA / sqlLAB

Encrypted Stored Procedures for Better Labs

Most DBAs have a healthy number of test labs on hand. These are great for experimenting in areas your are unfamiliar with, or simply a comfort net if you want to really dig deep into a topic. However, I think the traditional test lab is limited and suggest that encrypted stored procedures may help to enhance the learning experience by enabling scenario-based approach to labs.

Here’s the problem that I see with test labs: they are too obvious. Let me illustrate what I mean a story of my very own.

‘Joe’ is a DBA who, like me, is quite new to the profession. Recently Joe has been talking to his team about transactional replication and Joe feels like he understands the concepts pretty well. But, he is not feeling very good about having to troubleshoot replication at 2:00 AM with “the panicking client” screaming down the phone. *GULP*. Because Joe is a proactive DBA, he has decided to set up his very own lab.

Now Joe is smart, he subscribes to SQL Server Central’s email newletters and he knows they have a Stairway to Replication series. So Joe begins by going through this. By the end of it, Joe has created a few publications and subscriptions and he has successfully got replication running in his lab. Wow – that was easy, no sweat. Didn’t even take him very long.

Did I mention that Joe was smart? Well, he is. He knows that the world is happy… while everything is working the way it should. But when it breaks at 2:00 AM in the morning, Joe is going to have to deal with “the panicking client”. So Joe sets out to break replication in his lab and then fix it.

The first thing Joe does is turn off the subscriber instance. Literally like pulling the power plug. Opening up the replication monitor, Joe can see that replication has stalled. Ah ha! he thinks, that’s what that looks like – well I can fix that. Joe calls “the panicking client’ back and tells him to restart the subscriber instance. Thanks Joe.

Joe goes back to his team, shares his success story and asks them about other common replication issues. Armed with new ideas, he goes back to his lab and “breaks” the replication in a couple of creative ways and then fixes his mistake. After a day in the lab, Joe is feeling confident about that 2:00 AM call out. Good work Joe!

Joe’s story is pretty typical of the “lab experience”. But when it turns to custard in the real world – you haven’t normally been watching the culprit in action. Joe did really well in his lab, and he got a feel for what common scenarios might look like and what to look out for. But the bottom line is, Joe knew what was wrong – he planned it! A more realistic lab environment would have you fixing a mysterious issue that you know nothing about. Now that would be interesting.

This is where encrypted stored procedures come in. A range of scenarios could be encapsulated in a stored procedure, which could then be encrypted – making it that little bit harder for the lab user to cheat and peek into the code (not impossible, but they would really have to cheat!). Of course the downside is that someone would have to engineer the labs for use by other team members. But that’s a small price for quality training.

Here’s an example. sp_ScenarioOne (below) removes even-numbered rows of data from the subscriber instance in Joe’s lab. The lab instructions ask Joe to run this sproc, identify the issue, identify the database objects involved and finally get replication back up and running. The code for sp_ScenarioOne is below:

USE [joesLab];

IF OBJECT_ID(N'nsql.sp_ScenarioOne', N'P') IS NOT NULL
  DROP PROCEDURE [sp_ScenarioOne];

-- to be run on the SUBSCRIBER instance
CREATE PROCEDURE [lab].[sp_ScenarioOne]
  DELETE FROM exampleTable
  WHERE exampleRowID % 2 = 0;

EXECUTE [nsql].sp_testEncryptedSproc

After executing this sproc, Joe should have absolutely no idea what to expect. But replication will definitely not like it! Hopefully he will check the error logs and see:

… The row was not found at the Subscriber When Applying the replicated command.

With a bit of ingenuity perhaps Joe will even expand the distribution database in the object explorer. Perhaps he might see the dbo.MSrepl_errors table, or the dbo.MSrepl_commands table. If he does, he will pretty soon come across the publisher database name, the publication and specific articles involved. Now that is some good investigative-DBA work. Well done Joe!


I believe that traditional labs are just to obvious. None of us work in a perfectly managed environment, we are always dealing with errors and requests from clients. The truth is, DBA work isn’t all sunshine and walks in the park – there are thunderstorms and 2:00 AM call outs. I am promoting a scenario-based approach; an approach that encourages lateral thinking, team work and just good old investigative -DBA action.


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s