How to install Microsoft SQL Client Libraries Using CFEngine

CFEngine is an IT infrastructure automation framework that helps engineers, system admins, and other stakeholders in an IT organization manage IT infrastructure while ensuring service levels and compliance.
We use CFEngine to solve one of the many problems within the automation for deploying Microsoft SQL Server client utilities. We will take a dive into CFEngine syntax and try to program (well, in configuration-management terminology declare the state of system).

What does it take to install Microsoft SQL Server client libraries using CFEngine?

You need two things to achieve this:
1. Microsoft SQL Server client installers
2. CFEngine understanding – we will learn this as we write the policy.

Microsoft SQL Server 2008 R2 Native Client

Let’s try to install the native client for the 64-bit system. The installer is available here. So we first need to download the installer and use it to install the application. Let’s break it down into smaller tasks to achieve this and figure out how to do the same using CFEngine.

Basically we need to figure out two things here:
1. How to download the installer file from the URL?
2. How to use the downloaded file and invoke the installer.

CFEngine defines a term ”promise” to describe the final state of a resource or part of a system. All such promises are written into a file referred to as ”policy file”. CFEngine has support for a large number of ”promise types” that can help you achieve day-to-day infrastructure tasks such as creating users or files with specific attributes, installing packages, etc.

CFEngine has its own language syntax, known asDSL, that helps you define how to automate the system. All these are well described in the documentation. The things we need to know are variables, bundles (think of these as methods, i.e., group of promises) and classes (think of these as events or conditionals). Then there is ”ordering” that defines the flow of execution, which is mostly the implied ordering though you can have explicitly defined ordering using ”depends_on”.

Well, I feel that I have described the whole CFEngine language in two paragraphs, which are going to be hard to understand unless you read CFEngine docs! But even if you do read them, these paragraphs should help you follow it with a real-life example.

Jumping back on above breakup of tasks, let’s have a look at how to download the installer .msi file from a web URL. The URLs will be different for different versions of SQL Server client and architecture.
Let’s define some variables using classes (as conditions)

x86_64.2008R2.native_client::
"package_url" string => "http://download.microsoft.com/download/B/6/3/B63CAC7F-44BB-41FA-92A3-
CBF71360F022/1033/x64/sqlncli.msi";
"msi_name" string => "sqlncli";
"msi_args" string => "IACCEPTSQLNCLILICENSETERMS=YES";
"package_name" string => "Microsoft SQL Server 2008 R2 Native Client";

The above CFEngine code defines a string variables initialized to values under the condition (using classes) that we are targeting 64-bit x86 systems and trying to install “Microsoft SQL Server 2008 R2 Native Client”. Note here that ‘x86_64’ is one CFEngine class; ‘2008R2’ is another class. You can define and initialize different values for these variables under other conditions, say x86.2008R2.native_client:: for 32-bit x86 systems.

So the next question is how do we define these classes?

“class_name_in_quotes” expression => condition based on classes or functions

The above CFEngine code defines a string variables initialized to values under the condition (using classes) that we are targeting 64-bit x86 systems and trying to install “Microsoft SQL Server 2008 R2 Native Client”. Note here that ‘x86_64’ is one CFEngine class; ‘2008R2’ is another class. You can define and initialize different values for these variables under other conditions, say x86.2008R2.native_client:: for 32-bit x86 systems.

So the next question is how do we define these classes?

“class_name_in_quotes” expression => condition based on classes or functions

Before we get into defining our classes lets write a definition for bundle (think of it as writing a method) that takes a few input arguments.

# isserver - 0/1
# architecture - x86/x86_64
# mssqlversion - sql version 2008R2/2012
# type - native_client, cli, clr_types, management_objects, sql_powershell_ext
bundle agent ensure(runenv, metadata, isserver, purge, architecture, mssqlversion, installer_type)
{
   …
}

I hope this is self explanatory; just note that bundles are a CFEngine way of grouping a set of promises that may or may not take arguments. Logically, bundles can hold variables, classes, or methods in order to define a state of the system in a certain context.
Let’s come back to defining classes required for our solution: classes can be based on system state or bundle arguments.

So this is how we can define classes we require.

