""Bill Cheng [MSFT]"" <billchng@online.microsoft.com> wrote in message
news:W$xA1HFXDHA.2080@cpmsftngxa06.phx.gbl...
> Hi Frank,
>
> What do you mean by "some changes to the Primary keys on several
> databases"? Do you mean you will change the value of the "Primary Key
> columns"? What does "several databases" mean?
>
> According to my test, if I have the following topology:
> 1. Publisher, SQL1, DB1, PUB1 - ARTICLE1
> CREATE TABLE [dbo].[ARTICLE1] (
> [c1] [int] NOT NULL ,
> [c2] [int] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[ARTICLE1] WITH NOCHECK ADD
> CONSTRAINT [PK_ARTICLE1] PRIMARY KEY CLUSTERED
> (
> [c1]
> ) ON [PRIMARY]
> GO
> 2. Subscriber, SQL2, DB2
>
> If I change the value on c1 column, the changes will be automatically
> propagated to subscriber without performing any changes on replication.
>
>
> Bill Cheng
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --------------------
> | From: "frankm" <frank@nxspxm.mallardcentral.com>
> | Subject: Rebuild Replication after Primary key mods???
> | Date: Wed, 6 Aug 2003 11:25:33 -0500
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> | Message-ID: <uPrfddDXDHA.2568@tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.replication
> | NNTP-Posting-Host: angrboda.ms.acxiom.com 206.66.66.9
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.replication:42302
> | X-Tomcat-NG: microsoft.public.sqlserver.replication
> |
> | We have:
> | SvrA as Publisher (database: DBX)
> | SvrB as Distributer
> | SvrB as Pull Subscriber (not updateable, database: DBXRPT)
> | This transacational Replication on an 8 minute cycle.
> | There may be some changes to the Primary keys on several databases.
> |
> | My questions is are:
> | Do we need to drop Publisher, Distributer and Subscriber and rebuild to
> make
> | the changes?
> | Is there a better way than the process below?
> | Would scripting be advantageous for all three....Publisher, Distributer
> and
> | Subscriber ?
> |
> | Currently our process is to ............ (according to a vendor
document)
> | Drop all: Publisher, Subscriber, Distributor........................
> | Backup the source db and restore it to subscriber as a different name.
> | Then, configure the distributer, configure the publisher, configure the
> | subscriber, create publication, create pull subscription.
> |
> | Frankm
> | You know you are in trouble when the
> | learning curve turns into a death spiral.
> |
> |
> |
> |
> |
> |
>

""Bill Cheng [MSFT]"" <billchng@online.microsoft.com> wrote in message
news:K78QB0QXDHA.2180@cpmsftngxa06.phx.gbl...
> Hi Frank,
>
> I understand that you want to change the Primary key schema of the tables
> in the publication. If so, I am afraid that we need to drop subscription,
> drop publication, change schema of the tables, ecreate publication and
> subscription. It is not good to change the Primary key column.
>
> If the added or deleted columns are not Primary key, we can use the
> following method to add or delete columns.
>
> How to apply schema changes on publication databases (Enterprise Manager)
> To add columns to an article
>
> In SQL Server Enterprise Manager, under Replication, expand Publications
> and then right-click the publication where you want to modify a schema.
>
>
> Click Properties, click Filter Columns, and then click Add Column.
>
>
> In the Add Column to Replicated Table dialog box, enter the name of the
> column and the SQL syntax that defines the column. In the SQL for the
> column definition, you must either specify a default value or allow NULL
> values.
>
>
> For information about the syntax required to define the column, see the
> Transact-SQL ALTER TABLE statement.
>
>
> In the Add Column to Replicated Table dialog box, select the publications
> to which you want to add the column.
> To drop columns from an article
>
> In SQL Server Enterprise Manager, expand Replication, expand Publications,
> and then right-click the publication where you want to modify a schema.
>
>
> Click Properties, click Filter Columns, select a table in the Tables in
> publication list, select a column in the Columns in selected table list,
> and then click Drop Column.
>
>
> If the column is constrained, you will be prompted; columns with primary
> key or unique constraints, and uniqueidentifier columns cannot be dropped.
> If you attempt to drop one of those types of columns, an error message is
> displayed. For other constraints, a warning message is displayed; click OK
> to drop the column.
>
>
> Bill Cheng
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --------------------
> | From: "frankm" <frank@nxspxm.mallardcentral.com>
> | References: <uPrfddDXDHA.2568@tk2msftngp13.phx.gbl>
> <W$xA1HFXDHA.2080@cpmsftngxa06.phx.gbl>
> | Subject: Re: Rebuild Replication after Primary key mods???
> | Date: Wed, 6 Aug 2003 15:37:37 -0500
> | Lines: 94
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> | Message-ID: <ek6YUqFXDHA.1480@tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.replication
> | NNTP-Posting-Host: angrboda.ms.acxiom.com 206.66.66.9
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.replication:42317
> | X-Tomcat-NG: microsoft.public.sqlserver.replication
> |
> | Sorry, I did not word that correctly...
> | It's not several databases, its several tables.
> | The changes are to the Primary Key schema, not data.
> | Right now that is all I know about the "upgrades".
> |
> | I guess the question is: will a change to a primary key (adding or
> dropping
> | a column(s)) necessitate rebuilding replication. I doubt very much I
will
> | get a list of the changes, so I won't know what has been changed.
> |
> |
> | ""Bill Cheng [MSFT]"" <billchng@online.microsoft.com> wrote in message
> | news:W$xA1HFXDHA.2080@cpmsftngxa06.phx.gbl...
> | > Hi Frank,
> | >
> | > What do you mean by "some changes to the Primary keys on several
> | > databases"? Do you mean you will change the value of the "Primary Key
> | > columns"? What does "several databases" mean?
> | >
> | > According to my test, if I have the following topology:
> | > 1. Publisher, SQL1, DB1, PUB1 - ARTICLE1
> | > CREATE TABLE [dbo].[ARTICLE1] (
> | > [c1] [int] NOT NULL ,
> | > [c2] [int] NULL
> | > ) ON [PRIMARY]
> | > GO
> | >
> | > ALTER TABLE [dbo].[ARTICLE1] WITH NOCHECK ADD
> | > CONSTRAINT [PK_ARTICLE1] PRIMARY KEY CLUSTERED
> | > (
> | > [c1]
> | > ) ON [PRIMARY]
> | > GO
> | > 2. Subscriber, SQL2, DB2
> | >
> | > If I change the value on c1 column, the changes will be automatically
> | > propagated to subscriber without performing any changes on
replication.
> | >
> | >
> | > Bill Cheng
> | > Microsoft Online Partner Support
> | >
> | > Get Secure! - www.microsoft.com/security
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > --------------------
> | > | From: "frankm" <frank@nxspxm.mallardcentral.com>
> | > | Subject: Rebuild Replication after Primary key mods???
> | > | Date: Wed, 6 Aug 2003 11:25:33 -0500
> | > | Lines: 28
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> | > | Message-ID: <uPrfddDXDHA.2568@tk2msftngp13.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.replication
> | > | NNTP-Posting-Host: angrboda.ms.acxiom.com 206.66.66.9
> | > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | > | Xref: cpmsftngxa06.phx.gbl
> microsoft.public.sqlserver.replication:42302
> | > | X-Tomcat-NG: microsoft.public.sqlserver.replication
> | > |
> | > | We have:
> | > | SvrA as Publisher (database: DBX)
> | > | SvrB as Distributer
> | > | SvrB as Pull Subscriber (not updateable, database: DBXRPT)
> | > | This transacational Replication on an 8 minute cycle.
> | > | There may be some changes to the Primary keys on several databases.
> | > |
> | > | My questions is are:
> | > | Do we need to drop Publisher, Distributer and Subscriber and rebuild
> to
> | > make
> | > | the changes?
> | > | Is there a better way than the process below?
> | > | Would scripting be advantageous for all three....Publisher,
> Distributer
> | > and
> | > | Subscriber ?
> | > |
> | > | Currently our process is to ............ (according to a vendor
> | document)
> | > | Drop all: Publisher, Subscriber, Distributor........................
> | > | Backup the source db and restore it to subscriber as a different
name.
> | > | Then, configure the distributer, configure the publisher, configure
> the
> | > | subscriber, create publication, create pull subscription.
> | > |
> | > | Frankm
> | > | You know you are in trouble when the
> | > | learning curve turns into a death spiral.
> | > |