Muutke küpsiste eelistusi

Learn dbatools in a Month of Lunches [Pehme köide]

4.43/5 (14 hinnangut Goodreads-ist)
, , ,
  • Formaat: Paperback / softback, 400 pages, kõrgus x laius x paksus: 235x186x24 mm, kaal: 713 g
  • Ilmumisaeg: 07-Jul-2022
  • Kirjastus: Manning Publications
  • ISBN-10: 1617296708
  • ISBN-13: 9781617296703
Teised raamatud teemal:
Learn dbatools in a Month of LunchesSuurem pilt
  • Formaat: Paperback / softback, 400 pages, kõrgus x laius x paksus: 235x186x24 mm, kaal: 713 g
  • Ilmumisaeg: 07-Jul-2022
  • Kirjastus: Manning Publications
  • ISBN-10: 1617296708
  • ISBN-13: 9781617296703
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781617296703.html
Märksõnad:
An effective DBA is an efficient DBA. And if you work with SQL Server, dbatools is a lifesaver. With over 500 commands, this free and open source PowerShell module has the horsepower to automate just about every task you can imagineand then some! Learn dbatools in a Month of Lunches teaches you techniques that will make you more effectiveand efficientthan you ever thought possible.

Learn dbatools in a Month of Lunches is a practical hands-on guide to automating SQL Server with PowerShell and the awesome dbatools module. You'll master techniques you can immediately put into practice, from daily duties like backups and restores right through to performing security audits. Stabilize and standardize your SQL server environment, and simplify your tasks by building automation, alerting, and reporting with this powerful tool. Each lesson delivers another skill that you can use to speed through your core tasks as a SQL Server DBA!

About the Technology Want to automate tasks for thousands of SQL servers at once? Want to migrate an entire SQL server using just the command line? dbatools can do all thatand more. A free and open source PowerShell module, dbatools offers over 500 commands for automating SQL Server from the command line. Boasting advanced options unavailable in official tools, dbatools makes it easy to automate tasks including mass exports for simplified disaster recovery, tempdb configuration, and improving an instance's security posture.

Arvustused

"This is an excellent resource to use for advancing your skills with advanced administration for SQL servers, regardless of the number of servers you are administering." Joseph Houghes



"A great book that holds your hand on the journey from a beginner who has never used dbatools all the way to an expert!" Paul Broadwith



"If you have not heard of dbatools, prepare to make managing your SQL Server infrastructure easier with the combination of it and PowerShell!" Wayne Mather



"One of, if not THE best, technical books I have read, this book brings joy and life to automating DBA tasks through PowerShell, so much fun." Ben McNamara



"This book and dbatools are a very serious game changer for doing automation, get out of the dark ages and look at these tools!" Steve Atchue



"This book is a must for a busy SQL Server database professional." Arthur Zubarev

