Microsoft SQL Server Database Administration step by step
☑ Microsoft SQL Server Setup
☑ Installing Sample Databases
☑ Creating Database Objects
☑ Database Design
☑ Tracking database activities
☑ Creating stored procedures
☑ Database backup and recovery
☑ Monitoring Database Performance
☑ Database security and user accounts
☑ Querying Database with T-SQL
☑ Aggregating Data using aggregate functions
☑ Filtering and sorting records
☑ Extracting data from multiple tables
SQL Server is Microsoft’s relational database management software and uses a common set of tools to deploy and manage databases both on-premises and in the cloud.
Microsoft SQL Server Management Studio is a rich, integrated administrative client designed to meet the SQL Server and Azure SQL Database administrator’s server management requirements. In Management Studio, administrative tasks are accomplished using Object Explorer, which allows you to connect to any server in the SQL Server family and graphically browse its contents. A server can be an instance of the Database Engine, Analysis Services, Reporting Services, Integration Services or Azure SQL Database.
The tool components of Management Studio include Registered Servers, Object Explorer, Solution Explorer, Template Explorer, the Object Explorer Details page, and the document window.
Use Management Studio to:
- Register servers.
- Connect to an instance of the Database Engine, SSAS, SSRS, SSIS or Azure SQL Database.
- Configure server properties.
- Manage database and SSAS objects such as cubes, dimensions, and assemblies.
- Create objects, such as databases, tables, cubes, database users, and logins.
- Manage files and filegroups.
- Attach or detach databases.
- Launch scripting tools.
- Manage security.
- View system logs.
- Monitor current activity.
- Configure replication.
- Manage full-text indexes.
SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.
T-SQL, which stands for Transact-SQL and is sometimes referred to as TSQL, is an extension of the SQL language used primarily within Microsoft SQL Server. This means that it provides all the functionality of SQL but with some added extras.
English
Language
Microsoft SQL Server Setup
Introduction
What is SQL Server
What is a DBA
SQL Server Editions
Hardware | Software Requirements
Download SQL server
Install SQL Server
Install SSMS
SQL Server Configuration Manager
SQLCMD Utility
Schema
Connecting SSMS to SQL Server
Installing Sample Databases
Database Concepts
Install Adventureworks 2014
Install Adventureworks 2016
Install AdventureworksDW 2016
Install World Wide Importers Database
Install World Wide ImportersDW Database
Creating Database Objects
Create and configure a new database
Create a new table
Edit Table Structure
Manually Inserting Data into database
Import data from csv file
Create a table from a flat file
SQL Server Data Files
Database Table Design Properties
Creating an identity column
Primary keys
Setting default values
Check constraints
Unique Constraints
Foreign Keys
Creating relationships between tables
SQL Server Profiler – DBA Tool
What is SQL Server Profiler
How SQL Server Profiler can help DBA’s
SQL Server Profiler Architecture
SQL Server Profiler Terminology
Permissions required to access SQL Server Profiler
How to start SQL Server Profiler
Stimulating database activity
How to start a trace
How to load a saved trace
Exploring events selection
Creating a custom trace from scratch – part 1
Creating a custom trace from scratch – part 2
Creating a custom trace from scratch – part 3
Creating CRUD Stored Procedures
What is CRUD
What are stored procedures
Basic Application Stucture
Permission required to create stored procedures
Ways to create stored procedures
Creating CRUD Stored Procedures
Naming Convention for CRUD Stored Procedures
Stored Procedures for CREATE CRUD Operation
Stored Procedures for READ CRUD Operation
Stored Procedures for UPDATE CRUD Operation
Stored Procedures for DELETE CRUD Operation
Database Backup & Recovery
Enabling Log Shipping
Copy database
Full Database Backup
Differential Database Backup
Transactional Database Backup
Database Recovery
Lab Exercise 1
Lab Exercise 2
Monitoring SQL Server Performance
Introduction
Activity Monitor
Collecting data
Performance Monitor
Extended Events
Using Indexes
Database Security & user accounts
Creating a new user account
Resetting System Admin Account
Enforcing Authentication
Applying permission level access
Checking database integrity with audits
Querying Data with Transact-SQL
What is SQL
What is Transact-SQL ( T-SQL)
Creating database with T-SQL
Creating table with T-SQL
Inserting data with T-SQL
Querying data
Views
Updating Data
Backing up data with T-SQL
Deleting Data
Truncating Table
Dropping Table
Dropping Database
Restore database with T-SQL
Aggregating Data with T-SQL
Introduction
Max Aggregate Function
Min Aggregate Function
Sum Aggregate Function
Count Aggregate Function
AVG Aggregate Function
Grouping Data
Using Multiple Aggregate Functions
Filtering Data with T-SQL
Introduction
Filtering data with basic comparison
Filtering data with logical comparison
Filtering Data with string comparison filters
Filtering data with NULL Values
Filtering Data with string comparison
Sorting Data with T-SQL
Introduction
Using ORDER BY Clause
Sorting data in ascending order
Sorting data in descending order
Sorting data by multiple columns
Extracting Data with Table Joins using T-SQL
Introduction
Why Table Join
Full Outer Join
INNER Join
LEFT Outer Join
Right Outer Join