That means, I cant using Enterprice-Manager.
I dont want using SQLDMO because of redistribution-problems.
Using T-SQL, it is quite difficult to implement Merge-Replication.
Are the thrid-pary-products, ActiveX-Controls, Libraries, assembly,
etc. that manage SQL-Server-Replication very well?
What do you think about writing your own merge-replication tool? I
just wrote a prototyp, and it seems to be very easy.
pro:
- no limitations on changing data strukturs while publishing tables
!!!
- using a connection in ONE direction (no re-connect from subscriber
to distritubor necessary, ideal for replication into internet !! )
- high level of transparency, you can debug each step, you can log
each SQL-Statement
- if you want to use a single-sided replication (like transactional
replication) the subscriber database need not implement anything (no
table, no trigger, nothing!!)
- few overhead: just adding 3 triggers per table (like
MS-merge-replication does) and 2 or 3 system-tables
- very simple code to switch on replication (just adding the 3
triggers to each table, independent on data strukture)
contra:
- replication-process itself is not tested, each possible event have
to be implemented and well tested
I would be happy if someone would like to diskuss this with me, or if
someone has just written a tool like this, or have another good idea.
Andreas Lauffer, Germany
answers in line.
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
"Andreas Lauffer" <al@.lauffers.de> wrote in message
news:6e97e7d4.0502210327.44c6f52f@.posting.google.c om...
> I have to implement a simple Merge-Replication, using the MSDE.
> That means, I cant using Enterprice-Manager.
> I dont want using SQLDMO because of redistribution-problems.
SQLDMO uses stored procedures under the covers, so I would advise you to use
the replication stored procedures - or possibly the replication activeX
controls.
> Using T-SQL, it is quite difficult to implement Merge-Replication.
> Are the thrid-pary-products, ActiveX-Controls, Libraries, assembly,
> etc. that manage SQL-Server-Replication very well?
The only product out there that I know of which handles bi-directional
replication is DataMirror - and in my experience if you are having problems
with merge replication you will also have problems with Data Mirror.
> What do you think about writing your own merge-replication tool? I
> just wrote a prototyp, and it seems to be very easy.
>
It is possible, however merge replication has been designed to handle
conflicts and has 6 or more years of research, customer experience behind
it.
> pro:
> - no limitations on changing data strukturs while publishing tables
> !!!
bi-directional transactional replication does not require changes to data
structures either.
> - using a connection in ONE direction (no re-connect from subscriber
> to distritubor necessary, ideal for replication into internet !! )
replication only requires a connection inbound or outbound, not both ways as
well.
> - high level of transparency, you can debug each step, you can log
> each SQL-Statement
> - if you want to use a single-sided replication (like transactional
> replication) the subscriber database need not implement anything (no
> table, no trigger, nothing!!)
Transactional replication has an option to replicate using pure SQL
statements as well - however there is a severe performance penalty to pay
for doing this on larger tables.
> - few overhead: just adding 3 triggers per table (like
> MS-merge-replication does) and 2 or 3 system-tables
> - very simple code to switch on replication (just adding the 3
> triggers to each table, independent on data strukture)
>
Watch out for triggers - there is a performance impact for doing this,
especially if you are replicating over a network.
> contra:
> - replication-process itself is not tested, each possible event have
> to be implemented and well tested
> I would be happy if someone would like to diskuss this with me, or if
> someone has just written a tool like this, or have another good idea.
> Andreas Lauffer, Germany
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<OHWyUIBGFHA.3824@.TK2MSFTNGP10.phx.gbl>...
> It is possible, however merge replication has been designed to handle
> conflicts and has 6 or more years of research, customer experience behind
> it.
OK, this is the most substantial argument agains my idea.
>
> bi-directional transactional replication does not require changes to data
> structures either.
I mentioned the problem, that you cannot change any application data
structure if there are publications and articles on your database.
>
> replication only requires a connection inbound or outbound, not both ways as
> well.
Please look in the thread
"Push-Replication with Internet-Connection" within this group.
http://groups.google.de/groups?hl=de...%26start%3D125
I tried to discuss this topic there. A subscriber definitly wants to
connect to the distributor. This could be a huge problem behind a
firewall.
> Transactional replication has an option to replicate using pure SQL
> statements as well - however there is a severe performance penalty to pay
Could a transactional replication replace a merge replication?
Another point is the fact, that transactional replication cannot be
used with MSDE.
> Watch out for triggers - there is a performance impact for doing this,
> especially if you are replicating over a network.
MS Merge Replication also implement 3 triggers per table for
replication.
Andreas Lauffer, Germany
|||Answer's inline.
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
"Andreas Lauffer" <al@.lauffers.de> wrote in message
news:6e97e7d4.0502220205.3a6c23e8@.posting.google.c om...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:<OHWyUIBGFHA.3824@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
behind
> OK, this is the most substantial argument agains my idea.
[vbcol=seagreen]
data
> I mentioned the problem, that you cannot change any application data
> structure if there are publications and articles on your database.
Let's back up here. Merge, Queued and Immediate make schema changes to your
tables - bi-directional transactional does not.
[vbcol=seagreen]
ways as
> Please look in the thread
> "Push-Replication with Internet-Connection" within this group.
>
http://groups.google.de/groups?hl=de...%26start%3D125
> I tried to discuss this topic there. A subscriber definitly wants to
> connect to the distributor. This could be a huge problem behind a
> firewall.
>
Are you sure this is the correct thread - this is a thread you and I were
involved in some time ago. To continue that thread, if the publisher and the
distributor are on the same machine you don't have a problem. When the
publisher and subscriber are on different machines you will need to map a
drive to the distributor to push the snapshot. Locally no problem, if it a
pull it can be problematic, unless the subscriber can map a drive to the
snapshot share on the publisher or subscriber.
If there is a firewall, firewalls can be configured to allow inbound or to
allow outbound or both. Most security admins will lock down ports and only
open certain ports for inbound, outbound, or both. This means that if port
1433 is open for outbound traffic a subscriber accross the internet will be
unable to create an inbound connection to the publisher/distributor. If port
1433 is open for inbound traffic, then the subscriber can. Communication
after the session is established will be bi-directional. The blocking is
only done on the initiating session.
So, for a pull subscriber if ports 1433 and 21 are open for inbound you will
not have a problem with a pull.
[vbcol=seagreen]
pay
> Could a transactional replication replace a merge replication?
> Another point is the fact, that transactional replication cannot be
> used with MSDE.
It could in some limited circumstances. 1) few nodes, 2) well connected, 3)
perfectly partitioned to minimize conflicts or transactions only occur on
one node at a time.
>
> MS Merge Replication also implement 3 triggers per table for
> replication.
Yes, this is true - merge replication does not offer the performance of
transactional replication for this and other reasons. However, for
frequently disconnected clients, or when a large part of your changes are
updates, merge can offer better performance. For instance if you have a
stock market application when stock prices vary all over the place thousands
of times per day, and you only replicate data a few times per day, merge
will offer much better performance as only the changes are going accross the
wire. Also if your data changes are small merge is better. So if you have a
sales force and the sales agents don't generate a significant amount of
sales compared to the volumn of data, merge will offer better performance.
> Andreas Lauffer, Germany
Hilary Cotter, USA
