Friday, June 25. 2010
SQL Server Missing from Synchronization Manager
Symptoms
After (re-)creating a subscription to a pull merge replication publication from SQL Server Express 2005, the subscription fails to appear in Synchronization Manager. After further investigation, the symptom was determined to be restricted to non-Administrators.
Things To Check
- Make sure
sp_addmergepullsubscription_agentwas run with@enabled_for_syncmgr = 'TRUE'. This requirement differs from previous SQL Server versions where this was the default. When this parameter is not set to 'TRUE', the subscription will not appear in Synchronization Manager - Make sure the subscription can be synchronized outside of Synchronization Manager (to confirm that it is a problem when run through replsync.dll - in Synchronization Manager). The easiest way to do this is using replmerg from the command-line.
- Make sure the user has permissions to write to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions. Without write permission, SQL Server will silently fail to appear in Synchronization Manager. Also, by default in many configurations, non-Administrators do not have write access to this key so it must be adjusted manually.
The last item is particularly important and required quite a bit of my time to determine... which resulted in the need for this post. Hopefully one of the above suggestions will help you avoid spending the same amount of time that I did to solve this problem.
Sunday, June 20. 2010
Triggers, NOT FOR REPLICATION Identity, and Merge Replication
Initial Note/Warning
I hope that there is a more elegant, simple, and robust method for dealing with the issues described in this post than the one that I present herein. My method is a rather ugly kludge that relies on undocumented features of SQL Server and is not nearly as parallelizable as I would like. If you are aware of a better method (which I hope exists), please don't hesitate to post it in the comments. Until a better method is posted, I invite you to use and/or learn from the method presented in this post.
Background
Consider an SQL Server 2005 database which is published for merge
replication from a single publisher with multiple subscribers. The database
contains 2 tables which we are considering:
Forms and
InboxForms.
Forms contains some sort of form data and
InboxForms references all forms which are
present in a user's "inbox". Each of the tables contains an
INT IDENTITY primary key column and several other
columns of data that are not relevant to the problem at hand. The publication
is filtered based on the Forms that each user is able to view (determined by
some separate mechanism not described here). When a new row is inserted into
Forms, a trigger is used to route the forms
into the appropriate inbox(es).
The Problem
The routing can not occur (exclusively) at the subscribers, because the
filter for the subscriber will not necessarily include
user information for the recipient of the form and the form can not be placed
into InboxForms if it recipient user does not
exist on the subscriber. So, the trigger must run on the publisher during
synchronization when inserts
are performed by the merge agent (i.e. the trigger must not be marked
NOT FOR REPLICATION). However, in this configuration,
when the merge agent runs, the following error message is produced:
Explicit value must be specified for identity column in table 'InboxForms' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
The problem is that the INT IDENTITY column in
InboxForms was marked NOT FOR
REPLICATION when the publication was created in order to facilitate
automatic identity range management, which is described in the
Replicating
Identity Columns article in the SQL Server Books Online.
NOT FOR REPLICATION behaves very similarly to
IDENTITY_INSERT
(as hinted at in the error message), such that when a row is inserted by the
merge agent, the ideneity seed value is not incremented and the value of the
identity column must be explicitly specified. Note, however, that it is not
the same mechanism as IDENTITY_INSERT, so changing IDENTITY_INSERT in the
trigger will not remove the requirement for explicitly specified identity
values.
The Solution
One method to solve this problem is to disable NOT FOR
REPLICATION, as suggested in
KB908711 (which
specifically addresses this issue). However, using this option will
interfere with automatic identity range management, since the identity values
generated on the subscribers can not be copied to the publisher, and other
steps will need to be taken to manually manage identity values. For me, this
was an unacceptably high price to pay and another solution was required.
A solution which does not interfere with automatic identity range
management is to calculate values for the identity columns and explicitly
specify those values when they are required. In order to accomplish this,
understanding several features of T-SQL is required: In order to determine
when the values are required, the trigger needs to test if it is being run
from the merge replication agent. This can be done by testing for the
'replication_agent'
SESSIONPROPERTY.
In order to determine appropriate values for the identity column, use
IDENT_CURRENT and
IDENT_INCR.
Note that using the maximum value for the identity column is not necessarily
correct because the maximum identity range will not necessarily be allocated
to the publisher.
DBCC
CHECKIDENT can be used to update the identity seed value (which
is not affected by explicitly inserted identity values).
One other complicating factor in our implementation is that there is no way to atomically insert explicit identity values and update the identity seed value. Therefore, locking is required to prevent multiple connections from simultaneously updating the values and causing collisions. (Or collision errors must be caught and retried) In the following implementation, an exclusive table lock is acquired which prevents any inserts from occurring on the table when the trigger is running. This is a serious performance problem as it prevents any other operations on the locked table from completing while the trigger is executing. Keep this in mind when designing the queries that will run while the lock is held.
Now, without further ado, here's the trigger:
ALTER TRIGGER TR_RouteForms
ON dbo.Forms
AFTER INSERT
AS
BEGIN
-- Client isn't expecting routing counts from their insert
SET NOCOUNT ON;
IF SESSIONPROPERTY('replication_agent') <> 0
BEGIN
-- Running from the replication agent
-- Need explicit value for NOT FOR REPLICATION IDENTITY columns
-- Use transaction to limit lock scope
BEGIN TRAN
-- Variables for IDENT_CURRENT and IDENT_INCR, required
-- because DBCC CHECKIDENT syntax won't support nested parens
DECLARE @Ident INT, @Incr INT;
-- RowCnt used to preserve @@ROWCOUNT
DECLARE @RowCnt INT;
-- Must acquire exclusive lock on InboxForms to prevent other
-- inserts (which would invalidate the identity and cause
-- collisions in the identity column).
-- Select into variable to prevent resultset going to client
-- WHERE clause quickly evaluated, returns small (empty) result
DECLARE @Dummy INT;
SELECT @Dummy = InboxFormID
FROM InboxForms WITH (TABLOCK, XLOCK, HOLDLOCK)
WHERE InboxFormID = 0;
-- Perform the form routing (inserts into InboxForms)
SET @Ident = IDENT_CURRENT('InboxForms');
SET @Incr = IDENT_INCR('InboxForms');
INSERT INTO InboxForms (InboxFormID, FormID, ...)
SELECT @Ident + @Incr * ROW_NUMBER() OVER (ORDER BY FormID) AS InboxFormID, FormID, ...
FROM inserted
WHERE ...routing criteria...
SET @RowCnt = @@ROWCOUNT;
IF @RowCnt > 0
BEGIN
-- At least 1 form was routed, update the identity seed value
-- Note: Can't use MAX(InboxFormID) since publisher may not
-- have been allocated the maximum identity range
SET @Ident = @Ident + @Incr * @RowCnt;
DBCC CHECKIDENT (InboxForms, RESEED, @Ident)
WITH NO_INFOMSGS;
END
COMMIT TRAN
END
ELSE
BEGIN
-- NOT running from the replication agent
-- Can insert normally into NOT FOR REPLICATION IDENTITY columns
-- Perform the form routing (inserts into InboxForms)
INSERT INTO InboxForms (InboxFormID, FormID, ...)
SELECT @Ident + @Incr * ROW_NUMBER() OVER (ORDER BY FormID) AS InboxFormID, FormID, ...
FROM inserted
WHERE ...routing criteria...
END
END
Friday, June 18. 2010
LAN Party version 1.1
Thursday, June 10. 2010
Non-strongly typed attributes in ASP.NET
Saturday, June 5. 2010
LAN for Kids: The Aftermath
Well I just realized that it has been nearly two weeks since LAN for Kids and I hadn’t done a follow up. I am just recovering now so I figured this might be a good time. LAN for Kids was insanely fun! I had such a great time putting it together, thank you to everyone who came out. I would also like to thank our sponsors once again. The Tech Ranch for sponsoring the venue, Michael Clark from Danix who let us use a whole bunch of his networking equipment, Scott Lease from Pepsi who donated a ton of pop, Brandon VanCleeve from Pine Cove Consulting who let us use a couple of their switches, Colter Lease from Propaganda Works who donated a sweet John Belushi print, Stacey Alzheimer from Theraputika who donated an hour long massage, Richard Stallman/The Free Software Foundation for signing the comic, and HeadRoom for the use of their headphones. Go buy their stuff now.
Because you can’t expect to put on a large event without running into problems, and because it’s sometimes funny to hear about those problems afterwards, especially if you weren’t responsible for fixing them, I will mention our minor freak outs briefly. First we woke up to snow. Dirty, evil snow. Fortunately logistics problems had ruled out our plan for an outdoor LAN (hahaha) and we all knew how to man up so the snow didn’t slow us down. Take that Mother Nature--oh snap! We also didn’t have the internets. Well, we had wireless internet but none of the wired jacks worked. Apparently one of the internet trucks broke down in the internet tubes. We thought Steam games might be a lost cause because even though we were hosting our own servers we still had to make an initial login to Steam to host the game. Then, out of nowhere, Kevin swooped in like a ninja and bridged his laptop’s wireless connection, plugged it into the router and BAM, internet for all! Kevin also set up some sweet traffic shaping so our network performed like a champion and a boss. Kevin saved the day, many thanks Kevin! Also big thanks to Ian Nicklin who helped with setup and game hosting on his personal super computer and Peter Nix who also helped with hosting and setup, you guys rule!
The gaming rocked! Games from 20 years ago are the best. We played some huge Quake 3 games, a StarCraft 1 game, a little Left4Dead and finished off the night with Team Fortress 2. Some guys brought an Xbox with Street Fighter and an arcade control pad and played it on the projector. It was RAD. I bought WAY too much pizza. On the up side it meant everyone got (was forced) to take some pizza home at the end of the night. The event ended just before midnight. Unfortunately during all the craziness I completely forgot to bring my camera, so no pictures this time, but trust me, the setup was beautiful and epic and beautifully epic.
Raffle Winners:
XKCD comic signed by Richard Stallman – Chris Webster
John Belushi Print – Carson Welch
1 Hour Massage – Erin Snyder
Wii Guitar Hero Controller – Ian Nicklin’s boss
Overall I/we had an amazing time and I can’t wait to run another. In fact I will be trying to put together some additional equipment so we can have a complete LAN kit in house and we could potentially host smaller events once every month or two. I did learn a couple of things and I think future events will be much cheaper ($5 or possibly free) and we will sell the food and drink. We also need to find some cheaper space but I’m pretty sure we can work that out. Thanks again to everyone that donated, helped, came or just generally supported us, we really appreciated it!
Boring logistical stuff:
The tough part in hosting LAN parties is that there are actually some non-trivial costs for each additional person that attends. Each person needs a decent amount of table, a chair, three or so outlets, a port on a preferably gigabit switch, CAT 5 to the switch, long CAT 5 from each table switch to a central switch which needs to be a fairly large gigabit switch. Fortunately each piece of that equation is a one-time cost. Unfortunately renting a large space can be relatively expensive compared to the number of people the space can host times the amount of money we can reasonably charge them to get in, especially if we want some of that money to go to a charity. If I can get the equipment we will be able to host approximately 50-60 people. Fifty or sixty people are all we could reasonably fit into the Homewood Suites ballroom. I went to nearly every big rental space in Bozeman before we decided on Homewood Suites and the general consensus was $300-$400 for 6 hours to a full day worth of the meeting space. The Homewood Suites was really nice and gave us a discount price of $200 for the whole day which made them SUBSTANTIALLY cheaper than the competition. Even still, assuming we didn’t have a donation to cover the room we would need 40 people at $5 each before we broke even on the space. If we were to rent out the SUB Ballroom, which would be ideal, we couldn’t even cover the cost of the room at $5 per person. However, like I said, we may be able to get some smaller spaces for free in order to host intermediate LANs and then we can maybe splurge once a year for a big meeting space and go crazy.
