UW Certificate in SQL Server Administration
Program Overview
Students will:
- Learn how to manage, analyze and leverage complex data using SQL Server databases and servers
- Study data structures and SQL Server architecture and tools
- Be equipped to control data with the Transact-SQL programming language
- Understand issues involved in the planning, implementation and configuration of SQL Server databases and servers
- Learn about on-demand and periodic maintenance, as well as monitoring and performance tuning
Key Outcomes
Students will acquire the skills needed to effectively implement, configure and maintain SQL Server database management systems.
Audience:
- IT professionals who want to become a database administrator
- Individuals working for organizations that rely on SQL Server or are considering the purchase or migration to a SQL Server product
- IT professionals considering SQL Server certification
- IT professionals interested in expanding their database expertise to become multi-database experienced
Admissions Requirements:
- Experience with a database product
- Familiarity with database design and database theory
- Knowledge of basic SQL commands: complete the SQL Self-Test
About theCourses
Each course is 10weeks long and delivered online. Each week there will be a one-hour, live online meeting, followed by lecture videos that can be viewed according to each student’s weekly schedule. Attendance during live online meetings is expected.These meetings will be recorded for later viewing and for those unable to make the session due to time zone issues or extenuating circumstances. Throughout each week there will be a forum and exercises related to the current topic and at the end of each week there will be a short quiz.
A Note about Discussion Forums
Forums are a core part of each course, and mandatory. Students will be required to respond to and generate forum questions. In addition, students are highly encouraged to create and use Forums for their own questions, as they are a real part of day-to-day work in the profession, and using them to get and give help is a valuable skill.
Each week includes a posted question or comment from the online discussion that students will respond to. These will benefit the students and help instructors evaluate students’ understanding and adjust their teaching accordingly. These online discussions allow students to share knowledge and help each other learn.
Course 1: SQLADMN 100: SQL Server Concepts & Tools
Course Instructor:
Buck Woody
Course Overview
This 10-week course introduces SQL Server, including installation, configuration, tools, and database objects. The course also provides an overview of the Transact-SQL (T-SQL) language used in SQL Server, and the PowerShell scripting language used to manage and monitor SQL Server that is developed throughout the rest of the certificate program. The areas you concentrate on in this section of your certification are:
- Career Overview
- Overview of Relational Database Management Systems (RDBMS)
- SQL Server Architecture and Components
- Overview of SQL Server tools and utilities
- Introduction to Data Objects
- PowerShell scripting concepts and uses in SQL Server
- Introduction to Data Design
- Introduction to the Structured Query Language (SQL), including Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL)
- Introduction to Security and Permissions and Optimization basics
- Database Backups and Restoration
- Overview of SQL Server Optimization
- Overview of SQL Server Maintenance
Course Objectives
Upon successfully completing this course, you will be able to
- Choose an optimal design for SQL Server based on requirements and components;
- Install a functional SQL Server;
- Formulate basic DDL statements;
- Create users and assign database roles and rights;
- Understand the impact of indexes and queries; and
- Develop a basic maintenance plan
Required Textbooks:
- William R. Stanek. Microsoft SQL Server 2012 Pocket Consultant. Microsoft Press (February 10, 2012).ISBN-10: 0735663769; ISBN-13: 978-0735663763
- Itzik Ben-Gan. T-SQL Fundamentals for Microsoft SQL Server 2012 and SQL Azure. Microsoft Press (July 22, 2012).ISBN-10: 0735658145; ISBN-13: 978-0735658141
About the Lessons
The ten lessons in this course concentrate on techniques and processes to install, configure and maintain database servers and database objects. Refer to the Calendar for due dates
Lesson 1: Career Overview, Overview of Relational Database Management Systems (RDBMS)
This lesson introduces you to the course, gets you connected, and gives you information on the data profession and your career choices.
Lesson 2: SQL Server Components and Installation
This lesson covers the SQL Server components such as the Database Engine, Analysis Services, Reporting Services, and Integration Services. It also covers the process to design an optimal layout for those components, and overview on the Editions of SQL Server, and follows up with an installation of the Developer Edition.
Lesson 3: Overview of SQL Server tools and utilities
This lesson covers the primary tools you will use to manage, monitor and maintain database servers and database objects.
Lesson 4: Introduction to Data Objects and Design
This lesson introduces the database objects such as:
- Databases
- Tables
- Stored procedures
- Views
- Users
- Other constructs contained within the database.
Then, it follows with the basic concepts of database design. Following the information of working with data objects, you’ll put them into practice using design concepts that follow on from the first lesson.
Lesson 5: PowerShell scripting concepts and uses in SQL Server
This lesson covers the PowerShell scripting language in more depth, and explains how it is used within the SQL Server environment.
Lesson 6: Introduction to the Structured Query Language (SQL), including Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL)
This lesson covers the basics of the Transact-SQL language – something you’ll develop further in the courses that follow. You’ll learn how to select, read, and update data in a database.
Lesson 7: Introduction to Security and Permissions
This lesson introduces you to the platform, database, and object security within SQL Server, and also encompasses the security of users and groups (principals and roles).
Lesson 8 Database Backups and Restoration
This lesson explains how to work with backups in SQL Server and the restoring of those backups for emergency and testing purposes.
Lesson 9: Overview of SQL Server Optimization
This lesson explains how to optimize the SQL Server platform, and the controls you have within the database to optimize performance within a given query.
Lesson 10: Overview ofSQL Server Maintenance
This lesson explains how to maintain the SQL Server platform, and the controls you have within the system to maintain databases and database objects, using the tools discussed in lesson two.
Course 2: SQLADMN 200: Planning and Configuration
Course Instructor:
Kyle LaValley
Course Overview
This course will cover the administration of SQL Server 2012 servers in an enterprise environment, including the hardware, operating system and platform planning, implementation and configuration. It is the second course of a three course Certificate in SQL Server Administration certificate program offered online through the University of Washington. It is assumed that you have successfully completed course one of the certificateprogram, SQL Server Concepts & Tools (or have equal professional experience).
Course Objectives
When you have successfully finished this course, you will be able to
- Plan a system based on requirements and constraints;
- Evaluate Hardware and Operating System considerations;
- Perform basic Networking as it relates to SQL Server;
- Conduct platform configuration
- Understand High Availability; and
- Plan for Disaster Recovery
Required Textbooks
- Stanek, William R. Microsoft SQL Server 2012 Pocket Consultant, Second Edition. Redmond, WA: Microsoft Press, 2012. ISBN: 9780735663763.
- Jorgensen, A., Wort, S., LoForte, R., and Knight, B. Professional Microsoft SQL Server 2012 Administration, Indianapolis, IN: John Wiley & Sons, Inc., 2012. ISBN: 9781118106884
About the Lessons
The 10 lessons in this course concentrate on the planning and configuration of SQL Server 2012
Lesson 1: Installation and Initial Configuration
This lesson continues with installing SQL Server 2012. It covers which components/features that can be installed. It covers hardware considerations including the CPU, RAM, and Storage I/O. It will also cover the minimum hardware and OS requirements needed for installing SQL Server.
Exercise1,install a named instance of SQL Server 2012, two if one does not already exist, unattended install
Exercise 2, disable default SA and create a SQL login with SA privileges
Exercise 3, attach and detach database
Exercise 4, create a file group
Lesson 2: Configuration
This lesson covers SQL Server 2012 configuration tools. You will learn how to stop and start SQL Server 2012 by various means. You will also learn about rebuilding the system databases, contained databases, and the CLR environment.
Exercise 1, move the system databases to a new location
Exercise2, create a startup procedure to log the start time
Exercise 3, create a batch script to start, restart, and stop the SQL Server services
Exercise 4, start SQL Server in single user mode
Lesson 3: SQL Server Security
This lesson covers the security of SQL Server. You will learn about windows logins, along with SQL Server logons and users. You will learn about certificate and asymmetric keys. It will also cover several types of roles, transparent data encryption and audits.
Exercise1, create a windows local login, a SQL Server logon, and a SQL Server user
Exercise 2, set the encryption for the server
Exercise 3, encrypt a database
Exercise 4, create and test an audit
Exercise 5, open MMC to view certificates
Lesson 4: SQL Server Security Continued and SQL Server Networking
This lesson covers the basics of networking as it relates to SQL Server 2012. You will learn about TCP/IP and UDP. You will learn about the default ports that SQL Server features use. You will also learn about the basics of firewalls. You will also learn about SSL encryption and certificates.
Exercise 1, create a logon using a certificate
Exercise 2, configure the OS firewall to allow SQL Server 2012 through
Exercise 3, change the default port of the SQL Server service
Exercise 4, set SQL configuration to force encryption
Exercise 5, create a linked server
Exercise 6, create an alias
Lesson 5: Optimizing SQL Server
This lesson covers the optimization of the SQL Server instance. You will learn database file placement along with partitioning. You will also learn about tuning SQL Server memory, CPU considerations, and data compression. You will also learn about indexes and the resource governor.
Exercise 1, set page level compression for table within a database
Exercise 2, create a resource pool and work group
Exercise 3, create a clustered index and a non-clustered index on a table
Exercise 4, set SQL Server priority boost and enable lightweight pooling
Exercise 5, create a second file group and file to the tempdb
Lesson 6: SQL Server Settings
This lesson covers the setting commonly used in SQL Server 2012. You will learn about instance level facets, properties, and database mail. You will also learn about policy management and instance level triggers.
Exercise 1, create an instance level trigger
Exercise 2, configure database mail
Exercise 3, enable a specific feature and test
Exercise 4, create a policy and export it to another instance
Lesson 7: SQL Agent and Replication
This lesson covers the SQL Agent and Replication.
Exercise 1, create an operator
Exercise 2, create an alert
Exercise 3, create job to shrink database and add a notification
Exercise 4, configure a replication distributor
Exercise 5, create a replication publication
Exercise 6, create a replication subscriber
Lesson 8: SQL Server Clustering, Log Shipping, and Mirroring
This lesson covers High Availability Clustering and Log Shipping.
Exercise 1, configure a log shipping solution
Exercise 2, configure a mirror solution for a database
Lesson 9: SQL Server Always On and Disaster Recovery Planning
This lesson covers High Availability Always On and Disaster Recovery Planning.
Exercise 1, Create an RPO/RTO
Lesson 10: SQL Server Backup and Restore Options
This lesson covers Backup and Restore solutions.
Exercise 1, perform a full backup
Exercise 2, perform a differential backup
Exercise 3, perform a log backup
Exercise 4, verify a backup
Exercise 5, restore a full backup and a log backup
Exercise 6, restore a point in time
Course 3: SQLADMN 300: Systems Management and Operation
Course Introduction
Course Instructor:
boB Taylor
Course Overview
This course will cover on-demand and periodic maintenance of SQL Server Instances and Databases, monitoring and performance tuning, and advanced concepts of platform selection and implementation. It is the final course of a three course Certificate in SQL Server Administration certificate program offered online through the University of Washington. It is assumed that you have successfully completed course one of the certificateprogram, SQL Server Concepts & Tools, and course two, Planning and Configuration (or have equal professional experience).
Course Objectives
When you have successfully finished this course, you will be able to
- Plan periodic and On-Demand maintenance;
- Design and implement Security Best Practices – to include Principal, Securables and Principal of Least Privilege;
- Understand and demonstrate the use of PowerShell as an administrative tool;
- Understand the Waits and Queues Methodology as a cookbook for Bottleneck Analysis
- Perform Monitoring and Performance Tuning – to include SQL Server and Windows Event Logs, Performance Monitor, Extended Events, Auditing and Dynamic Management Objects.
- Understand Virtualization and its impact on SQL Server, to include Hypervisor fundamentals and how virtualization impact SQL Server performance.
- Discuss advances in cloud based computing as it pertains to SQL Server, namely Infrastructure as a service (IaaS) and Database as a Service (DaaS).
Required Textbook
- Stanek, William R. Microsoft SQL Server 2012 Pocket Consultant, Second Edition. Redmond, WA: Microsoft Press, 2012. ISBN: 9780735663763.
Recommended Textbook
- Thomas, Orin. Administering SQL Server 2012 Databases, Redmond, WA: Microsoft Press, 2012. ISBN: 9780735666078
About the Lessons
The 10 lessons in this course concentrate on the management and operation of SQL Server 2012.
Lesson 1: Periodic and on-demand maintenance activities
This lesson discusses the concepts of maintenance activities necessary to ensure reliability, proper operation and acceptable performance.
Exercise 1, Alerting on hardware failure
Exercise 2, Ensuring database consistency
Exercise 3, Statistics maintenance
Exercise 4, Backup
Lesson 2: Security
This lesson covers SQL Server 2012 Security concepts and Best Practices.
Exercise 1, Examine effective permission sets
Exercise 2, Implement Schema based security
Exercise 3, Ownership Chaining
Exercise 4, Contained Databases
Lesson 3: Waits and Queues Methodology
This lesson covers a methodology designed to quickly identify bottlenecks.
Exercise 1, Use Activity Monitor to examine resource usage
Exercise 2, Server Bottlenecks
Exercise 3, Database Bottlenecks
Exercise 4, Apply Waits and Queues Methodology to identify a bottleneck
Lesson 4: Performance Monitoring Basics
This lesson covers the basics of performance monitoring to include OS tools, SQL Server tools as well as Open Source Tools such as Performance Analysis for Logs (PAL).
Exercise 1, Management Data Warehouse Introduction
Exercise 2, Performance Monitor
Exercise 3, Activity Monitor
Exercise 4, SQLDiag
Exercise 5, PAL
Lesson 5: Performance Monitoring Advanced Topics
This lesson covers advanced topics in Performance Monitoring.
Exercise 1, Baseline creation
Exercise 2, Analyze a workload against a baseline
Exercise 3, Apply Waits and Queues Methodology to analysis
Lesson 6: Tools
This lesson covers tools that can be used to help analyze bottlenecks, monitor system health and provide system diagnostic information.
Exercise 1, SQL Server Profiler
Exercise 2, Extended Events
Exercise 3, Database Engine Tuning Advisor
Lesson 7: Auditing and Introduction to Dynamic Management Objects
This lesson covers the SQL Server Auditing feature and introduces Dynamic Management Objects.
Exercise 1, creating an Audit
Exercise 2, create a Server Audit Specification
Exercise 3, create a Database Audit Specification
Exercise 4, Dynamic Management Objects familiarity
Exercise 5, Dynamic Management Objects with Waits and Queues
Lesson 8: Dynamic Management Objects
This lesson covers the full range of Dynamic Management Objects and demonstrates the key DMOs.
Exercise 1, examine the catalog of DMOs
Exercise 2, explore key DMOs
Exercise 3, use DMOs to help determine bottleneck
Lesson 9: Advanced DMO Concepts
This lesson continues with the exploration of the Dynamic Management Objects.
Exercise 1, Identify Missing Indexes
Exercise 2, Identify Resource Contention
Exercise 3, Identify and resolve bottlenecks
Lesson 10: SQL Server Virtualization and Cloud Concepts
This lesson covers Hypervisor fundamentals.
Exercise 1, Import a VM
Exercise 2, Start, Stop, and Snapshot a VM
Page 1 of 10