This intensive course is aimed at getting students familiar with SQL Server administration skills and tools. Improving database and server performance will be covered. The will include implementing a database security model. The course will contain the new features in SQL Server 2016. This course can be customised for earlier versions of SQL Server.
Duration:
4 Days
Prerequisites
- Before attending this course, students must have:
- Understanding of relational database concepts
- Experience of querying database tables
- Experience using SQL Server Management Studio
6 Top Takeaways from our courses
- Improve database design
- Improve query performance
- Improve database resilience with a backup policy
- Secure data stores by restricting user access
- Access remote data stores
- Integrate diverse data stores to a single repository
Module 1: SQL Server Overview
- The Role of Database Providers
- Client Side and Server Side Components
- SQL Server Architecture
- SQL Server Developer Tools
- SQL Server Sample Databases
Module 2: Installing SQL Server
- Preparing for Installation
- Upgrading an Earlier Version
- Installing SQL Server
- Configuring the Server
Module 3: Working with SQL Server Management Studio
- Getting Started with SSMS
- Exploring the Object Explorer
- Working with the Query Editor
- Using SQL Server Books Online
Module 4: Designing a Database
- Defining Entities
- Applying Normalisation
- Database Objects
- System Tables
- Defining Databases
- Setting Database Options
- Managing Data and Log File Growth
Module 5: Implementing Tables
- Creating Tables
- Setting Column Data Types
- Adding and Dropping a Column
- Generating Column Values
- Adding Constraints
- Using the Identity Property
Module 6: Backup and Recover a Database
- Database Recovery Models
- Backing Up Transactions Logs and Databases
- Backing Up in Management Studio
- Restoring in Management Studio
Module 7: Design and Administer Security Levels
- Designing security plan
- Administering server and database authentication
- Administering database authorization
- Administering database permissions
- Administering users, groups and roles
Module 8: Indexing Tables
- Introduction to Indexes
- Index Architecture
- How SQL Server Retrieves Stored Data
- How SQL Server Maintains Index and Heap Structures
- Deciding Which Columns to Index
Module 9: Managing Transactions and Locks
- Introduction to Transactions and Locks
- Managing Transactions
- SQL Server Locking
- Managing Locks
Module 10: Accessing Linked Servers
- Introduction to Distributed Queries
- Executing an Ad Hoc Query on a Remote Data Source
- Setting up a Linked Server Environment
- Executing a Query on a Linked Server
- Executing a Stored Procedure on a Linked Server
- Managing Distributed Transactions
- Modifying Data on a Linked Server
- Using Partitioned Views
Module 11: Monitoring and Tuning
- SQL Servers inbuilt monitoring tools
- Use SQL Profiler to monitor a database
- Describe how the Index Tuning Wizard works and when to use it
- Define database partitioning
Module 12: Automating Administrative Tasks
- Using SQL Server Agent
- Creating and scheduling job
- Maintenance Plans
- SQL Management Objects (SMO)
Module 13: Programming Replication
- Overview of SQL Server Replication
- Replication Programming Interfaces
- Configuring Replication
- Synchronizing Data
Module 14: Using Integration Services
- Importing and Exporting Data
- Integration Services Tools
- Building a Package
- Troubleshooting a Package
Module 15: New Features in SQL Server 2016
- Always Encrypted
- Stretch Database
- Real-time Operational Analytics
- PolyBase into SQL Server
- Native JSON Support
- Enhancements to AlwaysOn
- Enhanced In-Memory OLTP