Truth be told the 5.0 App-V Management server is pretty lightweight however it will grow over time and more than likely at some point the question about adequately sizing your database will arise.
In the 4.x generation of Management server database growth was primarily influenced by the amount of users and how often applications were launched, due to the fact we no longer store this usage data in our management database and offer a separate reporting database this is no longer the case.
In 5.0 database growth is primarily influenced by the number of applications we import and how many integrations those applications have. Due to the nature of applications in 5.0 the way we store them is not as simple as a single record, in fact there a multiple tables which will contain your application metadata depending on how it is made up, making sizing slightly more complex than in previous versions.
I have collected the following data to give you indicative figures as to how large your database might grow and these figures serve as a guide. This environment had numerous packages of different sizes including larger packages such as Microsoft Office and Oracle Client and smaller packages such as WinRar and Skype.
Here is a breakdown of some of the key tables in the Management server database and the average size of a record per table.
|Table||Average Record Size||Description|
|Applications||0.4 KB||For each application in a package|
|FileTypeAssociations||0.2 KB||For each FTA per package|
|PackageEntitlements||14 KB||For each entitlement to a package|
|PackageVersions||178 KB||For each package|
|ProgIds||0.3 KB||For each program ID|
|PublishingServers||8 KB||For each publishing server|
|ShellCommands||0.4 KB||For each shell command|
|Shortcuts||1 KB||For each shortcut|
Let’s analyse the main action that is going make our database grow and how we can go about calculating the impact.
Importing and Entitling a Package
As mentioned this will be the main driver for database size, the good news here is that unlike in 4.6 where the database would constantly grow based on users and usage, in 5.0 the growth will be less dynamic, with the impact mainly held in the early stages of provisioning packages and then the gradual add of new or updating of existing packages over time.
The bad news however is calculating this impact is not straight forward! The reason for this is because every time you import and entitle a package, records are created across multiple tables and the amount of storage required will vary. For example the PackageVersions table will contain a full copy of both user and machine config .xml files, the size of these files will vary package to package, subsequently so will every FTA that gets written into the FileTypeAssociations table or every shortcut written into the Shortcuts table. The PackageEntitlements table will also contain any custom configuration too and can also mean different record sizes.
The three key things you will need to get a handle to size appropriately are:
- What your average package is made up of (more on this below)
- How many packages you foresee importing (including package upgrades and future packages)
- Approximately how many group entitlements each package will have
So based on my averages the way to calculate the database growth of importing and entitling a package would be:
178 KB (Average PackageVersion record size)
(Number of Applications in Package x 0.4 KB)
(Number of FTAs per package x 0.2 KB)
(Number of ProgIds x 0.3 KB)
(Number of shell commands x 0.4 KB)
(Number of shortcuts x 1 KB)
(Number of groups entitled to package x 14 KB)
Database growth from single package import and entitlement
Number of Packages
Database growth from package imports and entitlements
Phew! Okay so not the most straight forward thing to calculate although you could look to automate a lot of the number crunching via PowerShell as the numbers are all held within the configs xml files and the database. However I think for most people doing this per package would be over the top and a simplified approach of taking the stats of what an average package is and applying it across the board would be enough to keep the database admins happy!
The average package in this particular environment is made up as below:
So if your average package was as above and we had 1,000 packages in the environment the formula would look like this:
|178 KB (Average PackageVersion record size)||178 KB|
|(Number of Applications in Package (5) x 0.4 KB)||2 KB|
|(Number of FTAs per package (50) x 0.2 KB)||10 KB|
|(Number of ProgIds (47) x 0.3 KB)||14.1 KB|
|(Number of shell commands (37) x 0.4 KB)||14.8 KB|
|(Number of shortcuts (3) x 1 KB)||3 KB|
|(Number of groups entitled to package (8) x 14 KB)||112 KB|
|Database growth from single package import and entitlement||333.9 KB|
|Number of Packages||1,000|
|Database growth from package imports and entitlements||333,900 KB|
In this case for 1,000 applications we can expect approximately 334 MB of data to be written to the data store. Again, remember this is based on an average application in a particular environment and may vary depending on the type applications you have.
Once you are armed with this number I would recommend multiplying by three. This will account for the following:
- SQL “reserved data” allocations per table
- Other configuration and entitlements data such as connection groups
- Margins of error with estimated averages
- Future growth
Database growth from package imports and entitlements x 3 = Size for SQL Database
This means for my environment of 1,000 packages I would be sizing my SQL database at approximately 1GB in size.
As always please proactively manage your database and usage data. These figures are meant to provide an approximate guideline. In any respect I think you will agree even after calculating the storage impact, our final number for an environment of 1,000 packages is relatively modest and shouldn’t be anything that will cause your storage/database teams too much headache. Now you understand what impacts your App-V Management Server SQL database size go ahead and use the calculator to find out your figures by using the link below: