ArchiTech IT

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