Search

Friday, April 8, 2011

Replication


Replication is a set of technologies for copying and distributing data and database objects from one database to another. Replication is a desired option when we want to have the same data on multiple database servers. There are different types of replication and more info on Replication can be read from this MSDN library article.
Use:
  • It is good option when we want to have multiple server updates with disconnected options. It can be near real time. It can also be used to implement load balancing for a database.
Recovery:
  • Recovery Time Objective (RTO) - This could be immediate and only require re-pointing your application to a different server.
  • Recovery Point Objective (RPO) - If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because this is an exact copy of the primary database all transactions will be replicated to the mirror copy as they were done on the primary server.
Pros:
  • Can be configured for individual database objects instead of the entire database.
  • Enhanced tools available for configuration and troubleshooting.
  • Provides near real time disaster recovery.
  • It supports disconnected architecture, so it is beneficial for mobile users.
  • Secondary copy could be used for near real-time reporting to offload reporting use on production database.
Cons:
  • Reconfiguration may require manual intervention.
  • Need to rely on distributor to push changes.
  • Other items need to be handled outside of Replication such as logins, SQL Agent jobs, etc...
  • It is more complicated to setup and maintain then the other options.
  • SQL Server will need to be fully licensed for the secondary server.

No comments:

Post a Comment