Msft Partner gold blk v2

Administering a SQL Database Infrastructure

Skillable has reimagined this popular Administering a SQL Database course using our modern challenge-centric instructional design model. Live hands-on labs are at the forefront of this course...

Read More
$2,595 USD
Course Code 55380
Duration 4 days
Available Formats Classroom, Virtual
6865 Reviews star_rate star_rate star_rate star_rate star_half
Course Image

Skillable has reimagined this popular Administering a SQL Database course using our modern challenge-centric instructional design model. Live hands-on labs are at the forefront of this course allowing learners to learn while doing and include additional reference materials and post class access to Challenge Labs to further promote and enable continuous learning. In this 4-day course, learners will validate their introductory skills related to the administration of SQL Server Database infrastructure including security, encryption, and back up and restore best practices

Skills Gained

  • Configuration of SQL Server security
  • Encryption and auditing data
  • Backup and restore of SQL databases
  • Managing automation SQL Server
  • Import and Export of Data
  • Monitoring SQL Server Performance

Who Can Benefit

This course is intended for an IT professional who manages and maintains the administration of SQL Server databases.

Prerequisites

  • Recommended that learners have a functional understanding or working experience and knowledge of Windows client operating systems
  • Recommended that learners have a functional understanding or working experience and knowledge of Relational databases
  • Recommended that learners have a functional understanding or working experience and knowledge of database design
  • Recommended that learners have a functional understanding or working experience and knowledge of Transact-SQL (T-SQL)

Course Details

Outline


Module 1: Configure SQL Server Security
In this hands-on adaptive module, learners will configure SQL Server security. First, the learner will configure the server authentication mode, and then create SQL logins for authentication. Next, the learner will create Windows logins for authentication, and then create database users for authorization.
Lessons

  • Configure server authentication mode
  • Create SQL logins for authentication
  • Create Windows logins for authentication
  • Create database users for authorization

Lab 1: Configure SQL Server Security
  • Create SQL Logins for Authentication.
  • Create Windows Logins for Authentication.
  • Create Database Users for Authorization.
After completing this module, students will be able to:
  • Configure server authentication mode
  • Create SQL logins for authentication
  • Create Windows logins for authentication
  • Create database users for authorization

Module 2: Assign Server and Database Roles
In this hands-on adaptive module, learners will assign roles for a Microsoft SQL Server. First, the learner will assign a fixed server role to a login, and then create and assign a user-defined server role to a login. Finally, the learner will assign fixed and user-defined database roles to database users.
Lessons
  • Assign Roles for a Microsoft SQL Server
  • Assign a fixed server role to a login
  • Create and assign a user-defined server role to a login
  • Assign fixed and user-defined database roles to database users.

Lab 1: Assign Server and Database Roles
  • Assign a Fixed Server Role
  • Create and Assign a User-defined Server Role
  • Assign Fixed and User-defined Database Roles
After completing this module, students will be able to:
  • Explain server-scoped permissions.
  • Describe server-scoped permissions.
  • Understand fixed server roles and the public server role.
  • Create user-defined server roles.

Module 3: Authorize Users to Access Resources
In this hands-on adaptive module, learners will authorize access to a Microsoft SQL Server. First, the learner will authorize user access to objects, and then authorize users to execute code. Finally, the learner will configure permissions at the schema level.
Lessons
  • Authorize access to a Microsoft SQL Server
  • Authorize user access to objects
  • Authorize users to execute code
  • Configure permissions at the schema level

Lab 1: Authorize Users to Access Resources
  • Authorize User Access to Objects
  • Authorize Users to Execute Code
  • Configure Permissions at the Schema Level
After completing this module, students will be able to:
  • Authorize user access to objects
  • Authorize users to execute code
  • Configure permissions at the schema level

Module 4: Protect Data with Encryption and Auditing
In this hands-on adaptive module, learners will protect data in a Microsoft SQL Server. First, the learner will encrypt a database by using Transparent Data Encryption, and then encrypt sensitive columns by using Always Encrypted. Finally, the learner will enable SQL Audit to allow auditing of SQL Server activity.
Lessons
  • Learners will protect data in a Microsoft SQL Server
  • Encrypt database by using Transparent Data Encryption
  • Encrypt sensitive columns by using Always Encrypted
  • Enable SQL Audit to allow auditing of SQL Server Activity

Lab 1: Protect Data with Encryption and Auditing
  • Encrypt a database
  • Encrypt sensitive columns
  • Enable SQL Audit
After completing this module, students will be able to:
  • Describe the options for auditing data access.
  • Implement and Manage SQL Serve Audit.
  • Understand and implement encryption methods in SQL Server.

Module 5: Back Up SQL Server Databases
In this hands-on adaptive module, learners will back up Microsoft SQL Server databases. First, the learner will perform a full database backup, and then perform a transaction log backup.
Lessons
  • Backup Microsoft SQL Server Databases
  • Perform a full database backup
  • Perform a transaction log backup

Lab 1: Back Up SQL Server Databases
  • Create and execute a Transact-SQL query that returns backup
  • Create a logical backup device
  • Create a tail-log backup for a database
After completing this module, students will be able to:
  • Understand a variety of backup strategies
  • Explain how database transition logs work
  • Create SQL Server backup strategies

Module 6: Restore SQL Server Databases
In this hands-on adaptive module, learners will restore databases on a Microsoft SQL Server. First, the learner will perform a restore by using a full database backup, and then perform a second restore by using a differential database backup. Finally, the learner will perform a point-in-time restore by using transaction log backups.
Lessons
  • Learners will restore databases on a Microsoft SQL Server
  • Perform a restore by using a full database backup
  • Perform a second restore by using a differential database backup
  • Perform a point-in-time restore by using transaction log backups

Lab 1: Restore SQL Server Databases
  • Create and execute a Transact-SQL query that performs a restore
  • Create a differential database backup
  • Perform a point-in-time restore by using SSMS
After completing this module, students will be able to:
  • Understand the restore process
  • Restore databases
  • Conduct advanced restore operations
  • Complete a point-in-time recovery

Module 7: Automate SQL Server Management
In this hands-on adaptive module, learners will automate management in a Microsoft SQL Server. First, the learner will enable SQL Server Agent for automation, and then create, run, and schedule a job. Next, the learner will create a single-schedule maintenance plan, and finally create a multi-schedule maintenance plan.
Lessons
  • Automate management in a Microsoft SQL Server
  • Enable SQL Server Agent for automation
  • Create, run, and schedule a job
  • Create a single-schedule maintenance plan
  • Create a multi-schedule maintenance plan

Lab 1: Automate SQL Server Management
  • Create a SQL Server Agent job for to back up the database
  • Create and executed maintenance plans by using SSMS
After completing this module, students will be able to:
  • Manage SQL Server Agent jobs.
  • Define jobs, job types, and job categories.
  • Troubleshoot failed jobs.

Module 8: Configure Security for SQL Server Agent
In this hands-on adaptive module, learners will configure security for automation in a Microsoft SQL Server. First, the learner will create and run a job by impersonating a user, and then create a job to perform file operations.
Lessons
  • Configure security for automation in a Microsoft SQL Server
  • Create and run a job by impersonating a user
  • Create a job to perform file operations

Lab 1: Configure Security for SQL Server Agent
  • Enable SQL Server Agent and job to automate backup
  • Automate file operations with proper permissions
  • Create and enable a proxy
After completing this module, students will be able to:
  • Give an overview of security in SQL Server Agent.
  • Describe the fixed SQL Server Agent roles.
  • Troubleshoot problems with security in SQL Server Agent.
  • Discuss configuring credentials in SQL Server Agent.

