Microsoft SQL Server Administration Using MS SQL Server 2005

1
Microsoft SQL Server Administration Using MS SQL
Server 2005

• David Henson
• dhenson_at_certifiednetworks.com
• www.certifiednetworks.com

2
Logistics

• Class Days 3
• Class Hours 9-5
• Notes/Handouts
• Demos
• Class website

3
Recommended Reading

• Microsoft SQL Server(TM) 2005 Administrator's
  Pocket Consultant
• (Pro-Administrator's Pocket Consultant)
• Microsoft Press

4
Module 1 Overview

• Course Outline
• Lab Setup
• Definitions
• Tools
• What is SQL Server
• SQL Language Basics
• SQL Server Network Integration

5
Course Outline Day 1

• Module 1 Overview
• Module 2 Installation
• Module 3 Objects and Securables
• Module 4 Security And Principals

6
Course Outline Day 2

• Module 5 Backup
• Module 6 Restore

7
Course Outline Day 3

• Module 7 Web and Email Integration
• Module 8 Automation and Job Scheduling
• Module 9 Transferring Data
• Module 10 Replication

8
Lab Setup

• Windows 2003 Server
• SQL Server 2005
• Standard, or Enterprise
• Computer Names
• IP Addresses

9
Definitions

• Transact SQL
• DBA
• Server
• Client
• Concurrency/Locks
• Login
• User
• Role
• Database

• SQL SMO(Formerly DMO)
• SQL Instance
• Principal
• Securable

10
Tools

• Query Analyzer SQL 2000
• SQL Management Studio
• Visual Studio .Net 2005
• SQL Server Configuration Manager
• Command Prompt
• SQLCMD
• Ping, telnet, nslookup, etc.
• Database Tuning Advisor
• Full-FeaturedText Editor
• UltraEdit
• Textpad
• Profiler

11
What is SQL Server?

• Client Server Relational Database Operating
  System
• Windows Service That Manages Database Files,
  Security and Concurrency

12
SQL Language Basics

• SELECT
• INSERT
• UPDATE
• DELETE

13
SQL Server Network Integration

• Network oriented protocols
• TCP/IP disabled on SQL Express by default
• DNS
• Windows Domain/Active Directory
• Firewalls

14
Lab 1A Tools

• In this instructor led lab, you will
• Preview SQL Management Studio
• Preview Database Engine Tuning Advisor
• Preview Profiler

15
Module 2 Installation

• Software Requirements
• Hardware Requirements
• SQL Server Versions
• Licensing
• Capacity Planning
• SQL Services
• Installation

16
Software Requirements

• Windows 2000 or 2003
• Windows XP (Developer)
• Windows CE

17
Hardware Requirements

• Install Type Dependent
• Express
• 192 MB RAM
• Standard/Enterprise
• 512MB RAM
• 450 MB Typical Install

18
SQL Server Versions

• Enterprise
• Standard
• Developer
• Workgroup
• Express
• Windows Mobile

19
Licensing

• Per Processor
• Server plus Device CALS
• Server plus User CALS

20
Capacity Planning

• Drive Space
• CPU
• Memory
• I/O Subsystem

21
I/O Subsystem

• RAID 0, 1, 5 or 10
• Logical Drive Structure
• Physical Drive Structure
• Considerations
• Performance
• Ease and Cost of Management
• Disaster Planning

22
SQL Services

• Service Names
• SQL Server (MSSQLServer)
• SQL Server Agent (MSSQLServer)
• SQL Server Browser
• SQL Traffic director, allows Dedicated Admin
  Connection (DAC)
• SQL Server Integration Services
• SQLIS.com
• SQL Server Fulltext Search
• SQL Server Analysis Services
• SQL Server Reporting Services
• Starting/Stopping

23
Service Accounts

• LocalSystem
• Local Account
• Domain Account
• Automatic Startup

24
Installation

• Options
• Server
• Instance
• Client Tools Only
• Administrative Rights
• Instances

25
Online Demonstration Installing an SQL Server
instance
26
Module 3 Managing Objects

• Object Definition
• Creating Objects
• Altering Objects
• Dropping Objects
• MetaData
• Object Permissions

27
What is an Object?

• Type Of Objects
• Table
• View
• Stored Procedure
• Trigger
• DataType
• Function (UDF)
• Index
• Constraint

28
Synonyms

• Allows an alias for a securable
• New CREATE SYNONYM syntax

29
Creating Objects

• CREATE Statement
• Example
• CREATE TABLE Customers(
• CustomerID int identity,
• CustomerEmail varchar(50)
• )

30
Altering Objects

• ALTER Statement
• Example
• ALTER TABLE Customers
• ADD CustomerPhone varchar(40)

31
Dropping Objects

• DROP Statement
• Example
• DROP TABLE Customers

32
Getting Metadata

• Information Schema
• Sysobjects table
• Sp_helptext

33
Setting Permissions

• GRANT Statement
• Example
• GRANT SELECT
• ON Customers
• TO Public

34
Lab 3A Creating Objects
35
Lab 3B Altering Objects
36
Module 4 Security and Principals
37
Surface Area Configuration

• Best practice reduce the surface area
  exposure of your system to minimum
• Off By Default
• Microsoft .NET Framework,
• SQL Service Broker Network Connectivity, and
• HTTP connectivity in Analysis Services
• Xp_cmdshell
• Ad Hoc Remote Query
• Database Mail
• Native XML Web Service
• See Surface Area Configuration Tool

38
Levels of Security

• Login Server Level
• User Database Level
• Object Permissions to Access Objects
• Application Security
• Network Access
• Encryption

39
Windows Workgroup Model

• Distributed Accounts
• Each machine has locally maintained user and
  group database
• Windows Server Not Required

40
Windows Domain Model

• Centralized Accounts
• Active Directory is a single source for users and
  groups
• Single Sign-On
• Access all resources after logging in only once
• Auditing

41
Active Directory

• Extension of the Domain Model
• Leverages DNS Name Resolution
• Better Security Protocols
• Better Fault Tolerance
• More Extensible
• Group Policy

42
Logins

• Types
• Windows (Integrated)
• SQL (Standard)
• Tools
• SQL Management Studio
• Scripts

43
Users

• Tools
• SQL Management Studio
• Scripts

44
Roles

• Role Is A Group of users
• Purpose Ease Permission Management
• Role Types
• Standard
• Application Role
• Tools
• SQL Management Studio
• Scripts

45
Permissions

• User Defined
• Action
• Object Access
• Object Execution
• Tools
• SQL Management Studio
• Scripts

46
Lab 4A Creating Accounts
47
Lab 4B Creating Users and Groups
48
Lab 4C Permissions
49
Module 5 Backup
50
Reasons for Data Loss

• Accidental Update
• Hardware Failure
• Improper Application Design
• Transactions Not Used
• Other User Error
• Intentional Distruction
• Hackers
• Viruses

51
SQL Server Backup

• Online Backup
• Users can access data during backup
• Types
• Full
• Differential
• Log
• COPY_ONLY option
• Does not disturb normal backups, truncate logs,
  or reset any flags
• Use of Filegroups

52
Increasing Backup Performance

• Set appropriate BLOCKSIZE parameter
• -2048 good for cdrom stored backups
• With default setting, restore uses buffered io
  which is more flexible and slower
• Stripe backup devices

53
Restricted Activities During Backup

• Modifying Database Properties
• Autogrowth
• Managing Indexes
• Nonlogged Operations

54
Backup Statement

• Example
• BACKUP DATABASE Lab3A
• To Diskc\Lab3A.bak
• WITH INIT, BLOCKSIZE2048

55
Using SQL Management Studio
56
Backup Options
57
Database Recovery Model

• Property of a database
• Options
• Full
• Bulk_Logged
• Simple
• Affects log behavior

58
Backup Requirements

• System Databases
• Master New Logins, change password
• MSDB Modifying or creating jobs
• User Databases
• Whenever data loss is unacceptable
• At a rate when the cost(cash, downtime) of
  restore is unacceptable

59
Operating A Standby Server

• Server 1-Log Backup Regularly
• Server 2-Log Restore with NoRecovery
• Server 2 Database is read-only
• Server 2-Log Restore with Recovery
• Used in case of catastrophic failure of Server1

60
Backup Devices

• Device File
• Permanent object you can reference by name
• Example
• BACKUP DATABASE Lab3A
• TO DailyBackup

61
Tape Backup

• Must be attached locally to the SQL Server
• Some syntax is unique to tape media
• Must be recognized by Windows Backup as a SCSI
  tape device

62
Special Log Backups

• WITH Truncate_Only
• Clears log, no backup file is created
• Used to maintain log when no regular log backups
  occur
• WITH No_Truncate
• Emergency use when data file not available
• WITH COPY_ONLY (new to SQL 2005)
• Does not disturb normal backup flow or interrupt
  normal log backup/restore process
• Use on an ad-hoc basis before running potentially
  harmful queries

63
Backup Strategies

• Weekly Full Backup
• Nightly Full Backup
• Variations of above with Differential and Log
  Backups

64
Best Practices

• Use Automation
• Test Your Backups
• Use Notification
• Use COPY_ONLY option as needed
• Understand the Business
• Keep Management Informed

65
Lab 5A Backup
66
Module 6 Restore
67
Restore Safety Features

• Restore will fail(or need override) if
• Backup file stored name does not match the
  database name
• Set of database files do not match
• All necessary files are not available

68
Restore Database Statement

• RESTORE DATABASE Lab6
• FROM DISKc\Lab6.bak
• WITH RECOVERY

69
Restore Process

• Always backup the log first!
• Full
• Differential
• Log
• Recovery

70
Saving Space

• Maintain the log file
• Compress your backup files

71
Log Restore Options

• Stopping at a certain time
• Stopping at a named bookmark

72
Single Row Restore

• Use in case of accidental delete
• Restore to a temporary location
• Enable identity inserts
• Insert necessary rows
• Intimate knowledge of data structure required

73
Restoring Master

• Place server into single user mode
• Perform Restore
• Restart Server

74
Restoring Master with No Backup

• Rebuilm.exe utility creates brand new master,
  model, msdb
• Sp_attach_db plugs data files back in

75
Best Practices

• Backup the log at first sign of trouble
• Periodically test a restore
• Maintain enough working space
• Avoid the need to restore
• Backup system databases as required

76
Lab 6A Restore
77
Module 7 Web and Email Integration
78
Web Technologies

• Allow connection to SQL Server through HTTP
• Used for logging, collecting data, reporting,
  data transfer and admin functions
• Examples
• ASP, ASP.Net, Perl, CGI, Cold Fusion, JSP, Java

79
Communication Process
80
Benefits of web integration

• Secure worldwide access
• No client deployment
• Remote access for administrative functions

81
Dangers of web access

• Misuse of data
• Data communication interception
• Hackers have a doorway to your network

82
SQL Injection

• Malicious users leverage web input against SQL
  Server
• Examples
• Running server level commands
• Changing the where clauseOR 11
• Running DDL Commands
• Reading ODBC Errors for data discovery

83
Protection from SQL injection

• Only use minimal priviledged account
• Pre-process all input
• Minimize direct SQL from web page by using Stored
  Procs and UDFs

84
ASP Example
85
ASP.Net Example
86
Email Notification

• Allows asynchronous communication with SQL Server
• Numerous techniques
• Database Mail
• New to SQL 2005
• Easy to use smtp mail provider, easy setup
• CDONTS.Newmail
• Provided as a .dll file with Windows 2000
• Com Object
• CDO.Message
• Provide with XP/Windows 2003
• Com Object
• SQLMail
• MAPI Client
• Must be setup during a full moon
• Third party objects
• .NET Framework mail objects

87
CDONTS Object
88
CDO Object
89
Database Mail
90
SQL Mail Setup

• Not recommended
• Requirements
• MAPI compliant mail server
• MAPI client on SQL Server
• SQL Service logged in as domain account
• Domain account has mailbox available

91
Lab 7A Web/Email Integration
92
Module 8 Automation and Job Scheduling
93
Requirements

• SQLServerAgent service must be running
• Permissions on network or local resources

94
Automation Examples

• Nightly backup
• Nightly data transfer
• Hourly generation of web pages
• Detection of server problems
• Log 95 full
• Backup Log
• Email Notification

95
Automation Components

• Job
• Collection of steps, each step performing work
• Operator
• Pointer to email account in Database Mail or SQL
  Mail
• Alerts
• Connection between a detected problem and a
  job/operator

96
Jobs

• Created with gui or with sp_add_job
• May be scheduled
• Comprised of Steps
• Transact SQL
• Operating System Commands
• ActiveX (VBScript/Javascript)
• Other (Replication, SSIS, Analysis Services)
• Retain history
• Might have success/failure workflow
• Can target multiple servers

97
Defining a Job Step
98
Operators

• An alias to a native email address of SQL Mail or
  Database Mail
• Net Send address also supported
• Operator Addresses
• Email business hours email address
• Pager after hours email address
• Net send
• Failsafe Operator

99
Defining an Operator
100
Alerts

• Alert types
• SQL Server Error
• SQL Server Performance Condition
• WMI Query
• Windows eventlog MUST trigger response
• Alert response is defined by admins
• Execute Job
• Send Notification

101
Defining An Alert
102
Proxies

• Allow job steps to run with a different windows
  login

103
Lab 8A Automation and Job Scheduling
104
Module 9 Data Transfer
105
Data Transfer

• Movement of data between systems
• One of the most common DBA functions

106
Data Transfer Examples

• Nightly download of sales data from all stores
• Quarterly catalog update for website
• Order fulfillment
• Hourly transfer of order information to
  fulfillment center
• Hourly gathering of ship tracking information
• Movement of web data into accounting system

107
Data Transfer Techniques

• SSIS and Import-Export Wizard
• Legacy DTS Package Support
• BCP.EXE command line utility
• Backup/Restore
• Distributed Queries
• Saving results from query analyzer
• Web Page download
• Bulk Insert Statement
• Detach/Re-attach database files
• Log Shipping
• Select/Insert

108
SSIS

• Import/Export Wizard
• Visual Studio Project

109
BCP

• Command line utility
• Examples
• Bcp northwind..products out c\nwind.csv w t,
  -r/n T
• Bcp select from products queryout
  c\nwind.txt c t -r/n Usa -P

110
Backup/Restore

• Simply compress, then transfer the backup file to
  the destination
• Clean up sysusers table after restore

111
Distributed queries

• Openquery/Openrowset/OpenXML
• SELECT INTO
• INSERT/SELECT
• Linked Servers

112
Query Analyzer

• Controlling saved results
• Output to text file
• Reading from different providers

113
Web page download

• Response Content type change
• Saving results to a file, then providing a link
• Job scheduler, results come through email

114
Bulk Insert
115
Detach/Reattach database files

• On database node
• Right click/all tasks/detach
• Clean up of sysusers not needed after
  re-attachment

116
Lab 9A Data Transfer
117
Module 10 Replication
118
Definitions

• Automatic transfer of data between locations

119
Replication Types

• Transactional
• Only changes are copied across
• Most efficient
• Merge
• Two sources of read/write data
• Most dangerous
• Snapshot
• Data is removed, then re-created on a schedule

120
Replication Models

• Central publisher/Distributer
• Central Subscriber/Multiple Publisher
• Multiple Publisher/Subscriber

121
Setup Tips

• Ensure box servers are logged in with a domain
  account
• Ensure the domain account is an admin on both
  boxes
• Ensure the domain account can map to c
• Ensure the domain account can run queries against
  the other server

122
Lab 13A Replication