Add a table to a merge replicated database WITHOUT forcing a snapshot

Hello,
I've tried to catch up on all the posts in this area, and I can't really see
where this has been asked. If I have repeated a previous question, I do
apologize.
We are trying to add new tables to an existing merge replication by running
a create table statement on a publication database and then using
sp_addmergearticle with the @.creation_script parameter pointing to the sql
file to run on the subscriber as we are setting the identity fields
manually. When the procedure runs, it detects that the change requires a new
snapshot. We cannot afford the time it takes to create an entirely new
snapshot (could take days) so we are looking for the ways to speed up the
process.
We also tried to create tables directly by entering information into the
system tables but we have had trouble with generating insert, update and
delete triggers to replicate data.
We would like to know whether
1. There is a way to invalidate a snapshot for new tables only?
2. There is another way of creating new tables on a replicated
database.
Any help you could provide would be greatly appreciated! Thanks for your
assistance.
Sincerely,
Marshall
The standard ways to achieve this are:
(a) to create a new publication which contains the new table
(b) or to 'initialize' the table (by creating a complete snapshot)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall
|||Creation of new publication with one empty table in it should take much less than 30 minutes... If it's not a problem to have multiple publications, then it is the way to go.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"WideOpenWest" <MBCDev> wrote in message news:e6GdnTskHapNZPreRVn-jg@.wideopenwest.com...
Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall
|||I never thought of doing something like that; it would probably work, but it
is standard procedure for us to add tables when we do software releases.
After some time, we're going to have publication after publication, which is
ugly and probably a significant performance threat (each publication will
need its own agents.)
Is there some way we could use the information generated in the snapshot of
that new publication to add the tables to the original publication? Perhaps
this is not so orthidox, but we really need to find a solution to this
issue.
Another idea that has been suggested is to add several hundred generic,
empty tables to the new publication. Then, every time we need a new table
in a release we add the columns to one of those generic tables and begin
using it. The problem here is that we'd have several hundred generically
named tables! Is it possible to rename a table across a merge replication?
This is ugly also.
Help!
Thanks,
Marshall
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:%235KQnK63FHA.2424@.TK2MSFTNGP10.phx.gbl...
Creation of new publication with one empty table in it should take much less
than 30 minutes... If it's not a problem to have multiple publications, then
it is the way to go.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"WideOpenWest" <MBCDev> wrote in message
news:e6GdnTskHapNZPreRVn-jg@.wideopenwest.com...
Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall