(Retired – no longer offered as a public class)

Overview

This 5-day SQL Server training class is designed to give a solid grounding for database administrators in topics related to development support, security, and scripting with PowerShell. Security is an area that is critical in today’s environment and all DBAs should be able to secure a SQL Server installation, and the class spends almost two days on security best practices from A to Z. The addition of days on PowerShell and security provide coverage of areas that have more recently become requirements for production DBAs but are not traditionally covered in “DBA” classes. The course also covers a wealth of developer support topics, with the focus on best practices for configuration and security, planning, and supporting these features in a production environment, rather than in-depth coverage of how to program (covered in the developer immersion event) with the features.

Instructor: Bob Beauchemin

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Curriculum

Part 1: Setting Up and Managing a Secure SQL Server

This section covers optimization of procedural code rather than optimization of individual T-SQL statements. It also covers plan caching in detail and achieving plan stability.

Module 1: Setting up SQL Server – Service Accounts and Surface Area Reduction

The first task for securing a SQL Server instance is to choose the service account and other configuration parameters to limit the surface area. Topics covered include:

• Service account selection and management
• Surface area reduction
• Using SQL Server Configuration Manager
• Configuring the security surface area with SQLSAC in SQL Server 2005
• Network connectivity
• Setting up an encrypted session via SSL certificates
• Division of services in SQL Server 2005-2012

Module 2: Authentication

Authentication is the process of identifying yourself to the database instance. Before you can perform any operation in SQL Server, you must be able to log in. Logins in SQL Server can use Windows (Operating System) accounts or SQL Server-specific accounts. In addition, some special functions like SQL Server Service Broker use certificates for authentication. Topics include:

• Authentication mode
• Administrator privileges – built in logins and role assignment
• Server roles in SQL Server 2012
• Password policy
• Mapping Windows users and groups to SQL Server logins
• Special logins
• Security and endpoints
• Linked server authentication
• Setting up constrained delegation
• Setting up Service Broker security
• Proxy accounts, credentials, and SQL Server Agent

Module 2: Authorization

Once you’ve identified yourself to the database, you need to be granted privileges before you can access any data or perform system functions. This is accomplished through the GDR (Grant-Revoke-Deny) verbs. SQL Server 2005 instituted the concept that all permissions are grantable using GDR. This module’s topics include:

• Database Users and mapping logins to users
• Built-in users and roles
• DBO and Database Owner
• Database ownership and trust
• The TRUSTWORTHY Database Property
• Grouping permissions through roles
• Instance roles
• Database roles
• Mapping a Windows group to a SQL Server role
• Application roles
• Login-less users
• User-schema separation
• Defining database objects in schemas
• Schema-based management vs. everything owned by dbo
• Mapping permissions through default schemas and schema ownership
• Catalog security
• SQL Server Permissions
• Inside the new permission hierarchy
• Granting granular permissions to a login, user, or role
• Creating a custom permission

Module 4: Secret Storage, Key Management, and Encryption

SQL Server 2005 introduced data encryption and certificate-based security. But it is a more difficult problem to store secrets securely that to implement data encryption. This module covers not only encryption but the secret storage hierarchy in SQL Server 2005-2008, as well as some new encryption capabilities in SQL Server 2008. Topics covered include:

• Managing the Service Master Key and Database Master Keys
• Key manipulation in backup/restore, failover, and mirroring
• Understanding and managing certificates, asymmetric and symmetric Keys
• Third-party key management in SQL Server 2008
• Data encryption functions
• Encryption algorithms
• Encryption and indexing
• Choosing the right encryption algorithm
• Strategies for using data encryption
• SQL Server 2008 transparent data encryption

Module 5: Writing and Executing Safe Code 

One of the major topics of discussion when making a database secure is SQL injection. We’ll go over rules and best coding practices to prevent SQL injection in this module. In addition we’ll discuss execution content for procedural code, ownership chains, and the hazards of dynamic SQL. We’ll also explore a new SQL Server 2005 feature that permits you to assign permissions by signing code with a certificate or asymmetric key. Topics covered include:

• SQL injection
• Inline comment attacks
• Query stacking attacks
• System commands
• Metadata discovery
• Using parameterized queries
• The QUOTENAME function
• Execution content
• Object ownership and ownership chains
• Cross-database ownership chains
• EXECUTE AS and SQL batches
• EXECUTE AS and procedures, functions, and triggers
• Identity functions in TSQL
• Adding a signature to procedural code

Module 6: Auditing and Compliance

SQL Server 2008 adds granular auditing capabilities to the already strong auditing functionality in SQL Server. This module will examine auditing in detail, C2 and Common Criteria Certification and setup, and suggest some best practices for maintaining a database at its safest level and troubleshooting security-related problems. Topics covered include:

• SQL Server and C2 security
• SQL Server 2005 and Common Criteria Compliance
• Login triggers
• Auditing functionality in SQL Server through trace events
• Custom auditing opportunities in DDL triggers and event notifications
• Auditing DDL in SQL Server 2008

Part 2: SQL Server PowerShell Scripting

PowerShell is an object-oriented shell that is part of the Windows Common Engineering Criteria. PowerShell is used with SQL Server in conjunction with SMO, the SQL Server Management Object libraries that support all facets of SQL Server administrative programming. There’s a SQL Server PowerShell provider, PowerShell cmdlets, and PowerShell integration with SQL Server Management Studio. This includes the multi-server management and Data Tier Applications in SQL Server 2008 R2.

Module 1: Introduction to the PowerShell Language

This module covers the PowerShell scripting language constructs. We’ll be using PowerShell V2, which adds a number of enhancements like modules. You’ll learn to work with primitive types, .NET objects, WMI, COM and PS objects in PowerShell, and well as how to use the PowerShell pipeline to pass objects between commands and operators. Topics covered include:

• Profile
• Commands
• Objects
• Members
• Pipes and formatters
• Script security
• Data types, variables, and operators
• Error handling
• Functions
• Scripts
• Modules

Module 2: Using PowerShell with SQL Server

In this module we’ll dig into the SQL Server-specific support for PowerShell, introduced in SQL Server 2008. There is a great deal of scripting functionality covering all aspects of the SQL Server product. Topics covered include:

• Configuring SQL Server provider/cmdlets in “vanilla” PowerShell
• Using the PowerShell provider and SMO
• Using the built-in SQL Server cmdlets
• Combining PowerShell with T-SQL
• Administrative tasks (CREATE/ALTER objects, BACKUP/RESTORE)
• Running PowerShell scripts in SQL Agent jobs
• Network configuration and other WMI tasks
• Policy-Based Management
• Performance Data Collection
• Deploying to a list of SQL Servers
• Configuring Multi-Server Management

Part 3: Alternate Data Maintenance and Programming Models

Although all applications will use T-SQL and traditional database objects, non-traditional programming models and extended functionality is continuously being introduced. This section gives administrators the background they need to manage the objects and data that are unique to these non-traditional programming models. For each model, we’ll cover the technology, how it affects the rest of SQL Server and technology-specific optimization and troubleshooting techniques.

Module 1: Supporting and Optimizing SQLCLR

This module covers the internals of how SQLCLR works in SQL Server to give you the knowledge to architect, manage, define, and troubleshoot SQLCLR-based database applications. Because SQL Server procedural code (stored procedures, UDFs and triggers) can now be written in T-SQL or .NET languages, we’ll also focus on what happens when .NET CLR code accesses the database and discuss which coding language is a better choice for a given problem. Topics covered include:

• Internals – security, reliability, and performance
• Integration with the SQL Engine
• Database objects – assemblies, functions, procedures, triggers, UDTs and UDFs
• Debugging and troubleshooting
• SQLCLR vs. T-SQL – choosing the right technology

Module 2: Supporting and Optimizing XML Technologies

This module covers XML functionality choices in the SQL Server relational database, including the XML data type, schema and query along with XML indexes. We’ll look at how each of these components work internally (with examples) and how to define and use XML-based persisted computed columns for best query performance. Finally, we’ll explore the internals of XML indexes and describe the best practices with respect to XML query performance. Topics covered include:

• XML functionality choices
• XML data type
• XML schema support
• XQuery methods
• XML indexes, queries, and performance

Module 3: Supporting and Optimizing Spatial Data

