Replication sample program

Hello i am new to SQL Replication.

I have a application in which Publisher node insert/update/delete in a SQL server data base on it's own node.

I have 3-4 different nodes which has SQL servers.

My reqirement is that when ever i insert/update/delete on the sql server in the publisher node, all the subscriber should insert/update/delete the same data in their respective data base.

so in short, all the publishers and subscribers should have same set of data. note that the subscribers are not doing any additional changes in their databases.

Also , at some point of time (when there is a fail over in the subscriber database), while restarting the subscriber database, i need to first synchronize all the data which were lost during down time with the publisher database and then onwards continue replication.
I will term this as startup synchronization step.

Can i use the SQL Transactional replication for this purpose? DO i need some other mechanism for startup synchronization step.

For example in the publisher i run this query,
Insert into customer(customerid,name,itemname) Values(20,'faith','speaker')

Once this query is executed on the publisher, i want to see that all the customer tables on the various subscribers node should have this record (20,'faith','speaker').

Is Replication is good approach or any thing else is suggested.

Can any body point to some sample tutorial or sample script to perform this kind of operation using Replication

Thanks a lot in adavance

Yes. Transactional replication is good enough to implement your scenario.

Whenever your data change happened on the publisher, subscriber will get the same change immediately (just default setting , continuous mode for both log reader and distribution agents). As for your "startup synchronization step", you can just resynchronize subscription, publisher present data will wrap up and load to subscriber through snapshot and distribution agent.

Here is transactional replication introduction in SQL Books On Line. It will also point you to implementation sample through UI and/or scripts.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/3ca82fb9-81e6-4c3c-94b3-b15f852b18bd.htm

Thanks

|||

Hello, Thanks for the info, it was really useful.

I have an extra complexity involved in my application. That is I have more than one set of publishers which are redundant to overcome the fail over situation.

When a particular publisher goes down , all the subscribers which were connected to the this failed publisher node, should now switch to a new publisher(before starting the replication , this subscriber should synchronise it self with the new publisher).

all the publishers independently updating there databases from a redundant lagacy application. that means i don't need to implement redundancy of publishers using the Replication.

I just should be able to synchronise the subscriber with the new publisher, assuming that the new publisher has the latest data (It is same as the .

There on, i should replicate the data from the new publisher to the subscriber.

Please proveide some details on how to perform this mixed approach.

Thanks in advance for your valuable feed back.

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(integrity violation) - Cannot Sync up emulator -HELP!

Can somebody help please - i keep getting this erro when trying to sycn
up my emulator to my Merged Replication DB.
"The row update or insert cannot be reapplied due to an integrity
violation. [,,,,,]"
Anybody know what this problem could be? I have recently dropped an
recreated a lot of my tables and changed some data types but did not
think it would effect me this badly.
I have also disabled and re-enabled my merge replication db many times.
I am sure all my connection settings are correct and the problem
appears to be deeper than that.
Recently i have changed the constraint on all of my tables to "Not to
enforce relationships on insert / update". It seems like this is what
the emulator is trying to do though?
Suggestions GREATLY appreciated!!!!
Thanks,
Steve
When you disable these constraints it means they will be fired twice.
The first time do to the originating action, the second time due to the
replicated command. This is probably not what you want unless you do not
have cascading updates and deletes on your subscriber.
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
"steroche" <steroche@.gmail.com> wrote in message
news:1125268893.110010.148790@.g44g2000cwa.googlegr oups.com...
> Can somebody help please - i keep getting this erro when trying to sycn
> up my emulator to my Merged Replication DB.
>
> "The row update or insert cannot be reapplied due to an integrity
> violation. [,,,,,]"
> Anybody know what this problem could be? I have recently dropped an
> recreated a lot of my tables and changed some data types but did not
> think it would effect me this badly.
> I have also disabled and re-enabled my merge replication db many times.
> I am sure all my connection settings are correct and the problem
> appears to be deeper than that.
> Recently i have changed the constraint on all of my tables to "Not to
> enforce relationships on insert / update". It seems like this is what
> the emulator is trying to do though?
> Suggestions GREATLY appreciated!!!!
> Thanks,
> Steve
>

Replication prerequisites

I need to make use of replication to merge two remote databases with one
central database and then update the two remote databases with the data on
the central database. What is the prerequisites for setting up replication? I
want to use C# for the merging. What do I need to do, because I've been
confusing myself now with security issues etc, etc...
Please help anyone?
This sounds like merge replication, but could equally well be transactional
with queued updating or transactional with immediate updating subscribers.
Have a look in BOL for the differences between these methods, or if you post
back with details of the following, someone will be able to help out:
(a) are the publisher and subscribers continuously connected?
(b) does the data overlap? If so, will you need any complicated conflict
resolution methods?
(c) will there be other subscribers coming onboard?
(d) do all the tables have PKs?
(e) do you want to replicate the execution of stored procs?
As for the programming aspect, please have a look here for some details to
start off with:
The SQLDMO scripts on here: http://www.replicationanswers.com/Scripts.asp
The ActiveX control scripts on here:
http://support.microsoft.com/default...b;EN-US;319649
http://support.microsoft.com/default...b;EN-US;319646
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanx alot for your response. I should be able to sort out the programming
side of this replication through some research that I have done. If you
wouldn't mind, I would like to go into more detail on what I want to do.
I have an application that our marketers will use to keep tract of their
leads and advertisers for our business directory website. Let's call the
marketers M1 and M2. ok, M1 and M2 will go out to different clients.
Obvisouly they won't go and see the same clients at the same time so they
need to share the information that M1 and M2 has gathered. So M1 has data and
M2 has a different set of data but they need to share it.
Both M1 and M2 are using laptops which isn't connected to our server or the
internet permanently. So M1 and M2 needs to come into the office and upload
their data and then receive new data that the marketer uploaded. If M1 came
into the office in the morning and uploaded his/hers data onto the server
using replication and M2 comes in, in the afternoon, M2 must be able to
receive the data that M1 uploaded that morning and then upload M2's data onto
the server so that M1 can receive M2's data the next time M1 uploads.
Hopefully that's explained clear enough to what I want to do. So, both M1
and M2 isn't always connected to each other or the internet. Yes, their will
be data overlaping since the server will have new information and the
marketer will also have new data which shouldn't be overwrittin but added.
All the tables have PK's. I don't have any stored procedures.
What will be the best option for this problem and how should I go about
setting everything up? I thought about having the each marketer's laptop as a
publisher and subscriber at the same time? is this possible?
Thanx again...
"Paul Ibison" wrote:

> This sounds like merge replication, but could equally well be transactional
> with queued updating or transactional with immediate updating subscribers.
> Have a look in BOL for the differences between these methods, or if you post
> back with details of the following, someone will be able to help out:
> (a) are the publisher and subscribers continuously connected?
> (b) does the data overlap? If so, will you need any complicated conflict
> resolution methods?
> (c) will there be other subscribers coming onboard?
> (d) do all the tables have PKs?
> (e) do you want to replicate the execution of stored procs?
> As for the programming aspect, please have a look here for some details to
> start off with:
> The SQLDMO scripts on here: http://www.replicationanswers.com/Scripts.asp
> The ActiveX control scripts on here:
> http://support.microsoft.com/default...b;EN-US;319649
> http://support.microsoft.com/default...b;EN-US;319646
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If M1 and M2 connect up to a central server, I'd set up the central server
as a publisher with M1 and M2 as subscribers. I'd suggest looking at queued
updating subscribers for this scenario, provided you are not dealing with
text/image columns (merge would be the other alternative).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication performing UPDATES as INSERTS and DELETES on the subscriber

I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.

Why does it do this?

How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?

Thanks

Cut paste from a different thread, see if this helps:

>> If you can limit the update to unique columns as singleton (each update affects one row) , SQL2000 will replicate it as update. Otherwise for multi-row updates to unique columns you may consider replication of ‘proc exec’ instead.

>> You should also look at the parameter @.schema_option for sp_addarticle. There’s an option that marks FKs at the subscriber as Not For Replication, this may be what you’re looking for. Enabling this should work if you don’t have any other fancy business-triggers at the subscriber that are breaking due to the delete/insert.

|||

What's happening is called a deferred update. See kb 238254 for more info. This is a by design behavior.

Is this causing any problems in your scenario? Are subscriber-side triggers breaking? Are you getting referential integrity errors?

Two common workarounds are to replicate the update via proc exec, or enable schema option 0x20000 in sp_addarticle, which does not enforce RI constraints if raised by replication agent.

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 performance degrade in unidirectional Direction and lock time out (Update are high t

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

looking for any experieced solution/suggestion.

Thanks in advance.

Hi adrshen,

Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.

With merge replication, it would affect the performance as it uses triggers to capture the changes.

regards

Jag

|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.

Replication performance degrade in unidirectional Direction (Update are high than inserts)

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

looking for any experieced solution/suggestion.

Thanks in advance.

Hi adrshen,

Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.

With merge replication, it would affect the performance as it uses triggers to capture the changes.

regards

Jag

|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.

Replication or Update via Trigger ?

Hi SQL Gurus,
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>

Replication of DB2 data on SQL Server

What tools are available to update SQL Server application tables from a DB2 database?
The DB2 database is on iSeries (AS/400).
Thanks in advance.you can probably create a linked server from SQL Server to DB2 and then use select statements as usual. search for db2 on the link below.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx|||It really depends on the version of SQL Server you are running. Linked servers as mentioned is one option, another option is using DTS or SSIS to transfer data regulary. Unfortunately it appears that you have to pull the data from the SQL Server, I don't think the AS/400 will be able to push data to the SQL Server.|||You could write java programs to push data either way using an JDBC connectivity.

We can also look @. tools from IBM to push data to SQL Server using DB2 Information Integrator V8, IBM DB2 Replication & DataPropagator.

Replication ntext only by UPDATE

Dear group,
in the SQL-Server documentation it says:
"Merge replication supports the replication of text, ntext, and image
columns only if they have been updated explicitly by an UPDATE statement
because it causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers."
Is this also valid for INSERT-Statements?
In our case we have a table where only INSERTS of new rows occur (incl.1
ntext column). Can we replicate this by merge replication without problems?
Do you have a URL to 'prove' this? ;-)
thanks a lot,
regards,
ERic
I understand that you have to have a trigger that fires on insert, to update
the text column in the row again.
I haven't tried this myself.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Eric Voigt" <eric@.ericvoigt.de> wrote in message
news:2ubq8aF28taboU1@.uni-berlin.de...
> Dear group,
> in the SQL-Server documentation it says:
> "Merge replication supports the replication of text, ntext, and image
> columns only if they have been updated explicitly by an UPDATE statement
> because it causes a trigger to fire that updates meta data ensuring that
the
> transaction gets propagated to other Subscribers."
> Is this also valid for INSERT-Statements?
> In our case we have a table where only INSERTS of new rows occur (incl.1
> ntext column). Can we replicate this by merge replication without
problems?
> Do you have a URL to 'prove' this? ;-)
> thanks a lot,
> regards,
> ERic
>
>

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)