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/
- Slides: 28