50206 - Microsoft SQL Server 2005 for Application Developers

This three-day, hands-on, instructor-led course provides students with the knowledge and skills to developdatabase solutions using Microsoft SQL Server 2005.

After completing this course, students will be able to:

• Perform fundamental database administrationtasks at a level suitable for maintaining development/test platforms.
• Create and maintain server-side objects that are common to all database application development projects: schemas, synonyms, tables, data integrity constraints, views, and indexes.
• Evaluate and implement database securityand be aware of best practices related to the use of security roles.
• Create, test and debug stored proceduresthat use variables and parameters, IF..ELSE branching and WHILE looping, and handle errors.
• Design and implement solutions involving cursors and be aware of the many cursor-related options.
• Be aware of the wide array of XML-related features in SQL Server and be able to perform basic XML manipulation.
• Create user-defined functionsand understand which of the three function types is best suited to solve a particular problem.
• Understand transactions and when (and when not) to use explicit transaction syntax.
• Understand the nature and purpose of triggersand be able to implement both the afterand instead ofvariety.
• Be familiar with the nature and purpose of the other services available with SQL Server so you can leverage the full value of your investment.

Module 1: Management and Administration
Ideally there’s a clean separation of responsibilities between those who utilize database server resources in application software and those who manage, administer, maintain and protect those resources.  In practice, however, developers are often expected to contribute to (if not totally handle) management and administration duties. Additionally, programmers are strongly encouraged to run their own development/test servers, for which they’ll be responsible for maintenance and administration. This module provides an overview of all the major administrative functions, which include: creating databases and transaction logs, managing the file system, server and database configuration, and backup and recovery.

Lessons
• Quick introduction to the tools
• Creating databases and transaction logs
• Managing the file system
• Server configuration
• Database configuration
• Backup and recovery

Lab : Management and Administration
• Create a Database
• Alter Database Settings
• Backup and Restore

Module 2: Creating Server Objects
While developers are not typically called upon to install, configure and maintain database servers, they ought to be able to handle all aspects of creation and maintenance for server-side objects accessed by their software applications. This module covers key concepts and syntax for creating and maintaining: schemas, tables, data integrity constraints, views, indexes and synonyms.  Stored procedures, functions and triggers are covered in separate modules.

Lessons
• Schemas
• Synonyms
• Tables
• Data Integrity Constraints
• Views
• Indexes
• Best Practices

Lab : Creating Server Objects
• Creating Tables and Constraints
• Creating Views and Dropping Objects

Module 3: Security
Most administrative activities required for the health of a database are isolated and all but hidden (and rightly so) from the eyes of software developers.  Application design does not and should not depend on factors involving the server’s use of the file system, backup or recovery strategy.  Security, however, is another matter entirely.  While developers are not normally responsible for the safety and security of databases, they most certainly must contend with decisions that have been made and should be consulted and relied upon for security policy and configuration.  This module covers what every developer must know about the way security works with SQL Server.

Lessons
• Server-level configuration
• Database access and roles
• Object-level permissions
• Encryption
• Best Practices

Lab : Security
• Logins , Roles, Permissions via SSMS
• Permissions via Data Control Language
• Encryption

Module 4: Stored Procedures
SQL Server’s built-in programming language is called Transact-SQL or T-SQL for short.  This module covers everything you need to know to create and maintain stored procedures that use variables and parameters, IF..ELSE branches, WHILE loops, and error handling.

Lessons
• Creating and maintaining procedures
• Variables and Expressions
• Parameters
• Branching
• Looping
Error Handling
• Batch Separators and Statement Terminators
• Best Practices

Lab : Stored Procedures
• Basic Process
• Working with Input Parameters              
• Output Parameters and Return Codes
• Working with Errors

Module 5: Non-Relational Features
SQL Server’s built-in programming language is called Transact-SQL or T-SQL for short.  This module covers everything you need to know to create and maintain stored procedures that use variables and parameters, IF..ELSE branches, WHILE loops, and error handling.

Lessons
• Creating and maintaining procedures
• Variables and Expressions
• Parameters
• Branching
• Looping
• Error Handling
• Batch Separators and Statement Terminators
• Best Practices

Lab : Non-Relational Features
• Basic Process              
• Working with Input Parameters              
• Output Parameters and Return Codes
• Working with Errors

Module 6: User-Defined Functions
User-defined functions are often over-looked by designers and architects.  This is a pity because when used correctly user-defined functions are a simple replacement for complex stored procedures and occupy the much-needed middle ground between views and stored procedures.

Lessons
• The nature and purpose of UDFs
• Scalar functions
• In-line functions
• Table-valued functions
• APPLY Operator
• Best practices

Lab : User-Defined Functions
• Scalar Function
• Inline Table Function
• Multi-Statement Table Function

Module 7: Transactions
Of all the subjects in this course, transactions are possibly the most misunderstood and consequently misused feature of SQL Server.  Worse, poorly-crafted transactions can be a considerable drain on performance that no amount of indexing, tuning and additional CPU processing power and memory can overcome.  This module starts with a conceptual foundation of what transactions are and why they are used, then moves into syntax and behavior.

Lessons
• Foundations
• Explicit transaction syntax
• Transaction isolation levels
• The basics of locking and blocking
• Monitoring locks
• Best practices

Lab : Transactions
• Create a Transaction
• Experiment with Locking

Module 8: Triggers
A trigger is a stored procedure that runs when an insert, update and/or delete statement is issued against a table or view.  AFTER triggers run in the context of a transaction, meaning they run after the modifications have been made but before the changes have been committed. INSTEAD OF run instead of the requested insert/update/delete statement and can be defined for tables or views.  SERVER triggers run when the database schema is changed.  All types of triggers are discussed in this module along with a survey of the different types of design challenges triggers can help to resolve.

Lessons
• AFTER triggers
• OUTPUT Clause
• Handling ROLLBACK
• INSTEAD OF triggers
• DATABASE triggers
• Performance considerations
• Best practices

Lab : Triggers
• Investigating AFTER Triggers
• Investigating INSTEAD OF Triggers

Module 9: Other Services
A SQL Server license includes far more than just the relational database engine features detailed in this course.  Your organisation’s investment in SQL Server includes numerous valuable services that should be leveraged when designing and implementing solutions.  This module provides a quick introduction to Reporting Services, Integration Services, and Analysis Services.  Each topic is covered in detail in a separate multiple-day training course.  The goal in this course is to make you aware of the breadth and depth of the functionality provided along with a sense of how easy it is to get started and make productive use of these tools.

Lessons
• Reporting Services
• Integration Services
• Analysis Services

This course is intended for:

• Database application developers who integrate SQL Server resources in their software,
• Project managers who must have a basic understanding of what their development team is up to when working with SQL Server, and
• Database administrators who want to know what to expect from the applications connecting to their servers.

While there are no formal prerequisites, due to the fast pace of this course and the amount of material that will be covered it will help if the student is:

• Comfortable with the principals of relational databases,
• Has some experience with the SQL query language, and
• Is familiar with a modern Microsoft application development environment like Visual Basic, C# or other tools and languages in the Visual Studio line.
• Alto’s course on SQL Query class is intended as a prerequisite to this course, to cover the SQL query language requirements.