Re: Schema Changes in Transactional Replication

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uN%23h3iz3HHA.600@xxxxxxxxxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: Merge or Trans with QUS best when publishing partitions of database to slow subscribers
    ... With queued replication the single queue does serve all subscribers. ... No real conflict detection and resolution. ... The majority of the DML should originate on the publisher. ...
    (microsoft.public.sqlserver.replication)
  • Re: Behaviour of Merge Replication
    ... So the branch office should be the publisher, ... would you still use a Transactional Replication after knowing this? ... I normally use pull for over 10 subscribers. ... For the merge publications use central publisher in your head office. ...
    (microsoft.public.sqlserver.replication)
  • Re: MS access and SQL Server
    ... MSDE or Access for merge replication - I would select MSDE as it obviously ... Microsoft Jet 4.0 does not support case-sensitive sort orders. ... create publications for Jet 4.0 Subscribers. ... Publisher, create a pull subscription at the Jet 4.0 Subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: Schema Changes in Transactional Replication
    ... publisher is the distributor. ... It is a push replication. ... change the schema on the subscriber so it's the ... table, or if I have to recreate the entire snapshot, or re-init the ...
    (microsoft.public.sqlserver.replication)
  • Re: HELP! full text catalogs disapearing!
    ... I have Transactional Replication running once a week (The Publisher is only ... I rebuild that catalog each time because that only takes about ... When I come back the next day, the Subscribers data is sync'ed, but all FTI ... I dont understand how a transactional replication, ...
    (microsoft.public.sqlserver.fulltext)