Microsoft SQL Server 2008 R2 Specifications

ContentsContents at a Glance vContents viiAcknowledgments xvIntroduction xviiWho Is This Book For? . . . . . . . . . . . . . . . . . . . . . . . . .

x Contents PART II BUSINESS INTELLIGENCE DEVELOPMENTCHAPTER 6 Scalable Data Warehousing 109Parallel Data Warehouse Architecture . . . . . . . .

80 CHAPTER 4 High Availability and Virtualization Enhancements 4. Under Automatic Start Action, for the What Do You Want This Virtual Machine To

Implementing Live Migration for SQL Server 2008 R2 CHAPTER 4 81 11. On the Select Service Or Application page, shown in Figure 4-9, click Virtua

82 CHAPTER 4 High Availability and Virtualization Enhancements NOTE To make a VM highly available, you must ensure that it is not running. It must

Implementing Live Migration for SQL Server 2008 R2 CHAPTER 4 83Initiating a Live Migration of a SQL Server VMAfter an administrator has enabled CS

85C H A P T E R 5Consolidation and MonitoringToday's competitive economy dictates that organizations reduce cost and improve agility in t

86 CHAPTER 5 Consolidation and MonitoringConsolidating Databases and InstancesA very common SQL Server consolidation strategy involves placing many

SQL Server Consolidation Strategies CHAPTER 5 87SQLInstance01SQLInstance02SQLInstance03 FIGURE 5-2 Consolidating many databases onto a single ph

88 CHAPTER 5 Consolidation and Monitoringand 64-bit versions within a single host. In addition, physical SQL Servers can easily be virtual-ized by

Using the SQL Server Utility for Consolidation and Monitoring CHAPTER 5 89Using the SQL Server Utility for Consolidation and MonitoringThe SQL Ser

Contents xiIntegration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135Importing Mast

90 CHAPTER 5 Consolidation and MonitoringThe three main dashboards for monitoring and managing resource utilization and consoli-dation efforts are

Using the SQL Server Utility Dashboard CHAPTER 5 91The SQL Server Utility dashboard includes the following information: Utility Summary Found i

92 CHAPTER 5 Consolidation and Monitoring● No Data Available Either data has not been uploaded from a managed instance or there is a problem with

Using the SQL Server Utility Dashboard CHAPTER 5 93 Data-Tier Application Health This section is located in the top-right corner of the Utility

94 CHAPTER 5 Consolidation and Monitoring● Overutilized Database Files This represents the number of deployed data-tier applications with databas

Using the Managed Instances Viewpoint CHAPTER 5 95This section explained how to obtain summary information for all managed instances of SQL Serve

96 CHAPTER 5 Consolidation and MonitoringResource utilization for each managed instance of SQL Server is presented in the list view located at the

Using the Managed Instances Viewpoint CHAPTER 5 97 Volume Space Volume space utilization is summarized in this column for volumes with databas

98 CHAPTER 5 Consolidation and MonitoringFIGURE 5-8 The Storage Utilization tab on the Managed Instances viewpointIndependent of how the les are

Using the Managed Instances Viewpoint CHAPTER 5 99FIGURE 5-9 The Policy Details tab on the Managed Instances viewpointNOTE To override the glob

xii Contents CHAPTER 9 Reporting Services Enhancements 165New Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

100 CHAPTER 5 Consolidation and MonitoringFIGURE 5-10 The Property Details tab on the Managed Instances viewpointUsing the Data-Tier Application V

Using the Data-Tier Application Viewpoint CHAPTER 5 101FIGURE 5-11 The data-tier application viewpointResource utilization for each deployed dat

102 CHAPTER 5 Consolidation and MonitoringThe Data-Tier Application List ViewThe columns presenting the state of health for each deployed data-tier

Using the Data-Tier Application Viewpoint CHAPTER 5 103Two linear graphs are presented next to each other. The rst graph shows CPU utiliza-tion

104 CHAPTER 5 Consolidation and Monitoring Policy Details The Policy Details tab, shown in Figure 5-13, is where a DBA can view the global polic

