professional_microsoft_sql_server_2012_administration.pdf

(71552 KB) Pobierz
937658692.004.png
Professional Microsoft® SQL Server® 2012 Administration
Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2012 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-1-118-10688-4
ISBN: 978-1-118-28684-5 (ebk)
ISBN: 978-1-118-28218-2 (ebk)
ISBN: 978-1-118-28388-2 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108
of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization
through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers,
MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the
Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011,
fax (201) 748-6008, or online at http://www.wiley.com/go/permissions .
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with
respect to the accuracy or completeness of the contents of this work and specifi cally disclaim all warranties, including
without limitation warranties of fi tness for a particular purpose. No warranty may be created or extended by sales or
promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work
is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional
services. If professional assistance is required, the services of a competent professional person should be sought. Neither
the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is
referred to in this work as a citation and/or a potential source of further information does not mean that the author or the
publisher endorses the information the organization or Web site may provide or recommendations it may make. Further,
readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this
work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the
United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with
standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media
such as a CD or DVD that is not included in the version you purchased, you may download this material at
http://booksupport.wiley.com . For more information about Wiley products, visit www.wiley.com .
Library of Congress Control Number: 2012933629
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress
are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affi liates, in the United States and other
countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of
Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not
associated with any product or vendor mentioned in this book.
CONTENTS
INTRODUCTION
xxxvii
CHAPTER 1: SQL SERVER 2012 ARCHITECTURE
1
SQL Server 2012 Ecosystem
1
New Important Features in 2012
2
Production DBA
2
Development DBA
2
Business Intelligence DBA and Developer
3
SQL Server Architecture
4
Database Files and Transaction Log
4
SQL Native Client
5
Standard System Databases
6
Schemas
8
Synonyms
8
Dynamic Management Objects
9
SQL Server 2012 Data Types
10
Editions of SQL Server
17
Edition Overview
17
Licensing
18
Summary
20
CHAPTER 2: INSTALLING SQL SERVER 2012 BEST PRACTICES
21
Planning the System
22
Hardware Options
22
Software and Install Options
27
Installing SQL Server
29
New Installs
30
Side-by-Side Installs
30
Upgrades
30
Unattended Installs
30
Attended Installations
36
Installing Analysis Services
40
Multidimensional and Data Mining Mode (UDM Mode)
41
Tabular Mode
42
Installing PowerPivot for SharePoint
43
Burning in the System
45
937658692.005.png 937658692.006.png
CONTENTS
Post-Install Confi guration
45
Confi guring SQL Server Settings for Performance
46
tempdb
47
Confi guring SQL Server Setting for Security
49
Best Practices Analyzer (BPA)
50
SQL Server Confi guration Manager
50
Back It Up
51
Uninstalling SQL Server
51
Uninstalling Reporting Services
51
Uninstalling Analysis Services
51
Uninstalling the SQL Server Database Engine
52
Troubleshooting a Failed Install
52
Summary
52
CHAPTER 3: UPGRADING SQL SERVER 2012 BEST PRACTICES
53
Why Upgrade to SQL Server 2012?
53
Risk Mitigation — the Microsoft Contribution
54
Independent Software Vendors and SQL Community Contributions
54
Upgrading to SQL Server 2012
55
In-Place Upgrading
55
Side-by-Side Upgrade
57
In-Place Upgrade versus Side-By-Side Upgrade Considerations
58
Pre-Upgrade Steps and Tools
58
Pre-Upgrade Steps
58
Pre-Upgrade Tools
59
Backward Compatibility
67
Unsupported and Discontinued Features in SQL Server 2012
67
SQL Server 2012 Deprecated Database Features
67
Other SQL Server 2012 Changes A ecting Behavior
68
SQL Server Component Considerations
68
Upgrading Full-Text Catalog
68
Upgrading Reporting Services
68
Upgrading to 64-Bit
69
Post-Upgrade Checks
69
Poor Query Performance After Upgrade
69
Summary
70
CHAPTER 4: MANAGING AND TROUBLESHOOTING
THE DATABASE ENGINE
71
Confi guration and Administration Tools
71
SQL Server Confi guration Manager
72
Startup Parameters
73
xx
937658692.007.png 937658692.001.png
CONTENTS
Startup Stored Procedures
77
Partially Contained Databases
78
Troubleshooting Tools
79
Dedicated Administrator Connection
79
Rebuilding the System Databases
81
Management Studio
82
Reports
82
Confi guring SQL Server in Management Studio
85
Filtering Objects
90
Error Logs
90
Activity Monitor
91
Monitoring Processes in T-SQL
96
sp_who and sp_who2
96
sys.dm_exec_connections
97
sys.dm_exec_sql_text
97
Multiserver Management
98
Central Management Servers and Server Groups
98
SQL Server Utility
99
Trace Flags
99
Getting Help from Support
101
SQLDumper.exe
101
SQLDiag.exe
102
Summary
104
CHAPTER 5: AUTOMATING SQL SERVER
105
Maintenance Plans
106
Maintenance Plan Wizard
106
Maintenance Plan Designer
109
Automating SQL Server with SQL Server Agent
111
Jobs
112
Schedules
117
Operators
118
Alerts
121
SQL Server Agent Security
126
Service Account
126
Access to SQL Agent
126
SQL Server Agent Proxies
127
Confi guring SQL Server Agent
130
General Properties
131
Advanced Properties
132
Alert System Properties
133
Job System Properties
133
xxi
937658692.002.png 937658692.003.png
 

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin