The following article will provide recently found issues discovered when planning a system separation using Merge Replication using Microsoft SQL 2000 Database Server. These issues (or pitfalls) were discovered first-hand in a real world environment at the company I work for when we were doing a monthly release of our in-house software, while getting ready for a complete System Separation using Merge Replication. The resolutions offered in this article have been tested and are known to work.
The company I work for made a decision that in order to keep up with the continuing growth of the company and our ever expanding customer base, the best way to improve the performance of both our web presence and our in-house applications was to do a complete System Separation by co-locating our database servers off-site, cut down on the use of bandwidth and increase the overall performance of our websites. The decision was made to move all the web data off-site and use Merge Replication in SQL 2000 to keep the in-house data and the web data synchronized, since the in-house applications needed use of the web data as well.
The databases, and the required objects within the database (stored procedures, tables, user-defined functions, triggers, etc.) were marked for replication and all was moving ahead smoothly. When we create our database scripts, whether it be triggers, alter table scripts, or stored procedures, we employ the use of an in-house created version control header to prevent old scripts from being promoted to production. In the header template the script searches the SysObjects System Table for a procedure with that name. If the procedure is found, it is dropped then recreated, incrementing the version of the script, otherwise it simply creates the stored procedure as normal. This is when the first pitfall was recognized. If a stored procedure has been marked for replication using Merge Replication, you cannot simply drop the procedure and re-create it, you must do the search (as normal for us) If the procedure is found then the script alters the procedure, if it isn't found then it is created (a blank procedure with the parameter list) then it is altered to add the "meat" of the procedure. This was a simple enough issue and resolution, causing a minor setback, but nothing we couldn't recover from in a small amount of time.
The next pitfall, discovered by myself during the promotion and build of a monthly release of an in-house application, wasn't quite as simple to fix, and could have caused a huge problem if it had not been discovered prior to the system separation. Had that happened, if this situation had been encountered (we'll get to the "situation", or sequence of events momentarily) while doing one of the monthly builds for anyone of the in-house application after the separation, it would have posed serious problems for my company.
While making updates for my application for the next monthly release, I created and ran an alter table script because I needed to add some additional columns to a table. I created the script and ran it on the dev server, a mirror image of production, or so I thought, and it ran fine, no problems at all, so I continued with my tasks for the monthly release. It wasn't until it was time to promote the current monthly release to the QA Department that the pitfall was discovered. While running one of the Alter Table scripts I created, the following error was displayed:
Version: 01.00 of: PAT dbo.<sup>*</sup>Alter_Table_Script.pat
(1 row(s) affected) Server: Msg 4931, Level 16, State 1,
Line 3 Cannot add columns to table <sup>*</sup>'Table_Name' because it is being
published for merge replication. -
Well as you can imagine when I saw this, I had a feeling this wasn't good news. It was 9 o'clock at night and I knew there was nothing I could do until morning when the DBA arrived at work.
The Next Morning
So 7:00 AM rolls around the next day and I am already at work with the DBA trying to find out what went wrong, and what kind of impact this had on our system separation project. After a little research, he thought the system separation was dead, unless we could upgrade to SQL 2005 and in a hurry, as the separation was mere weeks away. There was some fierce searching going on in the office, Google was being taxed, online SQL 2000 reference sites, in-house SQL Library (which is actually quite extensive), there were 3 of us desperately looking for a resolution to our problem.
The first solution turned out to be not so reliable. When a database is marked for replication, subsequent tables are marked, SQL creates a corresponding table for the marked table, the tables are named
conflict_DBName_TableName, so I started building a query based on that, until we realized that SQL 2000 doesn't clean up after itself, when a table is no longer marked for Merge Replication that table isn't deleted, thus offering the possibility of "false positives" when the query is run. So of course, this solution was scrapped. That's when the this person rushing to find a solution found the end all be all of solutions.
When a database is marked for Merge Replication SQL 2000 creates a new database, aptly named
distribution, inside this database is a table named "
MSArticles". It is in this table that all the names of all objects that have been marked for Merge Replication are stored, along with the object type. Then there is a system stored procedure names
sp_repladdcolumn, but of course this stored procedure can only be run on tables that have been marked for replication, it will fail otherwise. This gave me what I needed, what I had to do was first check and see if the database has been marked for Merge Replication, as if it's not then the search of
distribution.dbo.MSArticles will cause an error, thus failing all together. Taking all this into consideration, I wrote the below script that did everything I needed it to do, tested it, then released it to the other developers on the team.
* Denotes table name and script name changed.
Using the Code
The script I came up with below will have certain information missing, information that is specific to our Version Control Header, and proprietary to the company I work for. I will however show the script part that actually:
- Checks if the database has been marked for Merge Replication
- Checks if the table actually exists (just a double check I like to perform, I don't like surprises)
- Checks to see if the table has been marked for merge replication
- Alters the table accordingly
// Below is the script used to check required elements
// so that an Alter Table script can be created and ran on a SQL 2000 database
// that has been marked for Merge Replication.
IF EXISTS(SELECT * FROM master..sysdatabases WHERE name='distribution')
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Table_Name')
IF EXISTS (SELECT Name, CASE(xtype) WHEN 'U' _
THEN 'User table' ELSE xtype END AS ObjectType FROM sysobjects
WHERE NAME IN (SELECT source_object from distribution.dbo.MSarticles) _
AND Name = 'Table_Name')
Exec sp_repladdcolumn @source_object = _
N'[dbo].[Table_Name]', @column = N'column_name',
@typetext = N'TIMESTAMP NOT NULL', @publication_to_add = N'all'
That is the script we now use for our alter table scripts since many of our database and database objects have been marked for Merge Replication. As for other objects, such as Stored Procedures, the old way our script worked was to simply query
sysobjects to see if the procedure existed, if it did then drop it and recreate it (and with our Version Control Header the version would increment, preventing old versions from being promoted to live, but that's a different article all together). So we as a team came up with this script for our Stored Procedures:
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' _
AND name = 'Stored_Procedure_Name')
PRINT 'Creating Procedure Stored_Procedure_Name'
DECLARE @SQL varchar(8000)
SET @SQL = 'CREATE PROCEDURE Stored_Procedure_Name /* Param List */
PRINT 'Altering Procedure Stored_Procedure_Name'
ALTER PROCEDURE Stored_Procedure_Name
That's it. This is how to overcome the pitfalls discovered while doing a Merge Replication in a SQL 2000 database.
Points of Interest
This scenario was corrected in SQL 2005, and I imagine in subsequent releases of the database program so if you aren't running SQL 2000 then this shouldn't be an issue. If, however, you are running SQL 2000 and are planning a Merge Replication then this information needs to be brought to the attention of your DBA. Chances are he already knows this, but if he doesn't then you will save him, and your company, a ton of headaches.
- 12th July, 2007: Initial post