Course Overview
This five-day instructor-led course provides students who administer and maintain SQL Server databases with the knowledge and skills to administer a SQL server database infrastructure. Additional it will be of use to individuals who develop applications that deliver content from SQL Server databases.
Who's It For?
The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.
The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.
What You'll Learn
After completing this course, students will be able to:
- Authenticate and authorize users
- Assign server and database roles
- Authorize users to access resources
- Protect data with encryption and auditing
- Describe recovery models and backup strategies
- Backup SQL Server databases
- Restore SQL Server databases
- Automate database management
- Configure security for the SQL Server agent
- Manage alerts and notifications
- Managing SQL Server using PowerShell
- Trace access to SQL Server
- Monitor a SQL Server infrastructure
- Troubleshoot a SQL Server infrastructure
- Import and export data
Study Method
- VirtualVirtual Delivery - Live and interactive classroom-style learning conducted completely online
- BlendedBlended delivery - both online course content and partial face to face requirements
- In-classIn class delivery - predominately face to face course content conducted at a specific location
- OnlineOnline delivery - online course content with the exception of assessments and work placement
Duration and Study Load
- 5 days of virtual instructor-led training, 9 am-4 pm (approx. course duration)
Delivery
Virtual Instructor-led training
This course will be delivered virtually, with a live instructor taking you step by step through course material along with other students in a virtual environment using industry-leading video conferencing facilities.
Note: Students will need to have access to a webcam and headset with a microphone and audio capability
Entry Requirements
In addition to their professional experience, students who attend this training should already have the following technical knowledge:
- Basic knowledge of the Microsoft Windows operating system and its core functionality
- Working knowledge of Transact-SQL
- Working knowledge of relational databases
- Some experience with database design
Course Features
Control your learning experience with Microsoft Official Courses On-Demand!
Now you can build Microsoft technical expertise while balancing the demands of your schedule, with Microsoft Official Courses On-Demand (MOC On-Demand).
Subjects
Protection of data within your Microsoft SQL Server databases is essential and requires a working knowledge of the issues and SQL Server security features. This module describes SQL Server security models, logins, users, partially contained databases, and cross-server authorization.
Lessons
- Authenticating Connections to SQL Server
- Authorizing Logins to Connect to databases
- Authorization Across Servers
- Partially Contained Databases
Using roles simplifies the management of user permissions. With roles, you can control authenticated users’ access to system resources based on each user’s job function—rather than assigning permissions user-by-user, you can grant permissions to a role, then make users members of roles. Microsoft SQL Server includes support for security roles defined at server level and at database level.
Lessons
- Working with server roles
- Working with Fixed Database Roles
- Assigning User-Defined Database Roles
In the previous modules, you have seen how Microsoft SQL Server security is organized and how sets of permissions can be assigned at the server and database level by using fixed server roles, user-defined server roles, fixed database roles, and application roles. The final step in authorizing users to access SQL Server resources is the authorization of users and roles to access server and database objects. In this module, you will see how these object permissions are managed. In addition to access permissions on database objects, SQL Server provides the ability to determine which users are allowed to execute code, such as stored procedures and functions. In many cases, these permissions and the permissions on the database objects are best configured at the schema level rather than at the level of the individual object. Schema-based permission grants can simplify your security architecture. You will explore the granting of permissions at the schema level in the final lesson of this module.
Lessons
- Authorizing User Access to Objects
- Authorizing Users to Execute Code
- Configuring Permissions at the Schema Level
When configuring security for your Microsoft SQL Server systems, you should ensure that you meet any of your organization’s compliance requirements for data protection. Organizations often need to adhere to industry-specific compliance policies, which mandate auditing of all data access. To address this requirement, SQL Server provides a range of options for implementing auditing. Another common compliance requirement is the encryption of data to protect against unauthorized access in the event that access to the database files is compromised. SQL Server supports this requirement by providing transparent data encryption (TDE). To reduce the risk of information leakage by users with administrative access to a database, columns containing sensitive data—such as credit card numbers or national identity numbers—can be encrypted using the Always Encrypted feature. This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server Audit feature, and how to implement encryption.
Lessons
- Options for auditing data access in SQL Server
- Implementing SQL Server Audit
- Managing SQL Server Audit
- Protecting Data with Encryption
One of the most important aspects of a database administrator’s role is ensuring that organizational data is reliably backed up so that, if a failure occurs, you can recover the data. Even though the computing industry has known about the need for reliable backup strategies for decades—and discussed this at great length—unfortunate stories regarding data loss are still commonplace. A further problem is that, even when the strategies in place work as they were designed, the outcomes still regularly fail to meet an organization’s operational requirements. In this module, you will consider how to create a strategy that is aligned with organizational needs, based on the available backup models, and the role of the transaction logs in maintaining database consistency.
Lessons
- Understanding Backup Strategies
- SQL Server Transaction Logs
- Planning Backup Strategies
About DDLS
DDLS is Australia’s largest provider of corporate IT and process training, with the largest portfolio of strategic partners and courses in Australia. We partner with world-class companies to help organisations and individuals in the IT industry remain up-to-date with new processes, technology and platforms to reduce risk and enable efficient business practices. We have convenient locations in almost every capital in Australia as well as the Philippines, flexible delivery modalities, industry-accredited trainers, and state-of-the-art course material and labs to produce the highest quality learning outcomes for our clients.
DDLS promotes a balanced approach to training with a focus on the key areas of Technology, Process and People. We provide extensive training options tailored to your organisation’s needs – from vendor-certified courses to customised training, including bespoke in-house developed courses.