Title: Microsoft SQL Server Administration Using MS SQL Server 2005
1Microsoft SQL Server Administration Using MS SQL
Server 2005
- David Henson
- dhenson_at_certifiednetworks.com
- www.certifiednetworks.com
2Logistics
- Class Days 3
- Class Hours 9-5
- Notes/Handouts
- Demos
- Class website
3Recommended Reading
- Microsoft SQL Server(TM) 2005 Administrator's
Pocket Consultant - (Pro-Administrator's Pocket Consultant)
- Microsoft Press
4Module 1 Overview
- Course Outline
- Lab Setup
- Definitions
- Tools
- What is SQL Server
- SQL Language Basics
- SQL Server Network Integration
5Course Outline Day 1
- Module 1 Overview
- Module 2 Installation
- Module 3 Objects and Securables
- Module 4 Security And Principals
6Course Outline Day 2
- Module 5 Backup
- Module 6 Restore
7Course Outline Day 3
- Module 7 Web and Email Integration
- Module 8 Automation and Job Scheduling
- Module 9 Transferring Data
- Module 10 Replication
8Lab Setup
- Windows 2003 Server
- SQL Server 2005
- Standard, or Enterprise
- Computer Names
- IP Addresses
9Definitions
- Transact SQL
- DBA
- Server
- Client
- Concurrency/Locks
- Login
- User
- Role
- Database
- SQL SMO(Formerly DMO)
- SQL Instance
- Principal
- Securable
10Tools
- 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
11What is SQL Server?
- Client Server Relational Database Operating
System - Windows Service That Manages Database Files,
Security and Concurrency
12SQL Language Basics
- SELECT
- INSERT
- UPDATE
- DELETE
13SQL Server Network Integration
- Network oriented protocols
- TCP/IP disabled on SQL Express by default
- DNS
- Windows Domain/Active Directory
- Firewalls
14Lab 1A Tools
- In this instructor led lab, you will
- Preview SQL Management Studio
- Preview Database Engine Tuning Advisor
- Preview Profiler
15Module 2 Installation
- Software Requirements
- Hardware Requirements
- SQL Server Versions
- Licensing
- Capacity Planning
- SQL Services
- Installation
16Software Requirements
- Windows 2000 or 2003
- Windows XP (Developer)
- Windows CE
17Hardware Requirements
- Install Type Dependent
- Express
- 192 MB RAM
- Standard/Enterprise
- 512MB RAM
- 450 MB Typical Install
18SQL Server Versions
- Enterprise
- Standard
- Developer
- Workgroup
- Express
- Windows Mobile
19Licensing
- Per Processor
- Server plus Device CALS
- Server plus User CALS
20Capacity Planning
- Drive Space
- CPU
- Memory
- I/O Subsystem
21I/O Subsystem
- RAID 0, 1, 5 or 10
- Logical Drive Structure
- Physical Drive Structure
- Considerations
- Performance
- Ease and Cost of Management
- Disaster Planning
22SQL 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
23Service Accounts
- LocalSystem
- Local Account
- Domain Account
- Automatic Startup
24Installation
- Options
- Server
- Instance
- Client Tools Only
- Administrative Rights
- Instances
25Online Demonstration Installing an SQL Server
instance
26Module 3 Managing Objects
- Object Definition
- Creating Objects
- Altering Objects
- Dropping Objects
- MetaData
- Object Permissions
27What is an Object?
- Type Of Objects
- Table
- View
- Stored Procedure
- Trigger
- DataType
- Function (UDF)
- Index
- Constraint
28Synonyms
- Allows an alias for a securable
- New CREATE SYNONYM syntax
29Creating Objects
- CREATE Statement
- Example
- CREATE TABLE Customers(
- CustomerID int identity,
- CustomerEmail varchar(50)
- )
30Altering Objects
- ALTER Statement
- Example
- ALTER TABLE Customers
- ADD CustomerPhone varchar(40)
31Dropping Objects
- DROP Statement
- Example
- DROP TABLE Customers
32Getting Metadata
- Information Schema
- Sysobjects table
- Sp_helptext
33Setting Permissions
- GRANT Statement
- Example
- GRANT SELECT
- ON Customers
- TO Public
34Lab 3A Creating Objects
35Lab 3B Altering Objects
36Module 4 Security and Principals
37Surface 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
38Levels of Security
- Login Server Level
- User Database Level
- Object Permissions to Access Objects
- Application Security
- Network Access
- Encryption
39Windows Workgroup Model
- Distributed Accounts
- Each machine has locally maintained user and
group database - Windows Server Not Required
40Windows 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
41Active Directory
- Extension of the Domain Model
- Leverages DNS Name Resolution
- Better Security Protocols
- Better Fault Tolerance
- More Extensible
- Group Policy
42Logins
- Types
- Windows (Integrated)
- SQL (Standard)
- Tools
- SQL Management Studio
- Scripts
43Users
- Tools
- SQL Management Studio
- Scripts
44Roles
- Role Is A Group of users
- Purpose Ease Permission Management
- Role Types
- Standard
- Application Role
- Tools
- SQL Management Studio
- Scripts
45Permissions
- User Defined
- Action
- Object Access
- Object Execution
- Tools
- SQL Management Studio
- Scripts
46Lab 4A Creating Accounts
47Lab 4B Creating Users and Groups
48Lab 4C Permissions
49Module 5 Backup
50Reasons for Data Loss
- Accidental Update
- Hardware Failure
- Improper Application Design
- Transactions Not Used
- Other User Error
- Intentional Distruction
- Hackers
- Viruses
51SQL 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
52Increasing 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
53Restricted Activities During Backup
- Modifying Database Properties
- Autogrowth
- Managing Indexes
- Nonlogged Operations
54Backup Statement
- Example
- BACKUP DATABASE Lab3A
- To Diskc\Lab3A.bak
- WITH INIT, BLOCKSIZE2048
55Using SQL Management Studio
56Backup Options
57Database Recovery Model
- Property of a database
- Options
- Full
- Bulk_Logged
- Simple
- Affects log behavior
58Backup 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
59Operating 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
60Backup Devices
- Device File
- Permanent object you can reference by name
- Example
- BACKUP DATABASE Lab3A
- TO DailyBackup
61Tape 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
62Special 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
63Backup Strategies
- Weekly Full Backup
- Nightly Full Backup
- Variations of above with Differential and Log
Backups
64Best Practices
- Use Automation
- Test Your Backups
- Use Notification
- Use COPY_ONLY option as needed
- Understand the Business
- Keep Management Informed
65Lab 5A Backup
66Module 6 Restore
67Restore 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
68Restore Database Statement
- RESTORE DATABASE Lab6
- FROM DISKc\Lab6.bak
- WITH RECOVERY
69Restore Process
- Always backup the log first!
- Full
- Differential
- Log
- Recovery
70Saving Space
- Maintain the log file
- Compress your backup files
71Log Restore Options
- Stopping at a certain time
- Stopping at a named bookmark
72Single 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
73Restoring Master
- Place server into single user mode
- Perform Restore
- Restart Server
74Restoring Master with No Backup
- Rebuilm.exe utility creates brand new master,
model, msdb - Sp_attach_db plugs data files back in
75Best 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
76Lab 6A Restore
77Module 7 Web and Email Integration
78Web 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
79Communication Process
80Benefits of web integration
- Secure worldwide access
- No client deployment
- Remote access for administrative functions
81Dangers of web access
- Misuse of data
- Data communication interception
- Hackers have a doorway to your network
82SQL 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
83Protection from SQL injection
- Only use minimal priviledged account
- Pre-process all input
- Minimize direct SQL from web page by using Stored
Procs and UDFs
84ASP Example
85ASP.Net Example
86Email 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
87CDONTS Object
88CDO Object
89Database Mail
90SQL 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
91Lab 7A Web/Email Integration
92Module 8 Automation and Job Scheduling
93Requirements
- SQLServerAgent service must be running
- Permissions on network or local resources
94Automation Examples
- Nightly backup
- Nightly data transfer
- Hourly generation of web pages
- Detection of server problems
- Log 95 full
- Backup Log
- Email Notification
95Automation 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
96Jobs
- 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
97Defining a Job Step
98Operators
- 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
99Defining an Operator
100Alerts
- 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
101Defining An Alert
102Proxies
- Allow job steps to run with a different windows
login
103Lab 8A Automation and Job Scheduling
104Module 9 Data Transfer
105Data Transfer
- Movement of data between systems
- One of the most common DBA functions
106Data 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
107Data 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
108SSIS
- Import/Export Wizard
- Visual Studio Project
109BCP
- 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
110Backup/Restore
- Simply compress, then transfer the backup file to
the destination - Clean up sysusers table after restore
111Distributed queries
- Openquery/Openrowset/OpenXML
- SELECT INTO
- INSERT/SELECT
- Linked Servers
112Query Analyzer
- Controlling saved results
- Output to text file
- Reading from different providers
113Web page download
- Response Content type change
- Saving results to a file, then providing a link
- Job scheduler, results come through email
114Bulk Insert
115Detach/Reattach database files
- On database node
- Right click/all tasks/detach
- Clean up of sysusers not needed after
re-attachment
116Lab 9A Data Transfer
117Module 10 Replication
118Definitions
- Automatic transfer of data between locations
119Replication 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
120Replication Models
- Central publisher/Distributer
- Central Subscriber/Multiple Publisher
- Multiple Publisher/Subscriber
121Setup 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
122Lab 13A Replication