MCT USE ONL
Y. STUDENT USE PROHIBITED
O F F I C I A L M I C R O S O F T L E A R N I N G P R O D U C T
20462C
Administering Microsoft®
SQL Server® Databases
MCT USE ONL
Y. STUDENT USE PROHIBITED
ii 20462C: Administering Microsoft SQL Server Databases
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links may be provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.
© 2014 Microsoft Corporation. All rights reserved. Microsoft and the trademarks listed at
http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspxare trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners
Product Number: 20462C Part Number: X19-32473 Released: 05/2014
MCT USE ONL
Y. STUDENT USE PROHIBITED
MICROSOFT LICENSE TERMS
MICROSOFT INSTRUCTOR-LED COURSEWARE
These license terms are an agreement between Microsoft Corporation (or based on where you live, one of its affiliates) and you. Please read them. They apply to your use of the content accompanying this agreement which includes the media on which you received it, if any. These license terms also apply to Trainer Content and any updates and supplements for the Licensed Content unless other terms accompany those items. If so, those terms apply.
BY ACCESSING, DOWNLOADING OR USING THE LICENSED CONTENT, YOU ACCEPT THESE TERMS. IF YOU DO NOT ACCEPT THEM, DO NOT ACCESS, DOWNLOAD OR USE THE LICENSED CONTENT. If you comply with these license terms, you have the rights below for each license you acquire. 1. DEFINITIONS.
a. “Authorized Learning Center” means a Microsoft IT Academy Program Member, Microsoft Learning Competency Member, or such other entity as Microsoft may designate from time to time.
b. “Authorized Training Session” means the instructor-led training class using Microsoft Instructor-Led Courseware conducted by a Trainer at or through an Authorized Learning Center.
c. “Classroom Device” means one (1) dedicated, secure computer that an Authorized Learning Center owns or controls that is located at an Authorized Learning Center’s training facilities that meets or exceeds the hardware level specified for the particular Microsoft Instructor-Led Courseware.
d. “End User” means an individual who is (i) duly enrolled in and attending an Authorized Training Session or Private Training Session, (ii) an employee of a MPN Member, or (iii) a Microsoft full-time employee. e. “Licensed Content” means the content accompanying this agreement which may include the Microsoft
Instructor-Led Courseware or Trainer Content.
f. “Microsoft Certified Trainer” or “MCT” means an individual who is (i) engaged to teach a training session to End Users on behalf of an Authorized Learning Center or MPN Member, and (ii) currently certified as a Microsoft Certified Trainer under the Microsoft Certification Program.
g. “Microsoft Instructor-Led Courseware” means the Microsoft-branded instructor-led training course that educates IT professionals and developers on Microsoft technologies. A Microsoft Instructor-Led
Courseware title may be branded as MOC, Microsoft Dynamics or Microsoft Business Group courseware. h. “Microsoft IT Academy Program Member” means an active member of the Microsoft IT Academy
Program.
i. “Microsoft Learning Competency Member” means an active member of the Microsoft Partner Network program in good standing that currently holds the Learning Competency status.
j. “MOC” means the “Official Microsoft Learning Product” instructor-led courseware known as Microsoft Official Course that educates IT professionals and developers on Microsoft technologies.
MCT USE ONL
Y. STUDENT USE PROHIBITED
l. “Personal Device” means one (1) personal computer, device, workstation or other digital electronic device that you personally own or control that meets or exceeds the hardware level specified for the particular Microsoft Instructor-Led Courseware.
m. “Private Training Session” means the instructor-led training classes provided by MPN Members for
corporate customers to teach a predefined learning objective using Microsoft Instructor-Led Courseware. These classes are not advertised or promoted to the general public and class attendance is restricted to individuals employed by or contracted by the corporate customer.
n. “Trainer” means (i) an academically accredited educator engaged by a Microsoft IT Academy Program Member to teach an Authorized Training Session, and/or (ii) a MCT.
o. “Trainer Content” means the trainer version of the Microsoft Instructor-Led Courseware and additional supplemental content designated solely for Trainers’ use to teach a training session using the Microsoft Instructor-Led Courseware. Trainer Content may include Microsoft PowerPoint presentations, trainer preparation guide, train the trainer materials, Microsoft One Note packs, classroom setup guide and Pre-release course feedback form. To clarify, Trainer Content does not include any software, virtual hard disks or virtual machines.
2. USE RIGHTS. The Licensed Content is licensed not sold. The Licensed Content is licensed on a one copy per user basis, such that you must acquire a license for each individual that accesses or uses the Licensed
Content.
2.1 Below are five separate sets of use rights. Only one set of rights apply to you. a. If you are a Microsoft IT Academy Program Member:
i. Each license acquired on behalf of yourselfmay only be used to review one (1) copy of the Microsoft
Instructor-Led Courseware in the form provided to you. If the Microsoft Instructor-Led Courseware is in digital format, you may install one (1) copy on up to three (3) Personal Devices. You may not install the Microsoft Instructor-Led Courseware on a device you do not own or control.
ii. For each license you acquire on behalf of an End User or Trainer, you may either:
1. distribute one (1) hard copy version of the Microsoft Instructor-Led Courseware to one (1) End User who is enrolled in the Authorized Training Session, and only immediately prior to the commencement of the Authorized Training Session that is the subject matter of the Microsoft Instructor-Led Courseware being provided, or
2. provide one (1) End User with the unique redemption code and instructions on how they can access one (1) digital version of the Microsoft Instructor-Led Courseware, or
3. provide one (1) Trainer with the unique redemption code and instructions on how they can access one (1) Trainer Content,
provided you comply with the following:
iii. you will only provide access to the Licensed Content to those individuals who have acquired a valid license to the Licensed Content,
iv. you will ensure each End User attending an Authorized Training Session has their own valid licensed copy of the Microsoft Instructor-Led Courseware that is the subject of the Authorized Training Session,
v. you will ensure that each End User provided with the hard-copy version of the Microsoft Instructor-Led Courseware will be presented with a copy of this agreement and each End User will agree that their use of the Microsoft Instructor-Led Courseware will be subject to the terms in this agreement prior to providing them with the Microsoft Instructor-Led Courseware. Each individual will be required to denote their acceptance of this agreement in a manner that is enforceable under local law prior to their accessing the Microsoft Instructor-Led Courseware,
vi. you will ensure that each Trainer teaching an Authorized Training Session has their own valid licensed copy of the Trainer Content that is the subject of the Authorized Training Session,
MCT USE ONL
Y. STUDENT USE PROHIBITED
vii. you will only use qualified Trainers who have in-depth knowledge of and experience with the Microsoft technology that is the subject of the Microsoft Instructor-Led Courseware being taught for all your Authorized Training Sessions,
viii. you will only deliver a maximum of 15 hours of training per week for each Authorized Training Session that uses a MOC title, and
ix. you acknowledge that Trainers that are not MCTs will not have access to all of the trainer resources for the Microsoft Instructor-Led Courseware.
b. If you are a Microsoft Learning Competency Member:
i. Each license acquired on behalf of yourselfmay only be used to review one (1) copy of the Microsoft
Instructor-Led Courseware in the form provided to you. If the Microsoft Instructor-Led Courseware is in digital format, you may install one (1) copy on up to three (3) Personal Devices. You may not install the Microsoft Instructor-Led Courseware on a device you do not own or control.
ii. For each license you acquire on behalf of an End User or Trainer, you may either:
1. distribute one (1) hard copy version of the Microsoft Instructor-Led Courseware to one (1) End User attending the Authorized Training Session and only immediately prior to the
commencement of the Authorized Training Session that is the subject matter of the Microsoft Instructor-Led Courseware provided, or
2. provide one (1) End User attending the Authorized Training Session with the unique redemption code and instructions on how they can access one (1) digital version of the Microsoft Instructor-Led Courseware, or
3. you will provide one (1) Trainer with the unique redemption code and instructions on how they can access one (1) Trainer Content,
provided you comply with the following:
iii. you will only provide access to the Licensed Content to those individuals who have acquired a valid license to the Licensed Content,
iv. you will ensure that each End User attending an Authorized Training Session has their own valid licensed copy of the Microsoft Instructor-Led Courseware that is the subject of the Authorized Training Session,
v. you will ensure that each End User provided with a hard-copy version of the Microsoft Instructor-Led Courseware will be presented with a copy of this agreement and each End User will agree that their use of the Microsoft Instructor-Led Courseware will be subject to the terms in this agreement prior to providing them with the Microsoft Instructor-Led Courseware. Each individual will be required to denote their acceptance of this agreement in a manner that is enforceable under local law prior to their accessing the Microsoft Instructor-Led Courseware,
vi. you will ensure that each Trainer teaching an Authorized Training Session has their own valid licensed copy of the Trainer Content that is the subject of the Authorized Training Session,
vii. you will only use qualified Trainers who hold the applicable Microsoft Certification credential that is the subject of the Microsoft Instructor-Led Courseware being taught for your Authorized Training Sessions,
viii. you will only use qualified MCTs who also hold the applicable Microsoft Certification credential that is the subject of the MOC title being taught for all your Authorized Training Sessions using MOC, ix. you will only provide access to the Microsoft Instructor-Led Courseware to End Users, and x. you will only provide access to the Trainer Content to Trainers.
MCT USE ONL
Y. STUDENT USE PROHIBITED
c. If you are a MPN Member:
i. Each license acquired on behalf of yourself may only be used to review one (1) copy of the Microsoft Instructor-Led Courseware in the form provided to you. If the Microsoft Instructor-Led Courseware is in digital format, you may install one (1) copy on up to three (3) Personal Devices. You may not install the Microsoft Instructor-Led Courseware on a device you do not own or control.
ii. For each license you acquire on behalf of an End User or Trainer, you may either:
1. distribute one (1) hard copy version of the Microsoft Instructor-Led Courseware to one (1) End User attending the Private Training Session, and only immediately prior to the commencement of the Private Training Session that is the subject matter of the Microsoft Instructor-Led Courseware being provided, or
2. provide one (1) End User who is attending the Private Training Session with the unique redemption code and instructions on how they can access one (1) digital version of the Microsoft Instructor-Led Courseware, or
3. you will provide one (1) Trainer who is teaching the Private Training Session with the unique redemption code and instructions on how they can access one (1) Trainer Content,
provided you comply with the following:
iii. you will only provide access to the Licensed Content to those individuals who have acquired a valid license to the Licensed Content,
iv. you will ensure that each End User attending an Private Training Session has their own valid licensed copy of the Microsoft Instructor-Led Courseware that is the subject of the Private Training Session, v. you will ensure that each End User provided with a hard copy version of the Microsoft Instructor-Led
Courseware will be presented with a copy of this agreement and each End User will agree that their use of the Microsoft Instructor-Led Courseware will be subject to the terms in this agreement prior to providing them with the Microsoft Instructor-Led Courseware. Each individual will be required to denote their acceptance of this agreement in a manner that is enforceable under local law prior to their accessing the Microsoft Instructor-Led Courseware,
vi. you will ensure that each Trainer teaching an Private Training Session has their own valid licensed copy of the Trainer Content that is the subject of the Private Training Session,
vii. you will only use qualified Trainers who hold the applicable Microsoft Certification credential that is the subject of the Microsoft Instructor-Led Courseware being taught for all your Private Training Sessions,
viii. you will only use qualified MCTs who hold the applicable Microsoft Certification credential that is the subject of the MOC title being taught for all your Private Training Sessions using MOC,
ix. you will only provide access to the Microsoft Instructor-Led Courseware to End Users, and x. you will only provide access to the Trainer Content to Trainers.
d. If you are an End User:
For each license you acquire, you may use the Microsoft Instructor-Led Courseware solely for your personal training use. If the Microsoft Instructor-Led Courseware is in digital format, you may access the Microsoft Instructor-Led Courseware online using the unique redemption code provided to you by the training provider and install and use one (1) copy of the Microsoft Instructor-Led Courseware on up to three (3) Personal Devices. You may also print one (1) copy of the Microsoft Instructor-Led Courseware. You may not install the Microsoft Instructor-Led Courseware on a device you do not own or control. e. If you are a Trainer.
i. For each license you acquire, you may install and use one (1) copy of the Trainer Content in the
form provided to you on one (1) Personal Device solely to prepare and deliver an Authorized Training Session or Private Training Session, and install one (1) additional copy on another Personal Device as a backup copy, which may be used only to reinstall the Trainer Content. You may not install or use a copy of the Trainer Content on a device you do not own or control. You may also print one (1) copy of the Trainer Content solely to prepare for and deliver an Authorized Training Session or Private Training Session.
MCT USE ONL
Y. STUDENT USE PROHIBITED
ii. You may customize the written portions of the Trainer Content that are logically associated with instruction of a training session in accordance with the most recent version of the MCT agreement. If you elect to exercise the foregoing rights, you agree to comply with the following: (i)
customizations may only be used for teaching Authorized Training Sessions and Private Training Sessions, and (ii) all customizations will comply with this agreement. For clarity, any use of
“customize” refers only to changing the order of slides and content, and/or not using all the slides or content, it does not mean changing or modifying any slide or content.
2.2 Separation of Components. The Licensed Content is licensed as a single unit and you may not separate their components and install them on different devices.
2.3 Redistribution of Licensed Content. Except as expressly provided in the use rights above, you may not distribute any Licensed Content or any portion thereof (including any permitted modifications) to any third parties without the express written permission of Microsoft.
2.4 Third Party Notices. The Licensed Content may include third party code tent that Microsoft, not the third party, licenses to you under this agreement. Notices, if any, for the third party code ntent are included for your information only.
2.5 Additional Terms. Some Licensed Content may contain components with additional terms, conditions, and licenses regarding its use. Any non-conflicting terms in those conditions and licenses also apply to your use of that respective component and supplements the terms described in this agreement.
3. LICENSED CONTENT BASED ON PRE-RELEASE TECHNOLOGY. If the Licensed Content’s subject
matter is based on a pre-release version of Microsoft technology (“Pre-release”), then in addition to the other provisions in this agreement, these terms also apply:
a. Pre-Release Licensed Content. This Licensed Content subject matter is on the Pre-release version of the Microsoft technology. The technology may not work the way a final version of the technology will and we may change the technology for the final version. We also may not release a final version. Licensed Content based on the final version of the technology may not contain the same information as the Licensed Content based on the Pre-release version. Microsoft is under no obligation to provide you with any further content, including any Licensed Content based on the final version of the technology. b. Feedback. If you agree to give feedback about the Licensed Content to Microsoft, either directly or
through its third party designee, you give to Microsoft without charge, the right to use, share and commercialize your feedback in any way and for any purpose. You also give to third parties, without charge, any patent rights needed for their products, technologies and services to use or interface with any specific parts of a Microsoft technology, Microsoft product, or service that includes the feedback. You will not give feedback that is subject to a license that requires Microsoft to license its technology, technologies, or products to third parties because we include your feedback in them. These rights survive this agreement.
c. Pre-release Term. If you are an Microsoft IT Academy Program Member, Microsoft Learning
Competency Member, MPN Member or Trainer, you will cease using all copies of the Licensed Content on the Pre-release technology upon (i) the date which Microsoft informs you is the end date for using the
Licensed Content on the Pre-release technology,or (ii) sixty (60) days after the commercial release of the
technology that is the subject of the Licensed Content, whichever is earliest (“Pre-release term”). Upon expiration or termination of the Pre-release term, you will irretrievably delete and destroy all copies of the Licensed Content in your possession or under your control.
MCT USE ONL
Y. STUDENT USE PROHIBITED
4. SCOPE OF LICENSE. The Licensed Content is licensed, not sold. This agreement only gives you some
rights to use the Licensed Content. Microsoft reserves all other rights. Unless applicable law gives you more rights despite this limitation, you may use the Licensed Content only as expressly permitted in this
agreement. In doing so, you must comply with any technical limitations in the Licensed Content that only allows you to use it in certain ways. Except as expressly permitted in this agreement, you may not:
• access or allow any individual to access the Licensed Content if they have not acquired a valid license
for the Licensed Content,
• alter, remove or obscure any copyright or other protective notices (including watermarks), branding
or identifications contained in the Licensed Content,
• modify or create a derivative work of any Licensed Content,
• publicly display, or make the Licensed Content available for others to access or use,
• copy, print, install, sell, publish, transmit, lend, adapt, reuse, link to or post, make available or
distribute the Licensed Content to any third party,
• work around any technical limitations in the Licensed Content, or
• reverse engineer, decompile, remove or otherwise thwart any protections or disassemble the
Licensed Content except and only to the extent that applicable law expressly permits, despite this limitation.
5. RESERVATION OF RIGHTS AND OWNERSHIP. Microsoft reserves all rights not expressly granted to
you in this agreement. The Licensed Content is protected by copyright and other intellectual property laws and treaties. Microsoft or its suppliers own the title, copyright, and other intellectual property rights in the Licensed Content.
6. EXPORT RESTRICTIONS. The Licensed Content is subject to United States export laws and regulations.
You must comply with all domestic and international export laws and regulations that apply to the Licensed Content. These laws include restrictions on destinations, end users and end use. For additional information, see www.microsoft.com/exporting.
7. SUPPORT SERVICES. Because the Licensed Content is “as is”, we may not provide support services for it. 8. TERMINATION. Without prejudice to any other rights, Microsoft may terminate this agreement if you fail
to comply with the terms and conditions of this agreement. Upon termination of this agreement for any reason, you will immediately stop all use of and delete and destroy all copies of the Licensed Content in your possession or under your control.
9. LINKS TO THIRD PARTY SITES. You may link to third party sites through the use of the Licensed
Content. The third party sites are not under the control of Microsoft, and Microsoft is not responsible for the contents of any third party sites, any links contained in third party sites, or any changes or updates to third party sites. Microsoft is not responsible for webcasting or any other form of transmission received from any third party sites. Microsoft is providing these links to third party sites to you only as a
convenience, and the inclusion of any link does not imply an endorsement by Microsoft of the third party site.
10. ENTIRE AGREEMENT. This agreement, and any additional terms for the Trainer Content, updates and
supplements are the entire agreement for the Licensed Content, updates and supplements.
11. APPLICABLE LAW.
a. United States. If you acquired the Licensed Content in the United States, Washington state law governs the interpretation of this agreement and applies to claims for breach of it, regardless of conflict of laws principles. The laws of the state where you live govern all other claims, including claims under state consumer protection laws, unfair competition laws, and in tort.
MCT USE ONL
Y. STUDENT USE PROHIBITED
b. Outside the United States. If you acquired the Licensed Content in any other country, the laws of that country apply.
12. LEGAL EFFECT. This agreement describes certain legal rights. You may have other rights under the laws
of your country. You may also have rights with respect to the party from whom you acquired the Licensed Content. This agreement does not change your rights under the laws of your country if the laws of your country do not permit it to do so.
13. DISCLAIMER OF WARRANTY. THE LICENSED CONTENT IS LICENSED "AS-IS" AND "AS AVAILABLE." YOU BEAR THE RISK OF USING IT. MICROSOFT AND ITS RESPECTIVE
AFFILIATES GIVES NO EXPRESS WARRANTIES, GUARANTEES, OR CONDITIONS. YOU MAY HAVE ADDITIONAL CONSUMER RIGHTS UNDER YOUR LOCAL LAWS WHICH THIS AGREEMENT CANNOT CHANGE. TO THE EXTENT PERMITTED UNDER YOUR LOCAL LAWS, MICROSOFT AND ITS RESPECTIVE AFFILIATES EXCLUDES ANY IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT.
14. LIMITATION ON AND EXCLUSION OF REMEDIES AND DAMAGES. YOU CAN RECOVER FROM MICROSOFT, ITS RESPECTIVE AFFILIATES AND ITS SUPPLIERS ONLY DIRECT DAMAGES UP TO US$5.00. YOU CANNOT RECOVER ANY OTHER DAMAGES, INCLUDING CONSEQUENTIAL, LOST PROFITS, SPECIAL, INDIRECT OR INCIDENTAL DAMAGES.
This limitation applies to
o anything related to the Licensed Content, services, content (including code) on third party Internet
sites or third-party programs; and
o claims for breach of contract, breach of warranty, guarantee or condition, strict liability, negligence,
or other tort to the extent permitted by applicable law.
It also applies even if Microsoft knew or should have known about the possibility of the damages. The above limitation or exclusion may not apply to you because your country may not allow the exclusion or limitation of incidental, consequential or other damages.
Please note: As this Licensed Content is distributed in Quebec, Canada, some of the clauses in this agreement are provided below in French.
Remarque : Ce le contenu sous licence étant distribué au Québec, Canada, certaines des clauses dans ce contrat sont fournies ci-dessous en français.
EXONÉRATION DE GARANTIE. Le contenu sous licence visé par une licence est offert « tel quel ». Toute
utilisation de ce contenu sous licence est à votre seule risque et péril. Microsoft n’accorde aucune autre garantie expresse. Vous pouvez bénéficier de droits additionnels en vertu du droit local sur la protection dues
consommateurs, que ce contrat ne peut modifier. La ou elles sont permises par le droit locale, les garanties implicites de qualité marchande, d’adéquation à un usage particulier et d’absence de contrefaçon sont exclues.
LIMITATION DES DOMMAGES-INTÉRÊTS ET EXCLUSION DE RESPONSABILITÉ POUR LES
DOMMAGES. Vous pouvez obtenir de Microsoft et de ses fournisseurs une indemnisation en cas de dommages
directs uniquement à hauteur de 5,00 $ US. Vous ne pouvez prétendre à aucune indemnisation pour les autres dommages, y compris les dommages spéciaux, indirects ou accessoires et pertes de bénéfices.
Cette limitation concerne:
• tout ce qui est relié au le contenu sous licence, aux services ou au contenu (y compris le code) figurant sur des sites Internet tiers ou dans des programmes tiers; et.
• les réclamations au titre de violation de contrat ou de garantie, ou au titre de responsabilité stricte, de négligence ou d’une autre faute dans la limite autorisée par la loi en vigueur.
MCT USE ONL
Y. STUDENT USE PROHIBITED
Elle s’applique également, même si Microsoft connaissait ou devrait connaître l’éventualité d’un tel dommage. Si votre pays n’autorise pas l’exclusion ou la limitation de responsabilité pour les dommages indirects, accessoires ou de quelque nature que ce soit, il se peut que la limitation ou l’exclusion ci-dessus ne s’appliquera pas à votre égard.
EFFET JURIDIQUE. Le présent contrat décrit certains droits juridiques. Vous pourriez avoir d’autres droits
prévus par les lois de votre pays. Le présent contrat ne modifie pas les droits que vous confèrent les lois de votre pays si celles-ci ne le permettent pas.
MCT USE ONL
Y. STUDENT USE PROHIBITED
MCT USE ONL
Y. STUDENT USE PROHIBITED
xii 20462C: Administering Microsoft SQL Server Databases
Acknowledgments
Microsoft Learning would like to acknowledge and thank the following for their contribution towards developing this title. Their effort at various stages in the development has ensured that you have a good classroom experience.
Design and Development
This course was designed and developed by Content Master, a division of CM Group Ltd. Content Master is a global provider of technical content and learning services.
Graeme Malcolm – Lead Content Developer
Graeme Malcolm is a Microsoft SQL Server subject matter expert and professional content developer at Content Master—a division of CM Group Ltd. As a Microsoft Certified Trainer, Graeme has delivered training courses on SQL Server since version 4.2; as an author, Graeme has written numerous books, articles, and training courses on SQL Server; and as a consultant, Graeme has designed and implemented business solutions based on SQL Server for customers all over the world.
Lin Joyner – Contributing Content Developer
Lin is an experienced SQL Server developer and administrator, having worked with SQL Server since version 6.0. She designs and writes SQL Server and .NET development training materials. Prior to joining Content Master, Lin was a professional trainer for five years when she held the MCT and MCSD
MCT USE ONL
Y. STUDENT USE PROHIBITED
20462C: Administering Microsoft SQL Server Databases xiii
Contents
Module 1: Introduction to SQL Server 2014 Database Administration
Module Overview 1-1
Lesson 1: Database Administration Overview 1-2 Lesson 2: Introduction to the SQL Server Platform 1-5 Lesson 3: Database Management Tools and Techniques 1-11 Lab: Using SQL Server Administrative Tools 1-16
Module Review and Takeaways 1-21
Module 2: Installing and Configuring SQL Server 2014
Module Overview 2-1
Lesson 1: Planning SQL Server Installation 2-2 Lesson 2: Installing SQL Server 2014 2-10 Lesson 3: Post-Installation Configuration 2-15 Lab: Installing SQL Server 2014 2-18
Module Review and Takeaways 2-21
Module 3: Working with Databases and Storage
Module Overview 3-1
Lesson 1: Introduction to Data Storage with SQL Server 3-2 Lesson 2: Managing Storage for System Databases 3-8 Lesson 3: Managing Storage for User Databases 3-12 Lesson 4: Moving Database Files 3-21 Lesson 5: Configuring the Buffer Pool Extension 3-24 Lab: Managing Database Storage 3-28
Module Review and Takeaways 3-32
Module 4: Planning and Implementing a Backup Strategy
Module Overview 4-1
Lesson 1: Understanding SQL Server Recovery Models 4-2 Lesson 2: Planning a Backup Strategy 4-8 Lesson 3: Backing up Databases and Transaction Logs 4-15 Lesson 4: Using Backup Options 4-24 Lesson 5: Ensuring Backup Reliability 4-29 Lab: Backing Up Databases 4-35
MCT USE ONL
Y. STUDENT USE PROHIBITED
xiv 20462C: Administering Microsoft SQL Server Databases
Module 5: Restoring SQL Server 2014 Databases
Module Overview 5-1
Lesson 1: Understanding the Restore Process 5-2 Lesson 2: Restoring Databases 5-6 Lesson 3: Advanced Restore Scenarios 5-11 Lesson 4: Point-in-Time Recovery 5-17 Lab: Restoring SQL Server Databases 5-21
Module Review and Takeaways 5-25
Module 6: Importing and Exporting Data
Module Overview 6-1
Lesson 1: Introduction to Transferring Data 6-2 Lesson 2: Importing and Exporting Data 6-9 Lesson 3: Copying or Moving a Database 6-17 Lab: Importing and Exporting Data 6-22
Module Review and Takeaways 6-26
Module 7: Monitoring SQL Server 2014
Module Overview 7-1
Lesson 1: Introduction to Monitoring SQL Server 7-2 Lesson 2: Dynamic Management Views and Functions 7-7 Lesson 3: Performance Monitor 7-11 Lab: Monitoring SQL Server 2014 7-15
Module Review and Takeaways 7-18
Module 8: Tracing SQL Server Activity
Module Overview 8-1
Lesson 1: Tracing SQL Server Workload Activity 8-2 Lesson 2: Using Traces 8-9 Lab: Tracing SQL Server Workload Activity 8-18
MCT USE ONL
Y. STUDENT USE PROHIBITED
20462C: Administering Microsoft SQL Server Databases xv
Module 9: Managing SQL Server Security
Module Overview 9-1
Lesson 1: Introduction to SQL Server Security 9-2 Lesson 2: Managing Server-Level Security 9-9 Lesson 3: Managing Database-Level Principals 9-18 Lesson 4: Managing Database Permissions 9-28 Lab: Managing SQL Server Security 9-36
Module Review and Takeaways 9-44
Module 10: Auditing Data Access and Encrypting Data
Module Overview 10-1
Lesson 1: Auditing Data Access in SQL Server 10-2 Lesson 2: Implementing SQL Server Audit 10-7 Lesson 3: Encrypting Databases 10-16 Lab: Auditing Data Access and Encrypting Data 10-22
Module Review and Takeaways 10-26
Module 11: Performing Ongoing Database Maintenance
Module Overview 11-1
Lesson 1: Ensuring Database Integrity 11-2 Lesson 2: Maintaining Indexes 11-7 Lesson 3: Automating Routine Database Maintenance 11-14 Lab: Performing Ongoing Database Maintenance 11-17
Module Review and Takeaways 11-20
Module 12: Automating SQL Server 2014 Management
Module Overview 12-1
Lesson 1: Automating SQL Server Management 12-2 Lesson 2: Implementing SQL Server Agent Jobs 12-5 Lesson 3: Managing SQL Server Agent Jobs 12-11 Lesson 4: Managing Job Step Security Contexts 12-15 Lesson 5: Managing Jobs on Multiple Servers 12-20 Lab: Automating SQL Server Management 12-25
MCT USE ONL
Y. STUDENT USE PROHIBITED
xvi 20462C: Administering Microsoft SQL Server Databases
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
Module Overview 13-1
Lesson 1: Monitoring SQL Server Errors 13-2 Lesson 2: Configuring Database Mail 13-6 Lesson 3: Configuring Operators, Notifications, and Alerts 13-11 Lab: Using Notifications and Alerts 13-17
Module Review and Takeaways 13-20
Lab Answer Keys
Module 1 Lab: Using SQL Server Administrative Tools L01-1 Module 2 Lab: Installing SQL Server 2014 L02-1 Module 3 Lab: Managing Database Storage L03-1 Module 4 Lab: Backing Up Databases L04-1 Module 5 Lab: Restoring SQL Server Databases L05-1 Module 6 Lab: Importing and Exporting Data L06-1 Module 7 Lab: Monitoring SQL Server 2014 L07-1 Module 8 Lab: Tracing SQL Server Workload Activity L08-1 Module 9 Lab: Managing SQL Server Security L09-1 Module 10 Lab: Auditing Data Access and Encrypting Data L10-1 Module 11 Lab: Performing Ongoing Database Maintenance L11-1 Module 12 Lab: Automating SQL Server Management L12-1 Module 13 Lab: Using Notifications and Alerts L13-1
MCT USE ONL
Y. STUDENT USE PROHIBITED
About This Course xvii
About This Course
This section provides you with a brief description of the course, audience, required prerequisites, and course objectives.
Course Description
This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2014 database. The course focuses on teaching individuals how to use SQL Server 2014 product features and tools related to maintaining a database.
Audience
The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of
responsibility, or work in environments where databases play a key role in their primary job.
The secondary audience for this course is individuals who develop applications that deliver content from SQL Server databases.
Student Prerequisites
This course requires that you meet the following prerequisites:
Basic knowledge of the Microsoft Windows operating system and its core functionality. Working knowledge of Transact-SQL.
Working knowledge of relational databases. Some experience with database design.
Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills:
20461C: Querying Microsoft SQL Server
Course Objectives
After completing this course, students will be able to:
Describe core database administration tasks and tools. Install and configure SQL Server 2014
Configure SQL Server databases and storage. Plan and implement a backup strategy. Restore databases from backups. Import and export data.
Monitor SQL Server. Trace SQL Server activity. Manage SQL Server security. Audit data access and encrypt data.
MCT USE ONL
Y. STUDENT USE PROHIBITED
xviii About This Course
Perform ongoing database maintenance.
Automate SQL Server maintenance with SQL Server Agent Jobs. Configure Database Mail, alerts and notifications.
Course Outline
This section provides an outline of the course:
Module 1: Introduction to SQL Server 2014 Database Administration Module 2: Installing and Configuring SQL Server 2014
Module 3: Working with Databases and Storage
Module 4: Planning and Implementing a Backup Strategy Module 5: Restoring SQL Server 2014 Databases
Module 6: Importing and Exporting Data Module 7: Monitoring SQL Server 2014 Module 8: Tracing SQL Server Activity Module 9: Managing SQL Server Security
Module 10: Auditing Data Access and Encrypting Data Module 11: Performing Ongoing Database Maintenance Module 12: Automating SQL Server 2014 Management
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
Course Materials
The following materials are included with your kit:
Course Handbook. A succinct classroom learning guide that provides all the critical technical
information in a crisp, tightly-focused format, which is just right for an effective in-class learning experience.
Lessons: Guide you through the learning objectives and provide the key points that are critical to
the success of the in-class learning experience.
Labs: Provide a real-world, hands-on platform for you to apply the knowledge and skills learned
in the module.
Module Reviews and Takeaways: Provide improved on-the-job reference material to boost
knowledge and skills retention.
Lab Answer Keys: Provide step-by-step lab solution guidance at your fingertips when it’s
needed.
Lessons: Include detailed information for each topic, expanding on the content in the Course
Handbook.
Labs: Include complete lab exercise information and answer keys in digital form to use during lab
MCT USE ONL
Y. STUDENT USE PROHIBITED
About This Course xix
Resources: Include well-categorized additional resources that give you immediate access to the
most up-to-date premium content on TechNet, MSDN, Microsoft Press
Student Course Files: Include the Allfiles.exe, a self-extracting executable file that contains all
the files required for the labs and demonstrations.
Course evaluation. At the end of the course, you will have the opportunity to complete an
online evaluation to provide feedback on the course, training facility, and instructor.
To provide additional comments or feedback on the course, send e-mail to support@mscourseware.com. To inquire about the Microsoft Certification Program, send e-mail to mcphelp@microsoft.com.
Virtual Machine Environment
This section provides the information for setting up the classroom environment to support the business scenario of the course.
Virtual Machine Configuration
In this course, you will use Microsoft Hyper-V to perform the labs.
The following table shows the role of each virtual machine used in this course:
Virtual machine Role
20462C-MIA-DC Domain Controller
20462C-MIA-SQL SQL Server VM
Software Configuration
The following software is installed on each VM: SQL Server 2014 (on the SQL Server VM)
Course Files
There are files associated with the labs in this course. The lab files are located in the folder D:\Labfiles on the student computers.
Classroom Setup
Each classroom computer will have the same virtual machine configured in the same way.
Course Hardware Level
To ensure a satisfactory student experience, Microsoft Learning requires a minimum equipment configuration for trainer and student computers in all Microsoft Certified Partner for Learning Solutions (CPLS) classrooms in which Official Microsoft Learning Product courses are taught. This course requires hardware level 6+.
Processor: Intel Virtualization Technology (Intel VT) or AMD Virtualization (AMD-V) Hard Disk: Dual 120 GB hard disks 7200 RM SATA or better (Striped)
RAM: 12 GB or higher DVD/CD: DVD drive
MCT USE ONL
Y. STUDENT USE PROHIBITED
xx About This Course
Video Adapter/Monitor: 17-inch Super VGA (SVGA) Microsoft Mouse or compatible pointing device Sound card with amplified speakers
In addition, the instructor computer must be connected to a projection display device that supports SVGA 1024 x 768 pixels, 16 bit colors.
Note: For the best classroom experience, a computer with solid state disks (SSDs) is recommended. For
optimal performance, adapt the instructions below to install the 20462C-MIA-SQL virtual machine on a different physical disk than the other virtual machines to reduce disk contention.
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-1
Module 1
Introduction to SQL Server 2014 Database Administration
Contents:
Module Overview 1-1
Lesson 1: Database Administration Overview 1-2
Lesson 2: Introduction to the SQL Server Platform 1-5
Lesson 3: Database Management Tools and Techniques 1-11
Lab: Using SQL Server Administrative Tools 1-16
Module Review and Takeaways 1-21
Module Overview
This module introduces the Microsoft® SQL Server® 2014 platform. It describes the components, editions, and versions of SQL Server 2014, and the tasks that a database administrator commonly performs for a SQL Server instance.
Objectives
After completing this module, you will be able to: Describe the SQL Server platform.
Describe common database administration tasks. Use SQL Server administration tools.
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-2 Introduction to SQL Server 2014 Database Administration
Lesson 1
Database Administration Overview
Most organizations use software applications to manage business processes and activities, and these applications generally store data in a database. Organizations are increasingly reliant on applications and the data they store, and often databases are a “mission-critical” component of a business’s information technology (IT) infrastructure.
The role of a database administrator (DBA) includes a wide range of responsibilities and tasks that ensure that the databases an organization relies on are maintained and kept at optimum efficiency. This lesson describes some of these responsibilities and tasks, which will be explored in greater detail throughout the rest of this course.
Lesson Objectives
After completing this lesson, you will be able to:
Describe common characteristics of a database administrator. Describe common database administration tasks.
Describe the importance of documentation in a database solution.
What Makes a Good Database Administrator?
While this course is focused on performingdatabase maintenance tasks for a SQL Server 2014 database, it is important to consider the important characteristics of a successful DBA. While there are many thousands of DBAs working successfully throughout the world, each with their own experience and personality, some common factors that contribute to success include:
Technological knowledge and skills. A good
DBA not only requires in-depth knowledge of the database platform used to host the database; but also needs to be familiar with
host operating system configuration, storage devices, and networking.
Business-awareness. While a DBA is a technical role, a good DBA typically understands the business
context within which the database operates, and its role in supporting the business.
Organizational skills. Database systems can be complex, with a lot of components and subsystems
to manage. Some tasks need to be performed at specific times, and a good DBA must keep track of these tasks while also responding to unexpected issues as they arise.
Ability to prioritize. When unexpected problems affect a database, application users and business
stakeholders typically make demands on the DBA to resolve the situation based on their individual requirements. A good DBA must prioritize the resolution of issues based on factors such as service-level agreements (SLAs) with the business for database services, the number of users and systems affected, and the degree to which the problems are affecting ongoing operations.
MCT USE ONL
Y. STUDENT USE PROHIBITED
Administering Microsoft® SQL Server® Databases 1-3
Common Database Administration Tasks
Depending on the organization, a single DBAmight be responsible for managing multiple database servers and databases, or multiple DBAs might each take responsibility for a specific application, database server, business unit, or geographic location.
Regardless of how database management
responsibility is apportioned, common tasks that a DBA must perform include:
Provisioning database servers and databases. This can involve installing and
configuring instances of SQL Server on
physical or virtual servers, or creating new virtual machines based on template images. It can also involve creating databases and allocating their data and log files to appropriate storage devices. Maintaining database files and objects. After a database has been created and populated with
data in tables and indexes, it requires ongoing maintenance to ensure it continues to perform optimally. This involves reducing any fragmentation that occurs in data files as records are added and deleted, ensuring that data files are kept at an appropriate size, and ensuring that the logical and physical data structures remain consistent.
Managing recovery in the event of database failure. Databases are often critical to business
operations, and a core responsibility for a DBA is to plan an appropriate backup and recovery strategy for each database, ensure backups are performed, and restore the database in the event of a failure. Importing and exporting data. Data is often transferred between systems, so DBAs often need to
extract data from, or import data to, databases.
Applying security to data. An organization’s database servers often contain its most valuable asset –
the data that enables the business to operate. Security breaches can be costly, expensive and time-consuming to trace and repair; and damaging to customer trust and confidence. A DBA must implement security policies that enable users to access the data they need, while ensuring that the business meets its legal compliance obligations, protects its assets, and mitigates the risks associated with security breaches.
Monitoring and troubleshooting database systems. Many database administration operations are
reactive, in the sense that they involve taking action to troubleshoot and remediate a problem that has been identified. Successful DBAs also undertake a proactive approach, in which they monitor systems against an established baseline to try to detect potential problems before they impact data operations.
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-4 Introduction to SQL Server 2014 Database Administration
Documenting Database Management Procedures
One of the key attributes of a successful DBA is theability to be organized. Most DBAs are familiar with the systems they manage, and the tasks that must be performed on a day-to-day basis. However, even the best DBAs do not rely purely on their memory. DBAs commonly compile and maintain documentation, often referred to as a “run book”, that includes information such as: Configuration settings and file locations. Personnel contact details.
Standard maintenance procedures and schedules.
Disaster recovery procedures.
While it may be unexciting, maintaining documentation for the database system is an important part of database administration. A detailed run book can be invaluable when a new DBA must take over responsibility for managing a database, or when an unexpected emergency occurs and the DBA is not present to deal with it. Even when the DBA is available to respond to a disaster such as the failure of a database server, having clearly documented steps to recover the database reduces the sense of panic and pressure, and enables a faster resolution of the problem.
MCT USE ONL
Y. STUDENT USE PROHIBITED
Administering Microsoft® SQL Server® Databases 1-5
Lesson 2
Introduction to the SQL Server Platform
As a DBA, it is important to be familiar with the database management system used to store your data. SQL Server is a platform for developing business applications that are data focused. Rather than being a single monolithic application, SQL Server is structured as a series of components. It is important to understand the use of each of these.
You can install more than one copy of SQL Server on a server. Each of these is called an instance and can be configured and managed independently.
SQL Server ships in a variety of editions, each with a different set of capabilities for different scenarios. It is important to understand the target business cases for each of the SQL Server editions and how the evolution through a series of improving versions over many years results in today’s stable and robust platform.
Lesson Objectives
After completing this lesson, you will be able to:
Explain the role of each component that makes up the SQL Server platform. Describe the functionality that SQL Server instances provide.
Explain the available SQL Server editions. Explain how SQL Server has evolved.
SQL Server Components
SQL Server is a very good relational database engine, but it offers more than just that. It is a complete data platform comprising of many components.
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-6 Introduction to SQL Server 2014 Database Administration
Component Description
Database Engine The SQL Server database engine is the heart of the SQL Server platform. It provides a high-performance, scalable relational database engine based on the SQL language that can be used to host Online Transaction Processing (OLTP) databases for business applications and data
warehouse solutions. SQL Server 2014 also includes a memory-optimized database engine which uses in-memory technology to improve
performance for short-running transactions.
Analysis Services SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) engine that works with analytic cubes and tables. It is used to implement enterprise BI solutions for data analysis and data mining. Integration Services SQL Server Integration Services (SSIS) is an extract, transform, and load
(ETL) platform tool for orchestrating the movement of data in both directions between SQL Server components and external systems. Reporting Services SQL Server Reporting Services (SSRS) is a reporting engine based on web
services, providing a web portal and end-user reporting tools. It can be installed in native mode, or integrated with Microsoft SharePoint Server. Master Data Services SQL Server Master Data Services (MDS) provides tooling and a hub for
managing master or reference data.
Data Quality Services SQL Server Data Quality Services (DQS) is a knowledge-driven data quality tool for data cleansing and matching.
StreamInsight SQL Server StreamInsight provides a platform for building applications that perform complex event processing for streams of real-time data. Full-Text Search Full-Text Search is a feature of the database engine that provides a
sophisticated semantic search facility for text-based data. Replication The SQL Server database engine includes Replication, a set of
technologies for synchronizing data between servers to meet data distribution needs.
PowerPivot for
SharePoint Server PowerPivot for SharePoint is a specialized implementation of SQL Server Analysis Services that can be installed in a Microsoft SharePoint Server farm to enable tabular data modeling in shared Microsoft Excel
workbooks.
PowerPivot is also available natively in Excel. Power View for
SharePoint Server Power View for SharePoint is a component of SQL Server Reporting Services when installed in SharePoint-Integrated mode. It provides interactive data exploration, visualization, and presentation experience that encourages intuitive, impromptu reporting.
MCT USE ONL
Y. STUDENT USE PROHIBITED
Administering Microsoft® SQL Server® Databases 1-7
SQL Server Instances
It is sometimes useful to install more than one copy of a SQL Server component on a single server. Many SQL Server components can be installed more than once as separate instances.
SQL Server Instances
The ability to install multiple instances of SQL Server components on a single server is useful in a number of situations:
You may want to have different administrators or security environments for sets of databases. You can manage and secure each instance of SQL Server separately.
Some of your applications may require server configurations that are inconsistent or incompatible with the server requirements of other applications. You can configure each instance of SQL Server independently.
Your application databases might need different levels of service, particularly in relation to availability. You can use SQL Server instances to separate workloads with differing service level agreements (SLAs). You might need to support different versions or editions of SQL Server.
Your applications might require different server-level collations. Although each database can have different collations, an application might be dependent on the collation of the tempdb database when the application is using temporary objects.
Different versions of SQL Server can also be installed side-by-side using multiple instances. This can assist when testing upgrade scenarios or performing upgrades.
Default and Named Instances
Prior to SQL Server 2000, only a single copy of SQL Server could be installed on a server system. SQL Server was addressed by the name of the Windows server on which it is hosted. To maintain backward compatibility, this mode of connection is still supported and is known as the default instance. In internal configuration tools, a default instance of the database engine is named MSSQLSERVER.
Additional instances of SQL Server require an instance name that you can use in conjunction with the server name and are known as named instances. If you want all your instances to be named instances, you do not need to install a default instance first. You cannot install all components of SQL Server in more than one instance. A substantial change in SQL Server 2012 allows for multiple instance support for SQL Server Integration Services (SSIS). To access a named instance, client applications use the address Server-Name\Instance-Name. For example, a named instance called Test on a Windows server called
APPSERVER1 would be addressed as APPSERVER1\Test.
There is no need to install SQL Server tools and utilities more than once on a server. You can use a single installation of the tools to manage and configure all instances.
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-8 Introduction to SQL Server 2014 Database Administration
SQL Server Editions
SQL Server is available in a wide variety of editions, with different price points and levels of capability.
SQL Server Editions
Each SQL Server edition is targeted to a specific business use case, as shown in the table on the next page:
Edition Business Use Case
Parallel Data Warehouse Uses massively parallel processing (MPP) to execute queries against vast amounts of data quickly. Parallel Data Warehouse systems are sold as a complete "appliance" rather than through standard software licenses.
Enterprise Provides the highest levels of reliability for demanding workloads. Business Intelligence Adds Business Intelligence (BI) to the offerings from Standard Edition. Standard Delivers a reliable, complete data management platform.
Express Provides a free edition for lightweight web and small server-based applications.
Compact Provides a free edition for stand-alone and occasionally connected mobile applications, optimized for a very small memory footprint. Developer Allows building, testing, and demonstrating all SQL Server
functionality.
Web Provides a secure, cost effective, and scalable platform for public websites and applications.
Microsoft Azure SQL
MCT USE ONL
Y. STUDENT USE PROHIBITED
Administering Microsoft® SQL Server® Databases 1-9
SQL Server Versions
SQL Server has been available for many years, yet it is rapidly evolving with new capabilities and features. It is a platform with a rich history of innovation achieved while maintaining strong levels of stability.
Early Versions
The earliest versions (1.0 and 1.1) were based on the OS/2 operating system. SQL Server 4.2 and later moved to the Microsoft Windows® operating system, initially on Windows NT.
Subsequent Versions
SQL Server 7.0 saw a significant rewrite of the product. Substantial advances were made in reducing the administration workload and OLAP Services (which later became Analysis Services) was introduced. SQL Server 2000 featured support for multiple instances and collations. It also introduced support for data mining. After the product release, SQL Server Reporting Services (SSRS) was introduced as an add-on enhancement to the product, along with support for 64-bit processors.
SQL Server 2005 provided another significant rewrite of many aspects of the product. It introduced support for:
Non-relational data stored and queried as XML.
SQL Server Management Studio (SSMS) was released to replace several previous administrative tools. SSIS replaced a former tool known as Data Transformation Services (DTS).
Another key addition was the introduction of support for objects created using the Common Language Runtime (CLR).
The T-SQL language was substantially enhanced, including structured exception handling.
Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) were introduced to enable detailed health monitoring, performance tuning, and troubleshooting.
Substantial high availability improvements were included in the product; in particular, database mirroring was introduced.
Support for column encryption was introduced. SQL Server 2008 also provided many enhancements:
Filestream support improved the handling of structured and semi-structured data. Spatial data types were introduced.
Database compression and encryption technologies were added.
Specialized date- and time-related data types were introduced, including support for time zones within datetime data.
Full-text indexing was integrated directly within the database engine. (Previously full-text indexing was based on interfaces to operating system level services.)
A policy-based management framework was introduced to assist with a move to more declarative-based management practices, rather than reactive practices.
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-10 Introduction to SQL Server 2014 Database Administration
The enhancements and additions to the product in SQL Server 2008 R2 included: Substantial enhancements to SSRS.
The introduction of advanced analytic capabilities with PowerPivot. Improved multi-server management capabilities were added.
Support for managing reference data was provided with the introduction of Master Data Services. StreamInsight provides the ability to query data that is arriving at high speed, before storing it in a
database.
Data-tier applications assist with packaging database applications as part of application development projects.
The enhancements and additions to the product in SQL Server 2012 included: Further substantial enhancements to SSRS.
Substantial enhancements to SSIS.
The introduction of tabular data models into SQL Server Analysis Services (SSAS). The migration of BI projects into Visual Studio 2010.
The introduction of the AlwaysOn enhancements to SQL Server High Availability. The introduction of Data Quality Services.
Strong enhancements to the T-SQL language such as the addition of sequences, new error-handling capabilities, and new window functions.
The introduction of the FileTable.
The introduction of statistical semantic search. Many general tooling improvements.
SQL Server 2014
SQL Server 2014 builds on the mission-critical capabilities of previous versions and provides even better performance, availability, scalability, and manageability. It provides new in-memory capabilities for OLTP and data warehousing, as well as new disaster recovery functionality through Microsoft Azure™.
MCT USE ONL
Y. STUDENT USE PROHIBITED
Administering Microsoft® SQL Server® Databases 1-11
Lesson 3
Database Management Tools and Techniques
A DBA for a SQL Server database has a range of tools for managing different aspects of the database solution at their disposal. It is important to be familiar with the available tools, and the techniques you can use within them to manage a SQL Server database.
Lesson Objectives
After completing this lesson, you will be able to:
Describe common tools for managing a SQL Server database system.
Use SQL Server Management Studio to manage a database server and databases. Run Transact-SQL statements to perform maintenance tasks.
Use the SQLCMD command line utility. Use Windows PowerShell with SQL Server.
SQL Server Tools
SQL Server provides multiple tools that you can use to manage various aspects of the database system. These tools include:
SQL Server Management Studio (SSMS).
This is the primary database management tool for SQL Server database servers. It provides a graphical user interface (GUI) and a Transact-SQL scripting interface for managing the database engine component and databases. Additionally, you can use SSMS to manage instances of SSAS, SSIS, and SSRS as well as cloud-based databases in Microsoft Azure SQL Database.
SQL Server Configuration Manager (SSCM). You can use SSCM to configure and control SQL Server
services, and to manage server and client network protocols and aliases.
SQL Profiler. When you need to examine activity in a SQL Server database or SSAS data model, you
can use SQL profiler to record a trace that can be viewed or replayed. This enables you to troubleshoot problems or optimize database configuration based on actual usage patterns. SQL Server Database Engine Tuning Advisor (DTA). A properly optimized database uses indexes
and other structures to improve query performance. The DTA provides schema recommendations based on analysis of representative workloads, and can provide a useful starting point for database optimization.
SQL Server Import and Export. This tool is a graphical wizard that simplifies the process of
transferring data in or out of a SQL Server database.
The sqlcmd utility. Pronounced “SQL Command”, this is a command line tool that you can use to
MCT USE ONL
Y. STUDENT USE PROHIBITED
1-12 Introduction to SQL Server 2014 Database Administration
The bcp utility. BCP stands for Bulk Copy Program, and the bcp utility is a command line tool for
importing and exporting data to and from SQL Server.
Additionally, SQL Server includes configuration and management tools for specific components such as Analysis Services, Reporting Services, Data Quality Services, and Master Data Services. You can also install SQL Server Data Tools (SSDT) and SQL Server Data Tools for Business Intelligence (SSDT-BI) add-ins in Microsoft Visual Studio, and use them to develop database and business intelligence (BI) solutions based on SQL Server components.
SQL Server Management Studio
SSMS is the primary tool for managing SQL Server databases. It is based on the Visual Studio shell used for software development projects, and supports the following features: Object Explorer. This is a pane in which you
can connect to SQL Server instances and manage the objects they contains. By default, when you open SSMS you are prompted to connect to a SQL Server instance, and this instance is displayed in Object Explorer. You can then connect to additional instances and view them concurrently.
Code Editor. You can manage database servers and objects using graphical interfaces (typically
opened from Object Explorer), or you can enter and run Transact-SQL statements in the code editor pane. Using Transact-SQL code to perform management tasks enables you to save the commands as scripts, which can be re-executed at a later time or scheduled to run automatically. The code editor in SSMS supports IntelliSense, which provides auto-completion of statements and color-coding of keywords to improve script readability. You can also use snippets to simplify the creation of commonly used statements. SSMS also provides the ability to generate Transact-SQL code for most tasks that can be performed using graphical tools, making it easier to create reusable scripts for administrative tasks.
Solutions and Projects. You can use projects and solutions to keep related scripts, connections, and
other documents together. This can make it easier to keep track of all the script files required to create and manage a database solution.
Reports. SSMS includes an extensible report interface that you can use to view detailed configuration
