Lets cook best SQL Server DBA practices SQLMaster

Let's cook ‘best’ SQL Server DBA practices @SQLMaster Satya Jayanty SQL Server MVP sqlmaster@sqlserverqa. net Principal Architect & Solutions Lead www. sqlserver-qa. net

Let's cook ‘best’ SQL Server DBA practices @SQLMaster sqlmaster@sqlserverqa. net www. sqlserver-qa. net

About me : Satya Jayanty § IT Experience § Been in the IT field over 24+ years (using SQL Server ver. 4. 2 onwards) § Principal Architect – D Bi A Solutions, Europe (consulting@dbiasolutions. co. uk ) § 10 years as Microsoft MVP (Windows – SQL Architecture) § Community Contributions § Speaker : Microsoft Tech-Ed (North America/India/Europe), SQLPASS, SQL Bits, User Group (Scottish Area SQL Server User Group) § ‘Ask The Experts’ Lounge: Microsoft Tech-Ed, Heroes. Happn. Here & SQLPASS § Writer & Technical Reviewer for SQL Server 2008 & 2012 certification papers. § Publications § Founder (SQLMaster) & blogs at www. sqlserver-qa. net; (SQL Server Knowledge Sharing Network) § Author: SQL Server 2008 R 2 Administration cookbook & SQL Server Analysis Services Cube Security Instant § Co-Author: MVP Deep Dives Volume II. Technical Reviewer: SQL Server books from Packt Publishers. § Active participation in assorted forums such as SSP, SQL Server Central, MSDN, SQL Server magazine, dbforums etc.

Say Thank you to Volunteers: § They spend their FREE time to give you this event. § Because they are love our COMMUNITY. § Because they want YOU to learn from the BEST IN THE WORLD. § If you see one of these guys in the hall – buy them a beer/wine, they deserve it.

Vitaliy Popovych Olena Smoliak

Maksim Garnets Yevhen Nedashkivskyi

Oksana Borysenko

Author www. packtpub. com http: //tinyurl. com/sql 2 k 8 r 2 admincookbook http: //tinyurl. com/sql 2012 Instant. Cube. Security e. Book & Paper. Back § § www. packtpub. com Amazon US & UK

http: //www. manning. com/delaney/ Manning Publications and the authors of this book support the children of Operation Smile.

Before We Begin § Covered ü ü Benefit of best practices Common best practices What to do, not how to do it Rules vs exceptions, as they fit § Not Covered Ø End-to-end coverage or in-depth drilldown of all SQL Server 2008 R 2/2012 or 2014 features Ø Exhaustive list of all issues, no magic tricks Ø No promotion of 3 rd party tool § Assumption ü Working knowledge – SQL Server ü Aspiring DBA from System Support/Developer ü You may be a part-time or full-time DBA (limited experience), but you are familiar with SQL Server basics. ü If you are an experienced DBA, then you probably are already familiar with most of this content.

Features cloud…journey so far: SQL Server 2008 R 2 & 2012 … 2014 Incremental Statistics Resource Governor Enhancements

Déjà vu… • • Data Platform How can you manage instances (pro-actively)? What tools can help collect data for analysis? What kind of monitoring strategy you would follow on various SQL instances? How can you detect troubled instances/databases?

Benefit of Best Practices By focusing on SQL Server best practices basics, it helps you as a DBA to: • • Ensure Optimize Maximize Provide Securit y Performance • Be proactive Availabi lity • Reducing the amount of time you spend being in “crisis mode” • Every step counts • Many of them seem familiar and small in scope • Consistency must be part of the best practice Scalability

Common Best Practices. . . Ø Hardware Considerations Ø Installation & Configuration Ø Disaster Recovery – backup and restore strategies Ø Security Ø High Availability Ø Maintenance Ø Performance Tuning Ø Reporting Ø Monitoring & Alerts Ø Upgrades Ø. Ø. Ø. ü Documentation!

Hardware § Core DB relational (OLTP) vs Data Warehouse (OLAP) § Important decisions § SQL Server requirements and usage § § CPU Memory Storage Network § I/O subsystem § Planning requirements § Designing for redundancy and performance § Partition alignment § Virtual vs Physical

Virtualization § To-be or not-to-be § VMWare or Hyper-V, its your choice § Only when you plan multiple VMs § Virtual machines typically limit the number of processors § Whitepapers § SQLCAT : Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations § https: //www. vmware. com/files/pdf/solutions/SQL_Server_on_VMware. Best_Practices_Guide. pdf § http: //download. microsoft. com/download/6/1/D/61 DDE 9 B 6 -AB 46 -48 CA-8380 D 7714 C 9 CB 1 AB/Best_Practices_for_Virtualizing_and_Managing_SQL_Server_2 012. pdf § http: //www. brentozar. com/sql/virtualization-best-practices/

Installation and Configuration § Not a simple topic as it seems. § Plan ahead for performance and optimization § SQL Server installation best practices § § Add Features System & User database file placement Backup file placement Sysprep § SQL Server configuration settings § Database file layouts § Binary Long Objects data § File. Stream/Remote Blob Storage considerations

Property Settings § Instance-wide § Do not change any default settings § § § § Memory Processors Security Connections Database Settings Advanced Permissions § Do not forget to DOCUMENT!

Property Settings § Database-wide § § Auto Create Statistics: On Auto Update Statistics: On Auto Shrink: Off Auto-growth: Leave on § Recovery Mode: FULL/Bulk-logged/Simple § Page Verify: Use Checksum (2005/2008), don’t turn off. § Compatibility Level: § Application specific § Do not make it permanent

