Maintaining a Microsoft SQL Server 2008 R2 Database Highlights

Posted on July 25, 2011


Day 1

  • Architecture
    • Analyze data trends
    • Availability
      • Clustering
      • Transaction Log (to Restore DB)
    • Dataside encryption (Security)
    • Scalable
      • Express (max 4gb) to Enterprise
    • Integrated Platform
      • SQL Management Studio
  • Components
    • Database Engine
    • Relational and Business Intelligence
      • Integration Services
      • Analysis Services (look at data over time)
      • Reporting Services
      • Full Text Search
      • StreamInsight (document storage, blobs)
  • SQL Server Instances (ServerName\Instance, e.g. MIA.SQL\Reporting)
    • Components instance aware
    • For isolation of configuration, versions, help with SLAs
    • Can assist with upgrade, testing
    • Two types: Default, Named
  • Editions
    • Developer(no network connections) develops to Enterprise (can dumb it down for Standard)
      • Suggestion: Install Developer on server running web application and connect locally  to develop and test.
      • Use to install vanilla version of application
    • Standard lacks bells & whistles of Enterprise
      • To Do: Check or ask which version is installed.
    • Express replaces Access
  • Connecting from Clients and Applications
    • Shared Memory (Default), TCP/IP *, named pipes
    • SQL Native Access Client (SNAC)
    • Tabular Data Stream (TDS)
    • Authentication – Windows logins, groups, SQL Server logins
    • Connect to server & database
  • SQL Server Management Studio
    • Query, configure, manage, administer
    • Graphical tools for components
    • Scripting tools
  • Books Online
    • Can install offline and online
    • All Programs > Microsoft SQL Server 2008 R2 > Documentation and Tutorials > SQL Server Books Online
    • From Management Studio
      • select SQL term, click Shift + F1
  • SQL Server Configuration Manager
    • Server Services
    • Network Config (network protocols) (MSSQL default port 1433)
    • Aliases, multiple aliases can point to same server
  • Other Tools
    • Profiler
    • Database Engine Tuning Advisor
      • pass in index file and tool recommends tables to index, and creates indexes
      • check recommendations
    • Reporting Services Configuration Manager
    • PowerShell Provider
      • PowerShell behind Management Studio
  • Preparing to install SQL Server (Module 2)
    • Architecture
      • Query Execution Layer
        • parses, resolves and optimizes queries
        • manages cache
        • execution plan (saved for reuse)
      • Storage Engine Layer
        • buffer pages 8kb, 8192 bytes each, 8060 usable, max. size for row, blobs – pointers to file system – are a work-around for page size limit
        • disk I/O
        • locking (pages), transactions
      • SQL OS Layer
        • abstraction layer for system os
    • CPU usage
      • One scheduler(thread) for each logical CPU
      • can limit processors with CPU affinity mask
      • waiting tasks put on waiting list
    • Parallelism: more than one CPU working on one query, only for expensive queries, can limit
    • 32 vs. 64
      • 4GB vs. 8TB Memory
      • MSSQL memory pig, 64 bit recommended
    • Memory
      • Buffer Pool > data cache, MSSQL components, 8K pages
    • I/O
      • Physical: requested page not available in buffer cache
      • Logical: requested page available in buffer cache e.g Stored Procedure reuses same query so query goes into logical I/O first time it is used and stays there, more economical
    • Planning Resource Requirements
      • go for 64 bit, more logical cores
      • use Profiler: can capture and replay capture to test performance
      • use Data Collection tool
      • CPU s/b %30 > %70, problem, peaks should not last
      • More memory: reduce I/O and CPU usage using data cache
      • Network: need to think about throughput, consider serveral network adapters (n-tier model easier to maintain but want 2 network adapters for each machine, 1 for each direction between webserver and db server)
      • Storage: not NAS, I Scuzzi (poor man’s SAN) needs dedicated network connection, choices: dedicated physical (raid, or raid 5, 10(mirrored stripe)) or SAN. Turn off write caching for physical disk.
      • Pre-checks of I/O – OLTP lots of transactions, OLAP lots of reads. Tool for I/O check: SQLIOSIM. In MSSQL>BIN folder

