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.
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.
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.
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.
5:
Non-Relational Features
SQL Server is fundamentally a relational
database management system. However, not all problems can be solved by
normalizing data into tables and manipulating it through SELECT, INSERT, UPDATE
and DELETE statements. We’ll cover two important departures from the strict
relational model: 1) a cursor is a row-at-a-time processing feature that offers
an important alternative to the set-oriented statements. 2) XML is designed as a
generic and extensible way to store data and meta-data. SQL Server has extensive
support for XML, so much in fact that we can only cover this topic at an
introductory level in this course.
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.
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.
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.
9: Other
Services
A SQL Server license includes far more
than just the relational database engine features detailed in this course. Your
organization’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.
