One question I seem to get asked a lot is what is the difference between Replication and Mirroring when it comes to SQL, and especially for the Sys Admin stuck in the middle, what is the best one to use for a Disaster Recovery scenario.
Firstly, for people who haven't heard of either, both replication and mirroring are methods of transferring data from a primary database, to a secondary database or databases. Two common scenarios for this are to have a separate copy of the database for reporting purposes, or to have a copy of the database safely off site in the event of disaster on the primary database. In theory both Replication and Mirroring have the same purpose, to shift data from your database to other locations, but each method has a very different application and way of going about things. Obviously nothing in these guides are black and white, completely right or wrong, but as a general rule this is the way you would apply these methods.
Replication - Reporting and database synchronization
There are many configurations you can put together for replication, or mirroring, and you'd have to identify exactly what setup would suit your needs, but the above is a good baseline as to the best practices. As always, any questions relating to the blog or to your particular setup, please don't hesitate to ask me.
Sorry again for the short and less detailed nature of this update, but life is getting in the way! I'll try to get a new detailed one done over the weekend for Monday. In the meantime, have a great weekend everyone!
Firstly, for people who haven't heard of either, both replication and mirroring are methods of transferring data from a primary database, to a secondary database or databases. Two common scenarios for this are to have a separate copy of the database for reporting purposes, or to have a copy of the database safely off site in the event of disaster on the primary database. In theory both Replication and Mirroring have the same purpose, to shift data from your database to other locations, but each method has a very different application and way of going about things. Obviously nothing in these guides are black and white, completely right or wrong, but as a general rule this is the way you would apply these methods.
Replication - Reporting and database synchronization
- Secondary database is online and searchable. Basically you can run queries against your secondary database and treat it like a normal database while protecting the performance of your primary database.
- There are different forms of replication
- Transactional - Reads transactions to your secondary DB(s) as they occur on your Primary with a slight delay
- Snapshot - Takes dumps at scheduled times of the changes recorded in your logs since the last synch, and inserts them at a scheduled time
- Merge - This does what it says on the tin. It will merge content between databases so you can update data in both DB's. You might use this if you have two identical databases from day 1, and you have App 1 talking to DB 1, and App 2 talking to DB 2 for load balancing purposes or something. At the end of the day you might want to merge the transactions that occurred during the day to make sure you have two full lists of transactions in each DB.
- Secondary database is in an offline mode while mirroring is in place. This is to ensure integrity in the secondary database in the event of a failover being required. Nothing can write to the secondary database, so you know it will exactly match your primary.
- Can replicate schema changes. What this means is if you add a new index, or a new table, it will automatically be mirrored over. This is not the case with replication, it will only transfer across data level changes.
There are many configurations you can put together for replication, or mirroring, and you'd have to identify exactly what setup would suit your needs, but the above is a good baseline as to the best practices. As always, any questions relating to the blog or to your particular setup, please don't hesitate to ask me.
Sorry again for the short and less detailed nature of this update, but life is getting in the way! I'll try to get a new detailed one done over the weekend for Monday. In the meantime, have a great weekend everyone!
No comments:
Post a Comment