SQL Server Standards - SQLSoldier

brickcompetitiveΛογισμικό & κατασκευή λογ/κού

14 Δεκ 2013 (πριν από 3 χρόνια και 4 μήνες)

73 εμφανίσεις

SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

1

-


















[
Enter Team Name
]



SQL SERVER
PRODUCTION BUILD
-
OUT STANDARDS

























Revision

Summary of Changes

Contributor

December 1, 20
11

Created

Robert L Davis



















SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

2

-


Topic of Contents


1. Introduction

................................
................................
................................
...................

3

2. Configuration

................................
................................
................................
................

3

2.1 Hardware Configuration

................................
................................
........................

3

2.
1.1 Default Storage Configuration
................................
................................
........

3

2.1.2 Minimum Storage Configuration

................................
................................
...

3

2.1.3 Additional Storage Considerations

................................
................................

4

2.1.4 Storage Considerations for Clustering
................................
...........................

4

2.2 Software Configuration

................................
................................
..........................

4

2.2.1 Operating System Installation

................................
................................
........

4

2.2.2 Power Management Plan

................................
................................
................

4

2.2.3 SQL Server Installation

................................
................................
...................

4

2.2.4 Network protocols

................................
................................
............................

7

2.3 Security

................................
................................
................................
....................

7

2.3.1 Security Requirements:

................................
................................
...................

7


































SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

3

-


1. Introduction

The purpose of this document
is to outline the standards
for SQL Server

build
-
out
s

in a
production environment for applications

administered by
[
Enter Team name
]
. These standards
have been designed to aid in

getting SQL Servers deployed efficiently and to eliminate problems
.
Where app
licable
, the

standards

have been designed to take into account the policies
and
guidelines
provided
by
[
Enter Team Name
]
.


This document is intended to guide
i
nfrastructure and any other members of
[
Enter Team Name
]

that deploy SQL Servers to a production environment

and those who plan for future deployments
.
The definition of production is open to interpretation, and each individual application will need to
determine whether environments referred to as UAT, PPE, Sta
ging, etc. is considered production.
For the purposes of this document, production is defined as servers hosted in a production data
center

and

in a production domain
.


2. Configuration


The hardware and software configuration of any server should be
derived during

the
onboarding process
.

The primary driver of these discussions will be the application’s
Service
Level Agreements (
SLA
s)
;

therefore, it is imperative that

t
he SLA be complete before any final
decisions are made concerning the purchase of hardware or the platform configuration. The SLA
provides the exact benchmarks for performance, availability, and redundancy that will
determine
the

appropriate

backend config
uration for the application.

2.1 Hardware Configuration


Storage configuration w
ill be determined during the
onb
oarding process

f
or the
application in question. A representative from the
[Enter Team Name]

will work with the
application OE in determining wh
at the optimal storage configuration should be for the
application

database(s)

(i
.
e.
,

N
umber of LUN’s, RAID types
, and capacity
).


2.1
.1 Default Storage Configuration

Volume

Usage

Directory

RAID

C

Operating System

C:
\
Windows

O or 1

D

SQL Server
executables &
system data/log files

D:
\
Program Files
\
Microsoft SQL Server

0 ir 1

E
-
F

DB & tran log backups

E:
\
Backups

6

G
-
N

User DB data files

G:
\
MSSQL
\
DATA

10

O
-
P

User DB log files

O:
\
MSSQL
\
LOG

10

T

Tempdb
data and log
files

T:
\
MSSQL
\
DATA

10


2.1
.2
Minimum Storage Configuration

Volume

Usage

Directory

RAID

C

Operating System

C:
\
Windows

0 or 1

D

SQL Server executables &
system data/log files

D:
\
Program Files
\
Microsoft SQL Server

0 or 1

E

DB & tran log backups

E:
\
Backups

6

H

User DB data files

H:
\
MSSQL
\
DATA

10

O

User DB log files

O:
\
MSSQL
\
LOG

10

T

Tempdb
data and log
files

T:
\
MSSQL
\
DATA

10

SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

4

-



2.
1
.
3

Additional Storage Considerations

Additional storage considerations should be made if Full
-
text catalogs or a distribution
database (for
replication) is used. Full
-
text catalogs should be given a dedicated drive separate
from database files but of the same type of drive as a data drive. If a distribution database is
present on the server, its data and log files should be placed on a dedicat
ed drive. If the
distribution database is expected to handle a very high load, it should have a dedicated log drive
and a dedicated data drive. For a moderate load, the distribution database’s data and log files can
share a single drive.


When configuring
a server to handle a very heavy replication load, consideration should be given
to whether or not a dedicated drive should be used for the replication data share.


2.
1
.
4

Storage Considerations for Clustering

