SQL Replication

SQL replication is the process of copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Publication is a collection of database objects that are to be replicated. Publisher is the server that makes the data available for replication. Subscriber is the server that receives the replicated data. Other key terms include Distributor, which manages the flow of data and ensures synchronization, and Articles, which are the individual database objects (like tables or views) within a publication.

Before setting up SQL Replication, make sure the SQL Server and Agent services are running under the domain service account

Here’s the Employee table on WIN1 that will be replicated to WIN2

Distribution

On WIN1, configure the Distribution

Configure WIN1 as the distributor

Next configure for the SQL Server Agent Service to start automatically

Because we don’t run a pull method from the subscriber, we can use a local path instead of a network path here for the Snapshot Folder

Then configure the distribution database

After that select WIN1 as the publisher

Then select configure distribution at the end of the wizard

Hit finish

Publication

Still on WIN1, creata a new publication

Select the database to publish

Then select the publication type

Next select the articles to be published, here we select one table and one stored procedure

For the table filters we’ll just skip away

Select create snapshot immediately

Then on security, enter the domain service user

Next select create publication at the end of the wizard

Give the publication a name, then hit finish

And now the employee publication has been created

Subscription

Now on WIN2, create a new subscription

Select WIN1 as publisher and select the publication

For the agent location, we will use push type where the agent runs on the publisher

Then select the WIN2 as the subscriber and select the subcription database

Specify the domain service user

For the sync schedule, select run continuously

Then select initialize subcription

Select create subscription at the end of the wizard

Then hit finish

Monitoring Replication

On WIN1, on Replication Monitor we can see we have WIN2 in the subscription running the replication

We can also see the detailed logs for this particular replication

Now going back to WIN2, we can see the table has been replicated and available on WIN2

Modifying Data

On WIN1, we modify the table data by inserting 10 new rows

We can see on the Synchronization Status that we have 1 transaction with 10 commands being delivered to WIN2

And WIN2 now has 20 rows of data

Now we try deleting 7 rows of data on WIN1

The Synchronization Status also shows 7 commands being delivered to inform WIN2 about the deletion

Now WIN2 only has 13 rows of data

Now lets try stopping the synchronization

If we modify data while sync is off, the WIN2 will not receive the changes until the sync is back on

Now lets start the synchronization again

And we can see the 9 commands change we made is being delivered right after