Top 10 12 Things You Should Know About Your Database Infrastructure

Jeff Taylor

Principal Data Consultant

Fulton Analytics

A Senior Data Engineer, Architect & Consultant. Friend Of Redgate. Over 25 years of experience specializing in performance tuning and critical data issues.

Currently serving as the President of the Jacksonville SQL Server Users Group and is an active board member of the Jacksonville Development Users Group.

jssug.org

Every 3rd Wednesday 6-8pm

Except for May, November and December

https://sqlsaturday.com

https://sqlsaturday.com/jax

Annual Free Data Conference

In May - (15th Anniversary in 2023)

Top 10 12 Things You Should Know About Your Database Infrastructure

#1 - Where are your databases?

Question

Physical?
Virtual?
Cloud?

If you think using 'the cloud' removes all these infrastructure items, you are WRONG! It may even make some more important.

#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

Power Settings

Power Saver

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

Microsoft's Recommendations - https://bit.ly/3ceE66l

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<Instance Name>\MSSQL\Binn\SQLServr.exe

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<Instance Name>\OLAP\Bin\MSMDSrv.exe

Q:\ (Quorum drive)

C:\Windows\Cluster

MSDTC directory in the MSDTC drive

#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 - TCP Segmentation Offload
https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling - 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)?

#11 - Memory

Configuration

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?
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.