Installation silencieuse SQL Server 2008 R2 – Partie 2
Bonjour,
Dans la première partie de cette série d’article, je vous expliquait les grandes lignes d’une installation silencieuse de SQL Server.
Dans cette seconde et dernière partie, vous allez découvrir une beta du script que j’utilise pour déployer from scratch mes SQL Server en Lab. Ne pas utiliser ce script en production, la gestion des erreurs n’est pas assez pointue et ne vous garantie pas une résolution d’erreurs rapides.
J’utilise aussi des sources splistreamée pour SQL Server standard, c’est pour ça que je n’installe pas le SP2, contrairement à la version Express.
Par défaut
- ce script à besoin d’un disque D car il installe tous les fichiers de bases de données sur ce disque, mais vous pouvez très bien modifier ces chemins.
- Il faudra changer les password pour le compte ‘sysadmin’ pour les devs ainsi que le nom du login.
- Il fera un tuning léger de l’instance
- Il configure l’instance avec un port statique (pour alléger les règles de conf firewall)
- Creation des comptes locaux pour les services et attribution des droits (dans la prochaine version (finale) il y aura création dans le domaine)
- Ce script doit être lancé Administrateur en workgroup
- Je vous laisse découvrir la suite
<#
.SYNOPSIS
This Script install SQL Server 2008 SP2 32 Bits Express or Standard lab patforms
.DESCRIPTION
This script:
– Create service account
– Create directories
– Install SQL Server
– Tune the instance
– Create account for dev
.PARAMETER Computer
The hostname of the computer/server where you install the database server
.PARAMETER Features
The features that you want to install with SQL Server.
.Parameter sourcesDir
The directory where you can find the setup.exe to install SQL Server
.Parameter scriptdir
The directory where you can find the scripts to install SQL Server
.Parameter TCPport
The listening port
.PARAMETER SAPWD
SA Password
.PARAMETER SQLUser
dev sysadmin sql account
.PARAMETER TempDBSize
The size of a TempDB file
.PARAMETER TempDBMaxSize
The maximum size of a tempDB file .PARAMETER TempDBFileGrowth The growhth of the temp db files .PARAMETER Express This switch will launch the installation of SQL Exress (if not specified, it install SQL Server Standard) .PARAMETER SVC_Pwd This is the password for services account
.NOTE
Author: Fabien Dibot – @fdibot – posh.netau.net
Version 2.0
.EXAMPLE
C:PS> Install_SQL.ps1 -Express
.EXAMPLE
C:PS> Install_SQL.ps1
#>
param (
[Parameter(Mandatory=$false)]
[String]$computer = $env:computername,
[Parameter(Mandatory=$false)]
[String]$sourcesDir = “D:Sources”,
[Parameter(Mandatory=$false)]
[String]$scriptdir = “D:Scripts”,
[Parameter(Mandatory=$false)]
[String]$instanceName = “MSSQLSERVER”,
[Parameter(Mandatory=$false)]
[Int]$TCPport = 1433,
[Parameter(Mandatory=$false)]
[String]$SAPWD = ‘<put_your_pass_here>’,
[Parameter(Mandatory=$false)]
[String]$SQLUser = “<put_your_account_here>”, #the other SA account (for devs)
[Parameter(Mandatory=$false)]
[String]$TempDBSize = “256MB”,
[Parameter(Mandatory=$false)]
[String]$TempDBMaxSize = “512MB”,
[Parameter(Mandatory=$false)]
[String]$TempDBFileGrowth = “64MB”,
[Parameter(Mandatory=$false)]
[Switch]$Express,
[Parameter(Mandatory=$false)]
[String]$SVC_Pwd = ‘Sql$2008’,
[Parameter(Mandatory=$false)]
[String]$DebugPreference = “Continue”,
[Parameter(Mandatory=$false)]
[String]$SystemPath = “D:SQL_SystemDB”,
[Parameter(Mandatory=$false)]
[String]$SystemPathx86 = “D:SQL_SystemDBx86”,
[Parameter(Mandatory=$false)]
[String]$TempDBPath = “D:SQL_TempDB”,
[Parameter(Mandatory=$false)]
[String]$BackupPath = “D:SQL_Backup”,
[Parameter(Mandatory=$false)]
[String]$LogsPath = “D:SQL_Log”,
[Parameter(Mandatory=$false)]
[String]$DataPath = “D:SQL_Data”
)
#Statics variables
$rights = @(‘SeTcbPrivilege’, ‘SeIncreaseQuotaPrivilege’, ‘SeCreateTokenPrivilege’, ‘SeImpersonatePrivilege’, ‘SeBatchLogonRight’, ‘SeServiceLogonRight’, ‘SeAssignPrimaryTokenPrivilege’)
#, ‘SeManageVolumeNamePrivilege’, ‘SeLockMemoryPrivilege’, ‘SeChangeNotifyPrivilege’)
$ResponseFileNameExpress = $scriptdir + “settings_Express.ini”
$ResponseFileNameStandard = $scriptdir + “settings_Standard.ini”
$settings = ” /CONFIGURATIONFILE=”
$SQLService = ‘MSSQL$’ + $instanceName
$BrowserService = ‘SQLBrowser’
$FDService = ‘MSSQLFDLauncher$’ + $instancename
$SQLServerAgent = ‘SQLAgent$’ + $instanceName
$Services = @($SQLService,$BrowserService,$FDService,$SQLServerAgent)
$HKML_IPall = “HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLServerSuperSocketNetLibTcpIPAll”
$SetupSP2 = $sourcesDir + “EXPSQLServer2008SP2-KB2285068-x86-ENU.exe”
$paramSP2 = “/Q /ACTION=PATCH /INSTANCENAME=” + $instanceName
$QuerySQLAccount = “Create Login [$SQLUser] WITH PASSWORD=N'<put_your_pass_here>’, DEFAULT_DATABASE=[master]”
$QuerySQLRole = “EXEC sp_addsrvrolemember @loginame = N’$SQLUser’, @rolename = N’sysadmin'”
$CheckUser = “SELECT count(*) FROM sys.server_principals WHERE name = ‘$env:computername$SQLUser'”
$QueryMaxMemory = “EXEC sp_configure ‘show advanced options’, ‘1’
RECONFIGURE WITH OVERRIDE
EXEC sp_configure ‘min server memory’, ‘0’
EXEC sp_configure ‘max server memory’, ‘4096’
RECONFIGURE WITH OVERRIDE
EXEC sp_configure ‘show advanced options’, ‘0’
RECONFIGURE WITH OVERRIDE”
$QueryMaxMeroySetted = “select value FROM sys.configurations where Name = ‘max server memory (MB)'”
$TempDBadd = “USE [master]
GO
DECLARE @cpu_count int,
@file_count int,
@logical_name sysname,
@file_name nvarchar(520),
@physical_name nvarchar(520),
@size int,
@max_size int,
@growth int,
@alter_command nvarchar(max)
SELECT @physical_name = physical_name,
@size = size / 128,
@max_size = max_size / 128,
@growth = growth / 128
FROM tempdb.sys.database_files
WHERE name = ‘tempdev’
SELECT @file_count = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = ‘ROWS’
SELECT @cpu_count = cpu_count
FROM sys.dm_os_sys_info
WHILE @file_count < @cpu_count — Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
BEGIN
SELECT @logical_name = ‘tempdev’ + CAST(@file_count AS nvarchar)
SELECT @file_name = REPLACE(@physical_name, ‘tempdb.mdf’, @logical_name + ‘.ndf’)
SELECT @alter_command = ‘ALTER DATABASE [tempdb] ADD FILE ( NAME =N”’ + @logical_name + ”’, `
FILENAME =N”’ + @file_name + ”’, SIZE = ‘ + CAST(@size AS nvarchar) + ‘MB, MAXSIZE = ‘ + `
CAST(@max_size AS nvarchar) + ‘MB, FILEGROWTH = ‘ + CAST(@growth AS nvarchar) + ‘MB )’
PRINT @alter_command
EXEC sp_executesql @alter_command
SELECT @file_count = @file_count + 1
END”
$TempDBmodify = “ALTER DATABASE tempdb MODIFY FILE ( NAME = N’tempdev’, SIZE = ” + $TempDBSize + “, MAXSIZE = ” + $TempDBMaxSize + “, FILEGROWTH = ” + $TempDBFileGrowth + ” )”
$TempLogDBmodify = “ALTER DATABASE tempdb MODIFY FILE ( NAME = N’templog’, SIZE = ” + $TempDBSize + “, MAXSIZE = ” + $TempDBMaxSize + “, FILEGROWTH = ” + $TempDBFileGrowth + ” )”
$ResponseFileContentExpress = ‘[SQLSERVER2008]
INSTANCEID=”MSSQLSERVER”
ACTION=”Install”
FEATURES=”SQLENGINE,SSMS”
HELP=”False”
INDICATEPROGRESS=”False”
QUIET=”True”
QUIETSIMPLE=”false”
;X86=”False”
;ENU=”True”
MEDIASOURCE=”‘ + $sourcesDir + ‘EXP”
ERRORREPORTING=”False”
SQMREPORTING=”False”
INSTANCENAME=”MSSQLSERVER”
SQLSVCSTARTUPTYPE=”Automatic”
ENABLERANU=”True”
SQLCOLLATION=”French_CI_AS”
SQLSVCACCOUNT=”‘ + $env:computername + ‘svc_sqls”
SQLSYSADMINACCOUNTS=”Administrator”
ADDCURRENTUSERASSQLADMIN=”True”
TCPENABLED=”1″
NPENABLED=”0″
INSTALLSQLDATADIR=”‘ + $SystemPath + ‘”
SQLBACKUPDIR=”‘ + $BackupPath + ‘”
SQLUSERDBDIR=”‘ + $DataPath + ‘”
SQLUSERDBLOGDIR=”‘ + $LogsPath + ‘”
SQLTEMPDBDIR=”‘ + $TempDBPath + ‘”
SECURITYMODE=”SQL”
SAPWD=’ + $SAPWD + ‘
SQLSVCPASSWORD=’ + $SVC_Pwd + ‘”
INSTALLSHAREDDIR=’ + $SystemPathx86 + ‘”
INSTANCEDIR=’ + $SystemPath + ‘”
BROWSERSVCSTARTUPTYPE=”Automatic”‘
$ResponseFileContentStandard ='[SQLSERVER2008]
INSTANCEID=”MSSQLSERVER”
ACTION=”Install”
FEATURES=”SQLENGINE,SSMS,ADV_SSMS,IS”
HELP=”False”
INDICATEPROGRESS=”False”
QUIET=”True”
QUIETSIMPLE=”False”
MEDIASOURCE=”‘ + $sourcesDir + ‘STD”
ERRORREPORTING=”False”
INSTALLSHAREDDIR=’ + $SystemPathx86 + ‘”
INSTANCEDIR=’ + $SystemPath + ‘”
SQMREPORTING=”False”
INSTANCENAME=”MSSQLSERVER”
AGTSVCACCOUNT=”‘ + $env:computername + ‘svc_sqla”
AGTSVCSTARTUPTYPE=”Automatic”
AGTSVCPASSWORD=’ + $SVC_Pwd + ‘”
ISSVCSTARTUPTYPE=”Automatic”
ISSVCACCOUNT=”‘ + $env:computername + ‘svc_sqli
ISSVCPASSWORD=’ + $SVC_Pwd + ‘”
SQLSVCSTARTUPTYPE=”Automatic”
SQLCOLLATION=”French_CI_AS”
SQLSVCACCOUNT=”‘ + $env:computername + ‘svc_sqls”
SQLSYSADMINACCOUNTS=”Administrator”
SQLSVCPASSWORD=’ + $SVC_Pwd + ‘”
SECURITYMODE=”SQL”
ADDCURRENTUSERASSQLADMIN=”False”
TCPENABLED=”1″
NPENABLED=”0″
SAPWD=’ + $SAPWD + ‘
BROWSERSVCSTARTUPTYPE=”Automatic”
INSTALLSQLDATADIR=”‘ + $SystemPath + ‘”
SQLBACKUPDIR=”‘ + $BackupPath + ‘”
SQLUSERDBDIR=”‘ + $DataPath + ‘”
SQLUSERDBLOGDIR=”‘ + $LogsPath + ‘”
SQLTEMPDBDIR=”‘ + $TempDBPath + ‘”
PCUSOURCE=”‘ + $sourcesDir + ‘STDpcu”‘
$LSAType = @’
using System;
using System.Collections.Generic;
using System.Text;
namespace MyLsaWrapper
{
using System.Runtime.InteropServices;
using System.Security;
using System.Management;
using System.Runtime.CompilerServices;
using System.ComponentModel;
using LSA_HANDLE = IntPtr;
[StructLayout(LayoutKind.Sequential)]
struct LSA_OBJECT_ATTRIBUTES
{
internal int Length;
internal IntPtr RootDirectory;
internal IntPtr ObjectName;
internal int Attributes;
internal IntPtr SecurityDescriptor;
internal IntPtr SecurityQualityOfService;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)]
struct LSA_UNICODE_STRING
{
internal ushort Length;
internal ushort MaximumLength;
[MarshalAs(UnmanagedType.LPWStr)]
internal string Buffer;
}
sealed class Win32Sec
{
[DllImport(“advapi32”, CharSet = CharSet.Unicode, SetLastError = true),
SuppressUnmanagedCodeSecurityAttribute]
internal static extern uint LsaOpenPolicy(
LSA_UNICODE_STRING[] SystemName,
ref LSA_OBJECT_ATTRIBUTES ObjectAttributes,
int AccessMask,
out IntPtr PolicyHandle
);
[DllImport(“advapi32”, CharSet = CharSet.Unicode, SetLastError = true),
SuppressUnmanagedCodeSecurityAttribute]
internal static extern uint LsaAddAccountRights(
LSA_HANDLE PolicyHandle,
IntPtr pSID,
LSA_UNICODE_STRING[] UserRights,
int CountOfRights
);
[DllImport(“advapi32”, CharSet = CharSet.Unicode, SetLastError = true),
SuppressUnmanagedCodeSecurityAttribute]
internal static extern int LsaLookupNames2(
LSA_HANDLE PolicyHandle,
uint Flags,
uint Count,
LSA_UNICODE_STRING[] Names,
ref IntPtr ReferencedDomains,
ref IntPtr Sids
);
[DllImport(“advapi32”)]
internal static extern int LsaNtStatusToWinError(int NTSTATUS);
[DllImport(“advapi32”)]
internal static extern int LsaClose(IntPtr PolicyHandle);
[DllImport(“advapi32”)]
internal static extern int LsaFreeMemory(IntPtr Buffer);
}
/// <summary>
/// This class is used to grant “Log on as a service”, “Log on as a batchjob”, “Log on localy” etc.
/// to a user.
/// </summary>
public sealed class LsaWrapper : IDisposable
{
[StructLayout(LayoutKind.Sequential)]
struct LSA_TRUST_INFORMATION
{
internal LSA_UNICODE_STRING Name;
internal IntPtr Sid;
}
[StructLayout(LayoutKind.Sequential)]
struct LSA_TRANSLATED_SID2
{
internal SidNameUse Use;
internal IntPtr Sid;
internal int DomainIndex;
uint Flags;
}
[StructLayout(LayoutKind.Sequential)]
struct LSA_REFERENCED_DOMAIN_LIST
{
internal uint Entries;
internal LSA_TRUST_INFORMATION Domains;
}
enum SidNameUse : int
{
User = 1,
Group = 2,
Domain = 3,
Alias = 4,
KnownGroup = 5,
DeletedAccount = 6,
Invalid = 7,
Unknown = 8,
Computer = 9
}
enum Access : int
{
POLICY_READ = 0x20006,
POLICY_ALL_ACCESS = 0x00F0FFF,
POLICY_EXECUTE = 0X20801,
POLICY_WRITE = 0X207F8
}
const uint STATUS_ACCESS_DENIED = 0xc0000022;
const uint STATUS_INSUFFICIENT_RESOURCES = 0xc000009a;
const uint STATUS_NO_MEMORY = 0xc0000017;
IntPtr lsaHandle;
public LsaWrapper()
: this(null)
{ }
// // local system if systemName is null
public LsaWrapper(string systemName)
{
LSA_OBJECT_ATTRIBUTES lsaAttr;
lsaAttr.RootDirectory = IntPtr.Zero;
lsaAttr.ObjectName = IntPtr.Zero;
lsaAttr.Attributes = 0;
lsaAttr.SecurityDescriptor = IntPtr.Zero;
lsaAttr.SecurityQualityOfService = IntPtr.Zero;
lsaAttr.Length = Marshal.SizeOf(typeof(LSA_OBJECT_ATTRIBUTES));
lsaHandle = IntPtr.Zero;
LSA_UNICODE_STRING[] system = null;
if (systemName != null)
{
system = new LSA_UNICODE_STRING[1];
system[0] = InitLsaString(systemName);
}
uint ret = Win32Sec.LsaOpenPolicy(system, ref lsaAttr,
(int)Access.POLICY_ALL_ACCESS, out lsaHandle);
if (ret == 0)
return;
if (ret == STATUS_ACCESS_DENIED)
{
throw new UnauthorizedAccessException();
}
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY))
{
throw new OutOfMemoryException();
}
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public void AddPrivileges(string account, string privilege)
{
IntPtr pSid = GetSIDInformation(account);
LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1];
privileges[0] = InitLsaString(privilege);
uint ret = Win32Sec.LsaAddAccountRights(lsaHandle, pSid, privileges, 1);
if (ret == 0)
return;
if (ret == STATUS_ACCESS_DENIED)
{
throw new UnauthorizedAccessException();
}
if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY))
{
throw new OutOfMemoryException();
}
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
}
public void Dispose()
{
if (lsaHandle != IntPtr.Zero)
{
Win32Sec.LsaClose(lsaHandle);
lsaHandle = IntPtr.Zero;
}
GC.SuppressFinalize(this);
}
~LsaWrapper()
{
Dispose();
}
// helper functions
IntPtr GetSIDInformation(string account)
{
LSA_UNICODE_STRING[] names = new LSA_UNICODE_STRING[1];
LSA_TRANSLATED_SID2 lts;
IntPtr tsids = IntPtr.Zero;
IntPtr tdom = IntPtr.Zero;
names[0] = InitLsaString(account);
lts.Sid = IntPtr.Zero;
Console.WriteLine(“String account: {0}”, names[0].Length);
int ret = Win32Sec.LsaLookupNames2(lsaHandle, 0, 1, names, ref tdom, ref tsids);
if (ret != 0)
throw new Win32Exception(Win32Sec.LsaNtStatusToWinError(ret));
lts = (LSA_TRANSLATED_SID2)Marshal.PtrToStructure(tsids,
typeof(LSA_TRANSLATED_SID2));
Win32Sec.LsaFreeMemory(tsids);
Win32Sec.LsaFreeMemory(tdom);
return lts.Sid;
}
static LSA_UNICODE_STRING InitLsaString(string s)
{
// Unicode strings max. 32KB
if (s.Length > 0x7ffe)
throw new ArgumentException(“String too long”);
LSA_UNICODE_STRING lus = new LSA_UNICODE_STRING();
lus.Buffer = s;
lus.Length = (ushort)(s.Length * sizeof(char));
lus.MaximumLength = (ushort)(lus.Length + sizeof(char));
return lus;
}
}
public class LsaWrapperCaller
{
public static void AddPrivileges(string account, string privilege)
{
using (LsaWrapper lsaWrapper = new LsaWrapper())
{
lsaWrapper.AddPrivileges(account, privilege);
}
}
}
}
‘@
# ~~ Functions declaration
function Write-Log {
<#
.SYNOPSIS
This function write in log file all actions results
.PARAMETER ID
Usefull to get the error and documentation for it
.PARAMETER Status
This is the status of the action.. nothing complicated…
.PARAMETER Date
By default, it’ll be the running action date.
.PARAMETER Logdir
The directory where log file is stored
.PARAMETER Logfile
The name of the log file
.PARAMETER Server
By default the $env:computername, you can change it by any string you want.
.PARAMETER Message
The message you want to write in log file.
$_.Exception.Message
.PARAMETER Assembly
The assembly from where the errors happens.
$_.Exception.TargetSite.Module.Name
.PARAMETER Type
The Object Type which return the error.
$_.Exception.TargetSite.ReturnType.Name
.PARAMETER Stack
The list of all methods which have failed
$_.Exception.StackTrace
.EXAMPLE
Write-Log -ID 001 -msg $_.Exception.Message -Status Error -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
Write-Log -ID 002 -msg “All actionss are done.” -Status SuccessAudit -Quiet
Write-Log -ID 003 -Msg “This is a Warning” -Status Warning -Q
#>
param(
[Parameter(Mandatory=$true)]
[Int]$id,
[Parameter(Mandatory=$true)]
[ValidateSet(“Error”,”SuccessAudit”,”Warning”)]
[string]$Status,
[DateTime]$date = (Get-Date),
[ValidateScript({Test-Path $_ })]
[String]$logdir = “C:log”,
[String]$file = “log.txt”,
[String]$server = $env:computername,
[Parameter(Mandatory=$true)]
[String]$msg,
[String]$Assembly,
[String]$Type,
[String]$Stack,
[alias(‘Q’)]
[Switch]$Quiet
)
if (!(Test-Path $logdir)) {
New-Item -type Directory -Path $logdir | Out-Null
}
$Logfile = $logdir + $file
# In case of error trigger a specific process
# Write The error message, the assembly used, the stack…
if ($Status -eq “Error”) {
if (!$Quiet) { Write-Error -msg “$date $server : $Status : $msg” }
“$date : $server : $Status : $id : $msg” | Out-File $logfile -Append
“Assembly: $Assembly” | Out-File $logfile -Append
“Object Type: $Type” | Out-File $logfile -Append
“Call Stack: $Stack” | Out-File $logfile -Append
exit $id
}
# Sinon si un warning, just print the msg on host
elseif ($Status -eq “Warning”) {
“$date : $server : $Status : $id : $msg” | Out-File $logfile -Append
if (!$Quiet) { Write-Warning -msg “$CurrentDate $server : $Status : $msg” }
}
else {
# In case of Success, just print the line in log and print on host too if not quiet
“$date : $server : $Status : $id : $msg” | Out-File $logfile -Append
if (!$Quiet) { Write-Verbose -msg “$CurrentDate $server : $Status : $msg” }
}
}
function New-ServiceAccount {
param(
[Parameter(Mandatory=$True)]
[string]$Account,
[Parameter(Mandatory=$True)]
[string]$Description,
[Parameter(Mandatory=$false)]
[Array]$Group,
[Parameter(Mandatory=$false)]
[String]$label
)
$d = New-Object PSObject
$d.PsObject.TypeNames[0] = “PSSqlServerServiceAccount”
$d |
Add-Member -Name account -MemberType NoteProperty -Value $account -pass|
Add-Member -Name Description -MemberType NoteProperty -Value “Service account for $Description” -pass|
Add-Member -Name Group -MemberType NoteProperty -Value $Group -pass|
Add-Member -Name Label -MemberType NoteProperty -Value $label -pass
}
function New-SQLServerPath {
param(
[Parameter(Mandatory=$True)]
[string]$Path,
[Parameter(Mandatory=$True)]
[string]$Description,
[Parameter(Mandatory=$false)]
[String]$Label
)
$d = New-Object PSObject
$d.PsObject.TypeNames[0] = “PSSqlServerPath”
$d |
Add-Member -Name Path -MemberType NoteProperty -Value $Path -pass|
Add-Member -Name Description -MemberType NoteProperty -Value $Description -pass|
Add-Member -Name Label -MemberType NoteProperty -Value $label -pass
}
function Set-Permission {
param (
[Parameter(Mandatory=$true)]
[String]$path,
[Parameter(Mandatory=$true)]
[String]$Account
)
# Gather the current ACL port $Path
# and Add new for SQL Service.
# In order Backup and Restore works, it has to be done.
$ACLPath = Get-Acl $path
$SQLPermission = “$Account”,”FullControl”,”Allow”
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule $SQLPermission
$ACLPath.SetAccessRule($AccessRule)
$ACLPath | Set-Acl $path
}
Function Create-LocalUser {
# This function exists to create local user
param(
[Parameter(Mandatory=$true)]
[string]$user,
[Parameter(Mandatory=$true)]
[string]$password,
[Parameter(Mandatory=$true)]
[string]$Description,
[Parameter(Mandatory=$false)]
[Array]$Group,
[Parameter(Mandatory=$false)]
[Switch]$ChgPWDAtLogon,
[Parameter(Mandatory=$false)]
[switch]$NoChgPWD, #0x40
[Parameter(Mandatory=$false)]
[switch]$PWDNevExp, #0x10000
[Parameter(Mandatory=$false)]
[Switch]$Disable #0x2
)
$objOu = [ADSI]”WinNT://$env:computername”
$objUser = $objOU.Create(“User”, $user)
$objUser.setpassword($password)
$objUser.Setinfo()
$objUser.refreshcache()
$objUser.Put(“Description”,$Description)
$TempVar = $null
if ($ChgPWDAtLogon) {
$ObjUser.PasswordExpire = 1
}
elseif ($NoChgPWD -or $PWDNevExp) {
if ($NoChgPWD) { $TempVar = $TempVar + 0x40 }
if ($PWDNevExp) { $TempVar = $TempVar + 0x10000 }
}
elseif ($Disable) {
$TempVar = $TempVar + 0x2
}
else {
$TempVar = $null
}
$flag = $objUser.UserFlags.value -bor $TempVar
$objUser.put(“userflags”,$flag)
$objUser.Setinfo()
$objUser.refreshcache()
if ($group) {
$group | % {
$Dir = “WinNT://$env:computername/” + $_ + “,Group”
[ADSI]$group = [ADSI]$Dir
$UserPath = (“WinNT://$env:computername/$user”)
$group.add($UserPath)
}
}
Write-Debug “User $user created with $password password”
}
Write-Log -id 0001 -msg “Start Database installation on $env:computername” -status “SuccessAudit”
Write-Debug “Cleaning previous errors”
# As errors are stored in log file, we need to clear them before starting the script.
if ($error) {
$error.clear()
}
# Thos two custom object will help to improve the creation of users and paths
Write-Debug “Populate ServiceAccount custom object”
$Accounts = @(
New-ServiceAccount -Account “svc_sqls” -Description “SQL Server” -Group (‘Administrators’) -label “MSDE”;
New-ServiceAccount -Account “svc_sqla” -Description “SQL Server Agent”;
New-ServiceAccount -Account “svc_sqlb” -Description “SQL Server Browser”;
New-ServiceAccount -Account “svc_sqli” -Description “SQL Server Integration service”;
)
Write-Debug “Populate SQLServerPath custom Object”
$Paths = @(
New-SQLServerPath -Path $SystemPath -Description “SystemDisk” -label “SQL”;
New-SQLServerPath -Path $SystemPathx86 -Description “SystemDBDisk” -label “SQL”;
New-SQLServerPath -Path $BackupPath -Description “BackupDisk” -label “Backup”;
New-SQLServerPath -Path $DataPath -Description “UserDataDisk” -label “Datas”;
New-SQLServerPath -Path $LogsPath -Description “UserLogDisk” -label “SQL”;
New-SQLServerPath -Path $TempDBPath -Description “TempDBDisk” -label “SQL”;
)
Write-Debug “Creating directories”
$Paths | % {
Write-Debug “Test if Path ${$_.Path} exist”
# If path exists, maybe a SQL Server is already installed..
# In this case, we have a big problem of process 🙂
if (Test-Path $_.Path) {
Write-Log -id 0002 -msg “$($_.Path) already exist on the system. Skip creation for $($_.Description)” -status “Warning”
}
else {
Try {
Write-Debug “Creating $($_.Path)”
New-Item -Type Directory -Path $_.Path -ErrorAction Stop | Out-Null
Write-Log -id 0003 -msg “Folder $($_.Path) created for $($_.Description)” -status “Successaudit”
}
Catch {
Write-Debug “Error Creating $($_.Path)”
# The most common issues are disk doesn’t exist.
# A problem of rights will be strange as you have to be in Administrators group to launch this script.
Write-Log -id 0004 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
}
Try {
Write-Debug “COmpiling LSA rights C# addon”
# This line will compile $LSAType which contains c# code.
# Thanks to this we don’t have to use ntrights.exe to grant correct local privileges
Add-Type $LSAType -PassThru | Out-Null
Write-Log -id 0005 -msg “LSA library compilation” -status “Successaudit”
}
Catch {
Write-Log -id 0007 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
Write-Debug “Verify is some accounts exist on $env:computername”
# Check if previous installation haven’t split ouf service account.
# Important in case of Database migration from Express to Strandard
Try {
$GetSVC = (Get-WmiObject Win32_UserAccount -ErrorAction Stop | ?{ $_.Caption -match “svc_” -and $_.Domain -match “$computer” })
Write-Log -id 0008 -msg “WMI request to get local service accounts” -status “Successaudit”
}
Catch {
Write-Log -id 0009 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
if ($GetSVC) {
Write-Log -id 0010 -msg “svc_* account(s) already exist(s)” -status “Error”
}
else {
Write-Debug “Starting creation of service accounts”
$Accounts | % {
# We take each line of the array and create an user for each one.
Try {
#No SQL Agent or Integration Services) in express edition
Write-Debug “Creation of $($_.Account)”
Create-LocalUser -User $_.Account -Password $SVC_Pwd -NoChgPWD -PWDNevExp -Description $_.Description -Group $_.Group
Write-Debug “$($_.Account) created”
Write-Log -id 0011 -msg “$($_.Account) created” -status “SuccessAudit”
}
Catch {
Write-Log -id 0012 $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
Write-Debug “All service accounts created”
} # End Check accounts exist (or NOT specialy)
# Here we use $LSAType previsouly compiled
# SQL Server need specifics rights to be assigned to accounts in order to function smoothly
# $rights = @(‘SeTcbPrivilege’, ‘SeIncreaseQuotaPrivilege’, ‘SeCreateTokenPrivilege’, ‘SeImpersonatePrivilege’, ‘SeBatchLogonRight’, `
# ‘SeServiceLogonRight’, ‘SeAssignPrimaryTokenPrivilege’, ‘SeManageVolumeNamePrivilege’, ‘SeLockMemoryPrivilege’, ‘SeChangeNotifyPrivilege’)
# The account used to launch the SQL Service is already added to Administrators group
Write-Debug “Assign local privileges for service accounts”
Try {
Foreach ($Account in $Accounts) {
$Rights | % {
Write-Debug “Assign local privilege $($_) for $($Account.Account)”
# This function is create by the $LSAType compiled.
[void][MyLsaWrapper.LsaWrapperCaller]::AddPrivileges($($Account.Account), $_) | Out-Null
Write-Log -id 0013 -Status “SuccessAudit” -Msg “According $($_) to $($Account.Account)”
}
}
}
Catch {
Write-Log -id 0014 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
# Now we need to give correct rights on data drive and backup drive
# Thanks to this we can backup and restore database !
Write-Debug “Modify ACL on data and backup drive for ‘svc_sqls'”
# First get the drive names
Try {
# Start by a filter to only have an array with Datas & Backup drive(s)
$PathToMod = $Paths | ? { ($_.label -eq “Datas”) -or ($_.label -eq “Backup”) }
# Lets select the MSDE service account
$MSDEAccount = $Accounts | ? { $_.label -eq “MSDE” }
$PathToMod | % {
Try {
$PathRoot = [System.IO.Path]::GetPathRoot($_.Path);
Write-Debug “Add $($MSDEAccount.Account) to ACL for $($_.Path)”
Set-Permission -Account $MSDEAccount.Account -Path $_.Path
Write-Log -id 0015 -Status “SuccessAudit” -Msg “Add $($MSDEAccount.Account) to $($_.Path) drive”
}
Catch {
Write-Log -id 0016 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
}
Catch {
Write-Log -id 0017 -Status “Error” -Msg “Modify ACL.”
}
# Let’s launch the SQL Server silent install now
# We ‘ll use a response file generated by this script
# All variables are designed in this script.
# If you need to change statics one, please modify carefully the response file.
Write-Debug “Load the SQL Server silent install”
Try {
if ($Express) {
Try {
$setup = $sourcesDir + “EXPsetup.exe”
$ResponseFileContentExpress | Out-File $ResponseFileNameExpress -ErrorAction Stop
$SQLServerInstall = $setup + $settings + $ResponseFileNameExpress
Write-Debug “Generate response file”
Write-log -id 0018 -msg “SQL Server Express Response file generation” -status “SuccessAudit”
}
Catch {
Write-Log -id 0019 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
else {
Try {
$setup = $sourcesDir + “STDsetup.exe”
$ResponseFileContentStandard | Out-File $ResponseFileNameStandard -ErrorAction Stop
$SQLServerInstall = $setup + $settings + $ResponseFileNameStandard
Write-Debug “Generate repsonse file”
Write-log -id 0020 -msg “SQL Server Standard Response file generation” -status “SuccessAudit”
}
Catch {
Write-Log -id 0021 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
invoke-expression $SQLServerInstall -ErrorAction Stop
Write-Log -id 0022 -Status “SuccessAudit” -Msg “Installation of SQL Server”
Write-Debug “SQL Server is installed”
}
Catch {
Write-Debug “SQL Server install failed”
Write-Log -id 0023 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
#
# TUNING TIME !! 😀
#
# After all SQL Features are installed.
# It’s time to tune the instance
# We will
# – Tune the tempdb
# – Create a SA user for dev
# – Change listening TCP Port
# – Set the max memory
# At first,load the SQL Server Powershell Snappin
if ((Get-PSSnapin | ? { $_.name -like ‘sqlserver*’}).count -ne 2) {
Write-Debug “Load SQL Snappins”
Try {
add-pssnapin sqlserverprovidersnapin100 -ErrorAction Stop
add-pssnapin sqlservercmdletsnapin100 -ErrorAction Stop
Write-Log -id 0024 -Status “SuccessAudit” -Msg “load the SQL Server Powershell Snappin”
Write-Debug “SQL Snappins loaded”
}
Catch {
Write-Debug “Error loading snappin.. maybe no sql installed ?”
Write-Log -id 0025 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
Write-Debug “Change TCP Listening port”
Try {
# This Value can be any number.
# Btw, check previously if the value is not used by other soft on your network
# default is 1433
Set-ItemProperty $HKML_IPall -name “TcpPort” -value “$TCPport” -ErrorAction Stop
Set-ItemProperty $HKML_IPall -name “TcpDynamicPorts” -value “” -ErrorAction Stop
Write-Log -id 0026 -Status “SuccessAudit” -Msg “Change TCP static Port”
Write-Debug “Change TCP Listening port done.”
}
Catch {
Write-Debug “An error as occured during changement of Listening port”
Write-Log -id 0027 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
Finally {
$Get_TcpPort = (Get-ItemProperty $HKML_IPall -ErrorAction Stop).TcpPort
$Get_TcpDynamicPorts = (Get-ItemProperty $HKML_IPall -ErrorAction Stop).TcpDynamicPorts
if (($Get_TcpPort -eq $TCPport) -and ($Get_TcpDynamicPorts -eq “”)) {
Write-Log -id 0028 -Status “SuccessAudit” -Msg “Verification of static TCP Port”
} else {
Write-Log -id 0029 -Status “Error” -Msg “Verification of static TCP Port”
}
}
# Set Max memory will ensure the Quality of service
# Even if the database is fully loaded
Try {
Write-Debug “Setting Max Memory”
Invoke-Sqlcmd -Query $QueryMaxMemory -Serverinstance . -ErrorAction Stop
Write-Log -id 0030 -Status “SuccessAudit” -Msg “Max Memory request execution”
}
Catch {
Write-Log -id 0031 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
Finally {
Write-Debug “Verify max memory value”
$MaxmemSet = Invoke-Sqlcmd -Query $QueryMaxMeroySetted -Serverinstance . -ErrorAction Stop
if ($MaxmemSet.value = 4096) {
Write-Log -id 0032 -Status “SuccessAudit” -Msg “Max Memory value is 4096”
} else {
Write-Log -id 0033 -Status “Error” -Msg “Max Memory value is not 4096. Strange if the previous request is correctly executed….”
}
}
# Create the SQL Account
# Give him SA Rights and check it !
Try {
Write-Debug “Add Dev Login & add it to sysadmin fixed server role”
# lets put those two req in the same Try, as if one or other dosent work it’s a shame.
# The first create the account
# The second add the new account in the correct server role
Invoke-Sqlcmd -Query $QuerySQLAccount -Serverinstance . -ErrorAction Stop
Invoke-Sqlcmd -Query $QuerySQLRole -Serverinstance . -ErrorAction Stop
Write-Log -id 0034 -Status “SuccessAudit” -Msg “Login correctly created with sysadmin server role”
}
Catch {
Write-Log -id 0035 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
# let’s do some tuning for TempDB perf
# Will assign for each proc a tempdb file
# and fix growth and size/size max
Try {
Write-Debug “Tuning TempDB”
# Configuration of TempDB Maxsize for File
Try {
Write-Debug “TempDB maxsize tuning”
Invoke-Sqlcmd -Query $TempDBmodify -Serverinstance . -ErrorAction Stop
Write-log -id 0036 -msg “Modify TempDB maxsize” -Status “SuccessAudit”
}
Catch {
Write-Log -id 0037 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
# Configuration of TempDB
Try {
Write-Debug “EmpDB Log tuning”
Invoke-Sqlcmd -Query $TempLogDBmodify -Serverinstance . -ErrorAction Stop
Write-Log -id 0038 -msg “Modify TempDB Log size” -Status “SuccessAudit”
}
Catch {
Write-Log -id 0039 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
Write-Log -id 0040 -msg “TempDB configuration” -Status “SuccessAudit”
}
Catch {
Write-Log -id 0041 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
Finally {
Invoke-Sqlcmd -Query $TempDBadd -Serverinstance $Instance -ErrorAction Stop
Write-Debug “Creation of all tempDB files”
}
# Time to patch with Service Pack 2
# Cannot be slipstreamed in Express edition…
# So we’ll use the same SP2 for both version
if ($Express) {
Try {
Write-Debug “Spevice pack 2 patch action”
$InstallSP2 = [System.Diagnostics.Process]::Start($SetupSP2, $paramSP2)
$InstallSP2.waitforexit()
Write-Log -id 0042 -Status “SuccessAudit” -Msg “$Instancename correctly patched with SP2”
}
Catch {
Write-Log -id 0043 -msg $_.Exception.Message -status “Error” -Assembly $_.Exception.TargetSite.Module.Name -Type $_.Exception.TargetSite.ReturnType.Name -Stack $_.Exception.StackTrace
}
}
Write-Debug “SQL Server installation finished”
[/powershell]
