Database consolidation is the process of moving databases to run on fewer servers, reducing infrastructure (and possibly licensing) costs. It can also be used to spread databases from one instance onto new ones to balance the workload.
Creating a database consolidation project
- Select Plan > Database consolidation from the navigation menu.
- Select Create new project from the dropdown menu and give the project a name.
- Select the planning scope from the drop-down. This selection determines how far into the future the forecast is calculated.
- Select start date and end date from the calendar controls. These values determine the time period from which monitoring data is used.
- Click the Create button to save the project.
Creating a host server
- To create a new server, select the type of server from the drop-down menus called Brand, Series, Line and Model, and then click on the plus (+) button.
To create a Nutanix server, first check the "Create Nutanix template" checkbox and then select a brand from the dropdown.
Once you've clicked on the plus button, the new host server will appear below. For each host server, the number of cores and the server's benchmark is shown in parentheses.
To configure the host, select it and right-click to bring up the context menu.
The menu options are:
- Optimize server
- Automatically selects the optimal CPU and socket count combination for the host (i.e. one that has the least number of total cores but still fits everything) and, if possible, divides the host's cores among the virtual servers so that all SLAs are at 100%.
- Select CPU
- CPUs can also be selected manually. The list of CPUs available is sorted based on the number of maximum cores and benchmark. Minimizing the number of cores and maximizing the benchmark gives you the best value for money.
- CPUs can also be selected manually. The list of CPUs available is sorted based on the number of maximum cores and benchmark. Minimizing the number of cores and maximizing the benchmark gives you the best value for money.
- Set socket count
- Allows you to change the number of sockets in use on the server. The number of sockets available depends on the selected server. Cloud servers do not have this setting.
- Set OS / CVM cores
- The number of cores reserved for the operating system on the host. The higher this number is, the less processing power is available for the virtual servers under the host. In the case of Nutanix servers, this means the number of cores reserved for the Controller Virtual Machine (CVM). Cloud servers do not have this setting.
- Set OS / CVM RAM (GB)
- The amount of RAM, in gigabytes, reserved for the operating system. For Nutanix servers, this means the amount of RAM reserved for the Controller Virtual Machine. Cloud servers do not have this setting.
- Set RAM amount (GB)
- The amount of RAM, in gigabytes, available on the server. On physical servers, this can be set to less than the maximum installable on the server. Cloud servers do not have this setting.
- Set criticality
- This setting defines the maximum fill levels for CPU and RAM as well as the SLA levels. These values can be changed by going to Settings > Metadata > Criticality.
- Set CPU / RAM / IOPS growth factor
- By default, growth factors are set to zero, but you can override this by choosing another value. Manual values mean a yearly percentage increase. Choosing a manual value on the host level affects all virtualized servers under it.
- Clone server
- Creates 1-10 identical copies of the host server.
- Virtualize server
- Creates 1-20 virtual server under the physical host. Cloud servers do not have this option.
- Delete server
- Removes the host server and its virtualized servers from the project. Deleted virtualized servers will appear in the tree on the left again.
Moving databases onto new hosts
- Create the first instance by dragging one or more databases from the tree onto the new server you created.
For each server in the tree, the number of cores is shown in parentheses, as well as the server's current benchmark value. For databases, the average CPU and memory usage for the selected time period is shown.
The first time you drag databases onto a server, a new instance with system databases is automatically created for them. - Create another instance on the same server by dragging database nodes them from the tree onto the server node. A new instance will be automatically created.
- Add more databases onto existing instances by dragging them from the tree onto an already created instance node. The numbers in the parentheses next to the system databases reflect the number of source instances; in this case the two user databases come from two different instances. The workloads of the target system databases are a sum of the source system databases' workloads.
- View the total CPU and RAM of a host by clicking on a host server.
You can view the CPU and RAM of individual instances or databases by clicking on them.
Creating virtual servers from a host server
- Create a physical host or select a physical host with no databases added to it.
- Configure the host by doing the following:
- Select a CPU
- Select the number of sockets
- Set the amount of RAM
- Set the OS reserves
- Select the number of virtual servers from the Virtualize server submenu.
The virtual servers will appear under the physical host, and the available logical cores from the host will be divided equally among the virtual servers.
For example, here the host server has 32 physical cores which is 64 logical cores, so the virtual servers get 20 logical cores each by default. These core counts can be changed manually at any point or automatically adjusted using the auto-optimize feature.
Note that the CPU and socket count of the host cannot be changed after virtualizing the server. - Drag databases onto the virtual servers the same way you would on physical hosts.
New instances are automatically created the same way as on physical servers. - Configure the virtual servers using the context menu.
For each virtual server, you can configure the following:
- Target core type (physical or logical)
- Core count
- OS reserves
- Criticality
- Growth factors
Configuring databases and instances
- To configure a database, select a database and right-click to bring up the context menu. By default, growth factors are set to zero, but you can override this by choosing another value. Manual values mean a yearly percentage increase, while "Auto" will calculate the linear regression trend and apply it.
- To configure an instance, select an instance and right-click. The instance edition setting affects only the maximum RAM limit on the instance (128 GB for standard and unlimited for enterprise). To change the instance name, type in a new name and hit enter.
- To configure a virtual server, select a virtual server and right-click.
- To configure an instance, select an instance and right-click. The instance edition setting affects only the maximum RAM limit on the instance (128 GB for standard and unlimited for enterprise). To change the instance name, type in a new name and hit enter.
Using the auto-optimize feature
SQL Governor v16 has a powerful new feature for auto-optimizing host and virtual servers. Instead of manually experimenting to find out the correct number of cores for servers, you can let the auto-optimize feature to determine the best settings.
To optimize a physical server with no VMs:
- Select a brand from the dropdown and create a new host server with the model you want.
- Drag one or more databases to the host.
- Set the criticality for the host.
- Optionally, set growth factors for the databases and OS reserves for the host.
- Select the host and click Optimize.
The engine will select the correct CPU and socket count from the list while keeping SLAs at 100%.
To optimize the VMs of a physical host:
- Select a brand from the dropdown and create a new host server with the model you want.
- Virtualize the host by creating 1-20 virtual servers from the context menu.
- Drag one or more databases to the virtual servers.
- Set the criticality for the host and the virtual servers.
- Optionally, set growth factors for the databases and OS reserves for the host.
- Select the host and click Optimize.
The engine will select the correct number of cores and RAM for each virtual server while keeping SLAs at 100%.
To optimize cloud servers:
- Select Azure from the brand dropdown and create a new cloud server with the series you want.
- Drag one or more databases to the server.
- Set the criticality for the the host.
- Optionally, set growth factors for the databases.
- Select the host and click Optimize.
The engine will pick the correct size with enough cores and RAM from the selected series, while keeping SLAs at 100%.
Viewing the report
- Click Process.
- Navigate to Reports.
- Expand the Capacity planning node.
- Click on Database consolidation results in the report list.
- Select your project from the drop-down list inside the report.
- Select a server from the next drop-down list.
- Click on View report.
The top part of the report contains summary information about the whole project.
The next part shows information about the selected server setup.
The graphs below the tables show the CPU and RAM usage of the instances in a stacked area chart.
Under "IOPS and data needs", a list of all the instances on the selected server is shown along with their read and write data, latency and file sizes. Note that these are forecasted values based on the planning scope you selected and the collected time series.
To drill down and see the IOPS and data file forecasts of individual databases, click on one of the instance names. You should see two charts and a list of databases on the instance.
You can drill further down to database level by clicking on one of the databases. The database charts will show the forecasted reads, writes, file size and latency of the database.
Navigating back to the main page of the report, the volume info is shown. For each volume, its databases and their predicted IOPS values are shown. System databases and databases that were migrated from Azure Managed Instances are shown under the "-" volume, since they either have no clear target volume or are combined from several different source volumes.
By clicking on a database name, you can drill-down to a volume latency report. It shows latencies per hour as well as a distribution of latency times.