Using the Data-Tier Application Viewpoint CHAPTER 5 105The display is broken up into the following four policies, which can be viewed or overridd

PART IIBusiness Intelligence DevelopmentStacia MiSnerCHAPTER 6 Scalable Data Warehousing 109CHAPTER 7 Master Data Services 125CHAPTER 8 Complex E

109C H A P T E R 6Scalable Data WarehousingMicrosoft SQL Server 2008 R2 Parallel Data Warehouse is an enterprise data ware-house appliance bas

Contents xiiiCHAPTER 10 Self-Service Analysis with PowerPivot 189PowerPivot for Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

110 CHAPTER 6 Scalable Data WarehousingWarehouse software. When the assembly process is complete, the vendor ships the appliance to you using shock

Parallel Data Warehouse Architecture CHAPTER 6 111Control rack Data rackManagement nodeactive/passiveUser queriesControl nodeactive/passiveLanding

112 CHAPTER 6 Scalable Data WarehousingThe Control RackThe control rack is a separate rack that houses the servers, storage, and networking com-pon

Parallel Data Warehouse Architecture CHAPTER 6 113Development Studio, SQL Server Integration Services, SQL Server Analysis Services, and SQL Serve

114 CHAPTER 6 Scalable Data WarehousingThe Landing Zone NodeThe Landing Zone is a high-capacity data storage node in the control rack that contains

Data Management CHAPTER 6 115any necessary data to each compute node so that it can process the query in parallel with other compute nodes without

116 CHAPTER 6 Scalable Data WarehousingYou design the data layout on the appliance to avoid or minimize data movement for par-allel queries by usin

Data Management CHAPTER 6 117nodes in the appliance. There are performance considerations for the selection of a distribution column, such as dist

118 CHAPTER 6 Scalable Data WarehousingCREATE DATABASEThe CREATE DATABASE statement has a set of options for supporting distributed and repli-cated

Data Management CHAPTER 6 119NOTE Parallel Data Warehouse does not use the Transact-SQL partition schema or parti-tion function. Also, you can cr

120 CHAPTER 6 Scalable Data WarehousingIn addition, you can use a CREATE TABLE AS SELECT statement to create a table from the results of a SELECT s

Data Management CHAPTER 6 121Query ProcessingQuery processing in Parallel Data Warehouse is more complex than in an SMP data ware-house because pr

122 CHAPTER 6 Scalable Data WarehousingData Warehouse’s Landing Zone. You then invoke a command-line tool, DWLoader, and spec-ify options to load t

Business Intelligence Integration CHAPTER 6 123Business Intelligence IntegrationParallel Data Warehouse integrates with the SQL Server business in

125C H A P T E R 7Master Data ServicesMicrosoft SQL Server 2008 R2 Master Data Services (MDS) is another new technology in the SQL Server fami

126 CHAPTER 7 Master Data ServicesThis scenario presents additional problems for operational master data in an organiza-tion because there is no co

Master Data Services Components CHAPTER 7 127In addition to offering exibility, MDS allows you to manage master data proactively. Instead of disc

128 CHAPTER 7 Master Data ServicesMaster Data Services Conguration ManagerBefore you can start using MDS to manage your master data, you use Maste

Data Stewardship CHAPTER 7 129 Integration Management Use this area to create and process batches for im-porting data from staging tables into

xvAcknowledgmentsI would like to rst acknowledge Shirmattie Seenarine for assisting me on this title. I couldn't have written this book wi

130 CHAPTER 7 Master Data ServicesAn entity can also have any number of domain-based attributes whose values are mem-bers of another related entity

Data Stewardship CHAPTER 7 131FIGURE 7-3 A collectionMaster Data MaintenanceMaster Data Manager is more than a place to dene model objects. It a

132 CHAPTER 7 Master Data ServicesFIGURE 7-5 Attributes and validation issuesBusiness Rules One of the goals of a master data management system is

Data Stewardship CHAPTER 7 133FIGURE 7-7 The Required Fields business ruleWhen creating a business rule, you can use any of the following types o

