How to Perform a New Install of SQL Server

There are many blog posts on the Internet that will walk you through a basic installation of Microsoft’s SQL Server. This is not one of them. This is part three in a series of posts exploring more complicated installations. Today we will focus on installing the SQL Server Database Engine to our server.

The SQL installation wizard contains all of the SQL features in one installer. Installing “All Features With Defaults” may be fine for a test server or your personal workstation, but not for a production database server. This guide is a detailed process that covers a majority of the challenges with installing SQL Server. Application specific installations may vary from this guide, and very large, high performance database servers will require additional tuning, but this guide will get you on the right track and provide significantly better out-of-the-box performance for your SQL Server.

Final Preparations of the Server

1.  Go back and read <this blog> to prepare your operating system for SQL Server.

2.  Configure the storage:

a.  Open Server Manager -> File Services -> Disks

b.  Online the disks that you’ve added for SQL, using NTFS for all of them.

c.  Format the “SQL Programs” disk, where you will install SQL executables, to 4 K. Format every other disk to 64 K,   including all SQL data file disks and all SQL log file disks.

Perform Installation

1.  Download the installer, the latest service pack, and the latest cumulative update.

2.  Run setup.exe in the top level directory of the downloaded full installation ISO.

3.  Figure 1 below, shows the Installation Center.

a.  The Installation Center contains quite a few resources to assist with your SQL Server deployment.

Figure 1

b. Find the “Installation” page as highlighted above and click “New SQL Server stand-alone installation…” from Figure 2 to get started.

Figure 2

4.  Work through any failures or warnings on the set up checks in Figure 3.

Figure 3

5.  Choose New Install, enter your product key (or choose Express/Eval), and accept the license terms.

6.  Do not choose “All Features with Defaults” even though it is an option in Figure 4. It’s a horrible option for a production SQL server

a.  To select which features you will need on this server, choose the “SQL Server Feature Installation.”

Figure 4

b.  Choose your required features by selecting checkboxes in Figure 5.

Figure 5

c.  Select only the ones that this server was scoped for and only the ones the business requested.

7.  Update the Instance Root directory to the separate SQL Binaries drive (if you followed the previous configuration blogs) as highlighted in Figure 6.

Figure 6

8.  Confirm the Disk Usage Summary has enough disk space on the next page.

9.  Enter in your SQL DB Engine domain account and password, and SQL Server Agent account and password in Figure 7.

a.  Don’t forget to click the Collation tab.

b.  Set the collation for the server to match your application requirements.

Figure 7

10.  This next screen, Figure 8, is one of the most important pages in the installation process.

a.  Choose “Mixed Mode” and set an ‘sa’ password—pick a good one with a mix of capitals, numbers and symbols. Make a copy of the password and save it somewhere secure.

b.  Add a domain group where you will add users given SysAdmin rights to this SQL Server.

c.  Add any specific users that will be SysAdmins on this SQL instance.

Figure 8

d.  Click over to the Data tab (Figure 9).

Figure 9

e.  Set the data root directory to the primary SQL Data drive.

f.  Set the other directories according to your disk plan.

g.  Click to the FILESTREAM and enable this feature if your application requires it.

11.  Click Next through the rest of the wizard and you’re through the hardest part of the installation.

Install Service Packs and Updates

1.  Run the service pack installation file and clear any errors or warnings in Figure 10.

Figure 10

2.  Accept the license terms, click Next, and then select the instance(s) to patch on the next screen.

3.  Confirm no files are in use.

a.  Close any applications referenced on the screen in Figure 11.

b.  Use Task Manager if needed.

Figure 11

4.  Click “Update” to perform the service pack installation and close the installer when finished (Figure 12).

Figure 12

5.  Almost done with the install. Now it is time for the cumulative update.

6.  Download the self-extracting .zip file and extract it to a known file path.

7.  Run the setup file.

8.  You are prompted with the familiar update condition check (Figure 13) that was in the previous installers as well.

Figure 13

9.  Agree to the license terms, select the instance(s) to patch, and run another check.

a.  Confirm no files are in use in Figure 14.

b.  Close any applications referenced by the check.

c.  Use Task Manager if needed.

Figure 14

10.  Click next to review the update configuration (Figure 15), and run the final update.

Figure 15

11.  Finally! SQL Server is up to date if everything succeeded in Figure 16.

Figure 16

Configure TempDB

1.  TempDB can require a significant effort to tune on its own.

