JEFF TAYLOR & SHAWN MEYERS

Top 10 12 Things DBA's Should Know About Their Infrastructure

JEFF TAYLOR

• Data/Software Architect
   
• SQL Server Since SQL 6.5
   
• 23+ years working with Microsoft SQL Server
   
• President of Jacksonville SQL Server Users Group
   
• Contact: Jeff@ReviewMyDB.com, @reviewmydb
   
• Blog: https://blog.reviewmydb.com

SHAWN MEYERS

• Field CTO - Tintri
   
• Experience in VMware, Microsoft,
  SQL Server, storage infrastructure,
  performance tuning
   
• Been working with SQL Server since 6.5 in 1996
   
• Lots of possible initials but I don't keep track
   
• @1dizzygoose
   
• linkedin.com/in/shawnmeyers42

#1 - Where are your databases?

Question

• Physical?
  
   
• Virtual?
  
   
• Cloud?

#1 - Where are your databases?

Settings

• When virtual, how many controllers?
   
• What are your Power settings on the virtualization host?

Power Settings

Settings

CPU-z Demo

Power Settings

Power Saver

Power Settings

Balanced

Power Settings

Performance

#1 - Where are your databases?

Settings

• When virtual, how many controllers?
   
• What are your Power settings on the virtualization host?
   
• When cloud, what performance level are you optimized for compute, storage, database and memory?

#2 - Storage Connection

How do you connect?

• Fiber Channel, 8GB, 16GB, 32GB
   
• iSCSI – 1GB, 10GB, 25GB, 40GB, 100GB
   
• FCoE – Fiber Channel over Ethernet
   
• NFS – Network File System
   
• SMB - Server Message Block
   
• CSV – Clustered Shared Storage

#3 - Storage Type

Hardware

• Flash, NVMe or spinning rust?
   
• What is your RAID level for each drive?  Not as important on AFAs
   
• How is your SAN configured? Disk pools, aggregates, or datastores?
   
• Bonus Points - What is your storage block size, for disk alignment?

#4 - Anti-Virus

Configuration

• What vendor?
   
• When are scans scheduled?
   
• Can you review and modify the exclusions?

#4 - Anti-Virus

Configuration

• SQL Server data files - .mdf, .ndf, .ldf
• SQL Server backup files - .bak, .trn
• Full-Text catalog files
• Trace files - .trc
• SQL audit files - .sqlaudit
• SQL query files - .sql
• Analysis Services files
• Filestream data files
• Remote Blob Storage files
• Reporting Services temporary files and Logs
• Extended Event file targets -  .xel or .xem.
• Exception dump files - .mdmp and Logs
• In-memory OLTP files
• DBCC CHECKDB files

#4 - Anti-Virus

Configuration

• Replication executables and server-side COM objects
• Files in Replication Snapshot folder
• Processes to exclude from virus scanning - Most excluded with

• For Clustering - Exclude the following

#5 - Network

Configuration

• How many networks do you have?

• Data
• Storage
• Backup
• Heartbeat
• Availability Group Replication

• How many networks cards do you have?
   
• What are the speeds of each network?
   
• Bonus: TSO? RSS? - Homework
  https://kb.vmware.com/s/article/2055140
  https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling

#5 - Network

Performance

• Is Jumbo frames enabled on the storage network?
   
• Is Jumbo frames enabled on your storage network cards?
   
• Do you have Multi-Path setup between your server and SAN?

#6 - Network Security

Configuration

• Network segmentation is the new hot security topic
   
• Do you have firewalls?
   
• Do you have internal firewalls?
   
• Do you have ACLs? (Access Control Lists)
   
• Can every computer inside your network see your SQL Server?

#6 - Network Security

Configuration

• Is SQL Server in a DMZ? - Never do that.
   
• For External Access - Use Non-Standard Ports
   
• Lock down with ACLs what in DMZ what can see SQL

#7 - What is on MY Server?

Agents

• What agents does the IT department install on my Database servers?
   
• Have you installed any database monitoring tools like Idera SQL DM, or SQL Sentry or Quest on your server?
   
• Has your IT department installed an agent for Windows monitoring?
   
• Are there patching agents?
   
• Are there inventory agents?
   
• Are IDS agents? (Intrusion Detection)

#8 - Windows

Roles and Features

• What roles and features are installed on my server?
   
• You only need one feature, .NET 3.5 framework, SQL Server 2017 and above don't.
   
• Most others just require more patching, more security risk.
   
• Minimize - Only what is installed.
   
• Consider using Windows Core - Keep IT from using your powerful server as their desktop.

#9 - NUMA

Configuration

• How many NUMA nodes do you have?

#9 - NUMA

Configuration

#9 - NUMA

Configuration

#9 - NUMA

Configuration

• Set Your MAXDOP

• If multiple NUMA nodes set to max inside one NUMA Node.
   
• If single set to 1/2 of a single node

#10 - Processor

Configuration

• What processor do I have?
   

• How many processors?
   

• How many cores per processor?
   

• Is Hyper-threading enabled?

#11 - Memory

Configuration

• How much memory does your server have?
   
• What is the maximum memory my server supports?
   
• What speed of memory is it?
   
• Do you have NVDimms installed?
   
• Are you using PMem (Persistant Memory)?
   
• Did you know most of the last 4 generations of Intel processors would slow down memory speed by up to 30% if you used all memory banks?

#11 - Memory

Configuration

• Newest generation of Intel Processors no longer has this limitation.
   
• E3, E5 and E7 processors - v1, v2, v3, and v4 all have this issue.  (Sandy Bridge, Ivy Bridge, Haswell, and Broadwell.)  Platform scaleable are good.

#12 - BIOS

Configuration

• Is your BIOS up to date?
   
• Are power settings disabled in BIOS?
   
• If using virtualization, are your virtualization settings in BIOS enabled – all servers shipped in the last 8 years have them.

Performance

How are your databases doing?

Demo - Current Stats

Show Improvement

Does this make a difference?

Crystal Disk Mark

Before/After Results

Show Improvement

Does this make a difference?

iOps/Throughput

Before/After Results

• Don't forget all the database items
   
• Backups
   
• Security
   
• Encryption

This is just infrastructure

Questions?

Contact

Jeff Taylor

Email: Jeff@ReviewMyDB.com

Twitter: @ReviewMyDB

Blog: https://blog.reviewmydb.com

Shawn Meyers

Email: smeyers@tintri.com

Twitter: @1dizzygoose

Linked In: linkedin.com/in/shawnmeyers4