Day 2

Installing and configuring SQL Server

  • Almost any processor is fast enough, more the better
  • Disk space trivial these days, disk I/O. Example drives for physical disk: 
    • C:\ OS, MSSQL
    • D:\system db
    • E:\tempdb (your fastest drive)
    • F:\DB1, DB2 user databases -random write
    • G:\DB Log1
    • H:\DB Log2
  • Memory
    • Enterprise 2TB
    • Standard 64GB
  • Prefer 64 bit SQL on 64 bit OS
  • Avoid DC
  • Software
    • .Net Framework 2.0 on Win Server 2003, otherwise 3.5
  • File placement
    • .mdf, possible .ndf – database
    • .ldf – log
  • Service accounts
    • want to give them the minimum
    • domain accounts for network access
    • have account ready before installation and install will configure permissions
  • Collation
    • all database need to use the same locale, so we can compare sorting
    • Windows collations, based on rules of associated Windows locale
    • don’t do anything, and all dbs will use same collation
    • cannot change collation after install

Installing SQL Server


  1. Component Update
  2. System Configuration Checker
  3. Feature Selection
  4. Server Configuration
  5. Install

Once install is done, check in Configuration Manager


  • In-place, replace existing
  • Side-by-Side, 2 versions running, straightforward rollback, failover/switchover, test migration

Unattended (multiple sites): Install/upgrade from command line, MSSQL 2008 uses command line switches

Working with Databases (Module 4)

Ensure you have enough disk space, probably want to estimate the size you’ll need:

how many bytes per row? e.g. int 4b, char(25) 25b, datetime 4b, currency 4b

how many rows per page?

how many pages needed? = total space needed in kb

want to avoid autogrowth options, because db has to stop, create & reallocate new space > plan for manual expansion to stay away from expansion procedures

Log Files:

OLTP – 25% db size

OLAP – 10% db size

System Databases:

  • master: system config
  • msdb: SQL Server Agent configuration (jobs, alerts)
  • model: template for new databases
  • tembdb: created with restart
  • resource: hidden, read only service pack updates

Files & Workgroups

Create db

  • GUI
  • Collation can be specified
  • Can configure database-level options  in “Database Properties”
    • autogrowth options, should restrict max size if there’ s autogrowth
    • autoclose default: false
    • autoshrink default: false, because if you archive data, database will grow again
    • page verify default: checksum
    • recovery model: how does log file get used?
      • simple: truncate log on checkpoint – nothing in log file, for testing and development
      • bulk-logged: no truncate on checkpoint, stuff left in transaction log, but just one record in log for a bulk load, speeds bulk load operations
      • full: everything logged (probably want to switch back to bulk-logged for bulk loads)
    • state
      • multiuser: lots of users
      • single user: only one user, e.g. when developer makes changes
      • restricted user: only dba, e.g. when dba needs to restore db
  • Altering Database
    • Add, drop, expand, shrink (only if necessary, can fragment db) data files
      • SSMS
      • T-SQL
  • Filegroups
    • Primary Filegroup
    • Other Filegroups, e.g. for archives, for i/o access give the data’s file it’s own drive if it will be queried a lot
  • Moving Database Files
    • default path = install path
    • detach and attach, removes all metadata for db and adds it in new server, for moving from server to server
      • good for db recover
      • right click DB > Tasks > Detach…
    • or can move database files if staying on same server using ALTER DATABASE, same instance on same server
    • db needs to be offline
    • Copy Databases wizard, unlike SSIS it can copy more than data

Recovery Model (Module 5)

  • Full – starting point
  • Differential – everything that’s changed since full
  • Incremental (MSSQL : Transaction) – everything that’s changed since last incremental
  • File/Filegroup, need to make sure files have whole db
  • The can be combined
  • Copy, doesn’t mark transaction log so doesn’t mess up backup schedule
  • Before Restore: Tail Log, includes what hasn’t been backed up since last incremental
  • If CPU pinned: sqlcmd -A uses dedicated Admin Connection to server, must be dba, only 2 connections, priority queue login then run sp_who, kill spid, then can access server run Tail Log backup
  • Snapshot database

