|
|
You're in the
sql server replication
group:Schema Changes in Transactional Replication
sql server replication:
and subscribers and are using the replicate_ddl option (by default this is set to true), most schema changes will be replicated. PK changes will not be and some other changes (i.e. adding identity properties, etc). If you do use the replicate_ddl option typically only the tables you have modified will be resnapshotted, this might be a better option for you than the way you are doing it. -- relevantNoise - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Freddy" <frosenbergNOSpam@globaltouchtelecom.com> wrote in message 1) Yes, and it will not invalidate the snapshot unless you are using one of news:uN%23h3iz3HHA.600@TK2MSFTNGP05.phx.gbl... > Hi, > > We're doing transactional replication. about 150 tables are being > replicated. > > The people on our development team do frequent product releases, and as > part of this the schema of some replicated tables almost always changes. > Their procedure is: > > 1. remove relevant tables from replication, using the SSMS UI > 2. make the schema changes on the publisher -- either using the SSMU UU, > or Red Gate's Sql Compare. often the changes are significant, including > changing the names of primary keys. > 3. release the software > 4. put the tables back into replication using the SSMS UI. > 5. I then reinit the subscription using the SSMS UI, and get only the > option to use a New Snapshot and I normally select Mark for > Reinitialization, to force the new snapshot to be created immediately. (is > this correct?) > > my questions are: > 1. after step 1 above, will replication continue normally? does it > invalidate the snapshot? and if so, does this mean replication does not > continue normally, or does this just mean that the current snapshot will > be used, until a new one can be recreated? the updateable subscribers options. [quoted text, click to view] > This is a difficult to determine as the default snapshot option in SQL 2005 > 2. is there a better way to do this so that the snapshot would not need > complete rebuilding? See the note above. > > 3. how to determine how long it takes for a snapshot to be rebuilt? I > have looked at the various monitors but can't seem to find a direct answer > to this. is concurrent snapshot option which means brief locks are held on the tables as the snapshot it generated and then after the snapshot is applied on the publisher the log is consulted to determine what has changed since the watermark when the snapshot was generated and delta statements are built to make the publisher and subscriber consitent. If the publisher is under load this can be a significant amount of time to generate this delta. [quoted text, click to view] > > thanks for any help on this! > > Fred > > >
Hi,
We're doing transactional replication. about 150 tables are being replicated. The people on our development team do frequent product releases, and as part of this the schema of some replicated tables almost always changes. Their procedure is: 1. remove relevant tables from replication, using the SSMS UI 2. make the schema changes on the publisher -- either using the SSMU UU, or Red Gate's Sql Compare. often the changes are significant, including changing the names of primary keys. 3. release the software 4. put the tables back into replication using the SSMS UI. 5. I then reinit the subscription using the SSMS UI, and get only the option to use a New Snapshot and I normally select Mark for Reinitialization, to force the new snapshot to be created immediately. (is this correct?) my questions are: 1. after step 1 above, will replication continue normally? does it invalidate the snapshot? and if so, does this mean replication does not continue normally, or does this just mean that the current snapshot will be used, until a new one can be recreated? 2. is there a better way to do this so that the snapshot would not need complete rebuilding? 3. how to determine how long it takes for a snapshot to be rebuilt? I have looked at the various monitors but can't seem to find a direct answer to this. thanks for any help on this! Fred
Hi Hilary,
Thanks so much for your answers. re: If you do use the replicate_ddl option typically only the tables you have modified will be resnapshotted, this might be a better option for you than the way you are doing it. This sounds good, but the better way you are indicating is not yet clear to me.. we are indeed using SS 2005 on both publisher and subscriber, and the publisher is the distributor. It is a push replication. And, replicate schema changes is set to true. So, what would I do differently from steps 1 thru 5 below? Now, regarding PK changes --as you've indicated,they don't get replicated. I have a table whose primary key column name was changed, and the old name is now a computed column (so the code doesn't break), and I'm getting the error 'The column "logpk" cannot be modified because it is either a computed column or is the result of a UNION operator.' Unless you (or someone else can suggest a better way), I was thinking to pull the table out of replication using the UI, change the schema on the subscriber so it's the same as the publisher, and then add that article back into the publication article list. I'm not sure if replication will now start to work for this table, or if I have to recreate the entire snapshot, or re-init the subscriber? or, as you've hinted above, doing something that forces just the changed article to be resnapshotted. (I've almost got these concepts straight--perhaps a bit confused here.) It occurs to me that using some of the system stored procedures might be an alternative here. Thanks for any clarification, and I hope this discussion helps others. Fred [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:Obcxgtz3HHA.5740@TK2MSFTNGP04.phx.gbl... > Answers inline. Not that if you are using SQL 2005 for both your publisher > and subscribers and are using the replicate_ddl option (by default this is > set to true), most schema changes will be replicated. PK changes will not > be and some other changes (i.e. adding identity properties, etc). If you > do use the replicate_ddl option typically only the tables you have > modified will be resnapshotted, this might be a better option for you than > the way you are doing it. > > -- > relevantNoise - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Freddy" <frosenbergNOSpam@globaltouchtelecom.com> wrote in message > news:uN%23h3iz3HHA.600@TK2MSFTNGP05.phx.gbl... >> Hi, >> >> We're doing transactional replication. about 150 tables are being >> replicated. >> >> The people on our development team do frequent product releases, and as >> part of this the schema of some replicated tables almost always changes. >> Their procedure is: >> >> 1. remove relevant tables from replication, using the SSMS UI >> 2. make the schema changes on the publisher -- either using the SSMU UU, >> or Red Gate's Sql Compare. often the changes are significant, including >> changing the names of primary keys. >> 3. release the software >> 4. put the tables back into replication using the SSMS UI. >> 5. I then reinit the subscription using the SSMS UI, and get only the >> option to use a New Snapshot and I normally select Mark for >> Reinitialization, to force the new snapshot to be created immediately. >> (is this correct?) >> >> my questions are: >> 1. after step 1 above, will replication continue normally? does it >> invalidate the snapshot? and if so, does this mean replication does not >> continue normally, or does this just mean that the current snapshot will >> be used, until a new one can be recreated? > 1) Yes, and it will not invalidate the snapshot unless you are using one > of the updateable subscribers options. > >> >> 2. is there a better way to do this so that the snapshot would not need >> complete rebuilding? > See the note above. >> >> 3. how to determine how long it takes for a snapshot to be rebuilt? I >> have looked at the various monitors but can't seem to find a direct >> answer to this. > This is a difficult to determine as the default snapshot option in SQL > 2005 is concurrent snapshot option which means brief locks are held on the > tables as the snapshot it generated and then after the snapshot is applied > on the publisher the log is consulted to determine what has changed since > the watermark when the snapshot was generated and delta statements are > built to make the publisher and subscriber consitent. If the publisher is > under load this can be a significant amount of time to generate this > delta. >> >> thanks for any help on this! >> >> Fred >> >> >> > >
Rre: If you do use the replicate_ddl option typically only the tables you
have modified will be resnapshotted, this might be a better option for you than the way you are doing it. --> Since I have not changed the default, my procedure below should only resnapshot changed tables, right? can someone help me understand Hilary's suggestion for a better option? Thanks, Fred [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:Obcxgtz3HHA.5740@TK2MSFTNGP04.phx.gbl... > Answers inline. Not that if you are using SQL 2005 for both your publisher > and subscribers and are using the replicate_ddl option (by default this is > set to true), most schema changes will be replicated. PK changes will not > be and some other changes (i.e. adding identity properties, etc). If you > do use the replicate_ddl option typically only the tables you have > modified will be resnapshotted, this might be a better option for you than > the way you are doing it. > > -- > relevantNoise - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Freddy" <frosenbergNOSpam@globaltouchtelecom.com> wrote in message > news:uN%23h3iz3HHA.600@TK2MSFTNGP05.phx.gbl... >> Hi, >> >> We're doing transactional replication. about 150 tables are being >> replicated. >> >> The people on our development team do frequent product releases, and as >> part of this the schema of some replicated tables almost always changes. >> Their procedure is: >> >> 1. remove relevant tables from replication, using the SSMS UI >> 2. make the schema changes on the publisher -- either using the SSMU UU, >> or Red Gate's Sql Compare. often the changes are significant, including >> changing the names of primary keys. >> 3. release the software >> 4. put the tables back into replication using the SSMS UI. >> 5. I then reinit the subscription using the SSMS UI, and get only the >> option to use a New Snapshot and I normally select Mark for >> Reinitialization, to force the new snapshot to be created immediately. >> (is this correct?) >> >> my questions are: >> 1. after step 1 above, will replication continue normally? does it >> invalidate the snapshot? and if so, does this mean replication does not >> continue normally, or does this just mean that the current snapshot will >> be used, until a new one can be recreated? > 1) Yes, and it will not invalidate the snapshot unless you are using one > of the updateable subscribers options. > >> >> 2. is there a better way to do this so that the snapshot would not need >> complete rebuilding? > See the note above. >> >> 3. how to determine how long it takes for a snapshot to be rebuilt? I >> have looked at the various monitors but can't seem to find a direct >> answer to this. > This is a difficult to determine as the default snapshot option in SQL > 2005 is concurrent snapshot option which means brief locks are held on the > tables as the snapshot it generated and then after the snapshot is applied > on the publisher the log is consulted to determine what has changed since > the watermark when the snapshot was generated and delta statements are > built to make the publisher and subscriber consitent. If the publisher is > under load this can be a significant amount of time to generate this > delta. >> >> thanks for any help on this! >> >> Fred >> >> >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |