Module 2: Installing and Configuring SQL Server 2014
1 of 34
Module
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
2014
Contents:
Lesson 1: Lesson 2: Lesson 3: Lab:
Module One of the key servers and databases.
databases SQL Server
on physical servers This module
and how to
install it.
Objectives After completing • Plan a SQL • Install SQL • Perform post-installation
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
2 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Lesson Before starting the requirements specific requirements which it runs
how each of consider the platforms on before
installing SQL
Lesson After completing • Describe considerations • Describe the • Assess CPU • Describe considerations
SQL Server.
• Plan service
Considerations
When planning
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
3 of 34
• Required SQL Server requirements. are required, actually needed. opportunity of the installation
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
consider is which business in case they that are reducing the utilization overhead components
that would • Hardware
installed and the
workloads
the hardware
resources memory, and using the virtual) is organizations other applications
consumes CPU, applications server (physical or some small co-exist with hardware
resources.
where you intend
to install SQL
workload while
providing • Service account
within the
context of
the service,
and is used
plan the
accounts that ensure that that all authenticated
carefully to which states system rights
they need • Default data data in databases,
and manage you can
specify the
a default
location for
specified.
You should
when you
install SQL
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
4 of 34
• Server collation data is sorted you have matches that the server system databases
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
how character and unless collation that can override is used for temporary
objects.
Minimum
SQL Server Hardware Requirements In earlier versions processor, disk space requirements
requirements for speeds and disk processor in
a new laptop Processors In enterprise
significant issue.
While it might
consider
that there is a
all computer
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
5 of 34
architectures
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
require
architectural The information
and x64
processor architectures:
Architecture x86 x64
with EM64T
Disk The hardware
the product.
These values
databases can
have a greater Disk subsystem bound, if it is thing. Any computer somewhere. subsystem), something more constantly recompiling
today is I/O itself, not a bad bottleneck I/O memory or Server is discussed in the next
topic. Software Requirements Like any server
system and
software in order Operating Even though such as the Windows
operating systems, designed for
use on server
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
6 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Similarly, many
editions of
Windows. SQL
and
editions. It is strongly
controller. If you
attempt this, • You cannot
service account
or a network • After SQL domain member
from a you change
the host computer • After SQL domain controller
from a you change
the host computer • SQL Server
service accounts
on a read-only
SQL Server
domain
controllers and Prerequisite In earlier versions,
requirements as part of
the installation
PowerShell
need to be preinstalled
install the
SNAC and the SQL Server, these components Framework often
installation time for preinstalled the .NET components
can further reduce General Software
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
7 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The SQL Server
should
consider installing
to minimize
SQL Server Several components
Explorer® browser.
These components
SSMS,
Business Intelligence
any use of
HTML Help.
Assessing
Planning server allows you to
formula that database size and
the number of CPU CPU utilization on the system. system architectures
are running straightforward, in that few resources. Testing
with realistic Increasing the creating parallel
scope for workloads will
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
8 of 34
make good use large number
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
workloads from a when large
amounts of data Try to ensure that are running services running
Most servers significant applications
such as Microsoft Many new systems traditional symmetric single system
architectures. In a bound to a added. On a
NUMA-based
memory. In
some systems,
sets are
called NUMA
but the local
access to local
CPUs mostly
access their own
and try to
make use of Optimal NUMA
configurations in
the system BIOS
check with
the hardware
specific
NUMA-based Memory The availability
the most
important factors While SQL Server
memory
configuration
minimum, values.
For example,
of memory,
regardless of The majority
space that can
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
9 of 34
directly access memory outside
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
servers is that be directly
accessed) is retrieved
earlier versions
of SQL Server
pages, SQL
Server 2012
increase the
address space
Storage
The performance
subsystem it
is using. Current
key task
during the planning Determining In the first phase including the reads and writes number of random log files. By
determined, and size of produce a high on database large scans on
data files. Storage Styles The second planning
With direct
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
10 of 34
attached storage ( network (SAN)
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
storage area but SAN
storage typically consolidation. One particular generally more
administrators are rather than the
performance
file layouts
with a SAN
for specific
files. Leave the focus on what
administrator. That is, achieved.
RAID Systems In SAN-based independent on a file basis,
of performance and
physical disk For DAS storage,
levels exist,
RAID levels Number of For most current somewhat dated), will hold substantial sufficient I/O Solid state drive (
is now large disks that provide workable. in this area.
Drive Caching Read caching manages its own
already re-read a page
from disk that
caches can
substantially
caches
guarantee a write,
survive failures
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
11 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
and this can Tools for Assessing To help you utilities to simulate
you can use to support.
SQLIOSim SQLIOSim is
download site.
It is designed
to have SQL
Server installed.
server systems
that are targets SQLIOSim is be installed on
does not need to and
command-line While SQLIOSim
performance
testing. The tasks
attempt should
be made to compare
terms of timing.
SQLIO SQLIO is another
download
site. Unlike SQLIOSIM,
is designed to
create entirely
the types of
I/O operations A common way
find issues
with certain
specific
problematic
SQLIO is also a
stand-alone tool
Also, it is
advised to perform
checks one I/O
type at a time.
task.
Planning 26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
12 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server
Server, you
must specify
also
configure service
Server
Configuration
Note:
Server accounts.
SQL Server When you install
available services.
The available • SQL Server • SQL Server
Database Engine. running jobs and
issuing alerts. • Analysis
and
data-mining • Reporting
reports.
• Integration
transform,
and load (
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
13 of 34
• Master Data
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
data items
to ensure • Data Quality
through
operations • SQL Server which means
assignment, from the
available ports.
Server
instances
service is not
required for
the
well-known
that use a
TCP specific
string, the
SQL Server • Full-text
unstructured and
semi-structured • SQL Writer Volume Shadow
Windows you should
only enable
Choosing Service There are several
it is
important to
secure SQL
Server installation.
correct
privileges; however,
accounts, that
have excessive service accounts, job. When you program) to security group under its own
choosing requires to do its installation relevant should run account
being compromised, The different
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
14 of 34
• Domain user
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
choice for
service accounts • Local user
for service
accounts in • Local System by various
account that is used account to run
SQL Server • Local Service privileges
with restricted Windows
services and
and generally
a dedicated
database
server runs
account
instead (see • Network Local System However, services, you Windows
than the resources. Windows on instead (see
below). • Managed system is service accounts).
operating managed introduced in
SQL Server
is
associated
cannot use a
managed account. Additionally, passwords
domain user manually manage create and
configure • Virtual service is Windows accounts). 2012. A virtual
operating system service SQL Server that it is a
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
15 of 34
type of local Unlike managed virtual service
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
account. configure a virtualized
instance of
Lesson After making installation. In SQL Server
proceed to through and how System
Configuration For most users, situations where
For the rare post-installation
checks and troubleshooting.
Lesson After completing • Describe options • Install SQL • Perform an • Describe strategies
Options
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
16 of 34
You can install
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
wizard, from the
command prompt,
methods
provide the same
installation process
for your environment. Installation The SQL Server
SQL Server.
It comprises
product.
The installation
that you want
to install. As
also use it to
add components
Note: computer. permissions.
the local
Command You can also
using switches to
specify the options
the setup
program, to view
mode without
any user interface.
agreement, users
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
17 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
will always be Configuration As well as using use a configuration
you can also identically-configured
instances across The configuration file by running installing the
manually create this then, instead of the
configuration If you use a configuration
command
prompt values SQL Server SQL Server installation later. multiple instances
complete the computers or functionality in
previous versions
Performing
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
18 of 34
You install the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Server 2014
Setup program. • Product Key
must enter
the product • License Terms
the
installation. • Product Updates • Install Setup
prerequisite software. requires to install
SQL Server. • Install Rules
can occur
during setup • Setup Role
that the process
includes the o SQL Server Server,
of SQL Services, and
Integration o SQL Server
for
SharePoint o All Features
and uses the
default After you
to install on
the next • Feature Selection install. You • Instance (on the first • Server Configuration
want to shared features. named instance use. each service
that you are
26/09/2016 15:39
Module 2: Installing and Configuring SQL Server 2014
19 of 34
•