1 / 42

Module 10 Designing SQL Server 2008 Components

Module 10 Designing SQL Server 2008 Components. Module Overview. Overview of SQL Server 2008 Components Designing a Service Broker Architecture Designing the Service Broker Data Flow Designing the Service Broker Availability Exploring Full-Text Search

alair
Download Presentation

Module 10 Designing SQL Server 2008 Components

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 10 Designing SQL Server 2008 Components

  2. ModuleOverview Overview of SQL Server 2008 Components Designing a Service Broker Architecture Designing the Service Broker Data Flow Designing the Service Broker Availability Exploring Full-Text Search Designing a Full-Text Search Strategy

  3. Lesson 1: Overview of SQL Server 2008 Components Overview of the SQL Server 2008 Architecture Considerations for Using Database Mail What Is a Distributed Query? Considerations for Using Linked Servers Demonstration: How To Set Up Linked Servers

  4. Overview of the SQL Server 2008 Architecture SQL Server Agent Analysis Services Service Broker Integration Services Database Engine Reporting Services Sample Components of SQL Server 2008 Sample Text

  5. Considerations for Using Database Mail Use Database Mail instead of another e-mail solution while: • Operating with the SQL Server database engine • Using Simple Mail Transfer Protocol (SMTP) instead of Messaging Application Programming Interface (MAPI) • Implementing isolation and robustness • Supporting clusters and 64-bit servers

  6. What Is a Distributed Query? Pass-through Queries Ad-hoc Queries Are not predefined queries and are accessed infrequently Sends commands directly to an ODBC database server Linked Server Enables SQL Server to execute commands against OLE DB data sources on remote servers Distributed queries are queries that access data from multiple heterogeneous data sources Distributed Queries

  7. Considerations for Using Linked Servers Linked servers are advantageous because they can: • Access remote servers • Issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise • Address diverse data sources similarly Usage Considerations • Security Context • Distributed Transactions • Clustered Environments

  8. Demonstration: How To Set Up Linked Servers In this demonstration, you will see how to: Set up linked servers

  9. Lesson 2: Designing a Service Broker Architecture Components of Service Broker Architecture of Service Broker Service Characteristics of a Service Broker Solution Considerations for Implementing a Service Broker Solution Process for Identifying Services Components of Service Broker Conversation Service Broker Conversation Process Considerations for Identifying Conversations Considerations for Designing Dialog Standards Considerations for Designing Queue Usage

  10. Components of Service Broker

  11. Architecture of Service Broker Service ContractProcessExpenses ServiceProcessExpense Contract Message Type SubmitExpense (Initiator) Message Type Service Queue Message Message Type AcceptDenyExpense (Target) Message Message Message Type ReimbursementIssued (Target) Message Service Program

  12. Characteristics of a Service Broker Solution Service Broker components have reliable fault tolerance Service Broker delivers a message only once Service Broker delivers messages in the order that they were sent Service Broker supports asynchronous processing of multiple applications

  13. Considerations for Implementing a Service Broker Solution ServiceBroker Solution Consider the following when implementing a Service Broker solution: • Process tasks asynchronously • Process tasks in a specific order • Wait for another application before processing tasks • Configure databases as broker instances • Ensure fault tolerance

  14. Process for Identifying Services Process tasks in parallel Process tasks independently Service Broker Services Service Broker Services Service Broker Service • A service is a named interface within a Service Broker solution that provides a structure for a set of business tasks Identifying Services

  15. Components of Service Broker Conversation

  16. Service Broker Conversation Process ProcessExpense Contract ExpenseClaim Message Type (Initiator) ClaimResponse Message Type (Target) ProcessExpense Service SubmitExpense Service ExpenseQueue queue ExpenseQueue queue ExpenseClaim message 3 msg msg msg msg ClaimResponse message 8 4 6 7 ExpenseResult Stored Procedure ProcessExpense Stored Procedure 2 5 SubmitExpense Stored Procedure 1 ExpenseSubmission service program activated; conversation ends 8

  17. Considerations for Identifying Conversations Considerations for Identifying Service Broker Conversations • Establish communication between services • Initiate a dialog between services Service Broker Conversations • An application initiates a conversation • A conversation is a named, two-way dialog between two services • The BEGIN DIALOG CONVERSATION statement initiates a conversation • The SEND statement sends a message Service Broker Components

  18. Considerations for Designing Dialog Standards END CONVERSATIONS End conversations manually Maintain conversation sessions Set the time-out period Deliver messages in the correct order

  19. Considerations for Designing Queue Usage Service Broker Solution Considerations for Designing Queue Usage • Store messages for a service • Enable message retention • Process groups of messages • Disable unused queues

  20. Process for Designing the Service Broker Data Flow Considerations for Identifying Conversation Groups Considerations for Identifying Service Routes Considerations for Identifying Service Activation Methods Lesson 3: Designing the Service Broker Data Flow

  21. Process for Designing the Service Broker Data Flow Specify the message order Check contract specifications Define how Service Broker validates messages Specify the message lifetime Identify messages for parallel processing

  22. Considerations for Identifying Conversation Groups Apply locks to conversation groups Determine the message order

  23. Considerations for Identifying Service Routes Store routing information in multiple places Use service route names Expose Service Broker endpoints Use intermediary broker instances to forward messages Use routing to scale out your applications Secure your routes

  24. Considerations for Identifying Service Activation Methods Queue Service Broker External Program Determining Whether to Activate the Queues Activating the Queues Externally

  25. Lesson 4: Designing the Service Broker Availability Designing Service Broker Fault Tolerance Designing a Service Broker Backup Strategy

  26. Designing Service Broker Fault Tolerance Set up database mirrors Create server clusters Service Broker Evaluate the effects on performance

  27. Designing a Service Broker Backup Strategy Back up databases Create a recovery plan to restore data Create Transact-SQL script files Back up and restore external applications

  28. Lesson 5: Exploring Full-Text Search Overview of Full-Text Search Role of the Full-Text Engine Process of Full-Text Indexing and Querying

  29. Overview of Full-Text Search To configure a database for full-text searching: • Create a full-text catalog • Create a full-text index on each table that you want to search Full-text queries can search for: • Simple terms: One or more specific words or phrases • Prefix terms: A word or a phrase where the words begin with specified text • Generation terms: Inflectional forms of a specific word • Proximity terms: A word or phrase close to another word or phrase • Thesaurus: Synonymous forms of a specific word • Weighted terms: Words or phrases using weighted values

  30. Role of the Full-Text Engine Full-Text Engine Indexing Querying

  31. Process of Full-Text Indexing and Querying Full-Text Engine Query Processor Host Full-Text Engine Alert (…) Client Full-Text Portions: SQL Operators Word Breaking Word List Inverted Indexes Full-Text Search Indexing Process Querying Process

  32. Lesson 6: Designing a Full-Text Search Strategy Overview of Full-Text Search Predicates and Functions Considerations for Designing Full-Text Search Predicates and Functions Considerations for Designing Full-Text Search Performance Gathering Information Through Full-Text Search Demonstration: How to Create a Full-Text Catalog Demonstration: How to Perform Full-Text Search

  33. Overview of Full-Text Search Predicates and Functions Full-text predicates: • Return a TRUE or FALSE value • Specify selection criteria for matching rows to a full-text query • CONTAINS searches for precise or fuzzy matches • FREETEXT searches for words matching the word meaning CONTAINS FREETEXT • Referenced in the FROM clause of a SELECT statement • Specify the base table to be full-text searched • The KEY column returns unique values of the returned rows • The RANK column returns a rank value for each row Full-text functions: CONTAINSTABLE FREETEXTTABLE (…) KEY RANK • Combine several search terms to perform logical operations • AND searches for all the terms • OR searches for either of the terms • AND NOT excludes a term from the search Boolean operators:

  34. Considerations for Designing Full-Text Search Predicates and Functions FREETEXT CONTAINS CONTAINSTABLE (…) When designing full-text search predicates and functions, you can: • Search for specific word or phrase • Perform prefix searches • Search for the inflectional form of a specific word • Search for words or phrases close to another word or phrase • Limit ranked result sets FREETEXTTABLE Full-Text Search Functions

  35. Considerations for Designing Full-Text Search Performance • Defragment the index of the base table • Reorganize the full-text catalog • Use a small full-text key column • Use an integer full-text key • Combine multiple CONTAINS predicates • Use CONTAINSTABLE for rank or key information • Use the top_n_by_rank parameter to increase performance • Choose an appropriate join plan for the full-text query • Reorganize full-text catalogs to reduce fragmentation • Frame queries by using a single logical operator • Use query optimizer to exploit predicate or range pushdown

  36. Gathering Information Through Full-Text Search To set up full-text indexing capability: • Create a full-text catalog to store full-text indexes • Create a full-text index on the table or indexed view Choose a filegroupfor a full-text index Assign the full-text index to a full-text catalog Associate a stoplist with the full-text index Update the full-text index

  37. Demonstration: How To Create a Full-Text Catalog In this demonstration, you will see how to: Create a full-text catalog

  38. Demonstration: How To Perform Full-Text Search In this demonstration, you will see how to: Perform full-text search

  39. Lab 10: Designing SQL Server 2008 Components Exercise 1: Analyzing the Organizational Needs Exercise 2: Designing a Service Broker Solution Model Exercise 3: Designing a Detailed Service Broker Solution Exercise 4: Implementing the Service Broker Solution Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd Logon Information Estimated time: 60 minutes

  40. Lab Scenario You are a lead database designer at QuantamCorp. QuantamCorp manufactures and sells metal and composite bicycles to North American, European, and Asian commercial markets. The organization has its base operation with 290 employees at Bothell, Washington, and several regional sales teams throughout their market base. After completing a successful fiscal year, QuantamCorp is now looking to broaden its market share by targeting their sales to their best customers, extending their product availability through an external Web site, and reducing their cost of sales through lower production costs. QuantamCorp wants to ensure that addressing and pricing information is synchronized between the central office and the branch offices. You need to implement processes to handle the following events: Customer address information changes at the branch office. A stored procedure in the branch office database should write the changes to that database and then send a message with the updated address information to the central office database. A stored procedure in the central office database should receive the message, update that database, and then send a message with the updated address information to the other branch office databases. Item pricing information changes at the central office. A stored procedure in the central office database should write changes to that database and then send a message with the updated item pricing information to the branch office databases. The management has decided to invest on SQL Server 2008. In this lab, you will evaluate the business requirements of your organization and identify the appropriate SQL Server services to meet the requirements. In addition, you will implement a Service Broker solution.

  41. Lab Review What are the steps necessary to create a Service Broker solution? What is a poison message? How can you activate a Service Broker? Should you use a single queue or multiple queues to send messages to the branch office databases? How can you prioritize price update messages over address update messages in the branch offices?

  42. Module Review and Takeaways • Review Questions • Real-world Issues and Scenarios • Best Practices for Service Broker

More Related