SQL Server 2022 Database Administration Essential Training

1. Pull the SQL Server 2022 (16.x) Linux container image from the Microsoft Container Registry.

docker pull mcr.microsoft.com/mssql/server:2022-latest

2. To run the Linux container image with Docker, you can use the following command from a bash shell or elevated PowerShell command prompt.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=abcdqwertf1124" -p 1432:1433 --name sql1 --hostname sql1  mcr.microsoft.com/mssql/server:2022-latest

Note: When connecting to the docker environment is "localhost, 1432". The comma must be used to separate the localhost from the port.

Fixed roles

The fixed server roles are applied serverwide, and there are several predefined server roles:

• SysAdmin: Any member can perform any action on the server.

• ServerAdmin: Any member can set configuration options on the server.

• SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.

• Security Admin: Any member can manage server security.

• ProcessAdmin: Any member can kill processes running on SQL Server.

• DbCreator: Any member can create, alter, drop, and restore databases.

• DiskAdmin: Any member can manage SQL Server disk files.

• BulkAdmin: Any member can run the bulk insert command.

Predefined database roles

You may need to create your own, but you have access to several predefined database roles:

• db_owner: Members have full access.

• db_accessadmin: Members can manage Windows groups and SQL Server logins.

• db_datareader: Members can read all data.

• db_datawriter: Members can add, delete, or modify data in the tables.

• db_ddladmin: Members can run dynamic-link library (DLL) statements.

• db_securityadmin: Members can modify role membership and manage permissions.

• db_bckupoperator: Members can back up the database.

• db_denydatareader: Members can’t view data within the database.

• db_denydatawriter: Members can’t change or delete data in tables or views.

SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database.

SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday. If the backup encounters a problem, SQL Server Agent can record the event and notify you.

DMVs are virtual tables that expose server state information in a structured manner, allowing database administrators to monitor and troubleshoot SQL Server instances. The lecture will cover the structure and usage of DMVs, and how they can be leveraged to gain insight into server health, performance, and resource utilization.

The following DBCC commands operate on an internal read-only database snapshot that the Database Engine creates. The snapshot prevents blocking and concurrency problems when these commands are executed. For more information, see Database Snapshots (SQL Server).

• DBCC CHECKALLOC

• DBCC CHECKCATALOG

• DBCC CHECKDB

• DBCC CHECKFILEGROUP

• DBCC CHECKTABLE

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

SQL Server Activity Monitor is a lecture that focuses on exploring the capabilities of SQL Server Activity Monitor as a tool for monitoring and troubleshooting SQL Server instances.

The lecture will cover the various features of SQL Server Activity Monitor, including the ability to view real-time performance metrics, active processes, and resource usage. Attendees will learn how to use Activity Monitor to identify and diagnose performance bottlenecks, such as long-running queries or resource-intensive processes, and take corrective action.

In this lecture, we will cover how to effectively manage user permissions in SQL Server, including granting and revoking database access with expiration dates. We'll explore creating a central UserPermissions table to track user permissions, and implementing stored procedures for granting access (GrantDatabaseLoginAccess) and revoking expired permissions (RevokeExpiredPermissions).