Designing Policy Based Management in Microsoft SQL Server 2008

2/18/2009

Contents

Chapter Review

To practice and reinforce the skills you learned in this chapter further, you can perform the following tasks:

Review the chapter summary.
Review the list of key terms introduced in this chapter.
Complete the case scenario. The scenario sets up a real-world situation involving the topics in this chapter and asks you to create a solution.
Complete the suggested practices.
Take a practice test.

Chapter Summary

Facets are the .NET assemblies that define the set of properties for an object upon which conditions are built.
A condition can be defined for a single facet and a policy can be checked for a single instance.
Policies can be checked manually or automatically. Automatic policy checking can be performed on a scheduled basis or by using the event notification infrastructure.
A database owner can subscriber a database to one or more policies; however, a policy that belongs to a policy category set with the Mandate property requires checking against all databases.

Key Terms

Do you know what these key terms mean? You can check your answers by looking up the terms in the glossary at the end of the book.

Condition
Facet
Policy category
Policy target

Case Scenario

In the following case scenario, you apply what you’ve learned in this chapter. You can find answers to these questions in the “Answers” section at the end of this book.

Case Scenario: Designing a Management Strategy for Coho Vineyard

Background

Company Overview

Coho Vineyard was founded in 1947 as a local, family-run winery. Due to the award-winning wines it has produced over the last several decades, Coho Vineyards has experienced significant growth. To continue expanding, several existing wineries were acquired over the years. Today, the company owns 16 wineries; 9 wineries are in Washington, Oregon, and California, and the remaining 7 wineries are located in Wisconsin and Michigan. The wineries employ 532 people, 162 of whom work in the central office that houses servers critical to the business. The company has 122 salespeople who travel around the world and need access to up-to-date inventory availability.

Planned Changes

Until now, each of the 16 wineries owned by Coho Vineyard has run a separate Web site locally on the premises. Coho Vineyard wants to consolidate the Web presence of these wineries so that Web visitors can purchase products from all 16 wineries from a single online store. All data associated with this Web site will be stored in databases in the central office.

When the data is consolidated at the central office, merge replication will be used to deliver data to the salespeople as well as to allow them to enter orders. To meet the needs of the salespeople until the consolidation project is completed, inventory data at each winery is sent to the central office at the end of each day.

Management wants to ensure that you cannot execute stored procedures written in C#.NET or use the OPENROWSET or OPENDATASOURCE command.

Existing Data Environment

Databases

Each winery presently maintains its own database to store all business information. At the end of each month, this information is brought to the central office and transferred into the databases shown in Table 8-1.

TABLE 8-1 Coho Vineyard Databases

DATABASE	SIZE
Customer	180 megabytes (MB)
Accounting	500 MB
HR	100 MB
Inventory	250 MB
Promotions	80 MB

After the database consolidation project is complete, a new database named Order will serve as a data store to the new Web store. As part of their daily work, employees also will connect periodically to the Order database using a new in-house Web application.

The HR database contains sensitive data and is protected using Transparent Data Encryption (TDE). In addition, data in the Salary table is encrypted using a certificate.

Database Servers

A single server named DB1 contains all the databases at the central office. DB1 is running SQL Server 2008 Enterprise on Windows Server 2003 Enterprise.

Business Requirements

You need to design an archiving solution for the Customer and Order databases. Your archival strategy should allow the Customer data to be saved for six years.

To prepare the Order database for archiving procedures, you create a partitioned table named Order.Sales. Order.Sales includes two partitions. Partition 1 includes sales activity for the current month. Partition 2 is used to store sales activity for the previous month. Orders placed before the previous month will be moved to another partitioned table named Order. Archive. Partition 1 of Order.Archive includes all archived data. Partition 2 remains empty.

A process needs to be created to load the inventory data from each of the 16 wineries by 4 A.M. daily.

Four large customers submit orders using Coho Vineyards Extensible Markup Language (XML) schema for Electronic Data Interchange (EDI) transactions. The EDI files arrive by 5 P.M. and need to be parsed and loaded into the Customer, Accounting, and Inventory databases, which each contain tables relevant to placing an order. The EDI import routine is currently a single threaded C++ application that takes between three and six hours to process the files. You need to finish the EDI process by 5:30 P.M. to meet your Service Level Agreement (SLA) with the customers. After the consolidation project finishes, the EDI routine loads all data into the new Order database.

You need to back up all databases at all locations. All production databases are required to be configured with the Full recovery model. You can lose a maximum of five minutes of data under a worst-case scenario. The Customer, Account, Inventory, Promotions, and Order databases can be off-line for a maximum of 20 minutes in the event of a disaster. Data older than six months in the Customer and Order databases can be off-line for up to 12 hours in the event of a disaster.

Answer the following question.