Transcript Introduction to SQL Server for Windows Administrators Presented to WiNSUG 02/05/09 Bret Stateham Owner, Net Connex [email protected] Blogs.netconnex.com Twitter: @BStateham.

Introduction to SQL Server for
Windows Administrators
Presented to WiNSUG 02/05/09
Bret Stateham
Owner, Net Connex
[email protected]
Blogs.netconnex.com
Twitter: @BStateham
Agenda
•
•
•
•
•
•
•
The SQL Server Product Family
SQL Server Installation
Initial configuration
Client Tools
Databases
Security
Backup and Restore
The SQL Server Product Family
•
•
•
•
SQL Server Database Engine
SQL Server Analysis Services (SSAS)
SQL Server Integration Services (SSIS)
SQL Server Reporting Services (SSRS)
SQL Server
Analysis Services
SQL Server
Integration Services
SQL Server Database Engine
SQL Server
Reporting Services
SQL Server Database Engine
• What people mean when they say “SQL
Server”
• Relational Database Management System
(RDBMS)
• Supports multiple databases per Instance
• Supports multiple instances per server
• Used for On Line Transaction Processing
(OLTP) solutions
SQL Server Analysis Services
• Multi-Dimensional database engine
• Multiple databases with multiple cubes per
instance
• Multiple instances per server
• Used for On Line Analytical Processing (OLAP)
operations
• Databases are created in “Business Intelligence
Development Studio” (BIDS)
• Includes Data Mining capabilities for discovering
patterns in data.
SQL Server Integration Services
• Used to move data between databases and
platforms
• Wizards:
– “Import and Export Wizard”
– “Copy Database Wizard”
• More complex development done in “Business
Intelligence Development Studio” (BIDS)
• SQL Servers “Extract, Transform and Load”
(ETL) tool
SQL Server Reporting Services
• Enterprise reporting platform
• Provides a website that users can go to for
reports
• Reports can also be delivered via email or as files
in a folder share using subscriptions
• Can report on data in platforms other than SQL
Server
• Again, Business Intelligence Development Studio
is the development environment
• Includes “Report Builder” for power users
SQL Server Editions
Edition
Description
Enterprise Edition
Everything that SQL Server offers.
Standard Edition
Departmental or small business use. All
services, but some features are limited.
Workgroup Edition
For small workgroups. Limited features
Developer Edition
Same as express, but can be used in
development only. Lists for $49.00
Express Edition
Free database engine. Legal to use in
production environments but with limited
features and power.
Compact Edition
For use on hand-held devices or as a small
footprint in memory database engine.
Installation Demo
SQL Server Services
Service
Description
SQL Server (<instance name>)
The SQL Server Database Engine
SQL Server Active Directory Helper
Used for AD integration
SQL Server Agent (<instance name>)
Performs scheduled operations
SQL Server Analysis Services (<instance>)
The multi-dimensional database engine
SQL Server Browser
Helps clients find instances
SQL Server FullText Search (<instance>)
Creates indexes on large character fields
SQL Server Integration Services
ETL (Import and Export)
SQL Server Reporting Services (<instance>) Centralized reporting platform
SQL Server VSS Writer
Volume Shadowcopy Service integration
SQL Server Files
• Default Path:
C:\Program Files\Microsoft SQL Server
• \80, \90, \100 Folders are the client tools
• SQL 2005 - MSSQL.x are the various instances
– MSSQL = Database Engine
– OLAP = Analysis Services
– Reporting Services = Reporting Services
• SQL 2008 – Folders are named:
– MSSQL10.<instance> = SQL Database Engine
– MSAS10.<instance> = Analysis Services
– MSRS10.<instance> = Reporting Services
Registry Keys
• Software Configuration:
HKLM\Software\Microsoft\Microsoft SQL Server *
• Service Configuration:
HKLM\System\CurrentControlSet\Services\MSSQL*
Initial Configuration
• SQL Server Configuration Manager
– Services
– Server Network Configuration
– Client Network Configuration
• Surface Area Configuration Manager
– SQL 2005 Only (Goes away in 2008)
– Enable features that are off by default
– Some overlap with SQL Server Configuration
Manager
Key Configurations
• Set a secure password on the SA account
regardless of the security configuration
• Backup the system databases
• Apply service packs
• Backup the system databases again
• Use the SQL Server Configuration Manager to
enable remote access
Client Tools
• SQL Server Management Studio (SSMS)
– Replaces “Enterprise Manager”
– Replaces “Query Analyzer”
– Adds functionality that didn’t exist before (Analysis
Services, Compact Edition, Reporting Services)
• SQLCMD (Command Line Tool)
– Replaces “OSQL”
– Allows interaction with SQL Server from the command
line or batch files.
Databases
From an Admin’s Point of View
•
•
•
•
The Physical Database
The Logical Database
System Databases
Creating Databases
The Physical Database
• A database is at least two files:
– At least one database file (.mdf and .ndf)
– At least one transaction log file (.ldf)
– Most databases will only have one of each
• Data files store
– The data in the tables and indexes
– The code for the stored procedures, etc.
– The “meta data” (object definitions, security, etc.)
• Log Files store
– Details about the transactions that occur in the
database.
The Logical Database
• Tables
– Store the various types of data we want to work
with (Employees, Products, etc)
• Indexes
– Help SQL Server find your data quickly (Index by
last name, postal code, phone number, etc)
• Views, Stored Procedures, Triggers, Functions
– Code objects stored in the database for re-use
– Adds a layer of security to the database
System Databases
Database
Use
Master
The only database SQL Server knows about at start up
Documents all the other databases
Stores the server configuration values (like memory config,etc)
Stores “logins” to control access to the instance
Model
The template database used when each database is created
MSDB
Stores SQL Server Agent jobs, alerts, operators, schedules
Stores backup and restore history
Stores SSIS packages that are saved to “SQL Server” or “MSDB”
TempDB
Used by SQL Server when it needs extra room during sorts, etc.
Can be used by developers to store temporary data
Is used in certain cases to store previous versions of data
Creating a Database
• SQL Server Management Studio (SSMS)
• File Placement
– Data files and log files should be in different drives
– Data files work well on striped arrays
– Log files work well on mirror sets
• Choose an appropriate initial file size
• Choose appropriate file growth settings
Creating a Database Demo
SQL Server Security
•
•
•
•
Authentication methods
Instance Level Permissions
User Accounts
Database Level Permissions
Authentication Methods
• Windows Authentication
– Recommended method
– Uses Windows User Accounts and Groups
– Easiest management in the long run
• SQL Authentication
– The “SA” Account
– Can use the same password policies as Windows
– Use only in special situations
• Logins
– Can be either “Windows” or “SQL” logins
– Used to gain access to the instance
Instance Level Permissions
• Are stored in the “master” database
• Fixed Server Roles
– Assign bundles of instance permissions to logins
– Sysadmin: full control over the instance
– Dbcreator: allowed to create databases
• All permissions are assignable
– The “CONTROL” permission grants full control over
any resource
User Accounts
•
•
•
•
Are stored in each database
Used to grant access to individual databases
Map to a login
“dbo” stands for Database Owner
– Maps to the login that owns the database
– Full control over the database
• “guest” exists, but is disabled by default
Database Level Permissions
• Fixed Database Roles
–
–
–
–
Assign bundles of permissions to users
Db_owner: Full control over the database
Db_ddladmin: can create any object
Db_datareader: can select data from any table
• Assignable Permissions
– CONTROL, CREATE, SELECT, INSERT, UPDATE,etc.
• User Defined Roles
– You can create roles to simply the assignment of
permissions
SQL Server Security Overview
Active
Directory
Windows
Server
SQL Server
Logins & Roles
Administrator
Administrator
sa
dbo
Domain
Admins
Administrators
sysadmin
db_owner
BUILTIN
Administrators
Database
Users & Roles
Schema &
Objects
Creator or
Owner
Creating Logins and Users Demo
Backup and Restore
•
•
•
•
•
•
Backup Types
Backup Media
Third Party Backup Solutions
Database Recovery Model Option
Performing Backups
Performing Restores
Backup Types
• Database Backups
– Backup data from the data files
– Full Backups: Backup everything
– Differential Backups: Backup changes since the last full
backup
• Transaction Log Backups
– Backup any unbacked-up information in the
transaction log
– Act as “Incremental” backups
– Allow us to restore our databases to the point of
failure
Backup Media
• Tape
– Can backup to tape if there is a tape device visible to
SQL Server
• File
– Can backup instead to a file on disk
– Can be a local file or a unc path
• Can “Append” or “Overwrite” media when
backing up
• Common solution
– SQL Server to backup to disk
– Enterprise backup solution backs up backup files
Third Party Backup Solutions
•
•
•
•
Enterprise Backup Solutions support SQL
Typically, and “Agent” is installed
Most of them perform regular SQL Backups
They store they backup data on their media
Database Recovery Model Option
• Each database has a “Recovery Model” option
• FULL
– All transactions logged in detail, including bulk operations
– Transactions are kept in the log until we backup
– Best choice for most production databases
• BULK_LOGGED
–
–
–
–
All transactions are logged, Bulk operations only “lightly”
Bulk data is included in the log backup, not the live log
Transactions are kept in the log until we backup
Use this when you do a lot of bulk loads
• SIMPLE
–
–
–
–
Transactions are logged just like BULK_LOGGED
Transactions are cleared from the log when they are done
You can not backup the log
Good choice for development databases or databases where it is ok to lose
some data.
Performing Backups
Performing Restores
Summary
•
•
•
•
•
•
•
The SQL Server Product Family
SQL Server Installation
Initial configuration
Client Tools
Databases
Security
Backup and Restore
Thanks!
• Stay in touch:
– Email: [email protected]
– Blog: blogs.netconnex.com
– Twitter: @BStateham
• Do you know about
Microsoft Tag?
gettag.mobi