SQL Server Database Administration Tutorial
Are you excited to start your journey toward becoming a database administrator? SQL Server is a powerful relational database management system, and having it on your resume is definitely a plus. This tutorial provides you all the essential concepts and practical skills you need to become a successful SQL Server Database Administrator (DBA). We’ll cover everything from installation to backups and security, and more!
If you want to take your SQL Server learning to a new level, look at the SQL Server course syllabus we provide, so you can dive into the subject in a structured manner.
Introduction to SQL Server and the DBA Role
A SQL Server Database Administrator (DBA) is the guardian of an organization’s data. You are responsible for ensuring the data is always available, secure, and performing optimally. Your responsibilities typically include:
- Installation and Configuration: Installing the SQL Server instance and configuring its various options.
- Security: Managing users, their permissions, and ensuring data is secure.
- Backups and Recovery: Creating, and implementing a yearly backup or recovery strategy to ensure data cannot be lost.
- Performance Tuning: Monitoring the database, its use, and optimizing options for it to run efficiently.
- High Availability and Disaster Recovery: Implementing solutions, like Always On Availability Groups to ensure you avoid downtime.
Explore: SQL Server DBA course online.
Getting Started: Installation and Basic Configuration
Before you can be a DBA, you need a database! So, we will get started by installing a free version of SQL Server (like SQL Server Express or Developer Edition) and the SQL Server Management Studio (SSMS), the main tool used to manage your databases.
Installation Steps
- Download the installer: Go to the Microsoft SQL Server downloads page and download the Developer or Express edition.
- Run the installer: Follow the on-screen prompts with the installer. You will want to choose Basic Installation for now.
- Install SSMS: After installing the SQL Server, the installer will give you the option to install SSMS. You will want to install this, as it allows you to interact with your database. (e.g., DESKTOP-ABC\SQLEXPRESS).
- Connect to your instance: Launch SSMS, and connect to your SQL Server Instance (you will use the server name you specified in the installation above).
When connected, you will see the Object Explorer, which is sort of like the control panel for all things SQL Server.
Recommended: Oracle DBA Course Online.
Creating and Managing Databases
A database is a set of data that is organized so data can easily be accessed and managed. A DBA will be constantly creating, modifying and deleting databases.
Creating your first Database
You can create a database using the graphical interface within SSMS or by using a T-SQL (Transact-SQL) script.
By T-SQL:
— This is a T-SQL comment
CREATE DATABASE MyFirstDatabase;
GO
- CREATE DATABASE: All this does is create a new database.
- MyFirstDatabase: The name for your new database.
- GO: Tells the system that you are done with a batch of T-SQL statements.
Working with Tables
Now that you have a database we can create tables to hold your data. A table consists of columns and rows.
To Create a table
SQL
USE MyFirstDatabase;
GO
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE
);
GO
- USE MyFirstDatabase: This tells the system that all subsequent statements will be executed against the MyFirstDatabase database.
- CREATE TABLE: The T-SQL command to create a new table.
- EmployeeID INT PRIMARY KEY: Creates a column called EmployeeID that has a data type of INT (integer) and is the primary key that uniquely identifies each row.
- VARCHAR(50): is a data type used for variable-length strings with a maximum length of 50 characters.
SQL Server Security: Logins, Users, and Permissions
Security is the top priority for any database administrator. You have to keep control of who accesses the database and their capabilities. SQL Server security is a two level security model consisting of Logins and Users.
- Logins: A login is a security principal at the server level. It provides a way for a person (or application) to connect to a SQL Server instance. You create logins from Windows accounts or you can create them from SQL Server’s own authentication.
- Users: A user is a security principal attached to one specific database. By mapping a login to a user, you can provide them access to that database.
Creating a SQL Server Login and User
Step 1: Create a Login
T-SQL
— Create a new SQL Server login
CREATE LOGIN MyNewLogin WITH PASSWORD = ‘MyStrongPassword123!’, CHECK_POLICY = ON;
GO
Step 2: Create a User and map it to the Login
T-SQL
SQL
USE MyFirstDatabase;
GO
CREATE USER MyNewUser FOR LOGIN MyNewLogin;
GO
Step 3: Grant Permissions
Now, let’s provide the user access to read data from the Employees table via the following T-SQL.
GRANT SELECT ON Employees TO MyNewUser;
GO
- GRANT: This keyword establishes a specific permission.
- SELECT: The permission to read data.
- ON Employees: This specifies the object (the Employees table) to grant permission on.
- TO MyNewUser: This specifies the user to assign the permission to.
Recommended: Oracle SQL Course Online.
Backups and Recovery: Your Safety Net
If there is one thing all Database Administrators should know it’s this: A DBA without a backup plan is a DBA in trouble. Backups protect you from data loss whether it’s from hardware failure or the loss of data due to human error – and yes – you are human and you will make mistakes, we all do. There are three kinds of backups:
The following:
- Full Backup: Fully backs up the entire database.
- Differential Backup: This backup includes all data that has changed since the last full backup.
- Transaction Log Backup: This backup captures the transaction log (records all transactions). This is important for point-in-time recovery.
Creating Backups:
SQL
— Take a full backup of MyFirstDatabase to a specified location
BACKUP DATABASE MyFirstDatabase
TO DISK = ‘C:\Backups\MyFirstDatabase_Full.bak’;
GO
- BACKUP DATABASE: Indicates that you are beginning to back up the database.
- TO DISK: Indicates that the backup location will be a file on disk.
- AND: Allows you to specify the full path.
- ‘C:\Backups\MyFirstDatabase_Full.bak’;: file name for the backup file.
Monitoring and Performance Tuning
Monitoring the health of the database is one of the key responsibilities of the DBA. To ensure optimal performance, DBAs need to keep track of certain metrics and make adjustments to improve performance.
- Performance Monitor (PerfMon): This is a Windows application that can be used to monitor performance metrics for things such as CPU consumption, disk I/O, and memory.
- Activity Monitor: This is a SSMS tool that provides a snapshot of the SQL Server instance’s state, including active processes, DAOs, recent queries and consumed resources.
- Dynamic Management Views (DMVs): These are special views that return server state information. These views are especially useful for troubleshooting performance problems.
Example DMV Query:
— This query helps identify the most expensive queries by CPU usage
SELECT TOP 10
total_worker_time / 1000 AS [Total CPU Time (ms)],
total_elapsed_time / 1000 AS [Total Elapsed Time (ms)],
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time DESC;
Common performance problems and their solutions:
- Absences of a necessary indexes: Slow running queries could point to a missing index. Indexes improve data updated time.
- Blocking: When one process is holding a lock on a resource and another is waiting on it, performance is bottlenecked.
- Non-efficient Queries (T-SQL): Poorly written T-SQL queries can be the leading cause of performance issues.
Explore: All Software Training Courses.
Conclusion
This SQL Server DBA tutorial for beginners has provided you with a certain level of exposure to the essentials of being a SQL Server DBA. We explored installation and database creation, and essential tasks such as; security, backup, and performance related tasks and responsibilities. While this is just the tip of the iceberg of your experience, with the knowledge you have acquired here you are on your way to becoming a SQL Server professional.
Are you ready to take the next step and become a fully certified expert? Our SQL Server DBA course in Chennai provides an in-depth, hands-on curriculum that will teach and provide exposure to the types of challenges you will face as a DBA and provide you with the skills you need to take the next step in your career.