Replication Triggers on replicated tables.

Howdy all. I set up Immediate Updating replication on AdventureWorks on 2005
Person.Address and AddressType tables. When this type of replication is
created, a replication trigger is created on the Publisher. However, this
replication trigger fires off the other (User) trigger on the table when a
row is updated, and they continue to fire each other off. I caught all this
in Profiler so Im sure this is whats happening. Anyways, the following
message is then displayed:
Maximum Stored Proc, function, trigger, or view nesting level exceeded
(limit 32).
Here are the triggers:
ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on [Person].[Address]
for update not for replication as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [Person].[Address] set msrepl_tran_version = newid() from
[Person].[Address], inserted
where [Person].[Address].[AddressID] = inserted.[AddressID]
ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Address]
SET [Person].[Address].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
END;
Someone must have encountered this before and have a workaround?
TIA, ChrisR
use set trigger order to have replication fire at the end or make your
triggers not for replication.
Can we see the table schema and triggers?
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
"ChrisR" <ChrisR@.foo.com> wrote in message
news:uN6YxlRMHHA.3556@.TK2MSFTNGP03.phx.gbl...
> Howdy all. I set up Immediate Updating replication on AdventureWorks on
> 2005 Person.Address and AddressType tables. When this type of replication
> is created, a replication trigger is created on the Publisher. However,
> this replication trigger fires off the other (User) trigger on the table
> when a row is updated, and they continue to fire each other off. I caught
> all this in Profiler so Im sure this is whats happening. Anyways, the
> following message is then displayed:
>
> Maximum Stored Proc, function, trigger, or view nesting level exceeded
> (limit 32).
>
> Here are the triggers:
>
> ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on
> [Person].[Address] for update not for replication as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
>
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [Person].[Address] set msrepl_tran_version = newid() from
> [Person].[Address], inserted
> where [Person].[Address].[AddressID] = inserted.[AddressID]
>
>
> ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
> AFTER UPDATE NOT FOR REPLICATION AS
> BEGIN
> SET NOCOUNT ON;
>
> UPDATE [Person].[Address]
> SET [Person].[Address].[ModifiedDate] = GETDATE()
> FROM inserted
> WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
> END;
>
> Someone must have encountered this before and have a workaround?
>
>
> TIA, ChrisR
>
>
>
>

replication timeout

I have 3 production machines which are replicated to a dr site, all
machines are using a central distributor machine. I have created a
separate distributor database for each system.
My problem is that one of my systems will not replicate I am doing
snapshot and transactional, it just keeps timing out I have put sql
profiler on the dr box and can see that the data gets to the box but
it just times out. All boxes publisher, distributor, subscriber can
see each other so why is it timing out?
what is the exact error message you are getting?
"stephen" <twolf@.icon.co.za> wrote in message
news:75c81dee.0404170439.22bbd18d@.posting.google.c om...
> I have 3 production machines which are replicated to a dr site, all
> machines are using a central distributor machine. I have created a
> separate distributor database for each system.
> My problem is that one of my systems will not replicate I am doing
> snapshot and transactional, it just keeps timing out I have put sql
> profiler on the dr box and can see that the data gets to the box but
> it just times out. All boxes publisher, distributor, subscriber can
> see each other so why is it timing out?
|||Have you looked into creating a different snapshot profile?
If you go into Publisher/Distributor Properties, Agent Profiles, and
then Snapshot Tab, If you look at the existing "Default profile" the
Query time-out is set to 300; you might want to increase that number
to maybe 1000 or so.
Here is something that I have found interesting though that you might
want to know ahead of time so you DON'T CANCEL the replication
process:
Depending on how high you increase this number to, there is always the
potential of getting a somewhat bizare error message while the
replication is running stating that no response has been received from
the snapshot agent, and the Replication monitor will display the red X
symbols (like if it had failed). However, the process is still alive
and well (you can check it under jobs to see if it is still in fact
running), so once it goes past the table that may be causing it to
time-out, the Replication monitor will refresh and the Xs will be
removed.
Try this on a test environment first as every environment reacts
diferently, and always remember to keep a current backup just in case.
Hope this helps.
Jose
twolf@.icon.co.za (stephen) wrote in message news:<75c81dee.0404170439.22bbd18d@.posting.google. com>...
> I have 3 production machines which are replicated to a dr site, all
> machines are using a central distributor machine. I have created a
> separate distributor database for each system.
> My problem is that one of my systems will not replicate I am doing
> snapshot and transactional, it just keeps timing out I have put sql
> profiler on the dr box and can see that the data gets to the box but
> it just times out. All boxes publisher, distributor, subscriber can
> see each other so why is it timing out?

Replication target table shrinks to zero

I want to replicate a database to a subscriber that will be used as a read
only copy. The data has to be replicated as close to instantly as possible.
To do this I set up a database export of objects and data to populate the
subscriber, then I set up transactional replication. To verify that
replication is working successfully, I count the rows in each table, there
are 3 tables in total. For one of the tables, the replication completes but
almost immediately afterward, the table starts to shrink, and after several
hours the record count is zero. This isn't happening to the other two
tables, and I can't figure out why.

If you have no idea what might be causing this, perhaps you can suggest
some places to start looking. This is Win2k SP4 with SQL 2000 SP3.

Thanks much."someguy" <nospam@.thanks.com> wrote in message
news:Xns945451BFDB728nospamthankscom@.207.35.177.13 5...
> I want to replicate a database to a subscriber that will be used as a read
> only copy. The data has to be replicated as close to instantly as
possible.
> To do this I set up a database export of objects and data to populate the
> subscriber, then I set up transactional replication. To verify that
> replication is working successfully, I count the rows in each table, there
> are 3 tables in total. For one of the tables, the replication completes
but
> almost immediately afterward, the table starts to shrink, and after
several
> hours the record count is zero. This isn't happening to the other two
> tables, and I can't figure out why.

Sounds like there's a trigger or another user operating on that table.

Replication itself should not cause a table to shrink (unless of course the
original source table is shrinking.)

> If you have no idea what might be causing this, perhaps you can suggest
> some places to start looking. This is Win2k SP4 with SQL 2000 SP3.
> Thanks much.

Replication Report

Hi,
Is there anyway to provide weekly status report of the replication. Like --
1. Which tables are replicated.
2. When replication is not possible with reason.
Thanks in advance.
This shouldn't be too difficult to produce. Sysarticles and sysmergearticles
on the publisher will give the first part. The distribution database
(MSrepl_errors, MSdistribution_history, MSsnapshot_history etc) will give
the latter. Reporting Services for the report then you're done.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You'll probably want to bump your history retention up to something more
than a week - by default it hangs around for 3 days. To do this, right click
on Replication Monitor, select Distributor Properties, and then click on the
properties button. Change History Retention here.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etnRVW5gFHA.2372@.TK2MSFTNGP14.phx.gbl...
> This shouldn't be too difficult to produce. Sysarticles and
sysmergearticles
> on the publisher will give the first part. The distribution database
> (MSrepl_errors, MSdistribution_history, MSsnapshot_history etc) will give
> the latter. Reporting Services for the report then you're done.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql

Replication removed but server says otherwise

I've got a big and urgent problem...
I need to alter some tables in a database. I had those
tables replicated. I removed the replication to make
thoses changes.
All the objects created by the replication are still
there, and the fields added in the tables also. When I
want to change a clustered index, It says :
- Unable to delete index 'PK_rel_cfp_sar'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot alter the table 'rel_cfp_sar' because it is
being published for replication.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
drop constraint. See previous errors.
The problem is, there is no more replication active!!
I've even removed the server as a distributor. Rebooted
the server, nothing works!!
What can I do? HELP ME!!!
Thanks in advance, Hussein.
hussein@.inforoutefpt.org
Hussein,
Running sp_removedbreplication can be used to remove all traces of
replication in the subscriber database, but obviously must only be done if
this database is not also configured as a publisher.
If sp_removedbreplication can't be used, there is a stored procedure to do
this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Alternatively, setting replinfo to 0 in sysobjects for the particular table
should do it.
HTH,
Paul Ibison
|||Thanks Paul writing me back, but it didn't worked.
The problem is at the publisher, I've disabled the server to be a Publisher
or Distributor. So it's very weird. The fields added in the tables for the
replication are still there, and I can't remove them. I can't change the
indexes either.
I've tried the two SP who told me, it says "The command(s) completed
successfully." and "1 row(s) affected", but still can't do anything...
Do you have another idea?
Thanks, Hussein
"Paul Ibison" wrote:

