Database Management Myths for Developers

Myths, Mistakes, and Lessons learned about Managing SQL Server databases. We also focus on automating and validating your critical database management tasks.

1. Database Management Myths
for Developers
Kon Melamud& John Sterrett
CTO & CEO

2. About Kon and John @ ProcureSQL
2
[email protected]
procuresql.com
linkedin.com/in/konmelamud
[email protected]
@johnsterrett
johnsterrett.com
procuresql.com/blog
linkedin.com/in/johnsterrett

3. Speaker
Dinner
Gold
Sponsors
Titanium Sponsors
Friends of KCDC
Platinum Sponsors

4. Speaker Evaluations

5. Direct Access to
Data/Application
Architects and DBAs
•Senior Data Architects are demanding
salaries over 200k per year.
•We provide customized plans to fit your
needs
•Plans start at 4k/month
•Includes
•Architecture
•Training and Mentoring
•Projects
•Reporting
•Implementation
•Support

6. Today’s Agenda
Myths of Database Management
Common Mistakes and Lessons Learned
Automate and Validate Important Stuff…
** NOTE: We will focus on SQL Server Database Engine, but everything today
applies to relational database engines. ***

7. Common Mistakes and Lessons
Lessons
•Disaster planning…(?)
•Manned Disasters
•Unmanned/HW
disasters
•Natural Disasters
Mistakes (?)
•ASSUME…

8. Most Frequent Unplanned Disaster…

9. BACKUPS ARE
WORTHLESS IF YOU
CANNOT RESTORE
THEM

10. High Availability – Failover Clusters (?)
10
John’s First Cluster

11. Disaster Events – if you stay READY you
won't have to get ready…..

12. SLA’s Define Your Maintenance Plans
Recovery Time
Objective
Recovery Point
Objective
Photo Credit: Clock and Lost and Found

13. How do you do performance tuning (??)

14. Are you utilizing the Query Store?
DBA Basics: Getting Started with Performance Tuning - SQL Server Consulting &
Remote DBA Service (johnsterrett.com)

15. Myths of Database Management
(The good, the Bad and the Ugly)

16. ORMs (Entity Framework) are worst
performers…
True?
False?

17. Once you move to the Cloud you don’t
need a Data Engineer(DBA)….
True?
False?

18. Performance - we can always scale
up…
True?
False?

19. Database Indexing – only affect DML
operations
True?
False?

20. You have automatic backups, are we are
good to go?

21. Do We Need Maintenance Plans?
Photo Credit

22. Critical Care SQL Maintenance
•Backups
•Integrity Checks
•Index and Statistics
•Disk Usage
•Error Log Management
•Jobs and Processes
Photo Credit

23. The Gold Standard SQL Server for
Database Maintenance Tasks
dbatools docs | Install-DbaMaintenanceSolution

24. Why shouldn’t we use the built in
maintenance plans?

25. Testing for Restores and Corruption….
Test-DbaLastBackup -SqlInstance ProdSQL01 -Destination TestSQL01
Restores all or some of the latest backups and performs a DBCC CHECKDB.
Gathers information about the last full backups
Restores the backups to the Destination with a new name. If no Destination is specified, the originating SQL Server instance
will be used.
The database is restored as "dbatools-testrestore-$databaseName" by default, but you can change dbatools-testrestoreto
whatever you would like using -Prefix
The internal file names are also renamed to prevent conflicts with original database
A DBCC CHECKDB is then performed
And the test database is finally dropped
dbatools docs | Test-DbaLastBackup

26. Central Management Server
© ProCure SQL LLC ®

27. Evaluate your daily checklist against 100+ instances with PBM and CMS - SQL Server Consulting &
Remote DBA Service (johnsterrett.com)

28. Daily Health Check Scorecard

29. Do Not Reinvent the Wheel
•Dbatools.io – Powershell automation for SQL Server
•Ola Hallengren Maintenance Solution
•Built in Features like Query Store, Central Management Studio,
Policy-Based Management

30. Procure Your Data Service Provider!
Service Details – Packages start as low as 4k
per month
•US-Based Resources only.
•Primary Data Architect and access to our
team of Experts for anything data related.
•Ability to help with training, architecture,
projects, and reporting at the same rate