134 CHAPTER 7 Master Data ServicesTransaction LoggingMDS uses a transaction log, as shown in Figure 7-9, to capture every change made to master dat

Integration CHAPTER 7 135IntegrationMaster Data Manager also provides support for data integration between MDS and other ap-plications. Master Dat

136 CHAPTER 7 Master Data Servicesselected. When the batch processing is complete, you can review the status of the batch in the staging batch log,

Administration CHAPTER 7 137subscription view in Master Data Manager as an entity-based leaf member view, you can query the Product view and see t

138 CHAPTER 7 Master Data Servicescreate a new version by copying a previously committed version and allowing users to make their changes to the ne

Administration CHAPTER 7 139Figure 7-15. An administrator with full access privileges would instead see the full list of func-tional areas on the

xvi And last but not least, I would like to thank my Microsoft mentors who assisted with my career development and transition to the Microsoft Tec

140 CHAPTER 7 Master Data ServicesColor table on the right side of the page. These icons indicate that the values in the table are not editable. Th

Administration CHAPTER 7 141More specically, the security conguration allows this user to edit only the Bikes and Ac-cessories categories in the

142 CHAPTER 7 Master Data ServicesModel DeploymentWhen you have nalized the master data model structure, you can use the model deploy-ment capabil

Programmability CHAPTER 7 143 Microsoft.MasterDataServices.Services Contains a class to provide instances of the MdsServiceHost class and a cla

144 CHAPTER 7 Master Data Services Mdq.RegexIsValid Indicates whether the regular expression is valid Mdq.RegexMask Converts a set of regular

145C H A P T E R 8Complex Event Processing with StreamInsightMicrosoft SQL Server StreamInsight is a complex event processing (CEP) engine. Th

146 CHAPTER 8 Complex Event Processing with StreamInsightSimilarly, there are certain types of applications that benet from the ability to analyze

StreamInsight Architecture CHAPTER 8 147Data StructuresThe high-throughput data that StreamInsight requires is known as a stream. More speci-call

148 CHAPTER 8 Complex Event Processing with StreamInsightPagers and monitoring devicesInput AdaptersData feeds Event storesand databasesWeb servers

StreamInsight Architecture CHAPTER 8 149Output AdaptersThe output adapters reverse the operations of the input adapters by translating events into

xviiIntroductionOur purpose in Introducing Microsoft SQL Server 2008 R2 is to point out both the new and the improved in the latest version of S

150 CHAPTER 8 Complex Event Processing with StreamInsightIf you choose to deploy CEP as a standalone server, there are some limitations that affect

Application Development CHAPTER 8 151Server database must adapt to the schema of the table that it queries. Instead, you provide the table schema

152 CHAPTER 8 Complex Event Processing with StreamInsightthe elds one at a time and enqueue the event. The untyped output adapter works similarly,

Application Development CHAPTER 8 153The nal step is to create a .NET assembly for the adapter. At minimum, the adapter includes a constructor, a

154 CHAPTER 8 Complex Event Processing with StreamInsightAnother task the adapter must perform is classication of an event. That is, the adapter m

Application Development CHAPTER 8 155QueriesAfter you create an event stream object, you write a LINQ expression on top of the event stream object

156 CHAPTER 8 Complex Event Processing with StreamInsightEvent WindowsA window represents a subset of data from an event stream for a period of tim

Application Development CHAPTER 8 157As you might guess, the key to working with windows is to have a clear understanding of the time span that ea

158 CHAPTER 8 Complex Event Processing with StreamInsightwindows stream. Although you can use the HoppingWindow method to create tumbling win-dows,

Application Development CHAPTER 8 159Count windows are completely different from the other window types because the size of the windows is variabl

xviii Introduction are improved with the introduction of the SQL Server Utility Control Point. Step-by-step instructions show DBAs how to quickly de

160 CHAPTER 8 Complex Event Processing with StreamInsightAssume you want to apply the Sum and Avg aggregations to eld x in an input stream. The fo

Application Development CHAPTER 8 161JoinsYou can use a join operation to match events from two streams. The CEP server rst matches events only i

