Preparing the Mirror server for Failover


When you set up mirroring, your intentions are clear that in the event of failover, the mirror takes on the full load. In order for the mirror to be fully functional as a principal, you have to do some configurations on the mirror server. Please note that database mirroring is a database-to-database failover solution only. The entire SQL Server instance is not mirrored. If you want to implement full instance failover, you have to consider failover clustering. We will compare different high-availability technologies in the section "Database Mirroring and other High Availability Solutions."

Hardware, Software, and Server Configuration

Your mirror server hardware should be identical (CPU, memory, storage, and network capacity) to that of the principal if you want your mirror server to handle the same load as your principal. You may argue that if your principal is a 16-way, 64-bit server with 32GB RAM, having an identical hardware for mirror is a costly solution if your principal is not going down often. You may feel that such expensive hardware is sitting idle. If your application is not that critical, you may want to have a smaller server for just failover and take the load for some time, but I would argue that if the application is not that critical, you do not have such extensive hardware on the primary either. Also with such huge hardware, the process on the server must be very heavy. So if you failover, your mirror should be able to handle that load even it is for some time. You have to determine the business cost versus hardware cost and make the decision. Also, you can use your mirror server for noncritical work so that while it is just a mirror, it can be used for some other process. Of course, you have to plan that out properly to make proper use of your mirror server.

Make sure that you have the same OS version, service packs, and patches on both servers. Of course, during a rolling upgrade (we will discuss that in the "Preparing for Planned Downtime" section), service packs and patch levels can be different temporarily.

Make sure you have same edition of SQL Server on both partners. If you are using a witness, you don't have to have the same edition. You can use a smaller server for the witness because it doesn't carry any load at all. Of course, the availability of the witness server is critical for automatic failover. You can refer to the table in the section "Database Mirroring and SQL Server 2005 Editions" for more details on which edition supports which database mirroring features.

Make sure you have an identical directory structure for the SQL Server install and database files on both the partners. If you add a database file to a volume/directory and that volume/directory does not exist on mirror, the mirroring session will be suspended right away.

Make sure that all the SQL Server configurations are also identical (tempdb size, trace flags, startup parameters, memory settings, degree of parallelism) for both principal and mirror.

Logins and their permissions are very important. All SQL Server logins on the principal must also be present on the mirror server; otherwise, your application will not be able to work in the event of failover. You can use SQL Server Integration Service, which has a "Transfer logins" task to copy logins and passwords from one server to another. You will still need to set the database permission for these logins. If you transfer these logins to a different domain, you have to match the SID also.

There are many other objects that may exist and are needed for that application on the principal. You have to transfer all these objects to the mirror server (for example, SQL jobs, SQL Server Integration Services packages, linked server definitions, maintenance plans, supported databases, SQL Mail or Database mail settings, and DTC settings).

If you are using SQL Server authentication, you have to resolve the logins on the new principal server after failover. You can use the sp_change_users_login stored procedure to resolve these logins. Note that sp_change_users_login cannot be used with a SQL Server login created from a Windows principal.

Make sure to have a process in place so that when you make any changes to any configuration on the principal, you repeat or transfer the changes on the mirror server.

Once you set up your mirror, failover the database and let your application run for some time on the new principal, because that is the only way you can make sure that all the settings are correct. Try to schedule this task during less busy hours and make sure you do the proper communication on your test.

Database Availability During Planned Downtime

There are two ways you can configure the mirroring session as far as transaction safety is concerned: SAFETY FULL and SAFETY OFF. We'll look at steps for both these options and see how to use the "rolling upgrade" technique to perform a software and hardware upgrade while keeping the database up for applications.

SAFETY FULL

Assuming that you have configured the mirroring session with safety full, if you have to perform software and hardware upgrades, perform these steps in order.

  1. Perform the hardware and software changes on the mirror server first. If you have to restart the SQL Server or the server itself, you can do so. As soon as the server comes back up again, mirroring sessions will be established automatically and the mirror will start synchronizing with the principal. Note that the principal is exposed for the duration that the mirror database is down, so if you have a witness configured, make sure that it is available during this time, or the principal will be running isolated and will not be able to serve the database, because it cannot form the quorum.

  2. Once the mirror is synchronized with the principal, you can now failover using the ALTER DATABASE <db_name> SET PARTNER FAILOVER command. The application will not connect to the new principal. We will talk about application redirection when database is mirrored in the "Client Redirection" section later. Open and in-flight transactions during failover will be rolled back. If that is not tolerable for your application, you can stop the application for the brief moment of failover and restart the application after failover succeeds. Now perform the hardware or software upgrade on your old principal server. After you are done with upgrades and the database becomes available on the old principal, it will assume the role of mirror, the database mirroring session will be established automatically, and it will start synchronizing.

  3. If you have a witness set up, perform the hardware or software upgrade on that server.

  4. Your old principal is currently acting as a mirror. You can fail back to your old principal because all your upgrades are done. If you have the same hardware on the new principal, leave it as is so that you don't have to stop the application for a brief moment. But if your hardware is not identical, you should consider switching back to your original principal.

