Details

Professional Microsoft SQL Server 2014 Administration


Professional Microsoft SQL Server 2014 Administration


1. Aufl.

von: Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight

35,99 €

Verlag: Wiley
Format: PDF
Veröffentl.: 27.08.2014
ISBN/EAN: 9781118859261
Sprache: englisch
Anzahl Seiten: 936

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

<b>Learn to take advantage of the opportunities offered by SQL Server 2014</b> <p>Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. <i>Professional Microsoft SQL Server 2014</i> is your comprehensive guide to working with the new environment. Authors Adam Jorgensen, Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the dream team of the SQL Server community, and they put their expertise to work guiding you through the changes.</p> <ul> <li>Improve oversight with better management and monitoring</li> <li>Protect your work with enhanced security features</li> <li>Upgrade performance tuning, scaling, replication, and clustering</li> <li>Learn new options for backup and recovery</li> </ul> <p><i>Professional Microsoft SQL Server 2014</i> includes a companion website with sample code and efficient automation utilities, plus a host of tips, tricks, and workarounds that will make your job as a DBA or database architect much easier. Stop getting frustrated with administrative issues and start taking control. <i>Professional Microsoft SQL Server 2014</i> is your roadmap to mastering the update and creating solutions that work.</p>
<p>INTRODUCTION xxxvii</p> <p><b>CHAPTER 1: SQL SERVER 2014 ARCHITECTURE 1</b></p> <p>SQL Server 2014 Ecosystem 1</p> <p>New Important Features in 2014 2</p> <p>Production DBA 2</p> <p>Development DBA 3</p> <p>Business Intelligence DBA and Developer 4</p> <p>SQL Server Architecture 5</p> <p>Database Files and Transaction Log 5</p> <p>SQL Server Native Client 6</p> <p>Standard System Databases 7</p> <p>Schemas 9</p> <p>Synonyms 10</p> <p>Dynamic Management Objects 10</p> <p>SQL Server 2014 Data Types 12</p> <p>Editions of SQL Server 18</p> <p>Edition Overview 18</p> <p>Licensing 21</p> <p>Summary 22</p> <p><b>CHAPTER 2: INSTALLING SQL SERVER 2014 BEST PRACTICES 23</b></p> <p>Planning the System 24</p> <p>Hardware Options 24</p> <p>Software and Install Options 29</p> <p>Installing SQL Server 32</p> <p>New Installs 32</p> <p>Side-by-Side Installs 32</p> <p>Upgrades 33</p> <p>Attended Installations 33</p> <p>Unattended Installs 37</p> <p>Installing Analysis Services 43</p> <p>Multidimensional and Data Mining Mode (UDM Mode) 44</p> <p>Tabular Mode 46</p> <p>Installing PowerPivot for SharePoint 47</p> <p>Burning in the System 48</p> <p>Post-Install Configuration 49</p> <p>Configuring SQL Server Settings for Performance 49</p> <p>tempdb 51</p> <p>Configuring SQL Server Settings for Security 52</p> <p>SQL Server Configuration Manager 54</p> <p>Back It Up 54</p> <p>Uninstalling SQL Server 54</p> <p>Uninstalling Reporting Services 55</p> <p>Uninstalling Analysis Services 55</p> <p>Uninstalling the SQL Server Database Engine 55</p> <p>Troubleshooting a Failed Install 55</p> <p>Summary 56</p> <p><b>CHAPTER 3: UPGRADING SQL SERVER 2014 BEST PRACTICES 57</b></p> <p>Why Upgrade to SQL Server 2014? 57</p> <p>Risk Mitigation—the Microsoft Contribution 58</p> <p>Independent Software Vendors and SQL Community Contributions 59</p> <p>Upgrading to SQL Server 2014 59</p> <p>In-Place Upgrading 60</p> <p>Side-by-Side Upgrade 62</p> <p>In-Place Upgrade versus Side-by-Side Upgrade Considerations 62</p> <p>Pre-Upgrade Steps and Tools 63</p> <p>Pre-Upgrade Steps 63</p> <p>Pre-Upgrade Tools 64</p> <p>Backward Compatibility 72</p> <p>Unsupported and Discontinued Features in SQL Server 2014 72</p> <p>SQL Server 2014 Deprecated Database Features 74</p> <p>Other SQL Server 2014 Changes Affecting Behavior 74</p> <p>SQL Server Component Considerations 75</p> <p>Upgrading Full-Text Catalog 75</p> <p>Upgrading Reporting Services 75</p> <p>Upgrading to 64-Bit 76</p> <p>Post-Upgrade Checks 76</p> <p>Summary 77</p> <p><b>CHAPTER 4: MANAGING AND TROUBLESHOOTING THE DATABASE ENGINE 79</b></p> <p>Configuration and Administration Tools 80</p> <p>SQL Server Configuration Manager 80</p> <p>Startup Parameters 82</p> <p>Startup Stored Procedures 84</p> <p>Partially Contained Databases 86</p> <p>Troubleshooting Tools 87</p> <p>Dedicated Administrator Connection 87</p> <p>Rebuilding the System Databases 89</p> <p>SQL Server Management Studio 90</p> <p>Reports 90</p> <p>Configuring SQL Server in SQL Server Management Studio 93</p> <p>Filtering Objects 97</p> <p>Error Logs 97</p> <p>Activity Monitor 98</p> <p>Monitoring Processes in T-SQL 104</p> <p>sp_who and sp_who2 104</p> <p>sys.dm_exec_connections 105</p> <p>sys.dm_exec_sql_text 105</p> <p>Multiserver Management 106</p> <p>Central Management Servers and Server Groups 106</p> <p>Trace Flags 107</p> <p>Getting Help from Support 109</p> <p>SQLDumper.exe 109</p> <p>SQLDiag.exe 110</p> <p>Summary 112</p> <p><b>CHAPTER 5: AUTOMATING SQL SERVER 113</b></p> <p>Maintenance Plans 114</p> <p>Maintenance Plan Wizard 114</p> <p>Maintenance Plan Designer 119</p> <p>Automating SQL Server with SQL Server Agent 122</p> <p>Jobs 123</p> <p>Schedules 128</p> <p>Operators 129</p> <p>Alerts 133</p> <p>SQL Server Agent Security 138</p> <p>Service Account 138</p> <p>Access to SQL Agent 138</p> <p>SQL Server Agent Proxies 139</p> <p>Configuring SQL Server Agent 143</p> <p>General Properties 143</p> <p>Advanced Properties 144</p> <p>Alert System Properties 145</p> <p>Job System Properties 146</p> <p>Connection Properties 147</p> <p>History Properties 147</p> <p>Multiserver Administration 147</p> <p>Using Token Replacement 148</p> <p>Event Forwarding 150</p> <p>Using WMI 151</p> <p>Multiserver Administration—Using Master and Target Servers 152</p> <p>Summary 154</p> <p><b>CHAPTER 6: SERVICE BROKER IN SQL SERVER 2014 155</b></p> <p>Asynchronous Messaging 155</p> <p>SQL Service Broker Overview 157</p> <p>SQL Server Service Broker versus Other Message Queues 158</p> <p>Configuring SQL Server Service Broker 159</p> <p>Setting Broker State 159</p> <p>Message Types 161</p> <p>Contracts 162</p> <p>Queues 163</p> <p>Services 165</p> <p>Routes 166</p> <p>Priorities 167</p> <p>Conversation Groups 168</p> <p>Using SQL Server Service Broker 169</p> <p>Sending Messages 169</p> <p>Receiving Messages 172</p> <p>Sending Messages between Instances 174</p> <p>External Activation 176</p> <p>Log User Example 177</p> <p>Summary 184</p> <p><b>CHAPTER 7: SQL SERVER CLR INTEGRATION 185</b></p> <p>Introduction to CLR 185</p> <p>SQL Server as a .NET Runtime Host 187</p> <p>Application Domains 187</p> <p>T-SQL versus CLR 187</p> <p>Enabling CLR Integration 188</p> <p>Creating CLR Assemblies 189</p> <p>The Non–Visual Studio Way 189</p> <p>Using Microsoft SQL Server Data Tools 191</p> <p>CLR Integration Security 193</p> <p>Performance Monitoring 194</p> <p>Windows System Monitor 194</p> <p>Extended Events 196</p> <p>Dynamic Management Views (DMVs) 197</p> <p>CLR Integration Design Goals 197</p> <p>Summary 198</p> <p><b>CHAPTER 8: SECURING THE DATABASE INSTANCE 199</b></p> <p>Authentication Types 200</p> <p>SQL Authentication 200</p> <p>Windows Authentication 201</p> <p>SQL versus Windows Authentication 202</p> <p>Logins and Users 202</p> <p>Authorizing Securables 202</p> <p>Server Securables 203</p> <p>Database Securables 209</p> <p>Permission Chains 210</p> <p>Cross-Database Permission Chains 211</p> <p>Row-Level Security 213</p> <p>Summary 214</p> <p><b>CHAPTER 9: IN-MEMORY OLTP 215</b></p> <p>Using and Implementing In-Memory OLTP 215</p> <p>Enabling In-Memory OLTP 216</p> <p>In-Memory OLTP Structures 218</p> <p>Records 218</p> <p>Indexes 219</p> <p>CPU Considerations 224</p> <p>Virtualization Considerations 224</p> <p>Memory Considerations 225</p> <p>Managing Memory with Resource Governor 227</p> <p>Creating Natively Compiled Stored Procedures 230</p> <p>Overview of the Analyze, Migrate, and Report (ARM) Tool 233</p> <p>Summary 246</p> <p><b>CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL PERFORMANCE 247</b></p> <p>What Every DBA Needs to Know About Performance 248</p> <p>The Performance Tuning Cycle 248</p> <p>Configuration 250</p> <p>Power Configuration 250</p> <p>Instant Database File Initialization 252</p> <p>Trace Flags 256</p> <p>Defining Good Performance 256</p> <p>Focus on What’s Most Important 257</p> <p>What the Developer DBA Needs to Know About Performance 258</p> <p>Users 258</p> <p>SQL Statements 259</p> <p>Data Usage Patterns 259</p> <p>Database Schema 259</p> <p>What the Production DBA Needs to Know About Performance 260</p> <p>Optimizing the Server’s Hardware 261</p> <p>Hardware Management 262</p> <p>CPU 263</p> <p>x64 263</p> <p>Cache 264</p> <p>Hyper-Threading 264</p> <p>Multicore Terminology 265</p> <p>Memory 266</p> <p>Physical Memory 266</p> <p>Physical Address Space 267</p> <p>Virtual Memory Manager 267</p> <p>The Page File 267</p> <p>Page Faults 268</p> <p>I/O 269</p> <p>Network 270</p> <p>Magnetic Disks 271</p> <p>Solid State/Flash Drives 274</p> <p>Storage Considerations 274</p> <p>Summary 278</p> <p><b>CHAPTER 11: OPTIMIZING SQL SERVER 2014 281</b></p> <p>Application Optimization 281</p> <p>Defining a Workload 282</p> <p>The Silent Killer: I/O Problems 282</p> <p>SQL Server I/O Process Model 282</p> <p>Database File Placement 283</p> <p>tempdb Considerations 284</p> <p>SQL Server Internals and File Allocations 288</p> <p>Table and Index Partitioning 290</p> <p>Why Consider Partitioning? 290</p> <p>Creating a Partition Function 292</p> <p>Creating Filegroups 294</p> <p>Creating a Partition Scheme 295</p> <p>Data Compression 296</p> <p>Row Compression 296</p> <p>Page Compression 299</p> <p>Estimating Space Savings 300</p> <p>Monitoring Data Compression 302</p> <p>Data Compression Considerations 303</p> <p>Understanding SQL Server and CPUs 304</p> <p>NUMA and Hot Add CPUs 305</p> <p>Cache Coherency 306</p> <p>Affinity Mask 307</p> <p>Max Degree of Parallelism (MAXDOP) 307</p> <p>Cost Threshold for Parallelism 308</p> <p>Memory Considerations and Enhancements 310</p> <p>Buffer Pool Extensions 310</p> <p>Tuning SQL Server Memory 312</p> <p>Data Locality 314</p> <p>Max Server Memory 315</p> <p>Resource Governor 316</p> <p>The Basic Elements of Resource Governor 317</p> <p>Using Resource Governor from SQL Server 2014 Management Studio 321</p> <p>Monitoring Resource Governor 322</p> <p>Summary 323</p> <p><b>CHAPTER 12: MONITORING YOUR SQL SERVER 325</b></p> <p>The Goal of Monitoring 326</p> <p>Determining Your Monitoring Objectives 326</p> <p>Establishing a Baseline 327</p> <p>Comparing Current Metrics to the Baseline 327</p> <p>Choosing the Appropriate Monitoring Tools 328</p> <p>Performance Monitor 329</p> <p>CPU Resource Counters 331</p> <p>Disk Activity 332</p> <p>Memory Usage 338</p> <p>Performance Monitoring Tools 341</p> <p>Monitoring Events 343</p> <p>The Default Trace 345</p> <p>system_health Session 346</p> <p>SQL Trace 347</p> <p>Event Notifications 351</p> <p>SQL Server Extended Events 354</p> <p>Monitoring with Dynamic Management Views and Functions 375</p> <p>What’s Going on Inside SQL Server? 377</p> <p>Viewing the Locking Information 380</p> <p>Viewing Blocking Information 380</p> <p>Index Usage in a Database 381</p> <p>Indexes Not Used in a Database 382</p> <p>View Queries Waiting for Memory Grants 383</p> <p>Connected User Information 384</p> <p>Filegroup Free Space 384</p> <p>Query Plan and Query Text for Currently Running Queries 385</p> <p>Memory Usage 385</p> <p>Buffer Pool Memory Usage 385</p> <p>Monitoring Logs 386</p> <p>Monitoring the SQL Server Error Log 386</p> <p>Monitoring the Windows Event Logs 387</p> <p>SQL Server Standard Reports 387</p> <p>System Center Advisor 387</p> <p>Summary 390</p> <p><b>CHAPTER 13: PERFORMANCE TUNING T-SQL 391</b></p> <p>Overview of Query Processing 392</p> <p>Generating Execution Plans 394</p> <p>Statistics 394</p> <p>SQL 2014 Query Optimization Improvements—The New Cardinality</p> <p>Estimator 395</p> <p>Using the New Cardinality Estimator 395</p> <p>Identifying SQL Query Performance Tuning Issues 396</p> <p>Monitoring Query Performance 396</p> <p>What to Do When You Find a Slow-Performing Query 397</p> <p>Generating the Query Plans 398</p> <p>Reading the Query Plan 400</p> <p>Data Access Operators in Query Plans 403</p> <p>Table Scan 404</p> <p>Clustered Index Scan 406</p> <p>Nonclustered Index Scans 408</p> <p>Connecting Access Operators 411</p> <p>Join Operators 412</p> <p>Nested Loop or Loop Join 412</p> <p>Hash Join 413</p> <p>Merge Join 415</p> <p>Data Modification Query Plan 417</p> <p>Query Processing on Partitioned Tables and Indexes 419</p> <p>Partition-Aware Operations 419</p> <p>Parallel Query Execution Strategy for Partitioned Objects 421</p> <p>Analyzing Query Performance in a Production Environment 421</p> <p>System Dynamic Management Views (DMVs) 422</p> <p>Putting It All Together 423</p> <p>Summary 424</p> <p><b>CHAPTER 14: INDEXING YOUR DATABASE 425</b></p> <p>What’s New for Indexes in SQL Server 2014 426</p> <p>About Indexes and Partitioned Tables 430</p> <p>Understanding Indexes 430</p> <p>Creating Indexes 435</p> <p>Using Partitioned Tables and Indexes 436</p> <p>Index Maintenance 437</p> <p>Monitoring Index Fragmentation 438</p> <p>Cleaning Up Indexes 439</p> <p>Improving Query Performance with Indexes 440</p> <p>Database Tuning Advisor 445</p> <p>Summary 448</p> <p><b>CHAPTER 15: REPLICATION 449</b></p> <p>Replication Overview 450</p> <p>Replication Components 450</p> <p>Replication Types 452</p> <p>Replication Models 454</p> <p>Single Publisher, One or More Subscribers 454</p> <p>Multiple Publishers, Single Subscriber 454</p> <p>Multiple Publishers Also Subscribing 456</p> <p>Updating Subscriber 457</p> <p>Peer-to-Peer 458</p> <p>Implementing Replication 460</p> <p>Setting Up Snapshot Replication 460</p> <p>Setting Up Distribution 460</p> <p>Implementing Snapshot Replication 464</p> <p>Implementing Transactional and Merge Replication 479</p> <p>Peer-to-Peer Replication 480</p> <p>Setting Up Peer-to-Peer Replication 480</p> <p>Configuring Peer-to-Peer Replication 481</p> <p>Scripting Replication 484</p> <p>Monitoring Replication 485</p> <p>Replication Monitor 485</p> <p>Performance Monitor 487</p> <p>Replication DMVs 488</p> <p>sp_replcounters 489</p> <p>Summary 489</p> <p><b>CHAPTER 16: CLUSTERING SQL SERVER 2014 491</b></p> <p>Clustering and Your Organization 492</p> <p>What Clustering Can Do 492</p> <p>What Clustering Cannot Do 493</p> <p>Choosing SQL Server 2014 Clustering for the Right Reasons 494</p> <p>Alternatives to Clustering 494</p> <p>Clustering: The Big Picture 497</p> <p>How Clustering Works 497</p> <p>Clustering Options 502</p> <p>Upgrading SQL Server Clustering 504</p> <p>Don’t Upgrade 504</p> <p>Upgrading Your SQL Server 2014 Cluster In-Place 505</p> <p>Rebuilding Your Cluster 505</p> <p>Back-Out Plan 507</p> <p>Which Upgrade Option Is Best? 507</p> <p>Getting Prepared for Clustering 507</p> <p>Preparing the Infrastructure 508</p> <p>Preparing the Hardware 509</p> <p>Clustering Windows Server 2012 R2 511</p> <p>Before Installing Windows 2012 R2 Clustering 511</p> <p>Installing Windows Server 2012 R2 Failover Clustering 512</p> <p>Preparing Windows Server 2012 R2 for Clustering 516</p> <p>Clustering Microsoft Distributed Transaction Coordinator 517</p> <p>Clustering SQL Server 2014 518</p> <p>Step-by-Step Instructions to Cluster SQL Server 519</p> <p>Installing the Service Pack and Cumulative Updates 526</p> <p>Test, Test, and Test Again 527</p> <p>Managing and Monitoring the Cluster 529</p> <p>Troubleshooting Cluster Problems 530</p> <p>How to Approach Windows Failover Clustering Troubleshooting 530</p> <p>Doing It Right the First Time 531</p> <p>Gathering Information 531</p> <p>Resolving Problems 532</p> <p>Working with Microsoft 532</p> <p>Summary 533</p> <p><b>CHAPTER 17: BACKUP AND RECOVERY 535</b></p> <p>Backup and Restore Enhancements 536</p> <p>SQL Server Backup to URL 536</p> <p>SQL Server Managed Backup to Windows Azure 536</p> <p>Encryption for Backups 536</p> <p>Overview of Backup and Restore 537</p> <p>How Backup Works 537</p> <p>Copying Databases 539</p> <p>Backup Compression 553</p> <p>Comparing Recovery Models 554</p> <p>Choosing a Model 556</p> <p>Switching Recovery Models 557</p> <p>Backing Up History Tables 558</p> <p>Permissions Required for Backup and Restore 559</p> <p>Backing Up System Databases 559</p> <p>Full-Text Backup 561</p> <p>Verifying the Backup Images 561</p> <p>How Restore Works 562</p> <p>Preparing for Recovery 563</p> <p>Recoverability Requirements 564</p> <p>Data Usage Patterns 565</p> <p>Maintenance Time Window 565</p> <p>Other High-Availability Solutions 566</p> <p>Developing and Executing a Backup Plan 567</p> <p>Using SQL Server Management Studio 568</p> <p>Database Maintenance Plans 572</p> <p>Using Transact-SQL Backup Commands 576</p> <p>Managing Backups 578</p> <p>Backup and Restore Performance 578</p> <p>Performing Recovery 579</p> <p>Restore Process 579</p> <p>SQL Server Management Studio Restore 583</p> <p>T-SQL Restore Command 588</p> <p>Restoring System Databases 588</p> <p>Archiving Data 590</p> <p>SQL Server Table Partitioning 590</p> <p>Partitioned View 591</p> <p>Summary 592</p> <p><b>CHAPTER 18: SQL SERVER 2014 LOG SHIPPING 593</b></p> <p>Log-Shipping Deployment Scenarios 593</p> <p>Log Shipping to Create a Warm Standby Server 594</p> <p>Log Shipping as a Disaster-Recovery Solution 595</p> <p>Log Shipping as a Report Database Solution 596</p> <p>Log-Shipping Architecture 597</p> <p>Primary Server 598</p> <p>Secondary Server 598</p> <p>Monitor Server 598</p> <p>Log-Shipping Process 599</p> <p>System Requirements 599</p> <p>Network 600</p> <p>Identical Capacity Servers 600</p> <p>Storage 600</p> <p>Software 600</p> <p>Deploying Log Shipping 601</p> <p>Initial Configuration 601</p> <p>Deploying with Management Studio 602</p> <p>Deploying with T-SQL Commands 610</p> <p>Monitoring and Troubleshooting 611</p> <p>Monitoring with SQL 2014 Management Studio 612</p> <p>Monitoring with Stored Procedures 613</p> <p>Troubleshooting Approach 613</p> <p>Managing Changing Roles 614</p> <p>Synchronizing Dependencies 614</p> <p>Switching Roles from the Primary to Secondary Servers 617</p> <p>Switching Between Primary and Secondary Servers 619</p> <p>Redirecting Clients to Connect to the Secondary Server 620</p> <p>Database Backup Plan 621</p> <p>Integrating Log Shipping with Other High-Availability Solutions 622</p> <p>SQL Server 2014 Data Mirroring 622</p> <p>Windows Failover Clustering 622</p> <p>SQL Server 2014 Replication 623</p> <p>Removing Log Shipping 624</p> <p>Removing Log Shipping with Management Studio 624</p> <p>Removing Log Shipping with T-SQL Commands 624</p> <p>Log-Shipping Performance 625</p> <p>Upgrading to SQL Server 2014 Log Shipping 626</p> <p>Minimum Downtime Approach 626</p> <p>With Downtime Approach 626</p> <p>Deploy Log-Shipping Approach 627</p> <p>Summary 627</p> <p><b>CHAPTER 19: DATABASE MIRRORING 629</b></p> <p>Overview of Database Mirroring 630</p> <p>Operating Modes of Database Mirroring 631</p> <p>Database Mirroring In Action 634</p> <p>Preparing the Endpoints 635</p> <p>Preparing the Database for Mirroring 640</p> <p>Initial Synchronization between Principal and Mirror 641</p> <p>Establishing the Mirroring Session 642</p> <p>High-Safety Operating Mode Without Automatic Failover 643</p> <p>High-Safety Operating Mode with Automatic Failover 643</p> <p>High-Performance Operating Mode 645</p> <p>Database Mirroring and SQL Server 2014 Editions 646</p> <p>Database Mirroring Role Change 646</p> <p>Automatic Failover 647</p> <p>Manual Failover 650</p> <p>Forced Failover 651</p> <p>Monitoring Using Database Mirroring Monitor 652</p> <p>Setting Thresholds on Counters and Sending Alerts 656</p> <p>Preparing the Mirror Server for Failover 658</p> <p>Hardware, Software, and Server Configuration 658</p> <p>Database Availability During Planned Downtime 660</p> <p>SQL Job Configuration on the Mirror 661</p> <p>Client Redirection to the Mirror 662</p> <p>Database Mirroring and Other High-Availability Solutions 663</p> <p>Database Mirroring versus Clustering 663</p> <p>Database Mirroring versus Transactional Replication 664</p> <p>Database Mirroring versus Log Shipping 664</p> <p>Database Mirroring versus Availability Groups 664</p> <p>Database Snapshots 665</p> <p>Summary 666</p> <p><b>CHAPTER 20: INTEGRATION SERVICES ADMINISTRATION AND PERFORMANCE TUNING 667</b></p> <p>A Tour of Integration Services 668</p> <p>Integration Services Uses 668</p> <p>The Main Parts of Integration Services 669</p> <p>Project Management and Change Control 671</p> <p>Administration of the Integration Services Service 671</p> <p>An Overview of the Integration Services Service 672</p> <p>Configuration 672</p> <p>Event Logs 675</p> <p>Monitoring Activity 676</p> <p>Administration of Integration Services Packages in Package Deployment Model 677</p> <p>Using Management Studio for Package Management 677</p> <p>Deployment 679</p> <p>Administration of Integration Services Packages in Project Deployment Model 683</p> <p>Configuring the SSIS Catalog 683</p> <p>Deploying Packages 685</p> <p>Configuring Packages 687</p> <p>Execution and Scheduling 690</p> <p>Running Packages in SQL Server Data Tools 691</p> <p>Running Packages with the SQL Server Import and Export Wizard 691</p> <p>Running Packages with DTExec 691</p> <p>Running Packages with DTExecUI (Package Deployment Model) 692</p> <p>Running Packages with the Execute Package Tool (Project Deployment Model) 693</p> <p>Scheduling Execution with SQL Server Agent 694</p> <p>Running Packages with T-SQL 696</p> <p>Applying Security to Integration Services 696</p> <p>An Overview of Integration Services Security 696</p> <p>Securing Packages in Package Deployment Model 697</p> <p>Summary 699</p> <p><b>CHAPTER 21: ANALYSIS SERVICES ADMINISTRATION AND PERFORMANCE TUNING 701</b></p> <p>Tour of Analysis Services 702</p> <p>MOLAP Components 703</p> <p>Tabular Model Components 704</p> <p>Analysis Services Architectural Components 704</p> <p>Administering Analysis Services Server 705</p> <p>Server Properties 706</p> <p>Required Services 707</p> <p>Analysis Services Scripting Language 708</p> <p>Administering Analysis Services Databases 709</p> <p>Deploying Analysis Services Databases 710</p> <p>Processing Analysis Services Objects 712</p> <p>Backing Up and Restoring Analysis Services Databases 717</p> <p>Synchronizing Analysis Services Databases 721</p> <p>Analysis Services Performance Monitoring and Tuning 722</p> <p>Monitoring Analysis Services Events 722</p> <p>Using Flight Recorder for After-the-Fact Analysis 723</p> <p>Summary 723</p> <p><b>CHAPTER 22: SQL SERVER REPORTING SERVICES ADMINISTRATION 725</b></p> <p>SQL Server Reporting Services</p> <p>Configuration Manager 726</p> <p>The Service Account 728</p> <p>The Web Service URL 729</p> <p>Reporting Services Databases 731</p> <p>The Report Manager URL 733</p> <p>E-mail Settings 734</p> <p>Execution Account 734</p> <p>Encryption Keys 736</p> <p>Scale-Out Deployment 737</p> <p>The Report Execution Log 739</p> <p>Report Manager 740</p> <p>Managing Report Manager 740</p> <p>Managing Reports 747</p> <p>Summary 761</p> <p><b>CHAPTER 23: SQL SERVER 2014 SHAREPOINT 2013 INTEGRATION 763</b></p> <p>Components of Integration 763</p> <p>PowerPivot 764</p> <p>Reporting Services 766</p> <p>Power View 768</p> <p>Data Refresh 771</p> <p>Using Data Connections in Excel 771</p> <p>PerformancePoint Data Connections 776</p> <p>Visio Services Data Refresh 778</p> <p>PowerPivot Data Refresh 780</p> <p>Summary 788</p> <p><b>CHAPTER 24: SQL DATABASE ADMINISTRATION AND CONFIGURATION 789</b></p> <p>Getting to Know Windows Azure SQL Database 789</p> <p>SQL Database Architecture 790</p> <p>Client Layer 791</p> <p>Services Layer 791</p> <p>Platform Layer 791</p> <p>Infrastructure Layer 791</p> <p>Understanding the Differences 791</p> <p>Configuring SQL Database 792</p> <p>Server and Database Provisioning 792</p> <p>Throttling and Load Balancing 800</p> <p>Configuring SQL Database Firewalls 801</p> <p>Connecting to SQL Database 802</p> <p>Administering SQL Database 804</p> <p>Creating Logins and Users 805</p> <p>Assigning Access Rights 807</p> <p>Working with SQL Database 807</p> <p>Backups with SQL Database 807</p> <p>Object Explorer for SQL Database 809</p> <p>What’s Missing in SQL Database 810</p> <p>Summary 811</p> <p><b>CHAPTER 25: ALWAYSON AVAILABILITY GROUPS 813</b></p> <p>Architecture 814</p> <p>Availability Group Replicas and Roles 815</p> <p>Availability Modes 815</p> <p>Types of Failover Supported 816</p> <p>Allowing Read-Only Access to Secondary Replicas 817</p> <p>Availability Group Example 818</p> <p>Configure a New Availability Group 819</p> <p>Configure an Existing Availability Group 829</p> <p>Availability Group Failover Operation 833</p> <p>Suspend an Availability Database 834</p> <p>Resume an Availability Database 835</p> <p>Client Application Connections 836</p> <p>Active Secondary for Secondary Read-Only 837</p> <p>Read-Only Access Behavior 837</p> <p>Secondary Replica Client Connectivity 838</p> <p>Performance 839</p> <p>Backup on the Secondary Replica 841</p> <p>Evaluate Backup Replicas Metadata 842</p> <p>AlwaysOn Group Dashboard 843</p> <p>Monitoring and Troubleshooting 845</p> <p>Summary 847</p> <p>INDEX 849</p>
<p><b>Adam Jorgensen</b> is the President of Pragmatic Works and the Executive Vice President of PASS with more than 15 years experience.</p> <p><b>Bradley Ball</b> is a MCITP 2005, 2008, and 2012 MCSE DBA with more than a decade of experience who is currently the Data Platform Management Lead with Pragmatic Works specializing in Data Platform solutions.</p> <p><b>Steven Wort</b> has been working with SQL Server since 1993, who joined Microsoft in 2000, and now works as an Architect in the CRM Service Engineering team.</p> <p><b>Ross LoForte</b> is a Technology Architect at the Microsoft Technology Center Chicago focused on Microsoft SQL Server solutions, with more than 20 years of business development, project management, and SQL experience.</p> <p><b>Brian Knight</b> is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpStartTV.com</p> <p><b>Wrox Professional guides</b> are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.</p>
<p><b>Best practices for administering SQL Server 2014, from Microsoft MVPs</b></p> <p>This comprehensive guide teaches the skills needed to successfully administer SQL Server, including change management, security, performance tuning, monitoring, and backups. Written by the leading authorities on Microsoft SQL Server, this book will help you master the latest features of the 2014 release, including greater integration of cloud and on-premises management using SQL Server Management Studio, enhanced performance optimization, new cardinality estimator, and new features for AlwaysOn availability groups. This comprehensive guide will help you improve the efficiency, scalability, and performance of the SQL Server environment so more can be done in less time.</p> <p><i>Professional Microsoft SQL Server 2014 Administration:</i></p> <ul> <li>Provides an overview of SQL Azure and new features for AlwaysOn availability groups</li> <li>Explains In-Memory OLTP and buffer pool extensions for SSDs and performance optimizations for 2014</li> <li>Details the new cardinality estimator (Query Optimizer) in SQL 2014</li> <li>Discusses the separation of duties and important regulatory requirements for security roles</li> <li>Provides real-world case examples and solutions</li> </ul> <p><b>Programmer Forums</b></p> <p>Join our Programmer to Programmer forums to ask and answer programming questions about this book, join discussions on the hottest topics in the industry, and connect with fellow programmers from around the world.</p> <p><b>Code Downloads</b></p> <p>Take advantage of free code samples from this book, as well as code samples from hundreds of other books, all ready to use.</p> <p><b>Read More</b></p> <p>Find articles, e-books, sample chapters, and tables of contents for hundreds of books, and more reference resources on programming topics that matter to you.</p>

Diese Produkte könnten Sie auch interessieren:

MDX Solutions
MDX Solutions
von: George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, Francesco Civardi
PDF ebook
53,99 €
Concept Data Analysis
Concept Data Analysis
von: Claudio Carpineto, Giovanni Romano
PDF ebook
107,99 €
Handbook of Virtual Humans
Handbook of Virtual Humans
von: Nadia Magnenat-Thalmann, Daniel Thalmann
PDF ebook
150,99 €