Muutke küpsiste eelistusi

SQL Server 2017 Administration Inside Out [Pehme köide]

4.33/5 (12 hinnangut Goodreads-ist)
, , ,
  • Formaat: Paperback / softback, 704 pages, kõrgus x laius x paksus: 225x190x30 mm, kaal: 1148 g
  • Sari: Inside Out
  • Ilmumisaeg: 15-Jun-2018
  • Kirjastus: Microsoft Press
  • ISBN-10: 1509305211
  • ISBN-13: 9781509305216
Teised raamatud teemal:
SQL Server 2017 Administration Inside OutSuurem pilt
  • Formaat: Paperback / softback, 704 pages, kõrgus x laius x paksus: 225x190x30 mm, kaal: 1148 g
  • Sari: Inside Out
  • Ilmumisaeg: 15-Jun-2018
  • Kirjastus: Microsoft Press
  • ISBN-10: 1509305211
  • ISBN-13: 9781509305216
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781509305216.html
Märksõnad:
Conquer SQL Server 2017 administrationfrom the inside out





Dive into SQL Server 2017 administrationand really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workaroundsall you need to plan, implement, manage, and secure SQL Server 2017 in any production environment: on-premises, cloud, or hybrid. Four SQL Server experts offer a complete tour of DBA capabilities available in SQL Server 2017 Database Engine, SQL Server Data Tools, SQL Server Management Studio, and via PowerShell. Discover how experts tackle todays essential tasksand challenge yourself to new levels of mastery.

Install, customize, and use SQL Server 2017s key administration and development tools

Manage memory, storage, clustering, virtualization, and other components

Architect and implement database infrastructure, including IaaS, Azure SQL, and hybrid cloud configurations

Provision SQL Server and Azure SQL databases

Secure SQL Server via encryption, row-level security, and data masking

Safeguard Azure SQL databases using platform threat protection, firewalling, and auditing

Establish SQL Server IaaS network security groups and user-defined routes

Administer SQL Server user security and permissions

Efficiently design tables using keys, data types, columns, partitioning, and views

Utilize BLOBs and external, temporal, and memory-optimized tables

Master powerful optimization techniques involving concurrency, indexing, parallelism, and execution plans

Plan, deploy, and perform disaster recovery in traditional, cloud, and hybrid environments











For Experienced SQL Server Administrators and Other Database Professionals





Your role: Intermediate-to-advanced level SQL Server database administrator, architect, developer, or performance tuning expert





