Managing Changing Roles | Professional SQL Server 2005 Administration (Wrox Professional Guides)

A high-availability solution must allow smooth role switching between the current primary and secondary servers for business continuity. To accomplish this goal, log shipping requires that certain dependencies are available on the secondary server, because the scope of log shipping is at the database level. Any object outside of the log-shipped database will not be maintained by log shipping. For example, SQL Server logins are contained in the master database, and SQL jobs are contained in msdb. Therefore, these dependencies and others need to be systematically maintained by other procedures to allow users to connect to the secondary server after it becomes the new primary server. Furthermore, a process will need to be developed to redirect the application to the new primary server.

Synchronizing Dependencies

Log shipping applies changes that occur inside the log-shipping database but does not maintain any outside dependencies. Moreover, log shipping cannot be used to ship system databases. Newly added logins, new database users, jobs, and other dependencies that live in other databases are not synchronized by log shipping. In a failover scenario, when users attempt to login to the secondary server's database, they will not have a login there. Moreover, any jobs configured on the primary server will not be present either. Additionally, if the primary server uses linked servers to access a remote SQL Server, the database operations would fail because they would not be able to find the linked server. Therefore, you need to identify the outside dependencies that the log-shipping database uses and develop a plan to make these resources available during the failover. Below are common log-shipping dependencies and solutions to those.

Login and Database Users

Because SQL Server logins and new database users are created on the primary server and its database, you need to develop a process to synchronize these users with the secondary server and database to prevent login-access issues in a failover scenario. This synchronization process works best if you set it up as a SQL job that runs at certain scheduled intervals. In a planned failover, you should run these SQL jobs before failover to update the secondary server with the current access information. Here are the steps:

To develop an Integration Services to Transfer logins, open SQL Server Business Intelligence Development Studio and Start a new Integration Services project.
In the Solution Explorer, name the project SSIS Transfer Logins, and rename the SSIS Package to Transfer Logins.
Click on the Toolbox and drag the Transfer Logins Task into the package.
Right-click the Transfer Logins Task and choose Edit.
Click Logins. You'll see the window shown in Figure 19-8.
For SourceConnection, enter a new connection to the primary server.
For DestinationConnection, enter a new connection for the secondary server.
For LoginsToTransfer, choose AllLoginsFromSelectedDatabases.
For DatabasesList, choose the log-shipping database.
In the Options section, in the IfObjectExists entry, choose what to do if the login exists, such as FailTask, Override, or Dkip. If the secondary server is hosting other databases, you may encounter duplicate logins.
Save the package, and choose BuildBuild SSIS Transfer Logins to compile the package.
From Microsoft SQL Server 2005 Management Studio, connect to the Integration Services of the primary server.
Under the Stored Packages, choose MSDB, right-click, and choose Import Package.

image from book
Figure 19-8

The next step is to create a new SQL Job on the Primary Server and rename it "Sync Secondary Server Access Information." This job will synchronize the logins from the primary to the secondary servers and will include executing the new SSIS Transfer Logins package.

Create a step named BCP Syslogins with these characteristics:
- Type: Operating system
- Run As: SQL Agent Service (the account will need sysadmin permission to execute this command and will need to have read/write permission on the folder that it will copy the file from)
- Command: BCP Master.sys.syslogins out c:\login1\syslogins.dat /N /S <Server_Name> -T
Create a step named Copy Syslogins with these characteristics:
- Type: Operating system
- Run As: SQL Agent Service (the account will need to have read access to the source folder and read/write access to the destination folder)
- Command: COPY c:\login1\syslogins.dat \\SecondaryServer\login2
Create a step named Transfer Logins Task with these characteristics:
- Type: Operating system
- Run As: SQL Agent Service (the account will need sysadmin permission to execute this command)
- Command: DTEXEC /sq <Package_Name> /ser <Server_Name>

Next, create a new SQL Agent job on the secondary server called "Resolve Logins Job." This job resolves the logins on the secondary server once the secondary database has been recovered. The SQL Server Agent service account or a proxy account must have the sysadmin role to run this job.