Transactions logs: Make transaction durable. If power goes out MSSQL can complete transaction (A – atomic, C – consitent, I – isolated (lock), D-durable)

full backup truncates transaction log

Backing up Databases and Transaction Logs


INIT appends to backup file specified

NO INIT overwrites the file

Options for Ensuring Backup Integrity

  • Mirrored Media Sets
  • CHECKSUM backukp option
  • Backup verification

RESTORE LABEL ONLY: info about backup media

RESTORE HEADER ONLY: when was the backup done and what type

Or… Tasks > Back Up > Select the filed and click “Contents”

Backups are performed online, does not prevent use

Day 3

Restoring Databases (Module 7)

Restore Types

  • Complete Restore in Simple recovery model
  • Complete Restore in Full recovery model
  • System Restore
  • Restore damaged files
  • Advanced (Enterprise only) Online restore, Piecemeal (File Groups), Page

Recovery Process

  1. Perform tail-log backup (full and bulk-logged recovery model only)
  2. Identify your backup files (Restore Database has list of backups performed)
  3. Recover
    1. Full
    2. differential
    3. log files until point of failure (restored in order of backup datetime) WITH NORECOVERY to this point (default is WITH RECOVERY!, so must explicity state WITH NORECOVERY)
    4. tail-log – you’re done – WITH RECOVERY
    5. database online

Phases of Restore Process

  1. Data Copy – creates files and copies data to files
  2. Redo – applies comitted transactions from logs
  3. Undo – roll back transactions that were uncomitted

WITH STANDBY option – allows users to query unrecovered database, for fault tolerance

STOPAT (time), STOPAT MARK/STOPBEFORE MARK (transaction log mark) option – allows you to stop restore before problem occured in db (SSMS or T-SQL)

STOPAT may not work for Synchronizing Recovery of Multiple Databases, leaving unresolved transactions between databases

For point-in-time Recovery – must be FULL recovery model

Recovering System Databases

  • master: Simple
    • if cannot start db, start in single user mode from command line
    • use RESTORE DATABASE with sqlcmd
    • start db normally
  • model: user defined
  • msdb: Simple
  • tempdb/resource: none needed

Recover File or FileGroup

  1. tail-log
  2. restore damaged file or file-group
  3. restore differential
  4. restore transaction logs
  5. recover database

Transfering Data To/From SQL Server (Module 8)

ETL – Extract Transform Load


  • Bulk Copy Program (bcp): transfering from flat files or xml
  • BULK INSERT: like bcp but t-sql, not command line, can rollback because part of transaction
  • OPENROWSET(BULK): db server to db server – can connect to remote datasource, using OLE-DB provider
  • Import/Export Wizard: allows basic transformations (creates SSIS package project that can be opened in VS and developed further)
  • XML Bulk Load: XML

Improving Performance

  • disable contraints, indexes, triggers
    • clustered and non-clusterd indexes, if a clustered index is disabled then prevents access to data
    • enable index rebuilds index
    • disabling primary key and unique key achieved by disabling associated index
    • foreign key and check constraints – disable constraint directly
    • db does not validate foreign constraints after data loaded and constraint enabled unless use WITH CHECK
  • minimize locking (TABLOCK)
  • minimize logging (BULK_LOGGED or SIMPLE model)
  • minimize conversions

SQL Server Integration Services (SSIS)

To open package in Business Development Studio, create project, add in .dtsx file, and open the file.

Autheniticating and Authorizion – Module 9

Authentication Modes of SQL Server. Two ways to authenticate who users is:

  • Windows Authentication
  • Mixed Mode Authentication (Windows and SQL Server Logins)

Who can authenticate at server level: Security > Logins

 At database level: Security > Users


Can authorized by:

  • assignment permissions to a securable
  • assign a principle to a role
  • DCL (data control language) GRANT, DENY, REVOKE
    • implicit deny (by default only object creator has any access) REVOKE returns to implicit deny
    • explicit deny DENY
    • explicit allow GRANT

