SQL Server User Defined Function Synchronization: The “yo dawg” method.

A client we’ve worked with for some time had an interesting SQL problem for me recently. These guys usually have interesting work, but this was particularly interesting.

The Problem:

There are multiple SQL Server 2005 servers with common user defined functions on them. The functions on these servers use data that is local to the server, meaning that being able to use the functions remotely via a linked server connection is not enough. Since this isn’t sufficient, the client would like to ensure that identical versions of the common functions are maintained on each server.

This problem is interesting to me, because as soon as I’d finished reading the email I had a solution in mind. The solution that was brewing in my mind involved using a DDL Trigger to catch any statements that would change a function and then issue those same statements on the other servers. I don’t frequently use database triggers in my day to day work, but I think they’re kind of neat. In my mind this solution is much simpler than trying to do any of this through any form of replication. Replication would have involved setting up servers as distributers and subscribers which is quite the hassle just for one specific kind of object.

About Triggers:

First off, let’s look at triggers in SQL Server. The SQL Server 2008 Books Online entry about triggers describes them thus: “A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server”. There are three types of events that a trigger can be set to fire on.

1. Data Manipulation Language events – These are commands that manipulate data, such as INSERT, UPDATE, DELETE.

2. Data Definition Language events – These are command that change or create object definitions, such as CREATE, ALTER, DROP.

3. Logon events – These triggers respond to the LOGON event when a user logs into a SQL Server instance.

When creating a trigger you can specify exactly which events you want the trigger to respond to, and what the trigger should do when that event occurs. You can also specify what scope the trigger should operate under. DML triggers can be scope to a view or a table within a specific database. DDL triggers can be scoped to the database or server instance level. Logon Triggers must be scoped to an entire server instance. A trigger scoped to an entire server instance can seem unwieldly, but it might make sense for something general like change control, security auditing, or logging.

With that covered, we can get back to the problem at hand.

Creating the Trigger:

The assumptions for this problem are that there is a common database, with common functions on two or more SQL Servers. In my case, the SQL Servers are all 2005 Standard Edition, though this works with SQL Server 2008 as well. The goal is to propagate any changes to user defined functions from one server to the other server(s).

The entire plan is to create a DDL trigger on each database that will respond to ALTER FUNCTION, CREATE FUNCTION, and DROP FUNCTION by catching the commands that were run, and running them on the other servers.

Let’s take a look at the code to do this.

CREATE TRIGGER SyncFunctions
ON DATABASE
FOR ALTER_FUNCTION, CREATE_FUNCTION, DROP_FUNCTION

AS

PRINT 'HELLO WORLD'

The CREATE TRIGGER command is used to create a new trigger. Since we’re creating a DDL trigger I’ve specified ON DATABASE to restrict the trigger to the database with the common functions. I’ve also specified that I want the trigger to respond to the SQL command that will change a function. This could easily be extended to synchronize stored procedures as well. I’ve given a basic trigger body to prove that my trigger is working. If you’re playing along at home, you can execute the code I’ve given above and then perform a command to manipulate a function and you should get the expected result, a print of HELLO WORLD.

Capturing Event Data:

So, we’ve got a trigger that responds to the events we’re interested in. Great. The next part of this problem is catching what exact SQL command was being run when the trigger condition was encountered. Fortunately for us, SQL Server has a function that provides contextual information about how the DDL trigger was fired. The function EVENTDATA() returns an xml value containing this information. Specifically, we’re interested in exactly what SQL command was executed, and what database it was executed against. We could, theoretically, hardcode the database name since we have it as one of our assumptions, but it’s just as easy to capture it here. Below is the new version of our trigger, with code to capture these details.

CREATE TRIGGER SyncFunctions
ON DATABASE
FOR ALTER_FUNCTION, CREATE_FUNCTION, DROP_FUNCTION

AS

SET NOCOUNT ON 
SET XACT_ABORT ON

DECLARE @data XML
SET @data = EVENTDATA()

DECLARE @command VARCHAR(MAX)
SET @command = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(MAX)')

DECLARE @database VARCHAR(MAX)
SET @database = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(MAX)')