Create a step named Resolve Logins with these characteristics:
- Type: Transact-SQL script (T-SQL)
- Command: EXEC sp_resolve_logins @dest_db = '<Database_Name>', @dest_path = 'c:\login2\', @filename = 'syslogins.dat';

SQL Agent Jobs

The Integration Services Transfer Jobs Task can be used to synchronize jobs from the primary to the secondary server. Create a package using SQL Server Business Intelligence Studio, and choose the Transfer Jobs Task. Provide the connection information and the jobs to transfer. Then compile and import a package into SQL Server 2005 or execute it as an SSIS filesystem. However, it is better to develop a SQL Agent job to periodically synchronize the secondary server.

Other Database Dependencies

Make a list of all dependencies that the log-shipping database depends on. These can be linked servers, another database, CLR procedures, database mail, and others. Develop a plan to synchronize these to the secondary server.

Switching Roles from the Primary to Secondary Servers

There are two potential types of role changes: planned and unplanned failover. A planned failover is most likely when the DBA needs to perform some type of maintenance, usually scheduled at a time of low business activity or during a maintenance window, and switches roles to apply a service pack on the primary server, for example. An unplanned failover is when in an unfortunate period the primary server becomes unavailable and for business continuity the DBA switches roles.

Planned Failover

For a planned failover, find a time when the primary server is less active. It is likely that the secondary server will not have had restored all of the transaction logs from the primary server, and there will be transaction log being copied across by the SQL Agent job that the restore SQL Agent job has not completed. Additionally, the active transaction log may contain records that have not been backed up. To bring the secondary server completely in synchronization, the active transaction log must be restored on the secondary server. The steps to accomplish it are:

Stop and disable the primary server transaction log backup job.
Manually copy all transaction-log backups that have not been copied from the primary server backup folder to the secondary server folder. Then restore each transaction log in sequence to the secondary server. A different option is to execute the log-shipping copy and restore jobs to restore the remainder of the transaction logs. Then use the log-shipping monitoring tool or report to verify that the entire set of transaction-log backups have been copied and restored on the secondary server.
Stop and disable the secondary server's copy and restore jobs.
Execute the Sync Secondary Server Access Information job and then disable it.
Back up the active transaction log from the primary to the secondary server with NoRecovery:
```
 USE MASTER; BACKUP LOG <Database_Name> TO DISK = 'C:\PrimaryBackupDir\<Database_Name>.trn' WITH NORECOVERY; 
```
This accomplishes two goals:
- Backs up the active transaction log from the primary server and restores it to the secondary server to synchronize the secondary database
- Changes the old primary server database to NoRecovery mode to allow transaction logs from the new primary server to be applied without initializing the database by a restore, as the log chain would not have been broken

On the secondary server, restore the active transaction log and then recover the database:

 RESTORE LOG <Database_Name> FROM DISK = 'c:\secondarydirectory\Database_name.trn' WITH RECOVERY

If the active transaction log is not accessible, the database can be recovered without it:
```
 RESTORE DATABASE <Database_Name> WITH RECOVERY; 
```
On the new primary server, execute the Resolve Logins job to synchronize the logins. The secondary server's database becomes the primary server's database and will start to accept data modifications.
Redirect all applications to the new primary server.
Configure log shipping from the new primary server to the secondary server. The secondary server (the former primary server) is already in NORECOVERY mode. During log-shipping configuration, in the Secondary Database Settings dialog box, choose "No, the secondary database is initialized."
When you finish configuring log shipping, the new primary server will be executing the transaction-log backup job, and the secondary server will be copying and restoring the transaction-log files. Set up and enable all SQL jobs that were synchronizing from the old primary server (for example, to synchronize the logins and database users to the old primary server).

Unplanned Role Change

If the primary server is unavailable, some data loss is probable. For example, the active transaction log may not be accessible, or the last transaction-log backup may not be reachable. Therefore, you will have to verify that the last copy and restore transaction logs have been restored by using the log-shipping monitoring and reporting functions. If the active transaction-log backup is accessible, it should be restored to bring the standby server in synchronization with the primary server up to the point of failure. Then restore the secondary database with RECOVERY.

