Pages

lørdag 15. juni 2013

Windows Server 2012 Core: Install SQL Server 2012 w/SP1

Assuming you have installed the Operating System and are ready with the command line... How to join computer to the domain and setup SQL server:

You should edit all text in red to match your environment!

Start PowerShell by typing:
powershell

# Set IP address:
$NetIPIF = Get-NetIPInterface -AddressFamily IPv4 -ConnectionState Connected | ? InterfaceAlias -ne 'Loopback Pseudo-Interface 1'
If ($NetIPIF) {
  Set-NetIPInterface -InterfaceAlias $NetIPIF.InterfaceAlias -Dhcp Disabled
  $NetIPIF | Set-NetIPInterface -Dhcp Disabled
  New-NetIPAddress -InterfaceAlias $NetIPIF.InterfaceAlias -IPAddress 192.168.160.30 -DefaultGateway 192.168.160.5 -AddressFamily IPv4 -PrefixLength 24
  Set-DnsClientServerAddress -InterfaceAlias $NetIPIF.InterfaceAlias -ServerAddresses ('192.168.160.10')
}

# Join Computer to the Domain
$Cred = Get-Credential
If ($Cred) {Add-Computer -DomainName 'lab.domain' -Credential $Cred -OUPath "OU=SQL,OU=Servers,OU=HQ,DC=lab,DC=domain" -Force}
# After verifying success, restart computer
Restart-Computer

After restart, remember to log in with your domain administrator account.

# Sync time with the PDC emulator
powershell
# If using Hyper-V, Disable aspects of time synchronization from Hyper-V with:
Set-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\VMICTimeProvider' -Name 'Enabled' -Value 0

# PS! Never disable Hyper-V Time Synchronization, it's important when the virtual machine boots and resumes saved states. Hyper-V Integration Services synchronizes the time of virtual machines with the physical host because virtual machines tend to experience time drift over time.

& C:\Windows\System32\w32tm.exe /config /syncfromflags:DOMHIER /update
Restart-Service w32time
& C:\Windows\System32\w32tm.exe /resync /rediscover /force

# If you have added extra disks for user databases tempdb and log, you must configure them
# Change Drive Letter on DVD Drive to X
gwmi Win32_Volume -Filter "DriveType = '5'" | swmi -Arguments @{DriveLetter = "X:"}

# Make all offline disks online:
Get-Disk | ? IsOffline –eq $true | Set-Disk –IsOffline $false -IsReadOnly $false

# Initialize all disks with RAW partition
Get-Disk | Where-Object PartitionStyle –eq 'RAW' | Initialize-Disk –PartitionStyle MBR

# List all disks without any partitions
Get-Disk | Where-Object NumberOfPartitions -eq 0

# Create partition on the disks, change DiskNumber and other paramters to match your setup
New-Partition –DiskNumber 2 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'mssqlserver' -FileSystem NTFS -Confirm:$false
New-Partition –DiskNumber 3 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'tempdbpart1' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
New-Partition –DiskNumber 4 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'tempdbpart2' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
New-Partition –DiskNumber 5 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'tempdbpart3' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
New-Partition –DiskNumber 6 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'tempdbpart4' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
New-Partition –DiskNumber 7 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'log' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
New-Partition –DiskNumber 8 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'opsmgrdb' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
New-Partition –DiskNumber 9 -UseMaximumSize -AssignDriveLetter | Format-Volume -NewFileSystemLabel 'opsmgrdw' -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false

# Set filesystem label on C disk
Set-Volume -NewFileSystemLabel 'os' -DriveLetter C

# Verify volumes
Get-Volume

# Disable Indexing on all drives
gwmi Win32_Volume -Filter "IndexingEnabled=$true" | swmi -Arguments @{IndexingEnabled=$false}

