Adding a new table to Replication in MSSQL Server without reinitializing the Snapshot

There are some prerequisites for Tables to be a part of replication:
  • It should have a PRIMARY KEY
  • It should have IDENTITY NOT FOR REPLICATION NOT NULL:
If prerequisites are not met for Primary key you will see the following error:
Msg 14088, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 1539 [Batch Start Line 0]
The table '[dbo].[T_YourTableNameHere]' must have a primary key to be published using the transaction-based method.
Though NOT FOR REPLICATION FOR IDENTITY will not throw any error during the time you are adding the table to replication but it will start throwing errors once you will start inserting rows or data into the table and I am guessing you must have heard this earlier "prevention is better than cure".

Alright once prerequisites has been taken care of  this tasks can be accomplished using the following steps :
  1. Create table in Publisher Node.
  2. Create table in all Subscriber Nodes.
  3. Use sp_addarticle to add and publish the new table in your publication (Code given below)
  4. Use sp_addsubscription to add subscription for the new table to your Subscribers (Code given below)
The sample code for creating an example new table which can be added later to replication should look like the one shown below:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_YourTableNameHere')

BEGIN
    CREATE TABLE T_YourTableNameHere(
             Id INT PRIMARY KEY IDENTITY NOT FOR REPLICATION NOT NULL,
             CompId INT NOT NULL,
             AppId INT NOT NULL,
             BlockStatus BIT NOT NULL DEFAULT 0,
             CreateDate datetime NOT NULL,
             UpdateDate datetime NOT NULL
           )
END

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME = 'IX_T_T_YourTableNameHere_AppId')

BEGIN
    CREATE INDEX IX_T_T_YourTableNameHere_AppId on T_YourTableNameHere (AppId ASC)
END

Execute below codes on sql server master  in given sequence to add it to Replication after changing the publication, articles ,subscribers and database names accordingly:
EXEC sp_addarticle
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@source_object = 'T_YourTableNameHere',
@type = N'logbased',
@pre_creation_cmd = N'none',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@status = 24

/*Add subscription to the published article for Respective Subscriber Instance*/
EXEC sp_addsubscription
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@subscriber = 'SubscriberInstanceName-MSSQL01',
@destination_db = 'YourDatabaseNameHere',
@subscription_type = N'push',
@reserved='Internal',
@sync_type = N'replication support only'
In Case you have more than 1 Subscriber instance which is normally the case and generally happens in most of the organizations you can do some thing like shown below:
  1. Create the table on each instance (Publisher+Subscriber) 
  2. Add Article to Publication. 
  3. Then add subscription to every Subscriber node. 
Note:The whole code snippet is supposed to be executed on Publisher node of course only after creation of table on each node.
Example:
EXEC sp_addarticle
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@source_object = 'T_YourTableNameHere',
@type = N'logbased',
@pre_creation_cmd = N'none',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@status = 24

/*Add subscription to the published article for Respective Instance*/
EXEC sp_addsubscription
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@subscriber = 'SubscriberInstanceName-MSSQL02',
@destination_db = 'YourDatabaseNameHere',
@subscription_type = N'push',
@reserved='Internal',
@sync_type = N'replication support only'

/*Add subscription to the published article for Respective Instance*/
EXEC sp_addsubscription
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@subscriber = 'SubscriberInstanceName-MSSQL03',
@destination_db = 'YourDatabaseNameHere',
@subscription_type = N'push',
@reserved='Internal',
@sync_type = N'replication support only'

/*Add subscription to the published article for Respective Instance*/
EXEC sp_addsubscription
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@subscriber = 'SubscriberInstanceName-MSSQL04',
@destination_db = 'YourDatabaseNameHere',
@subscription_type = N'push',
@reserved='Internal',
@sync_type = N'replication support only'

/*Add subscription to the published article for Respective Instance*/
EXEC sp_addsubscription
@publication = 'YourPublicationsNameHere',
@article = 'T_YourTableNameHere',
@subscriber = 'SubscriberInstanceName-MSSQL05',
@destination_db = 'YourDatabaseNameHere',
@subscription_type = N'push',
@reserved='Internal',
@sync_type = N'replication support only'

Comments

Popular posts from this blog

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

Adding copy to clipboard button to every google code prettify pre blocks in blogger

Query to Expand and List All logins within a SQL Server Group