When it comes to high availability options in SQL Server, there are several choices available. One such option is database mirroring, which was introduced in SQL Server 2005. Database mirroring provides a way to create a standby database that is ready to take over in case of a failure.

Database mirroring offers a lower cost alternative to clustering and provides some advantages over log shipping. However, it is important to research and understand your high availability needs before choosing a technology.

Initially, database mirroring was not supported in the RTM release of SQL Server 2005. Although it could be enabled using a trace flag, it required further testing before it was officially supported. Despite this initial setback, database mirroring has proven to be a reliable technology for maintaining a standby database.

One common question that arises when it comes to database mirroring is the limit on the number of databases that can be mirrored on an instance. In the past, there were claims that 50 databases was the maximum limit, but recent real-world testing has shown that 10 databases per instance is a more realistic limit, especially on 32-bit machines.

The reason for this limitation is related to threads. Each mirrored database uses 5 threads per instance, which can impact the performance of the system. While there is no official explanation from Microsoft, it is believed that the number of transactions and their size play a significant role in determining the limitations of database mirroring.

It would be helpful if Microsoft provided guidance on the number of transactions or transaction byte count, along with bandwidth requirements, to help better size systems for database mirroring. This would allow users to make more informed decisions based on their specific workload.

For those interested in learning more about database mirroring, Microsoft provides resources such as the “Database Mirroring Best Practices and Performance Considerations” and “Database Mirroring in SQL Server 2005” articles on their Technet website.

Overall, database mirroring is a solid technology for achieving high availability in SQL Server. While it has its limitations, proper setup and configuration can ensure a reliable standby database ready to take over in case of a failure.