| Groups | Blog | Home |
sql server replication : Central subscriber question
This could be for 2000 or 2005...all my experience is with 2000...
Transactional replication. I need to set up a Central Subscriber to be a reporting server, subscribing to 80 different database that are identical in schema (each db is a different facility). I cannot alter the schema of the publishers...3rd party application. The part I need opinions on is how to differentiate which database the data came from as it goes into the subscription db. I have tested pulling the HOSTNAME from sysprocesses in the Update and Insert stored procs, and that works just fine, but these 80 databases will all be on the same host. Any suggestions? -- Kevin Hill 3NF Consulting www.3nf-inc.com/NewsGroups.htm www.expertsrt.com - not your average tech Q&A site
Kevin,
could you add indexed views to the publisher? I know you can't change the schema, but this might not be regarded as a change. It could become a pain if there are a lot of tables but if we are talking about only a handful it should be possible, using a hardcoded hostname on each publisher's indexed views. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
performance with transformable subscriptions is not good. Its not really
scalable. It was intended for replicating to heterogeneous subscribers where you could not modify the data in flight in your stored procedure. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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] "Kevin3NF" <Kevin@DontNeedNoSpam3NF-inc.com> wrote in message news:OBMrJ2FlGHA.1000@TK2MSFTNGP04.phx.gbl... > The data in the publishers is different (except possibly for the keys. I > am assuming INTs here). All of the data will be in one subscriber for > reporting purposes. > > I have to look up "custom sync object." I may know it by a different name. > > Might transformable subscriptions be relevant? Have not tested or used > them in the past > > -- > Kevin Hill > 3NF Consulting > www.3nf-inc.com/NewsGroups.htm > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:e9IxoyElGHA.3304@TK2MSFTNGP03.phx.gbl... >> Its not clear to me whether the data in these subscriber databases will >> be identical or different. If they are to be different you could use a >> custom sync object for each one. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> 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 >> >> >> >> "Kevin3NF" <Kevin@DontNeedNoSpam3NF-inc.com> wrote in message >> news:%23b3P1WBlGHA.3588@TK2MSFTNGP02.phx.gbl... >>> This could be for 2000 or 2005...all my experience is with 2000... >>> >>> Transactional replication. >>> >>> I need to set up a Central Subscriber to be a reporting server, >>> subscribing to 80 different database that are identical in schema (each >>> db is a different facility). I cannot alter the schema of the >>> publishers...3rd party application. >>> >>> The part I need opinions on is how to differentiate which database the >>> data came from as it goes into the subscription db. >>> >>> I have tested pulling the HOSTNAME from sysprocesses in the Update and >>> Insert stored procs, and that works just fine, but these 80 databases >>> will all be on the same host. >>> >>> Any suggestions? >>> >>> -- >>> Kevin Hill >>> 3NF Consulting >>> www.3nf-inc.com/NewsGroups.htm >>> www.expertsrt.com - not your average tech Q&A site >>> >>> >>> >>> >> >> > >
Its not clear to me whether the data in these subscriber databases will be
identical or different. If they are to be different you could use a custom sync object for each one. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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] "Kevin3NF" <Kevin@DontNeedNoSpam3NF-inc.com> wrote in message news:%23b3P1WBlGHA.3588@TK2MSFTNGP02.phx.gbl... > This could be for 2000 or 2005...all my experience is with 2000... > > Transactional replication. > > I need to set up a Central Subscriber to be a reporting server, > subscribing to 80 different database that are identical in schema (each db > is a different facility). I cannot alter the schema of the > publishers...3rd party application. > > The part I need opinions on is how to differentiate which database the > data came from as it goes into the subscription db. > > I have tested pulling the HOSTNAME from sysprocesses in the Update and > Insert stored procs, and that works just fine, but these 80 databases will > all be on the same host. > > Any suggestions? > > -- > Kevin Hill > 3NF Consulting > www.3nf-inc.com/NewsGroups.htm > www.expertsrt.com - not your average tech Q&A site > > > >
Sorry...that would be regarded as a change. And while I have not sen the
schema yet (bidding the project), the nature of the application implies a large number of tables... -- Kevin Hill 3NF Consulting www.3nf-inc.com/NewsGroups.htm www.expertsrt.com - not your average tech Q&A site [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:%23cUMdSElGHA.5072@TK2MSFTNGP04.phx.gbl... > Kevin, > could you add indexed views to the publisher? I know you can't change the > schema, but this might not be regarded as a change. It could become a pain > if there are a lot of tables but if we are talking about only a handful it > should be possible, using a hardcoded hostname on each publisher's indexed > views. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) >
The data in the publishers is different (except possibly for the keys. I
am assuming INTs here). All of the data will be in one subscriber for reporting purposes. I have to look up "custom sync object." I may know it by a different name. Might transformable subscriptions be relevant? Have not tested or used them in the past -- Kevin Hill 3NF Consulting www.3nf-inc.com/NewsGroups.htm [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:e9IxoyElGHA.3304@TK2MSFTNGP03.phx.gbl... > Its not clear to me whether the data in these subscriber databases will be > identical or different. If they are to be different you could use a custom > sync object for each one. > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > 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 > > > > "Kevin3NF" <Kevin@DontNeedNoSpam3NF-inc.com> wrote in message > news:%23b3P1WBlGHA.3588@TK2MSFTNGP02.phx.gbl... >> This could be for 2000 or 2005...all my experience is with 2000... >> >> Transactional replication. >> >> I need to set up a Central Subscriber to be a reporting server, >> subscribing to 80 different database that are identical in schema (each >> db is a different facility). I cannot alter the schema of the >> publishers...3rd party application. >> >> The part I need opinions on is how to differentiate which database the >> data came from as it goes into the subscription db. >> >> I have tested pulling the HOSTNAME from sysprocesses in the Update and >> Insert stored procs, and that works just fine, but these 80 databases >> will all be on the same host. >> >> Any suggestions? >> >> -- >> Kevin Hill >> 3NF Consulting >> www.3nf-inc.com/NewsGroups.htm >> www.expertsrt.com - not your average tech Q&A site >> >> >> >> > >
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 |