SQL Agent Backup using SQLINSTANCE $ALL

[Dion]

Hi

I'm trying to set up an SQL backup in a environment with more that one SQL cluster group (Microsoft cluster) but for now am just trying to test a backup of the single default SQL virtual server in a single cluster group.  I have set up an SQL policy that includes the virtual server name and am using the below .bch file on each node to try a backup.  If I don't use the SQLINSTANCE $ALL statement and specify the host name the backup works fine but if I use the below settings, I can't get it backed up as it seems that NetBackup cannot enumerate the correct host and instance names.

I'm running NetBackup 6.5.1A on Windows Server 2003.  The client is Windows 2003 R2 Enterprise x64 Edition OS and SQL 2005 64-bit Enterprise Edition.

OPERATION BACKUP
SQLINSTANCE $ALL
DATABASE $ALL
NBSERVER "MDRBUPMED01.BUP.SDP.NET.NZ"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

If I turn on logging I get the following error in the dbclient log file on the client

15:05:10.650 [3960.2760] <4> VxBSAInit: Veritas NetBackup XBSA Interface - 6.5  0
OK stuff...
15:05:13.557 [3960.2760] <4> CGlobalInformation::VCSVirtualNameList: INF - Veritas Cluster Server is not installed.
15:05:13.557 [3960.2760] <1> CGlobalInformation::VCSVirtualNameList:     CONTINUATION: - The system cannot find the path specified.
15:05:13.557 [3960.2760] <4> dbc_GetServerName: entering dbc_GetServerName.
15:05:13.557 [3960.2760] <4> getServerName: Read server name from nb_master_config: mdrbupmed01.bup.sdp.net.nz
15:05:13.557 [3960.2760] <4> CDBIniParms::CDBIniParms: INF - NT User is SYSTEM
15:05:18.572 [3960.2760] <4> SQLEnumerator: INF - Enumerated SQL hosts: SERVER:Server={(local),MDRSRWSQL01N2,MDRSRWSQL01V1,MDRSRWSQL01V2\BSPPROD,MDRSRWSQL01V3\BSPSTAGING,MDRSRWSQL01V4\ESB,MDRSRWSQL03N1,MDRSRWSQL03V1};UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;*APP:AppName=?;*WSID:WorkStation ID=?;
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - Hosts and instances retrieved from host list string
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: MDRSRWSQL01N2
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: <default>
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: MDRSRWSQL01V1
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: <default>
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: MDRSRWSQL01V2
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: BSPPROD
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: MDRSRWSQL01V3
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: BSPSTAGING
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: MDRSRWSQL01V4
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: ESB
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: mdrsrwsql03n1
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: <default>
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF - host: MDRSRWSQL03V1
15:05:18.572 [3960.2760] <4> CGlobalInformation::SQLEnumerator: INF -      instance: <default>
15:05:18.572 [3960.2760] <16> CDBbackmain::UpdateBatchFileForAllInstances: USER - An instance of SQL Server was not found on this client.
15:05:18.572 [3960.2760] <4> CDBbackmain::dbbackup: INF - Results of executing <C:\Program Files\VERITAS\NetBackup\DbExt\MsSql\all_full.bch>:

<0> operations succeeded. <0> operations failed.
15:05:38.572 [3960.2760] <4> CGlobalInformation::DSNCleanUp: INF - SQLDataSources did not fetch any ODBC user data source names
15:05:38.572 [3960.2760] <4> VxBSATerminate: INF - entering VxBSATerminate.
15:05:38.572 [3960.2760] <4> VxBSAGetEnv: INF - entering GetEnv - NBBSA_DEBUGFD
15:05:38.572 [3960.2760] <4> VxBSAGetEnv: INF - returning -

The actual node and virtual server are highlighted in blue.

Any ideas on what I'm doing wrong?

Thanks in advance

[Colin_North]

Hi Dion,
I had similar issues backing up SQL clusters. Below is a sample .bch file - try editing yours with different settings and run some test backups.

The NBU SQL admin guide is a good place to start as well, lots of sample .bch files in there.

Good luck :)

OPERATION BACKUP
DATABASE $ALL
SQLHOST "MYSERVER"
SQLINSTANCE "MYINSTANCE"
SQLINSTANCE "MYINSTANCE2"
NBSERVER "MYSERVER"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
BROWSECLIENT "MYSERVER"
ENDOPER TRUE

[Dion]

Hi Colin - Thanks for the response

I can get backups to work using the HOSTNAME, SQLINSTANCE and BROWSECLIENT entries in the .bch file but doing it this way means I have to manage multiple files and Policies per cluster group.  I'm running an environment with over 30 SQL clusters which have multiple virtual SQL cluster groups with multiple instances.

Because the files are different between virtual SQL clusters, they have to be named differently thereby forcing you to have different policies per SQL file.  If you take into account that you have to have separate (different) files and separate policies for different backup types e.g. Full, Incr and Tx logs and a file for each virtual SQL cluster group this is at least 30 x 4 (90) .bch files and 90 extra policies to manage.  If someone adds a new instance to any server (or removes one) without me knowing, I have a problem as it won't be picked up dynamically.

I was hoping to use the INSTANCE $ALL entry to lessen this potential problem to some degree.

Thanks again

[Colin_North]

Dion,

Have you spoken to Symantec Tech Support regarding this one?

[Dion]

Yip - just started.  I will post a message on the outcomes of our findings.  Hopefully it will be something that can be usefull to everyone.

Cheers

[Dion]

Just an update

Symantec got back to me just over a week ago with the following.

The SQLINSTANCE $ALL will only work on a standalone machine with multiple SQL instances and is not designed to work on either a MSCS or Veritas cluster.  The function makes a call to the physical "host" name and enumerates all SQL instances attached to that name - it does not enumerate SQL instances attached to a cluster group name.

Cheers

[Omar_Villa]

Unfortunally for clusters you cannot use the $ALL directives, check on the guide:

ftp://exftpp.symantec.com/pub/support/products/NetBackup_Enterprise_Server/290212.pdf

page 123, you will see that it needs the instance and hostname under the script other way netbackup dont know which instance r u trying to backup, this is been since 4.5, so for every DB that you add to your instances you will need to touch you .bch files and add the new DB.

regards