Database mirroring a live database

1
votes

I've been reading about database mirroring on SQL Server 2008 R2 / 2012 and I have a question: does it matter if the database being mirrored is live? Do I have to do anything special or different if my database is live?

EDIT-1:

  • I have a (one) database on premises that's our production database. A few hundreds transactions happens per second, and 99.999% are read operations. Our plan is to mirror this database (single database) to SQL Azure (IaaS) as a "backup" structure, if the on premises environment crash for any reason, we just point to the cloud.
  • I would like a zero loss, but I settle for a 5 seconds loss
  • Latency wont be a problem... slow running is better than no running...
  • I have just one node and one database
2
sql-serverhigh-availabilitydatabase-mirroring
Would you be able to clarify what you mean by live? - Chris H
@ChrisH edited! check it out! - Leonardo

2 Answers

1
votes

The biggest thing to consider if you run in either high-performance or high-safety mode.

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

Basically, in high-performance the transaction is committed locally and hopefully on the remote side. The mirrored server asynchronously applies the transactions and data loss may be a factor. The upside is not having latency in the local server and you only need two machines.

In high-safety, the transaction is witnessed by a third server and has to be committed on both sides. This creates latency which would not be desirable.

1
votes

There are a lot of things to consider when using mirroring with a live DB.

  • How many nodes do you have?
  • You need to know the the approximate load you expect.
  • Consider the appropriate transaction safety level. The transaction safety level determines whether the changes on the principal database are applied to the mirror database synchronously or asynchronously.
  • Another is your network setup. Are your nodes on LAN, WAN, etc.
  • How many DBs are you mirroring. Based on this answer, your choices for the above might differ.
  • What sort of Disaster recovery do you need should the worst happen?