Configuration § Dedicated vs Shared § Stand-alone, dedicated § Physical, virtual § Multiple applications § Avoid multiple instances unless you have a really good reason to use them. § SQL Server only! § Unnecessary services should be uninstalled or turned off. § Anti-virus § Anti-spyware § If your organization’s policy requires running antivirus/antispyware software locally, exclude MDF, NDF, LDF, BAK, and TRN files. § Vendor-specific

Storage Configuration § SAN vs DAS ……SSD now § § The RAID group is dead – long live the storage pool! Logical reads vs Physical reads § Disk 1: OS/SQL Binaries § Disk 2: System databases (aside from tempdb) § Disk 3: tempdb • I/O is critical • Pre-size tempdb so autogrowth doesn’t have to happen often (8 MB is default, which is very low). • Set fixed amount autogrowth to avoid many growth spurts § Disk 4: User databases § Disk 5: User DB transaction logs § Tools § § SQLIO, IOMeter & SQLIOSIM tools from Microsoft Crystal. Disk. Mark tool - http: //crystalmark. info/software/Crystal. Disk. Mark/index-e. html http: //www. emc. com/collateral/white-papers/h 12341 -sqlserver-bp-wp. pdf https: //technet. microsoft. com/en-us/video/how-to-optimally-use-sql-server-with-ssds-withoutburning-them-out. aspx

Disaster Recovery - Strategy § Backups fundamentals § Recovery Models § Options – full/differential/filegroup/log § Database backups, how often you restore them? § Scheduled jobs § Databases – User + log & System § Bulk or Full recovery model – must backup transaction log § Initial verification: RESTORE_WITH_VERIFYONLY § Retention Policies § Periodically test backups to see if they can be restored. § Store backups securely and off-site (not on same disk array or SAN).

Disaster Recovery (DR) - Strategy § Limited disk space or backup window § Differential backups § Use Backup compression and tools § Avoid backup corruption § WITH CHECKSUM to the backups so that the page check occurs there as well. § Filegroup backups § Do not backup read-only regularly § Partitioned tables: do not include, full backup is best § Piecemeal restore. § DR plan § No matter what HA feature you use, backups are key § Test the plan periodically § Do it in a Rota

Security § Like you protect the house, you must secure SQL Server too! § Assess who can log in and what privileges they have § Tracking § Who/What/How/When § Authentication Modes § Windows Authentication § SQL Server logins, users and roles § SQL Server encryption § Certificates and auditing

§ Permissions: Server-wide vs Database-scope § Don’t give users more permissions than they need to perform their job. § SA login § A complex password & use domain account with SYSADMIN § No application to use the SA or a sysadmin account to access SQL Server. § Don’t give vendors sysadmin access to your servers. § Log off or lock your SQL Server (or workstation) when done.

High Availability § Disaster Recovery (DR) is different to High Availability (HA) § § § Features § § § § DR is a procedure in place for recovery - RTO HA is a set of technologies to reduce availability interruption - RPO Failover Clustering Database Mirroring Availability Groups – Always ON Log Shipping Replication SAN Mirroring § Vendor dependent Database Recovery model dependent § Per-database § Availability Groups § Data Mirroring § Log Shipping § Replication

Maintenance § Balance between user access, data loads, and maintenance. § Primary activities: § backups, index maintenance & integrity checks. § Maintenance Plans § Database Maintenance § DBCC checks, Update Statistics & DBREINDEX § REBUILD < 30% fragmentation, use ONLINE index (EE) § REORG (>5% & < 30%), then update statistics § Best One § Ola Hallengren’s maintenance solution for better automation. http: //ola. hallengren. com/ § Consistency checking § Index management § Statistics management

Monitoring § Problem, what problem? § Regular events § Performance oriented § Various methods of monitoring and interpret collected data § Schedule jobs - SQLAgent § Database mail/Operators § High severity problems § Create a SQL Server Event Alert for all events with a severity of 19 [fatal] and higher. § Tools for ongoing monitoring: § Performance Monitor and PAL tool (http: //pal. codeplex. com/) § Central Management System (CMS) http: //sqlcms. codeplex. com/ § Uses the MDW, Power. Shell, and the Policy Management Framework to collect system data from a variety of servers to a central server § System Center Operations Manager (SCOM) § SQL Server MOM – Monitoring Management Pack

Tools § SQL Server § § § SQLTrace and Trace Analysis Tools Policy Management Framework Essential DMVs for Monitoring Utility Control Point (UCP) Management Data Warehouse § Third party tools § Baseline, benchmarking and testing changes § Brad Mcgehee DBA checklist: http: //bradmcgehee. com/? s=dba+checklist

Upgrades § Instance wide & specific databases § Upgrade Strategy § In-place & Side-by-side § Don’t upgrade unless you have a good reason to upgrade § New features – go for it § Upgrade Advisor tool § Hardware & software pre-requisite § DBCC CHECKDB WITH DATA_PURITY; § SP_REFRESHVIEW § Backward Compatibility § Breaking changes & Behavioral changes § Reference guides § SQL 2012 Upgrade reference guide § SQL 2014 Upgrade reference guide § Edition based § Can go-up § Can’t go-down

Documentation! § How many of you maintain documentation? § Yes, documentation is very boring, but it is very critical to being a successful DBA. Be sure to document & update. § What to do and what-not? § § § Installation and configuration Application configuration Troubleshooting tasks DR plan Any time any change is made to any instance for any reason. § Be sure that documentation is easily available to everyone who needs access to it.

What we’ve learned… § DBA – protectors of data § Did you take oath when you accepted the job? § The total effect of following each and every recommendation made today can be huge. § What you learned today is only the tip of the iceberg, you will need to take time to learn many other best practices. § Practice makes Process Perfect!

Feedback § http: //speakerscore. com/sqlsat 377 DBA § SQLSaturday Feedback forms § Twitter – @SQLMASTER § Email – sqlmaster@sqlserver-qa. net

Our Sponsors: