SQL Server 2019 Administration
Introduction to SQL Server 2019 Administration
Overview of SQL Server 2019 features and capabilities
Understanding SQL Server architecture
Installation and configuration of SQL Server 2019
Overview of SQL Server 2019 features and capabilities: This module
provides an overview of the features and capabilities of SQL Server 2019.
Topics covered may include the following:
New features in SQL Server 2019, such as Big Data Clusters and
improved security features
SQL Server 2019 licensing and pricing models
Comparison of SQL Server 2019 editions and versions
Reference Link: https://docs.microsoft.com/en-us/sql/sql-server/what-s-
new-in-sql-server-ver15?view=sql-server-ver15
Understanding SQL Server architecture: This module covers the basics of
SQL Server architecture, including the following topics:
SQL Server components, such as the Database Engine, Analysis
Services, Integration Services, and Reporting Services
The SQL Server relational engine and the SQL Server storage engine
Understanding the role of SQL Server instances and databases
Reference Link: https://docs.microsoft.com/en-us/sql/sql-server/what-is-
sql-server?view=sql-server-ver15
Installation and configuration of SQL Server 2019: This module covers the
steps involved in installing and configuring SQL Server 2019, including the
following topics:
Prerequisites for installing SQL Server 2019, such as hardware and
software requirements
Installation options for SQL Server 2019, such as stand-alone
installations and cluster installations
Post-installation configuration of SQL Server 2019, such as
configuring network protocols and enabling features
Reference Link:
https://docs.microsoft.com/en-us/sql/sql-server/install/install-sql-server?
view=sql-server-ver15
Overall, this module provides students with a foundation for
understanding SQL Server 2019 and how to install and configure it. It's an
essential first step for any SQL Server 2019 administrator.
Managing SQL Server Security
Understanding SQL Server security concepts and principles
Authentication and authorization mechanisms in SQL Server
2019
Best practices for securing SQL Server 2019
When it comes to managing SQL Server security, there are a few
key concepts and principles to keep in mind.
First, it's important to understand that SQL Server security
involves both authentication and authorization. Authentication is
the process of verifying the identity of a user or client who is
attempting to connect to SQL Server. SQL Server supports several
authentication mechanisms, including Windows authentication
and SQL Server authentication. Windows authentication relies on
the security features of the Windows operating system, while SQL
Server authentication involves validating a user's login and
password against a SQL Server login database.
Authorization, on the other hand, involves determining what
actions a user or client is allowed to perform within SQL Server.
SQL Server uses a role-based security model, where permissions
are granted to roles and roles are granted to users or groups of
users. It's important to carefully manage permissions and roles in
order to ensure that users are only able to perform the actions
that they need to perform and that no one has more privileges
than necessary.
In addition to understanding authentication and authorization
mechanisms, there are several best practices that can help to
secure SQL Server. These include:
Regularly applying security updates and patches to SQL
Server and the underlying operating system.
Implementing strong passwords and password policies.
Using encryption to protect sensitive data both in transit and
at rest.
Auditing and monitoring SQL Server activity to detect and
respond to security threats.
Limiting the use of the sa account and other highly
privileged accounts.
For more information on SQL Server security, I recommend
checking out the following resources from Microsoft:
"SQL Server security concepts"
(https://docs.microsoft.com/en-us/sql/relational-databases/se
curity/sql-server-security-concepts?view=sql-server-ver15)
"Authentication modes"
(https://docs.microsoft.com/en-us/sql/relational-databases/se
curity/authentication-modes?view=sql-server-ver15)
"Authorization in SQL Server"
(https://docs.microsoft.com/en-us/sql/relational-databases/se
curity/authentication-modes?view=sql-server-ver15)
"Security considerations for a SQL Server installation"
(https://docs.microsoft.com/en-us/sql/database-engine/install
-windows/security-considerations-for-a-sql-server-
installation?view=sql-server-ver15)
Database Encryption Concepts and Techniques
Understanding database encryption concepts
Implementing Transparent Data Encryption (TDE) in SQL Server
2019
Implementing Always Encrypted in SQL Server 2019
Explanation: This section covers the fundamental concepts of database
encryption and how encryption can be implemented in SQL Server 2019 to
secure sensitive data. This includes an understanding of encryption
algorithms and keys, as well as various encryption techniques that can be
used to secure data at rest and in transit. This section also covers how to
implement Transparent Data Encryption (TDE) in SQL Server 2019, which
encrypts the data stored in the database and protects it from
unauthorized access. Additionally, this section covers how to implement
Always Encrypted, which allows for the encryption of specific columns in a
database, thus protecting sensitive data even when the database is
compromised.
Reference links:
Overview of Database Encryption: https://docs.microsoft.com/en-
us/sql/relational-databases/security/encryption/overview-of-
database-encryption?view=sql-server-ver15
Implementing Transparent Data Encryption (TDE):
https://docs.microsoft.com/en-us/sql/relational-databases/security/
encryption/transparent-data-encryption?view=sql-server-ver15
Implementing Always Encrypted:
https://docs.microsoft.com/en-us/sql/relational-databases/security/
encryption/always-encrypted-database-engine?view=sql-server-
ver15
Overall, adding the topic of database encryption concepts and techniques
to the SQL Server 2019 Administration training will provide learners with
the essential knowledge needed to secure sensitive data and build a
robust and secure SQL Server environment.
SQL Server Database Backup and Recovery
SQL Server database backup and recovery is an essential task for
database administrators to ensure that data is protected and available in
the event of a disaster or data loss. The following are the key concepts
that are involved in SQL Server database backup and recovery:
3.1 Understanding database backup and recovery concepts
Database backup is the process of creating a copy of a database and its
associated objects to protect the data against accidental or deliberate
loss. The backup can be used to restore the database to a previous state
in case of data loss or corruption. Recovery is the process of restoring a
database from a backup to bring it back to a consistent state. SQL Server
provides several types of database backups, such as full, differential, and
transaction log backups.
3.2 Creating and managing database backups
To create a database backup in SQL Server, you can use the Transact-SQL
BACKUP statement, SQL Server Management Studio, or PowerShell. A full
database backup backs up the entire database, while a differential backup
backs up the changes since the last full backup, and a transaction log
backup backs up the transaction log. SQL Server also provides backup
compression to reduce the size of backup files.
You can also schedule backups using SQL Server Agent jobs, and
configure backup retention policies to manage backup files. SQL Server
also supports backup to Azure Blob storage for offsite backup storage and
disaster recovery.
For more information on creating and managing backups in SQL Server,
see the official Microsoft documentation:
SQL Server backup and restore
(https://docs.microsoft.com/en-us/sql/relational-databases/backup-
restore/sql-server-backup-and-restore?view=sql-server-ver15)
Back Up and Restore of SQL Server Databases
(https://docs.microsoft.com/en-us/sql/relational-databases/backup-
restore/back-up-and-restore-of-sql-server-databases?view=sql-
server-ver15)
3.3 Performing database restores
To restore a database in SQL Server, you can use the Transact-SQL
RESTORE statement, SQL Server Management Studio, or PowerShell. The
restore process depends on the backup type and the recovery model of
the database.
A full database restore replaces the existing database with the backup,
while a differential restore applies the differential backup to the last full
backup to bring the database up to the point of failure. A transaction log
restore applies the transaction log backups to bring the database to a
specific point in time.
SQL Server also supports point-in-time recovery, where you can restore
the database to a specific transaction log backup to recover the database
up to a specific point in time. SQL Server also provides restore validation
to verify the integrity of the backup files before restoring.
For more information on performing database restores in SQL Server, see
the official Microsoft documentation:
SQL Server backup and restore
(https://docs.microsoft.com/en-us/sql/relational-databases/backup-
restore/sql-server-backup-and-restore?view=sql-server-ver15)
Back Up and Restore of SQL Server Databases
(https://docs.microsoft.com/en-us/sql/relational-databases/backup-
restore/back-up-and-restore-of-sql-server-databases?view=sql-
server-ver15)