Deflaut logins: 

  • dbo: sa login and sysadmin mapped to dbo account, for db admin
  • guest: limited access and disabled by default

Authorization across servers

“Double-Hop” problem

  1. Login into webserver, application process impersonates the user
  2. On daabase server delegation is not permitted, identity of service account used instead of identity of user
  3. database access not allowed to service account

Impersonation: ability to impersonate user on local machine

Delegation: ability to impersonate user across the network (must be activated for server)

Assigning Server and Database Roles

Server-scoped permissions:

  • Minimize use of fixed server roles
    • sysadmin
    • dbcreator
    • diskadmin
  • Assign more specific permissions
    • alter database
    • backup database
    • connect
    • view any definition
    • alter trace
    • backup log
    • create database
    • view server state

Database scoped permissions

Application Roles

  1. user runs app
  2. app connects to db as user
  3. app authenticates using sp_setapprole
  4. app assumes app role
  5. user loses all own permissions on db 

Securables: resources that can be secured, contained within scopes – server (server login, endpoint, database), database, schema

permissions that apply to tables and views

  • select
  • insert, update, delete
  • references – principal can select on all tables related through foreign keys to table with granted select

column level security – column level GRANT overrides table level DENY

WITH GRANT OPTION, principal granted access can grant to others

CASCADE with DENY takes away all access to all principal granted access to

Authorizing Users to Execute Code

Stored Procedures, Functions require EXECUTE to call

Managed Code, additional permission required. CLR assemblies registered with:

  • SAFE (default) Can only interact with SQL Server
  • EXTERNAL_ACCESS Can interact with registry and file system
  • UNSAFE network as well, only admins can create

Ownership Chains: When owner owns both objects, say sp and table, no problem. If sp and table have different owner and other user tries to use sp > broken ownership chain

This problem fixed in MSSQL 2005 when the owner concept was replaced by schemas, like Oracle.

Grant permissions at schema level

Day 4

Auditing Data Access


  • C2 Audit Mode
    • US Trusted Computer Systems Evaluation Criteria (TCSEC)
    • Superseded by Common Criteria
    • Now ISO standard 15408
    • Configured via ‘c2 audit mode” under Server Properties > Security or sp_configure in Enterprise and Data Center
  • Triggers
    • DML – insert, update, delete
    • DDL – Logon triggers – create, alter, drop 
    • Limitations
      • performance impact
      • ability to disable triggers
      • no support for SELECT
      • trigger nesting issues
      • recursive trigger issues (by default disabled)
      • complexities around trigger firing order, cannot control order when there are multiple triggers
    • SQL Trace
      • SQL Server Profiler, can be heavy on resources, but can trace command executions
      • SQL Trace
  • SQL Server Audit (new for MSSQL 2008 Enterprise and Datacenter editions)
    • Security > Audits, Security > Server Audit Specifications
    • light-weight eventing engine for servers
    • based on Extended Events
    • Comprised of:
      • Audits
      • Server Database Audit Specificatinos
      • Actions and action groups e.g. logon, insert, update
      • Targets e.g. schemas, objects
      • can write to event viewer or get parsing tool
  • Audit-related DMVs and system views for managing SQL Server Audit
  • View using sys.fn_get_audit_file
  • Issues
    • moving database between servers
      • orphaned audit specifications similar to orphaned SIDs



  • Reduced admin workload by automating admin tasks
    • e.g. rebuild index on 30% fragmentation
    • backup procedures, database consistency checks
  • Proactive management
    • moniter performance
    • look for potential issues

Achieved by:

  • jobs to perform regular tasks
  • alerts to respond to events, performance measures
  • operators to send notifications from jobs and alerts to users

SQL Server Agent

  • runs as Windows service
  • to enable: Configuration Manager > SQL Server Agent > set startup type to automatic
  • Job types
    • t-sql
    • command line app
    • powershell script
  • scheduled to
    • run once or repeatedly
    • start at sql server agent startup
    • start manually
  • jobs can be assigned to category
  • job schedules can be re-used for other jobs
  • jobs can be scripted for documenting, archiving
  • job history kept in msdb.sysjobhistory, jobs in msdb.sysjob
  • troubleshooting
    • check settings of the service
    • check msdb db
    • review job history
    • is job enabled, scheduled
    • is schedule enabled
    • are all dependent objects available

