Unattended installation of SQL Server 2012

• Download SQLInstall.zip - 4.7 KB

Introduction

As DBA, DevOps or Developer person, the unattended installation or so-called "silent" installation of Microsoft SQL Server Database Engine is a good sample of tasks from Development and Operations (named briefly DevOps), where Automation is the bread of every day.

This article describes an automated script of a installer for SQL Server 2012 using Powershell tool. The versions of operating system involved are Windows Server 2012 and Windows Server 2012 R2 and it has been tested in real critical production environments.

If you want to review manual installation, search on this site for an article called "Steps to install Microsoft SQL Server 2008 Express Edition (Standard or with Advanced Services)".

Background

Basically, general specifications of the silent or unattend installation script was presented in an article called "SQL Server Silent unattended installation using configuration file". But, it is very useful to get some help when a good recipe is given to taste the sucessful installation with minimal customization or to download and after, to execute without any modifications. The idea is to present an alternative way to perform automation tasks not only for DBA also is presented to Developers, DevOps and SysAdmin Roles, where you need to execute an override of custom configurations and it is necessary to execute several times the installation.

Pre-requisites

All pre-requisites work in Microsoft Windows Server 2012 and Microsoft Windows Server 2012 R2 environment, but they may be applied to Windows Desktops editions (Windows 8.1 and Windows 10). Whatever is the case, the steps must be executed with the Administrator account.

Steps

1. First, check if the following components are really installed (and tested) at least. Each component might carries on a restart of the machine.

 1.1. .NET FrameWork 3.5. (Figure 1 and Figure 2)

 1.2. Windows PowerShell 1.0  (Figure 3)

Run Installation

This script assumes that:

1. SQL Server Media Drive is allocated on drive D:
2. You must to change the current account administrative user to your machine's user administrative account specification. Here is named: HELLIUM02\Administrator
3. SQL Server Installation File is named "ConfigurationFile.ini".
4. All files must be in the same directory, here named C:\SQLInstall. So, you must create C:\SQLInstall.

The script will create:

• An Account for SQL Server service
• The Database Engine root data directory is C:\DATA
• A testdbv1 database for testing purpose.
• A login user to connect to the SQL Server database engine instance.

The source code written and run in powershell is depicted below:

# Version 2.0
# Date - 07/06/2016
# Author: Jesus Carroll
Clear-Host

<#*************Variable Declaration******************#>

# Script: Perform a preinstallation of Unattended SQL Server 2008 and 2012 Install, Uninstall SQL. Setup requirements
# Author: Jesus Carroll
# Version: 2.0
# Date - 07/06/2016

<#***********************************************START:Pre-installation Program********************************************#>
Write-Host "START Create DATA directory on C drive"
New-Item c:\DATA -type directory -force
Write-Host "END Create DATA directory"

Write-Host "START Create Account for SQL Server services"

$Computer = [ADSI]"WinNT://$Env:COMPUTERNAME,Computer"
$LocalAdmin = $Computer.Create("User", "services")
$LocalAdmin.SetPassword("Sqls2rv3")
$LocalAdmin.SetInfo()


Write-Host "END Create Account for SQL Server services"

<#***********************************************END:Pre-installation Program********************************************#>                

<#Get Date#>
[datetime]$unow = Get-Date
Write-Host "START[SQL Install Script]: SQL INSTALL PROGRAM MAIN"
Write-Host $unow

