Protecting the data in your Microsoft SQL Server databases is crucial, which calls for understanding SQL Server security features and related problems. This module covers cross-server authorisation, partially enclosed databases, users, logins, and SQL Server security models.

Lessons

• Authenticating Connections to SQL Server
• Authorising Logins to Connect to databases
• Authorisation Across Servers
• Partially Contained Databases

Lab: Authenticating Users

• Create Logins
• Create Database Users
• Correct Application Login Issues
• Configure Security for Restored Databases

After completing this module, you will be able to

• SQL Server basic concepts.
• SQL Server connection authentication.
• User login authorisation to databases.
• Partially contained databases.
• Authorisation across servers.

Managing user permissions is made more accessible by the usage of roles. Rather than granting rights to individual users, roles allow you to limit access to system resources for authenticated users based on their job functions. Users may be added to roles and have their permissions automatically assigned. Microsoft SQL Server supports security roles established at the server and database levels.

Lessons

• Working with server roles
• Working with Fixed Database Roles
• Assigning User-Defined Database Roles

Lab: Assigning server and database roles

• Assigning Server Roles
• Assigning Fixed Database Roles
• Assigning User-Defined Database Roles
• Verifying Security

You have seen how Microsoft SQL Server security is set up in the previous courses, including how user-defined server roles, fixed database roles, application roles, and fixed server roles may be used to provide rights at the server and database level. Authorising users and roles to access server and database objects completes granting users access to SQL Server resources. You will see how various object permissions are handled in this module. SQL Server can ascertain which users can execute code, including stored procedures and functions, and access rights on database objects. It is often preferable to establish these permissions and the permissions on the database objects at the schema level as opposed to the level of the individual object. Permission granting based on schemas helps streamline your security architecture. In the last lesson on this subject, you will investigate permissions at the schema level.

Lessons

• Authorising User Access to Objects
• Authorising Users to Execute Code
• Configuring Permissions at the Schema Level

Lab: Authorizing users to access resources

• Granting, Denying, and Revoking Permissions on Objects
• Granting EXECUTE Permissions on Code
• Granting Permissions at the Schema Level

After completing this module, you will be able to

• Authorise user access to objects.
• Authorise users to execute code.
• Configure permissions at the schema level.

As you configure security for your Microsoft SQL Server servers, be sure to comply with any data protection compliance standards set out by your organisation. Industry-specific compliance regulations that require audits of every data access are frequently enforced on organisations. SQL Server offers a variety of auditing implementation choices to meet this requirement. Data encryption serves as an additional common compliance need, safeguarding against unauthorised access if database file access is compromised. SQL Server offers transparent data encryption (TDE) to help meet this requirement. Using the Always Encrypted feature, columns holding sensitive data, such as credit card numbers or national identity numbers, can be encrypted to lower the risk of information leakage by individuals with administrative access to a database. This module explains how to utilise and administer the SQL Server Audit feature and add auditing settings in SQL Server.

Lessons

• Options for auditing data access in SQL Server
• Implementing SQL Server Audit
• Managing SQL Server Audit
• Protecting Data with Encryption

Lab: Using Auditing and Encryption

• Working with SQL Server Audit
• Encrypt a Column as Always Encrypted
• Encrypt a Database using TDE

After completing this module, you will be able to

• Describe the options for auditing data access.
• Implement SQL Server Audit.
• Manage SQL Server Audit.
• Implement encryption

A database administrator’s primary responsibility is to ensure that organisational data is consistently backed up to be restored in the event of a disaster. Even though the computer industry has long acknowledged and extensively addressed the necessity of dependable backup plans, tragic tales of data loss continue to occur. An additional issue is that, even in cases where the techniques perform as intended, the results frequently fall short of what an organisation needs to function. This session will cover creating an organisational needs-based approach based on various backup models and the function of transaction logs in preserving database consistency.

Lessons

• Understanding Backup Strategies
• SQL Server Transaction Logs
• Planning Backup Strategies

Lab: Understanding SQL Server recovery models

• Plan a Backup Strategy
• Configure Database Recovery Models

After completing this module, you will be able to

• Describe various backup strategies.
• Describe how database transaction logs function.
• Plan SQL Server backup strategies.

In the previous module, you studied how to organise a backup strategy for an SQL Server system. Now, you can learn how to create backups for SQL Server databases, including differential and complete backups, transaction logs and partial backups. This session will teach you how to use different backup plans.

Lessons

• Backing Up Databases and Transaction Logs
• Managing Database Backups
• Advanced Database Options

Lab: Backing Up Databases

• Backing Up Databases
• Performing Database, Differential, and Transaction Log Backups
• Performing a Partial Backup

After completing this module, you will be able to

• Perform backups of SQL Server databases and transaction logs.
• Manage database backups.

Describe advanced backup options.

In the previous module, you were taught how to generate backups of Microsoft SQL Server 2016 databases. It is crucial that you can efficiently recover the many backup kinds that may be included in a backup plan. It is expected to restore a database under pressure. But, you have to ensure you have a well-thought-out plan to move forward and effectively restore the database to the necessary state. It is possible to prevent making matters worse by having a solid strategy and knowledge of the repair procedure.

System failure is connected to certain database restorations. In these situations, you’ll want to get the system back to where it was before the breakdown as much as feasible. However, human error can cause certain failures, in which case you may want to restore the system to a time before the problem. You may accomplish this using SQL Server 2016’s point-in-time recovery tools. User databases are more susceptible to system failures than system databases since they are usually significantly more significant.

Nonetheless, system databases are susceptible to malfunctions thus, extra caution needs to be used when retrieving them. You cannot use the same procedure for all system databases, thus, you must specifically grasp how to restore each system database. You will learn how to apply point-in-time recovery and restore user and system databases in this module.

Lessons

• Understanding the Restore Process
• Restoring Databases
• Advanced Restore Scenarios
• Point-in-Time Recovery

Lab: Restoring SQL Server Databases

• Restoring a Database Backup
• Restring Database, Differential, and Transaction Log Backups
• Performing a Piecemeal Restore

After completing this module, you will be able to

• Explain the restore process.
• Restore databases.
• Perform advanced restore operations.
• Perform a point-in-time recovery.

Compared to competing database engines, Microsoft SQL Server’s tools simplify management. However, it is expected to repeat a task several times, even if it is simple. Robust database administrators acquire the ability to mechanise recurring duties. This can lessen the likelihood of an administrator forgetting to complete a task by the deadline. Perhaps even more crucially, task automation guarantees that each time it is carried out, it is done so consistently. This lesson explains how to automate tasks, set up security contexts for tasks, and set up multiserver jobs using SQL Server Agent.

Lessons

• Automating SQL Server management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
• Multi-server Management

Lab: Automating SQL Server Management

• Create a SQL Server Agent Job
• Test a Job
• Schedule a Job
• Configure Master and Target Servers

After completing this module, you will be able to

• Describe methods for automating SQL Server Management.
• Configure jobs, job step types, and schedules.
• Manage SQL Server Agent jobs.
• Configure master and target servers.

This course’s other modules have illustrated the necessity of limiting user access and adhering to the “least privilege” philosophy. According to this, users only possess the permissions necessary to carry out their jobs. The process of giving SQL Server Agent permissions follows the same reasoning. While it is simple to set up the SQL Server Agent service account as an administrator account and run all jobs in its context, doing so would lead to a weak security setup. Comprehending setting up a limited privilege security environment for SQL Server Agent operations is crucial.

Lessons

• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Accounts

Lab: Configuring Security for SQL Server Agent

• Analysing Problems in SQL Server Agent
• Configuring a Credential
• Configuring a Proxy Account

After completing this module, you will be able to

• Explain SQL Server Agent security.
• Configure credentials.
• Configure proxy accounts.

As they arise, being informed of issues and developments in the Microsoft SQL Server is a crucial component of proactive management. SQL Server logs an abundance of issue-related information. Using alerts and notifications, you may set it up to notify you automatically when these problems arise. Email is the most popular method for providing SQL Server database administrators with information about noteworthy occurrences. This lesson covers setting up alerts, notifications, and Database Mail for an SQL Server instance. It also covers setting up alerts for Microsoft Azure SQL Database.

Lessons

• Monitoring SQL Server Errors
• Configuring Database Mail
• Operators, Alerts, and Notifications
• Alerts in Azure SQL Database

• Configuring Database Mail
• Configuring Operators
• Configuring Alerts and Notifications
• Testing Alerts and Notifications

After completing this module, you will be able to

• Monitor SQL Server errors.
• Configure database mail.
• Configure operators, alerts, and notifications.
• Work with alerts in Azure SQL Database.

The usage of Windows PowerShell with Microsoft SQL Server is examined in this module. Companies need to continuously enhance the reliability and efficiency of managing their IT infrastructure; you may do this by writing scripts in PowerShell that automate repetitive operations. PowerShell scripts may save your company time and money by being tested and applied to several systems.

Lessons

• Getting Started with Windows PowerShell
• Configure SQL Server using PowerShell
• Administer and Maintain SQL Server with PowerShell
• Managing Azure SQL Databases using PowerShell

Lab: Using PowerShell to Manage SQL Server

• Getting Started with PowerShell
• Using PowerShell to Change SQL Server Settings

Analysing performance metrics is an excellent technique for evaluating a database solution’s effectiveness. To diagnose issues and find solutions to optimise workload performance, you may occasionally need to conduct a more thorough investigation of the activities within a Microsoft SQL Server instance. Integrated inside the Microsoft SQL Server Database Engine, SQL Server Extended Events is a lightweight, adaptable event-handling framework. The Extended Events architecture principles, debugging techniques, and usage scenarios are the main topics of this subject.

Lessons

• Extended Events Core Concepts
• Working with Extended Events

Lab: Extended Events

• Using the System_Health Extended Events Session
• Tracking Page Splits Using Extended Events

After completing this module, you will be able to

• Describe Extended Event’s core concepts.
• Create and query Extended Events sessions.

Without administrator intervention, the Microsoft SQL Server Database Engine may operate for extended periods. However, if you monitor the database server’s behaviour regularly, you may address such problems before they become serious. You may keep track of past activity information and monitor ongoing activity using some tools that SQL Server offers. Each tool has a function, and you must learn how to utilise it. The output volume that monitoring tools may provide can quickly overwhelm you. Therefore, you need also learn how to analyse the data of these instruments.

Lessons

• Monitoring activity
• Capturing and Managing Performance Data
• Analysing Collected Performance Data
• SQL Server Utility

Lab: Monitoring SQL Server

After completing this module, you will be able to

• Monitor current activity.
• Capture and manage performance data.
• Analyse collected performance data.
• Configure SQL Server Utility.

When problems emerge, database administrators using Microsoft SQL Server should take on the crucial role of troubleshooter, especially if users of business-critical applications that depend on SQL Server databases cannot function. When dealing with SQL Server systems, it is critical to have a sound approach for addressing difficulties in general and to be knowledgeable about the most frequent problems that may occur.

Lessons

• A Trouble Shooting Methodology for SQL Server
• Resolving Service-Related Issues
• Resolving Connectivity and Log-in issues

Lab: Troubleshooting Common Issues

• Troubleshoot and Resolve a SQL Login Issue
• Troubleshoot and Resolve a Service Issue
• Troubleshoot and Resolve a Windows Login Issue
• Troubleshoot and Resolve a Job Execution Issue
• Troubleshoot and Resolve a Performance Issue

After completing this module, you will be able to

• Describe a troubleshooting methodology for SQL Server.
• Resolve service-related issues.
• Resolve login and connectivity issues.

Although users executing application applications enter a lot of data directly into Microsoft SQL Server systems, there are times when data from other locations has to be moved to and from SQL Server. There are tools in SQL Server that you may use to import and export data. These auxiliary tools are not part of the database engine; examples are SQL Server Integration Services and the BCP (Bulk Copy Program) utility. The database engine also implements other utilities, such as the OPENROWSET function and the BULK INSERT command. Data-tier applications that combine all the tables, views, and instance objects connected to a user database into a single deployment unit may also be made using SQL Server. In this section, you will examine these methods and tools to learn how to import and export data to and from SQL Server.

Lessons

• Transferring Data to and from SQL Server
• Importing and Exporting Table Data
• Using BCP and BULK INSERT to Import Data
• Deploying and Upgrading Data-Tier Application

Lab: Importing and Exporting Data

• Import and Excel Data Using the Import Wizard
• Import a Delimited Text File Using BCP
• Import a Delimited Text File using BULK INSERT
• Create and Test an SSIS Package to Extract Data
• Deploy a Data-Tier Application

After completing this module, you will be able to

• Describe tools and techniques for transferring data.
• Import and export table data.
• Use data-tier applications to import and export database applications.