My original article about the sizing of the Personalization and Management Server databases was a little haphazard, a good advert for the accuracy of the old acronym about the seven Ps – Proper Planning and Preparation Preventing P*ss Poor Performance 🙂
Never mind, as the Japanese say, the burned hand teaches best. So we will revisit the database sizing stuff in a little more detail just now.
Database structure
It might be helpful to point out the actual file-level structure of these databases as a starter. The Personalization database is composed of three actual files
- PersonalizationServer.mdf – this contains the actual user personalization data
- Archive.ndf – this stores the archive copies of Personalization data
- PersonalizationServer_log.ldf – this is the transaction log for the database
The Management Server database is a little simpler and contains two files
- ManagementServer.mdf – the actual data that composes the Management Center database
- ManagementServer_log.ldf – the transaction log for the database
The database sizes for each can then be worked out as per the guidelines below.
Management Server
Agents, configurations and events are the main constituents of the Management Server database and the retention of agents and configs needs to be borne in mind, as well as the events that are being audited.
The agents are typically 7-10MB in size, with 7MB being given as a ballpark average figure by AppSense. There are always eight of these agents (Application Manager, Environment Manager, Performance Manager and Client Communications Agent, x64 and x86 versions of each). If you had no requirement for any x86 endpoints you could cut the number of agents down to four. As for retention of previous versions, agents aren’t updated an awful lot (they normally get updated when the AppSense core software is updated), so retaining about two previous versions should cover any possible issues.
The space required for agents would then equate to
Versions to maintain (1 + n previous versions) x number of agents x average agent size (7) = space required for agents in MB
A configuration is usually about 500KB in size. There are normally three of these, one per agent. There is also the question of how many previous versions you wish to keep. Dependent on your environment and recovery requirements, this could be anywhere between 5 and 50.
The space required for configurations would then equate to
Versions to maintain (1 + n previous versions) x number of configurations (3) x average config size (0.5MB) = space required for configurations in MB
An event is typically about 200 bytes in size. You’ll need to check this against your auditing requirements and settings, and ideally do a bit of testing to find out what sort of volume of events each user can expect to generate each day. There’s also the question of how many days’ events you need to retain for audit or regulatory compliance.
The space required for events would then equate to
Number of events raised per user per day x number of users x number of days’ retention x average event size (0.0002MB) = space required for events in MB
And then simply add these three figures together to get the total projected size of your Management Server database. Don’t forget that if you are only retaining a certain number of agents and configurations, you may need to have a process in place to manually trim the unneeded ones – I’m not aware (as yet, please someone correct me if I am wrong) of any feature inside the Management Center that removes them automatically.
Personalization Server
It is application and profile data that composes the Personalization Server database, so the figure you’ll need to find out is the average size of each user’s Personalization data. 10MB is usually given as a ballpark figure, but there’s no real substitute for testing. I’ve seen the total swell up towards 20-25MB when using App-V (particularly when the icons that App-V caches are up to 512KB in size), but it’s best to try and find out the projected figure for your environment as all enterprises are different. There’s also the question to be asked about how many archives of the data you will be keeping – by default it is set to five but can be changed to suit your requirements.
The live data would then be worked out by doing
Number of users x average size of Personalization data = space required for live data in MB
And the archive data can be worked out by doing
Number of users x number of archives to store (usually 5) x average size of Personalization data = space required for archives in MB
Then simply add the two figures together again to get the overall Personalization database size
Other factors
Transaction Logs – if using the Full Recovery model (which I’d recommend, particularly if the SQL mirroring or replication features are in use) the transaction log size is a crucial consideration. The log size will vary from one environment to another, but you need to ensure that the log file can expand to include the throughput of all data between backups. For example, if daily backups are taken, the log needs to be able to store an entire day‘s worth of data.
TempDB Size – the TempDB stores temporary tables and as such is somewhat similar to the transaction logs. If the TempDB is too small or is unable to grow sufficiently, the performance or stability of the database could be adversely affected.
TempDB sizing is difficult. Theoretically the entire contents of the database could be placed in the TempDB between backups. Therefore it needs to be placed on a volume that can allow the TempDB to auto-grow to the size of the database, with the ability to scale that storage utilization up or down based on a post-implementation bedding-in period.
High Availability – SQL replication and SQL mirroring are supported for both databases, as are SQL clusters. Clustered databases can be replicated, but mirrored ones cannot. At current time, SQL Server 2012’s AlwaysOn option (which is built around block-level replication, in the same way as Exchange uses DAGs) is not a supported option, but should be in the near future, which should eventually replace the current replication and mirroring options completely. (It is now supported!)
I will visit the resiliency options for the databases (and the web services too, incidentally) in more detail in a future post.
Share