SQL Server Agent Security

can use either local accounts (Local or Network Service) or Windows domain account, if agent needs access to network

by default SQL Server Agent has almost admin level of security

Can configure credentials and assign them to different proxy accounts, run SQL Server agent under those proxy accounts with the appropriate credentials:

  1. setup credentials (user account)
    1. listed in sys.credentials system view
  2. in proxy account, select the credentials to use
  3. proxy accounts created in SSMS or sp_add_proxy

in Services, can configure fault tolerance for SQL Server Agent

Security context of job is the “owner”

can create “output file” to troubleshoot

Alerts and Notifications

Configuration of Database Mail required

  • standard SMTP protocol
  • profiles
    • Private – specific users and roles
    • Public – anyone
  • runs under SQL Server Engine service account
  • relies on stored procedures disabled by default
  • must be member of DatabaseMailUserRole to send mail
  • mail logs in msdb database, will want to purge

Monitoring SQL Server Errors

  • errors raised by db engine have number, message, severity, state, procedure name, line number – sys.messages
  • severe errors written to Application and SQL Server log

Configuring Operators, Alerts and Notifications

Operators can be notified using Email, Pager, Net Send (avoid)

Only one operator for each job, so put use group as operator so everyone in group gets message. Or Fail-safe operator can be configured (only one for server)

Alerts triggered by:

  • Application log event
  • Performance conditions
  • WMI events

They can:

  • Notify an operator
  • Start a job


  • Ensure SQL Server Agent is running
  • Ensure error message written to Application Log
  • Ensure that alert is enabled
  • Check that alert was raised
  • If alert raised but no action
    • check job
    • check mail profile

Ongoing database maintenance

Database Integrity Checks


  • Verify logical and physical integrity of database
    • allocation of pages
    • consistency of tables and indexes
    • consitency of database catalogue
    • link level consistence of FILESTREAM objects (for blobs – within database)
    • Service Broker objects
  • Offers repair options
    • db needs to be in SINGLE_USER mode
    • consider restore if cannot repair without data loss
  • Runs online using internal database snapshot
  • Do frequently, before backup


  • Performance
    • without index mssql reads all table pages
    • index is balanced tree (b-tree), max 32 searches for list of 42 billion items
    • index must be updated when index installed
    • 2 types
      • clustered – affects sort ordered, points to data, one per table
      • non-clustered – uses pointer for data, one more lookup at bottom of tree
  • fragmentation
    • mssql reorganizes index pages when DML occurs, causes file fragmentation
    • find fragmentation with sys.db_index_physical_stats
  • FILLFACTOR (page fullnes at leaf level, e.g. 70% pages can take more data without new page, for performance) and PADINDEX
  • REBUILD new index, needs free space
  • REORGANIZE always online, sorts pages, can be interupted
  • >30% fragmentation, rebuild, <30% fragmentation, reorganize


  • SQL Server Database Maintenance Plans… Management > Maintenance Plans > Maintenance Plan wizard

Tracing Access to MSSQL

SQL Server Profiler

Capture tool using SQL Trace programming interface used for debugging, performance, deadlock (2 users each have locks stopping other user, deadlocks both, MSSQL checks and kills deadlocks) monitoring

recommend saving trace to file for better performance, avoid select too many columns

trace templates are pre-defined

Database Engine Tuning Advisor

used to suggest index and statistics changes

analyze indexes, table structure, recommend and tune db

performance tuning: start at top level, server level (Reliability and Performance Monitor), and drill down from there, database, schems, procedures, queries, etc.

Tracing Options

lightweight – based on system stored procedures

long term monitoring or large trace

can replay traces, for stress testing, e.g. on a new version of MSSQL

can overlay Profiler on Performace Monitor

  1. add performance counters to perfmon, save, start
  2. new trace in Profiler, start
  3. execute queries, etc.
  4. stop profiler
  5. stop perfmon
  6. close profiler
  7. import perfmon data into profiler, click in graph, see what query was running

