Designing a Database Strategy

Introduction

The Designing a Database Strategy module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

1. Identifying which SQL Server components to use to support business requirements

2. Designing a database model

3. Designing a data model by using the Entity Framework

4. Designing an application strategy to support security

5. Design a solution by using Service Broker

6. Design a Full-Text Search strategy

Exercise 1 - Identifying which SQL Server components to use to support business requirements

In this exercise, you will perform the tasks required to work with server components.

Exercise 2 - Designing a database model

In this exercise, you will perform the tasks required to design a Database Model.

Exercise 3 - Designing a data model by using the Entity Framework

In this exercise, you will perform the tasks required to implement the Entity Framework Model in Visual Studio.

Exercise 4 - Designing an application strategy to support security

In this exercise, you will perform the tasks required to implement security in SQL Server.

Exercise 5 - Design a solution by using Service Broker

Designing services

SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

The definition of a Service Broker service includes the names of the contracts for which the service is a target. A target service represents an address that accepts requests for the tasks identified by the contracts that the service specifies. An initiating service represents a return address for a conversation with a target service.

A service represents a business process as a distinct set of tasks. Each contract within the service represents a specific task. A service can specify more than one contract, and a contract can be used by more than one service.

Each service uses a queue to store messages. Messages sent to the service are delivered to the queue. In general, applications are easiest to implement when only one service uses a given queue. However, for flexibility, Service Broker allows multiple services to specify the same queue. In this case, the application either treats all messages of the same type the same way, or inspects both the message type name and the service name to determine how to process the message. This strategy can be convenient when an application supports multiple versions of the same service.

To create a service, you must do the following:

• Create message types that define the data that can be sent back and forth.
• Create a contract that identifies the message types that can be used, and which endpoint can send them, in order to accomplish a particular task.
• Create an application to receive, process, and send messages as necessary to accomplish the given task.
• Create a queue to store the incoming messages for the service. You may associate the queue with an activation stored procedure so that the broker automatically activates the stored procedure to process messages as messages arrive.
• Create a service and associate it with the queue that will receive the messages for the service. The service exposes the contracts that define the tasks that the service will perform on behalf of other services. The service does not need to specify contracts for tasks that the service requests from other services.

Exercise 6 - Design a Full-Text Search strategy

CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE

Full-Text Search helps to perform complex queries against character data. These queries can include word or phrase searching. You can create a full-text index on a table or an indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. This feature works with the RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions.

To create an Index, perform the following steps:

1. Create a Full-Text Catalog
2. Create a Full-Text Index
3. Populate the Index