Hello,
I have a database in SQL Server 2000, on which created publication and it
has one subscriber .
now if I change in data structure , following error occured.
'EMPMAST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
columns to table 'EMPMAST' because it is being published for merge
replication.
Harsha........................
Harsha,
please take a look at sp_repladdcolumn.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||and sp_addmergearticle for merge replication
"Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a database in SQL Server 2000, on which created publication and it
> has one subscriber .
> now if I change in data structure , following error occured.
> 'EMPMAST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
> columns to table 'EMPMAST' because it is being published for merge
> replication.
> Harsha........................
>
>
|||"uykusuz" <yb> wrote in message news:eCssQMmdFHA.720@.TK2MSFTNGP15.phx.gbl...
> and sp_addmergearticle for merge replication
that is for adding articles not columns , sorry
[vbcol=seagreen]
> "Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
> news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
it
>
Showing posts with label subscriber. Show all posts
Showing posts with label subscriber. Show all posts
Monday, March 19, 2012
Friday, February 24, 2012
Central Subscriber with updates to subscriber possible?
I am trying to set up a central subscriber topology (seven small databases
replicating into one large database). The requirement I am having trouble
with is that updates need to be made to the central subscriber and get
replicated back to the publisher DB they belong to.
This is why this topology is needed: The company sells training courses,
both online and through regional sales offices. The seven publisher
databases are located in the regional sales offices, and are updated through
a local application by the staff taking course registrations in their
office. The central subscriber is located in the corporate headquarters, and
drives the website where customers can register online and update their
contact info. Contact info updates and registrations made online need to be
replicated back to one or more of the regional office databases. The rules
for which regional DB to update as basically this:
1. Customer records need to get replicated to the regional database that
manages the geographical area containing the customer's address (can be
determined by State and Country) AND to any other regional databases where
the customer has registered for a course (the courses are held at the
regional offices).
2. Registration records need to get replicated to the regional databases
where the course registered for is held.
My biggest problem is that only merge replication seems to be able to handle
the logic about passing updates back to the regional databases, however
merge replication does not support the central subscriber topology, only
central publisher. I am very wary of trying to create a central publisher
topology out of the current setup (i.e. create a central merged DB, drop the
regional DBs and recreate them) - 90% of business activity happens in the
regional offices and it seems too risky to put the regional DBs at the
effect of the central one.
Is there any solution to this?
I think you will find that merge replication will work well for this. It is
a common deployment setup. Look at dynamic snapshots to redeploy the branch
office data, or separate publications.
Hilary Cotter
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> I am trying to set up a central subscriber topology (seven small databases
> replicating into one large database). The requirement I am having trouble
> with is that updates need to be made to the central subscriber and get
> replicated back to the publisher DB they belong to.
> This is why this topology is needed: The company sells training courses,
> both online and through regional sales offices. The seven publisher
> databases are located in the regional sales offices, and are updated
through
> a local application by the staff taking course registrations in their
> office. The central subscriber is located in the corporate headquarters,
and
> drives the website where customers can register online and update their
> contact info. Contact info updates and registrations made online need to
be
> replicated back to one or more of the regional office databases. The rules
> for which regional DB to update as basically this:
> 1. Customer records need to get replicated to the regional database that
> manages the geographical area containing the customer's address (can be
> determined by State and Country) AND to any other regional databases where
> the customer has registered for a course (the courses are held at the
> regional offices).
> 2. Registration records need to get replicated to the regional databases
> where the course registered for is held.
> My biggest problem is that only merge replication seems to be able to
handle
> the logic about passing updates back to the regional databases, however
> merge replication does not support the central subscriber topology, only
> central publisher. I am very wary of trying to create a central publisher
> topology out of the current setup (i.e. create a central merged DB, drop
the
> regional DBs and recreate them) - 90% of business activity happens in the
> regional offices and it seems too risky to put the regional DBs at the
> effect of the central one.
> Is there any solution to this?
>
|||Thanks for your reply. When you say "Look at dynamic snapshots to redeploy
the branch office data, or separate publications." it sounds like I have to
stop using the regional databases as the publishers, drop them and recreate
them as subscribers to the central database. Is that right and is there any
way to avoid dropping and recreating the regional databases? I am trying to
avoid any impact to the regional databases.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eHh36D4pFHA.248@.TK2MSFTNGP14.phx.gbl...
>I think you will find that merge replication will work well for this. It is
> a common deployment setup. Look at dynamic snapshots to redeploy the
> branch
> office data, or separate publications.
> --
> Hilary Cotter
> 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
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> through
> and
> be
> handle
> the
>
replicating into one large database). The requirement I am having trouble
with is that updates need to be made to the central subscriber and get
replicated back to the publisher DB they belong to.
This is why this topology is needed: The company sells training courses,
both online and through regional sales offices. The seven publisher
databases are located in the regional sales offices, and are updated through
a local application by the staff taking course registrations in their
office. The central subscriber is located in the corporate headquarters, and
drives the website where customers can register online and update their
contact info. Contact info updates and registrations made online need to be
replicated back to one or more of the regional office databases. The rules
for which regional DB to update as basically this:
1. Customer records need to get replicated to the regional database that
manages the geographical area containing the customer's address (can be
determined by State and Country) AND to any other regional databases where
the customer has registered for a course (the courses are held at the
regional offices).
2. Registration records need to get replicated to the regional databases
where the course registered for is held.
My biggest problem is that only merge replication seems to be able to handle
the logic about passing updates back to the regional databases, however
merge replication does not support the central subscriber topology, only
central publisher. I am very wary of trying to create a central publisher
topology out of the current setup (i.e. create a central merged DB, drop the
regional DBs and recreate them) - 90% of business activity happens in the
regional offices and it seems too risky to put the regional DBs at the
effect of the central one.
Is there any solution to this?
I think you will find that merge replication will work well for this. It is
a common deployment setup. Look at dynamic snapshots to redeploy the branch
office data, or separate publications.
Hilary Cotter
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> I am trying to set up a central subscriber topology (seven small databases
> replicating into one large database). The requirement I am having trouble
> with is that updates need to be made to the central subscriber and get
> replicated back to the publisher DB they belong to.
> This is why this topology is needed: The company sells training courses,
> both online and through regional sales offices. The seven publisher
> databases are located in the regional sales offices, and are updated
through
> a local application by the staff taking course registrations in their
> office. The central subscriber is located in the corporate headquarters,
and
> drives the website where customers can register online and update their
> contact info. Contact info updates and registrations made online need to
be
> replicated back to one or more of the regional office databases. The rules
> for which regional DB to update as basically this:
> 1. Customer records need to get replicated to the regional database that
> manages the geographical area containing the customer's address (can be
> determined by State and Country) AND to any other regional databases where
> the customer has registered for a course (the courses are held at the
> regional offices).
> 2. Registration records need to get replicated to the regional databases
> where the course registered for is held.
> My biggest problem is that only merge replication seems to be able to
handle
> the logic about passing updates back to the regional databases, however
> merge replication does not support the central subscriber topology, only
> central publisher. I am very wary of trying to create a central publisher
> topology out of the current setup (i.e. create a central merged DB, drop
the
> regional DBs and recreate them) - 90% of business activity happens in the
> regional offices and it seems too risky to put the regional DBs at the
> effect of the central one.
> Is there any solution to this?
>
|||Thanks for your reply. When you say "Look at dynamic snapshots to redeploy
the branch office data, or separate publications." it sounds like I have to
stop using the regional databases as the publishers, drop them and recreate
them as subscribers to the central database. Is that right and is there any
way to avoid dropping and recreating the regional databases? I am trying to
avoid any impact to the regional databases.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eHh36D4pFHA.248@.TK2MSFTNGP14.phx.gbl...
>I think you will find that merge replication will work well for this. It is
> a common deployment setup. Look at dynamic snapshots to redeploy the
> branch
> office data, or separate publications.
> --
> Hilary Cotter
> 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
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> through
> and
> be
> handle
> the
>
central subscriber
A central subscriber whose table is updated by multiple publishers and the
transactional push replication breaks on one of the publishers.
Does
a. If you do a new snapshot for that publisher does it automatically delete
the existing data in that table for that publisher assuming you have the
proper constraints in place for a unique column and then transactional
replication procedes as normal after the snapshot?
-or-
b. You have to wipe out the whole table and redo all the snapshots, i.e
reinitialize all the subscriptions.
-or-
c. something else
Any hard data in BOL,white papers, or other references to support either of
the possibilities?
a) But only if you user filters, and set up your name conflicts correctly.
To do this, right click on your publication, select properties, click on the articles tab, click on the browse button (the three dots) and then click on the snapshot tab. In the name conflicts section, pick delete all data that matches the row filter.
For Filters click on your Filter Commands tab, and add a filter condition. You might have to make changes to your schema to add a column which uniquely identifies your publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
transactional push replication breaks on one of the publishers.
Does
a. If you do a new snapshot for that publisher does it automatically delete
the existing data in that table for that publisher assuming you have the
proper constraints in place for a unique column and then transactional
replication procedes as normal after the snapshot?
-or-
b. You have to wipe out the whole table and redo all the snapshots, i.e
reinitialize all the subscriptions.
-or-
c. something else
Any hard data in BOL,white papers, or other references to support either of
the possibilities?
a) But only if you user filters, and set up your name conflicts correctly.
To do this, right click on your publication, select properties, click on the articles tab, click on the browse button (the three dots) and then click on the snapshot tab. In the name conflicts section, pick delete all data that matches the row filter.
For Filters click on your Filter Commands tab, and add a filter condition. You might have to make changes to your schema to add a column which uniquely identifies your publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Subscribe to:
Posts (Atom)