SQLBkup 40 - SQL Backup Installation on MSCS cluster

Brian Donahue

• date: 16 February 2006 (05 Aug Review)
• Version(s) affected: SQL Backup 4.x in conjunction with Microsoft Cluster Server and SQL Server

Microsoft and other vendors provide a solution whereby a SQL Server can automatically restart on another computer if the primary computer fails. These clustered servers can host applications such as Microsoft SQL Server to provide a high-availability solution for critical databases. Windows Server 2003 Enterprise Edition includes this feature, called Microsoft Clustering Service (MSCS).

If you install SQL Backup on a Microsoft virtual SQL Server, there are some special considerations to be aware of.

installation

SQL Backup must be installed on each individual node in the MSCS cluster environment. If there is more than one instance of Microsoft SQL Server, the SQL Backup Server Components must be installed for each instance. The particular node that you are installing SQL Backup on does not necessarily be the active SQL Server resource, but in this case, you would need to install any additional server components for the instance manually using SQBServerSetup.exe -I <instance name>. This file is copied into SQL Backup's program directory when SQL Backup is installed.

When SQL Backup prompts for a service account to run the SQL Backup Agent Service, select a domain account. A good idea is to use the same account that the MSSQLSERVER service runs as. This will most likely have the necessary rights to do backups, and also be a domain account, since you are running a clustered SQL Server.

licensing

SQL Backup requires a licence for each individual piece of server hardware. The unfortunate implication for cluster administrators is that there must be a separate licence purchased for every node in the cluster. Additionally, since the server only answers on a virtual server IP address, which is different from the local IP address(es) on the server, the licenses must be Professional Edition licenses, which allow remote management. You would not be allowed to connect to the virtual server, even from the local node, if the server was licensed as SQL Backup Standard Edition.

Once you have obtained a licence key from Red Gate by purchasing the licences, you must apply them to each individual node. Again, since the licence activation is derived from the local node's hardware, this requires a licence activation for each node in the cluster.

Using the SQL Backup GUI, you can register a virtual server, right-click it, and select install/activate licence. This, however, applies the licence only to the cluster node that is servicing requests for the virtual server. To licence other nodes, you will need to run ProductActivation.exe from the SQL Backup program folder while logged into the cluster node and enter your serial number to activate any standby nodes' SQL Backup licence.

Availability

One possibly overlooked aspect of a cluster is that if you change any of SQL Backup's settings, such as setting up the SMTP host to send email or decrease the backup log retention time, the settings, which are kept in the registry, will not apply to another cluster node if failure is initiated. SQL Backup would then run with different or default settings. Therefore it's a good idea to add the SQL Backup Agent service as a resource in Cluster Administrator.

The following steps apply to setting up the agent service as a resource:
(After each step, assume a click Next or continue)

• Open Cluster Administrator from the Administrative Tools group
• Right-click the group that contains the resources for your SQL Server
• Choose New, then Resource
• Name the resource SQL Backup Agent - <Instance Name>
• Change the resource type to Generic Service
• Choose all of the nodes that SQL Server can own (default)
• Add dependencies on the SQL Server and the SQL Agent services
• Enter the service name. For the default instance, it will simply be SQLBackupAgent and for named instances it will be SQLBackupAgent_<Instance Name> (The separator is an underscore)
• Add the following registry keys to be replicated:
  32-bit servers:
  Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
  Software\Red Gate\SQL Backup\InstalledInstances\<instance name>\ServiceLogin
  64-bit servers:
  Software\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
  Software\Wow6432Node\Red Gate\SQL Backup\InstalledInstances\<instance name>\ServiceLogin
• Finish

Now any settings saved for an instance of the SQL Backup Service will be replicated to the failover server, so that in the event of a failover, backups will continue in the same manner as before.