SQL DBA Training

Setting up SQL Server

• Selecting installation options
• Installing a named instance

Leveraging essential tools

• SQL Server Management Studio
• Configuration Manager
• Transact-SQL
• sqlcmd
• PowerShell
• Dedicated Administrator Connection

Constructing and Managing Databases

Inspecting storage structures

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

Designing file groups

• Maximizing storage utilization
• Placing tables on file groups

Upgrading and moving databases

• Choosing between upgrade and migration
• Detaching and attaching databases

Controlling database space

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

Handling Server and Database Security

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 mismapped logins

Granting and Revoking Permissions

Managing database-scoped privileges

• Permitting object creation
• Giving blanket permissions

Defining object-level permissions

• Limiting object access
• Meeting complex permission requirements with roles

Backup and Recovery

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

Streamlining Tasks with Jobs, Alerts and Database Mail

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

Database Maintenance and Monitoring

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