Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    1

    Unanswered: Schema change\multiple publishers into single subscription table

    I have scaled out an application so that multiple servers contain identical production databases (various clients use different server databases.)

    I have replication configured so that the same tables from these different databases are published into single subscription tables (to provide data warehouse and reporting across all production databases).
    The publications are set up to replicate ddl.

    Everything works wonderfully except I have just discovered the need to alter the schema of a table that is within the production databases.

    When I apply a script to change the ddl in all the publication databases I am getting errors in replication.
    I understand that I am NOT supposed to change an uderlying subscription table (this should be done through replication of the schema.)
    I suspect that the replication error is caused when the schema changes are replicated from the very first Publication update. What I'm effectively doing is changing the subscription table independently (and prior to) changing the schema of the OTHER (subsequent) publication table schemas.
    I experimented and unsubscribed my target table from all but ONE publication, and then my schema change is fine and replication is happy. But when I have multiple publications feeding the subscription I am not able to propagate the schema change across replicating publications without breaking replication.
    I am sure that other people out there must have similar situations as mine, where multiple publishers update a single subscription. How are you able to update schema successfully?
    Many thanks in advance,
    Normajean
    P.S. I have posted the script that I am running below....

    And here is the error I get in Replication Monitor when I try to run the script:
    The index 'Idx_FacilityStayPayer_facStayID_PayerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074)
    Get help: http://help/5074
    The index 'Idx_FacilityStayPayer_payerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074)
    Get help: http://help/5074
    ALTER TABLE ALTER COLUMN payerID failed because one or more objects access this column. (Source: MSSQLServer, Error number: 4922)


    ------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------
    -- make the facilityStayPayer.payerID column non nullable


    -- drop all of the foreignKeys and indexes that reference the column
    IF EXISTS (select constraint_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    where table_name = 'facilityStayPayer'
    and constraint_name = 'FK_FacilityStayPayer_Payer')
    BEGIN
    ALTER TABLE dbo.FacilityStayPayer
    DROP CONSTRAINT FK_FacilityStayPayer_Payer
    END

    GO
    IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_payerID')
    BEGIN
    DROP INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF )
    END
    GO
    IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_facStayID_PayerID')
    BEGIN
    DROP INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF )
    END
    GO
    IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'UNQ_FacilityStayPayer')
    BEGIN
    ALTER TABLE [dbo].[FacilityStayPayer] DROP CONSTRAINT [UNQ_FacilityStayPayer]
    END
    GO

    IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
    BEGIN
    -- alter the column
    ALTER TABLE FacilityStayPayer
    ALTER COLUMN payerID UNIQUEIDENTIFIER not null
    END
    GO


    IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
    BEGIN

    -- add all of the indexes and foreign keys back
    ALTER TABLE dbo.FacilityStayPayer WITH NOCHECK ADD CONSTRAINT
    FK_FacilityStayPayer_Payer FOREIGN KEY (payerID)
    REFERENCES dbo.Payer(payerID)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    END
    GO

    IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
    BEGIN

    CREATE NONCLUSTERED INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer]
    ([payerID] ASC)
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    END
    GO

    IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
    BEGIN
    CREATE UNIQUE NONCLUSTERED INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer]
    ([facStayID] ASC,
    [payerID] ASC)
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    END
    GO

    IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
    BEGIN
    ALTER TABLE [dbo].[FacilityStayPayer]
    ADD CONSTRAINT [UNQ_FacilityStayPayer] UNIQUE NONCLUSTERED
    ([facStayID] ASC, [payerID] ASC)
    WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    END
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This sounds like a nightmare in search of a victim to me. Have you considered using Merge Replication instead?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •