How do I add a new replicated table to a SQL Server 2005 DB that is in merge replication?

Question

We have merge replication set up over a distributed environment (50 to 1500km between offices) for a SQL Server 2005 database of about 350Gb. We now need to add a couple of new tables that must also be in replication, but without pushing the new snapshot to all the subscribers. Is this possible, and if so, what would be the best way to go about doing this?

Answer 1

Well, there is no way to do that BUT: there is still a solution that needs some planning. When you first create your database before replication, make sure you create a few extra tables such as:

Tbl_01unused
Tbl_02unused
Tbl_03unused
Tbl_04unused
Tbl_05unused

Create the first field of this table as a primary key (ideally of the GUID type, if this is the type of primary key you favour).

Tbl_01unused.id_01unused ,unique Identifier, primary Key
Tbl_02unused.id_02unused ,unique Identifier, primary Key
Tbl_03unused.id_03unused ,unique Identifier, primary Key
Tbl_04unused.id_04unused ,unique Identifier, primary Key
Tbl_05unused.id_05unused ,nvarchar(10), primary Key

You can then launch the replication, including these 5 tables (or more: last time we created 100 extra ones...).

Now: if you want to add a table. Let's say Tbl_Item with the following structure:

id_Item
itemCode
itemDescription
..
id_ItemFamily (foreign key relating to the ItemFamily table) 
...

You will have to:

• Add all non primary key fields to Tbl_01Unused

---

Alter table Tbl_01Unused add 
    itemCode nvarchar(12) Null, 
    itemDescription nvarchar(50) Null,
    ...
    id_itemFamily uniqueIdentifier Null
    ...
Go

• Add constraint

---

Alter table Tbl_01Unused
    add
    constraint Tbl_01Unused_itemFamily foreign key(id_ItemFamily)
        references dbo.tbl_itemFamily (id_itemFamily)
Go

• Add other objects using "Alter table" instruction (indexes, etc)

Then you're done:

Just create a view on your main server called Tbl_Item

---

SELECT id_01unused as id_Item,
    itemCode, 
    itemDescription, 
    ...
    id_ItemFamily, 
    ....
FROM Tbl_01unused

It is then very easy for you to distribute the view through all your subscribers, and your programs will be able to access it as a full "real" table.

This system has a few limitations: no possibility to change primary key, "not null" argument or default values are not accepted when adding a column, renaming/resizing a column is not possible, etc. But the system has been working with us for the last 18 months, and we are keeping on adding new functionnalities (and new tables) in a very transparent way.

Of course, if, for any reason, you should restart the replication process, take the time to rename your tables to their effective name, delete the views ... and prepare some spare tables for your next developments!

EDIT: In all cases you will have to restart your replication process with a new snapshot. 350 Gb of data is not a standard size for a database. I think you should have a look at snapshot compaction and/or think of manually distributing snapshots in the different locations (maybe an opportunity to make a nice travel?). If you are using virtual machines as suscribers, you can also think about bringing the virtual machine in your office, reinitialise snapshot, and send the virtual machine back to its place.

Answer 2

sp_addmergearticle - Adds an article to an existing merge publication. This stored procedure is executed at the Publisher on the publication database.

http://msdn.microsoft.com/en-us/library/ms174329.aspx