SQL Server Database Administration (DBA)

100

LESSONS

ALL

SKILL LEVEL

9:59 hr

DURATION

ENGLISH

AUDIO

ENG/ESP

SUBTITLES

OVERVIEW

SQL Server is one of the main database tools requested in the job market. The ability to do Database Administration for SQL Server is a desirable skill.

This course includes tasks which will give you hands-on experience in Database Administration. It will include a few deliberate ‘wrong turns’ to show you which problems might arise and how to deal with them. By the end of the course, you will be able to:

  • Create backups and restore databases.
  • Manage logins and server roles.
  • Import and export data.
  • Implement and maintain indexes.
  • Manage and configure databases, including compression and concurrency problems.
  • Manage SQL Server Agent and audit SQL Server instances.
  • Collect and analyse troubleshooting data.
  • Plan and install SQL Server and related services, including implementing a migration strategy.

In this course, we will only carry out activities which require one computer, rather than multiple servers or virtualization.

.

WHO IS THE COURSE FOR?

No prior knowledge of DBA is required.

It would be beneficial if you knew how to create simple T-SQL statements (SELECT, FROM, WHERE, GROUP BY) for some parts of the course, but this is not essential.

You may consider looking at the first two sessions of the T-SQL course to gain further understanding of SQL to assist you with a DBA role.

.

LEARNING PATH

BACKUP AND RESTORE

  • How to generate T-SQL.
  • Backing up and restoring.
  • Changing recovery models.
  • Different backup models.
  • Point in Time recovery.
  • Recover from a corrupted drive.

MANAGE LOGINS AND SERVER ROLES

  • Create login accounts.
  • Manage access to the server, and secure SQL server.
  • Create and maintain user-defined server roles.
  • Create database user accounts.
  • Fixed and user database-level roles.
  • Create and using schemas.
  • Create access to server/database with least privilege.
  • Protect objects from being modified.

IMPLEMENT AND MAINTAIN INDEXES

  • Indexes.
  • Fragmentation.
  • Fill factor.
  • Optimise indexes.
  • Identify unused indexes.
  • Statistics.

IMPORT/EXPORT. MANAGE SQL SERVER AGENT

  • Transfer data.
  • Bulk insert.
  • Import and Export data.
  • Jobs and alerts.
  • RAISERROR.
  • Adding operators.

MANAGE AND CONFIGURE DATABASES

  • Database configuration.
  • Design multiple file groups.
  • Manage file space with new filegroups.
  • Partitioning.
  • Managing a multi-TB database.
  • Log file growth.
  • DBCC.
  • Contained databases and logins.

COMPRESSION AND CONCURRENCY PROBLEMS

  • Page and row data compression.
  • Spare columns.
  • Columnstore indexes.
  • Blocking, live locking and deadlocking.
  • Monitoring.

TROUBLESHOOTING. AUDITING SQL SERVER INSTANCES

  • SQL Server Profiler.
  • XEvents (Extended Events)
  • System Monitor.
  • Bottlenecks.
  • Server audits.
  • Elevated privileges.
  • Track who modified an object.

PLAN INSTALLATION AND INSTALLING SQL SERVER

  • Choosing the right hardware.
  • Scale-up versus scale-out.
  • Windows-Level and Service Level Security.
  • Default and named instances.
  • Planning the installation of SQL Server.
  • Testing connectivity.
  • Configuring an O/S disk.

IMPLEMENT MIGRATION STRATEGY. INSTALL ADDITIONAL SERVER

  • Restore versus detach/attach.
  • Migrate security, systems and data from other sources.
  • Full-text indexing.
  • Security.
  • Filestream and Filetable.

REVIEWS

“Great course to update as well as improve my SQL-Server skills. Well explained in good detail with good examples. Thanks Phillip!” Maik

“Extremely comprehensive overview. This is the only database course I found that doesn’t focus on SQL, which is exactly what I needed. A lot of effort was put into this course. Very informative.” Layla

“The study is very detailed, the explanation is clear and accessible, and the demonstrations are very graphic and tangible, presenting as close as possible real situations.” Asaf

.

READY TO START?