Course information
SQL Server DBA: Database administrators need to handle all facets of managing Microsoft SQL Server ranging from installation and patching to creating databases to managing permissions that allow users to use the databases. However, more than anything, they are responsible for protecting the data and maintaining performance levels. They often act as internal advisors on how to use SQL Server effectively. It is a critical role because if a database is offline or when losing data, the business and its customers immediately feel the impact.
The tricky part about becoming a SQL database administrator is getting the ?rst job. No hiring manager wants to give a user with no track record the keys to the most valuable thing the company owns: the data. A single mistake by a database administrator can cause an outage or loss of data that can result in loss of revenue and even the failure of the company. Of course, any database administrator may and probably will make mistakes. However, the challenge for the hiring manager is that the ?rst time database administrator often makes mistakes because they did not understand the full impact of their actions. Many companies only have a single database administrator and cannot take the risk of hiring a junior database administrator because there is no one on staff with the time or skills to coach them.
Very few database administrators start out as database administrators. Many start out as developers and as they work with the database and almost all applications need at least one database. They ?nd that they migrate to the management side of things. Many start out as network or systems administrators who start working with the database by doing installs and patches, learning the basics and then gradually adding skills. Others begin as report writers or doing extract, transform, and load work. Regardless of their starting point, they show an affinity for data and databases as it comes naturally to
SQL Server Developer Topics / Course Contents
Introduction to RDBMS
File Management System
• Database Management System (DBMS) and Data Models
• Physical Data Models
• Logical Data Models
• Relational Data Model (RDBMS)
• Object-Relational Data Model (ORDBMS)
• Entity-Relationship (E-R) Model
Introduction to SQL Server
Advantages and Drawbacks SQL Server
• Connecting to Server
• Server Type
• Server Name
• Authentication Modes
• Sql Server Authentication Mode
• Windows Authentication Mode
• Login and Password
• Sql Server Management Studio and Tool explanation
Introduction To TSQL
• History and Features of TSQL
• Types 0f TSQL Commands
• Data Definition Language (DDL) – Create, Alter and Drop
• Data Manipulation Language (DML) – Insert, Delete, Update, Truncate
• Data Query Language (DQL) - Select
• Data Control Language (DCL) – Grant and Revoke
• Transaction Control Language (TCL) – Rollback, Savepoint
• Constrains (Not Null, Unique, Default, Check constraints, Primary Key and Referential Integrity or foreign key constraints
• Data Types In TSQL
Clauses
Where, Group by, Having and Order by
Scalar Functions
• Numeric Functions
• Date Functions
• Aggregate Functions
• Super Aggregates Over (partition by …) Clause
• Ranking Functions
• Common Table Expressions (CTE)
Inner Join
• Self Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
Single Row Sub Queries
• Multi Row Sub Queries
• Co-Related Sub Queries
• Clustered Index
• NonClustered Index
• Create, Alter and Drop Indexes
• Using Indexes
• Purpose of Views
• Creating, Altering and Dropping Indexes
• Simple and Complex View
• Encryption and Schema Binding Options in creating views
Transaction Management
• Introduction
• Begin Transaction
• Commit Transaction
• Rollback Transaction
• Save Transaction
Cursor
• Working with Cursors
• Types of Cursors
• Static, Dynamic and Keyset Cursors
• Local and Global Cursors
Stored procedure
• Creating, Altering, and Dropping
• Input and Output Parameters
User-Defined Functions
• Creating, Altering, and Dropping
• Types of User-Defined Functions
• Table Valued Functions
• Inline Table-Valued Functions
• Multi Statement Table Valued Functions
Trigger
• Purpose of Triggers
• Differences Between Stored Procedures and User-Defined Functions and Triggers
• Creating, Altering and Dropping Triggers
Exception Handling
• Implementing Exception Handling
Security
• Login Creation
• SQL Server Authenticated Login
• Windows Authenticated Login
• User Creation
• Granting Permissions
• Revoking Permissions
SQL SQL Server Administration (DBA)
Module 1: Introduction
Lessons
Introduction To The SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services
Module 2: Preparing Systems for SQL Server
Lessons
• Overview of SQL Server Architecture
• Planning Server Resource Requirements
• Pre-installation Testing for SQL Server
Module 3: Installing and Configuring SQL Server
Lessons
• Preparing to Install SQL Server
• Installing SQL Server
• Upgrading and Automating Installation
Module 4: Working With Databases
Lessons
• Overview of SQL Server Databases
• Working with Files and Filegroups
• Moving Database Files
Module 5: Understanding SQL Server Recovery Models
Lessons
• Backup Strategies
• Understanding SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy
Module 6: Backup of SQL Server Databases
Lessons
• Backing up Databases and Transaction Logs
• Managing Database Backups
• Working with Backup Options
Module 7: Restoring SQL Server Databases
Lessons
• Understanding the Restore Process
• Restoring Databases
• Working with Point-in-time recovery
• Restoring System Databases and Individual Files
Module 8: Importing And Exporting Data
Lessons
• Transferring Data To/From SQL Server
• Importing & Exporting Table Data
• Inserting Data in Bulk
Module 9: Authenticating And Authorizing Users
Lessons
• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers
Module 10: Assigning Server And Database Roles
Lessons
• Working with Server Roles
• Working with Fixed Database Roles
• Creating User-defined Database Roles
Module 11: Authorizing Users To Access Resources
Lessons
• Authorizing User Access to Objects
• Authorizing Users to Execute Code
• Configuring Permissions at the Schema Level
Module 12: Auditing SQL Server Environments
Lessons
• Options for Auditing Data Access in SQL
• Implementing SQL Server Audit
• Managing SQL Server Audit
Module 13: Automating SQL Server Management
Lessons
• Automating SQL Server Management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
Module 14: Configuring Security For SQL Server Agent
Lessons
• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Accounts
Module 15: Monitoring SQL Server With Alerts And Notifications
Lessons
• Configuration of Database Mail
• Monitoring SQL Server Errors
• Configuring Operators, Alerts and Notifications
Module 16: Performing Ongoing Database Maintenance
Lessons
• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance
Module 17: Tracing Access To SQL Server
Lessons
• Capturing Activity using SQL Server Profiler
• Improving Performance with the Database Engine Tuning Advisor
• Working with Tracing Options
Module 18: Monitoring SQL Server
Lessons
• Monitoring Activity
• Capturing and Managing Performance Data
• Analysing Collected Performance Data
Module 19: Managing Multiple Servers
Lessons
• Working with Multiple Servers
• Virtualizing SQL Server
• Deploying and Upgrading Data-Tier Applications
Module 20: Troubleshooting Common SQL Server Administrative Issues
Lessons
• SQL Server Troubleshooting Methodology
• Resolving Service-related Issues
• Resolving Concurrency Issues
• Resolving Login and Connectivity Issues
Course Information
- Course: SQL Server DBA
- Company: ArchiTech IT
- Course Content: Available
- Course Duration: 40-45 Days
- Session Recording: Lifetime Access