I’ve removed the PRINT ‘HELLO WORLD’ statement, because it’s time to get to business. In it’s place, I’ve added SET NOCOUNT ON. This will prevent the trigger from returning any messages about the number of rows effected. I’ve also added the SET XACT_ABORT ON statement. This will cause SQL Server to rollback the entire transaction if a run-time error is encountered. This statement is important to prevent a potential problem down the road.

Next, I’ve created a variable of the XML type and set it to the results of EVENTDATA(). I’ve then used the .value(…) xml method to perform an XQuery against the event data. .value(…) takes two parameters, the XQuery expression and the SQL datatype that it should return the result as. The expression @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, VARCHAR(MAX)’) will return the issued SQL command as a varchar(max). I’ve chosen this datatype because it is unknown how long the SQL command could be. I’ve added similar SQL to capture the database name that the query was run on.

Now that we’ve got the command that was run, and the database it was run on, we need to figure out how to execute this command on the other servers. We will have to set the other servers up as linked servers. This isn’t a difficult process, but I believe it’s out of scope of this article, so I will provide a link to the SQL Server 2008 Books Online article for linking servers. (http://msdn.microsoft.com/en-us/library/ms188279.aspx).

The Perilous Path of RPC:

After your servers are linked, verify that they are working by issuing some queries back and forth. Now, the real trick here is how to issue the query that fired the trigger on the other server. We can use the EXECUTE() command to execute a prepared SQL query from a string. A target server can also be specified using the AT [SERVER] syntax. Our approach will be to prepare the command for execution, and then use the EXECUTE() command to execute it on the target servers.

This approach has a few pitfalls however. Even though your linked servers are set up and work for things like selecting out of remote tables, or updating remote records, they most likely won’t work right out of the box for executing commands remotely with EXECUTE(). I’m going to attempt to cover all of the things that can go wrong with EXECUTE() against linked servers first, before the code. I will probably miss something. This is a particularly thorny path.

1. Server ‘RemoteServer’ is not configured for RPC.

Answer: Run the following SQL to enable Remote Procedure Calls on your target linked server. This is not a default option for linked servers.

exec sp_serveroption @server='RemoteServer', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='RemoteServer', @optname='rpc out', @optvalue='true'

2. MSDTC on server ‘RemoteServer’ is unavailable.

Answer: Make sure that the Distributed Transaction Coordinator service is running all involved SQL Servers. This is the process that coordinates transactions spanning multiple SQL Servers.

3. The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “RemoteServer” was unable to begin a distributed transaction.

Answer: MSDTC needs network access and inbound/outbound access to process Remote Procedure Calls. To check these settings open the Run dialog and open “dcomcnfg”. From console root, expand Component Services, Computers, and then right click on “My Computer” and select “Properties”. Select the MSDTC tab and click the “Security Configuration” button. On this screen make sure “Network DTC Access” is checked. That will allow you to check “Allow Remote Clients”, “Allow Inbound”, “Allow Outbound”, “Enable TIP Transactions”, and “Enable XA Transactions”. Select “No Authentication Required” on the radio buttons. Make sure that “NT AuthorityNetwork Service” is the DTC Logon Account. Hit OK on everything on the way back out. This will tell you that it needs to restart everything dependent on DTC. Turns out, this isn’t much.

4. The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “RemoteServer” was unable to begin a distributed transaction.

Answer: If this happens after you’ve done 1 through 3, you probably just need to stop and start the Distributed Transaction Coordinator service on each server. That seems to fix the problem. Also, make sure that SET XACT_ABORT ON is in your trigger. This not being there can cause this problem as well.

I believe those are all of the major roadblocks to getting EXECUTE() to work on a remote server. I managed to encounter each one of those.

Full Solution – First Pass:

After hacking through all of those problems, I found another one that took me quite some time to figure out. Below, I’ve listed the first complete version of my trigger.

CREATE TRIGGER SyncFunctions

ON DATABASE

FOR ALTER_FUNCTION, CREATE_FUNCTION, DROP_FUNCTION

AS

SET NOCOUNT ON 
SET XACT_ABORT ON

DECLARE @data XML
SET @data = EVENTDATA()

DECLARE @command VARCHAR(MAX)
SET @command = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(MAX)')

DECLARE @database VARCHAR(MAX)
SET @database = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(MAX)')

SET @command = 'USE [' + @database + ']' + CHAR(13) + REPLACE(@command,N'''',N'''''')

PRINT @command

EXECUTE(@command)
AT [RemoteServer]

Picking up where we left off, I’ve added a SET command to build up the SQL query that we’ll pass through with the EXECUTE() command. Here I’m simply adding in a USE statement to tell the RemoteServer which database I want to run the command on. It may not be obvious at first when working with triggers, but a trigger responding to a command will not include any queries before the event that fired it. If you were to script out an existing function, you might get something like this.

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Brian Bickell
-- Create date: 
-- Description:	Hello World
-- =============================================
CREATE FUNCTION [dbo].[PrintHelloWorld]()
RETURNS varchar(12)
AS
BEGIN
	DECLARE @Result varchar(12)
	SET @Result = 'Hello World!'
	RETURN @Result
END

If you were to catch this SQL query running with your trigger, and then issue it via EXECUTE() to another server, you might be surprised when your function ends up in the master database. Or if you were running an ALTER FUNCTION statement you would receive an error, because the function could not be found. This is because the SQL query caught in the trigger event data will not have the USE [DBA] statement needed. This is the reason we grabbed the database name earlier.

Also, since I’ll need to escape any potential quotes from the command string I’ve included a REPLACE() statement. QUOTENAME is a better option here, except that it has a mindnumbingly low character count for it’s input parameter. It’s a good bet that it won’t work in most cases for whole functions. That being the case we’re left with this ugly REPLACE() statement solution.
I’ve included a print statement in my trigger for debug purposes. This will allow me to see what command I’m sending over to the other server. If this isn’t the same as the command I issued to fire the trigger then something is amiss.

Finally, I’m using an EXECUTE() statement with my command as a parameter and specifying AT [RemoteServer] to issue the command to the other server.

Before I mentioned that this was the first version I thought to be functionaly complete. To test this trigger you can run the following CREATE FUNCTION query.

USE [DBA]
CREATE FUNCTION [dbo].[PrintHelloWorld]()
RETURNS varchar(12)
AS
BEGIN
	DECLARE @Result varchar(12)
	SET @Result = 'Hello World!'
	RETURN @Result
END

If you do, you’ll receive a somewhat straight forward, but strange error message.

‘CREATE FUNCTION’ must be the first statement in a query batch.

Enter the “yo dawg”:

This is where things get weird. I had no idea about this limitation. CREATE FUNCTION and ALTER FUNCTION must be the first statement in a batch. CREATE/ALTER PROCEDURE have the same limitation too, interestingly enough. This leaves us with a serious roadblock. I’ll state it thusly.

We can run a command on a remote server. By default, we’ll run that command on the master database. EXECUTE() AT [SERVER] has no way to pass a default database. Thus, we need to pass a USE [DATABASE] statement. We can’t pass a USE statement before the CREATE/ALTER statement, because of syntax problems.

Troubling indeed.

I pondered this for a long time before I came up with a solution I jokingly call the “yo dawg” solution. The “yo dawg” is an internet meme usually featuring a picture of rapper Xzibit with text generically of the form: yo dawg, I heard you like X, so I put an X in your Y so you can VERB(x) while you VERB(y).

CREATE TRIGGER SyncFunctions
ON DATABASE
FOR ALTER_FUNCTION, CREATE_FUNCTION, DROP_FUNCTION

AS

SET NOCOUNT ON 
SET XACT_ABORT ON

DECLARE @data XML
SET @data = EVENTDATA()

DECLARE @command VARCHAR(MAX)
SET @command = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(MAX)')

DECLARE @database VARCHAR(MAX)
SET @database = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(MAX)')

SET @command = 'USE [' + @database + ']' + CHAR(13) + 'EXECUTE (''' + REPLACE(@command,N'''',N'''''')  + ''')'

EXECUTE(@command)
AT [RemoteServer]

To get around our previous problem I’ve kept the USE [DATABASE] command from the first version, but I’ve added a second EXECUTE() statement inside the first one, to create another batch for our CREATE/ALTER statement so that it can be the first command in the batch. I think Xzibit can properly explain this complex SQL construct.

Once this trigger is created it will keep a database that exists on two or more (add additional EXECUTE() statements for more servers) synchronized on function changes. Of course, the functions need to be the same when setting up the script. An ALTER FUNCTION statement propogated to another database missing the function will raise an error.

I’ll leave error proofing this thing for another exercise.