Advertisements
Microsoft SQL server training course topics:
- Overview of IT environment with daily DBA activity and health check
- A brief of history (RDBMS & DBMS)
- SQL server internal architecture
- SQL server overview (Editions)
- SQL Installation overview (System DBs)
- Review of new features
- Transact -SQL overview
- Normalization
TABLES AND CONSTRAINTS
- Standard data types
- Tables and columns in SQL server
- Objects, Tables and columns in T-SQL
- Modifying tables and columns
- Database diagram architecture
- Constrains
TRANSACT SQL QUIERES
- Querying SQL server tables
- SQL and transact SQL
- Using the select statement
- Transact SQL built-in function
- Summary queries
- Sub queries
MERGING DATA WITH JOINS&UNIONS
- Working with inner joins
- Using left outer joins
- Multiplying data with cross joins
- Referencing self –joins
- Working with complex joins
- Nested loop join
- Merge join
- Hash join
- Join hints in SQL queries
AGGREGRATING DATA
- Using aggregate function
- Grouping a data set
- Nesting aggregations
- Filtering groups &adding grand totals
- MERGE statement
VIEWS
- Uses of views
- Limitation of views
- Calculated columns
- Creating views
- Overview of indexed views
INDEXES
- Overview of indexes
- Clustered versus non-clustered indexed
- Filtered index
- Creating indexed
- Using SHOW PLAN to see if the index is being used
- Using composite indexes to cover queries
- Duplicate keys and rows and how to solve problem
- The distribution page and update statistics
- Understanding the index statistics steps
TRANSACTIONS
- How data is changed in SQL server
- Manipulating data within transactions
- Transactions processing commands
- Transaction isolation levels
- Locking issues
- Using sp- lock, Whoisactive
- Table hints(locks)
PROGRAMING BASISCS
- Variables
- Flow control statements
- Error handling
- Using case expressions
- Working with nulls& coalesce
- Working with dates and times
- Parsing strings
- Converting data
USER DEFINEDD FUNCTIONS
- Scalar function
- Table function
- Appropriate usage of function
STORED PROCEDURES
- Creating stored procedures
- Passing parameters
- Returning values and updating parameters
- Compilation problems and methods
- Temporary procedures and auto start procedures
TRIGGERS
- Basic trigger assumptions and syntax
- Improving trigger performance
- Checking the number of rows affected
- Nested triggers
- Ensuring integrity with triggers
MONITORING SQL SERVER
- Viewing current activity
- Using DDL triggers
- Using event notifications
AUTOMATING ADMINISTRATIVE TASKS
- Automating administrative tasks in SQL server
- Configuring the SQL server agent
- Creating jobs and operations
- Creating alerts
- Managing multiple servers
- Managing SQL server agent security
Dynamic Management Views (DMVs & DMFs)
- Understanding DMVs and DMFs
- Use of DMVs and DMFs
INSTALLING AND CONFIGURATION SQL SERVER 2005
- Preparing to install SQL server
- Installing SQL server
- Managing a SQL server installation
DATA STORAGE & MANAGEMENT
- How is data organized in SQL server?
- Creating a database
- Using file groups
- Shrinking database
MANAGING
- Server roles
- Data base roles
- User mappings
LOGINS AND USERS
- Authentication and authorization
IMPELEMENTING DISASTER RECOVERY STRATEG
- Implementing a backup strategy
- Implementing a recovery model strategy
- Plan a backup strategy
- Backup user database
- Restore user database
- Backup and restore system databases
- Restoring database
- Rebuild the master database
MAINTAINING HIGH AVAIABILITY
- Introduction to high availability
- Implementing Always ON availability groups
- Implementing server clustering
- Implementing database mirroring
- Implementing log shipping
INTRODUCTION TO REPLICATION
- Overview of replication
- Replication scenarios
IMPLEMENTING REPLICATION
- Creating a publication
- Creating a subscription
- Describe replication and its component
- Describe common replication scenarios
LOG SHIPPING AND DATABASE MIRRORING
- Configuring transaction log shipping
- Understanding database mirroring requirement
- Configuring database mirroring
PERFORMANCE AND SQL SERVER
- Query tuning methods
- Execution plan reading
- Choosing and Building indexes online
- SQL statistics update
- Transact SQL improvements
- Monitoring resource consumption
- Establishing baseline metrics
- SQL server management studio
- SQL server configuration manager
- Database engine tuning advisor
- SQL server profiler
- Availability and scalability
DATABASE DESIGN FOR PERFORMANCE
- Introducing logical Database design for performance
- Introducing physical Database design
- Data volume analysis
- Transaction analysis
- Hardware environment consideration
SQL SERVER AND WINDOWS
- How SQL server uses CPU
- Priority
- Use of symmetric multiprocessing system
- Thread use
- Query parallelism
- Investigation CPU bottlenecks
- Solving problems with CPU
- SQL server and memory
- An overview of windows virtual memory management
- How SQL server uses memory
- Configuring memory for SQL server
- Investigation memory bottlenecks
- SQL server and disk I/O
- An overview of the data cache
- Keeping tables and indexes in cache
- Investigation disk I/O bottlenecks
ARCHITECTURAL PERFORMANCE OPTIONS AND CHOICES
- Storage Raid array
MONITORING PERFORMANCE
- System stored procedures
- System monitor, performance logs , and alerts
- Client statics
- The SQL server profiler
- Database engine tuning advisor
SQL SERVER SSIS & SSRS walk through.