SAFETY OFF

If you have configured the database mirroring with SAFETY OFF, you can still use the "rolling upgrade" technique by following these steps:

  1. Perform the hardware and software changes on the mirror first. See more details in the "SAFETY FULL" section.

  2. Change the SAFETY to FULL using the ALTER DATABASE <db_name> SET SAFETY FULL command. You have to run this on the principal server. Plan this activity during off-peak hours so that your mirror server will not take too much time to get synchronized.

  3. Once the mirror is synchronized, you can perform the failover to the mirror.

  4. Perform the hardware and software upgrade on the old principal. Once the old principal comes back up, it will assume the mirror role and start synchronizing with the new principal.

  5. Once synchronized, you can fail back to your original principal.

If you are using mirroring just for making a redundant copy of your database, you may not want to failover for planned downtime, because you may not have done all the other settings properly on mirror as mentioned in the "Hardware, Software, and Server Configuration" section. In that case, you have to take the principal down for upgrade, and the database service will not be available.

SQL Job Configuration on the Mirror

For identical configuration on both the principal and mirror servers, you also have to copy SQL jobs on your mirror server as we mentioned earlier. When the database is the mirror, you do not want these SQL jobs to run. You have some options on how to do that:

  • Disable these jobs and enable them manually when the database becomes the principal. As a DBA, you do not want to baby-sit these jobs, so this is not a good option.

  • Have some logic in the SQL job steps that checks for the database mirroring state and run the next step only if the database mirroring state is principal.

  • Listen for the database mirroring change event when the database becomes principal and execute a SQL job that will enable all the SQL jobs you want to run. Stop or disable these jobs again when the event is fired, indicating that the database state has changed to mirror. We will discuss how to listen to these database mirroring state change events in the "Mirroring Event Listener Setup" section later.

Database TRUSTWORTHY Bit on the Mirror

If you restore the database, the TRUSTWORTHY bit is set to 0 automatically. So when you set up database mirroring, this bit will be set to 0 as soon as you restore your database on your mirror server. If your application requires this bit to be 1, in case of failover, your application will fail because this bit is set to 0 on the mirror, which is now the new principal. To avoid this, when you set up the database mirroring, once it is set up correctly, failover to the mirror and set this bit to 1 using ALTER DATABASE <db_name> SET TRUSTWORTHY ON. Then optionally fail back to your original principal.

Client Redirection to the Mirror

In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and optionally the failover partner server. There are many ways to write the connection string, but here is one example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:

 "Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;" 

The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.

Assume a client is successfully connected to the principal, and a database mirroring failover (automatic, manual, or forced) occurs. The next time the application attempts to use the connection, the ADO.NET or SQL Native Client driver will detect that the connection to the old principal has failed and will automatically retry connecting to the new principal as specified in the failover partner name. If successful, and there is a new mirror server specified for the database mirroring session by the new principal, the driver will retrieve the new partner failover server name and place it in its client cache. If the client cannot connect to the alternate server, the driver will try each server alternately until the login timeout period is reached.

The great advantage of using the database mirroring support built into ADO.NET and the SQL Native Client driver is that you do not need to recode the application, or place special codes in the application, to handle a database mirroring failover.

If you do not use the ADO.NET or SQL Native Client automatic redirection, you can use other techniques that will enable your application to failover. For example, you could use Network Load Balancing (NLB) to manually redirect connections from one server to another, while the client just connects to a virtual server name. Of course, in NLB you have to configure to make sure that the entire load is diverted to the principal and not actually doing NLB, because at a given point only the principal database is available. Also, you will have to listen to the mirroring state change event to change the NLB configuration to divert the load to a particular server. You might also write your own redirection code and retry logic.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net