0

The following procedures sp_MSdel, sp_MSins, sp_MSupd are the outcome of an configured publication/subscription on SQL Server.

These procedures let you do small transformations inside them so the streamed data can be transferred or reach the destination in different schema.

For instance having a publication for a table called "Data" with two columns into a publisher:

CREATE TABLE [dbo].[Data](
    [id] [int] NOT NULL,
    [description] [nchar](10) NULL,
    CONSTRAINT [PK_Dasta] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY]
 )

I can have a little bit different structure for a table called "Data" with three columns into a subscriber:

CREATE TABLE [dbo].[Data](
    [tenant_id] NOT NULL,
    [id] [int] NOT NULL,
    [description] [nchar](10) NULL,
    CONSTRAINT [PK_Dasta] PRIMARY KEY CLUSTERED ([tenant_id] ASC, [id] ASC) ON [PRIMARY]
 )

While doing this I can also modify the replication procedure [dbo].[sp_MSins_dboData] to add logic in this new destination column tenant_id

ALTER procedure [dbo].[sp_MSins_dboData]
    @c1 int,
    @c2 nchar(10)
as
begin  
    insert into [dbo].[Data] (
        [tenant_id],
        [id],
        [description],
    ) values (
        1,
        @c1,
        @c2)

Now in a scenario where there are multiple publishers pointing to the same subscription, is it possible to pass a parameter of origin (so instead of the hardcoded one to make it variable) to make sure of which origin are the streamed data?

I suppose the easiest solution is to create different procedures for each source database like sp_MSins_dboData_1, sp_MSins_dboData_2 etc but I was thinking for something more dynamic.

Any ideas?

2 Answers 2

1

Now in a scenario where there are multiple publishers pointing to the same subscription

I take it to mean you'd be Publishing from multiple source tables to a single Subscriber table. This is not recommended and is error prone.

Instead replicate each Publication to a different table, and even schema for better manageability, and use a view (or some other mechanism) to unify the data. Please see this related DBA.StackExchange answer suggesting the same.

The following procedures sp_MSdel, sp_MSins, sp_MSupd are the outcome of an configured publication/subscription on SQL Server.

These procedures let you do small transformations inside them...

No, please don't do that either. Those are the native procedures that Replication generates for each Article in your Publication. It is also error prone and poor management to change those directly.

Instead, when you add an Article to Replication, under the Article Properties, in the Statement Delivery section, you can specify your own stored procedures for the INSERT, UPDATE, DELETE processes:

Replication Article Properties

Unfortunately I don't think you can specify parameters here (though I haven't tried myself). But that should no longer be relevant if you heed my advice about not replicating multiple Publisher tables to the same Subscriber table.

3
  • I see, but still I do not understand why is not suggested, is not suggested based on maitenance? Because all of this will be handled programmatically by an application... Nov 23, 2022 at 14:57
  • @StavrosKoureas One reason you shouldn't alter the default procedures is because any code you put in them would be lost if that Article was removed and re-added to your Publication, for example. If you put the code in your own procedures, then those procedures will remain even after removing the Article that depends on them from Replication. There's other unpredictable error-prone scenarios I'm sure that could occur was well, especially in future versions of Replication.
    – J.D.
    Nov 23, 2022 at 19:27
  • I would like to learn more, although what i did, is that I took the default procedures, I modified them, I renamed them and and I assign them into the article. In addition, every time I am creating the article I creating with the parameter COPY INSERT, UPDATE and DELETE stored procedures to false, it does not recreate them. Nov 24, 2022 at 7:47
0

I managed to do this for demo purposes, so I am not sure how this will behave for large scale projects like in the other answers are mentioning.

What I did is the following:

  • Created the "Data" table into publisher1 database (two columns)
  • Created the "Data" table into publisher2 database (two columns)
  • Created the "Data" table into subscriber database (three columns)
  • Created two stored-procedures (sp_MSins_dboData_1 & sp_MSins_dboData_2) for the inserts based on the originals to test the inserts.
  • While creating the publications in the article configuration, I configured the following:
    • not to copy anything like schemas, constrains etc in order not to overwrite anything on the destination.
    • set the property 'COPY INSERT, UPDATE and DELETE stored procedures' to 'false' in order not to overwrite your procedures using the default procedures.
    • set the property 'INSERT stored procedure' to your procedure_name 'sp_MSins_dboData_1' in order to use this custom procedure for this specific publication for handling inserts holding your destination logic (in this case the additional column).
    • set the property 'action if name is in use' to 'Keep existing object unchanged' in order not to overwrite your table

One thing I noticed, is that you cannot initiate snapshot while using this configuration as the destination has a different schema but there are options under the Snapshot Page in the publication to apply scripts before and after applying a snapshot.

2
  • There's a couple of concerns & problems with this. The biggest one you said yourself: "you cannot initiate snapshot while using this configuration". It's almost guaranteed that Replication is going to fail at some point during your usage (it's common with Replication which is a bit fragile) where the only solution to fix it will include re-initializing the snapshot. Also in your comment on my answer you stated "I took the default procedures, I modified them, I renamed them". You should make new procedures, not modify the default ones, you might've broken some meta-data in your Publication.
    – J.D.
    Nov 24, 2022 at 13:36
  • Also, "set the property 'action if name is in use' to 'Keep existing object unchanged'" will prevent you from being able to re-initialize your Subscriber (as in re-initializing the snapshot won't actually do anything). These, in addition to the problems mentioned in the linked related answer in my post, are going to be potentially problematic.
    – J.D.
    Nov 24, 2022 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.