Pre and post tips to installing sql server correctly

th EVENT Pre and Post tips to Installing SQL Server correctly SQL Server 2008, 2008R2, 2012,2014Antonios Chatzipavlis Database Architect • SQL Server Evangelist • Trainer MCT, MCSE, MCITP, MCPD,MCSD,MCDBA,MCSA,MCTS, MCAD,MCP, OCA,ITIL-F Jan 22, 2015 51

I have been started with computers. I started my professional carrier in computers industry. I have been started to work with SQL Server version 6.0 I earned my first certification at Microsoft as Microsoft Certified Solution Developer (3rd in Greece) and started my carrier as Microsoft Certified Trainer (MCT) with more than 20.000 hours of training until now! I became for first time Microsoft MVP on SQL Server I created the SQL School Greece (www.sqlschool.gr) I became MCT Regional Lead by Microsoft Learning Program. I was certified as MCSE : Data Platform, MCSE: Business Intelligence Antonios Chatzipavlis Database Architect • SQL Server Evangelist • Trainer • Speaker MCT, MCSE, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA, ITIL-F  1982  1988  1996  1998  2010  2012  2013

Follow us in social media Twitter @antoniosch / @sqlschool Facebook fb/sqlschoolgr YouTube yt/user/achatzipavlis LinkedIn SQL School Greece group Pinterest pi/SQLschool/

Selecting and Sizing the Server

51th AUTOEXEC.GR EVENT SQL Server places different demands on its underlying hardware depending on what type of database workload is running against the instance of SQL Server • OLTP workloads • OLAP workloads You also have to keep in mind that very few database workloads are pure OLTP- or pure DW-type workloads, so you will often have to deal with mixed workload types. You also might have to host multiple databases on a single SQL Server instance, where each database has a different type of workload Understanding your Workload

51th AUTOEXEC.GR EVENT • Characterized by a high number • of short-duration transactions • and queries that are usually executed on a single thread of execution. • They can have a higher percentage of write activity • The data in some tables can be extremely volatile. • These characteristics have important implications for the hardware selection and configuration process. OLTP Workloads

51th AUTOEXEC.GR EVENT • Characterized by longer running queries against more static data. • These queries are often parallelized by the query optimizer, so having a higher number of physical cores in your processors can be very beneficial. • Having a large amount of physical RAM is very useful for DW workloads • Because you will be able to have more data in the SQL Server buffer cache, which will reduce the read pressure on the I/O subsystem. • Tends to have very little write activity • DW-type queries read large amounts of data as they calculate aggregates • So good sequential read I/O performance is very important. • Which will also affect how you configure your I/O subsystem in terms of storage type and RAID level OLAP Workloads

51th AUTOEXEC.GR EVENT • Server Processor Count Selection • One common mistake is to assume that a “bigger” server in terms of physical processor count is a faster server compared to a smaller server. • Processor Vendor Selection • Intel or AMD • Processor Model Selection • The performance of SQL Server is hugely dependent on the size of the L2 and L3 caches. • Economizing on the L2 and L3 cache size is not usually an good choice. Processor Selection

51th AUTOEXEC.GR EVENT • The basic rule of thumb for SQL Server is that • You can never have too much memory • Total number of memory slots • More slots is better • SMP vs NUMA Memory Selection

Selecting and Configuring Windows OS

51th AUTOEXEC.GR EVENT • Windows Server 2012 R2 • Highly recommended especially for server than need to be highly available. • Avoid Windows Server 2008 R2 and older versions Choosing Windows OS

51th AUTOEXEC.GR EVENT • If you plan to use AlwaysOn AG it is important to apply the following patches • Windows Server 2008 R2 SP1 – KB2545685 • Windows Server 2012 – KB2784261 • Windows Server 2012 R2 – KB2920151 • Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups Apply patches and hotfixes on WinOS

51th AUTOEXEC.GR EVENT • SQL Server does not need a giant page file • If SQL Server is the major service on the box a 2GB page file on system drive it’s enough • Beware removing the page file (KB254649) Configure the Windows page file

51th AUTOEXEC.GR EVENT • Confirm that the Windows power plan is set to high performance • Confirm that the processors are running at full speed using CPU-Z Power Option

51th AUTOEXEC.GR EVENT • The servers is public on the Internet. • The server have open ports to servers that are not behind a firewall. • The server read or execute files from other servers. • The server run HTTP servers • The server hosting file shares. • The server use Database Mail to handle incoming or outgoing email messages. Use Anti-Virus when…

51th AUTOEXEC.GR EVENT • Directories of SQL Server instance • SQL Server data files • SQL Server backup files • Full-Text catalog files • Trace files • SQL audit files • SQL query files • SQL Server service Setting Anti-Virus exclusions

Configuring Disks Storage subsystem

51th AUTOEXEC.GR EVENT • Minimum RAID 1 for all drives • Including OS system drive • Even SSD or PCI-Express • RAID 10 for best performance • Use 128GB drive for OS system drive • Test the I/O performance SQLIO/SQLIOSIM Use RAID

51th AUTOEXEC.GR EVENT • OS System drive should be formatted with the default (4K) cluster size. • All drives holding data & log files should be formatted with 64K cluster size • Check your storage for partition alignment • Follow this rule even if it’s a VM on shared storage Disk Drive Format