Clustered servers will have different storage re
quirements than stand
-
alone servers.
Clustered servers must have the SQL Server system data and log files stored on a shared clustered
drive. For clustered servers, the files for the system databases can be stored on an additional
dedicated drive or they c
an be included on the drives with the user database and log files. If
stored on the same drives as the user databases, the system database log files should be stored
separated from the data files in the same manner as the
user databases.


Minimum storage c
onfiguration for a clustered server:

Volume

Usage

Directory

RAID

C

Operating System

C:
\
Windows

0 or 1

D

SQL Server executables

D:
\
Program Files
\
Microsoft SQL Server

0 or 1

E

DB & tran log backups

E:
\
Backups

6

H

User/System DB data files

H:
\
MSSQL
\
DATA

10

O

User/System DB log files

O:
\
MSSQL
\
LOG

10

Q

Quorum drive

(if used)

Q:
\

Any

R

DTC (if used)

R:

Any

T

Tempdb files

T:
\
MSSQL
\
DATA

10

2.2 Software Configuration

Only RTM software will be deployed in production environments unless
an
exception has been

granted
.
External

software will only be deployed if
properly

license
d.

2.2.1 Operating System I
nstallation

Host machines

will be running
the latest supported version of Windows

Enterprise
Edition
with
the latest
approved
service packs
.
Only the default
services will be enabled on the
servers

unless
a hosted

application
requires

additional
services.

All OS installations and upgrades
will be performed by the
appropriate

team
.

2.2.
2

Power Management Plan


Al
l servers hosting SQL Server will run using the hi
gh performance power plan.

2.2.3

SQL Server I
nstallation

All SQL instances will be
installed

using the latest version available unless
otherwise requested by the application
team. O
nly those SQL components that are
SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

5

-


immediately required for the application

may be installed
. Additional features can be installed as
needed.


Stand
-
alone instances are the preferred installation type for
all

applications.
Multiple instances
are not allowed unless
am exception has been granted.
Team after reviewing the architectu
ral
design and being provided business justification for using multiple instances.


A stand
-
alone instance of SQL Server will have the same name as the physical machine. Multiple
instances of SQL Server can be installed
t
o a single machine provided the des
ign has been
approved by the Inf
rastructure and SQL V
-
Teams.
[Edit:
Proprietary info

deleted]


Each SQL instance and associated services will run using a dedicated non
-
user domain account
owned by the
[Edit: Name deleted]

Infrastructure team

or by the oper
ations team/engineer
assigned to the application.


1.

Multi
-
Instance

Memory
Configuration



The following configuration changes
will be made as part of the SQL Server deployment by the installer

a.

Maximum and minimum memory

will be configured
according to the
needs of the relative weight of the hosted applications
, if known
. For
example, if a server hosts 2 instances and
1

instance hosts a larger OLTP
database subjected to frequent updates/deletes that instance would have a
greater relative weight. Its minimum
and maximum memory settings
would be greater than the other instance.

b.

Prior to
con
figuring the
maximum memory
, a determination must be made as to
how much memory is required to be

left

free

for the Operating System, external
processes, and non
-
buffer poole
d SQL processes. The amount of memory
allocated to the SQL instances will be limited to the total memory minus the
amount required to be left free.

i.

For a 4 GB server, a minimum of 1 GB of RAM will be left free for the
OS

ii.

For 8 GB’s or more, a minimum of 2
GB of RAM will be left free for the
OS

iii.

For 32 GB or more a minimum of 4 to 6 GB of RAM is recommended,
but not required,
to be left free
for the OS

iv.

A minimum of 512 MB

per instance

will be left free for the non
-
buffer
pooled SQL processes

v.

Additional memory

will be required for the non
-
buffer pooled SQL
processes i
f

DTS Packages or SSIS Packages are expected to run

c.

The sum of the maximum memory configured for all instances is not to exceed
the amount of memory determined to be
remaining after calculating the

amount
to leave free for the OS.

d.

Example: Server has 32 GB of RAM and will have 4 SQL instances installed on
it. There will be no other services or applications hosted on the server and neither
SSIS nor SSAS will be used.

The 4 instances are weighted equa
lly in terms of
application needs


2GB of RAM is determined as required for the OS.

512 MB is determined to be required for non
-
pooled SQL processes per instance.
(4 instances X 512 MB = 2 GB)

28 GB of RAM is available to be assigned to the SQL instances (
32 GB


2 GB


SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

6

-


2 GB)
\

7 GB is assigned as the maximum memory for each instance (28 GB / 4 = 7 GB)

2.

Single Instance

Memory
Configuration



Memory management is not as
critical for single, standalone instances of SQL Server and does not need to be set
at
install time. The setting can be set later by the OE when a better determination
can be made regarding OS and non
-
SQL memory requirements.

When setting the
maximum memory settings, the following should be considered.

a.

