Learn SQL Server Administration in a Month of Lunches
Don Jones
  • April 2014
  • ISBN 9781617292132
  • 256 pages
  • printed in black & white

Concise and easy to understand, even on the most challenging topics.

Spike Xavier, Transmission IT, LLC

Learn SQL Server Administration in a Month of Lunches is the perfect way to get started with SQL Server operations, including maintenance, backup and recovery, high availability, and performance monitoring. In about an hour a day over a month, you'll learn exactly what you can do, and what you shouldn't touch. Most importantly, you'll learn the day-to-day tasks and techniques you need to keep SQL Server humming along smoothly.

Table of Contents detailed table of contents

preface

acknowledgments

about this book

1. Before you begin

1.1. Is this book for you?

1.2. How to use this book

1.3. Setting up your lab environment

1.4. SQL Server documentation

1.5. Online resources

1.6. A word on my recommendations

1.7. Being immediately effective with SQL Server

2. Server assessment and configuration

2.1. Identifying instances and versions

2.2. Identifying databases

2.3. Identifying other server objects

2.4. Inventorying the server configuration

2.5. Hands-on lab

3. T-SQL crash course

3.1. SELECT queries

3.2. DELETE, UPDATE, and INSERT queries

3.3. Assessing a database’s structure

3.4. Multitable SELECT queries

3.5. Hands-on lab

4. Managing databases

4.1. Configuring database options

4.2. Detaching and attaching databases

4.3. Assessing database storage

4.4. Identifying potential performance concerns in storage

4.5. System databases

4.6. An overview of storage hardware

4.7. Hands-on lab

5. Backup and recovery

5.1. The transaction log

5.2. How backups work

5.3. How restoration works

5.4. Model backup and recovery scenarios

5.5. Special procedures for system databases

5.6. Backup and recovery using T-SQL or PowerShell

5.7. Hands-on lab

6. Authentication: who are you?

6.1. Understanding logins

6.2. Understanding database users

6.3. How the real world often does authentication

6.4. Understanding application roles

6.5. Hands-on lab

7. Authorization: what are you allowed to do?

7.1. Understanding SQL Server permissions

7.2. Using server roles

7.3. Using database roles

7.4. Schemas and owners

7.5. Permission chains

7.6. How the real world often does authorization

7.7. Hands-on lab

8. Accounting: what did you do?

8.1. Triggers

8.2. SQL Server Profiler

8.3. Event logs and C2 auditing

8.4. SQL Audit

8.5. Hands-on lab

9. Analyzing indexes

9.1. Index basics

9.2. Heaps

9.3. Clustered indexes

9.4. Nonclustered indexes

9.5. Index inventory

9.6. Constraints and indexes

9.7. More about indexes

9.8. Hands-on lab

10. Maintaining indexes

10.1. Understanding index fragmentation

10.2. Analyzing index fragmentation

10.3. Indexes maintenance strategies

10.4. Performing the maintenance

10.5. Suggestions for index maintenance

10.6. Hands-on lab

11. Tuning index designs

11.1. How indexes can become less useful

11.2. Using Profiler to capture usage patterns

11.3. Using the Database Engine Tuning Advisor

11.4. What you can and can’t do with indexes on someone else’s database

11.5. Hands-on lab

11.6. Way above and way beyond

12. Reading query execution plans

12.1. What’s an execution plan?

12.2. Common plan symbols and what they mean

12.3. How to read an execution plan

12.4. What to look for in an execution plan

12.5. Hands-on lab

13. Block and deadlock analysis

13.1. What’s locking and blocking?

13.2. Understanding transactions

13.3. Types of locks

13.4. Lock and block analysis

13.5. Deadlocks

13.6. Mitigating blocks and deadlocks

13.7. Hands-on lab

14. Automating management with SQL Server Agent

14.1. What is SQL Server Agent?

14.2. Jobs

14.3. Operators

14.4. Alerts

14.5. Database Mail

14.6. SQL Server Agent security

14.7. A quick cheat: Database Maintenance Plans

14.8. Ideas for using SQL Server Agent

14.9. Hands-on lab

15. Multiserver management

15.1. What is multiserver management?

15.2. Creating a multiserver management hierarchy

15.3. Creating and using multiserver management jobs

15.4. Ideas for multiserver jobs

15.5. Hands-on lab

16. Windows PowerShell and SQL Server

16.1. Windows PowerShell overview

16.2. SQL Server and PowerShell

16.3. SQL Server Agent and PowerShell

16.4. Hands-on lab

17. Using Extended Events

17.1. What are Extended Events?

17.2. Creating and using a session

17.3. Creating a custom session

17.4. Tips for Extended Events

17.5. Hands-on lab

18. Monitoring and analyzing performance

18.1. Key Performance Monitor counters

18.2. Using Data Collectors and reports

18.4. The big performance killers

18.5. Hands-on lab

19. Options for high availability

19.1. What is high availability?

19.2. Log shipping

19.3. Database mirroring

19.4. AlwaysOn Clustering

19.5. AlwaysOn Availability Groups

19.6. HA beyond SQL

19.7. HA comparison

20. Virtualizing SQL Server

20.1. Why virtualize?

20.2. Understanding virtual and physical hardware

20.3. CPU and memory in a virtual environment

20.4. Disk I/O in a virtual environment

20.5. Tricks with performance monitoring in a virtual environment

20.6. Hands-on lab

21. Moving, migrating, and upgrading databases

21.1. Refresh: detaching and attaching databases

21.2. The Copy Database Wizard

21.3. Overview: SQL Server Integration Services

21.4. Database upgrades

21.5. Compatibility levels

21.6. Hands-on lab

22. SQL Server performance checklist

23. Never the end

Appendix A: Installing SQL Server

A.1. Setting up your computer

A.2. Installing SQL Server

A.3. Installing the AdventureWorks 2012 database

index

About the book

Microsoft SQL Server is used by millions of businesses, ranging in size from Fortune 500s to small shops worldwide. Whether you're just getting started as a DBA, supporting a SQL Server-driven application, or you've been drafted by your office as the SQL Server admin, you do not need a thousand-page book to get up and running.

Learn SQL Server Administration in a Month of Lunches is the perfect way to get started with SQL Server. This concise, easy-to-read book skips academic introductions and teaches you day-to-day techniques for maintenance, backup and recovery, performance monitoring, and more. Each of the 21 short lessons gives you practical takeaways you'll use over and over.

What's inside

  • Master the basics—ndexes, logins, backup, recovery... and more
  • Learn what you can and cannot do when supporting a third-party application
  • Monitor and improve performance
  • Written by expert trainer and bestselling author Don Jones

About the author

Don Jones is a Microsoft MVP, speaker, and trainer. He is the creator of the Month of Lunches series and author of over 50 books on PowerShell, IIS, Active Directory, SCCM, SQL Server, and more.


Buy
combo $34.99 pBook + eBook + liveBook
eBook $27.99 pdf + ePub + kindle + liveBook

FREE domestic shipping on three or more pBooks

Don has written another gem equal to his PowerShell titles.

Carm Vecchio, Computer Science Corporation (CSC)

The essentials of SQL Server Administration, distilled into a friendly format.

Adam M Dutko, RunByMany, LLC

Extremely useful for anyone managing or trying to manage SQL Server.

Maqbool Patel, PhD, MEDHOST

Contains all the steps needed to become a professional DBA.

Ian Stirk, Stirk Consultancy, Ltd