> Hussein,
> Running sp_removedbreplication can be used to remove all traces of
> replication in the subscriber database, but obviously must only be done if
> this database is not also configured as a publisher.
> If sp_removedbreplication can't be used, there is a stored procedure to do
> this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Alternatively, setting replinfo to 0 in sysobjects for the particular table
> should do it.
> HTH,
> Paul Ibison
>
>
|||This is very strange. Can you query replinfo in sysobjects for the problem
table and tell me the value.
TIA,
Paul Ibison
|||The value is set to 1 for the table.
I also have many strored procedures who have a value of
512.
Hussein

>--Original Message--
>This is very strange. Can you query replinfo in
sysobjects for the problem
>table and tell me the value.
>TIA,
>Paul Ibison
>
>.
>
|||Hussein - OK set it to zero for the problem table and then you should be
able to change the table schema.
HTH,
Paul Ibison
"Hussein Abd-Rabbo" <hussein@.inforoutefpt.org> wrote in message
news:137b01c48c2f$e02c2ec0$a601280a@.phx.gbl...[vbcol=seagreen]
> The value is set to 1 for the table.
> I also have many strored procedures who have a value of
> 512.
> Hussein
> sysobjects for the problem
|||also try to run
sp_msforeachtable 'sp_MSunmarkreplinfo ''?'''
in your publication database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:udaTGdBjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> This is very strange. Can you query replinfo in sysobjects for the problem
> table and tell me the value.
> TIA,
> Paul Ibison
>

Replication question - updatable subscription?

Hi all,

I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.

My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)

I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.

Any help would be much appreciated!

Thanks,
FarazWhile you are flirting at the edge of what works well, at least what you've described is possible. I'm pretty sure that Microsoft does not recommend it, I'm not sure if they support it, but I have done it under very limited circumstances for very specific purposes.

This is not for the faint of heart. You need to think things out carefully. It might pay you to find someone experienced in replication that you can contract to help set things up and shake out the problems.

-PatP|||Thanks for the reply. We won't be using transactional replication. In fact, from my understanding, one of the goals of replication is ensuring that data consistency is maintained between publishers and subscribers; in our case, we don't actually want the data to be consistent. Hence, we'll go forth with a less elegant, but far simpler solution.

Thanks again!
-Farazsql

Replication problem... Please Help!

Hi,
I have the following problem with a Transactional Replication between two SQL Server 2000:
- One Database is replicated from one server to the other and it works well.
- I have cancelled the distribution, the publication and the subscription in the right order. So technically, there should be no trace left of the replication.
- Then I try to run a script to alter the type of a column and I get the following error message : "Cannot alter column because it is 'REPLICATED'."
- When I look at the colstat value (in syscolumns) for this column, it has a value greater than 4096 which means that it is effectively marked for replication.
Why is that happenning?
What can I do the remove the replication traces, and then be able to change the column type?
Thanks for all your help!!!
Jenny
Hi,
When I say I "cancelled" the replication, I mean that I have deleted everything related to the replication. So in my opinion there should not be any trace left of
the replication in my tables info. Is there a way to remove the replication info on a particular table? I really don't know what to do!
Thanks,
Jenny
"ChrisR" wrote:

> When you say you cancelled distribution, do you mean you
> got rid of the distribution db or turned off the agent? If
> you mean turned it off, Im not sure where, but this info
> is still in one of the system tables probably in the
> distribution db. Ive had to mess with these before for the
> same thing. Id figure it out right now for you but Im a
> bit pressed for time. Id be willing to bank on it being in
> the MSArticles table though. Let us know if thats not it
> and if you cant figure it out.
>
>
> Replication between two SQL Server 2000:
> and it works well.
> the subscription in the right order. So technically, there
> should be no trace left of the replication.
> column and I get the following error message : "Cannot
> alter column because it is 'REPLICATED'."
> this column, it has a value greater than 4096 which means
> that it is effectively marked for replication.
> be able to change the column type?
>
|||Assuming that you actually went and ran the Disable
Publishing and Distribution Wizard so you no longer have a
Distribution database, I wouldnt know what to tell you.
Chane the column value that you were talking about and see
if that help.

