SQL Administrator Database Certification

Prerequisites for Installing Microsoft SQL Server

• Outline the Business Scenario Case Study
• Identifying minimum hardware requirements
• Understanding the Processor and Memory Prerequisites
• Understanding the Disk Space Prerequisites
• Identifying Supported Operating Systems
• Identifying Software Requirements
• back to top

Selecting Microsoft SQL Server Editions

• Explain SQL Server services
• Understand the types of SQL Server services
  • Service Broker Service
  • SQL Server Integration Service (SSIS)
  • SQL Server Reporting Service (SSRS)
  • SQL Server Analysis Service (SSAS)
• Identify the need for different SQL Server editions
• List the different editions of Microsoft SQL Server
• Identify the differences between the various SQL Server editions
• Test Preparation Questions
• Audio Narration
• Print out course in Manual format
• Receive a SQLSteps Certificate of Completion
• Money Back Guarantee
• back to top

Installing Microsoft SQL Server

• Create Service Accounts
• Understand the SQL Server Configuration Manager
• Understand SQL Server Instances
• Set Authentication Modes
• Install and Configure a SQL Server Instance
• Install a Sample Database
• Configure Database Mail
• back to top

Understanding SQL Server Files

• Understand types of SQL Server files
• Understand Filegroups
• Work with Filegroups
• Understand Transaction logs
• Manage Transaction logs
• Understand the FILESTREAM feature
• Understand the tempdb database
• back to top

Managing the Database

• Describe the available database options
• Discuss the various recovery models
• Set the recovery model
• Automate actions in a database
• Understand change tracking
• Control database access
• Understand query parameterization
• Understand collation sequences
• Ensure database integrity
• back to top

Managing Tables

• Understanding Database Schemas
• Understand Data Types; Numeric, Character, Date and time, Binary, XML and Spatial data types
• Understand properties of table columns
• Understand computed columns
• Compress rows and pages to reduce disk storage space
• Create tables
• Understand table constraints
• back to top

Working with SQL Server Indexes

• Understand the Index Structure
• Discuss different SQL Server Indexes; Clustered, Nonclustered, Unique, Index with included columns, Filtered, XML, Spatial and Full text Indexes
• Work with and Implement Indexes
• Understand what is Index fragmentation
• Manage Indexes
• Work with Full Text Indexes
• back to top

Partitioning Data

• Understand Partitioning
• Implement Partition functions
• Implement Partition schemes
• Create Partitioned tables
• Create Partitioned indexes
• Manage Partitions
• back to top

Transferring Data

• Identify data transfer tools for Importing and Exporting data
• Understand and use the Bulk Copy Program (BCP)
• Understand and use the BULK INSERT command
• Understand and use the SQL Server Import and Export Wizard
• back to top

Managing Security in SQL Server

• Understand Policy-Based Management
• Understand the terms associated with Policy-Based Management
• Describe the Policy-Based Management process
• Identify how Policies are evaluated
• Create your own Policy
• Manage policies across multiple instances of SQL Server
• Import and export policies across database instances
• back to top

Implementing Layered Security

• Understand what are Endpoints
• Describe Endpoint Configuration for Database Mirroring and Service Broker
• Create an Endpoint for Database Mirroring and Service Broker
• Understand Surface area configuration
• Manage Protocols, Connections and Startup Options
• Work with Principals
• Describe Certificates and Asymmetric Keys
• Manage Permissions
• Audit SQL Server instances
• Encrypt data in SQL Server
• back to top

Identifying Disaster Recovery Options

• Understand database backups
• Understand transaction log backups
• Manage corrupt pages
• Understand the maintenance plan
• Validate backups
• Restore database backups
• Restore corrupt pages
• Restore databases with media errors
• Restore transaction Log Backups
• Work with database snapshots
• back to top

Automating Tasks with SQL Server Agent

• Understand the SQL Server agent
• Manage SQL Server Agent Jobs
• Understand operators
• Define SQL Server alerts
• Understand what are performance condition alerts
• Understand Windows Management Instrumentation (WMI) event alerts
• back to top

Optimizing Performance

• Understand the Database Engine Tuning Advisor
• Work with Database Engine Tuning Advisor
• Understand the process of workload analysis
• Set the Database Engine Tuning Advisor options
• Work with Resource Governor
• Determine Resource Governor configuration
• Implement the Resource Governor
• Work with Dynamic Management Views and Functions
• Work with Performance Data Warehouse
• back to top

Performance and Monitoring

• Understand the System Monitor
• Work with Counter Logs
• Work with Performance Counters
• Work with SQL Server Profiler
• Create a Trace
• Manage Traces
• Jointly Analyze SQL Server Profiler and System Monitor Data
• Work with Logs
• Manage Space for Logs and Databases
• Identify Reasons for Service Failure
• Work with Locks
• back to top

Understanding Failover Clustering

• Understand Failover Clustering
• Design Failover Clustering
• Design Windows Clustering
• Configure Windows Clustering
• Configure Failover Cluster Instances
• Perform Cluster Health Checks
• back to top

Implementing Database Mirroring

• Understand Database Mirroring
• Describe Database Mirroring Operating Modes
• Prepare Databases for Mirroring
• Copy System Objects from the Principal to the Mirror
• Understand Failover
• Design Database Mirroring Failback
• back to top

Implementing Log Shipping

• Understand Log Shipping
• List the Participants in Log Shipping
• Configure Log Shipping
• Understand Failover Strategies
• Understand Failback Strategies
• back to top

Implementing Replication

• Understand Replication
• Understand different Replication methods
• Describe what are Replication Agents
• Resolve Data Conflicts
• Implement Snapshot Replication
• Implement Transactional Replication
• Implement Merge Replication
• Microsoft Test Preparation
• back to top