[attachment=15279]
SQL Server Basics for non-DBAs
Independent consultant (Austin, TX)
Author of several SQL Server books
Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning)
Info: http://AnilDesai.net or Anil[at]AnilDesai.net
Overview and Agenda
SQL Server 2005 Platform Overview
Managing Databases
Database Maintenance and Data Protection
Securing SQL Server
Managing Database Objects / Best Practices
SQL Server 2005 Platform Overview
Understanding SQL Server’s features, services, and administrative tools
Relational Database Server Goals
SQL Server 2005 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
Instance-Specific (one service per instance):
SQL Server
SQL Server Agent
Analysis Services
Reporting Services
Full-Text Search
Instance-unaware
Notification Services
Integration Services
SQL Server Browser
SQL Server Active Directory Helper
SQL Writer
SQL Server 2005 Admin. Tools
SQL Server Management Studio
Database management GUI
Object browser; templates, reports, etc.
Based on Visual Studio 2005 IDE
Support for writing and executing queries
SQL Business Intelligence Dev. Studio
Analysis Services, Reporting Services, SSIS
SQL Server 2005 Admin. Tools
SQL Server Profiler
Database Engine Tuning Advisor
SQL Server Configuration Manager
Manages services and protocols
Surface Area Configuration
SQL Server Books Online
Configuring SQL Server
Default options are set during installation
SQL Server Management Studio
Server Properties:
Memory
Processors
Security (Windows, SQL Server); Auditing
Database settings (default file locations)

Managing Databases
An overview of working with physical and logical database files
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
Monitoring Disk Usage
SQL Server Management Studio Reports
Server: Server Dashboard
Database: Disk Usage (several reports)
Transact-SQL
Stored Procedures:
sp_Help, sp_HelpDB, sp_SpaceUsed
System Tables / Views
Sys.Database_Files
Designing Data Storage
Goals:
Maximize performance by reducing contention
Simplify administration
Best practices:
Monitor and analyze real-world workloads
Separate data files and transaction log files
Comparing RAID Levels
Monitoring Disk Usage
Moving and Copying Databases
Copy Database Wizard
Attaching and detaching databases
Allows directly copying data/log files
Database must be taken offline
Backup / Restore
Other methods:
SQL Server Integration Services (SSIS)
Generating scripts for database objects
Bulk copy / BULK INSERT
Database Maintenance & Data Protection
Methods for maintaining, backing up, and restoring databases
Database Backup Types
Recovery Models
Full
Bulk-logged
Simple
Backup operations
Full Backups
Differential Backups
Transaction Log Backups
Allows point-in-time recovery
Recovery Processes
Recovery process:
Latest full backup (Required)
Latest differential backup (Optional)
Unbroken sequence of transaction log backups (Optional)
All transaction logs should be restored with NO RECOVERY option (except for the last one)
Prevents database from being accessed while restore process is taking place
Database Maintenance Plans
Maintenance Plan Wizard
Scheduling
Single schedule for all tasks
Multiple schedules
Databases:
System, All, All User, or specific databases
Wizard Options:
Order of operations
Manages logging and history of operations
Reliability & Availability Options
Database Mirroring
Log-shipping
SQL Server Fail-Over Clusters
Distributed Federated Servers
Replication
Load-Balancing (at network or OS level)
Securing SQL Server
Understanding SQL Server 2005’s security architecture and objects
SQL Server Security Overview
Layered Security Model:
Windows Level
SQL Server Level
Database
Schemas (for database objects)
Terminology:
Principals
Securables
Permissions
Scopes and Inheritance
Security Overview
(from Microsoft SQL Server 2005 Books Online)
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
SQL Server Service Accounts
Local Service Account
Permissions of “Users” group (limited)
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
SQL Server Surface Area Configuration
Default installation: Minimal services
SAC for Services and Connections
Allow Remote Connections
Access to Reporting Services, SSIS, etc.
SAC for Features
Remote queries
.NET CLR Integration
Database Mail
xp_cmdshell
Managing Logins
Windows Logins
Authentication/Policy managed by Windows
SQL Server Logins
Managed by SQL Server
Based on Windows policies
Password Policy Options:
HASHED (pw is already hashed)
MUST_CHANGE
CHECK_EXPIRATION
CHECK_POLICY
Creating Logins
Transact-SQL
CREATE LOGIN statement
Replaces sp_AddLogin and sp_GrantLogin
SQL Server Logins
Windows Logins
SQL Server Management Studio
Setting server authentication options
Login Auditing
Managing Logins
Database Users and Roles
Database Users
Logins map to database users
Database Roles
Users can belong to multiple roles
Guest (does not require a user account)
dbo (Server sysadmin users)
Application Roles
Used to support application code
Creating Database Users and Roles
CREATE USER
Replaces sp_AddUser and sp_GrantDBAccess
Can specify a default schema
Managed with ALTER USER and DROP USER
CREATE ROLE
Default owner is creator of the role
SQL Server Management Studio
Working with Users and Roles
Built-In Server / Database Roles
Understanding Database Schemas
Schemas
Logical collection of related database objects
Part of full object name:
Server.Database.Schema.Object
Default schema is “dbo”
Managing Schemas
CREATE, ALTER, DROP SCHEMA
SQL Server Management Studio
Can assign default schemes to database users:
WITH DEFAULT_SCHEMA ‘SchemaName’
Configuring Permissions
Scopes of Securables
Server
Database
Schema
Objects
Permission Settings:
GRANT
REVOKE
DENY
Options
WITH GRANT OPTION
AS (Sets permissions using another user or role)
Managing Execution Permissions
Transact-SQL Code can run under a specific execution context
By default, will execute as the caller
EXECUTE AS clause:
Defined when creating an object or procedure
Options:
CALLER (Default)
SELF: Object creator
Specified database username
Other Security Options
Database Encryption
Encrypting Object Definitions
Data encryption
SQL Server Agent
Proxies based on subsystems allow lock-down by job step types
Preventing SQL Injection attacks
Use application design best practices
Managing Database Objects
Understanding database design, tables, and indexes
Overview of Database Objects
Designing a database
Normalization
Reduces redundancy and improves data modification performance
Denormalization is often done to enhance reporting performance (at the expense of disk space and redundancy)
Referential Integrity
Maintains the logical relationships between database objects
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 disk space
One per table (may inc. multiple columns)
Created by default on tables’ Primary Key column
Non-Clustered Index
Physical data structures that facilitate data retrieval
Can have many indexes
Indexes may include many columns
Database Management Best Practices
Maintenance and optimization of SQL Server 2005
SQL Server Maintenance
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
Regular tasks
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 Management Features
SQL Server Agent
Jobs
Alerts
Operators
SQL Server Logs
Database Mail
Linked Servers