Database Mirroring Automation
The slide deck and scripts from that presentation can be found here:
- DBMAutomation.pptx (928 KB)
- DBMAutomation.ppt (998 KB)
- dba_FailoverMirrorToOriginalPrincipal.sql (1.4 KB)
- dba_ManageLinkedServer.sql (2.5 KB)
- dba_ControlledFailover.sql (7.7 KB)
- dba_CopyLogins.sql (8.5 KB)
Failover Mirror to Original Principal
There is a very good reason to use the first automation procedure …. money. Licenses for SQL Server are not inexpensive. One of the good things about database mirroring is that if the mirror server is used only as failover and is otherwise always inactive, then the license for the principal server covers both servers. Certain limitations apply to this such as the licensing must cover the higher of the two servers. For example, if the principal is a 4 CPU server using Standard Edition and the mirror is an 8 CPU server using Enterprise Edition, the licensing must cover 8 CPUs and must cover Enterprise Edition. Another often overlooked limitation is that upon failover, you can only use the mirror server for 30 consecutive days as the principal. If you go past 30 days, you must license the mirror server separately. This could easily cost a business a lot of money if an automatic failover occurs and nobody notices or bother to fail back.For this procedure, I set up a job on the designated mirror server and set it to run every 5 minutes. When the procedure runs, ti won’t do any work beyond checking the sys.database_mirroring view unless it detects a database that is ready to be failed back to the original server. In order for a database to be failed back, it has to meet 3 criteria.
- It has to in the principal role on the mirror server
- It has to be in a synchronized state thus ensuring that the original principal is up, connected, and synchronized
- It has to be running in Full Safety
Performing a Controlled Failover
The controlled failover procedure does exactly what it says. It is for those times when you need to manually fail over all of your mirrored databases. This procedures does rely on linked servers for communication between the principal server and the mirror server. If the linked server does not exist, it will attempt to create it using another procedure that I provide called dba_ManageLinkedServers. Be sure to create this procedure along with the controlled failover procedure, dba_ControlledFailover.The controlled failover procedure does a LOT of work for you. First of all, it handles changing databases from asynchronous mode (safety off) to synchronous mode (safety full) so that they can be failed over. It has built-in timing mechanism to have the process wait for the databses that are not yet synchronized to become synchronized before failover. Everything has built-in timeouts so that any one database won’t cause the process to run forever. Next, once failover is complete, it sets the databases back to their original operating mode. If it was originally running in asynchronous mode, it sets it back to asynchronous mode after failover.
This procedure is great for those gotchas that can’t be set upon restore of the database for mirroring configuration until a failover occurs. To ensure smooth operation of the databases after failover, the procedure checks the database owner and sets it on the other database if it is not the same. It also checks the TRUSTWORTHY property for the database and sets it if it does not match the original principal. Within this subroutine, you can add any other steps you need to add. In the past, I have used this section of the code to disable and enable SQL jobs and to sync up the server level objects such as logins.
Speaking of logins, I have already covered transferring logins in a different blog post: Transferring Logins to a Database Mirror.
Once again, I include a @DBName (sysname, default of NULL) parameter and a @Debug (bit, default of 0) parameter. If @DBName is provided, it will only process the named database. Otherwise, it processes all mirrored databases. If @Debug is set to 1, it only returns the code that it would execute to fail over the databases without actually issuing any commands. For a @Debug value of 0, it issues the commands. It also has a parameter for @MaxCounter (int, default of 60). When it is waiting for a database to be synchronized before failover or waiting for the failover to complete, it pauses 5 seconds between each loop. The @MaxCounter parameter is the number of loops. If @MaxCounter is 60, then the timeout is 60 loops times 5 seconds or 3 minutes. If you find that your databases often take longer, you can pass in a larger number for this parameter.
No comments:
Post a Comment