Microsoft SQL Server Best Practices in a virtualized environment

What does a self-employed florist have in common with a multi-billion dollar company? What does a hospital have in common with an advertising company?

That’s right! They all use some form of a database* to store their data. Whether it’s an excel file containing names, orders, addresses or an access database, a MySQL, a postgres, Oracle or a MS SQL.

*Database. All companies need to order and categorize their data in a logical way so it can be queried the best way possible to provide the needed data.

Virtualization age

Since virtualization is omnipresent nowadays, it’s not surprising that database servers are being virtualized as well. This blog post contains some tips and tricks for setting up a database server. These are simply experiences in setting up a (Windows) virtual machine that holds a database and that provides the expected performance. The advice given below is dependent of your own specific environment. Use it with care.

The first thing to do is add more disks. The amount of disks you will need to add will always be more than 3. One for backup, one for logs and one for TempDB.  The amount of Data disks (the disks that hold the actual database files) to add, depends on the size the databases will eventually have. Usually it is good to have 3 Data disks present on the virtual machines. This also means you have to split up all database files in multiple data files.

Regardless of the number of disks you have to add, all disks added should be formatted as ReFS. Subsequently, you have to double check that you also added 3 more SCSI controllers of the type ‘ParaVirtual’. The OS disk, can be left as it is configured by default. But each extra disk added, should be attached to a different ParaVirtual SCSI Controller.

When you add multiple disks to the same SCSI Controller, make sure to start with location 1:0 for the first disk and if there is a second disk on that controller, add it to 1:8 (Same for the other paravirtual SCSI controllers).

When the disks are properly added, attached to the correct controllers, formatted and available in the windows machine; you should point your attention to SQL Server. Make sure the log files for all databases are kept on the intended disk, and the same for the backups of the databases. The tempDB is moved to the intended disk and is split into the multiple files. The amount of files used for the TempDB, you should set equal to the number of vCPU’s in the virtual machine with a maximum of 8. Like said before, you should format the disks with the ReFS filesystem, which uses 64K extents. Therefore you should change the database file growth for each database from a percentage to a fixed size in MB and to a value of 64, 128, 256, 512 or 1024, depending on the projected size of the database.

This leaves you with a good starting point for the database server. Next, when working with a transactional database (OLTP), we can do instance specific configurations that are beneficial for the performance. More details about these settings can be found here.

As you can see, it will take you some time setting up a virtual database server. With the steps described above, the performance of the database meets the requirements. With some further tweaking to the database instance and the databases itself, performance can be improved some more. But that is a trial and test which is done together with the business.

Maintenance

Each database server needs to be kept up to date. This to minimize the attack surface for both the OS as well as the SQL Server installation. It’s only logical to test the windows and SQL Server updates and after approval apply them to the production environment.

Next to this, the need to perform backups, index optimization and other things need to be done as well. To keep the SQL Server in optimal form, you can use the maintenance solution created by Olla Hallengren. Most of you will have heard of him or are already using his maintenance solution. If you don’t know him, you should definitely take a look at his website and see if it is a good fit for your customers/company. An example of the jobs created by the maintenance script can be found below:

The jobs created by the maintenance script are self explanatory. Once created, you only need to set them up according to a schedule.