SQL Server Best Practice for Accidental DBA Bratislava

SQL Server Best Practice for Accidental DBA Bratislava, 21. 2. 2019

Agenda • Inštalácia • Konfigurácia • Zálohovanie • Údržba • Monitoring

Inštalácia • Windows up to date

Inštalácia • Windows Power Plan

Inštalácia • Lock Pages in Memory

Inštalácia • Volume Maintenance Tasks

Inštalácia • Feature Selection

Inštalácia • Services configuration

Inštalácia • Collation

Inštalácia • Engine configuration

Inštalácia • Data Directories

Inštalácia • Tempdb configuration

Konfigurácia • Enable TCP/IP

Konfigurácia • SQL Server Agent Start Mode

Konfigurácia inštancie • Maximum Memory Size

Konfigurácia inštancie • Compress backup • Recovery Interval • Database Default Location

Konfigurácia inštancie • Optimize for Ad hoc Workload

Konfigurácia inštancie • Max Degree of Parallelism

Konfigurácia db • Autogrowth in MB

Konfigurácia db • Recovery model • Compatibility level • Autoclose • Autoshrink • Auto create stats • Auto update stats

Konfigurácia db • Page verify

Zálohovanie a obnova • Systémové db • Master, msdb • FULL backup • Denná záloha • User. DB • Full/Differential/Log backup • Denná/Týždenná/Hodinová • Test obnovy • Point-in-time restore

Údržba • Kontrola konzistencie • Checkdb system db - master, msdb • Checkdb user db • Údržba indexov • Rebuild • Reorg • Údržba štatistík • Aktualizácia • Don’t shrink db! • Aktualizácia inštancie – Service. Pack/Cumulative updates

Údržba • Maintenance solution • Ola Hallengren: https: //ola. hallengren. com/

Monitoring • SQL Server Alerts • Severity 16 - 25 • 16: Indicates general errors that can be corrected by the user • 17: Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator • 18: Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs. • 19: Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.

Monitoring • SQL Server Alerts • Severity 16 - 25 • 20: Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged. • 21: Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged. • 22: Indicates that the table or index specified in the message has been damaged by a software or hardware problem. • 23: Indicates that the integrity of the entire database is in question because of a hardware or software problem. • 24: Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor. 824 -

Monitoring • SQL Server Alerts • Errors: • 823: I/O error <error> detected during <operation> at offset <offset> in file '<file>’ • 824: SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1: 43686; actual 0: 0). It occurred during a read of page (1: 43686) in database ID 23 at offset 0 x 0000001554 c 000 in file 'H: MSSQL. SQL 2008MSSQLDATAmy_db. mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. • 825: A read of the file '%ls' at offset %#016 I 64 x succeeded after failing %d time(s) with error: %ls. Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Monitoring • SQL Server Alerts scripts • https: //www. brentozar. com/blitz/configure-sql-server-alerts/ • https: //www. sqlskills. com/blogs/glenn/the-accidental-dba-day-17 -of-30 configuring-alerts-for-high-severity-problems/