Data Flow Model for Failover backup with Transactional Replication Using SQL Server (4 Servers Involved)
A Little Intro
1. Mirroring:
Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
One Copy is Principal server and other copy is mirror server
When principle in active mode the mirror server will be in restoring mode and all the transactions will be copied to the
mirror that had been occurred in principle. When principle is down the mirror takes place for principle server with latest
copy of data.
Mirroring is used for failover and disaster recovery.
2. Replication:
It is a set of technologies for copying and distributing data and database objects from one database to another and then
synchronizing between databases to maintain consistency.
OR
Replication is a bundling of technologies that enable you to copy, distribute, and synchronize specific types of database
objects and their associated data and dependencies from one database to one or more databases on the same SQL Server
instance or different SQL Server instances (which is more common)
Sql server Replication has three components
a. Publisher: The SQL Server instance hosting the database from which you'll be distributing data is called the
Publisher.
b. Subscribers: The entity that receives the data from the Publisher is called the Subscriber. The Subscriber can be
the same SQL Server instance as the Publisher, the same SQL Server instance as the Distributor, or a separate
SQL Server instance altogether. A specific Subscriber is defined by adding a subscription to a specific publication.
c. Distributor: Replication requires a separate database to house both metadata and in-flight data. This database is
called the distribution database, and the SQL Server instance that hosts it is called a Distributor.
The Distributor can be the same instance as the Publisher, a separate SQL Server instance, or the instance where the data
is being distributed to. The decision of where to place the distribution database isn't always consciously considered, but
when it is, it's typically due to performance-overhead considerations or availability concerns (e.g., if transactional
replication is used in conjunction with database mirroring).
And has three types
i. Snapshot replication
ii. Merge Replication
iii. Transactional replication: Transactional replication is the automated periodic distribution of changes
between databases. Data is copied in (or near) real-time from the primary server (publisher) to the receiving
database (subscriber). Thus, transactional replication offers an excellent backup for frequent, daily databases
changes.
In transactional replication any changes made at subscriber are not reflected to publisher. Subscriber can only
request for latest change from publisher(Pull). Or Publisher will only send its changes to Subscriber (Push).
For one Subscriber Push will have advantage over Pull in transactional replication