We’ll cover the basics of the spatial data type concepts so that DBAs can assist in planning and implementation of spatial databases. This includes the difference in geometry and geography data types, the internals of spatial indexes and how to implement the best strategy for indexing, as well as the using the spatial diagnostic stored procedures that will ensure that you employ the best indexing strategy. Topics covered include:

• Spatial data types – Geography and Geometry
• Properties and methods
• Deploying and maintaining spatial as a .NET library
• Spatial indexes

Module 4: Supporting and Optimizing Full-Text Search

In this module, we’ll discuss the individual components that comprise full text search, as well as how to define and manage full text catalog, indexes, and other database objects. This includes a discussion of the internals of the in-server implementation of full-text indexes in SQL Server 2008. We’ll cover how to architect tables that use FTS for best performance. Finally we’ll cover the new parsing and other DMVs that make FTS less of a “black-box” and assist troubleshooting in SQL Server 2008. Topics covered include:

• Architecture – services
• Use cases
• FTS components – word breakers and filers
• FTS DDL and DML
• FTS components – stoplists and thesaurus
• Troubleshooting and tuning FTS

Part 4: Optimizing and Supporting Transact-SQL

Module 1: Supporting and Optimizing Service Broker

Service Broker uses an array of new database objects, such as services, queues, contracts, message types, and priorities and server-level objects such as endpoints. It also can use a set of Windows or certificate-based security features. As such, a Service Broker application can be difficult to set up as well as to monitor, diagnose, and troubleshoot. In this module, we’ll cover all of the objects and illustrate the object and security setup, as well as how to use SQL Profiler and the SSBDiagnose utility to validate your setup and diagnose conversation-specific runtime problems. Topics covered include:

• Service Broker use cases
• Service Broker and database maintenance
• Services, queues, message types, and contracts
• Activation
• Routing and forwarding messages
• Endpoint and conversation security
• Troubleshooting configuration and operations

Module 2: Supporting and Optimizing Change Tracking and Change Data Capture

SQL Server 2008 introduced two new mechanisms for tracking changes. This module covers both mechanisms, change data capture and change tracking, and explains the reason and use cases for each feature. In addition, we’ll cover setting up and troubleshooting each feature, as well techniques for monitoring a running system. We’ll accomplish this by examining the internals of how each feature works. Topics covered include:

• Change Data Capture database objects
• Change Data Capture database configuration
• Change Data Capture and incremental data warehousing
• Change Data Capture internals and troubleshooting
• Change Tracking database objects and database configuration
• Change Tracking and Sync Services integration
• Change Tracking internals and troubleshooting

Module 3: Supporting and Optimizing SQL Client Technologies 

In this module, we’ll cover client setup, including choosing protocols and endpoint definition, login and session encryption, as well as other client setup considerations. We’ll go into troubleshooting client problems using the SQL Server client stack’s built-in diagnostics and show how to use these in conjunction with Extended Events. A client application coded without regard for performance and data types can slow down even the best tuned and managed SQL Server database, so this module cover some common mistakes made when mapping SQL Server types to client data types. Topics covered include:

• Configuring client connectivity
• Client APIs supported by SQL Server
• Troubleshooting client coding problems and practices
• Tracing and troubleshooting client and network problems

Module 4: Supporting and Optimizing ORMs

In this module, we’ll cover dealing with ORMs (Object-Relational Mapping layers) as a DBA. A client application coded without regard for performance can slow down even the best tuned and managed SQL Server database, so this module covers best practices for client coding. There is advice and support that a DBA can give that helps an ORM make best use of the database. Topics covered include:

• ORMs and their effect on the database
• Using Stored Procedures in ORMs
• Coding LINQ for best performance
• Using hints with ORM-generated code

Module 5: Configuring and Supporting SQL Azure Databases

SQL Azure is Microsoft’s newest offering in the SQL Server space. Administration, operations, and internals tuning is accomplished by federated servers in worldwide IT centers. Deployment and programmability and integration are the main challenges in this cloud-based system. Topics covered include:

• SQL Azure architecture
• Topology
• Connecting
• Database object support
• Troubleshooting
• Transact-SQL DDL and DML differences
• Moving data to and from SQL Server

Questions?

If you have any questions, please contact us.