SQL Server Database Administration

Setting up SQL Server

• Selecting installation options
• Installing a named instance

Leveraging essential tools

• SQL Server Management Studio
• Configuration Manager
• Transact-SQL
• sqlcmd
• PowerShell

Inspecting storage structures

• Relating servers, databases and files
• Creating databases and transaction logs

Designing file groups

Upgrading and moving databases

• Maximizing storage utilization
• Placing tables on file groups
  • Choosing between upgrade and migration
  • Detaching and attaching databases

• Permitting automatic database growth
• Adding database files to expand databases

Implementing server security

• Comparing authentication modes
• Defining logins
• Creating user-defined server roles
• Enforcing password policy

Granting database access

• Contrasting users and logins
• Adding users
• Defining new roles
• Delegating privileges with predefined roles
• Repairing mis-mapped logins

Managing database-scoped privileges

• Permitting object creation
• Giving blanket permissions

Defining object-level permissions

• Limiting object access
• Meeting complex permission requirements with roles

Backing up databases

• Selecting a recovery model
• Investigating the transaction log
• Running full, log and differential backups

Restoring databases

• Performing a post-crash log backup
• Rebuilding the master database
• Recovering user and system databases

Configuring SQL Server Agent

• Setting agent properties
• Implementing database mail

Defining jobs to handle routine tasks

• Creating alerts and operators
• Initiating multi-step jobs
• Associating alerts with jobs

Handling fragmentation and database corruption

• Identifying and curing fragmentation
• Repairing database corruption

Building database maintenance plans

• Scheduling plan execution
• Notifying operators of success or failure

Resolving contention problems

• Identifying blocked processes
• Killing blockers