Foreword xv
Preface xvii
Acknowledgments xix
About this book xxiii
About the authors xxvi
1 Before you begin
1(7)
1.1 Why data professionals can't afford to ignore PowerShell
1(2)
A SQL Server DBA first win with PowerShell
2(1)
1.2 Automate it
3(1)
1.3 What is dbatools?
4(1)
1.4 Is this book for you?
5(1)
1.5 How to use this book
6(1)
The main chapters
6(1)
Hands-on labs
6(1)
Supplementary materials
6(1)
Further exploration
6(1)
1.6 Contacting us
6(1)
1.7 Being immediately effective with dbatools
7(1)
2 Installing dbatools
8(19)
2.1 Minimum requirements
8(5)
Server
9(1)
Workstation
9(1)
Ports
10(2)
Execution policy
12(1)
2.2 Signed software
13(2)
2.3 Understanding installation paths
15(1)
2.4 Installation methods
16(5)
The PowerShell Gallery
16(1)
Trusting the PowerShell Gallery
17(1)
Installing dbatools using the PowerShell Gallery, all users
18(1)
PowerShell Gallery, local user
19(1)
PowerShell Gallery, offline install
20(1)
2.5 PowerShell Gallery alternatives
21(2)
Downloading a zipped archive
21(1)
Additional methods
22(1)
2.6 How to find and use commands, the help system, and docs.dbatools.io
23(2)
Get-Command
23(1)
Find-DbaCommand
23(1)
Get-Help
23(2)
Docs.dbatools.io
25(1)
2.7 Updating
25(1)
PowerShell Gallery
25(1)
Alternative methods
26(1)
2.8 Hands-on lab
26(1)
3 The dbatools lab
27(13)
3.1 Why is a lab included in this book?
27(1)
3.2 Two options for building a dbatools lab environment
28(1)
3.3 Option 1: Windows lab
29(6)
Installation media for our lab
29(1)
Building the lab
30(3)
Configuration scripts
33(2)
Windows lab is ready for action
35(1)
3.4 Option 2: Quick demo environments using containers
35(5)
Running SQL Server in a container
36(4)
4 A gentle introduction to dbatools commands
40(20)
4.1 Getting started
40(1)
4.2 Checking the SQL connection
41(1)
4.3 First, getting help
41(2)
4.4 Running your first dbatools command
43(2)
4.5 The - Sqllnstance parameter
45(3)
Single instances
45(1)
Multiple instances
46(2)
4.6 The - SqlCredential parameter
48(6)
Connecting to instances with SQL Server Authentication
49(1)
Saving the credential to use SQL Server Authentication with multiple commands
50(1)
Other methods of using credentials for SQL Server Authentication
51(1)
Connecting to instances with a different Windows account
52(2)
4.7 The ComputerName parameter
54(2)
Methods of listing the SQL services on multiple servers
56(1)
4.8 The - Credential parameter
56(3)
Listing services on a server using a different account at the command line
56(1)
Listing services on a server using a different account with a credential variable
57(1)
Listing SQL services by type
58(1)
4.9 Bonus parameter: EnableException
59(1)
4.10 Hands-on lab
59(1)
5 Writing to SQL Server
60(19)
5.1 Piping commands
60(4)
5.2 Writing to a database
64(11)
Importing from a CSVfile to a database table
64(4)
Importing to a database table from a dbatools command
68(2)
Creating the database table first and then importing from a CSVfile
70(3)
Writing the results of other commands to a table
73(1)
Writing the results of other commands to an Azure SQL Database
74(1)
5.3 Copying tables, including their data
75(3)
PowerShell splatting
76(2)
5.4 Hands-on lab
78(1)
6 Finding SQL Server instances on your network
79(12)
6.1 Background
81(7)
Finding an instance
81(1)
Finding instances using a list of targets
82(3)
Finding SQL Servers in an Active Directory domain
85(1)
Finding SQL Servers in your surrounding network
86(2)
6.2 Working with detailed results
88(2)
6.3 OS support
90(1)
6.4 Hands-on lab
90(1)
7 Inventorying your SQL estate
91(11)
7.1 SQL features
92(2)
7.2 Build
94(1)
7.3 Host information
95(1)
7.4 Databases
96(4)
Filtering databases returned from Get-DbaDatabase
97(1)
Filtering databases returned from Get-DbaDatabase by last backup time
98(2)
7.5 Putting it all together into a database
100(1)
7.6 Hands-on lab
101(1)
8 Registered Servers
102(17)
8.1 Local Server Groups
104(1)
Version-specific RegSrvr.xmlfiles
105(1)
8.2 Azure Data Studio
105(2)
8.3 Central Management Server
107(2)
8.4 Inventory organization
109(3)
Importing advanced environment folder structures
110(2)
8.5 Further integration
112(5)
Adding new Registered Servers
112(3)
Copy, Export, Import
115(1)
Moving Registered Servers
116(1)
Removing Registered Servers
117(1)
8.6 Registered Server groups
117(1)
8.7 Hands-on lab
118(1)
9 Logins and users
119(14)
9.1 Failed logins
120(4)
9.2 Preventing login issues
124(2)
9.3 Logins, users, and permissions source control
126(2)
9.4 How was access gained?
128(4)
Finding nested Active Directory group access
131(1)
9.5 Hands-on lab
132(1)
10 Backups
133(12)
10.1 Creating backups
134(6)
Azure
137(2)
Docker
139(1)
10.2 Reading backup files
140(1)
10.3 Backup history
141(1)
10.4 Pruning old backup files
142(1)
10.5 Testing your backups
142(2)
10.6 Hands-on lab
144(1)
11 Restore
145(11)
11.1 Limitations and considerations
145(1)
11.2 Restore scenarios
146(3)
File
147(1)
Directory
147(2)
Output T-SQL restore scripts
149(1)
11.3 Restoring to custom data and log directories
149(5)
No recovery
150(1)
Renaming a database
151(1)
Point-in-time restores
152(1)
Restoring to a marked transaction
152(1)
Recovering a corrupt database
153(1)
11.4 Azure
154(1)
Shared access signatures
154(1)
Access keys
154(1)
11.5 Hands-on lab
155(1)
12 Snapshots
156(8)
12.1 Snapshots and SSMS
156(1)
12.2 Application upgrade
157(1)
12.3 When to use snapshots
158(1)
12.4 Creating a snapshot
158(1)
12.5 Upgrading
159(1)
12.6 Rolling back the entire database from a snapshot
160(1)
12.7 Restoring certain objects or data from a snapshot
161(1)
12.8 Cleaning up
162(1)
12.9 Reporting
162(1)
12.10 Hands-on lab
163(1)
13 Install and update SQL Server
164(15)
13.1 Installing
164(11)
Benefits of automated installs 165' Local installs
167(1)
Remote installs
168(3)
Customizing installation options
171(1)
ConfigurationFile and Configuration
171(2)
Built-in parameters
173(2)
13.2 Updating
175(1)
13.3 The importance of patching
176(1)
Fear of breaking everything
176(1)
Burdensome process leads to procrastination
177(1)
13.4 How we make it easier
177(1)
13.5 Hands-on lab
178(1)
14 Preparing for disaster
179(14)
14.1 Exporting an entire instance
180(7)
Scripting options
184(2)
Setting scripting options
186(1)
Excluding objects
187(1)
14.2 Granular exports
187(3)
Using Export-DbaScript
188(2)
14.3 Special commands
190(1)
14.4 Exporting server configurations (sp_configure)
191(1)
14.5 Hands-on lab
192(1)
15 Performing your first advanced SQL Server instance migration, part 1
193(2)
15.1 Databases
194(1)
16 Backup and restore
195(20)
Detach and attach
197(1)
Staging large databases for migration
198(5)
Other database migration options
203(1)
15.2 Hands-on lab
204(2)
Performing your first advanced SQL Server instance migration, part 2
205(1)
16.1 Logins and groups
206(3)
Which logins/groups are still needed"?
208(1)
16.2 SQL Agent objects: Jobs, operators, and more!
209(4)
16.3 Linked servers
213(1)
16.4 More migration fun
214(1)
16.5 Hands-on lab
214(1)
17 High availability and disaster recovery
215(14)
17.1 Log shipping
215(4)
Configuring log shipping with dbatools
216(1)
When log shipping goes bad: Gathering errors with dbatools
217(1)
Cutting over to a log shipped secondary database
218(1)
17.2 Windows Server Failover Cluster (WSFC)
219(3)
17.3 Availability groups
222(6)
Creating an availability group with dbatools
222(3)
Explore existing availability groups
225(2)
Managing existing AGs
227(1)
17.4 Hands-on lab
228(1)
18 PowerShell and SQL Server Agent
229(20)
18.1 Which to choose, CmdExec or PowerShell job steps?
231(1)
18.2 Creating Agent jobs to run PowerShell and dbatools
232(6)
Creating a SQL Server credential
234(1)
Creating a SQL Server Agent proxy
235(2)
The PowerShell file
237(1)
18.3 Creating the SQL Server Agent job with a CmdExec job step
238(2)
18.4 Tips
240(8)
Using default parameter values
240(1)
Ensuring that the Agent job fails when the PowerShell fails
241(3)
Logging
244(4)
Execution policies
248(1)
18.5 Hands-on lab
248(1)
19 SQL Server Agent administration
249(12)
19.1 Listing SQL Server Agent information
250(8)
SQL Server Agent jobs
250(3)
SQL Server Agent alerts
253(3)
Finding specific Agent jobs
256(2)
19.2 Agent job results and history
258(2)
Agent job results
258(1)
Time line
259(1)
19.3 Hands-on lab
260(1)
20 Creating and working with SQL Server Agent objects
261(13)
20.1 SQL Server Agent job creation
261(9)
Creating categories
262(1)
New schedule
263(1)
New proxy
264(2)
Create a new operator
266(1)
Create a new Agentjob
266(3)
The job step
269(1)
20.2 Bonus Agent job commands
270(3)
Start-DbaAgentJob
271(1)
Get-DbaRunningJob
272(1)
Get-DbaAgentJobHistory
273(1)
20.3 Hands-on lab
273(1)
21 Data masking
274(11)
21.1 Getting started
275(1)
21.2 A common approach
275(1)
21.3 The better approach
276(1)
Generating random data
276(1)
21.4 The process
277(7)
Finding potential PII data
277(2)
Generating a configuration file for masking
279(3)
Applying static data masking
282(1)
Validating a data masking configuration file
283(1)
21.5 Hands-on lab
284(1)
22 DevOps automation
285(13)
22.1 When should you use dbatools in DevOps?
286(1)
22.2 DACPAC
287(7)
Exporting a DACPAC from an existing database
287(2)
Publishing a DACPAC
289(1)
DACPAC options
290(4)
22.3 Running dbatools (and PowerShell) on a CI/CD system
294(3)
Creating a task
295(1)
Ensuring the dbatools module is available
295(1)
Understanding how to add parameters to the script
295(2)
22.4 Hands-on lab
297(1)
23 Tracing SQL Server activity
298(12)
23.1 SQL Server trace and SQL Profiler
299(2)
Converting traces to Extended Events
300(1)
23.2 Extended Events
301(8)
SSMS support
301(1)
Dbatools Support
302(1)
Finding Extended Events
302(1)
Using templates
303(2)
Starting and stopping Extended Event sessions
305(1)
Reading data
306(2)
Replicating Extended Event sessions to multiple instances
308(1)
Cleanup
308(1)
23.3 Hands-on lab
309(1)
24 Security and encryption
310(18)
24.1 Encrypting network connections
310(6)
Certificate
311(4)
Forcing encryption
315(1)
24.2 Extended protection for authentication
316(2)
24.3 Hide an instance
318(1)
24.4 Transparent data encryption (TDE)
319(4)
Encrypting databases
319(2)
Decrypting databases
321(2)
24.5 Database backup encryption
323(4)
Prerequisites
324(1)
Backing up the database with a certificate
325(1)
Checking encryption information from the backup
325(2)
24.6 Multilayered security
327(1)
24.7 Hands-on lab
327(1)
25 Data compression
328(12)
25.1 Types of compression
328(1)
25.2 How does rowstore data compression work?
329(1)
25.3 Why use data compression?
330(1)
25.4 It can't all be rainbows and unicorns: Compression drawbacks
330(1)
25.5 What's compressed?
331(1)
25.6 What should we compress?
332(1)
25.7 What makes a good candidate for compression?
333(1)
25.8 Dbatools, what should I compress?
333(2)
25.9 Compressing objects the old-fashioned way
335(1)
25.10 Dbatools to the rescue!
336(1)
25.11 Specifying the compression level
337(1)
25.12 Advanced settings
338(1)
25.13 Hands-on lab
339(1)
26 Validating your estate with dbachecks
340(10)
26.1 What dbachecks and dbatools have in common
340(1)
26.2 Our first check
341(2)
26.3 Viewing all available checks
343(1)
26.4 Configuring the check parameters
343(3)
26.5 Storing the output data in a database
346(3)
Storing data
347(1)
Power BI dashboard
348(1)
Configuring the connection
348(1)
26.6 Hands-on lab
349(1)
27 Working in the cloud
350(6)
27.1 Connecting to Azure
350(2)
27.2 Service principals and access tokens
352(1)
Using Az. Accounts
353(1)
27.3 Supported commands
353(2)
27.4 The future
355(1)
27.5 Hands-on lab
355(1)
28 Dbatools Configurations and logging
356(7)
28.1 Working with the configuration system
356(3)
Checking existing configurations
356(1)
Getting a specific configuration
357(1)
Getting just the value
358(1)
Changing a configuration value
359(1)
Resetting to default configuration values
359(1)
28.2 Taking the configs with you
359(1)
28.3 Using the logging system
360(1)
28.4 Exploring logged activity
360(2)
Ongoing logging
360(2)
28.5 Hands-on lab
362(1)
29 Never the end
363(2)
29.1 Use dbatools
363(1)
29.2 More PowerShell
364(1)
29.3 Contribute to dbatools
364(1)
29.4 Farewell
364(1)
Index 365
Chrissy LeMaire is a dual Microsoft MVP, awarded for her work with both SQL Server and PowerShell. She is an international speaker and the creator of the popular PowerShell module, dbatools. She has over 20 years of IT experience and currently works for General Dynamics Information Technology at NATO Special Ops HQ in Belgium.

Rob Sewell is a passionate automator who has been recognized as an MVP by Microsoft. He is a keen community contributor as an event organizer, speaker and open-source contributor. 

Cláudio Silva is a database professional, automator, and contributor of the open-source PowerShell module dbatools.

Jess Pomfret is a SQL Server Database Engineer and a Microsoft MVP. She started working with SQL Server in 2011, and enjoys the problemsolving aspects of performance tuning and automating processes with PowerShell.