Prior to configuring the maximum memory
, a determination must be made as to
how much memory is required to be left free for the Operating System, external
processes, and non
-
buffer pooled SQL processes. The am
ount of memory
allocated to
SQL
Server

will be limited to the total memory minus the a
mount
required to be left free.

i.

For a 4 GB server, a minimum of 1 GB of RAM will be left free for the
OS

and other processes

ii.

For 8 GB’s or more, a minimum of 2 GB of RAM will be left free for the
OS

and other processes

iii.

For 32 GB or more a minimum of 4 to 6

GB of RAM is recommended,
but not required,
to be left free
for the OS

and other processes

iv.

Additional memory will be required for the non
-
buffer pooled SQL
processes i
f

DTS Packages or SSIS Packages are expected to run

b.

The sum of the maximum memory config
ured for all instances is not to exceed
the amount of memory determined to be remaining after calculating the amount
to leave free for the OS.

c.

Example: Server has 32 GB of RAM and will have 4 SQL instances installed on
it. There will be no other services o
r applications hosted on the server and neither
SSIS nor SSAS will be used. The 4 instances are weighted equally in terms of
application needs


2GB of RAM is determined as required for the OS.

512 MB is determined to be required for non
-
pooled SQL
processes per instance.
(4 instances X 512 MB = 2 GB)

28 GB of RAM is available to be assigned to the SQL instances (32 GB


2 GB


2 GB)
\

7 GB is assigned as the maximum memory for each instance (28 GB / 4 = 7 GB)

3.

System Databases


a.

Master


will reside
on the same drive hosting SQL Server executables.

b.

Model


will reside on the same drive hosting SQL Server executables.

c.

MSDB


will reside on the same drive hosting SQL Server executables.

d.

TempDB



i.

Data & log files will reside on the T$ drive
.

ii.

Number of d
ata files will equate to the number of
CPU cores

(
ex.
if the
machine has 2

quad
-
core
CPU’s then there would be 8
data files
for the TempDB
).
This helps to solve potential performance
problems that are due to I/O operations. Increasing the number of
files
helps to avoid a latch contention on allocation pages
(manifested as a UP
-
latch).

By having the larger number of files,
you can increase the number of physical I/O operations that SQL
Server can push to the disk at any one time. The more I/O that
SQL Serve
r can push down to the disk level, the faster the database
SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

7

-


will run.

iii.

Initial size of data files should be based on expected TempDB
usage.
They will be of equal size and will not allow Auto
-
growth.

iv.

Initial size of the log file should be twice the size of a
single data
file. The log file should allow Auto
-
growth using a constant value
and not a percentage.

v.

Since the tempDB files will be the only thing on the T$ drive, the
files should be pre
-
sized to use about 90% of the drive space.

vi.

To compute the log files,

take the full size of the T drive
, multiply by
90%,

and divide it by the number of data files plus 2 for the log file.
Round down to the nearest GB, and leave any extra free for expansion.


For example, if you have a
10
0 GB T drive on a 4 CPU server
, the
calculation would be:


(
10
0 * .9) / (4 + 2)

= 15 GB (4 data files of 15 GB, 1 log file of 30 GB)

2.2.4

Network protocols

All SQL Servers will be listening on TCP/IP
, named pipes and shared memory
.


2.3 Security

Application requirements will determine the logins, users and groups that need to be
created.
Access to the SQL instance should be managed via domain security groups only.

The
following SQL Server and application security requirements are strongly recommen
ded:

2.3.1 Security R
equirements:

1)

Only
o
perations
security groups
should be member

of the machine Administrators group.
Individual user accounts or application/service accounts
should NOT be added to
machine Administrators group.


2)

Permissions for the SQL S
erver service accounts should not be configured manually. The
configuration should be performed
as part of the

install.


3)

The f
ollowing Local Security Policy settings are required

for the SQL Service account
and will be set by the
installer

w
hen setting the

service account. They are only called out
here in case the installation was not performed correctly.

a.

Act as a part of the operating system

b.

Adjust memory quotas for a process

c.

Lock pages in memory

d.

Log on as a batch job

e.

Log on as a service

f.

Create a token
object

4)

The built
-
in administrators group
should be deleted after ensuring that the proper
accounts have access.


5)

Limit the number of sysa
dmins on SQL server. Only
the o
perations
t
eam security groups
and any non
-
user domain account used to run SQL
Server Ag
ent service account

will be
SQL Server Production Buil
d
-
out Standards




[Enter

Confidentiality
S
tatement
]

-

8

-


members of the
s
ysadmin server role. Individual user accounts or application/service
accounts are NOT allowed to reside in the
s
ysadmin server role.


6)

Do not add Users/Admins directly to SQL Server, add them via security groups.


7)

SQL
Servers should be configured to use

Windows
Authentication Mode”
only.

Usage
of “SQL Server Authentication Mode”
must

be approved by
the operations team.