Mirroring With Replication.docx

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. ii. iii.

Snapshot replication Merge Replication 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

Note: When transactional replication is configured with mirroring the distribution database has to be on separate sever to technical reasons Note: In SQL Server 2017 we can replicate schema change with transactional replication Diagrammatic representation:

As in above diagram the publisher (the server which has data entry) will have mirror server and distribution database is and transactional replication configurations are kept on separate server as with mirroring it is not supported yet on same server. Lastly the subscriber for read only purpose.

Addition Material on: https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-and-replication-sqlserver?view=sql-server-2017 http://simplesql.blogspot.com/2011/01/replication-vs-mirroring-and-what-to.html https://www.itprotoday.com/microsoft-sql-server/getting-started-transactional-replication

https://blog.pythian.com/how-to-configure-transactional-replication-mirroring-failover/