Prerequisites: Basic understanding of database administration procedures
Foreword xvii
Introduction xix
Who this book is for
xix
Assumptions about you
xix
How this book is organized
xx
About the companion content
xxii
Acknowledgments
xxii
Support and feedback
xxiv
Errata & support
xxiv
Stay in touch
xxiv
Chapter 1 Getting started with SQL Server tools 1(44)
SQL Server setup
1(6)
Installing SQL Server by using the Installation Center
2(1)
Planning before an upgrade or installation
3(3)
Installing or upgrading SQL Server
6(1)
Tools and services installed with the SQL Server Database Engine
7(5)
Machine Learning Services
7(1)
Data Quality Services
7(2)
Command-line interface
9(2)
SQL Server Configuration Manager
11(1)
Performance and reliability monitoring tools
12(6)
Database Engine Tuning Advisor
12(1)
Extended events
13(2)
Management data warehouse
15(3)
SQL Server Reporting Services
18(3)
Installation
19(1)
Report Services Configuration Manager
20(1)
SQL Server Management Studio
21(20)
Releases and versions
21(1)
Installing SQL Server Management Studio
22(1)
Upgrading SQL Server Management Studio
22(1)
Features of SQL Server Management Studio
23(6)
Additional tools in SQL Server Management Studio
29(3)
Error logs
32(1)
Activity Monitor
33(4)
SQL Server Agent
37(4)
SQL Server Data Tools
41(3)
SQL Server Integration Services
41(3)
A note on deprecation
44(1)
Chapter 2 Introducing database server components 45(34)
Memory
45(4)
Understanding the working set
46(1)
Caching data in the buffer pool
46(1)
Caching plans in the procedure cache
47(1)
Lock pages in memory
47(1)
Editions and memory limits
48(1)
Central Processing Unit
49(2)
Simultaneous multithreading
49(1)
Non-Uniform Memory Access
50(1)
Disable power saving everywhere
51(1)
Storing your data
51(6)
Types of storage
52(1)
Configuring the storage layer
53(4)
Connecting to SQL Server over the network
57(2)
Protocols and ports
58(1)
Added complexity with Virtual Local-Area Networks
58(1)
High availability concepts
59(9)
Why redundancy matters
60(1)
Disaster recovery
60(1)
Clustering
61(2)
The versatility of Log Shipping
63(1)
Always On availability groups
64(2)
Read-scale availability groups
66(1)
Distributed availability groups
67(1)
Basic availability groups
67(1)
Improve redundancy and performance with NIC teaming
67(1)
Securing SQL Server
68(5)
Integrated authentication and Active Directory
68(3)
Azure Active Directory
71(2)
Abstracting hardware with virtualization
73(4)
Resource provisioning for VMs
74(1)
When processors are no longer processors
75(2)
The network is virtual, too
77(1)
Summary
77(2)
Chapter 3 Designing and implementing a database infrastructure 79(48)
Physical database architecture
79(19)
Data files and filegroups
80(5)
Recording changes in the transaction log
85(7)
Table partitioning
92(1)
Data compression
93(3)
Managing the temporary database
96(2)
Configuration settings
98(12)
Managing system usage by using Resource Governor
98(1)
Configuring the page file (Windows)
99(1)
Taking advantage of logical processors by using parallelism
100(2)
SQL Server memory settings
102(3)
Carving up CPU cores using an affinity mask
105(2)
File system configuration,
107(3)
Azure and the Data Platform
110(17)
Infrastructure as a service
110(6)
Platform as a service
116(5)
Hybrid cloud with Azure
121(6)
Chapter 4 Provisioning databases 127(70)
What to do before installing SQL Server
127(7)
Deciding on volume usage
127(3)
Important SQL Server volume settings
130(1)
SQL Server editions
131(3)
Installing a new instance
134(17)
Planning for multiple SQL Server instances
134(1)
Installing a SQL Server instance
134(3)
Installing options and features
137(5)
Installing other core features
142(4)
"Smart Setup"
146(1)
Setting up logging
147(1)
Automating SQL Server Setup by using configuration files
147(4)
Post-installation server configuration
151(13)
Post-installation checklist
151(13)
Installing and configuring features
164(5)
SSISDB initial configuration and setup
164(1)
SQL Server Reporting Services initial configuration and setup
165(3)
SQL Server Analysis Services initial configuration and setup
168(1)
Adding databases to a SQL Server instance
169(6)
Considerations for migrating existing databases
169(6)
Moving existing databases
175(22)
Creating a database
177(4)
Database properties and options
181(8)
Moving and removing databases
189(1)
Moving user and system databases
189(2)
Database actions: offline versus detach versus drop
191(4)
Single-user mode
195(2)
Chapter 5 Provisioning Azure SQL Database 197(44)
Azure and database-as-a-service concepts
198(6)
Database-as-a-service
198(1)
Managing Azure: The Azure portal and PowerShell
199(1)
Azure governance
200(1)
Logical SQL Servers
201(1)
Cloud-first
202(1)
Database Transaction Unit
202(1)
Resource scalability
203(1)
Provisioning a logical SQL server
204(5)
Creating a server using the Azure portal
205(1)
Creating a server by using PowerShell
206(1)
Establishing a connection to your server
207(2)
Deleting a server
209(1)
Provisioning a database in Azure SQL Database
209(5)
Creating a database using the Azure portal
210(1)
Creating a database by using PowerShell
211(1)
Creating a database by using Azure CLI
212(1)
Creating a database by using T-SQL
213(1)
Selecting a pricing tier and service objective
213(1)
Scaling up or down
214(1)
Provisioning an elastic pool
214(1)
Limitations of Azure SQL Database
215(3)
Database limitations
215(1)
Other SQL Server services
216(2)
Overcoming limitations with managed instances
218(1)
Security in Azure SQL Database
218(11)
Security features shared with SQL Server 2017
219(1)
Server and database-level firewall
219(3)
Access control using Azure AD
222(1)
Role-Based Access Control
223(1)
Auditing and threat detection
224(5)
Preparing Azure SQL Database for disaster recovery
229(10)
Understanding default disaster recovery features
229(1)
Manually backing up a database
230(2)
Configuring geo-replication
232(3)
Setting up failover groups
235(2)
Using Azure Backup for long-term backup retention
237(2)
Moving to Azure SQL Database
239(2)
Chapter 6 Administering security and permissions 241(50)
Logins and users
241(16)
Different types of authentication
242(4)
Solving orphaned SIDs
246(3)
Preventing orphaned SIDs
249(1)
Factors in securing logins
249(5)
Login security
254(2)
Contained databases
256(1)
Permissions in SQL Server
257(28)
Understanding Permissions for Data Definition Language and
257(2)
Data Manipulation Language Modifying permissions
259(2)
Granting commonly needed permissions
261(4)
Ownership versus authorization
265(2)
Understanding views, stored procedures, and function permissions
267(6)
Understanding server roles
273(5)
Understanding database roles
278(5)
Using the Dedicated Administrator Connection
283(2)
Moving SQL Server logins and permissions
285(6)
Moving logins by using SQL Server Integration Services (SQL Server only)
286(1)
Moving Windows-authenticated logins by using T-SQL (SQL Server only)
287(1)
Moving SQL Server-authenticated logins by using T-SQL (SQL Server only)
287(1)
Moving server roles by using T-SQL (SQL Server only)
288(1)
Moving server permissions by using T-SQL (SQL Server only)
288(1)
Moving Azure SQL Database logins
289(1)
Other security objects to move
289(1)
Alternative migration approaches
290(1)
Chapter 7 Securing the server and its data 291(42)
Introducing security principles and protocols
292(10)
Securing your environment with defense in depth
292(2)
The difference between hashing and encryption
294(2)
A primer on protocols and transmitting data
296(4)
Symmetric and asymmetric encryption
300(1)
Digital certificates
301(1)
Encryption in SQL Server
302(12)
Data protection from the OS
303(1)
The encryption hierarchy in detail
303(1)
Using EKM modules with SQL Server
304(2)
Master keys in the encryption hierarchy
306(2)
Encrypting data by using TDE
308(2)
Protecting sensitive columns with Always Encrypted
310(4)
Securing data in motion
314(5)
Securing network traffic with TLS
314(1)
Row-level security
315(2)
Dynamic data masking
317(1)
Azure SQL Database
318(1)
Auditing with SQL Server and Azure SQL Database
319(7)
SQL Server Audit
319(7)
Auditing with Azure SQL Database
326(1)
Securing Azure infrastructure as a service
326(7)
Network Security Group
327(1)
User-defined routes and IP forwarding
328(2)
Additional security features in Azure networking
330(3)
Chapter 8 Understanding and designing tables 333(50)
Reviewing table design
333(21)
Generic data types
333(6)
Specialized data types
339(6)
Keys and relationships
345(1)
Constraints
346(1)
Sequences
347(3)
User-defined data types and user-defined types
350(2)
Sparse columns
352(1)
Computed columns
352(2)
Special table types
354(13)
System-versioned temporal tables
354(3)
Memory-optimized tables
357(4)
PolyBase external tables
361(1)
Graph tables
362(5)
Storing BLOBS
367(3)
Understanding FILESTREAM
368(1)
FileTable
369(1)
Table partitioning
370(7)
Horizontally partitioned tables and indexes
371(6)
Vertical partitioning
377(1)
Capturing modifications to data
377(6)
Using change tracking
378(2)
Using change data capture
380(1)
Comparing change tracking, change data capture, and temporal tables
381(2)
Chapter 9 Performance tuning SQL Server 383(46)
Understanding isolation levels and concurrency
383(17)
Understanding how concurrent sessions become blocked
386(4)
Stating the case against READ UNCOMMITTED (NOLOCK)
390(1)
Changing the isolation level within transactions
391(2)
Understanding the enterprise solution to concurrency: SNAPSHOT
393(5)
Understanding on-disk versus memory-optimized concurrency
398(2)
Understanding delayed durability
400(1)
Delayed durability database options
401(1)
Delayed durability transactions
401(1)
Understanding execution plans
401(12)
Understanding parameterization and "parameter sniffing"
402(2)
Understanding the Procedure Cache
404(1)
Analyzing cached execution plans in aggregate
405(3)
Retrieving execution plans in SQL Server Management Studio
408(5)
Using the Query Store feature
413(5)
Initially configuring the query store
415(1)
Using query store data in your troubleshooting
416(2)
Understanding automatic plan correction
418(1)
Understanding execution plan operators
419(6)
Interpreting graphical execution plans
419(6)
Forcing a parallel execution plan
425(1)
Understanding parallelism
425(4)
Chapter 10 Understanding and designing indexes 429(30)
Designing clustered indexes
429(5)
Choosing a proper clustered index key
429(4)
The case against intentionally designing heaps
433(1)
Designing nonclustered indexes
434(12)
Understanding nonclustered index design
435(6)
Creating "missing" nonclustered indexes
441(4)
Understanding and proving index usage statistics
445(1)
Designing Columnstore indexes
446(3)
Demonstrating the power of Columnstore indexes
448(1)
Using compression delay on Columnstore indexes
449(1)
Understanding indexing in memory-optimized tables
449(3)
Understanding hash indexes for memory-optimized tables
450(1)
Understanding nonclustered indexes for memory-optimized tables
451(1)
Moving to memory-optimized tables
451(1)
Understanding other types of indexes
452(1)
Understanding full-text indexes
452(1)
Understanding spatial Indexes
452(1)
Understanding XML indexes
453(1)
Understanding index statistics
453(6)
Manually creating and updating statistics
454(1)
Automatically creating and updating statistics
454(1)
Important performance options for statistics
455(1)
Understanding statistics on memory-optimized tables
456(1)
Understanding statistics on external tables
457(2)
Chapter 11 Developing, deploying, and managing data recovery 459(34)
The fundamentals of data recovery
460(10)
A typical disaster recovery scenario
460(2)
Losing data with the RPO
462(1)
Losing time with the RTO
463(1)
Establishing and using a run book
463(1)
An overview of recovery models
464(6)
Understanding backup devices
470(2)
Backup disk
470(1)
Backup sets and media
470(2)
Physical backup device
472(1)
Understanding different types of backups
472(6)
Full backups
473(1)
Transaction log backups
474(1)
Differential backups
475(2)
File and filegroup backups
477(1)
Additional backup options
477(1)
Creating and verifying backups
478(9)
Creating backups
479(1)
Verifying backups
480(2)
Restoring a database
482(4)
Restoring a piecemeal database
486(1)
Defining a recovery strategy
487(6)
A sample recovery strategy for a DR scenario
488(2)
Strategies for a cloud/hybrid environment
490(3)
Chapter 12 Implementing high availability and disaster recovery 493(64)
Overview of high availability and disaster recovery technologies in SQL Server
493(14)
Understanding log shipping
494(3)
Understanding types of replication
497(3)
Understanding the capabilities of failover clustering
500(3)
Understanding the capabilities of availability groups
503(3)
Comparing HA and DR technologies
506(1)
Configuring Failover Cluster Instances
507(6)
Configuring a SQL Server FCI
510(3)
Configuring availability groups
513(25)
Comparing different cluster types and failover
514(5)
Creating WSFC for use with availability groups
519(1)
Understanding the database mirroring endpoint
520(1)
Configuring the minimum synchronized required nodes
520(1)
Choosing the correct secondary replica availability mode
521(1)
Understanding the impact of secondary replicas on performance
522(2)
Understanding failovers in availability groups
524(1)
Seeding options when adding replicas
525(4)
Additional actions after creating an availability group
529(2)
Reading secondary database copies
531(6)
Implementing a hybrid availability group topology
537(1)
Configuring an availability group on Red Hat Linux
538(10)
Installation requirements
538(1)
Setting up an availability group
539(6)
Setting up the cluster
545(3)
Administering availability groups
548(9)
Analyzing DMVs for availability groups
548(6)
Analyzing wait types for availability groups
554(1)
Analyzing extended events for availability groups
555(1)
Alerting for availability groups
556(1)
Chapter 13 Managing and monitoring SQL Server 557(50)
Detecting database corruption
557(4)
Setting the database's page verify option
557(1)
Using DBCC CHECKDB
558(2)
Repairing database data file corruption
560(1)
Recovering the database transaction log file corruption
560(1)
Database corruption in databases in Azure SQL Database
561(1)
Maintaining indexes and statistics
561(10)
Changing the Fill Factor property when beneficial
561(2)
Monitoring index fragmentation
563(1)
Rebuilding indexes
564(4)
Reorganizing indexes
568(1)
Updating index statistics
569(2)
Reorganizing Columnstore indexes
571(1)
Maintaining database file sizes
571(4)
Understanding and finding autogrowth events
573(1)
Shrinking database files
574(1)
Monitoring databases by using DMVs
575(9)
Sessions and requests
576(1)
Understanding wait types and wait statistics
577(7)
Reintroducing extended events
584(8)
Viewing extended events data
586(3)
Using extended events to detect deadlocks
589(1)
Using extended events to detect autogrowth events
590(1)
Securing extended events
591(1)
Capturing Windows performance metrics with DMVs and data collectors
592(8)
Querying performance metrics by using DMVs
592(3)
Querying performance metrics by using Performance Monitor
595(1)
Monitoring key performance metrics
596(4)
Protecting important workloads using Resource Governor
600(4)
Configuring the Resource Governor classifier function
601(1)
Configuring Resource Governor pools and groups
602(1)
Monitoring pools and groups
603(1)
Understanding the new servicing model
604(3)
Chapter 14 Automating SQL Server administration 607(58)
Components of SQL Server automated administration
607(5)
Database Mail
608(4)
SQL Server Agent
612(11)
Configuring SQL Server Agent jobs
612(11)
Maintaining SQL Server
623(2)
Basic "care and feeding" of SQL Server
623(2)
Using SQL Server Maintenance Plans
625(13)
Maintenance Plan report options
632(1)
Covering databases with the Maintenance Plan
633(1)
Building Maintenance Plans by using the design surface in SQL Server
634(2)
Management Studio Backups on secondary replicas in availability groups
636(2)
Strategies for administering multiple SQL Servers
638(10)
Master and Target servers for SQL Agent jobs
638(4)
SQL Server Agent event forwarding
642(1)
Policy-Based Management
643(1)
Evaluating policies and gathering compliance data
643(5)
Using PowerShell to automate SQL Server administration
648(17)
PowerShell basics
649(2)
Installing the PowerShell SQLSERVER module
651(1)
Using PowerShell with SQL Server
652(4)
Using PowerShell with availability groups
656(4)
Using PowerShell with Azure
660(5)
Index 665(14)
About the authors 679(1)
About the Foreword author 680
William Assaf,MCSE, is a Microsoft SQL Server consultant and manager and blogs about SQL at sqltact.com. William has been a designer, database developer, and admin on application and data warehousing projects for private and public clients. He has helped write the last two generations of Microsoft SQL Server certification exams since 2012 and has been a Regional Mentor for PASS since 2015. William and fellow author Patrick Leblanc worked together on SQL Server 2012 Step by Step (Microsoft Press, 2015), having met at and together led the SQL Server User Group and SQLSaturday in Baton Rouge. William and his high school sweetheart enjoy travelling to speak at SQLSaturdays around the south, and hope to see to see you there, too.