# Open firewall ports
# Open up firewall for SQL server DB Engine access
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 1433 -DisplayName "SQL Server (TCP 1433)"
# Used when a default instance is used, we use that so enable this
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 1434 -DisplayName "SQL Admin Connection (TCP 1434)"
# Used By Service Broker, we do not install that so we do not enable it
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 4022 -DisplayName "SQL Service Broker (TCP 4022)" -Enabled False
# Used when a named instance is used, we used default instance so we do not enable it
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol UDP -LocalPort 1434 -DisplayName "SQL Browser (UDP 1434)" -Enabled False
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 2382 -DisplayName "SQL Browser (TCP 2382)" -Enabled False
# Used By Analysis Services when a named instance is used, we do not install that so we do not enable it
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 2385 -DisplayName "Analysis Services SQL Browser (TCP 2385)" -Enabled False
# Used By Analysis Services when default instance is used, we do not install that so we do not enable it
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 2383 -DisplayName "Analysis Services (TCP 2383)" -Enabled False
# Used By Reporting Services, we do not install that so we do not enable it
New-NetFirewallRule -Group "SQL Server" -Direction Inbound -Action Allow -RemoteAddress LocalSubnet -Profile Domain -Protocol TCP -LocalPort 80 -DisplayName "Reporting Services Web (TCP 80)" -Enabled False
# Allow WMI and SMB etc
Set-NetFirewallRule -Name FPS-NB_Name-In-UDP -Enabled True
Set-NetFirewallRule -Name FPS-NB_Datagram-In-UDP -Enabled True
Set-NetFirewallRule -Name FPS-NB_Session-In-TCP -Enabled True
Set-NetFirewallRule -Name FPS-SMB-In-TCP -Enabled True
Set-NetFirewallRule -DisplayGroup "Windows Management Instrumentation (WMI)" -Enabled True

# Install Required Windows Features - Insert Windows Server 2012 installation media in the DVD drive
dism /online /enable-feature /featurename:netfx3 /all /source:X:\sources\sxs
Install-WindowsFeature -Name Net-Framework-Core -Source:wim:X:\Sources\install.wim:1

To setup SQL server you should create some service accounts, groups and users. To do this head over to your domain controller and start PowerShell. Example:

# Create User and Service Accounts for the SQL server
New-ADUser svc-SQL1-DBEngine -AccountPassword (Read-Host -AsSecureString -Prompt "Enter Password") -ChangePasswordAtLogon $False -PasswordNeverExpires $True -Path 'OU=ServiceAccounts,OU=HQ,DC=lab,DC=domain' -Description 'SQL Server service account' -Enabled $True
New-ADUser svc-SQL1-Agent -AccountPassword (Read-Host -AsSecureString -Prompt "Enter Password") -ChangePasswordAtLogon $False -PasswordNeverExpires $True -Path 'OU=ServiceAccounts,OU=HQ,DC=lab,DC=domain' -Description 'Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks' -Enabled $True
New-ADUser adm-SQL1-sysadmin -AccountPassword (Read-Host -AsSecureString -Prompt "Enter Password") -ChangePasswordAtLogon $False -PasswordNeverExpires $True -Path 'OU=Users,OU=Administrators,OU=HQ,DC=lab,DC=domain' -Description 'SQL Server sysadmin account' -Enabled $True

# Create a group for defining who will have the DB role sysadmin right on SQL1 server
New-ADGroup -Name acl-SQL1-sysadmin -GroupCategory Security -GroupScope Global -Path 'OU=ServerAccess,OU=Servers,OU=HQ,DC=lab,DC=domain' -Description 'Access group for server SQL1, Database role sysadmin right'

# Create a group for defining local administrator access to the SQL1 server
New-ADGroup -Name acl-SQL1-localadmin -GroupCategory Security -GroupScope Global -Path 'OU=ServerAccess,OU=Servers,OU=HQ,DC=lab,DC=domain' -Description 'Access group for server SQL1, Local Administrators'

# Create a group for defining remote desktop access to the SQL1 server
New-ADGroup -Name acl-SQL1-remoteuser -GroupCategory Security -GroupScope Global -Path 'OU=ServerAccess,OU=Servers,OU=HQ,DC=lab,DC=domain' -Description 'Access group for server SQL1, Remote Desktop Users'

# Add user adm-SQL1-sysadmin and Administrator to the group acl-SQL1-sysadmin
Add-ADGroupMember -Identity acl-SQL1-sysadmin -Members adm-SQL1-sysadmin, Administrator

# Add user adm-SQL1-sysadmin to the group acl-SQL1-localadmin
Add-ADGroupMember -Identity acl-SQL1-localadmin -Members adm-SQL1-sysadmin

# Add user adm-SQL1-sysadmin to the group acl-SQL1-remoteuser
Add-ADGroupMember -Identity acl-SQL1-remoteuser -Members adm-SQL1-sysadmin

You should trust the service account for delegation if you use linked servers:
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx
# Trust the svc-SQL1-DBEngine account for delegation
Set-ADUser -Identity svc-SQL1-DBEngine -TrustedForDelegation $true

Go back to the SQL server and PowerShell and continue preparing for SQL server installation:
# Add domain group acl-SQL1-localadmin to the local group Administrators
([ADSI]"WinNT://./Administrators,group").Add("WinNT://lab.domain/acl-SQL1-localadmin")
# Add domain group acl-SQL1-remoteuser to the local group Remote Desktop Users
([ADSI]"WinNT://./Remote Desktop Users,group").Add("WinNT://lab.domain/acl-SQL1-remoteuser")

