Beginning SQL Server 2012 Administration PDF
Preview Beginning SQL Server 2012 Administration
Beginning SQL Server 2012 Administration ■ ■ ■ Rob Walters Grant Fritchey Beginning SQL Server Administration Copyright © 2012 by Rob Walters, Grant Fritchey This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher's location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN978-1-430 2-3981-9 ISBN978-1-4302-3982-6(eBook) Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. President and Publisher: Paul Manning Lead Editor: Jonathan Gennick Developmental Editor: Jonathan Gennick, Douglas Pundick Technical Reviewer: Matt Eddinger Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade, Tom Welsh Coordinating Editor: Brent Dubi Copy Editor: Heather Lang Production Support: Patrick Cunningham Indexer: SPi Global Artist: SPi Global Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. For information on translations, please e-mail [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales– eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text is available to readers at www.apress.com. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/. Contents at a Glance About the Authors ..................................................................................................... xv About the Technical Reviewer ................................................................................. xvi Acknowledgments .................................................................................................. xvii Introduction ........................................................................................................... xviii ■Chapter 1: The Database Administration Profession ............................................... 1 ■Chapter 2: Planning and Installing SQL Server 2012 ............................................. 13 ■Chapter 3: What’s in the Toolbox? ......................................................................... 43 ■Chapter 4: SQL Server Development ...................................................................... 77 ■Chapter 5: Creating Tables and Other Objects ..................................................... 105 ■Chapter 6: Transact-SQL ...................................................................................... 125 ■Chapter 7: SQL Server Internals .......................................................................... 147 ■Chapter 8: Database Backup Strategies .............................................................. 157 ■Chapter 9: Database Restore Strategies .............................................................. 185 ■Chapter 10: Common Database Maintenance Tasks ........................................... 213 ■Chapter 11: SQL Server Security ......................................................................... 249 ■Chapter 12: Auditing, Encryption, and Compliance ............................................. 271 ■Chapter 13: Performance Tuning and Optimization ............................................ 299 ■Chapter 14: Fault Tolerance ................................................................................ 339 ■Chapter 15: Cloud Computing for the DBA ........................................................... 365 ■Chapter 16: SQL Server Private Cloud ................................................................. 387 ■Chapter 17: Where to Go Next? ............................................................................ 401 Index ....................................................................................................................... 407 iii Contents About the Authors ..................................................................................................... xv About the Technical Reviewer ................................................................................. xvi Acknowledgments .................................................................................................. xvii Introduction ........................................................................................................... xviii ■Chapter 1: The Database Administration Profession ............................................... 1 Why Do We Need a DBA? Access Runs Great! ................................................................... 1 What Exactly Does a DBA Do Anyway? .............................................................................. 2 Database Maintenance ............................................................................................................................. 2 Security and Regulatory Compliance........................................................................................................ 3 Disaster Recovery ..................................................................................................................................... 3 Design and Performance Improvement .................................................................................................... 3 Documentation ......................................................................................................................................... 4 Salary Information ............................................................................................................. 4 Words from Real-World DBAs ............................................................................................ 4 Grant Fritchey, Principal DBA ................................................................................................................... 5 Roman Rehak, Principal Database Architect ............................................................................................ 7 Charlie Pyne, Senior DBA .......................................................................................................................... 8 Ed Clapper, Database Product Architect Manager ................................................................................... 9 Michael Esposito, Senior Technical Consultant and DBA ....................................................................... 11 Summary ......................................................................................................................... 12 iv ■ CONTENTS ■Chapter 2: Planning and Installing SQL Server 2012 ............................................. 13 Understanding the Editions .............................................................................................. 14 Demystifying 32-Bit and 64-Bit Architectures ........................................................................................ 14 Server Editions ....................................................................................................................................... 14 SQL Server Terminology ......................................................................................................................... 16 Planning Your Installation ................................................................................................ 17 Assessing the Environment .................................................................................................................... 17 Choosing the Right Edition ..................................................................................................................... 18 Validating Hardware and Software Requirements ................................................................................. 19 Installing SQL Server Evaluation Edition .......................................................................... 19 Step 1: Downloading SQL Server Evaluation Edition .............................................................................. 19 Step 2: Understanding the SQL Server Installation Center ..................................................................... 20 Step 3: Installing an Instance of SQL Server .......................................................................................... 28 Upgrading SQL Server...................................................................................................... 37 Summary ......................................................................................................................... 40 ■Chapter 3: What’s in the Toolbox? ......................................................................... 43 Connecting to SQL Server ....................................................................................................................... 43 Issuing Queries Using SSMS .................................................................................................................. 46 Managing Multiple Servers ..................................................................................................................... 53 Monitoring Server Activity ...................................................................................................................... 55 Other Tools from the Start Menu ..................................................................................... 61 Analysis Services Folder ......................................................................................................................... 63 Configuration Tools Folder ...................................................................................................................... 63 Documentation and Community Folder .................................................................................................. 68 Integration Services Folder ..................................................................................................................... 68 Performance Tools Folder ...................................................................................................................... 69 Import and Export Data Wizard ............................................................................................................... 70 SQL Server Data Tools ............................................................................................................................ 70 v ■ CONTENTS Command-Line Tools ....................................................................................................... 70 SQLCMD .................................................................................................................................................. 71 PowerShell Provider for SQL Server ....................................................................................................... 73 Summary ......................................................................................................................... 74 ■Chapter 4: SQL Server Development ...................................................................... 77 Touring SSMS Through the Eyes of a Developer ............................................................. 77 IntelliSense ............................................................................................................................................. 77 Query Designer ....................................................................................................................................... 78 Templates ............................................................................................................................................... 81 Debugging in T-SQL ................................................................................................................................ 84 Data-Tier Applications ..................................................................................................... 85 Defining a Data-Tier Application ............................................................................................................. 85 Deploying a Data-Tier Application .......................................................................................................... 89 Registering an Existing Database as a Data-Tier Application................................................................. 90 Monitoring a Data-Tier Application Using the SQL Server Utility ............................................................ 90 SQL Server Developer Tools ............................................................................................ 95 Working with SSDT in Connected Mode ................................................................................................. 96 Working with SSDT in Offline Mode ........................................................................................................ 99 Application Versioning .......................................................................................................................... 102 SQL Server LocalDB ....................................................................................................... 102 Summary ....................................................................................................................... 103 ■Chapter 5: Creating Tables and Other Objects ..................................................... 105 Navigating the Object Explorer Tree .............................................................................. 105 Scripting the Actions of an SSMS Dialog Box ................................................................ 106 Transact-SQL (T-SQL) Primer ........................................................................................ 109 Data Definition Language (DDL) ............................................................................................................ 109 Data Manipulation Language (DML) ..................................................................................................... 109 Data Control Language (DCL) ................................................................................................................ 110 vi ■ CONTENTS Creating Tables .............................................................................................................. 110 Creating Tables from the Table Designer ............................................................................................. 111 Issuing the CREATE TABLE Statement .................................................................................................. 112 Altering Tables ............................................................................................................... 114 Adding Constraints ........................................................................................................ 114 NULL Constraints .................................................................................................................................. 114 CHECK Constraints ................................................................................................................................ 114 Primary Keys and Unique Constraints .................................................................................................. 115 Foreign Key Constraints ....................................................................................................................... 116 Dropping Tables ............................................................................................................. 117 Creating Indexes ............................................................................................................ 119 Summary ....................................................................................................................... 123 ■Chapter 6: Transact-SQL ...................................................................................... 125 The VetClinic Sample Database Revisited ..................................................................... 125 Data Types ..................................................................................................................... 126 Unicode vs. ANSI .................................................................................................................................. 127 Living with NULL ................................................................................................................................... 128 Data Manipulation Language ......................................................................................... 128 SELECT ................................................................................................................................................. 128 INSERT .................................................................................................................................................. 133 UPDATE ................................................................................................................................................. 135 DELETE ................................................................................................................................................. 135 Transactions .................................................................................................................. 136 Executing Transactions ........................................................................................................................ 136 Isolating Transactions .......................................................................................................................... 136 Deadlocks ............................................................................................................................................. 137 Stored Procedures ......................................................................................................... 139 Creating Stored Procedures Using Templates ...................................................................................... 140 vii ■ CONTENTS Modifying Stored Procedures ............................................................................................................... 142 System Stored Procedures ................................................................................................................... 142 Functions ....................................................................................................................... 143 Creating a Function .............................................................................................................................. 143 Invoking a Function .............................................................................................................................. 143 Triggers ......................................................................................................................... 144 Summary ....................................................................................................................... 145 ■Chapter 7: SQL Server Internals .......................................................................... 147 Databases ...................................................................................................................... 147 Master Database .................................................................................................................................. 147 Tempdb Database ................................................................................................................................. 147 Model Database .................................................................................................................................... 148 MSDB Database .................................................................................................................................... 148 Resource Database ............................................................................................................................... 149 Repairing Corrupt System Databases ............................................................................ 149 Writing Data to Disk ....................................................................................................... 149 SQL Server Services ...................................................................................................... 150 Single-User Mode .......................................................................................................... 152 Placing an Already-Started Database into Single-User Mode .............................................................. 153 Starting SQL Server in Single-User Mode ............................................................................................. 153 Summary ....................................................................................................................... 156 ■Chapter 8: Database Backup Strategies .............................................................. 157 Defining the Types of Backups ...................................................................................... 157 Full Backups ......................................................................................................................................... 158 Differential Backups ............................................................................................................................. 163 File and Filegroup Backups .................................................................................................................. 164 Backing Up the Logs ...................................................................................................... 166 Initiating Fully Recovery Mode ............................................................................................................. 167 viii ■ CONTENTS Log Backup Using Management Studio ................................................................................................ 168 Log Backup Using T-SQL ...................................................................................................................... 169 Backing Up a Copy of the Database ..................................................................................................... 169 Automating the Backup Process .................................................................................... 169 Encrypting Backup Files for Security ............................................................................. 179 Compressing Backups to Save Space ........................................................................... 180 Designing a Backup Plan ............................................................................................... 182 Summary ....................................................................................................................... 184 ■Chapter 9: Database Restore Strategies .............................................................. 185 Restoring a Database from the Management Studio GUI .............................................. 186 Specifying Restore Options from the GUI ....................................................................... 192 Restoring a Database Using T-SQL ................................................................................ 194 Executing a Simple Restore .................................................................................................................. 194 Creating a Copy of a Database ............................................................................................................. 195 Retrieving Logical and Physical File Names ......................................................................................... 196 Displaying General Information About a Backup .................................................................................. 198 Cleaning Up .......................................................................................................................................... 200 Restoring Differential Backups ...................................................................................... 200 Restoring Log Backups .................................................................................................. 201 Restoring File Backups .................................................................................................. 205 Testing Backups ............................................................................................................ 207 Developing a Full Disaster Recovery Plan ..................................................................... 207 Gathering Information About the Business ........................................................................................... 208 Establishing the Disaster Plan .............................................................................................................. 209 Testing the Disaster Plan ...................................................................................................................... 210 Summary ....................................................................................................................... 211 ix ■ CONTENTS ■Chapter 10: Common Database Maintenance Tasks ........................................... 213 Backing Up and Restoring Databases ............................................................................ 213 Moving Data ................................................................................................................... 214 Moving a Database Using the “Detach and Attach” Method ................................................................ 214 Moving a Database Using the Copy Database Wizard .......................................................................... 216 Moving Data Using the Import and Export Wizard ................................................................................ 220 Moving Data Using the Bulk Copy Program (BCP) ................................................................................ 227 Checking Database Integrity .......................................................................................... 229 Maintaining Indexes....................................................................................................... 230 Detecting Fragmentation ...................................................................................................................... 231 Defragmenting Indexes ........................................................................................................................ 233 Distribution Statistics .................................................................................................... 234 Understanding Distribution Statistics ................................................................................................... 234 Updating Statistics ............................................................................................................................... 237 Creating Maintenance Plans .......................................................................................... 237 Scheduling Maintenance Tasks ..................................................................................... 244 Creating Log Files .......................................................................................................... 245 Summary ....................................................................................................................... 247 ■Chapter 11: SQL Server Security ......................................................................... 249 Terminology ................................................................................................................... 249 Authentication ...................................................................................................................................... 249 Authentication Mode ............................................................................................................................ 250 Authorization ........................................................................................................................................ 250 Server Instance vs. the Database ......................................................................................................... 250 SQL Server Instance Security ........................................................................................ 252 Creating a SQL Server Login ................................................................................................................. 252 Server Roles ......................................................................................................................................... 253 Server Permissions ............................................................................................................................... 255 x
Description:The list of books you might like
