SQL Server 2012:
Installation and Configuration
Module 4: Installing SQL Server 2012
Glenn Berry
[email protected] Introduction
There are several important choices to make during installation
These affect security, performance, scalability, and maintainability
Selecting which instance and shared features to install
Only install the features you actually need now
Instance configuration
Default instance or a named instance
Specifying service accounts
You must select which accounts to use for each service
Changing SQL Server Agent to automatic startup
Database Engine configuration tasks
Server configuration tab
Data directories tab
FILESTREAM tab
Error reporting
Disabled by default
Selecting Which Features to Install
Only install the features you actually need right now
This reduces your attack surface
It reduces resource usage
It also reduces your patch surface, so fewer reboots are required
It is relatively easy to add additional features later
Don’t install features now that you “might” need later
Simply run setup from the Control Panel
Use a development or test instance for experimentation
You can install all features there for learning purposes
You can use Developer Edition on a workstation
You can use the free Enterprise Evaluation Edition for experimentation
Feature Selection Screen
Instance Configuration
You must choose between the default instance or a named instance of
SQL Server
You can have only one default instance of SQL Server
Normally, the first installed instance is the default instance
You can have up to 49 named instances of SQL Server on a machine
There is a 16-character limit for the name of a named instance
It is more common for production servers to only have a single
instance of SQL Server installed
Multiple instances compete for resources
Multiple instance are harder to manage and maintain
Named instances are more useful for development and testing
They allow you to have different versions of SQL Server on the same
machine
Instance Configuration Screen
Specifying Service Accounts
You need to specify accounts for each SQL-related service
These should be the domain accounts that were previously created
This will depend on which services you decide to install:
SQL Server Agent service
SQL Server Database Engine service
SQL Server Analysis Services service
SQL Server Reporting Services service
SQL Server Integration Services 11.0 service
You must enter the credentials for each account during setup
You need to have the Account Name and Password for each one
Server Configuration Screen
Changing SQL Server Agent Properties
By default, the SQL Server Agent service is set to Manual start
You should change this to Automatic start during installation
Otherwise you may forget to change it later
This is especially important for replication and for log shipping
Use SQL Server Configuration Manager to change it later
Do not use the Windows Services applet to make changes to SQL Server-
related services
The Windows Services applet does not handle all SQL Server settings
properly
Database Engine Configuration Tasks 1
Server Configuration tab
You must choose the Authentication Mode
Windows authentication mode
More secure, but requires domain logins
Mixed Mode authentication
Less secure, but required for many legacy applications
With Mixed Mode you must enter a system administrator password
Make sure to use a strong password, and don’t forget what it is
You should also add at least one SQL Server administrator
Typically, you add your current Windows domain account
You can add more administrators after installation
Database Engine – Server Configuration tab
Database Engine Configuration Tasks 2
Data Directories tab
Make sure to change the default data directories
They will be on the C: drive by default, which is usually not optimal
They are located on the Data Directories tab, so they are easy to miss
Change these from the defaults:
User database directory
User database log directory
Temp DB directory
Temp DB log directory
Backup directory
Database Engine – Data Directories
Database Engine Configuration Tasks 3
FILESTREAM tab
If you are going to use the FILESTREAM feature, you need to enable it
here
Enable FILESTREAM for Transact-SQL access
Required before the other options are available
Enable FILESTREAM for file I/O streaming access
Allows Win32 streaming access for FILESTREAM
Windows share name
The name of the file share where the FILESTREAM data will be stored
Allow remote clients to have streaming access to FILESTREAM data
You must enable this in order for remote access to function
Database Engine - FILESTREAM
Error Reporting
Microsoft collects telemetry information about fatal SQL Server errors
for these components:
Database Engine
SQL Server Agent
Analysis Services
Reporting Services
Integration Services
Replication
This is very useful information for Microsoft
This helps them improve the product over time
It is your decision whether to allow this or not
You can change this setting later
Use the Error and Usage Report Settings dialog
Error Reporting Screen
Summary
Proper SQL Server installation is very important
Provides better performance, scalability and security
Reduces the amount of configuration work needed after installation
Makes the instance easier to maintain in the future
Take your time and be detail oriented
It is very easy to miss important settings
What is Next?
Module 5 will cover post-installation configuration tasks for SQL
Server 2012
The importance of updating SQL Server 2012
Obtaining Service Packs and Cumulative Updates
Setting instance-level properties with the SSMS user interface
Setting instance-level properties with T-SQL
Configuring tempdb data files
Adding common instance-level trace flags
