Modern cloud IAAS offerings are sold as a fast and convenient way to setup a server without having to worry about purchasing hardware, networking, and basic installation/configuration work.
For the most part, I believe this is true, and they’ve fulfilled their promise very well. However, the basic configuration work still needs to be done for Microsoft SQL Server Virtual Machines (VMs) to ensure the best performance possible.
This blog post compares Pythian recommended best practices for a SQL Server box to what comes out of the box with a Microsoft Azure IAAS Instance on Windows Server Datacenter 2016. Please note that the recommended options here are our generic recommendations, and your mileage may vary. In addition, we’re only looking at networking & Windows settings here. The instance settings are too variable and based on the applications running on top of the database.
Pythian recommends setting the NIC to Full Duplex, disabling NetBIOS, and disallowing Anonymous Enumeration of SAM Accounts.
On the Azure instance, the NIC is not set to Full Duplex, NetBIOS uses the settings from the DHCP server, and doesn’t allow Anonymous Enumeration of SAM accounts.
For the NIC setting however, the emulated card connects to a virtual switch, which is then the bride to a physical card. The physical card will have the correct settings (hopefully).
So, maybe OK here depending on the DHCP server settings. The NIC settings may not be changeable by the end user, and potentially an issue.
Pythian recommends the following settings for Windows:
- Have latest SP installed
- Enable LPIM
- Have Paging file on separate drive & configured to ~1.5 the amount of ram (except for very large servers)
- Have CPU Power Plan to set to High Performance
- Enable Instant File Initialization
The VM comes with Windows Server Datacenter 2016 installed, and as of today there are no service packs. LPIM and Instant File Initialization are not enabled. CPU Power Plan is set to High Performance. The paging file is created on the D drive, as expected.
I would rate this pretty poorly as a default setup. LPIM and IFI are easily changed to allow SQL Server to make use of them, and while they might not always be needed or wanted, they should definitely be the default.