SQLServer 2008R2 unattended installation and configuration via powershell

My first steps on Powershell

Ok, Ok, as an “Oracle on Linux Certified Expert”, I’ve never been a great fan of SQLServer (I shouldn’t say this, I’m working on SQLServer since release 6.5…) and I’ve always hated the DOS command prompt.
However, things are changing fast after Microsoft released the Powershell some years ago. It’s surprising, now Windows powershell support new best of breed features like aliases and pipelines. ūüėÄ

Today Microsoft itself recommends Windows Core installations instead of the full ones, and also SQLServer 2012 comes with a lot of new Commandlets to manage your server.

So I’ve decided to move my first steps in the Powershell world and I’ve created a script for a customer that installs and configure a SQL2008 with a single Powershell script.

It has been quite painful to complete, you can download the complete script HERE.

Taking parameters

The very first line accepts named parameters. I’ve tried to reduce the number but I’ve preferred to take, as an example, different disks for different tasks.

Then I’ve put a little of interaction if some parameters are missing.¬†In facts, I can launch my scripts without inline parameters and specify everything when prompted by the script.

Before I prompt for the drive letters for the installation paths, I display a little table with the available local disks:

 

 Installing prerequisites

The commented command is to get the installed features after the installation. No really need to display it, it works really well.

 Dynamically prepare a configuration file

The unattended installation needs some parameters prepared in a configuration file.
This is likely where you will change most of your stuff depending on your standards:
Components, paths, service accounts, you can change everything or modify the script to accept also this variables as parameters.

The full documentation about filling the configuration file is on the MSDN:

http://msdn.microsoft.com/en-us/library/ms144259%28v=sql.105%29.aspx

Starting the SQL Server 2008 R2 installation

Off course you’ll need an installation media downloaded from the Microsoft customers site with the correct License Keys and mounted somewhere.¬† (remember the $sourceDir parameter?) I’ve decided to change the path in the directory containing the media and then change it back.

 

Launching the Service Pack installation

The Service Pack installation has been a little more painful, normally would be simple but actually the powershell prompt is returned immediately after firing the command. So, to wait it, I’ve had to figure out the name of the process (is the executable name without the file extension .exe), get its process id and wait for that process:

 Changing the TCP port

By default SQLServer starts listening on a dynamic port. If you have a default and you want to configure it without opening the configuration manager, you can do it with this snipplet that I’ve copied from sirSql (thank you for sharing this).

Adding your stuff at the end

Having the installation completed is in midstream. After the installation you may want to add tempfiles to your installation, modify your model database, add default accounts.

That’s up to you. If your scripts are identical you can execute them with sqlcmd.If you want to take benefit of the variables already set in the script you can execute them directly:

Putting all together…

Well, I’ll never paste again all the content here, you can download the script HERE. Just change the file extension from .txt to .ps1.

I know it’s not a rock-solid procedure but it works well for my purposes, feel free to comment or review my script. Just, if you do some improvement on it, please share it and back-link this post!

Cheers

The following two tabs change content below.

Ludovico

Oracle ACE and Senior Consultant at Trivadis SA
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Senior Database Specialist for Trivadis, Switzerland.

10 thoughts on “SQLServer 2008R2 unattended installation and configuration via powershell

  1. I tried executing the script but some how im not able to… not sure what im missing… can you give me a eg command line to invoke or execute the script in powershell… since im a beginner im not sure how to invoke this script…

  2. This script has lot of features. But it is not working. Do we need to set the envirionment or any pre requisites before running it in PS 2.0?

  3. Hi Ramki, thank you for your feedback.
    Actually I use it without special environments, just start it from the PS console.
    Which kind of error do you get?

    Ludovico

  4. Excellent and clean script compared to SPADE. Does it work with sql 2012?
    Due to some reason, service pack install and adding SQL Snapins didn’t work.
    I am trouble shooting it.

  5. Yes, with modifications.
    For 2012 the command for the installation changes a little:

    $installCmd = “.\setup.exe /q /ACTION=Install /IACCEPTSQLSERVERLICENSETERMS /SQLSVCPASSWORD=””$syncSvcAccountPassword”” /AGTSVCPASSWORD=””$syncSvcAccountPassword”” /SAPWD=””$saPassword”” /ConfigurationFile=””$configFile”””

    also, the configfile should contain:
    [OPTIONS]
    instead of
    [SQLSERVER2008]
    and should NOT contain:
    ACTION=””Install””
    and
    IACCEPTSQLSERVERLICENSETERMS=””True””
    They can be passed only through the commandline.

    Cannot post it now completely, I have to do some clean up first.

  6. Great script, was checking how to change from vbs scripts to powershell and this one is a good start for me. Thanks!!

  7. Any new ideas for automated installof SQl server? Can we use VHD for on premise instals instaed of uploading to Azure?

  8. The best and simplest script for automating SQL Server install via Powershell.

Leave a Reply

Your email address will not be published. Required fields are marked *