51th AUTOEXEC.GR EVENT • SQL Server application folders • SQL Server database data files • SQL Server database log file • including TempDB • TempDB data files • Backups Use separate drive for

51th AUTOEXEC.GR EVENT • The connectivity with SQL Server is important! • You have Failover clustering or Availability Groups • It’s a good practice even for standalone server • Teaming NICs Use Multiple Physical Network Cards when

Configuring Server for Security and Performance

51th AUTOEXEC.GR EVENT • Use dedicated domain user account with no special rights on the domain. • You do not need or want this account to be a local admin on the machine where SQL Server will be installed. • Use a separate, dedicated domain user account for the SQL Server Agent service. • If you are going to be installing and using other SQL Server related services, you will want dedicated domain accounts for each service. SQL Server services accounts

51th AUTOEXEC.GR EVENT • Enable Instant File Initialization • Perform Volume Maintenance Tasks • Grant Lock pages in memory • Common on SQL Server 2005 / Windows 2003 • Less common with newer versions (Still it is a good idea to enable LPIM on a new system) • Add the permissions to the Service Account in AD - KB319723 • readServicePrincipalName • writeServicePrincipalName Policy Settings and Rights for the SQL Server service account

51th AUTOEXEC.GR EVENT • Install only the features you actually need • This will reduce your attack surface • It will speed future maintenance of the instance because there are fewer components to patch • Install Services Packs or CUs • Enter a strong password for the sa account if you choose Mixed Mode authentication • Set the Data Directories according to plan • Do not use C: drive SQL Server Installation

51th AUTOEXEC.GR EVENT • 1118 • This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). • 2371 • that you can use to control when the query optimizer generates autostats on a table Trace Flags to enable

51th AUTOEXEC.GR EVENT • Enable compressed backups • Setting Default backup media retention (days) • Setting Database default location for • Data files • Log files • Backups Server Properties

51th AUTOEXEC.GR EVENT • Max Worker Threads • Priority Boost • Lightweight Pooling • Maximum number of concurrent connections • Network Packet Size Server Properties

51th AUTOEXEC.GR EVENT • Set Max Server Memory • Important when LPIM is enabled • Use this formula to calculate SQL Server Memory • Reserve 1GB for OS • Reserve 1GB for each 4GB after the first 4GB and until 16GB • Reserve 1GB for each 8GB after the first 16GB • Monitor the Memory:Available MB performance counter SQL Server Memory Server Memory 64GB 1GB for OS 3GB for 4-16GB 6GB for 16-64GB 10GB in total 64-10 = 54 Max SQL Server memory

51th AUTOEXEC.GR EVENT • Change the default size for data and log files • Change the file growth to fixed units Tweak Model database

51th AUTOEXEC.GR EVENT • Move TempDB to its own drive • Grow the size of data file • Add additional data files as the number of logical processors up to 8 logical CPUs • Each file must have the same size • Pre-allocate the space. • KB2154845 Configure TempDB

51th AUTOEXEC.GR EVENT • Set this to the number of physical cores in a single NUMA node socket on your hardware or less • Always use an even value • Use the value of 1 only of you have specific vendor requirements • SharePoint • BizTalk • SAP • KB2806353 Configure MAXDOP

51th AUTOEXEC.GR EVENT • General default value of 5 is low for most OLTP workloads and should be increased. • Base value of 20-25 used for most server installs. Cost Threshold of Parallelism

51th AUTOEXEC.GR EVENT • Control the amount of memory that is used by single-use, ad hoc query plans in the plan cache. • SQL Server store only a small stub of an ad hoc query plan in the plan cache the first time that the ad hoc plan is executed • Reduces the memory required for that plan in the plan cache. • It’s not a panacea for single-use ad hoc query plans • http://www.sqlschool.gr/blog/do-you-have-optimize-for-ad- hoc-workloads-on-sql-server-2008-r2-instances-380.aspx Optimize for Ad-hoc workloads

51th AUTOEXEC.GR EVENT • Enable TCP/IP • Firewall exceptions • TCP port of instance • UTP 1434 for SQL Browser SQL Server Network Connectivity

51th AUTOEXEC.GR EVENT • Configure Database Mail • Create Operators • Configure SQL Agent to use Database Mail • Create Alerts for Severity 16 to 25 • Create Alerts for Errors 823, 824, 825 • Adding Ola Hallengren’s Maintenance Solution • Install Adam Machanic sp_WhoIsActive • Install and run Brent Ozar sp_Blitz Configure Alerting and Monitoring

51th AUTOEXEC.GR EVENT • Don’t use the default file size • Don’t use presentence as file growth • Pay attention on T-Log size and growth to produce equal VLFs • Use more than one filegroups • In PRIMARY leave system object • Put all user objects to another filegroup • Use more than one data files • Even this are in the same drive Create Database

51th AUTOEXEC.GR EVENT • Don’t set Auto Close • Don’t set Auto Shrink • Don’t unset Auto Create/Update Statistics Database Properties

Pre and post tips to installing sql server correctly

More Related Content

What's hot

Viewers also liked

Similar to Pre and post tips to installing sql server correctly

More from Antonios Chatzipavlis

Recently uploaded

Pre and post tips to installing sql server correctly