Transcript SQL Server Administration

SQL Server
Administration
Overview
 Security




Server roles
Database roles
Object permissions
Application roles
 Managing data
 Backups
 Restoration
 Replication
 Performance Monitoring and Optimization
Security
 Server roles
 Database creators
 Able to create, modify, and rename databases
 Security administrators
 Able to create logins and modify low level
statement permissions
 Server administrators
 Able to manage how SQL Server functions
 System administrators
 Able to do anything
Security
 Database roles
 Public
 Every login is a member, permissions granted here are granted to
every login
 Database Owner
 Has complete control over the database
 Security Admin
 Able to manage the logins and permissions of the database objects
 DDL Admin
 Used for assistant administrators; can issue all data definition
language statements
 Data Reader/Writer and Deny
 Grants the ability to read/write or revoke read/write privileges
Security
 Object permissions
 Granted
 Allows the action to be performed
 Denied
 Disallows the action even if granted in another location
 Revoked
 Disallows the action unless other permissions are inherited
from another location
 Application Roles
 Grants its permissions to anyone with the correct
password
Security Tips
 Using views to display data
 Using stored procedures to insert/update
data
 Dedicated login for applications
Managing Data
 Backups
 How often
 What to back up
 Restoration
 Disaster recovery plan
 Replication
 Does it need to be done
 How often
 Can it be used instead of backups
Performance Monitoring and
Optimization
 SQL Server manages most optimization
internally
 There are many things that can be changed from the
defaults
 Query time, memory limits, parallelism
 Built-in performance monitors, profiler, and
counters
 Can also create user-defined counters
 Many optimization and monitoring tasks can be
automated