Thursday, May 9, 2013

Building The SQL Server 2012 Template (Version 1–With Windows Server 2008 R2 Core)

Why would we want to develop a SQL Server template? I can think of two main reasons. Firstly, configuring a SQL Server can be a time consuming process. By the time all the local policy settings, SQL settings etc have been configured it can take a full day!!! Templating speeds this up so that SQL Server deployment should take no longer than 30 minutes – simply deploy the template and reconfigure a handful of settings specific to the instance. Secondly templates help standardise the environment. This is particularly important when you have a number of DBAs in a team and everyone does things that little bit different.

Before I go too much further it’s important to understand the design philosophy I’m taking with this approach. Rather than having a few large monolithic virtual SQL Servers I’m looking at having numerous smaller virtual SQL Servers. These smaller SQL Servers are easier to move around in virtual environments that use high availability technologies like Vmotion or Live Migration. They can also help segregate the load and risk between applications and clients.

As a starting point I’m setting up a VMWare based SQL Server 2012 template running on Windows 2008 R2 Core. I’ve deliberately chosen Windows 2008 R2 Core Standard Edition for the following reasons:

  1. Windows Server 2012 requires VMWare ESX 5.1 or greater to run. As this is “relatively” new Server 2008 R2 is a much more ubiquitous choice.
  2. Windows Server Core uses around 25% of the memory of Windows with a GUI leaving more resources available to applications.
  3. Windows Server Core also requires far fewer patches than the full GUI version. For example there’s no Internet Explorer or other GUI components that require patching.

The final piece of the puzzle is that sysprepping of SQL Server 2012 is now supported as of SP1 CU2. This now gives us the ability to cleanly and rapidly deploy SQL Server 2012 in a uniform manner.

The SQL Server Template Design

Template Design

  • Windows Server 2008 R2 Core (Standard Edition)
    • E: – Data file drive
    • F: – Files drive. Filestream data and backups
    • L: – Log files
    • T: – TempDB files
  • .NET 3.5 SP1 (SQL Server 2012 pre-requisite)
  • SQL Server 2012 SP1 CU3
    • Database Engine
    • Replication
    • Full-Text Indexing
    • Integration Services
    • Filestream Enabled
  • Antivirus Software (Optional and Recommended)
  • Monitoring Software (Optional and Recommended)

Service Accounts

I’ll be using the virtual service accounts which are the default option. The reason behind this is that I’ll be deploying this template into both workgroup and domain configuration. The second reason is that these virtual service accounts can also access domain resources. They do this under the context of the computer machine account (eg. as <DOMAIN>/<COMPUTERNAME>%)

Template Build Process

Virtual Machine

Firstly deploy a virtual machine with the following configuration:

  • 1 vCPU
  • 2GB Ram
  • C Drive – 30GB
  • E Drive – 10GB
  • F Drive – 2GB
  • L Drive – 2GB
  • T Drive – 2GB

As you can see the drive sizes are quite small. These are stub drives that should be expanded to the required size during deployment.

Windows Server 2008 R2 Core Install

Perform a vanilla Windows Server 2008 R2 Core install onto the C drive of the virtual machine. Once completed we’ll customise the base operating system.

Fix the Page File

First I’m going to fix the page file to 2GB in size. This is enough for a minidump but not enough for any serious paging to occur. Our goal with SQL Sever is to have it not page so the 2GB should be fine.

< insert instructions here>

Enable Remote Management

The remote management tools allow you to use MMC from another computer to manage this SQL Server. You may find this handy as without the interface you cannot run a lot of the configuration tools on Windows Core. Run the sconfig command for an easy menu driven approach to configuring a Windows Core server. (Note lines with # in front are comments and should not be entered as input!)

   1: # Configure remote management
   2: sconfig
   3: 4) Configure Remote Management
   4: # Allow MMC Remote Management
   5: 1) Allow MMC Remote Management <Enter>
   6: # Enable Windows PowerShell. Sets execution policy to remote signed
   7: 2) Enable Windows PowerShell <Enter>
   8: 13) Exit to Command Line
   9: shutdown /r /t 0


Enable Remote Desktop


Next we’ll enable the Remote Desktop. Despite having no GUI it is useful to be able to directly access the command line via Remote Desktop.



   1: sconfig
   2: 4) Configure Remote Management
   3: 3) Allow Server Manager Remote Management <Enter>
   4: #Enable remote desktop
   5: 5) Return to main menu
   6: 7) Remote Desktop:
   7: (E)nable or (D)isable Remote Desktop? (Blank=Cancel)
   8: E <Enter>
   9: # Option 2 is mandatory in this instance. Not running on a domain at this stage.
  10: 2) Allow clients running any version of Remote Desktop (less secure) <Enter><Enter>
  11: 13) Exit to Command Line





 



Install all Windows Updates


Using sconfig install all Windows updates and ensure the server is fully patched.


Install .NET 3.5 SP1 Framework


By default this feature is not enabled. As this is a requirement for SQL Server 2012 you need to enable it by running the following command:



   1: Start /w ocsetup NetFx3-ServerCore

 


Setup the Disk Partitions


Next we’ll setup the disk partitions and filesystem structures so they are ready for SQL Server. We’re using the diskpart utility to create the disk structures and once again, the # symbol is used for comments. Diskpart wont recognize the # symbol or comments so don’t copy those lines into the utility. Also take note that these disk assignments match up to the disks I assigned my virtual template. The first “list disk” command can be used to determine your drive mapping.



   1: diskpart
   2: # Create boundary aligned volumes
   3: list disk
   4: select disk 1
   5: create partition primary align=1024
   6: select disk 2
   7: create partition primary align=1024
   8: select disk 3
   9: create partition primary align=1024
  10: select disk 4
  11: create partition primary align=1024
  12:  
  13: # Assign drive letters to volumes
  14: list volume
  15: select volume 3
  16: assign letter=e
  17: select volume 4
  18: assign letter=l
  19: select volume 5
  20: assign letter=t
  21: select volume 6
  22: assign letter=f
  23: exit

Next we’ll format the drives. I’m going to format the three SQL drives ( E:, L:, T: ) with 64K block size. The F drive could also be formatted with a 64K block size however I’m keeping it as a 4KB block size. My logic behind this is that I want to use compression for the filestream folder and this is only supported on 4KB block sizes.



   1: format e: /fs:ntfs /a:64k
   2: format l: /fs:ntfs /a:64k
   3: format t: /fs:ntfs /a:64k
   4: format f: /fs:ntfs

Finally I’ll create the folder structures I’ll be using in my SQL Server template:



   1: mkdir e:\MSSQL
   2: mkdir e:\MSSQL\DATA
   3: mkdir l:\MSSQL
   4: mkdir l:\MSSQL\LOGS
   5: mkdir f:\MSSQL
   6: mkdir f:\MSSQL\BACKUPS
   7: mkdir f:\MSSQL\FileStream
   8: mkdir f:\REPL

 


Filestream Disk Optimizations


Next step there’s a couple of disk based optimizations we can make for the filestream features.


First we’ll disable short file names. This is a backwards compatibility feature that creates a secondary 16 bit compatible filename for each and every file on the filesystem. This is no longer required (unless you’re running a 16 bit application on this server!)



   1: fsutil.exe behavior query disable8dot3
   2: fsutil behavior set disable8dot3 1

Next we’ll disable the Last Access Time file attribute. You may want to avoid this if you’re going to keep your backups on this drive (I’ll be moving them off post template deployment)



   1: fsutil.exe behavior set disablelastaccess 1








Set the Power Plan to “High Performance”


By default Windows Server runs on the “Balanced” power plan. This will try and save power by ramping up and down the frequency of the CPU as required. The problem is that the ramp up process can take a while depending upon your load profiles. By the time the CPU ramps up the frequency the short burst of load may have been processed. Rather than letting Windows determine the frequency of your CPU. The first command below shows us what the power plan is currently in use.



   1: # List all the power schemes
   2: powercfg.exe -L

This will return output similar to what is below:



Existing Power Schemes (* Active)
-----------------------------------
Power Scheme GUID: 381b4222-f694-41f0-9685-ff5bb260df2e  (Balanced) *
Power Scheme GUID: 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c  (High performance)
Power Scheme GUID: a1841308-3541-4fab-bc81-f71556f20b4a  (Power saver)

Here we’re interested in the GUID for the “High peformance” power scheme. We set it as active using the GUID above as shown below:



   1: powercfg.exe -SETACTIVE "8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c"

 


Installing SQL Server


The next step is to perform the SQL Server install. In the approach I’m taking I’m performing a vanilla install of SQL Server using the local virtual accounts and providing a default sa password. These can be customised post install as required.


The command line install is show below. 



d:\setup.exe /action=install /IACCEPTSQLSERVERLICENSETERMS /UpdateEnabled /FEATURES=SQL,IS,Replication,FullText /QS /SQLSYSADMINACCOUNTS=Administrator /INSTANCENAME="MSSQLSERVER" /SECURITYMODE=SQL /SAPWD=Password1 /INSTANCEDIR=E:\MSSQL /TCPENABLED=1 /BROWSERSVCSTARTUPTYPE=Automatic /SQLBACKUPDIR=F:\MSSQL\BACKUPS /SQLTEMPDBDIR=T:\MSSQL /SQLTEMPDBLOGDIR=T:\MSSQL /SQLUSERDBDIR=E:\MSSQL\DATA /SQLUSERDBLOGDIR=L:\MSSQL\LOGS /FILESTREAMLEVEL=1 /INSTALLSQLDATADIR=E:\MSSQL\DATA

A quick run down of the script options and the reason I’ve chosen those options is as follows:



  • FEATURES – The SQL database engine, Integration Services, Replication, and Full-text indexing. Some of these features may not be used on all deployed instances however there’s no harm in deploying them just in case.

  • SQLSYSADMINACCOUNT – I’ve added the local administrator account as a sysadmin on SQL Server. You may want to remove this depending on your security policy.

  • INSTANCENAME – The default MSSQLSERVER instance. I’m trying to move away from multiple instances on a single server in favour of multiple smaller virtual servers. It’s easier from a resourcing perspective and keeps the environment consistent.

  • SECURITYMODE – SQL. Need for access in a workgroup environment (remember – there’s no GUI!). Can be changed to Windows post install.

  • SAPASSWD – The sysadmin password is set to a default and should be changed manually post install.

  • INSTANCEDIR – The SQL Server instance will be installed to the E drive

  • TCPENABLED – By default TCP connectivity is disabled to the SQL Server. Turn it on.

  • BROWSERVERSVCSTARTUPTYPE  - Automatic.

  • SQLBACKUPDIR – the default location of backups. I will typically change this to a network share for centralised backups where possible.

  • SQLTEMPDBDIR – Put the tempDB on the T drive

  • SQLTEMPDBLOGDIR – Put the tempDB log files on the T drive. In high performance scenarios this may be located on another drive / another LUN.

  • SQLUSERDBDIR – The default location for user database files is on the E drive

  • SQLUSERDBLOGDIR – The default location for user database log files is on the L drive

  • FILESTREAMLEVEL – Set to 1. Ie. Access via SQL only. For level 2 and higher to work we need to open up SMB access to the SQL Server. As this is potentially a security risk I’ve left this disabled.

 


As a final setup, install the current Service Pack and Cumulative Update.


Post Installation Configuration - Operating System


The Firewall


The next step is to allow SQL Server to talk through the firewall. In this instance I’ve just opened up communication to the SQL Server and Browser executables. You can update this to lock it down further as required.



netsh advfirewall firewall add rule name="SQL Server" dir=in program="e:\mssql\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" action=allow
 
netsh advfirewall firewall add rule name="SQL Server Browser" dir=in program="c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" action=allow

 


Post Installation Configuration - SQL Server






Enable TCP/IP and Change the Default Port


I tend to change the default port from 1433. The main reason for this is that we can have external parties connecting to some of our sandboxed SQL instances and I’d rather not use the standard ports just as an extra layer of security. First run a SQL Powershell session using the sqlps.exe command, then run the following code:



[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer')
 
$uri = "ManagedComputer[@Name='W2008R2SQL2012S']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp
 
# want to repeat this for all IP addresses
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "9033"
$TCP.alter()

 



Grant Lock Pages In Memory Privilege to The SQL Server Service Account


There’s conflicting advice on whether to grant this privilege or not. Personally I use it everywhere. On production systems it’s mandatory, on non-production systems optional. This setting will force SQL Server to hold onto the memory it grabs and not release it back to the operating system.


This can be dangerous if you have other applications running on that server that try and access memory when none is available. Paging will occur. If you perform this step make sure that you leave memory free on the OS for the OS and other applications.


My approach here is to use the setting, then lock down the Max Memory setting in SQL Server such that there is always a certain amount of memory free for the operating system.



"C:\Admin\ntrights.exe" -u "NT SERVICE\MSSQLSERVER" +r SeLockMemoryPrivilege

 


Enable Instant File Initialization


Less controversial is adding the SQL Server service account to the “Perform Volume Tasks” privilege. This permission is required for instant file initialization. Instant file initialization allows SQL to allocate addition space to new and existing files without zeroing out that region of disk first. This is a massive performance boost for database growth and backups in particular.



"C:\Admin\ntrights.exe" -u "NT SERVICE\MSSQLSERVER" +r SeManageVolumePrivilege

 


Enable Backup Compression As Default


By default backup compression is not enabled. This setting turns it on and in turn compresses your backups. Typically compressed backups run faster and are around 1/3rd of the size of uncompressed backups.



EXEC sp_configure 'backup compression default', 1;
RECONFIGURE WITH OVERRIDE;
GO

 


Enable Remote Administration Connections


It’s worth turning this on so you can access your SQL Server in case of emergency.



sp_configure 'remote admin connections' , 1
GO
RECONFIGURE
GO

 


Enable XP Command Shell


This one may be controversial but I turn it on by default. I’ve got a few maintenance jobs that require it plus quite a few of our developer jobs require this feature.



EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
 

 


Split the TempDB


Splitting the tempDB helps reduce contention by creating more IO threads (one per data file). In this script I’ve created 4 tempDB data files, each 1GB in size. This needs to be reconfigure based on your server requirements. The guideline here is 1 data file per processor. Unless you have a lot of processors, then its more like 1 data file per 2 processors. The size also depends on your workload and the tempDB needs to be monitored so that it does not fill up.



alter database tempdb
modify file
(
       name = tempdev, size = 1000MB , maxsize = 1000 MB, filegrowth = 0MB
)
GO
 
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev_2 ,
MAXSIZE = 1000MB,
SIZE=1000 MB ,
FILENAME = 'T:\MSSQL\tempdb_2.ndf')
GO
 
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev_3 ,
MAXSIZE = 1000MB,
SIZE=1000 MB ,
FILENAME = 'T:\MSSQL\tempdb_3.ndf')
GO
 
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev_4 ,
MAXSIZE = 1000MB,
SIZE=1000 MB ,
FILENAME = 'T:\MSSQL\tempdb_4.ndf')
GO

 


Optimize For Adhoc Workloads


This setting is used to combat procedure cache bloat generated by adhoc queries. There’s no size limit to the procedure cache so in theory you can have GB’s of adhoc one-time queries being cached that will never be used again. This feature will tell SQL Server to only cache a query on the second time it is used.



sp_CONFIGURE 'show advanced options' ,1
RECONFIGURE
GO
 
sp_CONFIGURE 'optimize for ad hoc workloads' ,1
RECONFIGURE
GO

 




Other Manual Stuff


I’ve put all the above into scripts so that my SQL Server deployments are uniform. There is however some outstanding items that are beyond the scope of the template but still need to be looked at:



  • SQL Server Min and Max memory – this may go into the template at some point. These settings really depend on your server and as such don’t fit into the template.

  • Network Configuration – Server IPs etc.

  • Antivirus Configuration – skip mdfs, ldfs, ndfs

  • Database mail – depends on environment (ie. SMTP info)

  • Backup and Maintenance jobs – may include into template using default locations

  • Failsafe Operator
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)