$scriptblock={D:\Setup.exe /IACCEPTSQLSERVERLICENSETERMS /ConfigurationFile=C:\SQLInstall\ConfigurationFile.ini}
Try
{
    Invoke-Command -scriptblock $scriptblock

    Write-Host "END [SQL Install Script]: Finished SQL INSTALL PROGRAM"

    <#***********************************************START:Post-installation Program********************************************#>
    # Change the Execution Policy to enable SQL Server Module.
    Write-Host "START Change the Execution Policy to enable SQL Server Module"
    Set-ExecutionPolicy Unrestricted
    Write-Host "END   Change the Execution Policy to enable SQL Server Module"

    Write-Host "****************************************************"
    Write-Host "START Check presence of SQL Server PowerShell Module"
    Copy-Item "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS" -Destination "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\SQLPS" -Recurse
    Write-Host "END   Check presence of SQL Server PowerShell Module"
    Write-Host "****************************************************"

    # Wait to start slowest SQL Server machines
    Write-Host "START Wait to start slowest SQL Server machines"
    Start-Sleep -Seconds 5
    Write-Host "END Wait to start slowest SQL Server machines"

    # Import the SQL Server Module.
    
    Write-Host "START Perform customized SQL Server Engine"
    Import-Module "sqlps" -DisableNameChecking
    Set-Location SQLSERVER:\SQL

    Invoke-Sqlcmd "EXEC sys.sp_configure N'user options', N'512'; RECONFIGURE WITH OVERRIDE;"
    Invoke-Sqlcmd "CREATE LOGIN [user1] WITH PASSWORD=N'User2016', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;"
    Invoke-Sqlcmd "ALTER SERVER ROLE [sysadmin] ADD MEMBER [user1];"

    Write-Host "END Performed customized SQL Server Engine"

    # Create a sample database without data.

    Write-Host "START Create a sample database testdbv1"

    Invoke-Sqlcmd "USE master;
    IF EXISTS (SELECT * FROM sys.databases where name ='testdbv1')
    BEGIN
        DROP DATABASE testdbv1
        SELECT * FROM sys.databases
    END;
    CREATE DATABASE [testdbv1]
         CONTAINMENT = NONE
         ON  PRIMARY
        ( NAME = N'testdbv1', FILENAME = N'C:\DATA\testdbv1.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
         LOG ON
        ( NAME = N'testdbv1_log', FILENAME = N'C:\DATA\testdbv1_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10MB)
    ;"
    Invoke-Sqlcmd "use master;
        IF EXISTS (SELECT * FROM sys.databases where name ='testdbv1')
        BEGIN
            ALTER DATABASE [testdbv1] SET COMPATIBILITY_LEVEL = 110
        
            ALTER DATABASE [testdbv1] SET ANSI_NULL_DEFAULT OFF
        
            ALTER DATABASE [testdbv1] SET ANSI_NULLS OFF
        
            ALTER DATABASE [testdbv1] SET ANSI_PADDING OFF
        
            ALTER DATABASE [testdbv1] SET ANSI_WARNINGS OFF
        
            ALTER DATABASE [testdbv1] SET ARITHABORT OFF
        
            ALTER DATABASE [testdbv1] SET AUTO_CLOSE OFF
        
            ALTER DATABASE [testdbv1] SET AUTO_CREATE_STATISTICS ON
        
            ALTER DATABASE [testdbv1] SET AUTO_SHRINK OFF
        
            ALTER DATABASE [testdbv1] SET AUTO_UPDATE_STATISTICS ON
        
            ALTER DATABASE [testdbv1] SET CURSOR_CLOSE_ON_COMMIT OFF
        
            ALTER DATABASE [testdbv1] SET CURSOR_DEFAULT  GLOBAL
        
            ALTER DATABASE [testdbv1] SET CONCAT_NULL_YIELDS_NULL OFF
        
            ALTER DATABASE [testdbv1] SET NUMERIC_ROUNDABORT OFF
        
            ALTER DATABASE [testdbv1] SET QUOTED_IDENTIFIER OFF
        
            ALTER DATABASE [testdbv1] SET RECURSIVE_TRIGGERS OFF
        
            ALTER DATABASE [testdbv1] SET  DISABLE_BROKER
        
            ALTER DATABASE [testdbv1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
        
            ALTER DATABASE [testdbv1] SET DATE_CORRELATION_OPTIMIZATION OFF
        
            ALTER DATABASE [testdbv1] SET PARAMETERIZATION SIMPLE
        
            ALTER DATABASE [testdbv1] SET READ_COMMITTED_SNAPSHOT ON
        
            ALTER DATABASE [testdbv1] SET  READ_WRITE
        
            ALTER DATABASE [testdbv1] SET RECOVERY SIMPLE
        
            ALTER DATABASE [testdbv1] SET  MULTI_USER
        
            ALTER DATABASE [testdbv1] SET PAGE_VERIFY CHECKSUM  
        
            ALTER DATABASE [testdbv1] SET TARGET_RECOVERY_TIME = 0 SECONDS

            ALTER DATABASE [testdbv1] SET ALLOW_SNAPSHOT_ISOLATION ON
        END;"

    Invoke-Sqlcmd "USE [testdbv1];
    IF NOT EXISTS (SELECT name FROM
                   sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
                   ALTER DATABASE [testdbv1] MODIFY FILEGROUP [PRIMARY] DEFAULT;
    "

    Invoke-Sqlcmd "SELECT * FROM sys.databases where name ='testdbv1';"
    Write-Host "****************************************************"
    Write-Host "END Create a sample database testdbv1"
    Write-Host "****************************************************"
    Write-Host " "



    <#***********************************************END:Post-installation Program********************************************#>
}
Catch
{
    Write-Host -ForegroundColor Red "Error Setup SQL Server:"$_.Exception.Message                    
}

Source code of both files - powershell script and ini configuration file - are attached to this article.

Conclusions and Further Improvements

This script doesn't to attempt an issue of a not-controlled reboot on production servers, so this helps to keep online business continuity. The prerrequisites and Windows updates packages must be installed before to apply a succesful execution. In this installation, this is achieved thru the Setup process and it should discover and include product updates [1]. Keep Online is a "must be" principle when organizations don't have a cluster implementation. As Colin Dembovsky appoints at the reference [2] Rebooting during installs is always tricky though. Sometimes there is just no alternative..

References

• [1] Microsoft Technet: https://technet.microsoft.com/en-us/library/dd239405(v=sql.110).aspx
• [2] Colin Dembovsky's Devops blog: http://www.colinsalmcorner.com/post/install-and-configure-sql-server-using-powershell-dsc