2.  The SQL team at Microsoft recommends a tempdb data file for each CPU core on your server.

a.  That recommendation can be excessive for servers with a high number of cores and/or less tempdb usage.

b.  But for our four core basic server, that is where we will start.

c.  Run this script on your server to modify the existing tempdb files and create three new ones:

use master

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME ='T:\MSSQL\DATA\tempDB.MDF', size = 1GB, FILEGROWTH = 256MB)

ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME ='U:\MSSQL\LOGS\templog.LDF', size = 1GB, FILEGROWTH = 256MB)

ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev2', FILENAME ='T:\MSSQL\DATA\tempdev2.NDF' , SIZE = 1GB , FILEGROWTH = 256MB)

ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev3', FILENAME ='T:\MSSQL\DATA\tempdev3.NDF' , SIZE = 1GB , FILEGROWTH = 256MB)

ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev4', FILENAME ='T:\MSSQL\DATA\tempdev4.NDF' , SIZE = 1GB , FILEGROWTH = 256MB)

3.  Restart the SQL instance for the changes to take effect.

Configure SQL Engine

1.  Set model database auto-grow.

a.  In SSMS, expand Databases, expand System Databases, right-click on Model and choose properties.

b.  Click Files to bring you to Figure 17.

Figure 17

i.  For data and log files, configure auto-grow to a more appropriate interval.

ii.  Set to 64 MB instead of the default 10% as a better starting point.

2.  SQL Min/Max memory:

a.  The default settings are a joke, honestly.

b.  Right-click on the instance name in SSMS, choose properties, and select Memory to bring you to Figure 18.

Figure 18

c.  Set the maximum memory (in MB) to 2 GB less that the total amount of memory on the server

i.  If you are running additional services like SSRS, SSIS, etc., reduce the maximum memory accordingly.

ii.  If you are running third-party applications, make adjustments for their memory requirements as well.

d.  Set the minimum memory to 0-4 GB less than the maximum memory setting.

3.  Cost threshold and max degree of parallelism:

a.  On the same page from setting SQL Memory, choose the Advanced page and scroll down (Figure 19).

b.  The default cost threshold setting is a poor choice and leaves little room for tuning.

c.  Set it to 30 to “encourage” SQL server to use single threaded queries where execution can be faster than building a massively parallel plan.

d.  Many Microsoft apps recommend setting Max Degree of Parallelism (MAXDOP) to 1 to eliminate parallelism (follow this recommendation at your own discretion).

e.  Set this to the number 4 for a small server, to 8 if you have more than 8 CPU cores.

Figure 19

4.  Database Mail (Engine and Agent):

a.  From SQL Server Management Studio (SSMS), connect to an instance of SQL Server.

b.  Expand Management, right-click Database Mail, and select Configure Database Mail.

c.  Choose the Set up Database Mail option to set up Database Mail for the first time as in Figure 17.

Figure 20

d.  Choose one of the other options for specific database mail maintenance tasks:

i.  Manage Database Mail accounts and profiles

ii.  Manage profile security

iii.  View or change system parameters

SQL Maintenance Tasks

1.  Configure Index maintenance, integrity checks, and backups.

a.  I recommend Ola Hallengren’s excellent maintenance to get started.

2.  Create an Operator:

a.  Use IT/DBA group email address for the operator

EXEC dbo.sp_add_operator @name = 'The DBA Team', @enabled = 1, @email_address = 'superheroes@mycompany.com'

3.  Add alerts:

a.  Set up alerts for common and significant issues.

b.  A great script is all ready for you here.

Congratulations! You made it through a better SQL installation than 90% of all IT administrators out there! Now, all that is left to do is configure your monitoring solution and start granting users and applications access to the server.

Setting SQL Server up through following this guide is a fantastic start. SQL Server is constantly changing as the data it serves changes. Care and maintenance are paramount to maintaining health and getting the best performance possible out of your hardware. Reach out to Credera for a quick database health check, assistance in migrating to SQL 2012 or 2014, moving your databases to SQL Azure or Azure IaaS, or even building in some High Availability or reporting replica databases with Always On Availability Groups.

To contact a SQL Ninja, email info@credera.com or call 972.759.1836. For more information on Microsoft Windows Server 2012 R2 and SQL Server 2012 and 2014, please visit our blog or reach out to us at findoutmore@credera.com.

• Collaboration Sites
• Nodeschool