Mirroring Multiple Databases

You can mirror multiple databases on the same server, as we have discussed. You can either use the same server for a mirror partner, or you can use a different mirror server for each database. I would recommend that you use the same mirror server for mirroring multiple databases from a principal server. That way, the maintenance is less, and the system has less complexity; otherwise, you will have to perform all the steps we mentioned in the "Hardware, Software, and Server Configuration" section on each mirror server.

If you want to use the database mirroring feature, especially with high-safety and automatic failover, you have to be careful when you design your application. Consider a scenario where your application is using two databases, DB1 and DB2, on a server called ServerA. Now you have set up database mirroring for both these databases to your mirror server ServerB with automatic failover. Suppose a failover occurs with only DB1 (perhaps because of a disk failure on the disk where DB1 resides or a sporadic network issue that could cause the mirroring session of one database to time out), and because of the automatic failover, the database will failover to the mirror ServerB. So ServerB will be the principal for database DB1, and ServerA is the principal for database DB2. Where would your application connect? Even though both databases are available, your application would not function correctly. This could also happen if you manually failover the one database and not the other.

So an application that relies on multiple databases is not a good candidate for a high-safety with automatic failover scenario. You can probably have high-safety mode without automatic failover and have an alert when mirroring state changes so that you can manually failover all the databases or have a SQL job that does that for you.

Also remember that you cannot mirror a system database. Also, make sure that you do not mirror too many databases on a single server, or it may affect server and application performance. You should not mirror more than 10 databases at a time on an instance. Use Performance Monitor counters and the Database Mirroring Monitoring tool to see how your servers are performing.