162 CHAPTER 8 Complex Event Processing with StreamInsightQuery Template BindingThe method that the CEP server uses to instantiate the query templat

The Management Interface CHAPTER 8 163The Query ObjectIn both the explicit and implicit development models, you create a query object. With that o

164 CHAPTER 8 Complex Event Processing with StreamInsightWindows PowerShell DiagnosticsFor quick analysis, you can use Windows PowerShell scripts t

165C H A P T E R 9Reporting Services EnhancementsIf you thought Microsoft SQL Server 2008 Reporting Services introduced a lot of great new fe

166 CHAPTER 9 Reporting Services Enhancements Combining Data from More Than One DatasetTo display data from more than one source in a table (or in

Expression Language Improvements CHAPTER 9 167=Lookup(Fields!StateProvinceCode.Value, Fields!StProv.Value, Fields!StProvName.Value, "Dataset1

168 CHAPTER 9 Reporting Services Enhancements When there is a one-to-many relationship between the source and destination values, you use the Looku

Expression Language Improvements CHAPTER 9 169FIGURE 9-4 Aggregation of an aggregationHere is the expression for the value displayed in the Month

Introduction xixNew” topic in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/bb500435(SQL.105).aspx for the most up-to-date l

170 CHAPTER 9 Reporting Services Enhancements Another option is to use the RenderFormat global variable with the IsInteractive member to set the co

Layout Control CHAPTER 9 171FIGURE 9-6 Changing report variablesTo write to your report variable, you use the SetValue method of the variable. Fo

172 CHAPTER 9 Reporting Services Enhancements Pagination PropertiesThere are three new properties available to manage pagination: Disabled, ResetPa

Layout Control CHAPTER 9 173Last, consider how you can use the PageName property. As one example, instead of using page numbers in an Excel workbo

174 CHAPTER 9 Reporting Services Enhancements FIGURE 9-9 Synchronized groupsText Box OrientationEach text box has a WritingMode property that by d

Data Visualization CHAPTER 9 175Data VisualizationPrior to SQL Server 2008 R2 Reporting Services, your only option for enhancing a report with dat

176 CHAPTER 9 Reporting Services Enhancements SparklinesLike data bars, sparklines can be used to include a data visualization alongside the detail

Data Visualization CHAPTER 9 177FIGURE 9-13 Indicator typesAfter selecting a set of indicators, you associate the set with a value in your datase

178 CHAPTER 9 Reporting Services Enhancements Although you can manually congure the properties for the map and each map layer, the easiest way to

Reusability CHAPTER 9 179Shared DatasetsA shared dataset allows you to dene a query once for reuse in many reports, much as you can create a shar

PUBLISHED BYMicrosoft PressA Division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399Copyright © 2010 by Microsoft Corporation

180 CHAPTER 9 Reporting Services Enhancements Services can respond to a report request faster, and users are generally happier with the reporting s

Reusability CHAPTER 9 181You can publish report parts both from Report Builder 3.0 and Report Designer in Business Intelligence Development Studio

182 CHAPTER 9 Reporting Services Enhancements Although you can publish report parts in Report Designer and Report Builder 3.0, you can only use Rep

Report Builder 3.0 CHAPTER 9 183Report Builder 3.0Report Builder 1.0 was the rst release of a report development tool targeted for business users

184 CHAPTER 9 Reporting Services Enhancements FIGURE 9-19 The Report Part GalleryReport Access and ManagementIn this latest release of Reporting S

Report Access and Management CHAPTER 9 185FIGURE 9-20 Report ViewerNotice also that the Report Viewer does not include a link to open the report

186 CHAPTER 9 Reporting Services Enhancements Report Viewer ImprovementsThe display of reports is also improved in the Report Viewer available in t

SharePoint Integration CHAPTER 9 187SharePoint IntegrationSQL Server 2008 R2 Reporting Services continues to improve integration with SharePoint.

188 CHAPTER 9 Reporting Services Enhancements create the data source using the Microsoft SharePoint List connection type and provide credentials fo

189C H A P T E R 1 0Self-Service Analysis with PowerPivotMany business intelligence (BI) solutions require access to centralized, cleansed dat

PART IDatabase AdministrationRoss MistRyCHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3CHAPTER 2 Multi-Server Administration 21CHAPTER

190 CHAPTER 10 Self-Service Analysis with PowerPivotPowerPivot for ExcelPowerPivot for Excel is an add-in that extends the functionality of Excel 2

PowerPivot for Excel CHAPTER 10 191The Atom Data Feed ProviderLast, the add-in installs an Atom data feed provider to allow you to import data fro

192 CHAPTER 10 Self-Service Analysis with PowerPivot• IBM DB2 8.1• Sybase• Any database that can be accessed by using an OLE DB provider or an O

PowerPivot for Excel CHAPTER 10 193Linked TablesIf your data is in an Excel table already, or if you convert a range of data into an Excel table,

194 CHAPTER 10 Self-Service Analysis with PowerPivotRelationshipsBy building relationships between the data, you can analyze the data as if it all

PowerPivot for Excel CHAPTER 10 195FIGURE 10-5 Filtering a numeric column by valueIMPORTANT Use of a lter is not a security measure. Although a

196 CHAPTER 10 Self-Service Analysis with PowerPivotYou can use the Hide and Unhide button on the Design tab (shown in Figure 10-4) to control the

PowerPivot for Excel CHAPTER 10 197store the data that you selected for the chart. Just as you do with a standard PivotTable or PivotChart, you se

198 CHAPTER 10 Self-Service Analysis with PowerPivotFIGURE 10-9 The CUBEVALUE functionSlicersThe task pane for PowerPivot is similar to the one yo

PowerPivot for Excel CHAPTER 10 199Data Analysis ExpressionsThe ability to combine data from multiple sources into a single PivotTable is amazingl

200 CHAPTER 10 Self-Service Analysis with PowerPivotreport, provide a name for the current PivotTable if you want, and then specify the formula for

PowerPivot for SharePoint CHAPTER 10 201FUNCTION TYPE EXAMPLE DESCRIPTIONStatistical=AVERAGEX(ResellerSales, [SalesAmount]- [TotalProductCost])Eva

202 CHAPTER 10 Self-Service Analysis with PowerPivotSharePoint FarmWeb front end Application serverPowerPivot databaseAnalysis Services-VertiPaq Mo

PowerPivot for SharePoint CHAPTER 10 203balancing across servers for query processing if multiple servers are available. Furthermore, the PowerPiv

204 CHAPTER 10 Self-Service Analysis with PowerPivotContent ManagementContent management for PowerPivot is quite simple because the data and the pr

PowerPivot for SharePoint CHAPTER 10 205or right arrow to bring a different thumbnail into the preview area. You can also switch to All Documents

206 CHAPTER 10 Self-Service Analysis with PowerPivotapplication that can connect to Analysis Services directly can use the PowerPivot Web Service.

Index 207Aadapter base classes, 151AdapterFactory objects, 152adapters, for CEP applications, 151-154Admin Console, 122aggregate functions, 168AJ

208 data feed libraries, 205Data Movement Service (DMS), 112data racks, 111data sourcesjoining, 166-168for PowerPivot for Excel, 191-193data stewar

209guest model, 67-68history of, 64-65traditional model, 65troubleshooting, 70validating prerequisites for, 68-70feedback on book, xixle space

3C H A P T E R 1SQL Server 2008 R2 Editions and EnhancementsMicrosoft SQL Server 2008 R2 is the most advanced, trusted, and scalable data plat

210 overutilized resources, 92processor utilization, 96underutilized resources, 92viewing, 91Managed Instances viewpoint (Utility Explorer), 95-100

211Parallel Data WarehouseAdmin Console, 122architecture of, 109-115automatic growth feature, toggling, 118conguring, 110control node, 112-113c

212 reportsalternate access mappings, 187cache conguring, 179-180on DAC deployment, 54data synchronization, 173data visualization enhancements, 17

213Theater view (PowerPivot Gallery), 204-205Time Intelligence functions (PowerPivot), 193TopK operation, 160transaction logsfor MDS, 131-134spa

214 virtualization. See also Hyper-Vconsolidation with, 87-88technology for, 72 Visual Studio 2010deploying DACs from, 45-46importing DACs into, 47

215About the AuthorsRoss Mistry is a technical architect at the Microsoft Technology Center (MTC) in Silicon Valley. Ross provides executive bri

Stay in touch!To subscribe to the Microsoft Press® Book Connection Newsletter—for news on upcoming books, events, and special offers—please visit: Wha

4 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsMicrosoft has made major investments in the SQL Server product as a whole; however, the ne

SQL Server 2008 R2 Enhancements for DBAs CHAPTER 1 5the total cost of ownership of their database environment. The new SQL Server Utility dashboar

6 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsFIGURE 1-2 Identifying consolidation opportunities with the SQL Server Utility dashboard

SQL Server 2008 R2 Enhancements for DBAs CHAPTER 1 7environment. This can be done at scale, with information on resource utilization throughout th

8 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsIn the example in Figure 1-4, a DBA has optimized hardware resources within the environ-me

SQL Server 2008 R2 Enhancements for DBAs CHAPTER 1 9spoke (control node and compute nodes) architecture. Performance improvements can be attained

I dedicate this book to my wife and children, who make it all worthwhile.—Ross MistRyI dedicate this book to my husband and best friend, Gerry.—stacia

10 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsPowerPivot data access in the farm. This new approach promises better integration with Sha

SQL Server 2008 R2 Editions CHAPTER 1 11 Hyper-V improvements Building on the approval and success of the original Hyper-V release, Windows Ser

12 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsPremium EditionsThe premium editions of SQL Server 2008 R2 are meant to meet the highest d

SQL Server 2008 R2 Editions CHAPTER 1 13 Standard SQL Server 2008 R2 Standard is a complete data management and BI platform that provides mediu

14 CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements Compact SQL Server 2008 R2 Compact is typically used to develop mobile and small deskt

Hardware and Software Requirements CHAPTER 1 15HARDWARE COMPONENT REQUIREMENTSDisk Space Database Engine: 280 MBAnalysis Services: 90 MBReporting

16 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsInstallation, Upgrade, and Migration StrategiesLike its predecessors, SQL Server 2008 R2 i

Installation, Upgrade, and Migration Strategies CHAPTER 1 17In-Place Upgrade Pros and ConsThe in-place upgrade strategy is usually easier and cons

18 CHAPTER 1 SQL Server 2008 R2 Editions and EnhancementsSide-by-Side MigrationThe term side-by-side migration describes the deployment of a brand-

Installation, Upgrade, and Migration Strategies CHAPTER 1 19However, there are disadvantages to the side-by-side strategy. Additional hardware mig

21C H A P T E R 2Multi-Server AdministrationOver the years, an increasing number of organizations have turned to Microsoft SQL Server because

22 CHAPTER 2 Multi-Server AdministrationSQL Server Utility. It forms a collection of managed instances with a repository for perfor-mance data and

The SQL Server Utility CHAPTER 2 23REAL WORLDMany organizations that participate in the Microsoft SQL Server early adopter program are currently e

24 CHAPTER 2 Multi-Server Administration The Utility Explorer user interface A component of SSMS, this interface provides a hierarchical tree vi

The SQL Server Utility CHAPTER 2 25UCP PrerequisitesAs with other SQL Server components and features, the deployment of a SQL Server UCP must meet

26 CHAPTER 2 Multi-Server AdministrationCreating a UCPThe UCP is relatively easy to set up and congure. You can deploy it either by using the Crea

Creating a UCP CHAPTER 2 27 5. On the Specify The Instance Of SQL Server page, click the Connect button to specify the instance of SQL Server in

28 CHAPTER 2 Multi-Server Administration 8. On the next page, the SQL Server instance is compared against a series of prerequisites before the UC

Enrolling SQL Server Instances CHAPTER 2 29UCP Post-Installation StepsWhen the Create Utility Control Point Wizard is closed, the Utility Explorer

Contents at a GlanceIntroduction xviiPART I DATABASE ADMINISTRATIONCHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3CHAPTER 2 Multi-Server

30 CHAPTER 2 Multi-Server AdministrationManaged Instance Enrollment Prerequisites As with many of the other tasks in this chapter, certain conditio

Enrolling SQL Server Instances CHAPTER 2 31 8. As shown in Figure 2-4, a series of conditions will be evaluated against the SQL Server instance

32 CHAPTER 2 Multi-Server AdministrationEnrolling SQL Server Instances by Using Windows PowerShellWindows PowerShell can also be used to enroll in

Managing Utility Administration Settings CHAPTER 2 33FIGURE 2-5 The Managed Instances dashboardManaging Utility Administration SettingsAfter you

34 CHAPTER 2 Multi-Server Administration 3. On the Utility Explorer toolbar, click the Connect To Utility icon. 4. In the Connect To Server dia

Managing Utility Administration Settings CHAPTER 2 35FIGURE 2-6 Modifying global policies for managed instancesVolatile Resource Policy Evaluati

36 CHAPTER 2 Multi-Server AdministrationFIGURE 2-7 Volatile resource policy evaluationThe next set of congurable elements allows you to determine

Managing Utility Administration Settings CHAPTER 2 37The Security TabFrom a security and authorization perspective, there are two security roles a

38 CHAPTER 2 Multi-Server Administration 3. On the General page, enter the name of a Windows user in the Login Name box. 4. Select Windows Auth

Managing Utility Administration Settings CHAPTER 2 39REAL WORLD Many organizations have large teams managing their SQL Server infrastructures beca

40 CHAPTER 2 Multi-Server Administration 4. In the Utility Explorer Content pane, select the desired data retention period for the UMDW, as displ

41C H A P T E R 3Data-Tier ApplicationsAsk application developers or database administrators what it was like to work with data-driven applica

42 CHAPTER 3 Data-Tier ApplicationsThe Data-Tier Application Life CycleThere are two common methods for generating a DAC. One is to author and buil

Introduction to Data-Tier Applications CHAPTER 3 43Data-tier developers using a data-tier application project template in Visual Studio 2010 rst

44 CHAPTER 3 Data-Tier ApplicationsReal WorldOrganizations looking to accelerate and standardize deployment of database applications within their d

Visual Studio 2010 and Data-Tier Application Projects CHAPTER 3 45 Type: User-dened Data Type Type: User-dened Table Type User ViewDatab

46 CHAPTER 3 Data-Tier Applications 4. In the Project Template pane, select Data-Tier Application. 5. Specify the name, location, and solution

Visual Studio 2010 and Data-Tier Application Projects CHAPTER 3 47FIGURE 3-3 The Create Table schema and the Solution Explorer pane in a Visual S

48 CHAPTER 3 Data-Tier Applications 4. Review the information on the Welcome page, and then click Next. 5. On the Specify Import Options page,

Extracting a Data-Tier Application with SQL Server Management Studio CHAPTER 3 49Extracting a Data-Tier Application with SQL Server Management Stu

What do you think of this book? We want to hear from you! Microsoft is interested in hearing your feedback so we can continually improve our books an

50 CHAPTER 3 Data-Tier Applications Description This property is optional. Use it to describe the DAC. If this section is completed, the informa

Extracting a Data-Tier Application with SQL Server Management Studio CHAPTER 3 51conrms that the information is supported by the DAC, and display

52 CHAPTER 3 Data-Tier ApplicationsInstalling a New DAC Instance with the Deploy Data-Tier Application WizardAfter the DAC package has been create

Installing a New DAC Instance with the Deploy Data-Tier Application Wizard CHAPTER 3 53FIGURE 3-7 Specifying a DAC package to deploy with the De

54 CHAPTER 3 Data-Tier Applications 9. The next page includes a summary of the settings that are used to deploy the data-tier application. Review

Registering a Data-Tier Application CHAPTER 3 55NOTE Throughout this chapter, you can also use Windows PowerShell scripts in con-junction with da

56 CHAPTER 3 Data-Tier Applications 5. On the Set Properties page, complete the DAC properties by typing in the application name, version, and de

Deleting a Data-Tier Application CHAPTER 3 57 Delete Database The DAC metadata and the associated database are dropped. The data and log les a

58 CHAPTER 3 Data-Tier Applications 6. Review the information displayed in the Summary page, as shown in Figure 3-10.FIGURE 3-10 Viewing the Sum

Upgrading a Data-Tier Application CHAPTER 3 59Upgrading a Data-Tier ApplicationLet us recall the past for a moment, when updating changes to exist

viii Contents Creating a UCP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Creating a UCP

60 CHAPTER 3 Data-Tier Applications 6. On the Select Package page, specify the DAC package that contains the new DAC version to upgrade to. Alter

Upgrading a Data-Tier Application CHAPTER 3 61NOTE If the database has changed, it is a best practice to review the potential data losses before

62 CHAPTER 3 Data-Tier ApplicationsFIGURE 3-12 Reviewing the result information on the Upgrade DAC pageNOTE Data-tier applications are a large an

63C H A P T E R 4High Availability and Virtualization EnhancementsMicrosoft SQL Server 2008 R2 delivers several enhancements in the areas of

64 CHAPTER 4 High Availability and Virtualization Enhancements Windows Server 2008 R2 Hyper-V The Hyper-V virtualization technology im-provemen

Failover Clustering with Windows Server 2008 R2 CHAPTER 4 65including Internet Information Services (IIS), Cluster Server, SQL Server 7.0 Enterpri

66 CHAPTER 4 High Availability and Virtualization Enhancements Figure 4-1 illustrates a two-node single-instance failover cluster running SQL Serve

Failover Clustering with Windows Server 2008 R2 CHAPTER 4 67Guest Failover ClusteringIn the past, physical servers were usually afliated with the

68 CHAPTER 4 High Availability and Virtualization Enhancements NOTE Guest clustering is also supported when Hyper-V is on Windows Server 2008. How

Failover Clustering with Windows Server 2008 R2 CHAPTER 4 69• List Cluster Volumes• List Cluster Services And Applications• Validate Quorum Con

Contents ixCHAPTER 4 High Availability and Virtualization Enhancements 63Enhancements to High Availability with Windows Server 2008 R2 . . . . .

70 CHAPTER 4 High Availability and Virtualization Enhancements 6. On the Conrmation page, review the details for each test, and then click Next

Failover Clustering with Windows Server 2008 R2 CHAPTER 4 71The Windows Server 2008 R2 Best Practices Analyzer Another tool available in Windows S

72 CHAPTER 4 High Availability and Virtualization Enhancements SQL Server 2008 R2 Virtualization and Hyper-VVirtualization is one of the hottest to

SQL Server 2008 R2 Virtualization and Hyper-V CHAPTER 4 73Hyper-V01Hyper-V02Hyper-V03Hyper-V04C:\ClusterShares\Volume1VHDVHD VHD VHDC:\ClusterShar

74 CHAPTER 4 High Availability and Virtualization Enhancements NOTE System requirements vary based on an organization's virtualization requir

Implementing Live Migration for SQL Server 2008 R2 CHAPTER 4 75NOTE The Microsoft Assessment and Planning Toolkit can be used to identify whether

76 CHAPTER 4 High Availability and Virtualization Enhancements Enabling CSVAssuming that the Hyper-V cluster has already been built, the next step

Implementing Live Migration for SQL Server 2008 R2 CHAPTER 4 77 5. On the Specify Name And Location page, enter the name of the SQL Server VM an

78 CHAPTER 4 High Availability and Virtualization Enhancements 7. On the Networking page, connect the network adapter to an existing virtual net

Implementing Live Migration for SQL Server 2008 R2 CHAPTER 4 79 11. From the Virtual Machines section of the results pane in Hyper-V Manager, ri