Wednesday, March 7, 2012

Including New Tables in an Existing Publication

Hi.
I work for a company with 40+ remote workstations that synchronize their
databases wirelessly to receive new jobs, as well as submit field tickets
created on their laptops. This time of the year is our busiest period,
however we have some new tables in the database that we would like to
include in the replication scheme. It is my understanding that you can do
this, however you have to ensure that all of your subscriptions have
synchronized and that no data is modified until the new tables are included
in the publication, the dynamic snapshot jobs are re-run, and the
subscriptions have been re-created. Is this correct? If it is, herein lies
my problem. Because our company is in our busy period, the chances of this
happening are somewhere in between none and impossible. I was wondering if
any of you have encountered a situation like this before, and what you did
to "make the job happen". Any suggestions are MUCH appreciated, and if you
require any more information, please do not hesitate to let me know.
Best Regards,
Brad
PS - We are using SQL2000, with Merge Replication. Sorry I didn't include
this in the first post.
Brad
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23QGMl1X3EHA.3840@.tk2msftngp13.phx.gbl...
> Hi.
> I work for a company with 40+ remote workstations that synchronize their
> databases wirelessly to receive new jobs, as well as submit field tickets
> created on their laptops. This time of the year is our busiest period,
> however we have some new tables in the database that we would like to
> include in the replication scheme. It is my understanding that you can do
> this, however you have to ensure that all of your subscriptions have
> synchronized and that no data is modified until the new tables are
> included in the publication, the dynamic snapshot jobs are re-run, and the
> subscriptions have been re-created. Is this correct? If it is, herein
> lies my problem. Because our company is in our busy period, the chances
> of this happening are somewhere in between none and impossible. I was
> wondering if any of you have encountered a situation like this before, and
> what you did to "make the job happen". Any suggestions are MUCH
> appreciated, and if you require any more information, please do not
> hesitate to let me know.
> Best Regards,
> Brad
>
|||I think you would be best to create a new publication to distribute these
articles.
Either that or do something like this:
sp_addmergearticle 'Publication', 'NewTable', 'NewTable',
@.force_invalidate_snapshot =1 which will generate and distribute an entire
new snapshot of all the articles.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:O48xI8Z3EHA.2624@.TK2MSFTNGP11.phx.gbl...
> PS - We are using SQL2000, with Merge Replication. Sorry I didn't include
> this in the first post.
> Brad
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:%23QGMl1X3EHA.3840@.tk2msftngp13.phx.gbl...
>
|||So will this affect my users in any way? The main thing I need to know is
that will my users still be able to run their app, create records, save
them, sync, receive the new tables, and the data they created before last
sync - will it still be there, and will it make it back to the publisher?
I've thought about creating a second publication, but for ease of
administration, I'd like to keep everything in one publication if at all
possible.
Thanks alot for your help Hilary!
Best Regards,
Brad
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eOb3zNf3EHA.2156@.TK2MSFTNGP10.phx.gbl...
>I think you would be best to create a new publication to distribute these
>articles.
> Either that or do something like this:
> sp_addmergearticle 'Publication', 'NewTable', 'NewTable',
> @.force_invalidate_snapshot =1 which will generate and distribute an entire
> new snapshot of all the articles.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:O48xI8Z3EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||A safe way (I've done this) would be to add a second publication on your
server containing the new tables...
On the subscribers add a second subscription... This will leave your
production stuff in place & gives you time to roll out the new tables--
Only downside now there are 80 subs in the field logging in, + the
additional sub on the client (logs in twice... so its slower)
Once you have some down time you could then combine the 2 & redo all the
clients... (you can build scripts for this..)
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:O$$gO8f3EHA.524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> So will this affect my users in any way? The main thing I need to know is
> that will my users still be able to run their app, create records, save
> them, sync, receive the new tables, and the data they created before last
> sync - will it still be there, and will it make it back to the publisher?
> I've thought about creating a second publication, but for ease of
> administration, I'd like to keep everything in one publication if at all
> possible.
> Thanks alot for your help Hilary!
> Best Regards,
> Brad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eOb3zNf3EHA.2156@.TK2MSFTNGP10.phx.gbl...
entire[vbcol=seagreen]
their[vbcol=seagreen]
busiest[vbcol=seagreen]
that[vbcol=seagreen]
between
>
|||Okay, first let me thank you for helping me out. I realize that there is an
option to add a second publication, but I'd really like to avoid this if at
all possible. So, is it technically possible to add articles to merge
publications without reinitializing the subscriptions/affecting any data
entered by the subscriber which has not yet replicated to the publisher?
Basically, the reason why I'm trying to avoid the 2nd pub is because our
busy period is our *busiest* - we are still slammed in the spring, summer
and fall and we never get an opportunity to see all of the subscribers at
once - so I would never have enough downtime to redo the subscribers in a
timely manner which would allow for uninterrupted flow of data (which senior
management in my company demands).
If all of our merge subscriptions are dynamic snapshots (which they are),
and I want to add articles to the pub, will this require reinitializing each
subscriber?
Thanks to all of you for your responses and answers.
Best Regards,
Brad
"S c o t t K r a m e r" <sckramer2000@.hotmail.com> wrote in message
news:vc0ud.227$bu1.134@.fe61.usenetserver.com...
>A safe way (I've done this) would be to add a second publication on your
> server containing the new tables...
> On the subscribers add a second subscription... This will leave your
> production stuff in place & gives you time to roll out the new tables--
> Only downside now there are 80 subs in the field logging in, + the
> additional sub on the client (logs in twice... so its slower)
> Once you have some down time you could then combine the 2 & redo all the
> clients... (you can build scripts for this..)
>
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:O$$gO8f3EHA.524@.TK2MSFTNGP09.phx.gbl...
> entire
> their
> busiest
> that
> between
>
>

No comments:

Post a Comment