We have setup merge replication on a production server/database that
is also used for our web applications. We need the replication for
disconnected computing using an MSDE database. The replication works
exceptionally well, however, we had to publish ID ranges on the tables
so that data could be merged with the server correctly. This has
caused a problem with our web based applications. Once the end of the
ID range is reached, our other applications cannot insert data into
the tables without resetting the ranges or removing replication.
Has anyone else seen this problem? If so, is there a good work-around
to allow both entities to co-exist using the same database? We would
like to avoid adding a significant amount of code/scripts to manage
this automatically. Would we be better off to use a snapshot method
of replication and insert data directly from that?
Thanks for any suggestions you may have.
Dan Broomall
Dan,
there are 2 ways I can think of:
you can use large ranges - so large in fact that no new ranges are required,
or you can select to manually manage the ranges and select a simple
algorithm that ensures there is never any overlap between publisher and
subscriber(s). EG if you have a publisher and a single subscriber, the
publisher could have a seed of 1 and increment of 2 while the subscriber has
a seed of 2 and increment of 2 - the publisher has odd identity values and
the subscriber evens.
HTH,
Paul Ibison
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Monday, March 26, 2012
Replication Datawarehousing
Can anyone give me a clue as to do DataWarehousing using Replication
Publisher/Subscriber?
I have 4 Production Database's that I want to replicate to one
database acting as a DataWarehouse.
For the 4 production Databases I created a publication for each
database.
I then created a subscription for each publication on the machine that
will have the DataWarehouse database.
I am using "Transactional Publication".
When I set up all the subscriptions my DataWarehouse is only holding
the data of the last publication I set up.
I think this is because the Publication SnapShot over writes all the
other data.
Is there any way around this?
Am I doing this all wrong?
This is called a central subscriber.
In the articles section of the replication wizard there is a tab called
snapshot. In this section select the option to keep the existing table
intact.
Ideally you would have a filter on each publication so if you have to send
the snapshot down again, you would use the delete where the data meets the
row filter option and only the data from a particular publisher would be
deleted.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1175106220.671931.201830@.e65g2000hsc.googlegr oups.com...
> Can anyone give me a clue as to do DataWarehousing using Replication
> Publisher/Subscriber?
> I have 4 Production Database's that I want to replicate to one
> database acting as a DataWarehouse.
> For the 4 production Databases I created a publication for each
> database.
> I then created a subscription for each publication on the machine that
> will have the DataWarehouse database.
> I am using "Transactional Publication".
> When I set up all the subscriptions my DataWarehouse is only holding
> the data of the last publication I set up.
> I think this is because the Publication SnapShot over writes all the
> other data.
> Is there any way around this?
> Am I doing this all wrong?
>
|||On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Please take a look at this article:http://www.sql-server-performance.com/pi_multiple_publishers_replicat...
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hilary
When you say the "Replication Wizard" do you mean the Publication
Wizard? When I set up the publication and do the Articles Section
where you choose the Tables I don't get a SnapShot Tab. Not Using
Enterprise. Using Standard.
Mike
|||On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Hilary
> When you say the "Replication Wizard" do you mean the Publication
> Wizard? When I set up the publication and do the Articles Section
> where you choose the Tables I don't get a SnapShot Tab. Not Using
> Enterprise. Using Standard.
> Mike
I'm using "Truncate all data in the existing object" for all Article
Property
"Action if name is in use" and when I reinitialize the subscription it
still overwrites the data so I only get the data from one publisher
|||On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Hilary
> When you say the "Replication Wizard" do you mean the Publication
> Wizard? When I set up the publication and do the Articles Section
> where you choose the Tables I don't get a SnapShot Tab. Not Using
> Enterprise. Using Standard.
> Mike
If I choose "Keep existing object unchanged" I get the data from both
Publications into the Central Subscriber.
Now if I reinitialize the publications will it double up the data?
|||On Mar 29, 10:06 am, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
>
>
> If I choose "Keep existing object unchanged" I get the data from both
> Publications into the Central Subscriber.
> Now if I reinitialize the publications will it double up the data?
Jughead;
I believe that Hilary's quote comes into play here:
"Ideally you would have a filter on each publication so if you have to
send
the snapshot down again, you would use the delete where the data meets
the
row filter option and only the data from a particular publisher would
be
deleted. "
Joseph
Publisher/Subscriber?
I have 4 Production Database's that I want to replicate to one
database acting as a DataWarehouse.
For the 4 production Databases I created a publication for each
database.
I then created a subscription for each publication on the machine that
will have the DataWarehouse database.
I am using "Transactional Publication".
When I set up all the subscriptions my DataWarehouse is only holding
the data of the last publication I set up.
I think this is because the Publication SnapShot over writes all the
other data.
Is there any way around this?
Am I doing this all wrong?
This is called a central subscriber.
In the articles section of the replication wizard there is a tab called
snapshot. In this section select the option to keep the existing table
intact.
Ideally you would have a filter on each publication so if you have to send
the snapshot down again, you would use the delete where the data meets the
row filter option and only the data from a particular publisher would be
deleted.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1175106220.671931.201830@.e65g2000hsc.googlegr oups.com...
> Can anyone give me a clue as to do DataWarehousing using Replication
> Publisher/Subscriber?
> I have 4 Production Database's that I want to replicate to one
> database acting as a DataWarehouse.
> For the 4 production Databases I created a publication for each
> database.
> I then created a subscription for each publication on the machine that
> will have the DataWarehouse database.
> I am using "Transactional Publication".
> When I set up all the subscriptions my DataWarehouse is only holding
> the data of the last publication I set up.
> I think this is because the Publication SnapShot over writes all the
> other data.
> Is there any way around this?
> Am I doing this all wrong?
>
|||On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Please take a look at this article:http://www.sql-server-performance.com/pi_multiple_publishers_replicat...
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hilary
When you say the "Replication Wizard" do you mean the Publication
Wizard? When I set up the publication and do the Articles Section
where you choose the Tables I don't get a SnapShot Tab. Not Using
Enterprise. Using Standard.
Mike
|||On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Hilary
> When you say the "Replication Wizard" do you mean the Publication
> Wizard? When I set up the publication and do the Articles Section
> where you choose the Tables I don't get a SnapShot Tab. Not Using
> Enterprise. Using Standard.
> Mike
I'm using "Truncate all data in the existing object" for all Article
Property
"Action if name is in use" and when I reinitialize the subscription it
still overwrites the data so I only get the data from one publisher
|||On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Hilary
> When you say the "Replication Wizard" do you mean the Publication
> Wizard? When I set up the publication and do the Articles Section
> where you choose the Tables I don't get a SnapShot Tab. Not Using
> Enterprise. Using Standard.
> Mike
If I choose "Keep existing object unchanged" I get the data from both
Publications into the Central Subscriber.
Now if I reinitialize the publications will it double up the data?
|||On Mar 29, 10:06 am, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
>
>
> If I choose "Keep existing object unchanged" I get the data from both
> Publications into the Central Subscriber.
> Now if I reinitialize the publications will it double up the data?
Jughead;
I believe that Hilary's quote comes into play here:
"Ideally you would have a filter on each publication so if you have to
send
the snapshot down again, you would use the delete where the data meets
the
row filter option and only the data from a particular publisher would
be
deleted. "
Joseph
Wednesday, March 21, 2012
Replication between never-connected SQL Servers
Is it possible to set-up replication between two servers that are never
directly connected?
We have a production server that is not connected to our network for
security reasons. I am doing testing on a local server and then want to
periodically apply the updates to the production server via CD. I haven't
been able to do this with replication as it seems to require a connection to
the subscription db for the initial snapshot.
At the moment, the only way I have been able to do this is by creating a
backup and recreating the db on the remote server.
Thanks!
James
James,
replication will require a connection between servers at some point. Where a
connection is not possible, you can use a derivative of log shipping or
database shipping as you are doing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks very much Paul.
James
"Paul Ibison" wrote:
> James,
> replication will require a connection between servers at some point. Where a
> connection is not possible, you can use a derivative of log shipping or
> database shipping as you are doing.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
directly connected?
We have a production server that is not connected to our network for
security reasons. I am doing testing on a local server and then want to
periodically apply the updates to the production server via CD. I haven't
been able to do this with replication as it seems to require a connection to
the subscription db for the initial snapshot.
At the moment, the only way I have been able to do this is by creating a
backup and recreating the db on the remote server.
Thanks!
James
James,
replication will require a connection between servers at some point. Where a
connection is not possible, you can use a derivative of log shipping or
database shipping as you are doing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks very much Paul.
James
"Paul Ibison" wrote:
> James,
> replication will require a connection between servers at some point. Where a
> connection is not possible, you can use a derivative of log shipping or
> database shipping as you are doing.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Tuesday, March 20, 2012
Replication and secure off-site servers
FYI - I know basically next to nothing about replication.
We have production servers that are located in a secure location entirely
separate from our own location and networkd. We want to set up a hot-copy
that is, at most, a few minutes behind production but that would be located
in our own office, on our own network. Any clues as to whether this is
possible using SQL Server replication or would we need to consider a third
party solution, such as that offered by DataMirror?
Thanks,
Michael MacGregor
Database Architect
Michael,
some people have used transactional replication for this purposes. To keep
the 2 systems entirely in sync will require careful planning though, as not
all objects can be replicated - eg permissions, users and indexes, and by
default you'll lose identity attributes. Certainly I can see queued updating
subscribers being used in this way, which avoids the issues of not having
the identity columns, but it still leaves the matter of the other objects
being manually synchronized. Log shipping on the other hand will take all
the extra objects. In theory the log could be shipped once a minute but in
my experience you'll be pushing it to get a very low latency. I suspect this
might be the best option for your case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||How does that work when there is a firewall between the Production server
and the hot-copy?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
> Michael,
> some people have used transactional replication for this purposes. To keep
> the 2 systems entirely in sync will require careful planning though, as
not
> all objects can be replicated - eg permissions, users and indexes, and by
> default you'll lose identity attributes. Certainly I can see queued
updating
> subscribers being used in this way, which avoids the issues of not having
> the identity columns, but it still leaves the matter of the other objects
> being manually synchronized. Log shipping on the other hand will take all
> the extra objects. In theory the log could be shipped once a minute but in
> my experience you'll be pushing it to get a very low latency. I suspect
this
> might be the best option for your case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hello Michael,
It is suggested that you create a VPN connection to the remote network so
that publisher/subscriber are virtually in the same network. You may want
to adjust the repliciation agent profile parameters so that it can work
properly on a low bandwidth.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Mon, 15 Aug 2005 12:53:49 -0400
| Lines: 27
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15827
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How does that work when there is a firewall between the Production server
| and the hot-copy?
|
| "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
| news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
| > Michael,
| > some people have used transactional replication for this purposes. To
keep
| > the 2 systems entirely in sync will require careful planning though, as
| not
| > all objects can be replicated - eg permissions, users and indexes, and
by
| > default you'll lose identity attributes. Certainly I can see queued
| updating
| > subscribers being used in this way, which avoids the issues of not
having
| > the identity columns, but it still leaves the matter of the other
objects
| > being manually synchronized. Log shipping on the other hand will take
all
| > the extra objects. In theory the log could be shipped once a minute but
in
| > my experience you'll be pushing it to get a very low latency. I suspect
| this
| > might be the best option for your case.
| > Cheers,
| > Paul Ibison SQL Server MVP, www.replicationanswers.com
| > (recommended sql server 2000 replication book:
| > http://www.nwsu.com/0974973602p.html)
| >
| >
|
|
|
|||Michael,
I agree with Peter's advice re VPN, but if this is not possible, port 1433
(or whatever your sql port is) will need to be opened, as well as your FTP
port. This can still be locked down by restricting access to certain IP
address subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In SQL 2005, are the replication processes improved, are there better
interfaces, etc., compared to SQL 2000/
Michael MacGregor
Database Architect
|||Hello Michael,
As I know, there are some improvments in replication in such as management,
performance, monitor etc. I suggest that you go to SQL 2005 beta newsgroup
for more details about this.
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
<O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
<zGjivHioFHA.940@.TK2MSFTNGXA01.phx.gbl>
<#x61AvjoFHA.2180@.TK2MSFTNGP15.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Fri, 19 Aug 2005 10:44:49 -0400
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <#y5qXyMpFHA.3536@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15951
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| In SQL 2005, are the replication processes improved, are there better
| interfaces, etc., compared to SQL 2000/
|
| Michael MacGregor
| Database Architect
|
|
|
|||Michael,
I haven't done any performance measurements yet so can't really compare this
side of things.
From the interface point of view, the basic functionality is enhanced (eg
Hostname as a subscription property in Merge, tracer tokens exist in
Transactional), there are a few completely new areas (eg Oracle -> SQL,
Merge over HTTP) and GUIwise, the Replication Monitor is now isolated as a
separate application. I've written a few basic articles on
www.replicationanswers.com on a few of these.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
We have production servers that are located in a secure location entirely
separate from our own location and networkd. We want to set up a hot-copy
that is, at most, a few minutes behind production but that would be located
in our own office, on our own network. Any clues as to whether this is
possible using SQL Server replication or would we need to consider a third
party solution, such as that offered by DataMirror?
Thanks,
Michael MacGregor
Database Architect
Michael,
some people have used transactional replication for this purposes. To keep
the 2 systems entirely in sync will require careful planning though, as not
all objects can be replicated - eg permissions, users and indexes, and by
default you'll lose identity attributes. Certainly I can see queued updating
subscribers being used in this way, which avoids the issues of not having
the identity columns, but it still leaves the matter of the other objects
being manually synchronized. Log shipping on the other hand will take all
the extra objects. In theory the log could be shipped once a minute but in
my experience you'll be pushing it to get a very low latency. I suspect this
might be the best option for your case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||How does that work when there is a firewall between the Production server
and the hot-copy?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
> Michael,
> some people have used transactional replication for this purposes. To keep
> the 2 systems entirely in sync will require careful planning though, as
not
> all objects can be replicated - eg permissions, users and indexes, and by
> default you'll lose identity attributes. Certainly I can see queued
updating
> subscribers being used in this way, which avoids the issues of not having
> the identity columns, but it still leaves the matter of the other objects
> being manually synchronized. Log shipping on the other hand will take all
> the extra objects. In theory the log could be shipped once a minute but in
> my experience you'll be pushing it to get a very low latency. I suspect
this
> might be the best option for your case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hello Michael,
It is suggested that you create a VPN connection to the remote network so
that publisher/subscriber are virtually in the same network. You may want
to adjust the repliciation agent profile parameters so that it can work
properly on a low bandwidth.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Mon, 15 Aug 2005 12:53:49 -0400
| Lines: 27
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15827
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How does that work when there is a firewall between the Production server
| and the hot-copy?
|
| "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
| news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
| > Michael,
| > some people have used transactional replication for this purposes. To
keep
| > the 2 systems entirely in sync will require careful planning though, as
| not
| > all objects can be replicated - eg permissions, users and indexes, and
by
| > default you'll lose identity attributes. Certainly I can see queued
| updating
| > subscribers being used in this way, which avoids the issues of not
having
| > the identity columns, but it still leaves the matter of the other
objects
| > being manually synchronized. Log shipping on the other hand will take
all
| > the extra objects. In theory the log could be shipped once a minute but
in
| > my experience you'll be pushing it to get a very low latency. I suspect
| this
| > might be the best option for your case.
| > Cheers,
| > Paul Ibison SQL Server MVP, www.replicationanswers.com
| > (recommended sql server 2000 replication book:
| > http://www.nwsu.com/0974973602p.html)
| >
| >
|
|
|
|||Michael,
I agree with Peter's advice re VPN, but if this is not possible, port 1433
(or whatever your sql port is) will need to be opened, as well as your FTP
port. This can still be locked down by restricting access to certain IP
address subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In SQL 2005, are the replication processes improved, are there better
interfaces, etc., compared to SQL 2000/
Michael MacGregor
Database Architect
|||Hello Michael,
As I know, there are some improvments in replication in such as management,
performance, monitor etc. I suggest that you go to SQL 2005 beta newsgroup
for more details about this.
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
<O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
<zGjivHioFHA.940@.TK2MSFTNGXA01.phx.gbl>
<#x61AvjoFHA.2180@.TK2MSFTNGP15.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Fri, 19 Aug 2005 10:44:49 -0400
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <#y5qXyMpFHA.3536@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15951
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| In SQL 2005, are the replication processes improved, are there better
| interfaces, etc., compared to SQL 2000/
|
| Michael MacGregor
| Database Architect
|
|
|
|||Michael,
I haven't done any performance measurements yet so can't really compare this
side of things.
From the interface point of view, the basic functionality is enhanced (eg
Hostname as a subscription property in Merge, tracer tokens exist in
Transactional), there are a few completely new areas (eg Oracle -> SQL,
Merge over HTTP) and GUIwise, the Replication Monitor is now isolated as a
separate application. I've written a few basic articles on
www.replicationanswers.com on a few of these.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication and read only
There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
--
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
--
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
replication and logshipping
Hello
I read a post in the newsgroup about replication and logshipping. I wanted
to clarify further.
We have a warm-standby server to our production server. We use logshipping
to keep the stand-by in sync. Now we want to setup anothere server for
reporting as the current standby cannot serve as reporting server.(the
logshipping wither fails as there are users or the users need to be kicked
out of the stand-by server for every log restored). We thought of a few of
options.
a) use bcp to move data that we need for reporting in a batch.
b) Use linked servers to pull in data into the reporting servers
c) Tranactional replication
Our conditions are that the reporting server cannot have any downtime and
the data needs to be as close to real-time as possible (currently we are
running reports from the production nserver)
Have any of you tried any of the above?. if so can u share your expreiences.
Note: I have sql server experience. But have shunned away from replication
due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
appreciates.
techdummy
If your reporting server "cannot have any downtime and the data needs to be
as close to real-time as possible", transactional replication is your best
bet.
Since you are currently running these reports off your production server,
I'm guessing that your production server is pretty beefy and has ample
headroom. Given that, you can set a fairly low delay for transactional
replication so that updates get replicated quickly. <5minutes is pretty good
but can be lower (continuous if really necessary). Just make sure the server
and network doesn't get overwhelmed if you have lots of transactions going
through.
Of course, this is assuming you don't have some massive or a complex DB like
SAP or the likes of it.
BCP would work too but IMHO, it's quite a bit more work to script things
out, plug them into SQL Agent and monitor manually (sort of). At least with
replication, a lof of the setting up and monitoring is "relatively" easy so
long as you know what you're doing. Log Shipping won't work since you can't
read the standby database while LS is active.
joe.
"techdummy" <techdummy@.discussions.microsoft.com> wrote in message
news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
> Hello
> I read a post in the newsgroup about replication and logshipping. I
> wanted
> to clarify further.
> We have a warm-standby server to our production server. We use logshipping
> to keep the stand-by in sync. Now we want to setup anothere server for
> reporting as the current standby cannot serve as reporting server.(the
> logshipping wither fails as there are users or the users need to be kicked
> out of the stand-by server for every log restored). We thought of a few
> of
> options.
> a) use bcp to move data that we need for reporting in a batch.
> b) Use linked servers to pull in data into the reporting servers
> c) Tranactional replication
> Our conditions are that the reporting server cannot have any downtime and
> the data needs to be as close to real-time as possible (currently we are
> running reports from the production nserver)
>
> Have any of you tried any of the above?. if so can u share your
> expreiences.
>
> Note: I have sql server experience. But have shunned away from replication
> due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
> appreciates.
> --
> techdummy
|||Joe,
Thanks for ur response. I think replication would be agood choice as well.
However, will it work with Logshipping going on from the production server to
the standby?
techdummy
"Joe Yong" wrote:
> If your reporting server "cannot have any downtime and the data needs to be
> as close to real-time as possible", transactional replication is your best
> bet.
> Since you are currently running these reports off your production server,
> I'm guessing that your production server is pretty beefy and has ample
> headroom. Given that, you can set a fairly low delay for transactional
> replication so that updates get replicated quickly. <5minutes is pretty good
> but can be lower (continuous if really necessary). Just make sure the server
> and network doesn't get overwhelmed if you have lots of transactions going
> through.
> Of course, this is assuming you don't have some massive or a complex DB like
> SAP or the likes of it.
> BCP would work too but IMHO, it's quite a bit more work to script things
> out, plug them into SQL Agent and monitor manually (sort of). At least with
> replication, a lof of the setting up and monitoring is "relatively" easy so
> long as you know what you're doing. Log Shipping won't work since you can't
> read the standby database while LS is active.
>
> joe.
> "techdummy" <techdummy@.discussions.microsoft.com> wrote in message
> news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
>
>
|||We use replication for this. It works well. Latency can be as low as 10-60
seconds under high load (2500 tps).
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
"techdummy" <techdummy@.discussions.microsoft.com> wrote in message
news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
> Hello
> I read a post in the newsgroup about replication and logshipping. I
wanted
> to clarify further.
> We have a warm-standby server to our production server. We use logshipping
> to keep the stand-by in sync. Now we want to setup anothere server for
> reporting as the current standby cannot serve as reporting server.(the
> logshipping wither fails as there are users or the users need to be kicked
> out of the stand-by server for every log restored). We thought of a few
of
> options.
> a) use bcp to move data that we need for reporting in a batch.
> b) Use linked servers to pull in data into the reporting servers
> c) Tranactional replication
> Our conditions are that the reporting server cannot have any downtime and
> the data needs to be as close to real-time as possible (currently we are
> running reports from the production nserver)
>
> Have any of you tried any of the above?. if so can u share your
expreiences.
>
> Note: I have sql server experience. But have shunned away from replication
> due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
> appreciates.
> --
> techdummy
|||Hi
Are u using logshipping as well in parallel to replication from the same
source server?
techdummy
"Joe Yong" wrote:
> If your reporting server "cannot have any downtime and the data needs to be
> as close to real-time as possible", transactional replication is your best
> bet.
> Since you are currently running these reports off your production server,
> I'm guessing that your production server is pretty beefy and has ample
> headroom. Given that, you can set a fairly low delay for transactional
> replication so that updates get replicated quickly. <5minutes is pretty good
> but can be lower (continuous if really necessary). Just make sure the server
> and network doesn't get overwhelmed if you have lots of transactions going
> through.
> Of course, this is assuming you don't have some massive or a complex DB like
> SAP or the likes of it.
> BCP would work too but IMHO, it's quite a bit more work to script things
> out, plug them into SQL Agent and monitor manually (sort of). At least with
> replication, a lof of the setting up and monitoring is "relatively" easy so
> long as you know what you're doing. Log Shipping won't work since you can't
> read the standby database while LS is active.
>
> joe.
> "techdummy" <techdummy@.discussions.microsoft.com> wrote in message
> news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
>
>
I read a post in the newsgroup about replication and logshipping. I wanted
to clarify further.
We have a warm-standby server to our production server. We use logshipping
to keep the stand-by in sync. Now we want to setup anothere server for
reporting as the current standby cannot serve as reporting server.(the
logshipping wither fails as there are users or the users need to be kicked
out of the stand-by server for every log restored). We thought of a few of
options.
a) use bcp to move data that we need for reporting in a batch.
b) Use linked servers to pull in data into the reporting servers
c) Tranactional replication
Our conditions are that the reporting server cannot have any downtime and
the data needs to be as close to real-time as possible (currently we are
running reports from the production nserver)
Have any of you tried any of the above?. if so can u share your expreiences.
Note: I have sql server experience. But have shunned away from replication
due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
appreciates.
techdummy
If your reporting server "cannot have any downtime and the data needs to be
as close to real-time as possible", transactional replication is your best
bet.
Since you are currently running these reports off your production server,
I'm guessing that your production server is pretty beefy and has ample
headroom. Given that, you can set a fairly low delay for transactional
replication so that updates get replicated quickly. <5minutes is pretty good
but can be lower (continuous if really necessary). Just make sure the server
and network doesn't get overwhelmed if you have lots of transactions going
through.
Of course, this is assuming you don't have some massive or a complex DB like
SAP or the likes of it.
BCP would work too but IMHO, it's quite a bit more work to script things
out, plug them into SQL Agent and monitor manually (sort of). At least with
replication, a lof of the setting up and monitoring is "relatively" easy so
long as you know what you're doing. Log Shipping won't work since you can't
read the standby database while LS is active.
joe.
"techdummy" <techdummy@.discussions.microsoft.com> wrote in message
news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
> Hello
> I read a post in the newsgroup about replication and logshipping. I
> wanted
> to clarify further.
> We have a warm-standby server to our production server. We use logshipping
> to keep the stand-by in sync. Now we want to setup anothere server for
> reporting as the current standby cannot serve as reporting server.(the
> logshipping wither fails as there are users or the users need to be kicked
> out of the stand-by server for every log restored). We thought of a few
> of
> options.
> a) use bcp to move data that we need for reporting in a batch.
> b) Use linked servers to pull in data into the reporting servers
> c) Tranactional replication
> Our conditions are that the reporting server cannot have any downtime and
> the data needs to be as close to real-time as possible (currently we are
> running reports from the production nserver)
>
> Have any of you tried any of the above?. if so can u share your
> expreiences.
>
> Note: I have sql server experience. But have shunned away from replication
> due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
> appreciates.
> --
> techdummy
|||Joe,
Thanks for ur response. I think replication would be agood choice as well.
However, will it work with Logshipping going on from the production server to
the standby?
techdummy
"Joe Yong" wrote:
> If your reporting server "cannot have any downtime and the data needs to be
> as close to real-time as possible", transactional replication is your best
> bet.
> Since you are currently running these reports off your production server,
> I'm guessing that your production server is pretty beefy and has ample
> headroom. Given that, you can set a fairly low delay for transactional
> replication so that updates get replicated quickly. <5minutes is pretty good
> but can be lower (continuous if really necessary). Just make sure the server
> and network doesn't get overwhelmed if you have lots of transactions going
> through.
> Of course, this is assuming you don't have some massive or a complex DB like
> SAP or the likes of it.
> BCP would work too but IMHO, it's quite a bit more work to script things
> out, plug them into SQL Agent and monitor manually (sort of). At least with
> replication, a lof of the setting up and monitoring is "relatively" easy so
> long as you know what you're doing. Log Shipping won't work since you can't
> read the standby database while LS is active.
>
> joe.
> "techdummy" <techdummy@.discussions.microsoft.com> wrote in message
> news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
>
>
|||We use replication for this. It works well. Latency can be as low as 10-60
seconds under high load (2500 tps).
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
"techdummy" <techdummy@.discussions.microsoft.com> wrote in message
news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
> Hello
> I read a post in the newsgroup about replication and logshipping. I
wanted
> to clarify further.
> We have a warm-standby server to our production server. We use logshipping
> to keep the stand-by in sync. Now we want to setup anothere server for
> reporting as the current standby cannot serve as reporting server.(the
> logshipping wither fails as there are users or the users need to be kicked
> out of the stand-by server for every log restored). We thought of a few
of
> options.
> a) use bcp to move data that we need for reporting in a batch.
> b) Use linked servers to pull in data into the reporting servers
> c) Tranactional replication
> Our conditions are that the reporting server cannot have any downtime and
> the data needs to be as close to real-time as possible (currently we are
> running reports from the production nserver)
>
> Have any of you tried any of the above?. if so can u share your
expreiences.
>
> Note: I have sql server experience. But have shunned away from replication
> due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
> appreciates.
> --
> techdummy
|||Hi
Are u using logshipping as well in parallel to replication from the same
source server?
techdummy
"Joe Yong" wrote:
> If your reporting server "cannot have any downtime and the data needs to be
> as close to real-time as possible", transactional replication is your best
> bet.
> Since you are currently running these reports off your production server,
> I'm guessing that your production server is pretty beefy and has ample
> headroom. Given that, you can set a fairly low delay for transactional
> replication so that updates get replicated quickly. <5minutes is pretty good
> but can be lower (continuous if really necessary). Just make sure the server
> and network doesn't get overwhelmed if you have lots of transactions going
> through.
> Of course, this is assuming you don't have some massive or a complex DB like
> SAP or the likes of it.
> BCP would work too but IMHO, it's quite a bit more work to script things
> out, plug them into SQL Agent and monitor manually (sort of). At least with
> replication, a lof of the setting up and monitoring is "relatively" easy so
> long as you know what you're doing. Log Shipping won't work since you can't
> read the standby database while LS is active.
>
> joe.
> "techdummy" <techdummy@.discussions.microsoft.com> wrote in message
> news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
>
>
Monday, March 12, 2012
Replication and distribution: Agents
Hi all replication experts...
I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.
There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?
Grateful for answers
Best,
/M
Its hard to say exactly what this is, but it appears to be your distribution agent. You appear to have a pull agent configured on your subscriber.
It is essential for your transactional replication topology. Do not remove it.
Hilary
|||OK, thanks :)Friday, March 9, 2012
Replication - Replicating the contents from a view to a Table
I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...You can't use replication in this way. Replication has a very data-centric
view of what it is doing and within a replication set, you have to have all
of the dependencies satisfied. So to replicate your view you would also have
to replicate the tables it depends on and any other tables for which
constraints would need to be satisfied.
By leaving the view on Server A, you also would not be reducing the report
query impact on Server A.
Why not just link the servers and define the query on Server B in terms of
the linked tables to Server A? Then you have your always live data and the
query processing will primarily be done on Server B with out involving a
complicated mechanism like replication?
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668618.880225.309670@.j55g2000cwa.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...You can't use replication in this way. Replication has a very data-centric
view of what it is doing and within a replication set, you have to have all
of the dependencies satisfied. So to replicate your view you would also have
to replicate the tables it depends on and any other tables for which
constraints would need to be satisfied.
By leaving the view on Server A, you also would not be reducing the report
query impact on Server A.
Why not just link the servers and define the query on Server B in terms of
the linked tables to Server A? Then you have your always live data and the
query processing will primarily be done on Server B with out involving a
complicated mechanism like replication?
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668618.880225.309670@.j55g2000cwa.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>
Replication - Replicating the contents from a view to a Table
I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...
Replication - Replicating the contents from a view to a Table
I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...
Replication - filtering
Hi All,
Is it possible to replicate only INSERTS and UPDATES and NOT Deletes.
We have a production server and reporting server. The challenge is to keep
90days of data in the production system, and 180days of data on the
reporting system. Need to some how to ensure that the cleanups done on the
Production server are not propogated through to the Reporting server.
Really appreciate it if someone can point me in the right direction - I am
sure this problem has been solved before.
thanks
Mathew
If you're using transactional replication, on the article properties,
commands tab, enter NONE for the delete command. if it's merge, there isn't
a straightforward method, but provided you understand the ramifications, you
could disable the merge delete trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is it possible to replicate only INSERTS and UPDATES and NOT Deletes.
We have a production server and reporting server. The challenge is to keep
90days of data in the production system, and 180days of data on the
reporting system. Need to some how to ensure that the cleanups done on the
Production server are not propogated through to the Reporting server.
Really appreciate it if someone can point me in the right direction - I am
sure this problem has been solved before.
thanks
Mathew
If you're using transactional replication, on the article properties,
commands tab, enter NONE for the delete command. if it's merge, there isn't
a straightforward method, but provided you understand the ramifications, you
could disable the merge delete trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)