Getting Started with SQL Server
Setting up SQL Server
- Selecting installation options
- Installing a named instance
Leveraging essential tools
- SQL Server Management Studio
- Configuration Manager
- Transact-SQL
- sqlcmd
- PowerShell
Constructing and Managing Databases
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
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 mis-mapped 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