A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Change SQL Server 2005 Replication Agent Profiles

 
 
 
I was asked to adjust the latency of SQL Server Replication so that different latency lengths could be tested. 
 
So , I changed the log reader agent polling interval and here's how I did it. 
 
 
1.  Right Click on Replication in the SQL Server tree.
 
 
2. Left Click on Distributor Properties
 
 
 
3. Click on Profile Defaults in the bottom right.
 
 
 
4. Click on Log Reader Agents in the left nav. You should see the agent profiles appear in the right profiles pane.
 Click New in the bottom left. This should bring up a dialog to choose a template as the bases for your new agent profile.
 
 
 
 
5.  Edit the PollingInterval Parameter in the value field to the desired amount in seconds.
Give your new profile a name. 
 
 
 
 
6. Select your newly created profile as the agent profile. 
 
 
 
 
Hit Ok Ok, 
 
And that's it. 
 
Have fun.
 
-Don
 
 
 
 

Cannot alter table for replicated article after dropping article from all publications. (SQL2K5)

Issue:

I attempted to alter a column in a replicated table. When attempting to modify the column I get the following error: Cannot alter column '<column-name>' because it is 'REPLICATED'.

 
 

This article had been previously removed from Two publications. So the above error should not have occurred...   We have a consistency issue. 

 
 

Diagnostic  1: Check syscolumns.colstat.

 

Select * from syscolumns where id = object_id(<table name>)

 

The value of the colstat field for the particular column was 8192. I've run into this before on SQL 2k. The fix at that time was to update this field to 0 for the offending column. If you attempt that now, you get the following error :

Msg 259, Level 16, State 1, Line 2

Ad hoc updates to system catalogs are not allowed.

 

Either way... the 8192 is going to be an issue. I don't have the luxury of putting my production server into single user mode to modify system catalogs... so my fix needs to be real time and online.

 

Diagnostic 2: check sys.columns

 

Select is_non_sql_subscribed ,* from sys.columns where object_id = object_id(<table name>)

 
 

If the is_non_sql_subscribed field = 1, you have an issue. This can happen on servers with any type of replication including SQL Server only.  I'm currently running SQL 2k5 replication in my environment. No third party replication. Either way , the value returned for me was "1" which identifies an inconsistency issue. 

 
When the snapshot agent creates a new snapshot for your publication it sets this field to 1, once the snapshot is finished the agent resets the value back to 0. Our solution will use this snapshot agent behavior.

 

 
Workaround:

Create a new publication with your questionable article. Snapshot it. Check the data. SQL will attempt to fix your inconsistencies and properly flag the is_non_sql_subscribed field. Drop the article from your new publication, make your table edits and add the article back to your original publications.