SQL Server
Administration Course
Length - 2 Day
About the course
This DataShed SQL Server Administration course provides an introduction to performing various administrative
tasks with Microsoft SQL Server 2016, along with practical examples. The course covers most aspects of SQL
Server administration, including backup and recovery, the security model, and database maintenance. The course
also covers several methods of accessing and manipulating data in the database using Integration Services, plus a
rundown of the capabilities of the SQL Server Management Studio tool.
At the completion of this course participants should be able to:
• Customise and use the Management Studio administration tool
• Understand database components and SQL Server operation
• Back up and restore databases
• Choose an appropriate database recovery model and backup methodology
• Automate administrative, maintenance and other tasks
• Create and manipulate SQL objects including tables and views
• Create and modify constraints, triggers, and indexes
• Understand the SQL security model and the relationships between logins, users, roles and permissions
• Create and automate custom data import/export processes
Who should attend?
Users of Microsoft SQL Server who have been given the task of maintaining one or more SQL Server instances.
About our trainers
Our course leaders are selected from our team of highly skilled data consultants.
Course Content
Day 1
1. SQL Server Management Tools
2. Management SQL Instance and Database
3. Backup and recovery
4. SQL Server Agent
Day 2
1. Creating/Modifying tables, views and constratints
2. Indexes, Query Optimiser and Execution Plans
3. Stored Procedures, User-defined functions, Triggers
4. The Query Window and the Query Designer
5. Security/Permissions - Logins, Users, Roles, Privileges
+61 8 9432 1777 training@maxgeo.com maxgeo.com
Day 1
1. SQL Server Management Tools
SQL Server Configuration Manager
• Starting/stopping and configuring SQL services
• Configuring network listeners
• Client protocols and aliases
SQL Server Management Studio (SSMS)
• Customising the SSMS environment and layout
• Registering servers in SSMS
• Manipulating and moving windows
2. Management SQL Instance and Database
SQL Instances
• What is an instance?
• Managing multiple instances
• Instance naming
• Instance properties
Managing Databases
• System and User databases
• Database properties
• Creating and deleting databases
• Renaming and moving databases
• Managing database files
Adding data/log files
Extending/growing database files
Shrinking database files
3. Backup and Recovery
• Recovery Models
• Backups
Full, Differential, and Transaction Log backups
• Restoring from backup
4. SQL Server Agent
• What is the SQL Server Agent?
• Managing Jobs
Creating and scheduling jobs
Using the Activity Monitor
• Automating tasks with Maintenance Plans
Day 2
1. Creating/Modifying Tables, Views, Constraints
• Creating / Renaming / Deleting Tables & Views
• Opening tables/views
• Managing constraints
Primary keys
Foreign keys
Check constraints
Not Null constraints
Defaults
2. Indexes, Query Optimiser and Execution Plans
• Creating and deleting indexes
• How indexes work
• Multi-column indexes vs multiple indexes
• How the query optimiser works
• Introduction to execution plans
+61 8 9432 1777 training@maxgeo.com maxgeo.com
3. Stored Procedures, User-Defined Functions, Triggers
• Creating/editing and calling stored procedures
• Using and managing user-defined functions
• Creating and editing triggers
• Comparison of procedures, functions, and triggers
4. The Query Window and the Query Designer
• Features of the query window
• Editing and executing queries
• Browsing and saving results
• Using the query designer
5. Security / Permissions – Logins, Users, Roles, Privileges
• Managing logins, users, and roles
• Relationships between logins, users, roles
• Instance-level, database-level and object-level privileges
• Assigning privileges
+61 8 9432 1777 training@maxgeo.com maxgeo.com
