Module 10: Data Services v
!
Best Practices
There are four important best practices associated with data services:
• The logical design should specify SQL Server as the preferred data
service for relational, volatile data. All volatile entities, such as
requisitions, vendors, and so on, are stored on a database data service
provider.
• The logical design should specify Active Directory as the preferred data
service for hierarchical, non-volatile data. Non-volatile, highly
redundant data sets such as requestor are stored in Active Directory.
• The physical design of the SQL Server data service should use stored
procedures for data set retrieval and data modification. The physical
design should also use triggers to maintain data integrity.
• The design of an Active Directory schema should include both a class
design and an object design. While defining a class design is a
requirement, defining an object design allows the Active Directory
services to better index the directory to enable faster searches.
Lab Strategy
!
Lab 10: Data Services
The purpose of Lab 10 is for students to experiment with designing a
specialized query. The query is a hierarchical query that will require the
Data Shape Provider to implement the query in the physical design.
The physical design is the hardest part of the lab because students must
derive the query to pass to the Data Shape Provider. Microsoft MSDN
®
Library provides documentation for creating Data Shape Provider queries.
However, it is also acceptable for students to use a pseudocode solution.
Discuss with students their answers to Lab 10.
Module 10: Data Services 1
#
##
#
Overview
!
Introduction to Data Services
!
SQL Server
!
Active Directory
!
Logical Design of Data Services
!
Physical Design of Data Services
!
Market Purchasing
!
Best Practices
In this module, you will learn about the data services layer. This module
focuses primarily on Microsoft
®
SQL Server
™
and Active Directory
™
as two
key technologies of a data services layer. After completing this module, you
will be able to:
!
Identify the important features of a data service, and in particular those of
SQL Server and Active Directory.
!
Describe storage and retrieval for data services.
!
Describe how a data service works with data.
!
Describe how a data service maintains data integrity.
!
Describe how a data service handles distribution.
!
Describe the physical design of a data services layer and how to apply
technologies to implement it.
Topic Objective
To provide an overview of
the module topics and
objectives.
Lead-in
In this module, you will learn
about the data services
layer and how to create a
physical design for it.
2 Module 10: Data Services
#
##
#
Introduction to Data Services
!
The Business Problem
!
Business Requirements
The data services layer objects and business logic objects allow the data access
layers to retrieve and modify data from the underlying data storage systems.
In this section, the data services layer will be placed in the proper context of the
business problem. This discussion will be followed by a presentation on the
business requirements of a data services layer.
Topic Objective
To provide an overview of
the section topics and
objectives.
Lead-in
In this section, you will learn
what makes up a data
service.
Module 10: Data Services 3
The Business Problem
Data Access Layer
Connected Business
Logic Layer
Disconnected Business
Logic Layer
Facade Layer
Web Services Facade Business Facade
Transactional DAL
Nontransactional DAL
User Services
Data
Services
The data services layer is accessed by the transactional or nontransactional data
access layer (DAL). The data services layer consists of data service providers
that provide many services, including data definition, data manipulation and
retrieval, logging, archiving, transactions, and so forth.
The data services components are the requests for services that are initiated by
the DAL components and passed to the data service providers. A data service
provider processes the request and returns a result to the DAL.
In this module, you will learn about the characteristics of these requests as they
pertain to different types of data service providers.
Topic Objective
To provide background
about the business problem.
Lead-in
In this topic, you will learn
about the business problem
presented to designers who
need to implement a data
services layer.
4 Module 10: Data Services
Business Requirements
!
Relational
!
Hierarchical
The data services layer satisfies the requirements of business applications for
data storage and retrieval. Specifically, the requirement is that data services
provide a service beyond simple file I/O to be considered a data service. In
general, two types of services can been identified as data service providers:
back-end relational database servers, such as SQL Server, and hierarchical
directory services that can manage application information, such as Active
Directory. The next two sections of this module will explore these technologies
in more detail.
There are, however, other data service providers that can be used in an
application, including data warehouses such as OLAP (online analytical
processing) Server and electronic mail data stores such as Microsoft Exchange
Server. These types of data stores might not be applicable to a general type of
business application and therefore are not discussed in detail in this course.
In SQL Server 2000, the name OLAP Server has been changed to
Analysis Services.
For more information about using data warehouses, see Course 1502B,
Designing and Implementing a Data Warehouse Using Microsoft SQL Server
7.0. For more information about using workflow, see Course 1593A, Building
Collaborative Solutions by Using Microsoft Outlook 2000 and Course 2017A,
Creating Digital Dashboards.
Mail data stores are applicable to general business applications when
used for sending messages. For example, the Market Purchasing application is
designed to send e-mail messages to managers to let them know that there are
requisitions awaiting approval. However, for simplicity, the Market Purchasing
sample does not currently implement e-mail delivery.
Topic Objective
To provide information
about the business
requirements of data
services.
Lead-in
In this topic, you will learn
about the two types of
business requirements:
relational and hierarchical.
Note
Note
Module 10: Data Services 5
#
##
# SQL Server
!
SQL Server Storage and Retrieval
!
Using SQL Server to Work with Data
!
Data Integrity
!
SQL Server Distribution
The design of physical components for a data services layer can be based on
using a database data service provider such as SQL Server. In this section, the
salient SQL Server features will be covered in detail:
!
SQL Server Storage and Retrieval
!
Using SQL Server to Work with Data
!
Data Integrity
!
SQL Server Distribution
For more information about SQL Server, see Course 832B, System
Administration for Microsoft SQL Server 7.0; Course 833B, Implementing a
Database on Microsoft SQL Server 7.0; and Course 2013A, Optimizing
Microsoft SQL Server 7.0.
Topic Objective
To provide an overview of
the section topics and
objectives.
Lead-in
In this section, you will
review the important
physical design features of a
database data service
provider such as SQL
Server.
6 Module 10: Data Services
SQL Server Storage and Retrieval
!
Relational
!
Denormalization
!
Indexing
!
Hot Spots
!
Archives
Relational
SQL Server is a relational database. This means that information in the database
is represented in tables and that the relationship between the tables is through
common columns in tables. The physical design of a database for an application
is based on the entity relationship diagram (ERD) derived during the logical
design phase of the data services layer.
Based on the logical design, a first version of the table design of a database is
derived. In this design, a table or a column represents each entity and
relationship. While the logical design of a database is concerned with correctly
representing the entities of the enterprise and is a normalized view of the
relationships between the entities, the physical design is concerned with
performance.
Denormalization
The result of the representation of entities and relationships as tables is a highly
normalized table structure. This representation results in excessive joins in
order to retrieve information needed by the DAL. To avoid excessive joins, the
physical design of a database is denormalized: additional columns and rows are
added to tables to avoid the need for joins. Although denormalization increases
the speed of data retrieval, it results in duplicated columns across tables.
Constraints must be imposed to ensure consistency in these columns when
updates are made to any of the tables.
Indexing
To improve the performance of database retrievals and modifications, define
indexes. For optimal performance, base the definition of the indexes on the type
of operations performed on the database and their frequency.
Topic Objective
To provide an overview of
storage and retrieval.
Lead-in
In this topic, you will learn
about design considerations
for the storage and retrieval
of data.
Module 10: Data Services 7
Hot Spots
Hot spots are a key concern in physical database design. Hot spots are pages in
a table that receive an excessive number of access requests as compared to other
pages in the same table. Hot spots are usually a symptom of poor physical
database design. You can avoid hot spots by defining appropriate indexes on
tables.
Archives
Finally, there is a rule that says that 20 percent of the data is accessed 80
percent of the time. Specifically, the 20 percent of the data that is frequently
accessed is the current data. Separating older data, by archiving it, from current
data can enhance the performance of a physical design.
8 Module 10: Data Services
Using SQL Server to Work with Data
!
SQL Statements
$
SELECT
$
INSERT
$
UPDATE
!
Stored Procedures
$
Result set
$
Parameters
$
Return value
!
Triggers
$
INSERT
$
UPDATE
$
DELETE
The DAL presents data retrieval requests and data modification requests to a
data service provider as either SQL statements or stored procedures. While
using SQL statements is more universal and can be applied to different data
service providers, stored procedures offer a performance advantage.
SQL Statements
The SQL statements make up the syntax for retrieval and modification:
SELECT, INSERT, and UPDATE. There is also a DELETE statement, but its
use is highly discouraged. Deleting records can misrepresent the sequence of
events that occurred when a compensating transaction should have been used. It
is appropriate to use the DELETE statement when the business logic demands a
delete, such as when the administrator wants to delete a requestor’s information
from the database.
Stored Procedures
Stored procedures include both SQL Statements and control-of-flow statements
that encapsulate access control logic on the data service provider. Stored
procedures can return two types of results from the data service provider: a row
set (a table-like structure representing the result of a query) or an output
parameter. In general, row sets are more generic and easier to handle. In
addition, stored procedures can return a value indicating if the operation was
successful.
Topic Objective
To provide a review of
working with data.
Lead-in
In this topic, you will learn
the details of the physical
design of working with data.
Không có nhận xét nào:
Đăng nhận xét