Install SQL Server 2016 Marcin Szeliga
[email protected]
Install SQL Server 2016 › Planning a SQL Server 2016 Installation › Configuring Operating System › Installing SQL Server 2016 › Configuring SQL Server
Minimum Hardware and Software Requirements › SQL Server runs on Windows – And of course Windows runs on hardware – So even before you tune Windows, you need to have the best hardware you can afford for your system
› Hardware requirements: – Processors – Memory – Disk
› Software requirements: – Operating system – Prerequisite software – General software requirements
Assessing CPU and Memory Requirements › CPU: – Utilization dependent on types of queries running – Test against realistic workloads
› Memory: – Express Edition can only use 1 GB of memory – Cannot use AWE-based memory on 32-bit servers
Storage I/O and Performance › Plan and test your I/O requirements – Most large scale SQL Server deployments sooner or later run into I/O bottlenecks
› Considerations for storage: – – – –
Dedicated vs. SAN storage RAID systems Number of drives I/O caching configuration
› Assessing I/O by Using SQLIOSim – Attempts to accurately simulate the I/O patterns of SQL Server
› Assessing I/O by Using Diskspd – A general purpose load generator for I/O subsystems – Can be configured to mimic SQL Server I/O
How to Reduce Storage Throughput Needs › Keep memory free for SQL Server data caching › Design and maintain your indexes › Compress data › Use partitioned views to segment old archive data into tables with 100% fill factor
› Maintain perfect statistics › Merry-go-round scans with SQL Server Enterprise Edition
Install SQL Server 2016 › Planning a SQL Server 2016 Installation › Configuring Operating System › Installing SQL Server 2016 › Configuring SQL Server
Disk Partition Alignment › Partition alignment is a best practice & provides significant benefits › The following three must be correlated: – Partition starting offset – Stripe unit size – File allocation unit (cluster) size
› The following formula must result in an integer value: Partition_Offset ÷ Stripe_Unit_Size
OS Configuration › Power option should be set to High Performance › NTFS Allocation Unit Size – Default: 4 KB – Recommendation: At least 64 KB › For SQL Server data, logs, and tempdb files › Remember, IO for SQL Server happens in Extent/Page level
– Note, allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume – Use fsutil to check current settings: › fsutil fsinfo ntfsinfo c:
Planning for tempdb Files › tempdb files are similar in architecture to data files for other SQL Server databases › The usage pattern for tempdb is unlike other databases – Holds temporary objects created by users or by the database engine
› SQL Server recreates tempdb every time the instance restarts › tempdb is a shared resource, so its performance can affect the performance of the whole system › You can specify the following attributes of tempdb files during installation of SQL Server 2016: – – – –
Location Number Initial size Auto-growth increment
One tempdb File vs. Multiple Files › Multiple Files – Reduce allocation contention › Less of an issue in SQL Server 2005 and above
– One file per 1/8 to 1/4 the number of processor cores – Higher IO bandwidth when distributed across multiple disks – Writing out of the temporarily-spilled data can be really slowed down while the allocation system does round-robin allocation
› One File – No extra synchronization and work necessary to do the round-robin allocation – Better chance for sequential reads / writes
Install SQL Server 2016 › Planning a SQL Server 2016 Installation › Configuring Operating System › Installing SQL Server 2016 › Configuring SQL Server
Installing SQL Server 2016 › Options for Installing SQL Server 2016 – – – –
Installation wizard Command prompt Configuration file SysPrep
› Performing Post-Installation Checks – Verify that SQL Server services are running – If necessary, view log file information at: %ProgramFiles%\Microsoft SQL Server\130\Setup Bootstrap\Log
SQL Server Unattended Installations › Creating Configuration Files – SQL Server Setup generates a configuration file named ConfigurationFile.ini – It is a text file which contains the set of parameters in name/value pairs along with descriptive comments – It can be used to install SQL Server with the same configuration
› ConfigurationFile.ini can be used – With the Installation Wizard – From the command line
› To use it with graphicall tool – Go to SQL Server Installation Center and click on the Advanced page – Choose Install based on configuration file, specify the location of the ConfigurationFile.ini file and the SQL Server setup.exe file
› Starting with service pack 1 for SQL Server 2008, you can "slipstream" service packs for SQL Server
SQL Server Unattended Installations cont. › To install SQL Server from command prompt: – Go to the SQL Server installation media root folder – Specify the ConfigurationFile.ini as a parameter for Setup.exe: › Setup.exe /ConfigurationFile=ConfigurationFile.ini
– You can override any of the values in the configuration file or add additional values: › Setup.exe /SQLSVCPASSWORD="password" /ASSVCPASSWORD="password" /AGTSVCPASSWORD="password" /ISSVCPASSWORD="password" /RSSVCPASSWORD="password" /SAPWD="password" /ConfigurationFile=ConfigurationFile.INI › Installer does not write passwords into the ConfigurationFile.ini file
– You can also control the level of interaction: › /Q specifies that setup runs in a quiet mode › /QS specifies that setup runs and shows progress through the UI, but does not accept any input or show any error messages › For a fully unattended installation you have to accept the license by using the /IACCEPTSQLSERVERLICENSETERMS switch
Automating Installation summary › Unattended Installation – Setup.exe /q /ACTION=CompleteImage /INSTANCENAME=MYNEWINST /INSTANCEID=
/SQLSVCACCOUNT="" /SQLSVCPASSWORD="" /SQLSYSADMINACCOUNTS="" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS
› Upgrade SQL Server from the command line – Setup.exe /q /ACTION=upgrade /INSTANCENAME=MSSQLSERVER /RSUPGRADEDATABASEACCOUNT="" /RSUPGRADEPASSWORD="" /ISSVCAccount="NT Authority\Network Service" /IACCEPTSQLSERVERLICENSETERMS
Manually install SQL Server on an Azure Virtual Machine › You might want to do a manual install of SQL Server if you require a different version of SQL Server than what is available within the Windows Azure VM Gallery › Or maybe you want to install SQL Server on your own due to the licensing approach you are taking where you want to bring your own license for SQL Server into Azure › Provision a VM with a Windows OS from the Azure Gallery – – – – – – –
Choose a core Windows OS VM, one that is compatible with your version of SQL Server Open TCP ports in the Windows firewall Configure SQL Server to listen on the TCP protocol Configure SQL Server for mixed mode authentication Create SQL Server authentication logins Configure a Network Security Group inbound rule for the VM Configure a DNS Label for the public IP address
Manually install SQL Server on an Azure Virtual Machine cont. › Installing SQL Server on Azure IaaS Is just a normal installation of SQL Server with a few minor exceptions – You will want to enable the TCP protocol for that database server via SQL Server Configuration Manager – You also will want to configure database files to point to the non-OS disks – You should not use D: for tempdb
› A summary of the steps involved in creating and configuring non-OS disks – Attach disks in Azure portal to our existing VM – RDP into the Azure SQL Server VM and initialize them
Install SQL Server 2016 › Planning a SQL Server 2016 Installation › Configuring Operating System › Installing SQL Server 2016 › Configuring SQL Server
Configuring SQL Server › MAXDOP – Adjust this based on the number of physical cores – Turning this to 1 or a low number is not how to adresss parallelism
› Cost Threshold for Parallelism – has been set to 5 for more then 15 years – Can be used to change the number of „seconds” that is considered before opting for a parallel execution
› MAX Server Memory – There is no hard number, consider leave about 3-6 GB for OS
Configuring SQL Server cont. › Query Governor Cost Limit - Specify an upper limit on the time period in which a query can run › SQL Server can run in one of two modes: thread or fiber – By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution – When Lightweight pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber
Instant File Initialization › Instant file initialization is a little-known feature of SQL Server 2005 onwards that allows data files (only, not log files) to skip the usual zero initialization process – It's a fabulous way to reduce downtime when a disaster occurs and you have to restore a database from scratch – 20 GB database creation time reduced from 14 min to 1 sec – 30 GB database restoring time reduced from 21 min to 5 sec
› SQL Server just needs to have SE_MANAGE_VOLUME_NAME permission – SQL Server does a one-time check at startup – Enable trace flag 3004 and 3605 to check it and then create a database – Turning on trace flag 1806 will disable instant file initialization
INSTALL SQL SERVER 2016
Plan a SQL Server 2016 Installation Installing SQL Server 2016 on premises Configuring Operating System and SQL Server Automating Installation
