Home > Courses Certifications > Data Management > Course 2781 Synopsis

2781 - Designing Microsoft SQL Server 2005 Server-Side Solutions

Prerequisites: Before attending this course, students must:

• Have experience reading user requirements and business-need documents. For example, development project vision/mission statements or business analysis reports.
• Understand Transact-SQL syntax and programming logic.
• Understand XML. Specifically, they must be familiar with the syntax of XML, what elements and attributes are, and how to distinguish them.
• Understand security requirements. Specifically, must understand how unauthorized users can gain access to sensitive information and be able to plan strategies to prevent access.
• Be able to design a database to 3NF and know the tradeoffs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.
• Have basic monitoring and troubleshooting skills.
• Have basic knowledge of the operating system and platform. That is, how the operating system integrates with the database, what the platform or operating system can do, and how interaction between
  the operating system and the database works.
• Have basic knowledge of application architecture. That is, how applications can be designed in three layers, what applications can do, how interaction between the application and the database works, and how the interaction between the database and the platform or operating system works.
• Have some experience with a reporting tool.
• Be familiar with SQL Server 2005 features, tools, and technologies.
• Have a Microsoft Certified Technology Specialist: Microsoft SQL Server 2005 credential, or equivalent experience.

Module 1: Server Solutions with SQL Server 2005

• There is More to SQL Server Than Just SQL Server
• Full-Text Services
• Native XML Web Services
• Replication Concerns
• SQL Server Agent
• New Database Mail
• Notification Services
• Service Broker
• Reporting Services
• SQL Server Integration Services
• T-SQL Enhancements
• .NET CLR
• SQLXML

Module 2: Security – Its More Than Just a Password

• Security Concerns
• Who Are You?
• Preferred Approach Toward Securing Native Web Services
• Preferred Approach Toward Code Access Security
• Preferred Approach Toward Reporting Services Security
• Preferred Approach Toward Notification Services Security
• Preferred Approach Toward Integration Services Security
• Preferred Approach Toward Replication Security
• Preferred Approach Toward Agent and Database Mail Security
• Protect Objects With Roles and Schemas
• Execution Context
• Protect Stored Procedures, Views, and User Defined Functions
• Protect Columns
• How to Audit SQL Server
• Protect your Audit
• Not All Employees Are Equal
• Use DDL Triggers for Audit Information

Module 3: Plan for the Future by Planning for Today

• Using XML Requires Forethought
• XML Has Redundant Data
• How to Store – Character Type vs. New XML Type
• Model Your Database Solution
• Balancing with OLAP
• Rules of Normalization and When
• Can I Index to Often?
• Clustered Indexes on Non-Primary Keys
• Scalability Up or Out
• Spreading the Databases
• Partition Tables and Indexes
• Redundant Solutions with Scaling Out
• Scaling Up

Module 4: Transaction – Concurrency and Consistency

• Exploring Data Phenomenon
• Isolation Levels Options
• Change Data Processing Plans
• Balance Locks and Performance as a Result of Errors
• Accessing Objects
• Rollback Transactions Correctly
• Long Running Transactions
• Hints or Directives

Module 5: Defining a Push Solution with Notification Services

• Where and How to Store Data
• What is an Event Schema
• How does Indexing Help?
• Archiving Events
• Plan Toward a Subscription Schema
• Plan the Rules
• Plan the Subscription Indexes
• Maintain Subscriptions
• Define the Notification
• Plan the Notification Indexes
• Plan Toward Notification Delivery
• Protocol Planning
• Setting Execution Settings

Module 6: Reliable Messaging with Service Broker

• Why Use Service Broker?
• Locate Services
• Locate Conversations
• Plans for Dialogs
• Plan for Queue Usage
• Plan Toward Good Data Flow
• Locate Data Staging
• Locate Routes
• Plan for Activating Methods
• Backup Planning

Module 7: Protect, Test, and Execute

• Protect, Test, and Execute
• Source Control Saves Time and Effort
• Plan for Source Control
• Protect Intellectual Property
• Unit Tests Provide Low Level Confidence
• Planning for Unit Tests
• Perform Iterative Development
• Compare Performance to Known Levels
• Know What To Measure
• Know How To Measure
• Adapting to Change
• Put Services Where They Need to Be
• Plan for Deployment

Module 8: T-SQL Programming Enhancements

• Common Table Expressions
• Performance Gains over Sub-Queries?
• Pivot Queries
• Ranking Queries
• XQueries and FLWORs
• Planning for XQueries
• Follow the Path
• WHERE Matters
• Steps in Converting Data
• To XML…
• …And Back Again