Hello,
Currently I have successfully replicated data between 2
servers. The replication is for the most part stable but
does have its "issues" from time to time.I am still in the
process of understanding all situations that could cause
replication to error out.
Anyhow.. Whewww.. I am now in the position that I need to
replicate this data to 2 other servers.
My question is:
If replication occurs between SERVERA, SERVERB SERVERC
SERVERD.Lets say SERVERA replicates to SERVER B and
SERVERC but the replication fails between SERVERA and
SERVERC. Does the replication subscription I set up
between SERVERA and SERVERB also error out?
Sorry this is long winded.
Thanks,
niv
niv,
Not unless the cause of the replication failure between SERVER A and SERVER
C was caused by a network/hardware failure and the subscription on SERVER B
also relies on these resources. As long as the Log Reader and Distribution
agent is running as well as SERVER C and network connectivity is present it
should be fine.
This really is a broad question. I understand that you would like to cover
all possible scenarios and to ask you to be more specific would not be
relevant.
Charles Deaton
"niv" <niv@.hotmail.com> wrote in message
news:1b3c701c44fb3$e2c83b50$a401280a@.phx.gbl...
> Hello,
> Currently I have successfully replicated data between 2
> servers. The replication is for the most part stable but
> does have its "issues" from time to time.I am still in the
> process of understanding all situations that could cause
> replication to error out.
> Anyhow.. Whewww.. I am now in the position that I need to
> replicate this data to 2 other servers.
> My question is:
> If replication occurs between SERVERA, SERVERB SERVERC
> SERVERD.Lets say SERVERA replicates to SERVER B and
> SERVERC but the replication fails between SERVERA and
> SERVERC. Does the replication subscription I set up
> between SERVERA and SERVERB also error out?
> Sorry this is long winded.
> Thanks,
> niv
|||For transactional, there will either be one Distribution Agent per
subscription (an independent agent) or one Distribution Agent per
publication database and subscription database pair (a shared agent). For
merge, each merge subscription has its own Merge Agent that connects to and
updates both the Publisher and the Subscriber. So, as the agents in your
case are independant, a failure in one will not affect the other in a sense
of the replication process. However, as Charles points out, if the error is
a general one to your network then both agents will fail.
HTH,
Paul Ibison
Showing posts with label replicated. Show all posts
Showing posts with label replicated. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Replication and Log shipping
Sql server 2005 heavily hit database
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
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:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
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:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Monday, March 12, 2012
Replication and Deleting Rows
Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
Replication and Deleting Rows
Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
Replication and DBCCs
I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
replication need stopped in order to facilitate the dbccs'? Should a new
snapshot be taken following the dbccs?
No need to reinitialize, pause etc - you can run these in place.
If yo are running DBCC DBREINDEX you might notice some delay before the
synchronization completes though because of the locking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul.
"Paul Ibison" wrote:
> No need to reinitialize, pause etc - you can run these in place.
> If yo are running DBCC DBREINDEX you might notice some delay before the
> synchronization completes though because of the locking issues.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There have been some reports that some DBCC commands make the log reader
stop reading transactions. This was in SQL 2000.
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
"DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
> replication need stopped in order to facilitate the dbccs'? Should a new
> snapshot be taken following the dbccs?
|||We experience similar things during our weekly DBCC REINDEXing on the
replicated database. It stops the distributor from applying replicated
transactions and causes the distribution agent to timeout and fail eventually
after the retry count is exceeded.
Is there a way to automate the checking of distribution agent if it is
running at the end of REINDEXing and to restart distribution agent if not.
Thanks
PVG
"Hilary Cotter" wrote:
> There have been some reports that some DBCC commands make the log reader
> stop reading transactions. This was in SQL 2000.
> --
> 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
>
> "DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
> news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>
>
|||You could modify the reindex job to have an additional step before :
exec msdb..sp_stop_job 'distribution agent job name'
and an additional step at the end:
exec msdb..sp_start_job 'distribution agent job name'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||How about scheduling the distribution agent not to run in the interval you
are doing your re-indexing.
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
"PVG" <PVG@.discussions.microsoft.com> wrote in message
news:199ED0FD-FEE5-47AA-BE0D-CD08BB4C5B92@.microsoft.com...[vbcol=seagreen]
> We experience similar things during our weekly DBCC REINDEXing on the
> replicated database. It stops the distributor from applying replicated
> transactions and causes the distribution agent to timeout and fail
> eventually
> after the retry count is exceeded.
> Is there a way to automate the checking of distribution agent if it is
> running at the end of REINDEXing and to restart distribution agent if not.
> Thanks
> PVG
> "Hilary Cotter" wrote:
replication need stopped in order to facilitate the dbccs'? Should a new
snapshot be taken following the dbccs?
No need to reinitialize, pause etc - you can run these in place.
If yo are running DBCC DBREINDEX you might notice some delay before the
synchronization completes though because of the locking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul.
"Paul Ibison" wrote:
> No need to reinitialize, pause etc - you can run these in place.
> If yo are running DBCC DBREINDEX you might notice some delay before the
> synchronization completes though because of the locking issues.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There have been some reports that some DBCC commands make the log reader
stop reading transactions. This was in SQL 2000.
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
"DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
> replication need stopped in order to facilitate the dbccs'? Should a new
> snapshot be taken following the dbccs?
|||We experience similar things during our weekly DBCC REINDEXing on the
replicated database. It stops the distributor from applying replicated
transactions and causes the distribution agent to timeout and fail eventually
after the retry count is exceeded.
Is there a way to automate the checking of distribution agent if it is
running at the end of REINDEXing and to restart distribution agent if not.
Thanks
PVG
"Hilary Cotter" wrote:
> There have been some reports that some DBCC commands make the log reader
> stop reading transactions. This was in SQL 2000.
> --
> 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
>
> "DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
> news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>
>
|||You could modify the reindex job to have an additional step before :
exec msdb..sp_stop_job 'distribution agent job name'
and an additional step at the end:
exec msdb..sp_start_job 'distribution agent job name'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||How about scheduling the distribution agent not to run in the interval you
are doing your re-indexing.
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
"PVG" <PVG@.discussions.microsoft.com> wrote in message
news:199ED0FD-FEE5-47AA-BE0D-CD08BB4C5B92@.microsoft.com...[vbcol=seagreen]
> We experience similar things during our weekly DBCC REINDEXing on the
> replicated database. It stops the distributor from applying replicated
> transactions and causes the distribution agent to timeout and fail
> eventually
> after the retry count is exceeded.
> Is there a way to automate the checking of distribution agent if it is
> running at the end of REINDEXing and to restart distribution agent if not.
> Thanks
> PVG
> "Hilary Cotter" wrote:
replication alternatives
Anyone know of third party tools that would do the same
thing? Maybe without alter replicated tables restriction?
TIA, ChrisR
Chris,
Datamirror is sometimes mentioned in this newsgroup. I suppose a solution
could also be based on datacompare.
The main alternative that comes up is of course DTS/log shipping. These
solutions are for different scenarios, but often fit the bill. Using DTS if
you have updating subscribers can be painful however to set up.
BTW, transactional/snapshot replication (standard) doesn't alter the schema.
HTH,
Paul Ibison
thing? Maybe without alter replicated tables restriction?
TIA, ChrisR
Chris,
Datamirror is sometimes mentioned in this newsgroup. I suppose a solution
could also be based on datacompare.
The main alternative that comes up is of course DTS/log shipping. These
solutions are for different scenarios, but often fit the bill. Using DTS if
you have updating subscribers can be painful however to set up.
BTW, transactional/snapshot replication (standard) doesn't alter the schema.
HTH,
Paul Ibison
Friday, March 9, 2012
Replication & Report execution
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
Replication & NDF
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
JLS,
I'd say 'it depends' Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||" I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher."
Perfect, and exactly what I thought!!!! I knew this guy didn't have a clue as to what he was doing, and you just confirmed this for me. I read the exact same thing, and also hedged due to the fact that if the NDF becomes corrupt, the entire db is marked Suspect.....
Thanx once again Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uwKaevZ2FHA.3964@.TK2MSFTNGP10.phx.gbl...
JLS,
I'd say 'it depends' Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
|||No, we do database backups using 3rd party tool, LiteSpeed.
So, I see no advantage to the .ndf & am planning to replicate without considering the .ndf
Thanx for your opinions & advice, I really appreciate it!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:OVvZ7ta2FHA.3156@.TK2MSFTNGP10.phx.gbl...
JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||Short answer is what I thought you would say, Thanx!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXFxh1c2FHA.3912@.TK2MSFTNGP15.phx.gbl...
I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
JLS,
I'd say 'it depends'
Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||" I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher."
Perfect, and exactly what I thought!!!! I knew this guy didn't have a clue as to what he was doing, and you just confirmed this for me. I read the exact same thing, and also hedged due to the fact that if the NDF becomes corrupt, the entire db is marked Suspect.....
Thanx once again Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uwKaevZ2FHA.3964@.TK2MSFTNGP10.phx.gbl...
JLS,
I'd say 'it depends'
Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
|||No, we do database backups using 3rd party tool, LiteSpeed.
So, I see no advantage to the .ndf & am planning to replicate without considering the .ndf
Thanx for your opinions & advice, I really appreciate it!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:OVvZ7ta2FHA.3156@.TK2MSFTNGP10.phx.gbl...
JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||Short answer is what I thought you would say, Thanx!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXFxh1c2FHA.3912@.TK2MSFTNGP15.phx.gbl...
I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
Replication & Detatching
I'd like to detatch a database but I am told the following:
ATTENTION:the database cannot be detached while it is being replicated.
I was going to stop the replication but when I look in the 'Replication'
node in Enterprise Manager for that server there are no Publications or
Subscriptions listed there. Why can't I detatch the DB?
Cheers,
elziko
you have to disable the database for replication to detach it.
Go to Tools, Point to replication, configure publishers, subscribers,
distributor, and click on the publication databases tab, and disable the
database this way.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:ehqJ021YEHA.1180@.TK2MSFTNGP10.phx.gbl...
> I'd like to detatch a database but I am told the following:
> ATTENTION:the database cannot be detached while it is being replicated.
> I was going to stop the replication but when I look in the 'Replication'
> node in Enterprise Manager for that server there are no Publications or
> Subscriptions listed there. Why can't I detatch the DB?
> --
> Cheers,
> elziko
>
ATTENTION:the database cannot be detached while it is being replicated.
I was going to stop the replication but when I look in the 'Replication'
node in Enterprise Manager for that server there are no Publications or
Subscriptions listed there. Why can't I detatch the DB?
Cheers,
elziko
you have to disable the database for replication to detach it.
Go to Tools, Point to replication, configure publishers, subscribers,
distributor, and click on the publication databases tab, and disable the
database this way.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:ehqJ021YEHA.1180@.TK2MSFTNGP10.phx.gbl...
> I'd like to detatch a database but I am told the following:
> ATTENTION:the database cannot be detached while it is being replicated.
> I was going to stop the replication but when I look in the 'Replication'
> node in Enterprise Manager for that server there are no Publications or
> Subscriptions listed there. Why can't I detatch the DB?
> --
> Cheers,
> elziko
>
Replication - Row not correctly sync
Hi Everyone!
I have a strange issue in my replicated databases.
I've a 5 merge subscribers replication schema. Our client discover a row that isn't matching with another database.
After some research it seam that the correct row is only at one database and all other database, have a the same row in a incoherent status.
Anyone have a idea on how this could happen?
I'm clue less right now 
Thanks!
To fix the issue I decide to bring a change to the correct row to let it propagate to every other subcriber. But event more strange, not all collumn are updated to other subscriber!!! 
This is really really strange
Replication - general question
I have a database that I need to have replicated to another server - the server I need it replicated too already has a copy of the database. Is it possible to set up a replication and when replicating - keep it from the copy that is there? The copy that is there has a column that isn't set up on the main one.
Thanks!
Absolutely. You can specify which articles you want to replicate, and for tables, down to the columns.Wednesday, March 7, 2012
Replication - Changes to Server databases impact to local database
We have a SQLSERVER database that is replicated to many users.
We are currently in an expansion phase where we need to make changes
to the server database. Each time we rollout a new release, we are
deleting the local replicating database and recreating.
We are currently in an expansion phase where we need to make changes
to the server database. Each time we rollout a new release, we are
deleting the local replicating database and recreating.
Is there any way to automatically transfer the changes from the server
to existing local database without deleting?dipillama@.nswccd.navy.mil (Michael diPilla) wrote in message news:<4e67e68b.0308181234.4c8e5e98@.posting.google.com>...
> We have a SQLSERVER database that is replicated to many users.
> We are currently in an expansion phase where we need to make changes
> to the server database. Each time we rollout a new release, we are
> deleting the local replicating database and recreating.
> Is there any way to automatically transfer the changes from the server
> to existing local database without deleting?
It's not clear what you mean by 'changes' - changes to table
structures can be replicated if you make them through the publication
properties dialogue.
I'd suggest you post this in microsoft.public.sqlserver.replication,
as you'll probably get a better answer there.
Simon
Replication
Hi,can someone help me to detect the replication of data in SQL DB tables and how to remove the replicated data.Do you mean duplication of data? I'm not sure what you're asking here.|||Originally posted by strader
Do you mean duplication of data? I'm not sure what you're asking here.
Yes My friend duplication of data,can u help me please.|||Replication is a very different thing than duplication, so I had to ask.
Can you explain the scenario a bit further, what data are you trying to detect duplicates of?
The best situation is to avoid duplication by putting keys on columns. That might not be what you're after though
You can write some queries though that will detect multiples of something in a table.
Something like:
select dupfield, count(dupfield)
from duptable
group by dupfield
having count(dupfield) > 1
Do you mean duplication of data? I'm not sure what you're asking here.
Yes My friend duplication of data,can u help me please.|||Replication is a very different thing than duplication, so I had to ask.
Can you explain the scenario a bit further, what data are you trying to detect duplicates of?
The best situation is to avoid duplication by putting keys on columns. That might not be what you're after though
You can write some queries though that will detect multiples of something in a table.
Something like:
select dupfield, count(dupfield)
from duptable
group by dupfield
having count(dupfield) > 1
Replication
Dear Professional,
I am encountering one error while the data is going to be replicated.
Here is the detail !
Last Command :
{CALL sp_MSins_Master (16, 'Okie', 1,
{5575376A-73CB-419C-9175-F6662FB115B7})}
Transaction sequence number and command ID of last execution batch are
0x0000000B00000197000400000000 and 1.
Error message :
Could not find stored procedure 'sp_MSins_Master'.
Error Detail:
Could not find stored procedure 'sp_MSins_Master'.
(Source: NOOR\NOOR (Data source); Error number: 2812)
What I have to do for resolving the stored procedure 'sp_MSins_Master'.
Thanks in advance.
The procedure 'sp_MSins_Master' needs to exist in the subscribing database,
for replication to apply the insert statements. If this proc is lost for
some reason, then you can recreate it by runnning
sp_scriptpublicationcustomprocs on the publishing database, copying and
running the output script on the subscribing database.
Note that you only need to run the script for missing stored procedures. See
BOL for more info.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <naissani@.hotmail.com> wrote in message
news:u5nhUo8EFHA.1296@.TK2MSFTNGP10.phx.gbl...
Dear Professional,
I am encountering one error while the data is going to be replicated.
Here is the detail !
Last Command :
{CALL sp_MSins_Master (16, 'Okie', 1,
{5575376A-73CB-419C-9175-F6662FB115B7})}
Transaction sequence number and command ID of last execution batch are
0x0000000B00000197000400000000 and 1.
Error message :
Could not find stored procedure 'sp_MSins_Master'.
Error Detail:
Could not find stored procedure 'sp_MSins_Master'.
(Source: NOOR\NOOR (Data source); Error number: 2812)
What I have to do for resolving the stored procedure 'sp_MSins_Master'.
Thanks in advance.
I am encountering one error while the data is going to be replicated.
Here is the detail !
Last Command :
{CALL sp_MSins_Master (16, 'Okie', 1,
{5575376A-73CB-419C-9175-F6662FB115B7})}
Transaction sequence number and command ID of last execution batch are
0x0000000B00000197000400000000 and 1.
Error message :
Could not find stored procedure 'sp_MSins_Master'.
Error Detail:
Could not find stored procedure 'sp_MSins_Master'.
(Source: NOOR\NOOR (Data source); Error number: 2812)
What I have to do for resolving the stored procedure 'sp_MSins_Master'.
Thanks in advance.
The procedure 'sp_MSins_Master' needs to exist in the subscribing database,
for replication to apply the insert statements. If this proc is lost for
some reason, then you can recreate it by runnning
sp_scriptpublicationcustomprocs on the publishing database, copying and
running the output script on the subscribing database.
Note that you only need to run the script for missing stored procedures. See
BOL for more info.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <naissani@.hotmail.com> wrote in message
news:u5nhUo8EFHA.1296@.TK2MSFTNGP10.phx.gbl...
Dear Professional,
I am encountering one error while the data is going to be replicated.
Here is the detail !
Last Command :
{CALL sp_MSins_Master (16, 'Okie', 1,
{5575376A-73CB-419C-9175-F6662FB115B7})}
Transaction sequence number and command ID of last execution batch are
0x0000000B00000197000400000000 and 1.
Error message :
Could not find stored procedure 'sp_MSins_Master'.
Error Detail:
Could not find stored procedure 'sp_MSins_Master'.
(Source: NOOR\NOOR (Data source); Error number: 2812)
What I have to do for resolving the stored procedure 'sp_MSins_Master'.
Thanks in advance.
Saturday, February 25, 2012
Replication
undefined
Hi,
I have successfully replicated a database (type : Transactional), but when i have executed a Alter scripts to effect the schema of the database it worked. My concern is whether the replication was done successfully or not ?
Can we change the schema of a replicated database by plain Alter scripts ?
Plz let me know....
Thanks
RakeshDepending on what you altered. If you want to alter rules or foreign keys without checking replication, you don't have to do anything to the replication setup. But if you change table schma, you need to either use the replication system procedures to change it or you have to drop all subscriptions and initialize them.|||I am able to alter the table schema by using Replication stored procedures, but my concern is, i am also able to change the table schema by simple alter statements on the primary database, but these changes are not making into secondary database i,e subscriber database. Do we get any error Msg trying to alter a table schema of a replicated database ?|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.
Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?
-PatP|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.
Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?
-PatP
In case of merge replication it is not possible to alter repilcated table but for transaction replication it works. Welcome to Microsoft...
Hi,
I have successfully replicated a database (type : Transactional), but when i have executed a Alter scripts to effect the schema of the database it worked. My concern is whether the replication was done successfully or not ?
Can we change the schema of a replicated database by plain Alter scripts ?
Plz let me know....
Thanks
RakeshDepending on what you altered. If you want to alter rules or foreign keys without checking replication, you don't have to do anything to the replication setup. But if you change table schma, you need to either use the replication system procedures to change it or you have to drop all subscriptions and initialize them.|||I am able to alter the table schema by using Replication stored procedures, but my concern is, i am also able to change the table schema by simple alter statements on the primary database, but these changes are not making into secondary database i,e subscriber database. Do we get any error Msg trying to alter a table schema of a replicated database ?|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.
Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?
-PatP|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.
Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?
-PatP
In case of merge replication it is not possible to alter repilcated table but for transaction replication it works. Welcome to Microsoft...
Monday, February 20, 2012
replicating the resultset of a view
hello all,
I created a view and I want the result set(records) of the view to be replicated to another server. i've tried replicating the view by checking the view in the replication wizard but it seems that only the definition of the view is being replicated. Is there a way that i can fullfill the desired result through replication. That to store the result of the view to a new table in the replication destination. I'm using snapshot replication
thanks in advance
joey
Hi Joey,
I saw that Paul Ibison has already suggested that you publish your view as an indexed view to table article (or the slightly misnamed 'indexed view logbased' type article) on the microsoft.public.sqlserver.replication newsgroup. This is probably the easiest solution to your problem although creating a unique index on your view may impair update performance at your publisher. If your view is defined to return a subset of columns for one of your tables, you may want to publish the underlying table with horizontal\vertical filters that match your view definition. We will also consider to simply allow the scenario that you described in a future release.
Thanks for your feedback.
-Raymond
Replicating Stored Procedure
Hi:
I made a change to the stored proc in the publisher database but the change
did not get replicated. I should not have to run the snapshot agent again to
do that. Please let me know how can this be easily done.
Thanks
Mike,
this is better handled in SQL 2005, but in 2000 we can use sp_addscriptexec,
linked servers or manual addition of the new code. For this reason I
generally add the code parts of my publications (sps, views, functions) to a
separate publication, so if I decide to use replication to send them over, it
doesn't disturb my main publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use sp_addscriptexec to send the commands if your subscribers were deployed
through UNC(file copies). If there were deployed through ftp you will have
to connect to them individual and run the commands individually.
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
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:15028C08-3FA7-4647-9C23-7A2698AF8C41@.microsoft.com...
> Hi:
> I made a change to the stored proc in the publisher database but the
> change
> did not get replicated. I should not have to run the snapshot agent again
> to
> do that. Please let me know how can this be easily done.
> Thanks
>
I made a change to the stored proc in the publisher database but the change
did not get replicated. I should not have to run the snapshot agent again to
do that. Please let me know how can this be easily done.
Thanks
Mike,
this is better handled in SQL 2005, but in 2000 we can use sp_addscriptexec,
linked servers or manual addition of the new code. For this reason I
generally add the code parts of my publications (sps, views, functions) to a
separate publication, so if I decide to use replication to send them over, it
doesn't disturb my main publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use sp_addscriptexec to send the commands if your subscribers were deployed
through UNC(file copies). If there were deployed through ftp you will have
to connect to them individual and run the commands individually.
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
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:15028C08-3FA7-4647-9C23-7A2698AF8C41@.microsoft.com...
> Hi:
> I made a change to the stored proc in the publisher database but the
> change
> did not get replicated. I should not have to run the snapshot agent again
> to
> do that. Please let me know how can this be easily done.
> Thanks
>
Replicating ORACLE views?
I have successfully replicated ORACLE tables to a SQL Server, however
I don't see ORACLE views listed in the "Articles" dialog when I open
the ORACLE publication properties.
Is this because it is not possible to replicate ORACLE views or am i
missing something?
TIA
This is not supported for Oracle publishers - pls take a look at
sp_addarticle (http://msdn2.microsoft.com/en-us/library/ms173857.aspx)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hi,
I am trying to setup transactional replication between an Oracle DB (one
table - that too few of the columns) and SQL Server 2005. Can you please
point me to an article or web page where I can get some instructions. I have
followed the step in BOL and getting nowhere. Your help will be much
appreciated.
Thank you,
Yoga
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uw28PlTUHHA.1016@.TK2MSFTNGP04.phx.gbl...
> This is not supported for Oracle publishers - pls take a look at
> sp_addarticle (http://msdn2.microsoft.com/en-us/library/ms173857.aspx)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I know Andrew Carter was looking for people using the functionality to work
with a while back
(http://blogs.msdn.com/ascarter/archive/2004/08/05/209056.aspx) - might be
worth contacting him. There's also a webcast:
http://whitepapers.techrepublic.com.com/whitepaper.aspx?docid=149653. Apart
from that I think Sujoy Paul's Replication book has a section on setting it
up as far as I recall.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
I don't see ORACLE views listed in the "Articles" dialog when I open
the ORACLE publication properties.
Is this because it is not possible to replicate ORACLE views or am i
missing something?
TIA
This is not supported for Oracle publishers - pls take a look at
sp_addarticle (http://msdn2.microsoft.com/en-us/library/ms173857.aspx)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hi,
I am trying to setup transactional replication between an Oracle DB (one
table - that too few of the columns) and SQL Server 2005. Can you please
point me to an article or web page where I can get some instructions. I have
followed the step in BOL and getting nowhere. Your help will be much
appreciated.
Thank you,
Yoga
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uw28PlTUHHA.1016@.TK2MSFTNGP04.phx.gbl...
> This is not supported for Oracle publishers - pls take a look at
> sp_addarticle (http://msdn2.microsoft.com/en-us/library/ms173857.aspx)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I know Andrew Carter was looking for people using the functionality to work
with a while back
(http://blogs.msdn.com/ascarter/archive/2004/08/05/209056.aspx) - might be
worth contacting him. There's also a webcast:
http://whitepapers.techrepublic.com.com/whitepaper.aspx?docid=149653. Apart
from that I think Sujoy Paul's Replication book has a section on setting it
up as far as I recall.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Subscribe to:
Posts (Atom)