Replication

publisher + distributor +subscriber

Navigate to Replication on SSMS.

1. Setup distributor.

   1.1 Right click Replication -> configure distribution

   1.2 Follow the steps, create a distribution database for distributor. The database will locate in System Databases. A distributor can be on a standalone instance or together with the publisher database instance.

   1.3 Choose a snapshot folder. This is where the distributor dumps the data files. It's better using a network share so it supports both pull and push subscriptions.

         Make sure the snapshot folder is accessible to the users, or there will be silent error.   

2. Setup publisher

   2.1 Local Publications -> new Publication

   2.2. Choose publication type. It can be snapshot or transactional or merge or ...

         Snapshot publication sends a snapshot periodically, which may cause too much data transfer.

         Transactional publication streams transactions after the initial snapshot (this should send the delta only).

   2.3 Choose the database and tables to publish, a published table needs to have a primary key.

         The database is called publisher and the selection of tables is called publication.

         You may add filter to the tables too, so only a specified range of data is published.

         The initial snapshot can be created once or periodically by Snapshot Agent.

         Make sure the publisher database is with full transaction log, otherwise the log agent won't be able to detect changes.

3. Setup subscriber

   3.1 Local Subscriptions -> New Subscription

   3.2 Choose the publication to receive

   3.3 Choose subscription type (pull or push)

        A push subscription runs all the agent jobs at the Distributor. It's more centralized and easier to admin.

        A pull subscription runs all the agent jobs at the Subscriber. It moves the overhead to each subscriber.

   3.4 Choose subscription database to receive the publication.

   3.5 Specify the logins for the subscriber agent which needs to connect both distributor and subscriber

   3.6 Specify the synchronization schedule. Run continuously or on demand or on a schedule. This affects how soon a change is synced.

Basically it works as follows:

Publisher(publication)->distributor->Subscriber(subscription)

Snapshot Agent create the initial snapshot, or sync snapshots later.

Log Agent reads transaction log to determine is there is a change.

Distributor Agent ? transfers data between databases.

To script a replication, right click the replication and 'Generate Scripts'.

It's very handy.