Module 9: Manage SQL Server Alerts and Notifications
In this hands-on adaptive module, learners will manage alerts and notifications in a Microsoft SQL Server. First, the learner will configure SQL Server Agent to use Database Mail, and then create an operator and a notification to monitor a database. Finally, the learner will create and test an alert.
Lessons
  • Manage alerts and notifications in a Microsoft SQL Server
  • Configure SQL Server Agent to use Database mail
  • Create an operator and a notification to monitor database
  • Create and test an alert

Lab 1: Manage SQL Server Alerts and Notifications
  • Configure SQL Server Agent to enable default mail profiles
  • Create a query to show all emails sent from mail profiles
  • Automate notifications and alerts
After completing this module, students will be able to:
  • Configure a SQL Server error log.
  • Configure Database Mail profiles and security.
  • Describe the role of operators in SQL Server Agent.

Module 10: Trace Access to SQL Server with Extended Events
In this hands-on adaptive module, learners wil ltrace activity for a Microsoft SQL Server. First, the learner will create an Extended Events session, and then use the XEvent Profiler. The learner will then use the system_health Extended Events session.
Lessons
  • Create an Extended Events session using the XEvent Profiler
  • Use the system_health Extended Events session.

Lab 1: Trace Access to SQL Server with Extended Events
  • Create Windows groups and database user accounts
  • Create, start, and view an Extended Events session
  • Captures events related to deadlocks in SQL Server
  • View system-heal in graphical format
After completing this module, students will be able to:
  • Capture and manage SQL Server performance data.
  • Discuss SQL Server activity and data.
  • Describe the functionality of Activity Monitor in SQL Server Management Studio.
  • Explain the advantages of working with Performance Monitor.
  • Explain the role of the data collector.
  • Discuss the value of Query Statistics reports

Module 11: Monitor SQL Server Activity and Performance
In this hands-on adaptive module, learners will monitor activity and performance for a Microsoft SQL Server. First, the learner will use Activity Monitor in SQL Server Management Studio, and then use dynamic management objects to view activity. Finally, the learner will configure data collection for performance analysis
Lessons
  • Monitor activity and performance for a Microsoft SQL Server
  • Use Activity Monitor in SQL Server Management Studio
  • Use dynamic management objects to view activity
  • Configure data collection for performance analysis

Lab 1: Monitor SQL Server Activity and Performance
  • Enable Activity Monitor to discover the blocking and blocked connections activities
  • Use dynamic management objects (DMO) to capture activities
  • Configure a management data warehouse for data collection
  • Enable data collection sets
After completing this module, students will be able to:
  • Capture and manage SQL Server performance data.
  • Discuss SQL Server activity and data.
  • Describe the functionality of Activity Monitor in SQL Server Management Studio.
  • Explain the advantages of working with Performance Monitor.
  • Explain the role of the data collector.
  • Discuss the value of Query Statistics reports.

Module 12: Import and Export Data
In this hands-on adaptive module, learners will mport and export data for a Microsoft SQL Server. First, the learner will switch partitions for data transfer, and then use the SQL Server Import and Export wizard. Finally, the user will use the BCP utility and the BULK INSERT statement.
Lessons
  • Import and export data for a Microsoft SQL Server
  • Switch partitions for data transfer
  • Use the SQL Server Import and Export wizard
  • Use the BCP utility and the BULK INSERT statement

Lab 1: Import and Export Data
  • Execute a Transact-SQL query that creates a partition function, scheme, and tables
  • Initiate and verify data transfer
  • Execute an Import and Export of data using the SQL Server Import and Export Wizard
  • Create and execute a Transact-SQL query that imports data by using various statements
After completing this module, students will be able to:
  • Describe data transfer concepts.
  • Describe SQL Server tools for transferring data.
  • Import and export table data.
  • Explain the use of data-tier applications to import and export database applications.
  • Discuss the SQL Server Integration Services.
  • Describe working with bcp to import and export data.
Contact Us 1-800-803-3948
Contact Us
FAQ Get immediate answers to our most frequently asked qestions. View FAQs arrow_forward