Monday, 1 July 2013

Exchange Server Sizing with Quest MessageStats

Anyone who works with any version of Microsoft Exchange Server will know how important it is to correctly size your infrastructure.  One of the key considerations are your mailbox user profiles - the number of users, how many messages they are sending and receiving each day, and the average size of those messages.  These numbers are usually entered into the Exchange Server sizing tool to help estimate the capacity of the server.

As I worked on our upgrade from Exchange 2010 to Exchange 2013 we needed to gather information about our users. Fortunately, we have Quest MessageStats gathering data on our Exchange environment. Unfortunately, there is no out-of-the-box report to provide the exact data, but I was able to get what I needed with a custom SQL script.

Below is the SQL query I came up with to get data from MessageStats in the format I needed. 
After a look through the MessageStats database structure I found that the database view 'V_MailboxActivity' had messages sent and received by every user for each day.  If I could aggregate this data I would be able to create the report I needed.
 
The SQL script has three parameters: start date, end date, and a 'grouping' factor which determines the breakpoints between profiles.

The output is a table with four columns:
'Server' - the name of the server or DAG
'Mailbox Profile' - the average number of messages sent and received per day by this group of mailboxes
'Number of Mailboxes' - the number of mailboxes in this group
'Average Message Size (KB)' - the average message size in this group

The output is grouped by server/DAG as this is often the base unit used for sizing.

DECLARE @StartDate AS Date
DECLARE @EndDate AS Date
DECLARE @ProfileGrouping AS Int

SET @StartDate = '2013-01-01'
SET @EndDate = '2013-03-30'
SET @ProfileGrouping = 50;

WITH T AS (SELECT 
      [Display Name]
      ,[Server]
      ,AVG([Total Messages]) AS [Average Messages per Day]
      ,CASE WHEN AVG([Total Messages]) = 0 THEN 0 ELSE CAST(SUM([Total Volume (KB)])/SUM([Total Messages]) AS INT) END AS [Average Message Size (KB)]
      ,(((CAST(AVG([Total Messages])/@ProfileGrouping AS INT) * @ProfileGrouping) + @ProfileGrouping)) AS [Mailbox Profile]
  FROM [Messagestats].[dbo].[V_MailboxActivity]
  WHERE [Date] BETWEEN @StartDate AND @EndDate
  GROUP BY [Display Name],[Server])
  
  SELECT T.[Server]
    ,T.[Mailbox Profile]
    ,COUNT(T.[Display Name]) AS [Number of Mailboxes]
    ,AVG(T.[Average Message Size (KB)]) AS [Average Message Size (KB)] 
  FROM T 
  GROUP BY [Server],[Mailbox Profile]
  ORDER BY [Server],[Mailbox Profile]
 
Typical output may look like this (although you will probably have many more rows, depending on the range of average number of messages sent per day):

ServerMailbox ProfileNumber of MailboxesAverage Message Size (KB)
EXDAG150142345
EXDAG110035855
EXDAG11506465
EXDAG25085652
EXDAG210056847
EXDAG21508954

Which you could then use as input to the Exchange Server sizing tool.

Mailbox configuration in the Exchange Server Role Calculator








This script is probably fairly niche - limited to admins who have MessageStats for Exchange Server reporting - but I thought it might be useful to share.

- Ben

1 comment: