homeblogpuppetize your microsoft sql server pains

#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.

*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:

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

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.


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.

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.


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:

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.

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:

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