Shree Khanal – Architect’s Blog

Ms SQL Server Consultant

Installing SQL Backup on Multiple Servers using SQL Multi Script

One of the common requests we receive is how to quickly install the SQL Backup 5 server components remotely on a large number of servers. While the SQL Backup 5 User Interface supports remote installation, this can only be performed on a single server at a time, which can be time consuming when working with tens or even hundreds of servers.
Using the script provided in this article, the techniques utilised by the SQL Backup 5 User Interface, and SQL Multi Script, it is possible to perform an ‘unattended’ remote installation. This will allow you to install or upgrade your SQL Backup server components across the network in one go, rather than installing the components manually. Furthermore, these techniques can be used to collate versioning, licensing and installation information about the SQL Backup server component installations into one easy-to-read grid.
The Script
You can download the complete script from the box above, to the right of the article title. The following sections describe what is going on “under the hood” of the script, and how SQL Backup provides the information to automate this task.
1) Information Gathering
To be able to install the SQL Backup server components successfully, we first need to check that the machine meets the necessary criteria, namely that it is running SQL Server 2000 or 2005 in a non-clustered environment. While SQL Backup does support clustering, installation on a cluster is beyond the scope of this script.
The script performs a couple of preliminary checks to ensure that these criteria are met:

-- Establish the current SQL Server major version (e.g. 8, 9, 10).
SET @SqlProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR);
SET @SqlMajorVersion = CAST(SUBSTRING(@SqlProductVersion, 1, CHARINDEX('.', @SqlProductVersion) - 1) AS INT);

-- Establish the clustering status ('1' means clustered, '0' means non-clustered, NULL means unknown)
SET @SqlIsClustered = CAST(SERVERPROPERTY('IsClustered') AS VARCHAR(1));

IF @SqlMajorVersion >= 8 AND @SqlMajorVersion <=9 AND (@SqlIsClustered = '0')
We also gather the server and instance names, for the purpose of reporting and installation:
SET @MachineName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128));
IF @MachineName IS NULL SET @MachineName = '';

SET @InstanceName = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(128));
IF @InstanceName IS NULL SET @InstanceName = '';

SET @CombinedName = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(128));
IF @CombinedName IS NULL SET @CombinedName = '';

2) Gathering Existing Server Component Status
Once it has been established that the installation is allowed, the script gathers some benchmark information, by which it can ensure that the install or upgrade was successful.
The following three commands use the utility function ‘sqbutility’ to extract version information for xp_sqlbackup.dll (the extended stored procedure) and SQBCoreService.exe (the SQL Backup Agent Service), as well as the current license type and key.
These are inserted into a temporary table called #SqbOutput in order to eliminate the result grid that would otherwise be returned to the caller (and would then clutter up the SQL Multi Script output).
INSERT #SqbOutput EXECUTE master..sqbutility 30, @OldDllVersion OUTPUT;
INSERT #SqbOutput EXECUTE master..sqbutility 1030, @OldExeVersion OUTPUT;
INSERT #SqbOutput EXECUTE master..sqbutility 1021, @OldLicenseVersionId OUTPUT, NULL, @SerialNumber OUTPUT;
The @OldLicenseVersionId doesn't have any meaning as-is so, using a CASE statement, this is converted into a human readable form:
SELECT @OldLicenseVersionText =
CASE WHEN @OldLicenseVersionId = '0' THEN 'Trial: Expired'
WHEN @OldLicenseVersionId = '1' THEN 'Trial'
WHEN @OldLicenseVersionId = '2' THEN 'Standard'
WHEN @OldLicenseVersionId = '3' THEN 'Professional'
WHEN @OldLicenseVersionId = '6' THEN 'Lite'

3) Application Installation
The next part of the script attempts the actual installation. To do this we need to use the xp_cmdshell extended stored procedure, which must explicitly be turned on in SQL Server 2005 or later.
Once this has been done, we can perform a check to see if the installer file exists; if it doesn’t then it will be impossible to proceed. This check is performed using the shell syntax “IF EXIST “.
SET @SqbFileExistsExec = 'if exist ' + @DownloadDirectory + '\SqbServerSetup.exe time';
INSERT #SqbOutput EXECUTE master..xp_cmdshell @SqbFileExistsExec;

We return the value of ‘time’ as a single line output, rather than using ‘echo’ which returns a newline character (and hence two lines of output).
The installation needs to be performed silently, using the /VERYSILENT and /SUPPRESSMSGBOXES flags discussed below. We can supply other flags to configure the installer as necessary:


April 14, 2009 Posted by | Uncategorized | Leave a comment