When do I install the SQL Server database on a physical server?

This is one part of a 5 part series highlighting where DBAs can deploy SQL Server databases. The 5 places where your SQL database can live are:

• Physical server
• Virtual server
• Cloud server :IaaS – Azure VM or AWS EC2
• Cloud instance :PaaS – Azure Managed Instance
• Cloud database :DBaaS – Azure SQL Database or AWS RDS

Now, installing the SQL Server database on a physical server may take us back to the “(g)olden days” of data centers, the pre-ESXi days, the days of racks, and MORE racks of servers and storage and networking and cables… remember the cables?!?!??!  I took pride in my cabling, measured and ordered the lengths, colors, proper wraps, and even labeling. I actually found it therapeutic on some projects to make the cabling flawless.

But we don’t think of installing SQL Server on physical servers just for old-timers and relics shouting obscenities at Clouds, there are real use cases for physical servers today!

Top 5 (or 6) Pros to installing SQL Server database on a physical server

1. Zero performance loss
   1. No “vmware tax”, no noisy neighbors, etc
2. Exact processor selection
   1. Performance improvement every year from Intel
   2. Glenn Berry writes FANTASTIC blogs on this
      1. https://www.sqlskills.com/blogs/glenn
   3. As few as 4 cores up to 56 cores in a basic 2-socket server – nothing too fancy here
   4. Your total SQL Server cost is directly tied to the processor you select – CHOOSE WISELY
3. Capitalized/Depreciated expense
   1. Accountants love this, and most companies are more comfortable with this method of purchasing in IT
   2. One-time cost (mostly)
   3. Depreciated over 2 or 3 years?
      1. If performance really is crucial enough to be going with physical hardware, why not upgrade every year if you care about Pros 1 and 2.
4. Comfort level
   1. You can go pet it!
   2. 100% control of entire stack – OCD much?
5. Securing your data
   1. When done properly (read: expensively with a team of security engineers) everyone in the organization can be 100% confident no one has access to your data besides you
6. New technologies
   1. Buying a physical server will be the only way you can take advantage of some great new hardware
   2. Intel’s 3D-Xpoint Optane NVMe SSDs
   3. New persistent memory class of devices

Top 5 (or so) Cons to a physical SQL Server Database

1. Performance may not match the workload exactly
   1. Means we must upgrade again OR we spent too much money initially
      1. Some upgrades are easy – RAM, add more disks
      2. Some upgrades are difficult
         1. Add more disks and have to rebuild RAID?
         2. Add more CPU – replace processors, no returns!!
            1. And pay additional licensing…
2. High availability challenges
   1. Can’t do Failover Cluster without shared storage… adds expense
   2. Can still use replication, mirroring, Availability Groups
3. Backups
   1. How fast can we get your data OFF that physical server and land it somewhere else?
   2. How fast can that backup be restored to another box?
4. Think about SPOFs beyond the database
   1. Datacenter power? A/B power?
      1. Backup power – diesel delivery…
   2. Redundant power supplies
   3. Redundant networking, switching
   4. Fire suppression?
   5. Video surveillance
   6. HVAC… and more I’m sure!
5. Physical security
6. May not be able to pass SOC 1/2 audits!!!
   1. Move to colocatiuon facility for some ease
   2. Significant effort will be required if the business mandates compliance
7. Spare parts in stock?
   1. What spare parts do we keep on-site?
   2. Do we pay the vendor extra for 4HR parts support vs next business day?
8. Location
   1. Are the physical servers close to the application servers and are they close to your users where they work?
   2. Is that also in a location that also has good power, not prone to flooding, managed by a company that pays its bills and has multiple ISPs?

If we must absolutely, positively have the fastest SQL server for the money – we can build it ourselves selecting the exact CPU, high performance RAM, get the latest technology in persistent memory and NVMe goodness and eliminate every know bottleneck! There are still LOTS of applications for this, don’t think that because we are considering buying a server for SQL that we are already down the “wrong” path.

Tips:

• NEVER take an older server and install SQL Server on it for production
  • Licensing and ALL the reasons (every single one) are too important to waste money here
• DISKs will be your slowest part of the stack
  • Invest in RAM where you can to cache as much data as possible
  • RAID10 everything (no BUTs about it)
  • NVMe PCI-Express SSDs for all you can afford
  • NO SATA SSDs (even if they are superfast in your laptop)

Here are two recent SQL Server database builds of mine for clients with very specific requirements for performance, proximity to application servers, and licensing restrictions.

Use cases:

• Small 200GB database in an OLTP system
  • ~$17,000 to do it RIGHT
  • Dell PowerEdge R340 – ~$6000
    • Single Xeon E3-2174G with 4 cores at 3.8GHz
    • 128GB RAM
    • Redundant PSU, Intel NICs, all the Enterprise basics
  • Add mirrored 1TB PCI-Express based NVMe – $3,000
    • Intel Optane 905P (x2 remember)
  • SQL Server Standard licensing ~$8,000
  • Yes you can go cheaper on the hardware
    • but WHY if you’re paying $8k JUST for the license… DON’T DO IT!!!!
• Large 1.2TB database for a mixed workload (transactional, reporting, and SSIS)
  • ~$206,400 to get a very nice solution in place
  • HPE DL380 Gen10 – $30,0000
    • 2x Xeon Gold 6146 with 24 cores at 3.2GHz
    • 576GB RAM
    • 96GB pMem for TempDB and Log files
    • All the Enterprise basics
  • Add 4x Optane NVMe based SSDs – $8,400
    • RAID10
  • SQL Server Enterprise licensing ~$168,000
    • Required to use more than 128GB of RAM
    • No other hardware enabled features
      • Lots of other goodies (for another blog)
  • AGAIN – DO NOT HESITATE to upgrade that hardware component for $5,000 if it does not affect licensing
    • PBITPro can help you calculate your CPU core requirements to get that CPU selection and licensing cost nailed down
    • The rest of the build is <10% of the total cost so DON’T SKIMP
• High Availability
  • Take your “small” or “large” server build and double it!
    • Works as a failover replica for mirroring, log shipping, replication, and Availability Groups
  • For Failover Clustering, shared storage is the best way to get there
    • Add-on SAS based storage like HPE MSA 2050
    • Add-on iSCSI based storage like Nimble AF1000
    • This gains additional features as well like storage snapshots and tiering

Choosing physical servers for your SQL Server deployment does have its advantages, but as we learn more about modern implementations of databases we might find some better methods to storing our data!  Did you recently deploy SQL Server this way? What was the biggest reason you selected this implementation?

Reach out to me at paul@pbitpro.com and let’s have a conversation about SQL Server!