Administering Microsoft SQL Server Databases

This course will provide delegates with the knowledge and skills to maintain a Microsoft SQL Server 2014 database. It focuses on use of the SQL Server 2014 product features and tools required to successfully maintain a database.

Who's it for?

The course is designed for IT professionals that administer and maintain SQL Server databases as a  primary area of responsibility or are heavily involved with databases. It will also be helpful for developers producing applications that deliver content from SQL Server databases.

Key objectives

After attending this course a delegate will be able to:

  • plan and install SQL Server
  • describe system databases, the physical structure of databases and the most common configuration options
  • explain the concept of the transaction log and the SQL Server recovery models and implement the different backup strategies available with SQL Server
  • back up SQL Server databases
  • restore SQL Server databases
  • import and export data by using wizards, the bulk copy utility and data-tier applications
  • work with SQL Server security models, logins, users and contained databases
  • work with server roles and database roles
  • use permissions to secure resources
  • use SQL Server audit and Transparent Data Encryption
  • work with SQL Server Agent to configure and execute jobs
  • implement SQL Server Agent security, proxy accounts and credentials
  • configure database mail, alerts and notifications
  • create database maintenance plans
  • use SQL Server Profiler, SQL Trace and Distributed Relay
  • use distributed management views and configure data collection.

Prerequisites

Attendees should have:

  • a basic knowledge of the Microsoft Windows OS and its core functionality
  • a working knowledge of Transact-SQL and relational databases
  • some experience with database design.

What's covered?

Module 1: Installing SQL Server 2014

  • Introduction to the SQL Server platform
  • SQL Server services and configuration options
  • Considerations for installing SQL Server
  • Installing SQL Server 2014
  • Lab: Performing pre-installation stress tests
  • Lab: Installing a SQL Server instance
  • Lab: Performing post-installation checks

Module 2: Working with databases

  • Overview of SQL Server databases
  • Creating databases
  • Using database files and filegroups
  • Moving database files
  • Buffer pool extension
  • Lab: Creating a storage pool
  • Lab: Adjusting tempdb configuration
  • Lab: Creating and configuring a database
  • Lab: Attaching a database
  • Lab: Using buffer pool extension

Module 3: SQL Server 2014 recovery models

  • Backup strategies
  • Understanding SQL Server transaction logging
  • Planning a SQL Server backup strategy
  • Lab: Planning a backup strategy
  • Lab: Configuring recovery models
  • Lab: Reviewing recovery models and strategy

Module 4: Backup of SQL Server 2014 databases

  • Backing up databases and transaction logs
  • Managing database backups
  • Working with backup options
  • Lab: Investigating backup compression
  • Lab: Performing transaction log backups
  • Lab: Performing differential backups
  • Lab: Performing copy-only backups
  • Lab: Performing filegroup backups

Module 5: Restoring SQL Server 2014 databases

  • Understanding the restore process
  • Restoring databases
  • Working with point-in-time recovery
  • Restoring system databases and individual files
  • Lab: Determining a restore strategy
  • Lab: Restoring a database
  • Lab: Using STANDBY Mode

Module 6: Importing and exporting data

  • Transferring data to/from SQL Server
  • Importing and exporting table data
  • Inserting data in bulk
  • Deploying and upgrading data-tier applications
  • Lab: Importing an Excel spreadsheet
  • Lab: Importing a CSV file
  • Lab: Creating and testing an extraction package
  • Creating and Deploying a Data-tier Application
  • Comparing Loading Performance

Module 7: Authenticating and authorising Users

  • Authenticating connections to SQL Server
  • Authorising logins to access databases
  • Authorisation across servers
  • Partially contained databases
  • Lab: Creating logins
  • Lab: Correcting an application login issue
  • Lab: Creating database users
  • Lab: Correcting access to a restored database

Module 8: Assigning server and database roles

  • Working with server roles
  • Working with fixed database roles
  • Creating user-defined database roles
  • Lab: Assigning server roles
  • Lab: Assigning fixed database roles
  • Lab: Creating and assigning user-defined database roles
  • Lab: Checking role assignments

Module 9: Authorising users to access resources

  • Authorising user access to objects
  • Authorising users to execute code
  • Configuring permissions at the schema level
  • Lab: Assigning schema-level permissions
  • Lab: Assigning object-level permissions
  • Lab: Testing permissions

Module 10: Protecting data by using encryption and auditing

  • Options for auditing data access in SQL Server
  • Implementing SQL Server audit
  • Managing SQL Server audit
  • Protecting data by using encryption
  • Lab: Determining audit configuration and creating an audit
  • Lab: Creating server audit specifications
  • Lab: Creating database audit specifications
  • Lab: Testing audit functionality
  • Lab: Using Transparent Data Encryption

Module 11: Automating SQL Server 2014 management

  • Automating SQL Server management
  • Working with SQL Server agent
  • Managing SQL Server agent jobs
  • Multi-server management
  • Lab: Creating a job
  • Lab: Scheduling a job
  • Lab: Troubleshooting a failing job
  • Lab: Creating a multi-server job

Module 12: Configuring security for SQL Server agent

  • Understanding SQL Server agent security
  • Configuring credentials
  • Configuring proxy server accounts
  • Lab: Troubleshooting job execution failure
  • Lab: Resolving security issues
  • Lab: Performing further troubleshooting

Module 13: Monitoring SQL Server 2014 by using alerts and notifications

  • Configuring database mail
  • Monitoring SQL Server errors
  • Configuring operators, alerts and notifications
  • Lab: Configuring database mail
  • Lab: Implementing notifications
  • Lab: Implementing alerts

Module 14: Performing ongoing database maintenance

  • Ensuring database integrity
  • Maintaining indexes
  • Automating routine database maintenance
  • Lab: Check database integrity by using DBCC CHECKDB
  • Lab: Correcting index fragmentation
  • Lab: Creating a database maintenance plan
  • Lab: Investigating table lock performance

Module 15: Tracing access to SQL Server 2014

  • Capturing activity by using SQL Server Profiler
  • Improving performance by using the Database Engine Tuning Advisor
  • Working with tracing options
  • Distributed replay
  • Monitoring locks
  • Lab: Capturing a trace by using SQL Server Profiler
  • Lab: Analysing a trace by using the Database Engine Tuning Advisor
  • Lab: Configuring SQL Trace

Module 16: Monitoring SQL Server 2014

  • Monitoring activity
  • Capturing and managing performance data
  • Analysing collected performance data
  • SQL Server Utility
  • Lab: Creating a utility control point
  • Lab: Configuring a health policy
  • Lab: Configuring instances for data collection
  • Lab: Working with data collector reports

Next step

To obtain course venues/dates and pricing please email your account manager, submit the enquiry form below or call us on 0845 647 0000*

Enquire now . . .