bundle agent ensure(runenv, metadata, isserver, purge, architecture, mssqlversion, installer_type)
{
  classes:
    "$(mssqlversion)" expression => "any";
    "$(architecture)" expression => "any";
    "$(installer_type)" expression => "any";
…

We are defining our classes to be named the same as the argument values; for example, parameter architecture can be set as ‘x86_64’, and the argument mssqlversion can be set as ‘2008R2’. These are defined in ‘any’ case, but one can have conditional expression as well. For example, define a soft class, i.e., user defined class, ‘starnix’ if current platform is either Linux or Solaris, where Linux is another class (hard class) already defined by CFEngine.

"starnix" expression => "linux||solaris";

Download

Now that we have the basics, let’s write a bundle to download the installer from a web URL. Since we are doing this on Windows, we have two options to download the package from the Internet, using WScript or using powershell cmdlets. For using WScript we will have to write a script and trigger it via the CFEngine ‘commands’ promise. But using Powershell, the script will be very short and elegant compared to older WScript style.
Here is how we do the download:

bundle agent download_from_url(url, localpath)
{
  classes:
    "already_downloaded" expression => fileexists("$(localpath)");
  reports:
    already_downloaded::
      "File is present at $(localpath)."
      classes => if_repaired("download_success");
    !already_downloaded::
      "Downloading from $(url) to $(localpath)";
  commands:
    !already_downloaded.windows::
      "(new-object System.Net.WebClient).DownloadFile('$(url)', '$(localpath)')"
        contain => pscontainbody,
        classes => if_repaired("download_success");
  reports:
    !already_downloaded.download_success::
      "Package was downloaded successfully from $(url) into $(localpath).";
    !already_downloaded.!download_success::
      "Package download failed from $(url).";
}
body contain pscontainbody
{
  useshell => "powershell";
}

Note above that we are only trying to download if it is not already downloaded–a condition that is set by defining a class ‘already_downloaded’ using a CFEngine function fileexists() in an expression.

The ‘commands’ promise helps trigger a dos/powershell/unix command. The command we use is creating an object of ‘System.Net.WebClient’ class in powershell and calling DownloadFile() method to download the installer from a web URL. Note that we have to escape quotes to keep CFEngine happy and delimit at proper places. Additionally, if the download was successful, define a new class to indicate the condition that the download was successful. This is achieved using classes => if_repaired(“download_success”)
Another important CFEngine concept used here is the ‘body’, which can help modularize the specification of attributes. We just use it to define the ‘useshell’ attribute; for larger examples see this.

Install

For installation we have to use the installer in silent, non-interactive mode. This can be achieved by using ‘/qn’ flag to “msiexec.exe”.
Here is how we can perform the install.

bundle agent install_using_msi(installer, install_log, msi_args)
{
  reports:
    "Installing Package using $(installer)";
  commands:
    windows::
      "Start-Process -FilePath "msiexec.exe" -ArgumentList '/qn /log $(install_log) /
i $(installer) $(msi_args)' -Wait -Passthru"
        contain => pscontainbody,
        classes => if_repaired("installed_package");
}

This should be easy to understand now; just note that we are reusing the ‘body’ concept here in the form of ‘pscontainbody’ that was defined during the download bundle definition previously. The Start-Process cmdlet with ‘–Wait’ option helps in running the installation in a synchronous manner.

Now that we know how to download and install using bundles, we need to invoke these in order within the “ensure” bundle we looked at above, while defining variables. For this we will use the ”methods” promise type.

methods:
    "fetch" usebundle => download_from_url("$(package_url)", "$(local_temp_dir)$(msi_name).msi");
    !purge.download_success::
      "install" usebundle => install_using_msi("$(local_temp_dir)$(msi_name).msi",
"$(local_temp_dir)install_$(msi_name).log", "$(msi_args)");

The methods promises are named ‘fetch’ and ‘install’ and invoke the download_from_url and install_using_msi bundles respectively, passing the variable values. The “install” promise is evaluated only if the download was successful which is flagged using “download_success” CFEngine class.

Given here is the complete source code for installing various SQL Server client utilities.

# Copyright:: Copyright (c) 2014 Clogeny Technologies.
#
# License:: Apache License, Version 2.0
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file
# except in compliance with the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the
# License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
# either express or implied. See the License for the specific language governing permissions
# and limitations under the License.
body common control
{
  inputs => { "c:Program FilesCfengineinputslibrariescfengine_stdlib.cf" };
  bundlesequence => { "install_sql_client", "install_sql_cli", "install_sql_clr_types",
"install_sql_management_objects", "install_sql_powershell_ext" };
#  bundlesequence => { "uninstall_sql_powershell_ext", "uninstall_sql_management_objects",
"uninstall_sql_clr_types", "uninstall_sql_cli", "uninstall_sql_client" };
}
################ TEST SPECS ################
bundle agent install_sql_client
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "0", "x86_64", "2008R2", "native_client"),
      classes => if_repaired("installed_sqlclient");
  reports:
    installed_sqlclient::
      "Installed Microsoft SQL Server 2008 R2 Native Client successfully.";
}
bundle agent install_sql_cli
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "0", "x86_64", "2008R2", "cli"),
      classes => if_repaired("installed_sqlcli");
  reports:
    installed_sqlcli::
      "Installed Microsoft SQL Server 2008 R2 Command Line Utilities successfully.";
}
bundle agent install_sql_clr_types
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "0", "x86_64", "2008R2", "clr_types"),
      classes => if_repaired("installed_sql_clr_types");
  reports:
    installed_sql_clr_types::
      "Installed Microsoft SQL Server System CLR Types (x64) successfully.";
}
bundle agent install_sql_management_objects
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "0", "x86_64", "2008R2",
"management_objects"),
      classes => if_repaired("installed_sql_management_objects");
  reports:
    installed_sql_management_objects::
      "Installed Microsoft SQL Server 2008 R2 Management Objects (x64) successfully.";
}
bundle agent install_sql_powershell_ext
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "0", "x86_64", "2008R2",
"sql_powershell_ext"),
      classes => if_repaired("installed_sql_powershell_ext");
  reports:
    installed_sql_powershell_ext::
      "Installed Windows PowerShell Extensions for SQL Server 2008 R2 successfully.";
}
####### UNINSTALL ##########
bundle agent uninstall_sql_client
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "1", "x86_64", "2008R2", "native_client"),
      classes => if_repaired("uninstalled_sqlclient");
  reports:
    uninstalled_sqlclient::
      "UnInstalled Microsoft SQL Server 2008 R2 Native Client successfully.";
}
bundle agent uninstall_sql_cli
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "1", "x86_64", "2008R2", "cli"),
      classes => if_repaired("uninstalled_sqlcli");
  reports:
    uninstalled_sqlcli::
      "UnInstalled Microsoft SQL Server 2008 R2 Command Line Utilities successfully.";
}
bundle agent uninstall_sql_clr_types
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "1", "x86_64", "2008R2", "clr_types"),
      classes => if_repaired("uninstalled_sql_clr_types");
  reports:
    uninstalled_sql_clr_types::
      "UnInstalled Microsoft SQL Server System CLR Types (x64) successfully.";
}
bundle agent uninstall_sql_management_objects
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "1", "x86_64", "2008R2",
"management_objects"),
      classes => if_repaired("uninstalled_sql_management_objects");
  reports:
    uninstalled_sql_management_objects::
      "UnInstalled Microsoft SQL Server 2008 R2 Management Objects (x64) successfully.";
}
bundle agent uninstall_sql_powershell_ext
{
  methods:
    "any" usebundle => ensure("runenv", "metadata", "0", "1", "x86_64", "2008R2",
"sql_powershell_ext"),
      classes => if_repaired("uninstalled_sql_powershell_ext");
  reports:
    uninstalled_sql_powershell_ext::
      "UnInstalled Windows PowerShell Extensions for SQL Server 2008 R2 successfully.";
}
################ ACTUAL CODE ################
# isserver - 0/1
# architecture - x86/x86_64
# mssqlversion - sql version 2008R2/2012
# type - native_client, cli, clr_types, management_objects, sql_powershell_ext
bundle agent ensure(runenv, metadata, isserver, purge, architecture, mssqlversion, installer_type)
{
  classes:
    "server" expression => strcmp($(isserver), "1");
    "client" expression => strcmp($(isserver), "0");
    "purge" expression => strcmp($(purge), "1");
    "$(mssqlversion)" expression => "any";
    "$(architecture)" expression => "any";
    "$(installer_type)" expression => "any";
  vars:
    "local_temp_dir" string => execresult("$env:temp", "powershell");
    # ************ x86_64 configurations ************
    x86_64.2008R2.native_client::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x64/sqlncli.msi";
      "msi_name" string => "sqlncli";
      "msi_args" string => "IACCEPTSQLNCLILICENSETERMS=YES";
      "package_name" string => "Microsoft SQL Server 2008 R2 Native Client";
    x86_64.2008R2.cli::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x64/SqlCmdLnUtils.msi";
      "msi_name" string => "SqlCmdLnUtils";
      "msi_args" string => "";
      "package_name" string => "Microsoft SQL Server 2008 R2 Command Line Utilities";
    x86_64.2008R2.clr_types::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x64/SQLSysClrTypes.msi";
      "msi_name" string => "SQLSysClrTypes";
      "msi_args" string => "";
      "package_name" string => "Microsoft SQL Server System CLR Types (x86_64)";
    x86_64.2008R2.management_objects::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x64/SharedManagementObjects.msi";
      "msi_name" string => "SQLSharedManagementObjects";
      "msi_args" string => "";
      "package_name" string => "Microsoft SQL Server 2008 R2 Management Objects (x86_64)";
    x86_64.2008R2.sql_powershell_ext::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x64/PowerShellTools.msi";
      "msi_name" string => "SQLPowerShellTools";
      "msi_args" string => "";
      "package_name" string => "Windows PowerShell Extensions for SQL Server 2008 R2";
    # ************ x86 configurations ************
    x86.2008R2.native_client::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x86/sqlncli.msi";
      "msi_name" string => "sqlncli";
      "msi_args" string => "IACCEPTSQLNCLILICENSETERMS=YES";
      "package_name" string => "Microsoft SQL Server 2008 R2 Native Client";
    x86.2008R2.cli::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x86/SqlCmdLnUtils.msi";
      "msi_name" string => "SqlCmdLnUtils";
      "msi_args" string => "";
      "package_name" string => "Microsoft SQL Server 2008 R2 Command Line Utilities";
    x86.2008R2.clr_types::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x86/SQLSysClrTypes.msi";
      "msi_name" string => "SQLSysClrTypes";
      "msi_args" string => "";
      "package_name" string => "Microsoft SQL Server System CLR Types (x86)";
    x86.2008R2.management_objects::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x86/SharedManagementObjects.msi";
      "msi_name" string => "SQLSharedManagementObjects";
      "msi_args" string => "";
      "package_name" string => "Microsoft SQL Server 2008 R2 Management Objects (x86)";
    x86.2008R2.sql_powershell_ext::
      "package_url" string => "http://download.microsoft.com/download/B/6/3/
B63CAC7F-44BB-41FA-92A3-CBF71360F022/1033/x86/PowerShellTools.msi";
      "msi_name" string => "SQLPowerShellTools";
      "msi_args" string => "";
      "package_name" string => "Windows PowerShell Extensions for SQL Server 2008 R2";
  methods:
    "fetch" usebundle => download_from_url("$(package_url)", "$(local_temp_dir)$(msi_name).msi");
    !purge.download_success::
      "install" usebundle => install_using_msi("$(local_temp_dir)$(msi_name).msi", "$(local_temp_dir)
install_$(msi_name).log", "$(msi_args)");
    purge.download_success::
      "uninstall" usebundle => uninstall_using_msi("$(local_temp_dir)$(msi_name).msi",
"$(local_temp_dir)uninstall_$(msi_name).log", "$(msi_args)");
  reports:
    installed_package::
      "Installed Package successfully.";
}
bundle agent download_from_url(url, localpath)
{
  classes:
    "already_downloaded" expression => fileexists("$(localpath)");
  reports:
    already_downloaded::
      "File is present at $(localpath)."
      classes => if_repaired("download_success");
    !already_downloaded::
      "Downloading from $(url) to $(localpath)";
  commands:
    !already_downloaded.windows::
      "(new-object System.Net.WebClient).DownloadFile('$(url)', '$(localpath)')"
        contain => pscontainbody,
        classes => if_repaired("download_success");
  reports:
    !already_downloaded.download_success::
      "Package was downloaded successfully from $(url) into $(localpath).";
    !already_downloaded.!download_success::
      "Package download failed from $(url).";
}
body contain pscontainbody
{
  useshell => "powershell";
}
bundle agent install_using_msi(installer, install_log, msi_args)
{
  reports:
    "Installing Package using $(installer)";
  commands:
    windows::
      "Start-Process -FilePath "msiexec.exe" -ArgumentList '/qn /log $(install_log) /i $(installer)
$(msi_args)' -Wait -Passthru"
        contain => pscontainbody,
        classes => if_repaired("installed_package");
}
bundle agent uninstall_using_msi(installer, uninstall_log, msi_args)
{
  reports:
    "Uninstalling Package using $(installer)";
  commands:
    windows::
      "Start-Process -FilePath "msiexec.exe" -ArgumentList '/qn /log $(uninstall_log) /x $(installer)
 $(msi_args)' -Wait -Passthru"
        contain => pscontainbody,
        classes => if_repaired("uninstalled_package"),
        comment => "Uninstalling Package using $(installer)";
}