Online Tutorials & Training Materials |
Register Login

Principle Configuration Considerations for Database Mirroring

|| || 0

Principle Configuration Considerations for Database Mirroring

When utilizing the SQL Server database mirroring as a high-availability solution, the following things should be kept in mind:

  • Each database can only have one mirror. It is impossible to mirror a database to two different destinations.
  • The user can utilize log shipping in addition to database mirroring on the same database for creating little out of sync images in multiple destination of the database.
  • Both distances and network bandwidth are major deciding factors which determine the success of such a configuration. The user must keep in mind during a failover to the mirror, the entire traffic between application server and database server goes over the network to the mirror server. A mirror server which is located quite a few miles away from the principal server can significantly increase the overall response time on the SAP side.
  • When can we replace Log-shipping with Database Mirroring? There are several advantages and disadvantage in replacing the Log-shipping with Database Mirroring. On the advantage side, the mirrored database is quite closer to the state of the real productive database. As far as the disadvantage is concerned, the database image cannot be kept as signed more than a couple of hours for compensating for human errors. This is because, a change on the principal database is communicated and are applied to the mirror in just a few milliseconds or in seconds (all depending on the configuration). Another difficulty of database mirroring can be related to the requirement on network infrastructure. Inspite, of using the asynchronous database mirroring, the user requires ample of network bandwidth available at all time. The shared network infrastructures which is utilized heavily by employees between the two locations can seriously affect the bandwidth and therefore impact and lover the success in managing operations with database mirroring. The bandwidth should be constantly available. The distance acceptable is mainly dependent on the latency (in the synchronous case) and on the configuration of mirroring, the workload and application against SQL server can tolerate. During the asynchronous configuration, distances of 100 miles and even more is reasonable, coupled with a good network bandwidth and network reliability conditions. Assuming the bandwidth is good along with reliability by the network for synchronous configuration the distance will define the dormancy in communication. The distance should be kept small, along with the SAP workload which is characterized by small transactions. For synchronous mirroring, at this stage, one assumes that under the SAP workload characteristics a distance of 100 miles is on the edge. Depending on the customer specifically, implementation of very small distances also affect the substantial performance impact. Therefore, the user is advised to be careful in planning for the synchronous mirroring, over dozens of miles.
  • When should Database Mirroring replace Microsoft Cluster Services? There are pros and cons of utilizing mirroring instead of MSCS. One disadvantage is that the user requires needs additional investment in storage infrastructure for keeping a second image. Generally, the second image is not placed on the same SAN or NAS device. The second disadvantage is that mirroring, may not always automatically failover (see next item). There are scenarios, known as split-brain scenarios, which help avoid an automatic failover, however these require a manual failover (see next item).
  • The major advantage of using mirroring is having a second image of the database, right within the same datacenter. Having such a second database image near line is now one of the core focus areas of HA planning.
  • When upon selecting the Automatic Failover, you should be aware of situations, where an automatic failover will not occur, simply because the failover logic assumes a situation where transactions could have been made on the principal after the database mirroring already stopped because of a failure condition. For instance, imagine a situation where database mirroring stops because on the principal side, there was an issue with the transaction log. However, the Witness instance does not seem to have issues communicating with the principal utilizing the same mechanism as the mirror. There is no reason for initiating a failover as the database on the principal seems to be still available, inspite of the witness realizing that Database Mirroring did stop. However, the witness realizes the condition of having lost the principal, in case the principal SQL Server instance is shut down manually. However, the user should assume that transactions on the principle could have been committed after Database Mirroring failed because the manual shutdown of the principal eventually occurred minutes after Database Mirroring failed. Hence, the present implementation of Database Mirroring behaves very conservatively by entirely leaving it to the system administrator for initiating a manual failover. This is an important difference to MSCS when one works on one image of the database and a failover is issued irrespective of the conditions.
  • Beginning with Database Mirroring, the best is to begin with the Asynchronous Mirroring first. In case there are severe issues with the network bandwidth or with the network configuration then it can be easily detected. If throughput problems already are visible in the asynchronous mirroring configuration, then taking all the steps to the synchronous configuration would not make sense. If the asynchronous configuration did not reflect any problems or bottlenecks then the user could move to synchronous mirroring w/o failover in case the distance is satisfactory. Again, the performance and the impact on the system should be thoroughly evaluated.
  • Dedicating a Network Adapter to Mirroring? This is certainly possible. The user can assign a TCP/IP Address to an additional network adapter in the two servers. These TCP/IP addresses would be used for defining the two TCP/IP endpoints in the database mirroring setup. In this configuration database, the mirroring traffic would pass through the two dedicated network adapters.
  • Utilization of the Bulk Logged or the Simple Recovery model. For reducing the transaction log entries while creating indexes or performing bulk load activity, the principal database cannot be put into one of these recovery models. With the database mirroring only the 'FULL' recovery model is supported. This would also affect the layout of the transaction log once the bigger indexes must be created. For facilitating the bulk-logged recovery model you would need to pause database mirroring, establish a log-shipping between the two servers or manually supply the mirror server with all transaction log backups performed on the principal. The principal side is switched back into FULL recovery model, for going back to database mirroring once the activity under bulk-logged recovery model is finished. All the transaction log backups of the principal are applied to the mirror database, on the mirror side. The user can then setup database mirroring between the two SQL server instances once again.
  • All the scheduled tasks and jobs in SQL Server Agent are required to be duplicated and adapted. One has to duplicate and adapt all the scheduled tasks which are to be executed on the principal on the mirror as the mirror server does have a different name and msdb is not failed over. It is worthwhile thinking about name conventions for making clear which of the scheduled tasks is required to be run on which server. However this has to be done manually. Apart from these, activating and de-activating all those tasks post a failover is a manual process, which must be done to e.g. guarantee transaction log backups which take place on the mirror after a failover.
  • The user could face a situation where the role of the principal is held by the server which is addressed as Failover Partner in the connection string and where the user decides or is forced to disable Database Mirroring (difference to suspending mirroring). In this scenario, reconnects could also fail as the former Failover Partner is not in a database mirroring relationship anymore. Therefore, it will refuse connection attempts which address that server as Failover Partner. The error messages can be found in the SAP work process developer traces or in SM21 messages and these would appear as:
  • 'The connection attempted to fail over to a server that does not have a failover partner'
  • This issue was rectified with the latest SQL Server 2005 SP2 CU4 and has been documented in KBA #936179. However, for making this fix work, trace flag 1449 has to be set on the principal and mirror instance.

Related Articles