Transcript Mehrschichtige Anwendungen mit SQL Server

Using abstract data layers
in Microsoft SQL Server
Speaker:Uwe Ricken
(db Berater GmbH)
Uwe Ricken
Microsoft SQL Server
db Berater GmbH
Microsoft SQL Server
Microsoft
SQL Server
http://www.db-berater.de
[email protected]
http://db-berater.blogspot.de
https://twitter.com/@dbberater
http://www.xing.com/profile/Uwe_Ricken
Microsoft Certified Master:
SQL Server 2008
Microsoft SQL Server
Microsoft SQL Server
Uwe Ricken
db Berater GmbH
The Microsoft Certified Solutions Master (MCSM) and Microsoft Certified Master
(MCM) programs recognize the most experienced IT professionals who
demonstrate and validate their deep technical skills to successfully design and
implement technical solutions in a complex business environment.
Microsoft Certified Solutions Expert (MCSE) certifications are designed to
recognize IT professionals who can design and build solutions across multiple
technologies, both on-premises and in the cloud.
The Microsoft Most Valuable Professional (MVP) Award is our way of saying
thank you to exceptional, independent community leaders who share their
passion, technical expertise, and real-world knowledge of Microsoft products
with others.
 Multi tier model – common definition
 Multi tier model – development with SQL-Server



Integrity of data
Protection of data
Manipulation of data
 Sample application for reusability of elements


Microsoft Access
Reporting Services
 Multi layered application development mean segregation of the
application and it‘s business logic in multiple logical units
 A multi tier application has at least two separated layers:



data layer (resource layer)
exclusively for storage of data. No direct access to data is allowed!
Business logic (domain logic)
Processing data and calculation of information
User Interface (representation layer)
Visualization of data and input processing
 A multi tier application is not branded by it‘s distribution on
multiple servers but by segregation of each component!
Application A
- Menus
- Forms
- Reports
- Procedures/ Functions
Application B
- Menus
- Forms
- Reports
- Procedures/ Functions
Application C
- Menus
- Forms
- Reports
- Procedures/ Functions
data
Application A
- Menus
- Forms
- Reports
- Procedures/ Functions
Application B
- Menus
- Forms
- Reports
- Procedures/ Functions
Application C
- Menus
- Forms
- Reports
- Procedures/ Functions
Business Layer (C)
Business Layer (D)
consistent interface
between application and
data
Consistent objects in
database which controls
the access to the data.
data
GUI
Business
Microsoft Access, Microsoft Excel, Microsoft Word
.NET, Internet browser, …
external libraries and customized modules
Stored Procedures
Functions
Business
Views
data
data
 Integrity of data is highest priority


controlled access to data is required!
integrity of data should not be controlled by any application!
 reusability of data by multiple applications


unique access layer to all applications
controlled access to resources by standardized access layers
 An application has to support requirements of multiple customers
or business units
 Segregation of tasks within the development process
 application development in a team


experts for database development (i.e. SQL Server, Ora…)
experts for .NET, VB, BI, …
 Multi Tier Elements can be implemented in a Microsoft SQL Server
database by usage of schemes!
 a schema is a unique namespace independent from any user who
created it. This functionality has been implemented first time in
Microsoft SQL Server 2005.
 a schema is a container for objects.
 the ownership of schemes and securable elements can be
transferred.
 objects can be moved between schemes.
 multiple database users can use a single database schema.
 security administration for schemes and it‘s objects is very
granular!
 every schema (except of [sys]) can be the property of any database
principal.
DEMO
 Segregation of GUI / Business / Data
 Separation von



system objects
application objects
customer objects
 NO direct access to the data layer
 data manipulation by Stored Procedures only
LOGINS
Users
demo_db
Schemas
•
•
•
•
Roles
Application
Business
Controls
Data
 Data layer(data model)




tables only!
segregation of data in dedicated schemas depending on their specification:
(data / system data / application / …)
integrity by constraints, triggers and DRI (declarative referential integrity)
=> upstream business rules
no direct access for any user / application!
DEMO
 user data layer

preparation of data from layer 1 for business purposes

Data protection (show granted data only)

Simplification of complex data model from layer 1

BASIC permissions should be SELECT and EXECUTE
DEMO
 Data manipulation

Data can only be manipulated by usage of „resource access“

Only EXECUTE-permission for data manipulation objects!
DEMO
Explore Everything PASS Has to Offer
Free SQL Server and BI Web Events
Free 1-day Training Events
Regional Event
This is Community
Business Analytics Training
Local User Groups Around
the World
Session Recordings
PASS Newsletter
Free Online Technical Training
THANK YOU!
I am looking forward for your
Uwe Ricken
feedback!