Question:
Is it OK to use a virtual SQL Server?
Are there things I should consider when planning virtualization of the SQL Server?
Answer:
Plenty of folks now deploy SQL VMs. The notes below may help you plan well.
It is definitely possible to virtualize your SQL Server, but doing it incorrectly will result in decreased performance and throughput. Virtualization has become so simple on average that it is often done casually—after all, there are many benefits and it works so well. But, when you plan the storage for a virtualized SQL server, you need to understand and consider disk IO far more thoroughly than for virtualizing a file or application server.
Some of the language varies depending upon if you are using Microsoft Hypervisor or VMWare, but the concepts are the same.
If you do not understand these topics, STOP until you do or seek professional help 😉
- Use Multiple VHDX files, not one huge one and deploy them on separate physical LUNs
- OS
- SQL Production Data (MDF, NDF)
- SQL Logs (LDF)
- SQL Backups and SQL Archives (sometimes split)
- Use Enterprise grade SSD for OS, Production Data and LDF
- Optionally, use 10 or 15K HDD for Backups and/or Archive Databases
- If using a SAN, consider keeping the OS VHDX local to the Hypervisor and move SQL TempDB to this volume
- RAM: 32 GB minimum
- Cores: 2 Minimum, add 2 for more than 50 users.
What not to do
- Do not use production checkpoints on the SQL VM. Sure, you might use a short term checkpoint to protect an update. In our opinion, SQL generates too much data change to make production checkpoints viable.
SQL File Initialization
Whether or not you virtualize, review this Microsoft blog on How and why to enable instant file initialization and this more recent article Improving SQL Performance using Instant File Initialization. SQL 2016 and later allow you to enable Instant File Initialization during installation!
If you choose not to enable instant file initialization, then you may wish to decrease the auto growth increment and plan on scheduling predictive (pre-demand) increases to the database size.
Since SQL 2012 SP4, you can check if your server has properly enabled Instant File Initialization:
SELECT servicename, startup_type_desc, service_account , instant_file_initialization_enabled FROM sys.dm_server_services
The minimum auto growth increment recommended by Microsoft is 1MB and may result in fragmentation (and related alternate performance issues). The actual recommendation is to decrease allocation until a value is found that can be allocated without a timeout (32,16, 8,4,2…1).
ALTER DATABASE sfDocSys MODIFY FILE (NAME='sfDocSys_BinData', FileGrowth=1MB)
To pre-allocate space, use the pu_AssureAvailableSpace stored procedure. ICTool will create a SQL Agent job that runs this procedure daily. It determines recent space demand and assures there is sufficient space to meet the likely demand for the next day or two. If not, space is added, and data is written to the DB File to encourage SQL to initialize the newly allocated space.
KBA-01515; Last updated: September 12, 2018 at 14:48 pm