Randolph West is a Data Platform MVP from Calgary, Alberta, Canada. He is coorganizer of the Calgary SQL Server User Group and Calgary SQLSaturday. He speaks at various conferences around the world, and acts on stage and screen. Randolph specializes in implementing best practices, performance tuning, disaster recovery, and cloud migrations, through his company Born SQL. You can read his blog at bornsql.ca.





Sven Aelterman started with SQL Server when he first deployed version 2000 in a failover cluster scenario. Since then, he has worked as IT manager, principal consultant, and IT director. He currently serves the Trojans (students) of Troy University as a lecturer in information systems in the Sorrell College of Business and as director of IT for the College. In addition, he is cloud software architect for Sorrell Solutions, a business services nonprofit through which Trojans can gain real-world business and IT experience. In a fledgling attempt to give back to the community, he has spoken at many SQLSaturdays and code camps in the southeastern United States since 2005. He spoke about SSIS 2012 at Microsoft TechEd 2011. In 2012, he coauthored a book dedicated to SQL Server FILESTREAM. His involvement with Microsoft Azure resulted in the organization of two Global Azure Bootcamp events at Troy University. Sven blogs about a variety of Microsoft technologies at svenaelterman.wordpress.com and tweets and retweets about technology @svenaelterman.





Mindy Curnutt, an independent consultant, is 4X Microsoft Data Platform MVP and Idera ACE. She has been actively involved in the SQL Server Community for more than a decade, presenting at various User Group Meetings, SQLPASS Summits, as well as SQLSaturdays across North America. For two years, she was a team lead for the SQLPASS Summit Abstract Review Process and since 2015 has served as one of the three SQLPASS Summit program managers. She was a SME for a couple of the SQL 2012 and 2014 Microsoft SQL Server Certification Exams and helped to author SQL Server 2014 Step by Step. Mindy currently serves on the board of directors for the North Texas SQL Server Users Group. She also serves as a mentor to others, helping to educate and promote scalable and sustainable SQL Server architecture and design. She is passionate about Data Security, Accessibility, Usability, Scalability and Performance. You can follow Mindy at her blog, mindycurnutt.com and on Twitter where shes known as @sqlgirl.