Overview
5 Days $4,050 or FREE WITH SATVs (?)
This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2008 R2 database. The course focuses on teaching individuals how to use SQL Server 2008 R2 product features and tools related to maintaining a database.
- Introduction to SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
- Verify SQL Server Component Installation
- Alter Service Accounts for New Instance
- Enable Named Pipes Protocol for Both Instances
- Create Aliases for AdventureWorks and Proseware
- Ensure SQL Browser is Disabled and Configure a Fixed TCP/IP Port
After completing this module, students will be able to:
- Describe the SQL Server Platform
- Work with SQL Server Tools
- Configure SQL Server Services
- Overview of SQL Server 2008 R2 Architecture
- Planning Server Resource Requirements
- Pre-installation Testing for SQL Server 2008 R2
- Adjust memory configuration
- Perform Pre-installation Stress Testing
- Check Specific I/O Operations
After completing this module, students will be able to:
- Describe the SQL Server architecture
- Plan for server resource requirements
- Conduct pre-installation stress testing for SQL Server
- Preparing to Install SQL Server 2008 R2
- Installing SQL Server 2008 R2
- Upgrading and Automating Installation
- Review installation requirements
- Install the SQL Server instance
- Perform Post-installation Setup and Checks
- Configure Server Memory
After completing this module, students will be able to:
- Prepare to install SQL Server
- Install SQL Server
- Upgrade and automate the installation of SQL Server
- Overview of SQL Server Databases
- Working with Files and Filegroups
- Moving Database Files
- Adjust tempdb configuration
- Create the RateTracking database
- Attach the OldProspects database
- Add multiple files to tempdb
After completing this module, students will be able to:
- Describe the role and structure of SQL Server databases
- Work with files and filegroups
- Move database files within servers and between servers
- Backup Strategies
- Understanding SQL Server Transaction Logging
- Planning a SQL Server Backup Strategy
- Plan a backup strategy
- Configure recovery models
- Review recovery models and strategy
After completing this module, students will be able to:
- Describe the critical concepts surrounding backup strategies
- Explain the transaction logging capabilities within the SQL Server database engine
- Plan a SQL Server backup strategy
- Backing up Databases and Transaction Logs
- Managing Database Backups
- Working with Backup Options
- Investigate backup compression
- Transaction log backup
- Differential backup
- Copy only backup
- Partial backup
After completing this module, students will be able to:
- Back up databases and transaction logs
- Manage database backups
- Work with more advanced backup options
- Understanding the Restore Process
- Restoring Databases
- Working with Point-in-time Recovery
- Restoring System Databases and Individual Files
- Determine a restore strategy
- Restore the database
- Using STANDBY mode
After completing this module, students will be able to:
- Understand the restore process
- Restore databases
- Work with Point-in-time Recovery
- Restore system databases and individual files
- Transferring Data To/From SQL Server 2008 R2
- Importing & Exporting Table Data
- Inserting Data in Bulk
- Import the Excel spreadsheet
- Import the CSV file
- Create and test an extraction package
- Compare loading performance
After completing this module, students will be able to:
- Transfer data to and from SQL Server
- Import and export table data
- Insert data in bulk and optimise the bulk insert process
- Authenticating Connections to SQL Server
- Authorising Logins to Access Databases
- Authorisation Across Servers
- Create Logins
- Correct an Application Login Issue
- Create Database Users
- Correct Access to Restored Database
After completing this module, students will be able to:
- Describe how SQL Server authenticates connections
- Describe how logins are authorised to access databases
- Explain the requirements for authorisation across servers
- Working with Server Roles
- Working with Fixed Database Roles
- Creating User-defined Database Roles
- Assign Server Roles
- Assign Fixed Database Roles
- Create and Assign User-defined Database Roles
- Check Role Assignments
After completing this module, students will be able to:
- Work with server roles
- Work with fixed database roles
- Create user-defined database roles
- Authorising User Access to Objects
- Authorising Users to Execute Code
- Configuring Permissions at the Schema Level
- Assign Schema-level Permissions
- Assign Object-level Permissions
- Test Permissions
After completing this module, students will be able to:
- Authorise user access to objects
- Authorise users to execute code
- Configure permissions at the schema level
- Options for Auditing Data Access in SQL Server
- Implementing SQL Server Audit
- Managing SQL Server Audit
- Determine audit configuration and create audit
- Create server audit specifications
- Create database audit specifications
- Test audit functionality
After completing this module, students will be able to:
- Describe the options for auditing data access in SQL Server
- Implement SQL Server Audit
- Manage SQL Server Audit
- Automating SQL Server Management
- Working with SQL Server Agent
- Managing SQL Server Agent Jobs
- Create a Data Extraction Job
- Schedule the Data Extraction Job
- Troubleshoot a Failing Job
After completing this module, students will be able to:
- Automate SQL Server Management
- Work with SQL Server Agent
- Manage SQL Server Agent jobs
- Understanding SQL Server Agent Security
- Configuring Credentials
- Configuring Proxy Accounts
- Troubleshoot job execution failure
- Resolve the security issue
- Perform further troubleshooting
After completing this module, students will be able to:
- Explain SQL Server Agent security
- Configure credentials
- Configure Proxy accounts
- Configuration of Database Mail
- Monitoring SQL Server Errors
- Configuring Operators, Alerts and Notifications
- Configure Database Mail
- Implement Notifications
- Implement Alerts
After completing this module, students will be able to:
- Configure database mail
- Monitor SQL Server errors
- Configure operators, alerts and notifications
- Ensuring Database Integrity
- Maintaining Indexes
- Automating Routine Database Maintenance
- Check database integrity using DBCC CHECKDB
- Correct index fragmentation
- Create a database maintenance plan
- Investigate table lock performance
After completing this module, students will be able to:
- Ensure database integrity
- Maintain indexes
- Automate routine database maintenance
- Capturing Activity using SQL Server Profiler
- Improving Performance with the Database Engine Tuning Advisor
- Working with Tracing Options
- Capture a trace using SQL Server Profiler
- Analyse a trace using Database Engine Tuning Advisor
- Configure SQL Trace
After completing this module, students will be able to:
- Capture activity using SQL Server Profiler
- Improve performance with the Database Engine Tuning Advisor
- Work with tracing options
- Monitoring Activity
- Capturing and Managing Performance Data
- Analysing Collected Performance Data
- Investigating DMVs
- Configure Management Data Warehouse
- Configure Instances for Data Collection
- Work with Data Collector Reports
After completing this module, students will be able to:
- Monitor current activity
- Capture and manage performance data
- Analyse collected performance data
- Working with Multiple Servers
- Virtualising SQL Server
- Deploying and Upgrading Data-Tier Applications
- Configure CMS and execute multi-server queries
- Deploy a data-tier application
- Register and extract a data-tier application
- Upgrade a data-tier application
After completing this module, students will be able to:
- Work with multiple servers
- Describe options for virtualising SQL Server
- Deploy and upgrade Data-Tier Applications
- SQL Server Troubleshooting Methodology
- Resolving Service-related Issues
- Resolving Concurrency Issues
- Resolving Login and Connectivity Issues
- Troubleshoot and resolve SQL Server administrative issues
After completing this module, students will be able to:
- Explain SQL Server troubleshooting methodology
- Resolve service-related issues
- Resolve concurrency issues
- Resolve login and connectivity issues
- Basic knowledge of the Microsoft Windows operating system and its core functionality
- Working knowledge of Transact-SQL
- Working knowledge of relational databases
- Some experience with database design
- Completed Course 2778: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL
Instructors
Adam Keats
With over 24 years of real-world and training experience, Adam is ...
Newton Godoy
With over 17 years of in-class training experience and over 16 ...