Replication, Data Mirroring, Change Data Capture OR AlwaysOnAvailibility Group ?

There are scenarios when we need to have secondary database, like for Reporting purpose or data backup or for disaster recovery.
Generally people used to get confused among different strategies, it is confusing to say what to use for what purpose without having proper information about all. Recently I faced a scenario where we need to have a separate database for reporting purpose. 
I went through multiple resources over the internet and thought to write this blog which can help people choosing the appropriate one among different strategies for their requirement.
Let's talk one by one. Here I also given the reference link which I referred and felt can give you further details in case if you need. 

Replications:

There are three different Replication strategy:

1.Snapshot Replication:

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

This could be a good approach when

·         Data does not change frequently.

·         Acceptable to have old data (out of date and not real time) for a period of time.

·         Replicating small amount of data.

                Reference: http://msdn.microsoft.com/en-us/library/ms151832.aspx

                              

2.Merge Replication:

Merge replication is typically used in server-to-client environments and most suitable for the following situations:

·         Multiple subscribers might update the data and propagate those change to the publisher and other subscribers.

·         Subscribers might change data in offline and later synchronization will happen among Publisher and other subscribers.

            Reference: http://msdn.microsoft.com/en-us/library/ms152746.aspx
                      

3.  Transactional Replication:

Transactional replication is typically used in server-to-server environments and most suitable for the following situations:

·         Need incremental changes to be propagated to subscriber.

·         Need near real time data with low latency.

·         Publisher has a very high volume of insert, update and delete activity.

·         Can be used for non-sql server database also.

Note: Transactional Replication is not possible if tables does not have primary key.

            Reference: http://msdn.microsoft.com/en-us/library/ms151176.aspx                    

                              http://msdn.microsoft.com/en-us/library/ms152570.aspx

Data Mirroring:

            Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Database mirroring is best suitable options in following situations:

·         Need copy of primary database to make sure of data availability in case of any failure of Primary database.

This secondary database (mirror db) cannot be used for reporting db purpose. This is a deprecated features and will be removed in a feature version of SQL Server.

Reference: http://msdn.microsoft.com/en-us/library/ms189852(v=sql.90).aspx

           http://blog.fpweb.net/understanding-alwayson-mirroring-with-sql-server-2012/#.VBakePmSzql

          

Change Data Capture:

Change data capture tracks  insert, update and delete in transaction log (similar to replication) and insert the changes into "Changing tables" associated with the tracked tables. The change tables are automatically created in the same database.

It is recommended for database which will have very minimal or small change in database. And it is not recommended for the following reasons:

·         For every change in production db, CDC does the another insert in same change table in same database, which can have a noticeable performance hit on production DB.

·         Need to write our own polling mechanism to pull changes from change table.

·         Data in tracking tables needs to be cleaned up periodically which get logged in log table, which can have a noticeable performance hit on production DB.

Note: Due to extra logging caused by internal change tables (and cleanup), plus the need to write custom code, plus limitations on schema changes, most of the people says that Transactional Replication beats Change Data Capture.

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Reference: http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

           http://msdn.microsoft.com/en-us/library/cc645937.aspx

           

AlwaysOn Availability Groups:

This feature is introduced in SQL Server 2012, which allows to scale out read across multiple SQL Server instances. AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise and works as Data Mirroring works.

Disadvantages of using AlwaysOn over Transactional Replications:

·         AlwaysOn works as Data Mirroring and it mirrors everything in database where as Transactional replications allows to eliminates triggers, constraints and other definitions which is not required for reporting purpose.

·         AlwaysOn supported by SQL Server 2012 and later version only.

·         When using Transactional Replication , we can add extra indexes, tables, stored procedures, views etc only on replicated database as per requirement but in case of AlwaysOn, all must be added in primary database itself.

·         When using Transactional Replication, Replicated database can be maintained independently like security implementation and all.

Reference: http://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx

            http://dbinsight.com.au/dbinsight/transactional-replication-vs-availability-groups-for-read-only-workloads

            

Achieve High Availability for SQL Server

http://technet.microsoft.com/en-us/magazine/2007.03.highavailability.aspx

Thank You for reading. Don't forget to like it and feel free to give your feedback/comment.