Loading...
Loading...
There is an issue with Avamar SQL backup and database browsing in both SQL standalone and cluster environments. Case Scenario Back up and Browse issue using the avsql plug-in from the Avamar Administrator Console GUI or AUI. Backup error codes: avsql Error <15760> : No targets after expansion! When this error is present in the backups, it indicates that a browse issue and database information are not being fetched using the avsql services. Avamar SQL (avsql) backup or database browse fails with "avsql Error <15760>: No targets after expansion!" Avsql based backup and browse operation fails for SQL Standalone server and cluster clients.
There are several possible causes for this behavior. Missing sysadmin roles for the user account or Active directory user (SQL 2012 and above) used under backup agent services. Connection issue between SQL SMO and Avamar SQL plug-in. WMI services connection issue between the SQL server and Windows services. Avamar communication ports no accessible. Missing SMO dll files or other required files which function to fetch the Database information.
The following steps may be followed to mitigate the backup and browse issues in standalone and cluster configurations: SQL standalone servers Missing sysadmin roles for the user account used under backup agent services. Check the KB article on how to assign sysadmin roles: KB 000039635 Avamar: SQL backup failing with "BACKUP DATABASE permission denied in database" error Configure full control permissions for AD account used for SQL server 2012 and above. More detailed information is on Page 11 of Avamar SQL user guide V19.9 Required SMO files are missing for the SQL server services. The SMO installed version can be verified from control panel>>program and features. If the SMO version related to the SQL Server version is missing, download and install it from the Microsoft site To download SMO package for various versions of SQL SQL 2008 and 2008 R2 SMO/CLR download: https://www.microsoft.com/en-us/download/details.aspx?id=44272 SQL 2012 R2 SMO/CLR download: https://www.microsoft.com/en-us/download/details.aspx?id=29065 SQL 2014 SMO/CLR download: https://www.microsoft.com/en-us/download/details.aspx?id=42295 SQL 2016 SMO/CLR download: https://www.microsoft.com/en-us/download/details.aspx?id=54279 SQL 2017 and later SMO are distributed by Microsoft, as the "Microsoft.SqlServer.SqlManagementObjects" and it is updated through NUGet. NuGet Gallery | Microsoft.SqlServer.SqlManagementObjects 170.18.0 Nuget package installation instructions: To learn more about how to install the Nuget package review: https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo?view=sql-server-ver15 Avamar SQL (avsql) services unable to connect to SQL SMO files present on SQL due to a mixed environment KB article to follow: KB 000051925 Avamar SQL plugin failed to browse SQL instance in a mixed SQL environment Run the below command to verify if the SQL instance is being browsed. avsql.exe --debug --operation=browse --verbose Following flag syntax --usesmoversion="SMO version" may be used to verify which flag can be used to allow force connection between SMO and avsql services: Example for SQL 2016: avsql.exe --debug --operation=browse --verbose --usesmoversion=13 Run the following command to see if the SQL Databases are being browsed: avsql --operation=browse (local) Test the connectivity between SMO and SQL server using the below KB: KB 000156447 Avamar - How to test connectivity between SMO and SQL Server. The below steps can be followed to test the connectivity using the PowerShell commands: Remote Desktop to the SQL client. Open Powershell. Enter the following statement: [reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") Enter the following statement: $SQLServer = new-object ("Microsoft.SQLServer.Management.SMO.Server") Enter the following statement: foreach($SQLDatabase in $SQLServer.databases) {$SQLDatabase.name} This results in a list of the databases on the client. This confirms that the SMO is loaded properly and is connecting to SQL Server. If it results in an error, this must be resolved before Avamar can succeed. Review the cmd outputs of avsql.exe --debug --operation=browse --verbose and look for SQL services-related errors: If a WMI connection error is found, go to log in to the SQL configuration tool. If the SQL configuration tool fails to open with the following message "Cannot connect to WMI provider. If permission is denied or the server is unreachable, engage SQL Admins to resolve the issue. Microsoft article: https://learn.microsoft.com/en-US/troubleshoot/sql/tools/error-message-when-you-open-configuration-manager https://www.sqlnethub.com/blog/how-to-resolve-cannot-connect-to-wmi-provider-sql-server-configuration-manager Depending on the Bit version of SQL server, the below flag can be first tested with CLI-based browse and later added to avsql.cmd if needed to allow browse operation: Example: avsql.exe --debug --operation=browse verbose --provider-architecture=64bit Ensure the SQL browser services and named pipes are enabled under the SQL configuration tool, SQL network protocol for the particular SQL server instance. Ensure the required smo.dll files are present under c:\programfiles\avs\bin on the client version. Verify the SQL server version in use to check for the required version of the smo.dll file. SQL Active/passive or Always-On cluster Log in to the primary or the owner node of the cluster configuration Steps 1 - 9 from SQL standalone server troubleshooting can be reviewed and followed from the owner node of the cluster. The command used to perform CLI-based browse to fetch SQL instance information: avsql --operation=browse --sqlserver=SQLCluster_name --hostnamesql=SQLCluster_name Ensure communication between the cluster client IP and Avamar browsing ports 28002 and 28003 (or 30002 and 30003) If there is a suspected communication issue between the SQL cluster client and SQL listener, go to add the following flag in avsql.cmd under the shared var location: --sqlserver=listenerIP,listenerPortnumber Note: The above details are present in SQL Studio tool >>High availability>>SQL instance>>Availability Group listener>>listener and right-click for properties. Add cluster node flags in avsql.cmd file located under shared var location to specify individual SQL servers for communication errors "Unable to connect to the remote client '<IP_ADDRESS>', error code: 2. Ensure that the remote backup agent is running: KB 000070983 Avamar: SQL backup for dual-homed AlwaysON cluster is failing with error "Unable to connect to the remote client '', error code: 2. Please ensure that the remote backup agent is running. --clusternode=<SQL_node_name>(<IP_ADDRESS>) Review the paging port used for the cluster client from Avamar GUI>>Navigation>>Policy>> Select the client and edit. Add 280002/28003, followed by deactivating the client, changing the startup parameters mentioned below, and reactivating the cluster client. Replace --disable-gui and add --listenport=28002 or 28003 in the cluster startup parameters found under Failover cluster tool>>Select the role used>> Right-click properties on backup agent services for the cluster. Example: Before changes: --service --mcsaddr=coeavr01.coe.int --mcsport=28001 --dpndomain=clients --vardir="E:\Program Files\Backup Agents for Cluster Groups\COEWINFILE\var" --logfile="E:\ProgramFiles\Backup Agents for Cluster Groups\COEWINFILE\var\avagent.log" --sysdir="E:\Program Files\Backup Agents for Cluster Groups\COEWINFILE\etc" --netbind=. --disablegui=true --pin_include=windows.pin,sql.pin After changes: --service --mcsaddr=coeavr01.coe.int --mcsport=28001 --dpndomain=clients --vardir="E:\Program Files\Backup Agents for Cluster Groups\COEWINFILE\var" --logfile="E:\Program Files\Backup Agents for Cluster Groups\COEWINFILE\var\avagent.log" --sysdir="E:\Program Files\Backup Agents for Cluster Groups\COEWINFILE\etc" --netbind=. --listenport=28002 --pin_include=windows.pin,sql.pin The below flag can be added in avsql.cmd under the shared var location to list the SQL databases at the node level: --show_db_in_availability_group=true
Click on a version to see all relevant bugs
Dell Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.