>--Original Message--
>Hi,
>When I say I "cancelled" the replication, I mean that I
have deleted everything related to the replication. So in
my opinion there should not be any trace left of
> the replication in my tables info. Is there a way to
remove the replication info on a particular table? I
really don't know what to do![vbcol=seagreen]
>Thanks,
>Jenny
>"ChrisR" wrote:
you[vbcol=seagreen]
If[vbcol=seagreen]
info[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
it[vbcol=seagreen]
other[vbcol=seagreen]
and[vbcol=seagreen]
there[vbcol=seagreen]
means[vbcol=seagreen]
then
>.
>
|||Use this stored Procedure sp_removedbreplication
This should solve your problem. (If you have removed using one of the wizard)
Rakesh
"ChrisR" wrote:

> Assuming that you actually went and ran the Disable
> Publishing and Distribution Wizard so you no longer have a
> Distribution database, I wouldnt know what to tell you.
> Chane the column value that you were talking about and see
> if that help.
>
> have deleted everything related to the replication. So in
> my opinion there should not be any trace left of
> remove the replication info on a particular table? I
> really don't know what to do!
> you
> If
> info
> the
> in
> it
> other
> and
> there
> means
> then
>
|||Hi Jenny,
Some of your columns are still marked for replication - see
http://support.microsoft.com/default...b;en-us;811899
for a workaround.
Cheers,
Mark
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!
Can you post the schema of the problem table here? Also is this
transactional replication.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegr oups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>
|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilarysql

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
--
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:
> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication Problem

Hi

I have two location and 1 centralised server.
while doing transactional replication first the data from the 1st server got replicated in the central server , while doing with the second location , the data in the central server got overwrited and now the replication from the first location is not happening.Only the replication from the second location is happening.

Please tell me how to do the replication with two location and a centralised server.

Regards
Aneesh

If you want central server always download changes to two clients, you can just set transactional replication on central server and choose two clients as subscribers. But if you have changes on client server, which you want to upload onto central servers as well, you can set merge replication on central server and two clients as subscribers. As for merge replication, if there are conflicts from central server and clients, you need to set conflicts resolver to make sure the data won as what you expected.

Hope it will help.

Thanks

Yunjing

|||

Hi Yunjing,

thanks for your reply.

I dont want central server always download changes to two clients, instead i have to upload the date from the location1 and location2 to the central server. I mean publishers in location1 and location2 and i have a subsciber in central server.

I want to replicate the data from the location1 and location2 servers to the central server.

Replication performance Problem with SQL Server -> Oracle9i

We are replicating from SQL server 2000 DB to Oracle 9i database using a 2 Mbps line. When there is a mass update on our DB, the same is replicated to the Oracle 9i side, but apparently the SQl Server sends as many update/delete requests to Oracle as the
number of rows affected on SQL side (17000). The update therefore takes enormous time on the Oracle side and the whole system on Oracle side freezes for hours till all the updates happen and then the single row updates take place in about 30 seconds time.
when we sneak into the execution on the Oracle side, it seems it commits for each of the rows it updates. we use OLEDB provider for Oracle. does this have any link with the Auto-commit settings or due to some other?..
This is normal. Replication breaks down set based operations to singletons
to ensure that data is consistent on both ends - the Publisher and
Subscriber.
You will get better performance using the Microsoft ODBC driver for Oracle
and the Microsoft OLE DB provider for Oracle
If you are using the Oracle OLE DB provider you should be selecting the
Allow InProcess option of your linked server.
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:BEC3FC8A-34CB-4760-B145-08C94801FD39@.microsoft.com...
> We are replicating from SQL server 2000 DB to Oracle 9i database using a 2
Mbps line. When there is a mass update on our DB, the same is replicated to
the Oracle 9i side, but apparently the SQl Server sends as many
update/delete requests to Oracle as the number of rows affected on SQL side
(17000). The update therefore takes enormous time on the Oracle side and the
whole system on Oracle side freezes for hours till all the updates happen
and then the single row updates take place in about 30 seconds time. when we
sneak into the execution on the Oracle side, it seems it commits for each of
the rows it updates. we use OLEDB provider for Oracle. does this have any
link with the Auto-commit settings or due to some other?..

replication over a wan

Howdy kids. We are moving our servers to a new location here shortly. One of
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
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
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>

Replication or log shipping

I have a SQL server with 90 databases (with avg of 30
tables in each) , each needs to be replicated (Transaction
immediate)
The Distrubution databse is on this server. I have been
having problems with some of the database failing to
replicate, and I was wondering if Log shipping between
linked servers would be a better option.
Log shipping replicates the entire database. Replication will allow you to
replicate a subset of the database, allow you to subset the data, but will
not replicate system objects.
While using log shipping your users will have to go offline while the logs
are being applied, and the database must be treated as read only. You cannot
place custom indexes on the subscriber or modify the schema there.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Allan" <allen.penston@.sitel.co.uk> wrote in message
news:02dc01c4dc46$908e68d0$a601280a@.phx.gbl...
>I have a SQL server with 90 databases (with avg of 30
> tables in each) , each needs to be replicated (Transaction
> immediate)
> The Distrubution databse is on this server. I have been
> having problems with some of the database failing to
> replicate, and I was wondering if Log shipping between
> linked servers would be a better option.
>

Replication of check constraints

I have tables that are replicated using transactional and merge replication.As a result I am unable to use automatic identity management as transactional replication doesn’t seem to understand it.

Therefore I have implemented a version of the automatic mechanisms that seems to work in a hybrid environment.It is based on a central table that holds the maximum identity for each table that has been issued to date.Valid identity ranges are issued to each publisher and subscriber as needed in a similar way to the automatic mechanisms and tables are reseeded as needed.

I want to enforce the ranges in a similar way to the automatic mechanism using a check constraint similar to this:

alter table [dbo].[test1] with NOCHECK add CONSTRAINT repl_identity_range_48DF13ED_D503_4F5C_AED9_4E504D03E752 check NOT FOR REPLICATION (([id] > 10001 and [id] <= 20001) or ([id] > 50001 and [id] <= 70001))

This works OK on a client subscriber, but if the change is made on the publisher, then the alter statement itself is replicated out to all clients – which is not what is wanted.I have traced the automatic mechanisms using profiler and they issue an alter statement as above – following dropping of the constraint – but the check constraint isn’t replicated. I can't see how this is achieved.

How do I stop the check constraint being replicated?

The article property schema option can be set to stop replicating check constraints, but this seems to have no effect.If the publication property replicate_ddl is set to 0 then I do see the behaviour that I want.However, I do need to be able to replicate most schema changes due to upgrades etc – so this doesn’t look like a viable option – except possibly for the transactional publication.

Any help would be much appreciated

Thanks

aero1

I think I have found a way forward. I had been considering the publication setup as pretty static, but on subsequent thoughts I realised that there is no reason why I can't change the replicate_ddl property as and when I need to

I have tested out the following and it seems to work (Other unrelated schema changes get synched correctly)

begin tran

exec sp_changemergepublication to set replicate_ddl to 0

make the check constraint change

exec sp_changemergepublication to set replicate_ddl back to 1

commit

Is there any problem with this approach?

|||This is the supported way of not wanting to replicate DDL-type statements (enable/disable publication property replicate_ddl). Please let us know if you run into problems.|||

Thanks for the info

aero1

replication locking (sql2000 / transactional)

Currently, we have a table replicated from one database to another on the
same server.
If I update 127 records based on the primary key, the subscription process
locks all the records until all the 127 commands have been executed.
Seamingly regardless of how they were updated.
eg.
CREATE TABLE Orgs ( OrganisationID INT PRIMARY KEY CLUSTERED ,
TimeLastUpdated DATETIME NOT NULL )
DECLARE @.SelectedOrgs ( OrganisationID INT PRIMARY KEY CLUSTERED )
/* ... insert 127 organisations to update here ... */
-- Process Type 1
UPDATE Orgs SET TimeLastUpdated = GETDATE()
FROM Orgs INNER JOIN @.SelectedOrgs SO ON Orgs.OrganisationID =
SO.OrganisationID
-- Process Type 2
WHILE ( blah )
BEGIN
-- one at a time in asc order
SELECT @.OrgID = OrganisationID FROM @.SelectedOrgs WHERE ( blah )
UPDATE Orgs SET TimeLastUpdated = GETDATE() WHERE OrganisationID =
@.OrgID
END
Using the default isolation level and no explicit transactions the table in
the subscriber database was locked until the commands had all been executed.
What gives - both types lock the whole table on the subscriber.
Surely it's not meant to use the same locks as were applied when the data
was updated?
Is there any to stop it locking the whole thing while it processes the
commands?
Ideally we'd upgrade to 2k5 and use row versioning... but that wont happen
for a while.

I need to implement a similar thing on a much larger database which needs to
be up 24/7 and can't be locked for long periods of time, mean while on the
publisher, 5% of the data may be changed in one go - but if we can't get it
to use optimistic locking on the subscriber we can't use replication.
Rebecca,
an update statement always has an implicit transaction. if you use
lumigent's Logexplorer you can see how this works. Your update statement will
be logged to the transaction log as :
BEGIN TRAN
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 1
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 2
..
..
..
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 127
COMMIT TRAN
This is read by the log reader agent and converted to stored procedure
calls, but also with the transaction (AFAIR same XACT in msrepl_commands) .
The distribution agent applies it in the same way.
The table itself shouldn't be locked (lock escalation shouldn't occur on
such a low # rows), and inserting a record at the same time as the
distribution agent runs should prove this to be true.
Anyway, to answer your question more directly, -MaxCmdsInTran should allow
you to break the ACID properties and commit separate parts of the update as
separate transactions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication Limit?

Is there a limit to how many databases can be replicated in a MSDE 2000
environment? We have several servers with several replicated MSDE 2000
databases. It seems that we are hitting a limit because we cannot replicate
any more databases. Any ideas?
What kind of problems are you having?
Are you not able to setup anymore databases for replication or does the
replication just fail.
How many databases are you replicating on each server?
Have you off-loaded the distributor database to another server or does each
server act as its own distributor?
Jim
"VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
news:euv7a7jHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there a limit to how many databases can be replicated in a MSDE 2000
> environment? We have several servers with several replicated MSDE 2000
> databases. It seems that we are hitting a limit because we cannot
replicate
> any more databases. Any ideas?
>

Replication limit?

Is there a limit to how many databases can be replicated in a MSDE 2000
environment? We have several servers with several replicated MSDE 2000
databases. It seems that we are hitting a limit because we cannot replicate
any more databases. Any ideas?
there is a connection limit. MSDE is throttled to 8 simultaneous
connections. This might be what you are bumping into.
"VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
news:es6cv7jHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> Is there a limit to how many databases can be replicated in a MSDE 2000
> environment? We have several servers with several replicated MSDE 2000
> databases. It seems that we are hitting a limit because we cannot
replicate
> any more databases. Any ideas?
>
|||Any way around that?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23GwkWlkHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> there is a connection limit. MSDE is throttled to 8 simultaneous
> connections. This might be what you are bumping into.
> "VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
> news:es6cv7jHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> replicate
>
|||As Hilary mentionned, the limit is 8 simultaneous connections, and that
means CONCURRENT connections. So, by implementing schedules so that the
connections do not occur at the same time, I suppose you might work your way
out.
Raj Moloye

Replication issues

Hi All ,
I have some concerns with replication
1. if the replication fails 1/2 way , does that mean some data is being
replicated across alrdy or it'll do a roll-back to ensure either it's a
complete replication or no replication ?
2. previously i have already set up the application but in EM , i went to
Replication --> Publications and delete the publication instead of going to
the Replication Monitor --> Publishers --> to delete the publication and
found that i couldn't delete the publication in Replication Monitor -->
Publishers any more
which sp can i used to delete ?
3. i got the error from replication "the subscription has been marked and
must be reintialized . No Sync subscriptions will need to be dropped and
recreated?
- why this error occured , i have set for replication for 72 hours
Kindly advise
tks & rdgs1) this depends. If there is a problem deploying the snapshot, it will be
reapplied, but not rolled back. So if you don't reapply the snapshot after
failure your subscriber database will have some of the replicated objects
and replication metadata in place. If the merge or distribution agent fails
midway it depends on the transactional boundary. Transactional replication
replicates transactions transactionally. So if a transaction with 200
commands (for instance a transaction which updates 200 rows) fails, it will
be completely rolled back. If you are replicating a transaction which causes
a cascading update/delete on the publisher and you have enforced this
cascade for replication, and this transaction fails, it will be rolled back.
For merge replication, if a child record is applied before the corresponding
parent record and the parent is not part of the batch, the agent will fail
and this row will be rolled back, but the successfully applied rows in the
batch will remain. Next time you run the merge agent, this transaction
should succeed if the parent record comes over in the next batch. Merge
replication does not guarantee durability.
2) try sp_droppublication 'publicationname' in the publication database.
3) You should set your history retention to the same value of your
transaction retention (right click on replication monitor, select
distribution properties, and click on the properties button, and set it
there).
--
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
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:6A1D3EAE-EE3A-4824-8A97-ACFF7521CEF5@.microsoft.com...
> Hi All ,
> I have some concerns with replication
> 1. if the replication fails 1/2 way , does that mean some data is being
> replicated across alrdy or it'll do a roll-back to ensure either it's a
> complete replication or no replication ?
> 2. previously i have already set up the application but in EM , i went to
> Replication --> Publications and delete the publication instead of going
to
> the Replication Monitor --> Publishers --> to delete the publication and
> found that i couldn't delete the publication in Replication Monitor -->
> Publishers any more
> which sp can i used to delete ?
> 3. i got the error from replication "the subscription has been marked and
> must be reintialized . No Sync subscriptions will need to be dropped and
> recreated?
> - why this error occured , i have set for replication for 72 hours
>
> Kindly advise
> tks & rdgs|||Hi,
I have tried sp_droppublication on the publication db but there's no such
publication to be dropped
when i tried deleting from the replication monitor i got the following
error :
SQL server Enterprise Manager could not retrieve information about
publication 'publication1'
Error 21776 the name 'publication1' was not found in the TransPublication
collection. if the name is a qualified name use [] to seperate various parts
of the name and try again
how could i resove this ?
kindly advise
tks & rdgs
"Hilary Cotter" wrote:
> 1) this depends. If there is a problem deploying the snapshot, it will be
> reapplied, but not rolled back. So if you don't reapply the snapshot after
> failure your subscriber database will have some of the replicated objects
> and replication metadata in place. If the merge or distribution agent fails
> midway it depends on the transactional boundary. Transactional replication
> replicates transactions transactionally. So if a transaction with 200
> commands (for instance a transaction which updates 200 rows) fails, it will
> be completely rolled back. If you are replicating a transaction which causes
> a cascading update/delete on the publisher and you have enforced this
> cascade for replication, and this transaction fails, it will be rolled back.
> For merge replication, if a child record is applied before the corresponding
> parent record and the parent is not part of the batch, the agent will fail
> and this row will be rolled back, but the successfully applied rows in the
> batch will remain. Next time you run the merge agent, this transaction
> should succeed if the parent record comes over in the next batch. Merge
> replication does not guarantee durability.
> 2) try sp_droppublication 'publicationname' in the publication database.
> 3) You should set your history retention to the same value of your
> transaction retention (right click on replication monitor, select
> distribution properties, and click on the properties button, and set it
> there).
> --
> 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
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:6A1D3EAE-EE3A-4824-8A97-ACFF7521CEF5@.microsoft.com...
> > Hi All ,
> >
> > I have some concerns with replication
> >
> > 1. if the replication fails 1/2 way , does that mean some data is being
> > replicated across alrdy or it'll do a roll-back to ensure either it's a
> > complete replication or no replication ?
> >
> > 2. previously i have already set up the application but in EM , i went to
> > Replication --> Publications and delete the publication instead of going
> to
> > the Replication Monitor --> Publishers --> to delete the publication and
> > found that i couldn't delete the publication in Replication Monitor -->
> > Publishers any more
> > which sp can i used to delete ?
> >
> > 3. i got the error from replication "the subscription has been marked and
> > must be reintialized . No Sync subscriptions will need to be dropped and
> > recreated?
> > - why this error occured , i have set for replication for 72 hours
> >
> >
> > Kindly advise
> > tks & rdgs
>
>