Duration: 4 Days
Help protect your organization by using the knowledge gained in this course to correctly manage mission-critical databases. With this training, you learn the essential skills for administering SQL Server databases, and gain extensive experience using SQL Server tools to perform real-world administrative tasks with SQL Server Management Studio and Transact-SQL.
You Will Learn How To
Ensure SQL Server security with logins, roles, users, and permissions
- Develop backup strategies and recover from disasters
- Leverage SQL Server Agent to automate administrative tasks
- Isolate and repair fragmentation and database corruption
Important Course Information
Requirements:
Relational database experience at the level of:
- Course 2107, SQL Server Introduction
- Working knowledge of computers and a familiarity with Windows
Recommended Experience:
Programming experience
Software:
- This course uses Microsoft SQL Server 2014, running on Windows Server 2008
- This course is applicable to those using earlier versions of SQL Server
Course Outline
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
- 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
