Microsoft SQL Server Administration Using MS SQL Server 2005 - PowerPoint PPT Presentation

Loading...

PPT – Microsoft SQL Server Administration Using MS SQL Server 2005 PowerPoint presentation | free to download - id: 64a3a8-Yjc3N



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Microsoft SQL Server Administration Using MS SQL Server 2005

Description:

Microsoft SQL Server Administration Using MS SQL Server 2005 David Henson dhenson_at_certifiednetworks.com www.certifiednetworks.com Logistics Class Days: 3 Class Hours ... – PowerPoint PPT presentation

Number of Views:20
Avg rating:3.0/5.0
Date added: 22 August 2019
Slides: 123
Provided by: dhe87
Learn more at: http://www.certifiednetworks.com
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com