To avoid error 1032 messages in the Application log in Windows Server 2012 we should grant permission to dbengine service account to LogFiles\Sum folder. (http://support.microsoft.com/kb/2811566)
# Give modify permission on C:\Windows\System32\LogFiles\Sum to user svc-SQL1-DBEngine.
& icacls 'C:\Windows\System32\LogFiles\Sum' /grant 'LAB\svc-SQL1-DBEngine:W' /T /Q

# Register SPN for the DBEngine account
& setspn -A MSSQLSvc/SQL1:1433 LAB\svc-SQL1-DBEngine
& setspn -A MSSQLSvc/SQL1.lab.domain:1433 LAB\svc-SQL1-DBEngine
# Verify that SPN records was created
& setspn -L LAB\svc-SQL1-DBEngine

Now we should be ready to install SQL server. PS! You must have internet access on the server. For detailed information on installing SQL server look at:
http://msdn.microsoft.com/en-us/library/hh231669.aspx
http://msdn.microsoft.com/en-us/library/ms144259.aspx
# Install SQL Server with DBEngine and fulltext
& X:\Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine,FULLTEXT /INSTANCENAME=MSSQLSERVER /INSTALLSQLDATADIR='E:\MSSQLSERVER\' /INSTANCEDIR='E:\MSSQLSERVER' /SQLTEMPDBDIR='F:\MSSQLSERVER\MSSQL\TempDB\Data' /SQLTEMPDBLOGDIR='F:\MSSQLSERVER\MSSQL\TempDB\Log' /SQLUSERDBLOGDIR='J:\MSSQLSERVER\MSSQL\Log' /SQLCOLLATION='SQL_Latin1_General_CP1_CI_AS' /SQMREPORTING=0 /SQLSVCACCOUNT='LAB\svc-SQL1-DBEngine' /SQLSVCPASSWORD='UserPassword1' /SQLSYSADMINACCOUNTS='LAB\acl-SQL1-sysadmin' /SQLSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /AGTSVCACCOUNT='LAB\svc-SQL1-Agent' /AGTSVCPASSWORD='UserPassword1' /AGTSVCSTARTUPTYPE=Automatic /IACCEPTSQLSERVERLICENSETERMS

# After installation you should verify that the SQL Server instance was installed by looking at the Microsoft SQL Server
DIR E:\MSSQLSERVER

# And also check that the services are running
Get-Service | ? Name -Like '*SQL*'

# Configure tempdb with size and more files, set remote access and sql min max memory size
http://msdn.microsoft.com/library/ms175527.aspx
# Create folders for tempdb files
If (-Not (Test-Path 'G:\MSSQLSERVER\MSSQL\TempDB\Data\' -PathType Container)) { New-Item -Path 'G:\MSSQLSERVER\MSSQL\TempDB\Data\' -ItemType 'Directory'}
If (-Not (Test-Path 'H:\MSSQLSERVER\MSSQL\TempDB\Data\' -PathType Container)) { New-Item -Path 'H:\MSSQLSERVER\MSSQL\TempDB\Data\' -ItemType 'Directory'}
If (-Not (Test-Path 'I:\MSSQLSERVER\MSSQL\TempDB\Data\' -PathType Container)) { New-Item -Path 'I:\MSSQLSERVER\MSSQL\TempDB\Data\' -ItemType 'Directory'}

# View current tempdb files
Invoke-Sqlcmd -Query "SELECT physical_name AS FileName, size*1.0/128 AS FileSizeinMB, CASE max_size WHEN 0 THEN 'Autogrowth is off.' WHEN -1 THEN 'Autogrowth is on.' ELSE 'Log file will grow to a maximum size of 2 TB.' END, growth AS 'GrowthValue', 'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.' WHEN growth > 0 AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.' ELSE 'Growth value is a percentage.' END FROM tempdb.sys.database_files;"

# Verify space before increasing tempdb size
Get-Volume

# If increasing disksize in hyper-v/vmware, resize partition
$size = (Get-PartitionSupportedSize -DriveLetter F)
Resize-Partition -DriveLetter F -Size $size.SizeMax

# Alter tempdb database
Invoke-Sqlcmd -Query "ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE=2GB, FILEGROWTH=10%)"
Invoke-Sqlcmd -Query "ALTER DATABASE tempdb MODIFY FILE (NAME='templog', SIZE=2GB, FILEGROWTH=10%)"
Invoke-Sqlcmd -Query "ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='G:\MSSQLSERVER\MSSQL\TempDB\Data\tempdb2.ndf', SIZE=2GB, FILEGROWTH=10%)"
Invoke-Sqlcmd -Query "ALTER DATABASE tempdb ADD FILE (NAME='tempdev3', FILENAME='H:\MSSQLSERVER\MSSQL\TempDB\Data\tempdb3.ndf', SIZE=2GB, FILEGROWTH=10%)"
Invoke-Sqlcmd -Query "ALTER DATABASE tempdb ADD FILE (NAME='tempdev4', FILENAME='I:\MSSQLSERVER\MSSQL\TempDB\Data\tempdb4.ndf', SIZE=2GB, FILEGROWTH=10%)"

# Verify tempdb files
Invoke-Sqlcmd -Query "SELECT physical_name AS FileName, size*1.0/128 AS FileSizeinMB, CASE max_size WHEN 0 THEN 'Autogrowth is off.' WHEN -1 THEN 'Autogrowth is on.' ELSE 'Log file will grow to a maximum size of 2 TB.' END, growth AS 'GrowthValue', 'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.' WHEN growth > 0 AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.' ELSE 'Growth value is a percentage.' END FROM tempdb.sys.database_files;"

# Configure SQL Remote Access - in my case this was not needed - it was already configured
Invoke-Sqlcmd -Query "EXEC sys.sp_configure 'remote access', '1'; RECONFIGURE"
Invoke-Sqlcmd -Query "RECONFIGURE"

# Set min and max memory to 4096 MB
Invoke-Sqlcmd -Query "exec sp_configure 'show advanced options', 1; RECONFIGURE"
Invoke-Sqlcmd -Query "exec sp_configure 'min server memory', 4096"
Invoke-Sqlcmd -Query "exec sp_configure 'max server memory', 4096"
Invoke-Sqlcmd -Query "exec sp_configure 'show advanced options', 0; RECONFIGURE"

# To log off you can simply type:
LOGOFF