Shahar Bar – CEO – Valinor Ltd.

[email protected]
http://www.valinor.co.il
This Seminar agenda

SQL Server basics and installation process

Managing logical and physical database files
SQL Server Maintenance
Automating Administrative tasks
Monitoring SQL Server
Securing your database
Few last things in a nutshell….

Let’s Go!
Relational Database Server Goals

Reliability Availability Scalability

Data Integrity Transaction

Performance
and Protection Isolation

Reporting Data Analysis

Choose the right SQL Server edition

Enterprise
Standard

Workgroup

Web

express Datacenter
Parallel Data Warehouse
compact Read more at:
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
SQL Server Architecture

SQL Server Database Engine

Storage Engine
Query Engine
Databases
Logical collections of related objects
Instances
Separate running services of SQL Server
Default instance and named instances
SQL Server Services and components

• Instance-Specific • Instance-unaware
(one service per instance): Notification Services
SQL Server Integration Services
SQL Server Agent (SSIS)
Analysis Services SQL Server Browser
(SSAS) SQL Server Active
Reporting Services Directory Helper
(SSRS) SQL Writer
Full-Text Search (iFTS)
SQL Server install – best practices

Plan your hardware (with emphasis on disks: RAID, Volume

and Block Size (64KB...)
When storage is local – consider separating logs from
Data
For Large deployments – consider using separate disk for
TEMP DB
Use detailed installation, not the default one.
Install only needed components. If you don’t plan to build
cubes – don’t install OLAP….
Make sure you install the components on the correct disks
SQL Server install – best practices

Create at least one new Active Directory account for SQL

Server Service (simple domain account, no administrative
privileges!)
Configure antivirus software to skip databases folders.
Make sure you have latest service pack and CU ready to
install
Make sure you know needed default collation
Consult your application provider for further
configuration

Read more at:

http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-
before-the-install/
SQL Server Admin. Tools

SQL Server Management Studio

Database management GUI
Object browser; templates, reports, etc.
Based on Visual Studio UI
Support for writing and executing queries

SQL Business Intelligence Dev. Studio

SSAS, SSRS, SSIS
SQL Server Admin. Tools

SQL Server Profiler

Database Engine Tuning Advisor
SQL Server Configuration Manager
Manages services and protocols
Surface Area Configuration (SQL Server 2005)
Policy Based Management (SQL Server 2008)
SQL Server Books Online
SQL Server Physical Data Files

Database storage
Primarily table data and index data
Database Files:
Primary data file (*.mdf)
Secondary data files (*.ndf)
Transaction log file(s) (*.ldf)
Filegroups:
Logical collections of files
Objects can be created on filegroups
Physical DB Structure

Data file:
.mdf or .ndf
Log file:
.ldf

Extent: 8 contagious
pages – 64K

Page: 8KB
The Transaction Log

1 Data modification is
sent by application

3 Modification is recorded
Buffer Cache in transaction log on disk

Disk

Data pages are located in, Disk

2 or read into, buffer cache
and modified Checkpoint writes
committed
4 transactions
to database
Managing database files growth

Data and log files are configured to grow

automatically by default
Default growth values are bad! (1MB for data files,
10% for log files)
Best Practices:
Change auto growth increment to 300-400MB for
both files
Be proactive! – increase size of files in advance.
Avoid shrinking files! It creates fragmentation and
harms performance
Comparing RAID levels

RAID Level RAID Disk Space Read Write

Description Cost Performance Performance

RAID 1 Disk 50% of total No change No change

Mirroring disk space

RAID 5 Stripe Set Equivalent to Increased Decreased

with Parity the size of one
disk in the
array.
RAID 0 + 1 or Mirrored 50% of total Increased No change
Stripe Sets disk space
RAID 10
Database Recovery Models

Recovery Models define the way you will be

able to restore your database, by changing the
transaction log behavior
Three recovery models exist:
Simple
Full
Bulk-logged
Simple Recovery Model

No log backups.
Automatically reclaims log space to keep space
requirements small, essentially eliminating the
need to manage the transaction log space.
Changes since the most recent backup are
unprotected. In the event of a disaster, those
changes must be redone.
Can recover only to the end of a backup.
Full Recovery Model

Requires log backups. Transactions will not be

truncated from T-Log, unless backed up by
T-Log backup!
No work is lost due to a lost or damaged data
file.
Ability to recover to any point in time (for
example, prior to application or user error).
No work is lost, unless tail of transaction log is
damaged
Bulk-logged Recovery Model

Requires log backups.

Permits high-performance minimally logged
operations (index re-build, bulk inserts) by
logging allocation/de-allocation rather than
the data itself.
point in time recovery is not supported.
Database Backup Types

Backup Type Description

Full All data files and part of the transaction log

Transaction log Any database changes recorded in the log files

Tail-log The active portion of the log

The parts of the database that have changed since
Differential
the last full database backup
File / Filegroup Specified files or filegroups
The primary filegroup, every read/write filegroup,
Partial
and any specified read-only filegroups
The database or log (without affecting the backup
Copy-only
sequence)
Full Database Backup

Backs up entire database (+log)

Enables to restore database to the full backup
time
Use when:
Database is small
Data modified Sunday Monday Tuesday
rarely
As a basis to other backup methodologies
Transaction Log Backup

Backs up all changes since last T-Log backup

Enables to restore DB to any point in time
Restore full backup, then roll logs
Use when:
Data is modified
frequently
Frequent backups
Sunday Monday
are required
Data loss is not acceptable.
Differential Backup

Backs up pages changed since last full backup.

Use full + differential (+t-log) backups
Allows to restore a database quicker than full +
transaction log backups.
Use when:
Database is large
It is not possible to
perform full daily backup Monday Tuesday

Need for fast restore

Backup best practices

Build your backup plan according to the ability of

restore you need. Example:
Perform daily full backup to all your databases
(including system!)
Perform T-Log backup every 15 min to sensitive DB
Change Recovery Model to: “Simple” for the others
Save backups on local disk for 4 days – fast restore
If you are using Storage Based Snapshots or external
backup agent – make sure it gives you the desired
backup plan and that you know how to restore
First aid guide for full log situation
In most cases: log swells because database is in full
recovery model and there are no T-Log backups.
To solve the issue:
Take a full database backup – just in case…
Move database to simple recovery model
If log is not truncated run the command:
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY

Shrink the log file to desired size

Move database to full recovery model
Take a full backup
Build proper T-Log backup plan
Before restoring…..

Before restoring – one more backup… Tail-log

backup!
Should be taken just before a restore operation, in
most cases, under the full or bulk-logged recovery
models
captures the log records that have not yet been
backed up.
prevents work loss and keeps the log chain intact
How the Restore Process Works

Phases of the restore process:

Data copy
T-log Redo
T-log Undo
RECOVERY and NORECOVERY options:
RECOVERY brings database online – ready to use
NORECOVERY – lives database offline – ready for
additional restores
How to Restore a Database

Restore from a full or differential backup

Restores database files
Re-creates database objects and data
Use RECOVERY or NORECOVERY to control recovery
behaviour
Use MOVE…TO option to change file locations
Use REPLACE option to replace an existing database
USE master
RESTORE DATABASE AdventureWorks
FROM AWBack
WITH NORECOVERY

RESTORE DATABASE AdventureWorks

FROM AWBackDiff
WITH RECOVERY
How to Restore a Transaction Log

Restore from a transaction log backup

Restores changes to the database that are recorded
in the transaction log
Use the RECOVERY option on the final log to be
restored
RESTORE DATABASE AdventureWorks FROM AWBack
WITH NORECOVERY

RESTORE LOG AdventureWorks FROM AWBackLog

WITH FILE = 1, NORECOVERY

RESTORE LOG AdventureWorks FROM AWBackLog

WITH FILE = 2, RECOVERY
What is SQL Server Agent?

The component of SQL Server responsible

for automation
Runs as a Windows Service
Must be running to perform jobs, fire alerts, and
contact operators
Best Practices for SQL Server Agent
Configuration

SQL Server Agent service startup mode

SQL Server Agent service is not configured to start
automatically by default
SQL Server Agent service account
Requires log on as a service user right. Do not give
administrative privileges
SQL Server Agent service dependencies
SQL Server service
Messenger service for Net Send notifications
What Is a Job?

Specified series of operations performed

sequentially by SQL Server Agent
Can include Transact-SQL, command-line
application, and ActiveX script steps
Can be scheduled to run once, run repeatedly, or
be started manually
What Are Operators?

People or groups that can receive notification

Notification may be from a job, a job step, or an alert
Can be notified by using e-mail, pagers, or net
send messages
Fail-safe operator can be specified

Read more about database mail at:

http://www.sqlserver.co.il/?p=573
How to Create Job Steps

Job step 1: Back up Yes

Fail?
Database (Transact-SQL)
Write to
No Application Log
Job step 2: Transfer Data Yes
Fail?
(CmdExec)

No
Notify operator
Job step 3: Custom Yes
Fail?
Application (ActiveX Script)

No

Notify operator
What Is the Job Activity Monitor?

SQL Server Management Studio tool

Allows you to:
Start and stop jobs
View job properties
View the history for a specific job
Refresh the information in the Agent Job Activity grid
(manual or automatic)
What Is the Maintenance Plan Wizard?

Helps database administrator schedule core tasks

Creates one or more SQL Server Agent jobs
Best Practices – typical maintenance
plans for a server

Daily full backup for all databases

T-log backup every 15 minutes, for databases in
full recovery model
Both are saving backups on disk for 4 days
Weekly maintenance plan for:
Rebuild indexes
Update non-indexed columns statistics
Integrity checks
History clean-up
Moving and Copying Databases

Backup / Restore
Attaching and detaching databases
Allows directly copying data/log files
Database must be taken offline
Copy Database Wizard
Other methods:
SQL Server Integration Services (SSIS)
Generating scripts for database objects
Bulk copy / BULK INSERT
SQL Logs

Collects all events and errors occurred in the

database engine – contains precious data!
A new log is created each time SQL service restarts
Consider changing number of logs to be saved
Activity Monitor

Graphical views of current user connections and

locks
Process Info
Locks by Process
Locks by Object
SQL Server Profiler

Graphical tool for tracing server and database

activity
Create a trace that is based on a reusable template
Watch the trace results as the trace runs
Store the trace results in a table or file for
further analysis
Start, stop, pause, and
modify the trace results
as necessary
Replay the trace results
Best practices for using profiler

Use with care: when server is over loaded might

create additional pressure
Use Server side trace instead
For basic monitoring of performance Best to
collect 2 events:
Batch completed
RPC completed
Set appropriate filters and reduce\add as needed
Best Practices - Performance monitor:

Concentrate in findings queues:

Disk read\write queue (not more than 2 per spindle)
Processor Queue Length (not more than 4 per core)
Lock Wait time, Avg. Wait time
Paging (remember that it is natural for SQL Server to
capture as much memory as it needs and can. It is
paging that might be a problem)
NIC queues, errors and discarded packets
Read More at:
http://www.sqlservercentral.com/articles/Administration/perfor
mancemonitoringbasiccounters/1348/
Monitoring Disk Usage
SQL Server Maintenance best practices

Monitor real-world (production) database usage

Communicate and coordinate with application
developers and users
Develop policies and roles for database
administration
Optimize database administration
Automate common operations
Generate scripts for routine maintenance
SQL Server Maintenance best practices

On regular basis:

Monitor disk space usage
Monitor application performance
Monitor physical and logical disk space
Maintain indexes and data files
Review backup and recovery operations
Review security
Review SQL Server Logs and/or Windows logs
Verify the status of all jobs
SQL Server Security Overview

Layered Security Model:

Windows Level
SQL Server Level
Database
Schemas (for database objects)
Terminology:
Principals
Securables
Permissions
Scopes and Inheritance
SQL Server Service Accounts

Local System Account

Permissions of Local Administrative account
No network authentication
Network Service Account
Permissions of Users group
Network authentication with Computer account

Domain User Accounts

Adds network access for cross-server functionality
Best: use simple domain account
Managing Server Logins

Windows authentication Logins

Authentication/Policy managed by Windows
Safer, easier to handle
SQL authentication Logins
Authentication is managed by SQL Server
May be based on Windows policies
Logins can be mapped to Server Roles
Database Users

Server Logins are mapped to database users

Permissions on database objects are granted\
revoked from database users
Database users can be mapped to built-in\
user defined Database Roles
Built-In Server / Database Roles

Server Roles Database Roles

• SysAdmin • db_accessadmin
• ServerAdmin • db_BackupOperation
• SetupAdmin • db_DataReader
• SecurityAdmin • db_DataWriter
• ProcessAdmin • db_DDLAdmin
• DiskAdmin • db_DenyDataReader
• DBCreator • db_DenyDataWriter
• BulkAdmin • db_Owner
• db_SecurityAdmin
• public
Security Best Practices

Make security a part of your standard process

Use the principle of least privilege
Implement defense-in-depth (layered security)
Enable only required services and features
Regularly review security settings
Educate users about the importance of security
Define security roles based on business rules
Remove builtin\administrators from sysadmin
sever role
SQL Server Security Model

objects

Scheme

Database
users\roles

Server logins

Domain security groups

Domain users
SQL Server High Availability Options

Database Mirroring
Log-shipping
SQL Server Fail-Over Clusters
Geo-Cluster
Replication
Load-Balancing (at network or OS level)
SQL Server hierarchy

Server
Server
Server

Server Server
Server
Server
Server Server
What Are Schemas?

Namespaces for database objects

Person

Contact
(Server1.AdventureWorks.Person.Contact)

Sales

Customer
(Server1.AdventureWorks.Sales.Customer)

dbo

ErrorLog AdventureWorks
(Server1.AdventureWorks.dbo.ErrorLog)
Overview of Database Objects

Tables Indexes Views Programmability

• Data storage & • Improves • Logical result • Stored

Retrieval query sets Procedures
• Referential performance • Based on • Functions
integrity • Clustered SELECT queries • Triggers
• Non-clustered • Constraints
Database Objects - Tables

The tables role is to store data.

All data is stored in tables.
The tables are comprised of rows of data.
Each row contains values in columns.
The column has a defined name & type of
data stored.
Database Objects - Constraints

Constraints are used to enforce data integrity.

There are different types of constraints:
Check Constraint
Unique
foreign-key
default
Created as part of the table.
Database Objects - Views

A view can be thought of as a virtual table.

A view consists of a set of named columns and
rows of data.
A view (usually) does not exist as a stored set
of data values in a database. The rows and
columns of data come from tables referenced
in the query defining the view and
Database Objects – Stored Procedures

T-SQL queries & code stored on the server.

Accept input parameters and return multiple
values in the form of output parameters to the
calling procedure or batch.
Contain programming statements that perform
operations in the database, including calling
other procedures.
Returns an integer value to the executing code.
(May be used to indicate success/failure and the
reason for failure).
The 1-Minute* SQL Overview

The Structured Query Language (SQL) defines a standard for

interacting with relational databases
Most platforms support ANSI-SQL 92
Most platforms provide many non-ANSI-SQL additions

Most important data modification SQL statements:

SELECT: Returning rows
UPDATE: Modifying existing rows
INSERT: Creating new rows
DELETE: Removing existing rows
* Presenter makes no guarantee about the time spent on this slide
Indexing Overview

Index Considerations
Can dramatically increase query performance
Adds overhead for index maintenance
Best Practices
Base design on real-world workloads
SQL Profiler; Execution Plans
Scenarios:
Retrieving ranges of data
Retrieving specific values
Index Types

Clustered index
Controls the physical order of rows
Does not require additional disk space
One per table (may inc. multiple columns)

Non-Clustered Index
Physical data structures that facilitate data retrieval
Can have many indexes
Indexes may include many columns