Replication

[srajeevkumar]

Hello freinds,

Cn someone point me into the right direction for me to have some insights into Replication and publishing. I want to have an indepth idea on how to do this mainly to help enable our worldwide databases synchronise with the central database every so often. Any other ideas other than replication is also welcome.

Many thanks

Rgds

 

[johnherman]

same as thread183-823401
Please do not cross post.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[bertrandkis]

Unfortunately it's not possible to give you a full lecture on replication from this forum but, find below the ABC of Replication.I have made it as short as possible but I hope this will put you in the right direction.

--=========== REPLICATION =================
REPLICATION IS THE PROCESS OF MOVING DATA FROM A SOURCE DATABASE TO A DESTINATION DATABASE ON SEPARATE SERVERS.
--+++ REPLICATION TERMINOLOGIES
--++ARTICLE:Is the data in the table or a subset of data
in the table to be distributed(published).
--++PUBLICATION: A collection of articles.
--++PUBLISHER:The sql server that maintains the source database
--++SUBSCRIBER:The sql server that receives the replicated data.
--++DISTRIBUTOR:The intermediate sql server that receives data from publisher and send it to the subscriber.If load balancing is not required the Publishing server can be made the Distributor.

--++REPLICATION MODELS OR ARCHITECTURE OR CONFIGURATION
Replication model is the physical implementation of the
replication design.
-- 1) CENTRAL PUBLISHER/DISTRIBUTOR MODEL
-- 2) CENTRAL SUBSCRIBER/MULTIPLE PUBLISHERS MODEL
-- 3) MULTIPLE PUBLISHER/MULTIPLE SUBSCRIBERS MODEL

--++ SUBSCRIPTION TYPE
--==PUSH SUBSCRIPTION:Replication is initiated by the publisher
--==PULL SUBSCRIPTION: Replication is initiated by the subscriber

--++ REPLICATION TYPE
The replication type determines how the replicated data is maintained on the publisher and the subscriber.
--==SNAPSHOT REPLICATION: Picture of the entire publication is distributed. Not very suitable for large data.Replicated data does not have to be modified at the
subscriber as changes made at the subscriber do not
reflect back to the publisher unless IMMEDIATE UPDATING
SUBSCRIBERS OPTION IS USED

--==TRANSACTIONAL REPLICATION: Only changed data is distributed.Suitable for any replication size.
Replicated data does not have to be modified at the
subscriber as changes made at the subscriber do not
reflect back to the publisher unless IMMEDIATE UPDATING
SUBSCRIBERS OPTION IS USED.

REMARK:If data has to be updated at the subscriber and the updation reflected back to the publisher,this can be done by using the IMMEDIATE UPDATING SUBSCRIBERS OPTION.
This option adds to the replication the DTC and 2 phase commit protocol that to update the publisher as the changes are made to the subscriber.
To activate Immediate update,select checkbox to show advanced option.

--==MERGE REPLICATION: Multiple publishers make change to the data independently of one an other.

To setup replication the steps are:
1) PLANNIG
-Decide on the replication model
-Identify roles of each server in the model
-Decide on the replication type

2) IMPLEMETION:
-ON the distributor Server:
* Create a Windows Login account with admin rights
* Set this account as start up account for SQL agent
* Create an SQL login for the windows Account, this
login must be included in the SysAdmin group.
* Run the Publishing and Distribution Wizard.

-ON The Publisher server:
* Run the create Publication wizard.

-On subscriber server:
* Run the create Publication wizard.
* Ensure that the accounts used by Agents on the subscriber can access the distribution working folder (windows permissions).

An anonymous subscription is a type of pull subscription for which detailed information about the subscription and the Subscriber are not stored.

To find more about replication consult the BOL, type replication and choose architecture as a subtopic.

Bertrandkis

 

[srajeevkumar]

Thanks very much for the info bertrandkis.
Can I ask some basic questions before i strart going indepth into replication.

(1) Does the publisher role and the subscriber role has got to be fixed and cant it not be either way. Means A publicher can be a subscriber and a subscriber can be a publisher at times.

(2) The reason why we want to go in this way is to enusre that any data modified in our remote sites ( 3 of them ) globally is replicated in the main server in the UK. At the same time any record could have changed locally in the Uk server also. How does replication handle all this.

(3) What determines as to who takes priority when synchronising the data between sites.

Any help wil be highly appreciated.
I just need to be sure hat I am using the right technology for the right purpose. Any other methodology to do this is also welcome.
Many thanks in advance for any informative replies.

Rgds