Many large organisations use third party backup solutions, such as Veritas NetBackup, to manage data protection across the enterprise. The use of such tools is often more complex to setup and manage due to the presence of certain variables such as network firewalls, antivirus software, name resolution issues, etc. These packages in the field, give the organisation certain benefits such as:
- A centralized backup catalog
- Optimal use of disk pools dedicated to backups
- Capacity for heterogenous DBMS platforms
- Centralized, easier-to-manage retention policies
This article describes a challenge we recently had with configuring a SQL Server environment on Veritas NetBackup. We are not describing the fulling configuration procedure but a few pitfalls people out there may experience. The complete procedure for configuration of backups can be found in the reference provided at the end of the article.
The setup involved in this case is summarized as follows:
- SQL Server 2016 installed on two server computers (N1 and N2)
- WFCS configured on both boxes
- Availability Groups configured
- Two Availability Groups AG1 and AG2 created for two sets of databases
- AG1 was running off N1 and AG2 was running off N2 for a number of reasons
- Veritas netback client 7.6.1 was installed on both servers
The task was to configure backups of these instance such that Veritas would consistently backup databases in their respective Availability Groups (AG), no matter what node each AG was running on. The backup sets also had to be identifiable as for the databases in each Availability Group.
Veritas NetBackup Support for AlwaysOn
The first challenge in the venture is that he documentation for Veritas NetBackup 7.6.1 does not say anything about Availability Groups. There were details about protecting High Availabiltiy SQL Server installations in version 7.7.2 and above of the same document. Three key things I should highlight from the document are as follows:
- There is no support for Availability Groups in the newly released SQL Server Intelligent Policies. AlwaysOn Availability Group instances must be protected using Legacy Policies
- Veritas NetBackup supports the use of a preferred node in the AG cluster for backups. This can be achieved by using the keyword PREFERREDREPLICA TRUE
- Backups will not work reliably across the cluster except all nodes in the AG Cluster are added to the specific policy or policies configured for taking backups on the cluster.
What We Did
We had two availability groups, AG1 and AG2, and two corresponding listeners, LR1 and LR2. AG1 had Node N1 as the primary replica and AG2 had Node N2 as the primary replica. After installing the Veritas NetBackup Client, we ran the NetBackup MS SQL Client and connected to each instance on Nodes N1 and N2, using their respective listeners. This meant that the clients registered on the NetBackup Master and Media servers were registered as the names LR1 and LR2, not N1 and N2. However, we eventually had to add the names N1 and N2 in the policies we had created. Please note we created a separate policy for each Availability Group and each had its own script file specified in the policy.
What We Experienced
This article might not be worth writing if we do not mention the error we stumbled into in this implementation and the symptoms we experienced. We found that the backups always succeeded when an Availability Group was sitting on Node N1 but did not succeed when the AG sat on Node N2. We had to open ports 1556 and 13724 on the Network Firewall between each Node and the media and master servers. We could confirm this by telnet tests.
telnet mastersvr.domain.group 1556
telnet mastersvr.domain.group 13724
telnet mediasvr.domain.group 1556
telnet mastersvr.domain.group 13724
Telnet is an old protocol used to establish connections between clients and servers. It can be used to simple check whether a client can talk to a server on a specific TCP port. For the purpose of this definition , a server is the computer running a service which listens on the port being tested. Fig. 1 and 2 show the results of a successful connection to the master server on TCP port 1556 (Veritas Private Branch Exchange listens on this port). Fig 3 shows a failed connection. A failed connection could happen if the service is not running or if a Firewall is blocking traffic on that port.
Fig 1. Telnet Test
Fig 2. Successful Telnet Test
Fig 3. Failed Telnet Test
We also confirmed that the hosts files of all computers involved (master server, media server, Node N1 and Node N2) were updated. Nodes N1 and N2 had the entries for both media and master servers required for name resolution, and the master and media servers had entries for both NetBackup clients (Nodes N1 and N2). We could even verify this was configured correctly by doing a ping to the hostnames on each computer. We did get the correct IP Addresses. Inspite of all this we kept getting the following error on attempt to take backups for AG2 whenever it was hosted by Node N2
Server Exit Status = 25 cannot connect on socket
On digging into the dbclient error log located in C:\Program Files\Veritas\NetBackup\logs\dbclient, we found that the hostname we had specified was not the hostname the backup process was attempting to connect to. We were then able to dig further and realise there had been an error during installation. This wrong hostname showed up only in the registry (HKLM\SOFTWARE\Veritas\NetBackup\CurrentVersion\Config\Server) as well as here:
Fig 4. Wrong Host Name
Fig 5. Registry Key
How We Corrected It
We were able to address the situation we faced from Node N2 by modifying the registry key and rebooting the server. Once we were able to establish a connection to the server, we were able to run backup successfully where every the AG was sitting. Recall we used two different policies for ours instances INST1 and INST2 and we had to add the individual Node names for Nodes N1 and N2 to each policy for this to work. However, we did not need to add the individual nodes to the hosts file of the master and media servers.
We further had to specify a domain account to run the NetBackup Client Service and NetBackup Legacy Client Service. This same domain account needed to be in the Windows Administrator Group as well as have sysadmin privileges on both SQL Server instances. Details of the rules surrounding the service account permissions can be found in the NetBackup Administrator Guide. Lastly we also learnt that the order in which entries are introduced in the host files are very important to Netbackup. The order must be consistent across all servers. We use the format below:
xx.xx.xx.xx hostname hostname.domain.com
Veritas NetBackup is an excellent tool for enterprise backups however it is extremely complex and it is important to ensure everything is configured correct otherwise you can experience a lot of headaches for something seemingly as simple as backups. In my opinion, administering Veritas NetBackup is in itself a full blown role but where it is not feasible to have a dedicated NetBackup Admin, the DBA must assume this role. Reading the documentation thoroughly and using the simplest and most effective options will save a lot of headaches.