September 2, 2022 at 8:31 pm
Hello Fellow Database Enthusiasts,
I am an old guy and virtual machines are new to me, normally on a SQL server installation that you are going to have several or more instances running, you would have separate partitions that the instances are installed on, is that necessary on a VM machine running Windows server 2019? If so what is the best strategy for performance? Obviously having another server going would be nice but not always capable.
Thanks in advance!
September 3, 2022 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 12, 2022 at 2:58 pm
There is a lot that is not said in your question. I will focus mainly on high-level items in this reply.
My first experience of using a DB on a virtual machine was in about 1978 on a mainframe. Everything on the MVS OS was done virtual back then.
On Windows my first Production experience was 2012 when I was part of a team moving a £70m business from a bare metal co-lo to AWS IaaS. The business estimated that each day down would cost us 20% of our customers. End to end the project took 8 months and went live slightly below time and budget estimates with zero downtime.
More recently I worked with a charity running SQL 2019 across multiple data centres running everything in-house under W2019 Hyper-V clusters on HCI hardware. For the DB layer we used SQL 2019 guest clusters and distributed availability groups. Compared to spinning disks, the reduced footprint and power savings of SSD based HCI (disks used only for long-term backups) meant the environmental savings paid for everything in under 3 years. Most performance figures were over 10x better, some over 100x. Resilience and availability remain much higher than anything that could be build on bare metal.
The first thing to establish is where are you moving to and why. Azure PaaS, Azure IaaS, AnOther cloud host, in house, or whatever. What is the level of in-house skill for your chosen environment. The business needs should be your main driver but the skill levels will be your main limiting factor.
Do not be afraid of running SQL on a guest machine. For in-house or IaaS the overhead cost is about 500 Mhz of 1 CPU Core and 500Mb RAM per Windows instance compared to running on bare metal. This gives you a layer of indirection between your SQL host and the underlying hardware that allows you to quickly move your DB server to different hardware when needed. It also gives more options for Windows instance backups, but traditional SQL backups are still often the best approach.
You asked particularly about partitioning. This becomes redundant with SSD storage, but you still need to be aware of total IO loads to each SSD RAID set. Your guest servers should use .vhdx files for all storage rather than raw SSD as again this gives a layer of indirectlion that allows you to easily move things to different hardware . And all of this advice itself becomes redundant if you are running in the cloud.
To finish, when planning your move start small, start with Development, and build from there as you gain skills and experience. Unless you are a non-profit or located where connectivity is poor your first choice for hosting should be cloud, preferably PaaS. Plan for high resiliency and consolidate SQL instances to minimise license cost and wasted CPU cycles.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 12, 2022 at 4:45 pm
You also need to consider licencing costs for MSSQL and they can be very complicated. From memory the minumum licence is 4 cores and then in multiples of 2 up to 64.
You can either licence each VM (not sure what happens with multiple instances on a single VM) or on the bare metal as long as the total number of virtual cores is not greater than 64. After that you have to go into Enhanced support and the costs are truly eye watering.
I believe you also need licences for all of your DEV/TEST/CICD instances.
September 12, 2022 at 4:48 pm
You also need to consider licencing costs for MSSQL and they can be very complicated. From memory the minumum licence is 4 cores and then in multiples of 2 up to 64.
You can either licence each VM (not sure what happens with multiple instances on a single VM) or on the bare metal as long as the total number of virtual cores is not greater than 64. After that you have to go into Enhanced support and the costs are truly eye watering.
I believe you also need licences for all of your DEV/TEST/CICD instances.
DEV/TEST/CICD (as long as not prod one) can be running under SQL Developer licensing - so no cost here.
Only caveat is that those SQL Servers can't connect to the higher PROD environment, neither can the data from any of the databases on those servers be copied over to prod.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply