Behaviour of Merge Replication

I want to deepen the function of Merge Replication:
1) what's the main difference between a push subscription and a pull
subscription ?
2) if I have a subscriber node that for many time (4-5 months) is
disconnected from publisher, what will it do when it'll be re-connected
to the network ?
3) is there a method to force the re-synchronization of DB subscriber
in this case ?
4) instead if I have a failure of Publisher server, and I work only
with subscribers, what sould I do after the restart of Publisher to
load the changes made in subscribers DB ?
Hi Marco - answers inline:

> 1) what's the main difference between a push subscription and a pull
> subscription ?
Which box does the work. If I have the choice, I prefer to have the agents
centralised and use one set of alerts and notifications.
> 2) if I have a subscriber node that for many time (4-5 months) is
> disconnected from publisher, what will it do when it'll be re-connected
> to the network ?
It will error and require reinitialization.
> 3) is there a method to force the re-synchronization of DB subscriber
> in this case ?
The term we use is Reinitialization. sp_reinitmergesubscription and
sp_reinitmergepullsubscription can be used. Run the snapshot agent then the
merge agent.
> 4) instead if I have a failure of Publisher server, and I work only
> with subscribers, what sould I do after the restart of Publisher to
> load the changes made in subscribers DB ?
>
Restore an older version of the publisher's database and synchronize. This
is not foolproof (eg schema changes and download only articles will require
some thought) but it generally works.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||answers 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
"Marco" <peska78@.tin.it> wrote in message
news:1164723024.262636.185820@.h54g2000cwb.googlegr oups.com...
>I want to deepen the function of Merge Replication:
> 1) what's the main difference between a push subscription and a pull
> subscription ?
With a push the publisher initiates the sync, with a pull the subscriber
initiates it. Push is good for a small number of subscribers when you want
to centrally manage them. Pull is good for a large number of subscribers but
you don't have a central point of management.
> 2) if I have a subscriber node that for many time (4-5 months) is
> disconnected from publisher, what will it do when it'll be re-connected
> to the network ?
It will probably have expired so you will need to send a new snapshot down.
you can upload the subscriber changes before the snapshot is applied.
> 3) is there a method to force the re-synchronization of DB subscriber
> in this case ?
Its called a re-initialization. Expand your publication and right click on
your subscription and select reinitialize to reinitilize the problem
subscriber.
> 4) instead if I have a failure of Publisher server, and I work only
> with subscribers, what sould I do after the restart of Publisher to
> load the changes made in subscribers DB ?
>
Nothing. When the subscriber comes back on line it will synchronize changes
which have occurred with the subscribers. If the retention period has passed
most of your subscribers will probably have expired which means you will
have to reinitialize, run the snapshot agent and then run the merge agents.
|||Paul, Hilary
thanks for your answers.
|||Hi Josip
There is no hard rule defining small, or the cut off point going from push
to pull. I normally use pull for over 10 subscribers.
As you have some tables which are only one direction I would use
transactional replication. As you have some which are bi-directional I would
use merge for those.
For the merge publications use central publisher in your head office. For
the uni-directional publications it depends on the data flow, if they are
going to the central publisher make the branch offices the publishers, ie 14
publishers in the branch office going to the central publisher in the head
office. If they are going from the central publisher in the head office to
the subscribers branch office use the central publisher in the head office.
HTH
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
"Josep" <jmartinez@.autec.es> wrote in message
news:e4BsFn5FHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Hi Hilary,
> I new in replications and I've just realised that I need Merge replication
> (after reading the first chapter of your book). So I started looking for
> information about Merge. But still I've things unclear. For example, I
> don't know if I should use push or pull subscriber. When you say:
>
> what's a small number of subscribers? Because I've 15 machines to be
> replicated, where one is a dedicated server, the Central Server. Some
> tables must be replicated to everywhere (using the Central Server?) and
> some other tables just replicated to the Central Server (so a filter
> should be applied?). It's like a star topology.
> And this goes to another question. What's better, to have the publication
> in the Central Server and push/pull subscription to the other computers or
> generate a publication on each server and make the Central Server a
> subscriber of all?
> I think that the first option is the best, at least for the tables that
> must be replicated to everywhere, but I'm not sure for the tables that
> must be filtered.
>
> Thank you,
> Josep Martnez
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
> news:eNEH1zvEHHA.4132@.TK2MSFTNGP04.phx.gbl...
>

Behaviour issue with remote inserts

I have an issue doing remote inserts and don't understand why there is a behaviour difference between local and remote inserts. So far i have not been able to find an

answer. It may be something to do with parametised query execution

but i`m not sure yet.

Below is the scenario

If i do a

insert into server.db.dbo.remotetable

select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the

destination server where as if the insert into is local only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert? Its currently playing havoc with a trigger i have on a production box.

To test this i've supplied some very simple code. Setup instructions are commented into the code. Have not coded a linked server creation though.

At the end look at the tbllog and you will see what i mean.

All advise gratefully received!

Cheers
Andrew

Sample Code

---

--CREATE this table on Source server and Destination server

CREATE TABLE [dbo].[Input] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create this table on the destination table on destination server
CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the input table on Source server
insert into input values ('Remote','1/1/2000')
insert into input values ('Remote','1/2/2000')
insert into input values ('Remote','1/3/2000')

--Insert some sample data into the input table on Destination server
insert into input values ('Local','1/1/2000')
insert into input values ('Local','1/2/2000')
insert into input values ('Local','1/3/2000')

--Run the insert from the source server
insert into destinationsrv.destdb.dbo.destination
select * from input

--Run the insert from the destination server (as a local query)
insert into dbo.destination
select * from input

--On the destination server, run select from the log table which was populated by trigger.
--You will see remote insert has 3 rows but local only has 1!
select * from tbllogHmmm. Looks like SQL Server uses cursors to run any remote query, even if it is run via OPENROWSET (just tested it). I wonder if there is a way to avoid this, except moronic workarounds like temp table & remote SP.|||I've been hunting high and low for a way to get it to behave the same way as a local insert and not do multiple inserts without resorting to temp tables and remote sp's. Was hoping there might even have been a reg setting but not found one.

I'd also like to understand why it has to break down into multiple inserts!

Still looking for the answer but no joy. |||

I'm dealing with the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?

BDE 5.01 not connecting to SQL2K after patching

Hello All,
I am experiencing unusual behaviour with a number of desktops (3 of 20) running the BDE and the SQL connectivity option from the sql cd. For reasons unknown the BDE suddenly stops connecting to the database and cant be fixed without a reimage. Appears t
o be a key or dll that is causing interoperability issues.
No idea what o/s patch is causing trouble at this stage but its a recent phenomena.
has anyone seen this type of behaviour with third part software using BDE/SQL connectivity ?
Brian in AU
+61 431 479 751
What error do you get?
Could the problem be something along these lines?
259569 PRB: Installing Third-Party Product Breaks Windows 2000 MDAC Registry
http://support.microsoft.com/?id=259569
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.