Day 5

Monitoring SQL Server

Dynamic Management Views and Functions – Tell you what is going on right now.

Find under system views: sys.dm_%

sys.dm_exec _requests: running process, how long they’ve been running for, etc. id <50 means system process, id > 50 user process

sys.dm_exec_sql_text: shows sql command

sys.dm_os_wait_stats: where queries have been slowed down, wait times, locks, etc.

Activity Monitor in SQL Server

Like Task Manager for SQL Server


  • Graph of Processor Time, Wait Time, Database I/O
  • Processes
    • Can set up trace, or teminate
  • Wait time
  • Top transactions
  • Memory consumption

Windows Reliability and Perfomance Monitor

Resource Monitor: Real time monitoring of server performance counters, beefed up version of Performance Monitor, CPU, Disk, Network, Memory counters, graphs ready

Performance Monitor: Can add counters for SQL Server – Object, Counter, Instance

Data Collector (available with Standard edition)

  • Lower overhead data collection
  • Persistence f diagnostics data
  • Data retention
  • Rich reporting
  • Easy extensibility
  • Central repository for server SQL Server instances

Data Collector > Data Repository > Reporting

How: SSMS > Database > Management > Data Collection > Wizard – 1. Specify data warehouse 2. Specify data to collect

Analyzing Data Collector Reports

Includes graphical data and trend data, saves for 730 days

e,g. server activity, query statistics history, trends

Managing Multiple Servers

Central Management Servers

One query can be run against multiple servers, and results can be merged

Policies can be created on one server and propagated to other servers

Browse object explorer

To Do:

  • Define CMS server
  • Can create server groups if many servers, or…
  • Register servers

Virtualizing SQL Server

Root Partition Windows Server    Partition 1 – MSSQL    Partition 2 – MSSQL

                                      Hyper-V Hypervisor (runs independent of OS)



  • Server consolidation
  • Create supportable running older versions
  • Provide hardware independence
  • Create failover clusters and use Live Migration (better failover tool)
  • Provide dynamic CPU, memory and network allocation to meet SLA
  • Easy build and run test environments

Data-Tier Applications (only works within SQL Server)

for moving applications, or for instance a schema used for interfaces, to another server

n-tier app is considered a unit of deployment for T-SQL applications, installing tables, views, users, requirements, policies, etc.

Can create wizard based process to deploy application (create .dacpac file)

Can reverse engineer database created by an application


  1. Investigate
    1. Clearly defined the issue as perceived by user
    2. What works? What doesn’t work?
    3. Did it ever work? When did it last work? What else changed?
    4. How would you know if it is resolved?
  2. Analyze
    1. Brainstorm potential causes
    2. What potential causes are likely? How can they be tested for and eliminated?
  3. Implement
    1. Eliminate potential causes in descending order of likelyhood
  4. Validate
    1. Ensure that the issue is really solved.

Service-related issues

  • Check Windows and SQL error logs
    • check Log folder in MSSQL application folder
  • Check if you can log on locally
  • connect via sqlcmd /a
  • sql service will not start
    • start in single user  mode
    • check that tempdb path is accessable
    • start from cmd prompt
  • System Log
  • Application Log

Concurrency Issues

Two lock types:

  • Shared – Read – Select, allows others to read, but not write
  • Exclusive – Write – Insert, Update, Delete
    • prevents dirty read: read of an uncommitted transaction

Locks prevent update conflicts:

  • ensures transactions are serialized
  • locking is automatic
  • enables safe concurrent access to data

Locks can lead to Blocking

Processes are blocked waiting for locked resources – only a problem when wait duration is lengthy.

Want to monitor for long lasting block scenarios, anything longer than 15 seconds.


User A locks out User B to a resource User B wants, but at the same time User B is locking out User A to a resource that User A wants, so neither can complete their transaction.

SQL Server detects deadlock and returns 1205 error.

Solution: Rewrite query so users try to access data in the same way.

Cannot connect to SQL Server

Try access using Shared Memory (local)

Then look at network issues, ip address, domain controller available, is SQL Server in mixed mode.



Posted in: Technology