#Puppetize Your Microsoft SQL Server Pains

Following on the heels of our Windows module pack announcement, I’m pleased to announce that our Microsoft SQL Server module is now available for Puppet Enterprise customers.

Any user of Microsoft SQL Server knows there are many working parts to this powerful product. Along with this comes the difficulty of installing and managing the server, databases and logins. We built this module as part of our on-going effort to simplify operations for Windows admins because MS SQL is such a core requirement for many Microsoft products.

Installing a SQL Server instance manually is a long and arduous process. Our module is designed to make this process easier. We have broken the installation into two separate pieces: sqlserver_instance which are features not shared across named instances, such as the SQL Engine, Reporting Services and Data Quality and common sqlserver_features such as SQL Server Management Studio, SDK and Connectivity, which are only installed once per server. This separation allows for multiple instances to be installed per server.

$source = ‘\\mysharedresource\sqlserver2014_iso\’
sqlserver_instance{‘MSSQLSERVER’:
  source   => $source,
  features => [‘SQL’],
}
sqlserver_instance{‘SESSIONDB’:
  source               => $source,
  features             => [‘SQL’],
  security_mode  => ‘SQL’,
  sa_pwd             => ‘MyS!11yP@ss{}rd’,
}
sqlserver_features{‘Need the tools’:
  source    => $source,
  features => [‘Tools’],
}

*Note we have excluded some required fields for simplicity in the example above.

In this example we are installing two instances, a default which is ‘MSSQLSERVER’ and a named instance ‘SESSIONDB’. Also, we are installing the Tools feature, which is a superset that includes the Full SQL Server Management Studio Suite. Another thing you may have noticed is that our ‘SESSIONDB’ instance has a parameter for security_mode. The ‘SQL’ security mode is really a mixed mode provided by SQL Server to allow for both Windows and SQL Logins. We require the security_mode to be set to SQL in order to perform the following:

sqlserver::login
sqlserver::database
sqlserver::sp_configure

To specify the SQL login to use to modify this data, you use the following example:

sqlserver::config{‘SESSIONDB’:
  admin_user  => ‘sa’,
  admin_pass  => ‘MyS!11yP@ss{}rd’,
}

This will store and allow us to modify all data. We prefer this over a Windows domain login for security reasons. For example, if a domain user is passed as the admin, you have now given everybody who can read your manifests access to ALL of your servers.

##Logins

We never want to duplicate efforts when we create logins for each instance. Puppet core has a defined user type for this very reason. With the SQL Server module, we allow for both SQL and Windows Logins to be managed so you can quickly give applications the proper access to your server.

sqlserver::login{‘MyWebServerAppLogin’:
  instance  => ‘SESSIONDB’,
  password  => ‘ASuperDub3rP@ssw()rd1_’,	
}
sqlserver::login{‘nexus\MyDbaUser’:
  instance    => ‘SESSIONDB’,
  login_type  => ‘WINDOWS_LOGIN’,
  svrroles    => { ‘sysadmin’ => 1 },
}

As you can see from the example above, we have the ability to specify passwords for our SQL logins, pass in our Domain User and specify that they have a server role of Systems Administrator.

##Databases

Another common task for devs is creating and destroying database instances. We have parameters for such features as filestream, log, filespec, compatibility and containment to help with the most common installation and creation needs. But let’s get into some more examples:

sqlserver::database{‘session_store’:
  instance => ‘SESSIONDB’,
}

The example above shows the most basic thing you can do with a database.

##Server Level Configurations

Another great feature we have as a part of this first release is the ability to set server configurations found within the SQL Server stored procedure ‘sp_configure’. These configurations include things like ‘default full-text language’, ‘contained database authentication’, ‘remote query timeout’ as well as many others as documented in: http://msdn.microsoft.com/en-us/library/ms189631.aspx.

Now say we wanted to enable Filestream access on our server. This is different from most things we change in sp_configure.

$instance = ‘MSSQLSERVER’
$command = “ \$wmi=Get-WmiObject -namespace ‘root\Microsoft\SqlServer\ComputerManagement12’ -class FILESTREAMSettings | where   {\$_.InstanceName -eq ‘${instance}’} \$wmi.EnableFILESTREAM(3,‘${instance}’)”

sqlserver::sp_configure{‘filestream access level’:
  value       => 1,
  reconfigure => true
}
exec{‘Change Filestream’:
  provider    => powershell,
  command     => $command,
  refreshonly => true,
  subscribe   => Sqlserver::Sp_configure[‘filestream access level’],
}->
reboot{‘Change Requires Reboot’:}

As you can see, this will allow us to perform some of the typical manual steps to enable Filestream, combining sqlserver with our other supported modules.

Now to put it all together for a full end-to-end installation and setup:

$target_file = 'C:/SQLServer2014-x64-ENU.iso'
#Download the file to the target location, allowing for a large timeout due to it typically being 2.4+ GB
pget{'GetSQLISO':
  source  => 'http://mywebresource.local/SQLServer2014-x64-ENU.iso',
  target  => 'C:/',
  timeout => 10000,
}

#The ISO Must have execute rights on it so make sure our service has rights to mount it
acl{$target_file:
  require      => Pget[‘GetSQLISO’],
  permissions  => [  
    {identity => 'Administrators', rights => ['full'] }, 
  ],
}

#This is a beta module to mount the ISO at a given location
mount_iso{$target_file:
  drive_letter  => 'H',
  require       => Acl[$target_file], 
}

#Now we will install the Tools features.
sqlserver_features{'Generic':
  source   => 'H:',
  features => ['Tools',’SDK’],
  require  => Mount_iso[$target_file],
}

At this point, we now have the tools to do our development work as an easy-to-install script.

This first release reflects the most common use cases and pain points for our customers. We’ll be adding more features and granular controls in subsequent releases, including a TSQL provider and more. We'd love to hear what you think, so please leave a comment.

Learn More

Puppet sites use proprietary and third-party cookies. By using our sites, you agree to our cookie policy.