Editorials

How to Copy Sql Data

When you think about database redundancy and SQL Server, there are a number of ways to implement the duplication of data, built directly into the engine.

SQL Azure automatically creates three copies of any database you publish. It has a working copy, a witness, and witness of the witness. If the working copy goes offline, the witness is promoted, and a new third copy is created. This is all automatic and out of the box. This is very cool, working only at the database level.

Also working only at the database level is the database mirroring technology. Without implementation specifics, you define and deploy a database mirror. These are two completely separate copies of the same database. All database modifications are made to both copies. If the primary goes offline, the secondary database can take over in place of the primary, because it has received all the same database inputs.

Clustering works more at the database service level. That’s a very simple description, and it is capable of more. But generally, a clustered instance has two servers sharing the same disk. When one server goes down, the other takes over. There are other more sophisticated configurations possible. But, generally, the cluster is configured to take over the entire workload of another database service were it to go offline.

Replication, on the other hand, is a completely different technology. Replication is database specific. However, rather than keeping multiple copies of a complete database, replication allows you to replicate transactions. It does not replicate tables, or records. Transactions from one database are applied remotely to another data store. This is a big difference from the other implementations. It requires development for every kind of transaction you wish to apply to a different data store. This is the most granular method of duplicating data across more than one database instance.

Always on implementations provide even more flexibility. Because of its flexibility, I won’t try to summarize always on in a single paragraph. Based on its implementation it should provide a service level like that of SQL Azure where things keep working, and it is self healing.

This should give you an idea of different database duplication techniques available in SQL Server, and help you choose what implementation for which you need more details.

Cheers,

Ben