After assessing the damage and fixing the old primary server, you will most likely need to reconfigure log-shipping configuration because the active transaction log may not have been accessible, or the log chain may have been broken. When you configure log shipping, in the Secondary Database Settings dialog box choose either to restore from a previous database backup or generate a new database backup to restore. You may choose to switch roles to promote the original primary server.

Switching Between Primary and Secondary Roles

After performing the steps in switching the primary and secondary roles in a planned role change where the Log Shipping jobs have been deployed to both primary and secondary servers, you can switch between primary and secondary Servers by:

Stopping and disabling the primary server's transaction-log backup job
Verifying that all the transaction-log backups have been copied and restored, either manually or by executing the SQL jobs to do that
Executing the Sync logins job
Stopping and disabling the transaction-log copy and restore jobs on the secondary server
Backing up the active transaction log on the primary server with NORECOVERY
Restoring the active transaction-log backup on the secondary server
Restoring the secondary server's database with RECOVERY
Executing the Resolve Logins job
Enabling the transaction-log backup on the new primary server to log-ship to the secondary server
Enabling the secondary server transaction-log copy and restore jobs
Enabling synchronization of the logins and database users
Enabling any other SQL jobs

Redirecting Clients to Connect to the Secondary Server

Log shipping does not provide any client-redirect capability. After switching roles, the client connections will need to be redirected to the secondary server with minimal disruptions to users. The approach you choose may depend on the infrastructure and who controls it. Additionally, the number of clients that need to be redirected, the required availability of the application, such as a service level agreement (SLA), and the application activity may all play a factor in the decision. At minimum, users will experience a brief interruption as the client applications get redirected. The following sections discuss a few common approaches to redirecting client connections to the secondary server.

Application Coding

The application can be developed as failover-aware with the capability to connect to the secondary server with either automatic retry or by manually changing the server name. The application logic would connect to the primary server first, but if it is unavailable, and after the retry logic has run unsuccessfully, the application can attempt to connect to the secondary server after it has been promoted to a primary server. After the secondary database has been recovered, however, it may not necessarily be ready to serve user requests. For example, you may need to run several tasks or jobs first, such as running the Resolve Logins task. After database recovery, the database may need to be put into single-user mode to prevent other users from connecting while you perform tasks or jobs. Therefore, the application logic must be able to handle this situation where the primary server is no longer available and the secondary server is not yet accessible. The application will provide an option to connect to the primary or secondary server as controlled by users. With the manual method, a procedure will need to be in place to tell users when the new primary server is available and that they can connect.

Network Load Balancing

Use a network load balancing solution, either Windows Network Load Balancing (NLB) or a hardware solution where the application connects using the load balancing network name or IP address and the load balancing solution directs the application to the database server. Therefore, in a failover scenario, the application will continue to connect to the network load balancing network name or IP address, while the load balancer will be updated manually or by script with the new primary server network name and IP address. Then clients will be redirected. NLB is included with certain versions of Microsoft Windows. Configuration is straightforward, and it acts as the cross-reference to direct applications to the current primary server.

Domain Name Service (DNS)

DNS provides name-to-IP address resolution and can be used to redirect clients to the new primary server. If you have access to the DNS server, you can modify the IP address to redirect client applications after a failover, either by script or by using the Windows DNS management tool. DNS acts as a crossreference for the client application, because they will continue to connect to the same name, but the DNS modification will redirect the database request to the new primary server.

SQL Client Aliasing

This method may be less favorable if there are many client applications that connect directly to the database server, because aliasing would have to be applied at each client computer, which may not be feasible. It is more feasible if the client applications connect to a Web or application server that then connects to the database server. Then the SQL Client alias would have to be applied on the Web or application server, and all the clients would be redirected to the new primary server. To configure aliasing, go to the SQL Server Configuration Manager, under the SQL Native Client Configuration.