12 August 2009
For occasionally connected client replication, we are publishing stored procedures so all the clients have what they need to interact with their local data. So, what happens when you need to modify a procedure? Let's just alter one and see what happens. Ready? ALTER PROCEDURE usp… Now, let's wait and see what happens at the client... okay, nothing yet. Let's check again; still nothing. Okay, we'll wait a few minutes. Now? Still nothing. What's going on? Let's ask BOL.
Me: Hey, BOL, what happened to my stored procedure definition?
BOL: Well, the ability of merge replication to replicate stored procedure definitions (or views or user-defined functions), is merely a convenient mechanism for deploying these components to subscribers.
Me: What, exactly does that mean?
BOL: Subsequent changes to the definitions of these objects are not copied automatically to Subscribers.
Me: Thanks, BOL. You sure know your stuff.
So, there you have it. If you use replication to send procedures to your clients, it only works once. After that, you're on your own. Good thing we learned how to use sp_addScriptExec last time!
When setting up an occasionally connected database to work off-line, I had a great thrill the first time the replication snapshot actually made it to the client with all tables, data, and stored procedures intact. In fact, after days of frustration, I was so happy I went home for the day determined to put the entire replication experience (unpleasant) out of my mind for the evening!
Fast-forward to our iteration review demo the next day: "Hey, let's see if the application works off-line, now that we have replication working." "Okay, here goes…" Of course, we immediately got an error telling us that the user could not open the database specified in the login: our new off-line client was broken.
After spending the rest of the morning sobbing in the corner, I thought I'd look into the problem a little bit. It turns out that the DisconnectedClient_Role database role and associated permissions had not replicated to the client and Domain Users did not have access to the database. Only tables, procedures, views, and functions can be replicated.
My first resource, Google, failed to return anything useful so, I went to my second resort/newsgroup aggregator, groups.google.com. A quick search on microsoft.public.sqlserver.replication turned up the solution; sp_addScriptExec. This is basically a carte blanche post-initialization script deployment tool. I took my role creation script and stored procedure permission script and saved them in a file where both the publisher and subscriber could see and access it. Then, I executed the sp_addScriptExec against the publisher database like this:
, @scriptFile = '\\snaphot_storage_location\DisconnectedClient_role.sql'
, @skipError = 1
It immediately sent the role and permission changes down to all active subscribers and any subscriptions that were created subsequently received the permissions as part of the snapshot -- we now had security. This time, I actually logged into a client and verified that the off-line client could access the local copy of the database. Success!
This script will create a snapshot of the source DB, it will handle multiple
files in the DB. It also supports multiple snapshots of the same source by
Set @SourceDBName to the name of the DB you want a snapshot of.
Set @SSName to what you want to call your snapshot.
You can disable the Execute statement at the end if you just want to get
the TSQL code for later use.
DECLARE @SourceDBName AS SYSNAME,
@SSName AS SYSNAME,
@SQL AS VARCHAR(4000)
@SourceDBName = '$CURSOR$',
@SSName = 'snapshot_name'
@SQL = 'CREATE DATABASE '
+ ' ON '
+ 'AS SNAPSHOT OF '
+ ', ' AS [text()]
(SELECT TOP 100
WHEN RIGHT(mf.physical_name,4) = '.ndf'
THEN '(NAME = '
+ ' ,FILENAME = '''
ELSE '(NAME = '
+ ' ,FILENAME = '''
END AS col
sys.master_files AS mf
INNER JOIN sys.databases AS db
ON mf.database_id = db.database_id
WHERE db.name = @SourceDBName
AND mf.type_desc = 'ROWS'
ORDER BY [file_id]) AS a
ORDER BY col ASC
FOR XML PATH('')
) AS script) b