44,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2013
Implement, maintain, and repair SQL Server 2012 databases As the most significant update since 2008, Microsoft SQL Server 2012 boasts updates and new features that are critical to understand. Whether you manage and administer SQL Server 2012 or are planning to get your MCSA: SQL Server 2012 certification, this book is the perfect supplement to your learning and preparation. From understanding SQL Server's roles to implementing business intelligence and reporting, this practical book explores tasks and scenarios that a working SQL Server DBA faces regularly and shows you step by step how to handle them. * Includes practice exams and coverage of exam objectives for those seeking MSCA: SQL Server 2012 certification * Explores the skills you'll need on the job as a SQL Server 2012 DBA * Discusses designing and implementing database solutions * Walks you through administrating, maintaining, and securing SQL Server 2012 * Addresses implementing high availability and data distribution * Includes bonus videos where the author walks you through some of the more difficult tasks expected of a DBA Featuring hands-on exercises and real-world scenarios, this resource guides you through the essentials of implementing, maintaining, and repairing SQL Server 2012 databases.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1302
Ähnliche
Contents
Cover
Contents
Title page
Copyright
Dedication
Acknowledgments
About the Author
Table of Exercises
Introduction
Who Should Read This Book
What Is Covered in This Book
How to Contact the Author
Part I: Introducing SQL Server 2012
Chapter 1: Understanding SQL Server’s Role
What Is Information Technology?
Introduction to Databases
Database Servers and Applications
SQL Server’s Role
Summary
Chapter Essentials
Chapter 2: Installing SQL Server 2012
Installation Planning
Installing a Default Instance
Installing Named Instances
Installing to a Cluster
Installing Extra Features
Upgrading from Previous Versions
Validating an Installation
Removing an Installation
Summary
Chapter Essentials
Chapter 3: Working with the Administration Tools
SQL Server Configuration Manager
SQL Server Management Studio
SQL Server Data Tools
SQL Server Profiler
Books Online
Windows Server Administration for the DBA
Summary
Chapter Essentials
Chapter 4: SQL Server Command-Line Administration
Introducing the Command Prompt
General Commands
Batch Files
Mastering SQLCMD
Introducing Windows PowerShell
Using SQL Server PowerShell Extensions
Summary
Chapter Essentials
Chapter 5: Querying SQL Server
Understanding the SQL Language
SQL Statement Types
SQL Syntactical Elements
Coding Recommendations
Using SELECT Statements
Advanced Query Techniques
Using DDL Statements
Using DCL Statements
Modifying Data
Tuning and Optimizing Queries
Summary
Chapter Essentials
Part II: Designing Database Solutions
Chapter 6: Database Concepts and Terminology
Relational Database Theory
Database Design Processes
Project Management for the DBA
Summary
Chapter Essentials
Chapter 7: ERD and Capacity Planning
Planning a Database
Understanding Entity Relationship Diagramming
Building an ERD
Capacity Planning
Summary
Chapter Essentials
Chapter 8: Normalization and Other Design Issues
Designing for Normalization
Designing for Performance
Designing for Availability
Designing for Security
Summary
Chapter Essentials
Part III: Implementing Database Solutions
Chapter 9: Creating SQL Server Databases
SQL Server Databases
Database Storage
Database Options and Properties
Creating Databases in the GUI
Creating Databases with T-SQL
Creating Databases with PowerShell
Attaching and Detaching Databases
Database Snapshots
Summary
Chapter Essentials
Chapter 10: Creating Tables
Data Types
Collations
Table Creation Process
Data Partitioning
Summary
Chapter Essentials
Chapter 11: Indexes and Views
Understanding Indexes
Creating Basic Indexes
Creating Advanced Indexes
Managing Indexes
Understanding Views
Creating Views
Summary
Chapter Essentials
Chapter 12: Triggers and Stored Procedures
Triggers Defined
Using Triggers
Creating Triggers
Understanding Stored Procedures
Creating Stored Procedures
Summary
Chapter Essentials
Chapter 13: Implementing Advanced Features
Understanding and Installing Analysis Services
Understanding Integration Services
Understanding and Installing Reporting Services
Implementing Database Mail
Configuring Full-Text Indexing
Implementing Transparent Data Encryption
Data Compression
Summary
Chapter Essentials
Part IV: Administering and Maintaining SQL Server 2012
Chapter 14: Creating Jobs, Operators, and Alerts
Standardize, Automate, and Update
Understanding SQL Server Jobs
Creating T-SQL Jobs
Creating SSIS Jobs
Creating Windows Command Jobs
Creating and Using Operators
Creating and Using Alerts
Using WSUS for SQL Server 2012
Summary
Chapter Essentials
Chapter 15: Performance Monitoring and Tuning
Performance Tuning Principles
Performance and Troubleshooting Tools
Blocks, Locks, and Deadlocks
SQL Server Profiler
Database Engine Tuning Advisor
Performance Monitoring with System Monitor
Using the Resource Governor
Performance Studio
Advanced Monitoring Tools
Summary
Chapter Essentials
Chapter 16: Policy-Based Management
Policy-Based Management
Centralized Server Management
Standardizing with PBM and CMS
Summary
Chapter Essentials
Chapter 17: Backup and Restoration
Backing Up a Database
Backing Up System Databases
Restoring a Database
Backing Up the Environment
Summary
Chapter Essentials
Part V: SQL Server Security
Chapter 18: Security Threats and Principles
Security Defined
Security Threats
Security Principles
Summary
Chapter Essentials
Chapter 19: Authentication and Encryption
Understanding Authentication
SQL Server Authentication Methods
Logins, Users, and Roles
Understanding Encryption
Summary
Chapter Essentials
Chapter 20: Security Best Practices
Establishing Baselines
Implementing Least Privilege
Auditing SQL Server Activity
Configuring the Surface Area
Understanding Common Criteria and C2
Summary
Chapter Essentials
Part VI: Implementing High Availability and Data Distribution
Chapter 21: AlwaysOn and High Availability
Introducing AlwaysOn Technology
Mirroring and AlwaysOn
Failover Solutions
Selecting Hardware for AlwaysOn
Summary
Chapter Essentials
Chapter 22: SQL Server Failover Clustering
Understanding Windows Failover Clustering Service
Implementing a Windows Cluster
Installing SQL Server 2012 to a Cluster
Monitoring and Managing a SQL Server Cluster
Summary
Chapter Essentials
Chapter 23: Database Mirroring and Snapshots
RAID-based Data Redundancy
Using Database Mirroring
Understanding Log Shipping
Implementing Database Snapshots
Summary
Chapter Essentials
Chapter 24: Implementing Replication
SQL Server Replication
Importing and Exporting Data
Summary
Chapter Essentials
Part VII: Implementing Business Intelligence and Reporting
Chapter 25: Data Warehousing
Understanding Data Warehouses
Implementing Fact Tables
Implementing Dimensions
Summary
Chapter Essentials
Chapter 26: SQL Server Integration Services
Integration Issues
Installing SSIS
Configuring SSIS Security Settings
Deploying Packages
SSIS Auditing and Event Handling
Extracting, Transforming, and Loading Data
Summary
Chapter Essentials
Chapter 27: Data Quality Solutions
Understanding Data Quality Concerns
Installing Data Quality Services
Using Master Data Services
Cleaning Data
Summary
Chapter Essentials
Appendices
Appendix A: Microsoft’s Certification Program
How Do You Become Certified on SQL Server 2012?
Tips for Taking a Microsoft Exam
Certification Objectives Map
Appendix B: About the Additional Study Tools
Additional Study Tools
System Requirements
Using the Study Tools
Troubleshooting
End User License Agreement
List of Illustrations
Chapter 1: Understanding SQL Server’s Role
FIGURE 1.1 The core components of IT
FIGURE 1.2 A table of flat-file databases
FIGURE 1.3 The Sales and Items tables interact in a relational structure.
FIGURE 1.4 Comparing Microsoft Access and SQL Server database security
FIGURE 1.5 A simple implementation of client-server technology with a client accessing a single server directly
FIGURE 1.6 An n-tier application using a SharePoint server to access a backend database server
FIGURE 1.7 An example of multiserver queries with the user querying one server that queries three other servers in turn
FIGURE 1.8 Errors listed in the Error List dialog
FIGURE 1.9 The SQL Server 2000 Enterprise Manager used in earlier versions of SQL Server
FIGURE 1.10 The Query Analyzer from SQL Server 2000
FIGURE 1.11 The SQL Server 2005 Database Mail Configuration Wizard
FIGURE 1.12 SQL Server Integration Services showing a sample project
FIGURE 1.13 The Surface Area Configuration for Features dialog box
Chapter 2: Installing SQL Server 2012
FIGURE 2.1 SQL Server logical architecture
FIGURE 2.2 The SQL Server services are displayed.
FIGURE 2.3 SQL Server Installation Center
FIGURE 2.4 The Setup Support Rules screen
FIGURE 2.5 The Install Setup Files screen
FIGURE 2.6 Potential installation issues
FIGURE 2.7 The Feature Selection screen
FIGURE 2.8 The Server Configuration screen
FIGURE 2.9 The Database Engine Configuration screen
FIGURE 2.10 Installation summary
FIGURE 2.11 The installation is complete.
FIGURE 2.12 SSMS connecting to the installed instance
Chapter 3: Working with the Administration Tools
FIGURE 3.1 The SQL Server Configuration Manager interface
FIGURE 3.2 Creating an alias
FIGURE 3.3 The SSMS interface
FIGURE 3.4 The SSMS Options dialog
FIGURE 3.5 SSMS start-up mode with a query window
FIGURE 3.6 The SSDT interface
FIGURE 3.7 Creating a new project and solution
FIGURE 3.8 Adding a project to an existing solution
FIGURE 3.9 Solution Explorer with multiple projects
FIGURE 3.10 Viewing the package created by the wizard
FIGURE 3.11 Debugging the package
FIGURE 3.12 Viewing the CSV data
FIGURE 3.13 The SQL Server Profiler default interface
FIGURE 3.14 Basic Profiler trace settings
FIGURE 3.15 Event selection in a Profiler trace
FIGURE 3.16 Event filtering in a Profiler trace
FIGURE 3.17 Capturing SSMS start-up events
FIGURE 3.18 Books Online content in the Microsoft Help viewer
FIGURE 3.19 Working with permissions
FIGURE 3.20 The TCP/IP configuration
FIGURE 3.21 Basic IPConfig output
FIGURE 3.22 Detailed IPConfig output
FIGURE 3.23 Verifying DNS with NSLOOKUP
FIGURE 3.24 PathPING in action
Chapter 4: SQL Server Command-Line Administration
FIGURE 4.1 The Windows command prompt
FIGURE 4.2 The command prompt’s general options
FIGURE 4.3 Command-prompt font settings
FIGURE 4.4 Command-prompt screen layout
FIGURE 4.5 The command prompt’s Colors tab
FIGURE 4.6 CD command help
FIGURE 4.7 Directory navigation commands
FIGURE 4.8 Using the TREE command
FIGURE 4.9 Listing files with the TREE command
FIGURE 4.10 DIR command options
FIGURE 4.11 ECHO commands
FIGURE 4.12 Piping output from one command into another
FIGURE 4.13 SQL Server service list at the command prompt
FIGURE 4.14 Automatically responding to a NET STOP prompt
FIGURE 4.15 Analyzing fragmentation with Contig
FIGURE 4.16 Finding PIDs for active connections
FIGURE 4.17 Matching PIDs with process names
FIGURE 4.18 ROBOCOPY output and log
FIGURE 4.19 SQLCMD switches
FIGURE 4.20 SQLCMD in interactive mode
FIGURE 4.21 Viewing the SQLCMD output log
FIGURE 4.22 The Windows PowerShell interface
FIGURE 4.23 Navigating SQL Server in PowerShell
FIGURE 4.24 Executing queries with Invoke-SQLCMD
FIGURE 4.25 Running DBCC CHECKTABLE with Invoke-SQLCMD
Chapter 5: Querying SQL Server
FIGURE 5.1 SSMS as a color-coded editor
FIGURE 5.2 Processing a BETWEEN clause
FIGURE 5.3 The processing of an IN clause
FIGURE 5.4 The sorted results with ORDER BY
FIGURE 5.5 NULLIF result set
FIGURE 5.6 Better NULLIF results using COALESCE
FIGURE 5.7 Using the CONVERT and CAST functions
FIGURE 5.8 Complex INNER JOIN results
FIGURE 5.9 OUTER JOIN result examples
FIGURE 5.10 Results before, during, and after transaction processing and rollback
FIGURE 5.11 Error handling with transaction processing
Chapter 6: Database Concepts and Terminology
FIGURE 6.1 Simple data entry form
FIGURE 6.2 Device Status sample table
FIGURE 6.3 SSMS table designer
FIGURE 6.4 One-to-one diagram
FIGURE 6.5 One-to-many diagram
FIGURE 6.6 Querying data in a one-to-many scenario
FIGURE 6.7 Many-to-many relationship
Chapter 7: ERD and Capacity Planning
FIGURE 7.1 ERD for the Book Collection Database
FIGURE 7.2 Many-to-many relationship in an ERD
FIGURE 7.3 All entities created without relationships
FIGURE 7.4 The Book Collection ERD in Visio
FIGURE 7.5 Open System Architect
FIGURE 7.6 The Books entity shown in OSA
FIGURE 7.7 All entities created in OSA
FIGURE 7.8 Using the DataSizer tool
Chapter 9: Creating SQL Server Databases
FIGURE 9.1 The default data store including the system databases
FIGURE 9.2 Using the tempdb database in code
FIGURE 9.3 Viewing the size of the tempdb database and log file
FIGURE 9.4 Using more than one file in a single filegroup
FIGURE 9.5 The New Database window used to create databases from the GUI
FIGURE 9.6 The Script button’s options used to generate T-SQL scripts
FIGURE 9.7 The automatic script generated from the Script button
FIGURE 9.8 Running the CreateDB.ps1 script
FIGURE 9.9 The AWSS snapshot displayed after creating it with the T-SQL code
Chapter 10: Creating Tables
FIGURE 10.1 Using the Table Designer to create tables
FIGURE 10.2 Displaying the Properties window in the Table Designer view
FIGURE 10.3 The partitioning object hierarchy
Chapter 11: Indexes and Views
FIGURE 11.1 The SQL Server B-tree index structure
FIGURE 11.2 The B-tree index structure for a clustered index on the Customers table
FIGURE 11.3 Creating the Customers table for the Books database
FIGURE 11.4 Verifying the primary key for the clustered index
FIGURE 11.5 Disabling an index within SSMS
FIGURE 11.6 Enabling an index with the Rebuild option
FIGURE 11.7 Viewing the IsDisabled property of the ix_City index in both the disabled and enabled states
FIGURE 11.8 Viewing the fragmentation level on an index
Chapter 12: Triggers and Stored Procedures
FIGURE 12.1 Disabling recursive and nested triggers on the Books database
Chapter 13: Implementing Advanced Features
FIGURE 13.1 OLAP cubes represented as views of data
FIGURE 13.2 SSDT used to create data cubes
FIGURE 13.3 Creating an SSIS package in SSDT
Chapter 14: Creating Jobs, Operators, and Alerts
FIGURE 14.1 Creating a new job in SSMS with the GUI interface
FIGURE 14.2 The job steps page in the New Job dialog
FIGURE 14.3 Creating a new step in a job
FIGURE 14.4 A job step of type SSIS changes the interface.
FIGURE 14.5 Configuring advanced settings for a job step
FIGURE 14.6 The Schedules page with a CPU idle schedule
FIGURE 14.7 Configuring notifications for a job
FIGURE 14.8 Manually running the Backup and Snapshot for the AdventureWorks job
FIGURE 14.9 Viewing job activity in the Job Activity Monitor
FIGURE 14.10 Viewing the history of the Generate System Information Log job
FIGURE 14.11 Configuring an operator for notification within a SQL Server Agent job
Chapter 15: Performance Monitoring and Tuning
FIGURE 15.1 Viewing the blocking processes in sys.dm_exec_requests
FIGURE 15.2 Viewing the code executed by a connection or process in the Activity Monitor
FIGURE 15.3 Viewing the default System Diagnostics data collector set
FIGURE 15.4 Viewing a report in the Reliability and Performance Monitor
FIGURE 15.5 Configuring the stop condition for a data collector set
FIGURE 15.6 Viewing the properties for the Query Statistics system data collector set
FIGURE 15.7 Querying the sys.dm_exec_connections DMV
FIGURE 15.8 Running DBCC SHOWCONTIG against the Production.Product table
FIGURE 15.9 Viewing the Resource Monitor
FIGURE 15.10 Viewing the Resource Monitor report
Chapter 16: Policy-Based Management
FIGURE 16.1 PBM components represented hierarchically
FIGURE 16.2 Viewing the list of available facets in Object Explorer
FIGURE 16.3 The General page of the Create New Condition dialog box
FIGURE 16.4 The Description page of the Create New Condition dialog box
FIGURE 16.5 The General page of the Create New Policy dialog
FIGURE 16.6 The Description page of the Create New Policy dialog
FIGURE 16.7 Viewing the applicable target types by facet
FIGURE 16.8 Viewing the history of an evaluation showing noncompliant targets
FIGURE 16.9 Viewing On Change: Log Only entries in the Event Viewer application log
FIGURE 16.10 Viewing the registered servers in the Registered Servers window of SSMS
FIGURE 16.11 Viewing the options available when right-clicking a server group
FIGURE 16.12 Viewing the polSurfaceAreaLog entry in the application log
Chapter 17: Backup and Restoration
FIGURE 17.1 Querying the database recovery models of all databases
FIGURE 17.2 Viewing the approximate consumed space in a database
FIGURE 17.3 Forcing a database overwrite during a restoration
Chapter 18: Security Threats and Principles
FIGURE 18.1 Viewing the Secunia.com SQL Server vulnerability report
FIGURE 18.2 Using Win Sniffer to capture passwords
FIGURE 18.3 Sniffing a user’s email password from a wireless network connection
FIGURE 18.4 Cracking passwords with L0phtCrack
Chapter 19: Authentication and Encryption
FIGURE 19.1 Viewing LoginMode in REGEDIT
FIGURE 19.2 Viewing the sys.syslogins table that contains the logins within the SQL Server
FIGURE 19.3 Viewing the local password policies
FIGURE 19.4 Managing server role membership within user properties
FIGURE 19.5 Viewing encrypted data without decrypting it
FIGURE 19.6 Viewing encrypted data with the DecryptByKey function
Chapter 20: Security Best Practices
FIGURE 20.1 The SCM Showing the Member Server Security Compliance baseline
FIGURE 20.2 The Security Templates snap-in showing expanded categories
FIGURE 20.3 Using the log file to view the audit logs within SSMS viewer
FIGURE 20.4 Evaluating the surface area policy on a SQL Server 2012 default installation
FIGURE 20.5 Viewing the details of the evaluation
Chapter 21: AlwaysOn and High Availability
FIGURE 21.1 The WEI on Windows 8
FIGURE 22.1 Viewing the warnings in a Validate A Configuration Wizard report
FIGURE 22.2 Viewing the warning details in the report
Chapter 23: Database Mirroring and Snapshots
FIGURE 23.1 RAID levels 0, 1, and 5
FIGURE 23.2 The Mirroring page in the Database Properties dialog
FIGURE 23.3 The Transaction Log Shipping page in the Database Properties dialog
Chapter 24: Implementing Replication
FIGURE 24.1 The Distributor Properties dialog box
FIGURE 24.2 The Publisher Properties dialog box used to select replication databases
Chapter 26: SQL Server Integration Services
FIGURE 26.1 Choosing the default environment for Visual Studio when used with SSDT
FIGURE 26.2 The SSIS 11.0 Properties dialog
FIGURE 26.3 Integration Services Dashboard report
FIGURE 26.4 The SQL Server OLEDB Connection Manager
FIGURE 26.5 The Excel Connection Manager
FIGURE 26.6 The Data Flow tab of an SSIS package
FIGURE 26.7 The error event handler arrow
FIGURE 26.8 The Event Handler tab
FIGURE 26.9 Supported common and other transforms in SSIS packages
FIGURE 26.10 The SQL Server Destination Editor dialog
FIGURE 26.11 The Excel Destination Editor dialog
FIGURE 26.12 The Select Script Component Type dialog shown when you drag a script component to the Data Flow designer
List of Tables
Chapter 2: Installing SQL Server 2012
TABLE 2.1 Hardware Requirements
Chapter 4: SQL Server Command-Line Administration
TABLE 4.1 Command Prompt Color Codes
TABLE 4.2 The Command-Line Switches
Chapter 5: Querying SQL Server
TABLE 5.1 Operator Precedence
TABLE 5.2 String Definition Examples
TABLE 5.3 Sample Table for the NULLIF Function
Chapter 6: Database Concepts and Terminology
TABLE 6.1 Data in context
Chapter 7: ERD and Capacity Planning
TABLE 7.1 Book collection database information
TABLE 7.2 Entity parameters
TABLE 7.3 Entities
Chapter 8: Normalization and Other Design Issues
TABLE 8.1 The Books table without normalization
TABLE 8.2 Duplicate records problem solved
TABLE 8.3 The Authors table
TABLE 8.4 AuthorsBooks linking table
TABLE 8.5 The AuthorTopics table
TABLE 8.6 Books table in 2NF, but not 3NF
TABLE 8.7 Recommended Books table structure
TABLE 8.8 3NF table without Boyce-Codd (BCNF)
TABLE 8.9 BCNF table without 4NF
TABLE 8.10 Columns assigned to entities
TABLE 8.11 Table descriptions in 1NF
TABLE 8.12 The final table descriptions in 3NF
Chapter 9: Creating SQL Server Databases
TABLE 9.1 Database properties defined
Chapter 10: Creating Tables
TABLE 10.1 Numeric data types
TABLE 10.2 Date and time data types
TABLE 10.3 Character data types
TABLE 10.4 Binary data types
TABLE 10.5 Special data types
TABLE 10.6 Column specifications
Chapter 11: Indexes and Views
TABLE 11.1 Sample customer data table
Chapter 15: Performance Monitoring and Tuning
TABLE 15.1 Common performance tuning myths
TABLE 15.2 DBCC commands with examples
Chapter 17: Backup and Restoration
TABLE 17.1 Backup device rotation structure
Chapter 19: Authentication and Encryption
TABLE 19.1 Password policy descriptions and recommendations
Chapter 20: Security Best Practices
TABLE 20.1 Securables and scope level
Chapter 21: AlwaysOn and High Availability
TABLE 21.1 Availability group failover modes and operational modes
Chapter 22: SQL Server Failover Clustering
TABLE 22.1 Failover Clustering quorum modes and functionality
TABLE 22.2 Validate A Configuration Wizard tests and actions
Chapter 24: Implementing Replication
TABLE 24.1 Replication types and their applications
Chapter 25: Data Warehousing
TABLE 25.1 SSAS 2012 aggregate functions
TABLE 25.2 A Type 2 SCD solution
Appendix A: Microsoft’s Certification Program
TABLE A.1 Exam 70-461, Querying Microsoft SQL Server 2012 objectives map
TABLE A.2 Exam 70-462, Administering Microsoft SQL Server 2012 database
TABLE A.3 Exam 70-463, Implementing a Data Warehouse with Microsoft SQL Server 2012
Guide
Cover
Table of Contents
Begin Reading
Pages
iii
iv
v
vi
vii
viii
xxv
xxvi
xxvii
xxviii
xxix
xxx
xxxi
xxxii
xxxiii
xxxiv
xxxv
xxxvi
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
223
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
447
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
801
802
803
804
805
806
807
808
809
810
811
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
851
852
853
854
855
856
857
858
859
860
861
862
Microsoft® SQLServer® 2012Administration
Real World Skills for MCSA Certification and Beyond
Tom Carpenter
Carpenter Tom
Senior Acquisitions Editor: Jeff Kellum
Development Editor: Jim Compton
Technical Editors: Mitchell Sellers and Denny Cherry
Production Editor: Liz Britten
Copy Editor: Kim Wimpsett
Editorial Manager: Pete Gaughan
Production Manager: Tim Tate
Vice President and Executive Group Publisher: Richard Swadley
Vice President and Publisher: Neil Edde
Media Project Manager 1: Laura Moss-Hollister
Media Associate Producer: Marilyn Hummel
Media Quality Assurance: Shawn Patrick
Book Designers: Judy Fung and Bill Gibson
Proofreader: Sarah Kaikini, WordOne New York
Indexer: Ted Laux
Project Coordinator, Cover: Katherine Crocker
Cover Designer: Ryan Sneed
Cover Image: iStockphoto.com / Sami Suni
Copyright © 2013 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-1-118-48716-7
ISBN: 978-1-118-65473-6 (ebk.)
ISBN: 978-1-118-65490-3 (ebk.)
ISBN: 978-1-118-65468-2 (ebk.)
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 specifically disclaim all warranties, including without limitation warranties of fitness 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 or to obtain technical support, please contact our Customer Care Department within the U.S. at (877) 762-2974, outside the U.S. 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: 2013933939
TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, 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.
10 9 8 7 6 5 4 3 2 1
Dear Reader,
Thank you for choosing Microsoft SQL Server 2012 Administration. This book is part of a family of premium-quality Sybex books, all of which are written by outstanding authors who combine practical experience with a gift for teaching.
Sybex was founded in 1976. More than 30 years later, we’re still committed to producing consistently exceptional books. With each of our titles, we’re working hard to set a new standard for the industry. From the paper we print on, to the authors we work with, our goal is to bring you the best books available.
I hope you see all that reflected in these pages. I’d be very interested to hear your comments and get your feedback on how we’re doing. Feel free to let me know what you think about this or any other Sybex book by sending me an email at [email protected]. If you think you’ve found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex.
Best regards,
Neil Edde
Vice President and Publisher
Sybex, an Imprint of Wiley
I dedicate this book to my family and God—the two most importantrelationships in my life. Thanks for all you do.
Acknowledgments
I would like to acknowledge the many people who have impacted my technology journey; Jeff Kellum, Jamie Franzman, Mark Minasi, Tim Green, Carl Behn, Rick LaFollette, Jan Richardson, Sharon Yoder, Kevin Sandlin, Devin Akin, and many more have impacted my life through the good, bad, and ugly. Thank you, all.
About the Author
Tom Carpenteris a consultant and trainer based in Marysville, Ohio. He is the founder and current senior consultant for the Systems Education and Consulting Company (SysEdCo, LLC). SysEdCo provides technical content development services and training for Microsoft technologies, wireless networking, security, and IT professional development. Tom is the author of several books on topics ranging from wireless network administration to SQL Server database administration and optimization. He spends every spare moment he can with his amazing wife and children and his church family, where he is honored to pastor a fine group of believers.
Table of Exercises
Exercise 2.1
Installing a Named Instance
Exercise 3.1
Starting and Stopping Services
Exercise 3.2
Configuring Service Properties
Exercise 3.3
Configuring Protocols
Exercise 3.4
Performing the Initial SSMS Configuration
Exercise 3.5
Opening a New Query Window and Viewing the Error List
Exercise 3.6
Viewing Predesigned Reports
Exercise 3.7
Generating Administrative Task Scripts
Exercise 3.8
Creating a New Project and a New Solution
Exercise 3.9
Creating a New Project in an Existing Solution
Exercise 3.10
Deleting a Project from a Solution
Exercise 3.11
Using the Import and Export Wizard
Exercise 3.12
Configuring IP Settings in the GUI
Exercise 3.13
Opening the Windows Command Prompt
Exercise 3.14
Configuring the Windows Firewall
Exercise 4.1
Preparing Your System for the Batch File
Exercise 4.2
Creating the Batch File
Exercise 4.3
Running the Batch File
Exercise 7.1
Preparing the Visio Environment for Entity Relationship Diagramming
Exercise 7.2
Creating the Visio Entity Relationship Diagram
Exercise 7.3
Creating Relationships Between Entities
Exercise 7.4
Creating an ERD File
Exercise 7.5
Creating the Entities
Exercise 7.6
Creating Relationships
Exercise 7.7
Estimating the Size of a Clustered Index
Exercise 7.8
Estimating the Size of a Clustered Index Table
Exercise 9.1
Creating Multiple Filegroups
Exercise 9.2
Creating the Books Database in the GUI
Exercise 9.3
Detaching the Books Database in the GUI
Exercise 9.4
Attaching the Books Database in the GUI
Exercise 10.1
Assigning Collations at the Column Level
Exercise 10.2
Launching the Table Designer
Exercise 10.3
Creating Columns
Exercise 10.4
Selecting a Primary Key
Exercise 10.5
Specifying Table Properties and Saving the Table
Exercise 10.6
Scripting the Table for Documentation
Exercise 11.1
Setting the Primary Key
Exercise 11.2
Creating the LastName Nonclustered Index
Exercise 11.3
Creating the City Nonclustered Index
Exercise 11.4
Creating the Covering Index
Exercise 11.5
Creating the Filtered Index
Exercise 11.6
Creating a View
Exercise 12.1
Creating a DML Trigger
Exercise 12.2
Creating a DDL Trigger
Exercise 13.1
Installing Analysis Services
Exercise 13.2
Installing Integration Services
Exercise 13.3
Creating a Basic Integration Services Package
Exercise 13.4
Troubleshooting an SSIS Package with Debug
Exercise 13.5
Saving and Scheduling Packages
Exercise 13.6
Installing and Configuring Reporting Services
Exercise 13.7
Configuring Database Mail
Exercise 13.8
Creating a Full-Text Index
Exercise 14.1
Creating a T-SQL Job
Exercise 14.2
Creating the SSIS Export Package
Exercise 14.3
Creating an SSIS Job
Exercise 14.4
Creating a Batch File for Information Gathering
Exercise 14.5
Creating a Windows Command Job
Exercise 14.6
Creating an Operator
Exercise 14.7
Creating a SQL Server Alert in SSMS
Exercise 14.8
Creating a Free Drive Space Alert
Exercise 15.1
Generating a Deadlock Scenario
Exercise 15.2
Creating a Trace with SQL Server Profiler
Exercise 15.3
Creating a DTA Workload File in SQL Server Profiler
Exercise 15.4
Analyzing the Workload File with the DTA Tool
Exercise 15.5
Applying the Saved DTA Recommendations
Exercise 15.6
Installing the System Monitor in a Custom MMC
Exercise 15.7
Viewing Live Performance Data on Windows Server 2003
Exercise 15.8
Viewing Live Performance Data on Windows Server 2008 R2
Exercise 15.9
Creating a Performance Counter Log in Windows Server 2003
Exercise 15.10
Creating a Data Collector Set in Windows Server 2008 R2
Exercise 15.11
Adding SQL Server Counters to a Data Collector Set
Exercise 15.12
Creating the MDW for Performance Studio
Exercise 15.13
Setting Up Data Collection Options
Exercise 15.14
Viewing Performance Studio Reports
Exercise 15.15
Launching the Resource Monitor Directly
Exercise 16.1
Determining Read-Only Properties of Facets
Exercise 16.2
Importing the Microsoft Sample Policies
Exercise 16.3
Creating Custom Categories for Policies
Exercise 16.4
Subscribing to a Category
Exercise 16.5
Creating the conDatabaseSize Condition
Exercise 16.6
Creating the conPasswordRules Condition
Exercise 16.7
Creating the conSurfaceArea Condition
Exercise 16.8
Creating the polDatabaseSizeScheduled Policy
Exercise 16.9
Creating the polPasswordRulesPrevent Policy
Exercise 16.10
Creating the polSurfaceAreaLog Policy
Exercise 16.11
Creating a CMS in SSMS
Exercise 16.12
Creating Server Groups in the CMS
Exercise 16.13
Registering a Server
Exercise 16.14
Creating an Automated Event Log Monitoring Solution
Exercise 17.1
Setting the Recovery Model in SSMS
Exercise 17.2
Creating a Full Backup of the Database
Exercise 17.3
Creating a Backup Device That Points to a File
Exercise 17.4
Restoring to a Point in Time
Exercise 17.5
Backing Up the Tail Log After a Database File Storage Failure
Exercise 17.6
Starting the SQL Server Database Engine in Single-User Mode
Exercise 17.7
Installing Windows Server Backup
Exercise 18.1
Using the MBSA Utility from Microsoft
Exercise 18.2
Creating Strong Password Policies in Windows Domains
Exercise 19.1
Configuring the Authentication Mode in SSMS
Exercise 19.2
Creating a SQL Login
Exercise 19.3
Viewing Local Password Policies
Exercise 19.4
Creating Windows Logins
Exercise 19.5
Creating a Database Role with SSMS
Exercise 19.6
Creating a Database User
Exercise 20.1
Installing Microsoft SCM
Exercise 20.2
Creating a Custom Security Template
Exercise 20.3
Analyzing Security with Templates
Exercise 20.4
Creating a Baseline from Current Settings with SCW
Exercise 20.5
Managing Permissions in SQL Server Management Studio
Exercise 20.6
Enabling a SQL Server Audit
Exercise 20.7
Using sp_configure to Configure the Surface Area
Exercise 20.8
Configuring the Surface Area with PBM
Exercise 20.9
Enabling the C2 Audit Trace
Exercise 22.1
Installing Windows Failover Clustering
Exercise 22.2
Running the Validate A Configuration Wizard
Exercise 22.3
Creating a Failover Cluster
Exercise 23.1
Creating the Mirroring Endpoints
Exercise 23.2
Implementing Log Shipping
Exercise 23.3
Generating a Log Shipping Report
Exercise 24.1
Configuring the Publisher and Distributor
Exercise 24.2
Creating a Publication with a Single Table to Replicate the Production.Product Table
Exercise 24.3
Creating a Subscription
Exercise 24.4
Exporting Data with the Import/Export Wizard
Exercise 26.1
Signing a Package in SSDT
Exercise 26.2
Creating a Deployment Utility
Exercise 26.3
Deploying with the Package Installation Wizard
Exercise 27.1
Creating a Data Quality Project
Introduction
Administering SQL Server 2012 is no simple task. As database management systems go, SQL Server 2012 is one of the most complex solutions available today. Offering more than just straightforward database management, SQL Server 2012 includes data management for data transfer and transformation, data distribution through replication, and high availability through database mirroring and server clustering. Modern database administrators (DBAs) must understand all of these components and more to successfully administer efficient and secure data facilities, and this book has been written for just such people.
This book was written from two perspectives. First and primarily, it covers the most important administrative tasks that the DBA in organizations of all sizes will need to perform. These are covered with a real-world focus on SQL Server 2012 administration. Second and in part, it covers the MCSA: SQL Server 2012 exam objectives (70-461, 70-462, and 70-463), through the written pages of the book and the additional resources available for download. Whether you’re preparing for these exams or preparing for life as a DBA, you’ll find this book a useful reference and starting point.
Who Should Read This Book
As you can probably tell by the title of this book, SQL Server 2012 Administration: Real World Skills for MCSA Certification and Beyond, this book is primarily aimed at two groups: those seeking real-world SQL Server database administration knowledge and those preparing for the MCSA: SQL Server 2012 exams. Yet a third group may benefit from reading this book as well. The following are descriptions of those who will find this book useful:
DBAs looking for a reference for common administrative tasks. Everything from backing up your databases to securing them is covered in this book. You’ll find coverage of the extra SQL Server 2012 components such as SQL Server Integration Services and SQL Server Reporting Services as well.
Exam candidates preparing to take the MCSA: SQL Server 2012 exams.
70-461 Querying Microsoft SQL Server 2012
70-462 Administering Microsoft SQL Server 2012 Databases
70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012
You’ll find that all of the objectives are addressed at some level when you use the complete training kit this book provides, which includes the book and the additional online resources. It’s important to note that what you hold in your hands is more than just a book. The online resources include video training and memory-jogging flashcards, as well as practice exams and more, to help you on your journey to master the objectives of the MCSA exams.
Programmers will also find value in this book. This book does not contain programming guidance or detailed explanations of the T-SQL language or CLR code; however, it does provide programmers with a reference to the SQL Server 2012 functionality and how to install and manage the SQL Server that may be used as the backend for their data-driven applications.
As you can see, this book is useful to several groups. I have worked as a systems engineer creating applications that access SQL Servers, so I know the value of a good administration book sitting on my shelf, and I’ve striven to write this book with that in mind.
What You Will Learn
As you progress through this book, you will go from understanding what SQL Server 2012 has to offer to your organization to implementing it with all the bells and whistles it provides. You’ll learn to select the appropriate hardware for your servers and then install SQL Server 2012 right the first time. Then you’ll move on to learn how to use the administration tools from both the graphical user interface (GUI) of Windows and the command-line interface (my favorite place to be).
Next, you’ll learn how to design and implement database design solutions for SQL Server 2012. During this process, you’ll learn all about normal forms and database optimization and the many terms you’ll need to understand to master database administration and design. You’ll learn to create databases with the SQL Server Management Studio and with T-SQL code. Of course, part of the optimization process will be to implement indexes, so I’ll make sure you really understand what they are and how they work to improve the performance of your database queries. You’ll also learn to enforce rules and data standards by using triggers, stored procedures, and other advanced administration solutions.
Once your database is in place, you’ll need to provide ongoing support for that database. One of the first things you must implement is a working backup and recovery plan. You’ll learn how to do this by first learning to create jobs, operators, and alerts. Then you’ll learn to perform performance analysis and optimization and take advantage of the Declarative Management Framework (DMF), also known as Policy-Based Management (PBM). And, of course, you’ll learn how to back up and restore your databases. The primary focus of this ongoing administration process will be to standardize, automate, and update so that your workload is reduced over the long haul.
Once you have the maintenance plans in place, it’s time to think seriously about security, which is a very important issue for databases, networks, and anything else of value. There are three chapters in this section. First, you’ll learn about security threats and vulnerabilities. You’ll then move on to learn about authentication and encryption in detail. Finally, I’ll provide you with several best practices for securing a SQL Server 2012 environment.
The final sections of the book address SQL Server 2008 high availability and data warehousing. You’ll learn about failover clustering, database mirroring, database snapshots, data replication, and data quality in these chapters.
Throughout the book, you’ll find real-world exercises that walk you through the processes required to implement and support commonly used features of SQL Server 2012. You’ll also find notes and warnings scattered throughout the book to help you understand more detailed concepts. Additionally, real-world scenarios provide you with insights into the daily life of a DBA or database consultant.
This book was written to address the complete collection of tasks the DBA will be required to perform in the real world, while also covering all exam topics at some level so readers can prepare to pass their MCSA exams. Each section offers real-world exercises so you can learn with hands-on tasks. I have also provided videos of some of these exercises as well in the online resources.
Yet it’s also important that you remember what this book is not; this book is not a programming reference. My goal here is not to teach you everything about the T-SQL language. That would require a 700+ page volume itself and is well beyond the scope of this book. However, I have good news for you: if you are new to T-SQL, you will find an introduction to the T-SQL language and some code examples in the demonstration and training videos in the online resources that are included with this book. Additionally, you can visit www.TomCarpenter.net to find blog posts related to SQL Server and other technologies. In these posts, I often cover T-SQL best practices and optimization techniques as well as many other technology topics.
What You Need
The exercises in this book assume you are running SQL Server 2012 on Windows Server 2008 or newer. If you are using Windows Server 2003 R2 or previous versions, the exercises should work in most cases; however, they were tested only on Windows Server 2008 and newer.
If you do not have a Windows Server 2008 or 2008 R2 machine, you might want to create a virtual machine so that you can go through every exercise in the book. Here are your options:
You can also download a trial version of Windows Server 2008 from,
http://www.microsoft.com/windowsserver2008/en/us/trial-software.aspx
and install it as the host operating system. I recommend a machine with 4GB of RAM to perform this operation at a minimum.
You may be able to use the VMware Player found at
www.vmware.com
.
You will also need the SQL Server 2012 media for installation. If you do not have a licensed copy of SQL Server 2012, you have two choices:
First, you can download a trial version from Microsoft’s website at:
http://www.microsoft.com/en-us/sqlserver/get-sql-server/try-it.aspx
Second, you can purchase the Developer edition of SQL Server 2012. It usually costs between $50 and $70 and is the same as the Enterprise edition except for the licensing. The Developer edition license allows you to develop solutions but not deploy them. For example, you cannot implement a production database server for your users with the Developer edition; however, you can work through every exercise in this book using it.
Suggested Home Lab Setup
The following list is a recommended home lab setup that should work well for all exercises in this book:
A desktop or laptop computer with 16GB of RAM:
Must support virtualization extensions (Intel VT or AMD-V).
Should have 1TB or more of storage for virtual machine storage.
Windows Server 2008 R2 installed natively on the system:
Optionally, install Windows Server 2012.
Drivers for Windows 7 will work on Server 2008 R2.
Drivers for Windows 8 will work on Server 2012.
Installation instructions for Server 2008 R2 can be found at
http://bit.ly/HPXSAb
.
Installation instructions for installing Server 2012 can be found at
http://bit.ly/ShDkKq
.
Hyper-V installed on the system:
Installation instructions for Hyper-V on Server 2008 R2 can be found at
http://bit.ly/13n0C1B
.
Installation instructions for Hyper-V on Server 2012 can be found at
http://bit.ly/Tsvui8
.
Hyper-V to create virtual machines (VMs) for SQL Server and a domain controller:
The domain controller VM will run Windows Server 2008 R2 and can run on only 1GB of RAM.
The SQL Server VMs should have 4GB to 6GB of RAM each (this is the reason for using a 16GB machine).
As an alternative to this configuration, you can use only one SQL Server VM, and this would allow for the use of a machine with 8GB to 12GB of RAM. However, you would not be able to test things such as replication and mirroring with this configuration. Of course, instead of VMs, if you have the computers, you can certainly implement multiple physical machines with 2GB to 6GB of RAM each and native installations of Windows Server. The recommendations in this section are simply that—recommendations. You can implement any lab you desire, but the lab suggested here will provide a good learning and testing experience.
What Is Covered in This Book
SQL Server 2012 Administration: Real World Skills for MCSA Certification and Beyond is organized to provide you with the information you need to effectively administer your SQL Server 2012 instances. The following list provides an overview of the topics covered in each chapter:
Part I
—Introducing SQL Server 2012
Chapter 1
—Understanding SQL Server’s Role
: In this chapter, you will learn about the role of a database server and the various roles SQL Server 2012 can play in your organization.
Chapter 2
—Installing SQL Server 2012
: Master the SQL Server installation process by actually doing it in this chapter. You will install a named instance and also learn how to install a default instance. Each step of the installation process is covered in detail.
Chapter 3
—Working with the Administration Tools:
Take a tour of the administration tools provided with SQL Server and Windows Server and learn to use them to keep your SQL Servers running smoothly.
Chapter 4
—SQL Server Command-Line Administration:
This chapter teaches you how to use the command line and Windows PowerShell for SQL Server 2012 administration. You’ll learn how to use the
SQLCMD
command and also how to use the general command-line commands that ship with Windows itself.
Chapter 5
—Querying SQL Server:
This chapter introduces you to the basics of the SQL language. You learn the core language statements and build a foundational knowledge base that allows you to learn how to create any needed queries in the future.
Part II
—Designing Database Solutions
Chapter 6
—Database Concepts and Terminology:
It’s time to begin learning the theory behind database systems. You’ll learn all the important terms and what they mean. This chapter lays a foundation for the following chapters of the book.
Chapter 7
—ERD and Capacity Planning:
Have you heard of entity relationship diagramming? In this chapter, you will learn what it is and how to use it by using common tools and free tools available on the Internet. You’ll also learn to estimate the capacity needs for a given database specification.
Chapter 8
—Normalization and Other Design Issues:
Normalization is an important process, and this chapter teaches you how to use it to optimize your database designs. You’ll also look briefly at performance, availability, and security—topics of their own chapters—as design considerations.
Part III
—Implementing Database Solutions
Chapter 9
—Creating SQL Server Databases:
You will learn to create databases using the SQL Server Management Studio as well as T-SQL code in this chapter. This is where the theory meets reality in the SQL Server database system.
Chapter 10
—Creating Tables
: To create well-performing tables, you must understand data types. This chapter provides a reference of data types in SQL Server 2012 and how to choose the best data type for any situation. You’ll also learn the difference between a heap and a clustered index.
Chapter 11
—Indexes and Views:
Trainers like to talk about them. DBAs like to implement them. Now, you will learn what they really are and how they improve the performance of your databases. What are they? Indexes, of course. You’ll also learn about views and the benefits they provide.
Chapter 12
—Triggers and Stored Procedures:
Triggers and stored procedures are often used to centralize business rules or business logic. This chapter introduces the concepts and provides examples of both.
Chapter 13
—Implementing Advanced Features:
SQL Server 2012 provides some advanced functionality right out of the box, and this chapter introduces these capabilities with coverage of SQL Server Analysis Services, SQL Server Reporting Services, and SQL Server Integration Services.
Part IV
—Administering and Maintaining SQL Server 2012
Chapter 14
—Creating Jobs, Operators, and Alerts:
Now that your databases are in place, it’s time to maintain them. In this chapter, I introduce the Standardize, Automate, and Update (SAU) model of administration and provide steps for creating jobs, operators, and alerts.
Chapter 15
—Performance Monitoring and Tuning:
This chapter introduces you to the performance maintenance tools available in Windows Server and SQL Server 2012. You’ll learn how to track down performance problems and improve the responsiveness of your servers.
Chapter 16
—Policy-Based Management:
Policy-Based Management is also known as the Declarative Management Framework. This chapter teaches you how to implement Policy-Based Management from the ground up.
Chapter 17
—Backup and Restoration:
This final general administration chapter will focus on the very important task of backing up and restoring your databases. You’ll learn about the different backup types and how to implement them. You’ll also learn about the importance of recovery testing and recovery procedures.
Part V
—SQL Server Security
Chapter 18
—Security Threats and Principles:
When administering databases, you are often managing the most valuable asset in modern organizations. For this reason, I take a very serious approach to security when it comes to SQL Server 2012. This chapter begins the security journey by evaluating threats and vulnerabilities in a SQL Server 2012 networked environment.
Chapter 19
—Authentication and Encryption:
Continuing from Chapter 18, this chapter moves on to the topics of authentication and encryption. You’ll learn how authentication helps protect your environment and about the authentication options SQL Server provides. You’ll also learn to implement and manage encryption in SQL Server databases.
Chapter 20
—Security Best Practices:
In this third and final chapter on security, you’ll learn several best practices to help you maintain the security of your environment. You’ll learn to perform surface area reduction and auditing in this chapter.
Part VI
—Implementing High Availability and Data Distribution
Chapter 21
—AlwaysOn and High Availability:
SQL Server 2012 introduces a new technology called AlwaysOn. This chapter provides an understanding of this new technology and explains how to select the appropriate hardware for use with it.
Chapter 22
—SQL Server Failover Clustering:
SQL Server 2008 supports the failover clustering feature of Windows Server, and this chapter introduces you to the topic of clustering and how it is implemented in a SQL Server 2008 environment.
Chapter 23
—Database Mirroring and Snapshots:
Database mirroring was new in SQL Server 2005, and it has continued to be supported in SQL Server 2012. This chapter provides instruction for implementing database mirroring and database snapshots for point-in-time data recovery and analysis.
Chapter 24
—Implementing Replication:
