SQL Server Replication - overview

You cannot use SQL Server Express as a publisher for all types of replication including merge. SQL Server Express can only act as a subscriber. 

Basically, you have to create two things:

Fist, a PUBLICATION.

Secondly, a SUBSCRIPTION (to the aforementioned PUB).

But behind both of those, you need a DISTRIBUTOR.

When you are on a virgin SQL Server instance, and use the Create Publication Wizard, the first thing it will do for you is offer to create the DISTRIBUTOR.  

The other thing behind all this is the agents.

A SNAPSHOT Agent.

A LOG READER Agent. 

(? DISTRIBUTION Agent ?)

A REPLICATION MONITOR.

Other important concepts:

INITIALIZING, or the INITIAL SNAPSHOT.

The BUILD PROCESS.

==========

Publication Types

Snapshot:

Publisher sends a snapshot of the published data to Subscribers as scheduled intervals.

Transactional:

Publisher STREAMS TRANSACTIONS to the Sub after they receive an INITIAL SNAPSHOT of the published data.

Peer-to-Peer:

Enables MULTI-MASTER REPLICATION.

Publisher STREAMS TRANSACTIONS to all the PEERS.(in the topology)

All peers (called nodes) can read and write changes and the changes are propagated to all the others.

Merge:

Publisher and Subscribers can update the published data independently after the Subscribers receive an INITIAL SNAPSHOT of the published data.

Like GIT.

Changes are merged periodically.

NOTE: Compact Edition can only subscribe to these kinds of publications.

============

You can only select Tables for replication that have a PRIMARY KEY COLUMN.

All tables in transactional publications must have PRIMARY KEY COLUMNS.

New Publication Wizard:

Snapshot Agent

Specify when to run the SNAPSHOT AGENT

Subscriptions are initialized with a SNAPSHOT of a publication schema and data. The SNAPSHOT AGENT creates the SNAPSHOT.

x? Create a SNAPSHOT immediately and keep it available to initialized subscriptions.

? Schedule it to run at certain times.  Why would you want this?

Agent Security

For two agents: SNAPSHOT Agent and LOG READER Agent.

-selected Windows account, need to provide password, can be on another domain

-or Run under the SQL SERVER AGENT Service Account (not a rec'd best practice)

Connect to the Publisher:

- by impersonating the process account

????? what is the PROCESS account????????

- or using a SQL SERVER login (password must be provided)

============

Assuming you chose to CREATE SNAPSHOT IMMEDIATELY, the SNAPSHOT agent will run and create a bunch of files in your select backup directory (ie, G:\ReplData)

Folders and paths:

G:\ReplData\unc

\MACHINENAME_DBNAME_PUBNAME\

\YYYYMMDDssss\

files:

tablename_2.bcp

tablename_2.idx

tablename_2.pre

tablename_2.sch

Then I guess the SNAPSHOT agent sleeps.  But if you manually activate it (like by using the MONITOR: on where it says "Completed" for status, right-click and select START) it will do another snapshot starting with the new YYYMMDDsssss folder.

Meanwhile, the Log Reader agent will be started and still running.

In the Replication Monitor, assuming you selected the machine, the tabs are:

Publications

Subscription WatchList

Agents

On Agents, in addition to Snapshot and LogReader, there is "Maintenance Jobs"

This is a reminder that SQL AGENT JOBS were probably created after the Publication was craeated.

The ones that ran:

Agent history clean up: distribution

Distribution clean up: distribution

Replication: agents checkup

Monitor and sync replication agent jobs.

....like every 5 or 2 minutes....Distribution cleanup is every 10 minutes.

... and back in the ReplData folder, some of the created snapshots folders (oldest one) disappeared!

Maybe one of those jobs did that.

There can be at least 4 or 5 snaps so far without deleting.  I think the Distribution Cleanup might be the one deleting folders.  After it ran there was only two snap folders left, three older ones deleted.

One of the left-click menu options on the Publication is to RE INITIALIZE ALL PUBLICATIONS.  

"This action marks each subscription to be reinitialized from a snapshot the next time its Distribution Agent runs.  What snapshot should be used to reinitialize the subscription(s)?

- Use the current snapshot

- Use a new snapshot --------? Generate the new snapshot now.

[Mark for Reinitialization]

My guess is, if you don't have any SUBSCRIPTIONS, this doesn't really do anything.

EXCEPT if you select "Use a new snapshot and generate now", then you see the SNAPSHOT AGENT run immediately and you have a new snap folder.

Now let's see what happens when you go create a SUBSCRIPTION!

Two types of SUBS: 

Push

Pull

In the Wizard it is called Distribution Agent Location.  Choose WHERE to run the DISTRIBUTION AGENT:

Run all at the Distributor (current machine):  PUSH

"easier to admin..."

Run each agent at its own particular Subscriber: PULL

"relieves some processing overhead on the Distributor"

Subscribers: You have to pick the SUBSCRIPTION DATABASE, which is where the data is going to go....  I wonder what happens if you select the same database as the source?  For now using an empty REPL_TESTER database.

SYNCHRONIZATION SCHEDULE:

Either "Run continuously" or "Run on demand only". (also "<<<define schedule>>>)

INITIALIZE SUBSCRIPTIONS:

Specify whether to INITIAIZE each SUBSCIRPTION with a SNAPSHOT of the PUBLICATION DATA and SCHEMA.

"....needs to be done...unless it has already been "specially prepared" for the subscription"

INITIALIZE WHEN: Immediately or "At First Synchronization"

If you go immediately to the Replication Monitor, the "My Publishers"- machine name, underneath shows a warning yellow and ! symbol. But then goes away.

Might be .... If on SUB WATCH LIST tab I pick SHOW TRANSACTIONAL SUBS, it now shows something there (odd b/c I thought we just were doing snapshots) ....

... and after a minute or so, it goes green.

I got it to perform this when I did an INITALIZE immediately (now that we have a SUB).