Archive Migration Sizing Queries
Contents
1. Revision History 2
2. Overview 3
3. NearPoint Archive Sizing Instructions 3
3.1. NP SQL QUERY 4
4. Enterprise Vault (EV) Archive Sizing Instructions 5
4.1. EV SQL database (SQL Query) 5
4.2. EV size by date range 6
4.3. Ev size by user with date range 7
4.4. EV sizing – Archive by user 8
5. EV.Cloud 8
6. EAS Archive Sizing Instructions 9
6.1. Query #1 -- Total Users in EAS over time: 9
6.2. Query #2 -- Total users in EAS currently disabled: 9
6.3. Query #3 -- Total users in EAS currently enabled: 9
6.4. Query #4 – Data Size Query 9
7. SourceOne Sizing Queries 10
7.1. SQL Query for Compressed and Uncompressed Values for Exchange Mailbox Types 10
7.2. SQL Query for Compressed and Uncompressed Values for ALL Archived Mailboxes 11
8. EmailExtender 12
8.1. SQL Query for Uncompressed Size – Exchange Mailboxes Only 12
9. MessageOne Archive sizing 13
10. Mx-Logic Archiving Sizing 14
1. Revision History
Date / Revision / Changes / Author2-7-17 / 1 / Creation / DR
2. Overview
To provide you with an accurate quotation for your email archive migration project, we require the total size of your current archive Each email archive system has its own individual sizing instructions. Follow the instructions in the appropriate section from this document.
3. NearPoint Archive Sizing Instructions
To determine the total uncompressed size of the NearPoint archive, please run the NearPoint Archive Summary Report.
For NearPoint version 3.x, open the NearPoint Administrator Console then go to: NearPoint Server > Reports > Summary Reports > Summary Extraction Summary. Copy the Summary Section.
For NearPoint version 4.x, open the NearPoint Administrator Console. Go to: NearPoint Server > Reports > Summary Reports > Archival Summary. Copy the Summary Section.
NOTE: To determine your installed version of NearPoint, open the NearPoint Admin Console and go to Help > About
Example Report:
Archival Summary Report
Summary Report is current as of 9/30/2015 11:01 PM
Total Exchange Server Count 1
Storage Group Count 5
Mailbox Store Count 5
Total Message Count 14472485
Total Message Size 2.57 TB
Total Unique Body Count 5768751
Total Unique Body Size 94.33 GB
Total Unique Attachment Count 1318582
Total Unique Attachment Size 470.77 GB
Total Single Instanced Body Count Savings 7120835
Total Single Instanced Body Size Savings 133.04 GB
Total Single Instanced Attachment Count Savings 6648735
Total Single Instanced Attachment Size Savings 1.58 TB
Due to the way the Archive Summary report calculates the total message size, the results are consistently 15% LESS than the actual amount of data that you can expect to extract from the IOR. Take the value of the Total Message Size item in the report and increase it by 15% (multiply by 1.15) to get the size you need.
Total Message Size = 2.57 TB * 1.15 = 2.9 TB
3.1. NP SQL QUERY
USE MimosaContext
------
CREATE TABLE #btTemp (UncompressedSize BIGINT, ItemsCount BIGINT)
DECLARE @SQLServer VARCHAR(50)
DECLARE @DatabaseName VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
------
DECLARE @btCursor CURSOR
SET @btCursor = CURSOR FAST_FORWARD
FOR
--get item databases
SELECT
db.DBServer
,db.DBName
FROM DbMap db WITH (NOLOCK)
WHERE DBName LIKE 'MimosaExchangeItem%'
------
OPEN @btCursor
FETCH NEXT FROM @btCursor
INTO @SQLServer,@DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--get uncompressed size for each vault store
SET @sql = 'SELECT
SUM(CAST(mi.MessageSize AS BIGINT))
, COUNT_BIG(*)
FROM ' + @DatabaseName + '.dbo.MessageItem mi WITH (NOLOCK)
JOIN ' + @DatabaseName + '.dbo.MessageItemEntry mie WITH (NOLOCK) ON MIE.MessageItemId = MI.MessageItemID
WHERE mie.MessageCommitted = 1 AND (mi.UserDeletedDate IS NULL OR (mi.UserDeletedDate IS NOT NULL AND mi.Disposed = 0))'
--store the results in the temp table
INSERT INTO #btTemp
EXEC sp_ExecuteSQL @sql
--move to next result
FETCH NEXT FROM @btCursor
INTO @SQLServer,@DatabaseName
END
------
CLOSE @btCursor
DEALLOCATE @btCursor
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(ItemsCount) ItemsCount
FROM #btTemp
--drop temp table
DROP TABLE #btTemp
4. Enterprise Vault (EV) Archive Sizing Instructions
4.1. EV size by date range
Replace each highlighted value
*/
DECLARE @BeginDate VARCHAR (20)
SELECT @BeginDate = '2010-01-01 00:00'-- Put in the start date and time
DECLARE @EndDate VARCHAR (20)
SELECT @EndDate = '2015-01-01 23:59' -- Put in the end date and time
DECLARE @size INT
SELECT @size = '1024' -- MB 1024 GB 1024/1024
SELECT Report.[Number of Items] AS 'Number of Items',
SUM ([Size of the items(MB)]) AS 'Size of the items(MB)'
FROM (
SELECT COUNT(*)
AS "Number of Items",sum (cast(itemsize as bigint))/ @size AS "Size of the items(MB)",
MIN(ArchivedDate) AS "Start Date",MAX(ArchivedDate) AS "End Date"
FROM YOURvault store DATABASE HERE.dbo.Saveset
WHERE ArchivedDate >@BeginDate and ArchivedDate < @EndDate
UNION
SELECT COUNT(*)
AS "Number of Items",sum (cast(itemsize as bigint))/ @size AS "Size of the items(MB)",
MIN(ArchivedDate) AS "Start Date",MAX(ArchivedDate) AS "End Date"
FROM YOUR Vault store DATABASE HERE.dbo.Saveset
WHERE ArchivedDate >@BeginDate and ArchivedDate < @EndDate
) AS Report
GROUP BY Report.[Number of Items]
4.2. Ev size by user with date range
Replace each highlighted value.
Use EnterpriseVaultDirectory
SELECT A.ArchiveName,
COUNT(S.IdTransaction) "Num. Items Archived",
SUM(cast(s.itemsize as bigint))/1024 "Archived Items Size (MB)",
SUM(cast(SP.OriginalSize as bigint))/1024/1024 "Original Email Size (MB)"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
ENTER YOUR DATABASE IN.dbo.ArchivePoint AP,
ENTER YOUR DATABASE IN.dbo.Saveset S,
ENTER YOUR DATABASE IN.dbo.SavesetProperty SP
WHERE S.SavesetIdentity = SP.SavesetIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND A.ArchiveName = A.ArchiveName
AND S.iddatetime BETWEEN '5/1/2016' AND '10/24/16'
GROUP BY A.ArchiveName
4.3. EV sizing – Archive by user
Use EnterpriseVaultDirectory
SELECT A.ArchiveName,
COUNT(S.IdTransaction) "Num. Items Archived",
SUM(cast(s.itemsize as bigint))/1024 "Archived Items Size (MB)",
SUM(cast(SP.OriginalSize as bigint))/1024/1024 "Original Email Size (MB)"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
REPLACE WITH YOUR DATABASE.dbo.ArchivePoint AP,
REPLACE WITH YOUR DATABASE.dbo.Saveset S,
REPLACE WITH YOUR DATABASE.dbo.SavesetProperty SP
WHERE S.SavesetIdentity = SP.SavesetIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND A.ArchiveName = A.ArchiveName
GROUP BY A.ArchiveName
4.4. EV SQL database (SQL Query)
USE EnterpriseVaultDirectory
------
CREATE TABLE #btTemp (UncompressedSize BIGINT, CompressedSize BIGINT, ItemsCount BIGINT)
DECLARE @SQLServer VARCHAR(50)
DECLARE @DatabaseName VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
------
DECLARE @btCursor CURSOR
SET @btCursor = CURSOR FAST_FORWARD
FOR
--get vault stores
SELECT
vse.SQLServer
,vse.DatabaseName
FROM VaultStoreEntry vse WITH (NOLOCK)
------
OPEN @btCursor
FETCH NEXT FROM @btCursor
INTO @SQLServer,@DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--remove port number from SQLServer for this query
IF (CHARINDEX(',', @SQLServer) 0)
SET @SQLServer = SUBSTRING(@SQLServer, 0, CHARINDEX(',', @SQLServer))
--get uncompressed size for each vault store
SET @sql = 'SELECT
(SELECT SUM(CAST(sp.OriginalSize AS BIGINT)) UncompressedSize
FROM [' + @SQLServer + '].[' + @DatabaseName + '].[dbo].[SavesetProperty] sp WITH (NOLOCK))
, SUM(CAST(ap.ArchivedItemsSize AS BIGINT)) CompressedSize
, SUM(CAST(ap.ArchivedItems AS BIGINT)) ItemsCount
FROM [' + @SQLServer + '].[' + @DatabaseName + '].[dbo].[ArchivePoint] ap WITH (NOLOCK)'
--store the results in the temp table
INSERT INTO #btTemp
EXEC sp_ExecuteSQL @sql
--move to next result
FETCH NEXT FROM @btCursor
INTO @SQLServer,@DatabaseName
END
------
CLOSE @btCursor
DEALLOCATE @btCursor
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(CONVERT(DECIMAL, CompressedSize))/(1024*1024) CompressdSizeGB
, SUM(ItemsCount) ItemsCount
FROM #btTemp
--drop temp table
DROP TABLE #btTemp
5. EV.Cloud
Log into the EV.Cloud Management Studio and access the Archive Overview Section
Click the “View Full Archive Usage Report” link
Under the 5-Month Rolling Mail Volume you will find the below report.
6. EAS Archive Sizing Instructions
Below are queries to run to size an EAS environment. Run these queries against each instance of the EAS database in SQL Management Studio.
6.1. Query #1 -- Total Users in EAS over time:
Select count (*) from users with (NOLOCK)
6.2. Query #2 -- Total users in EAS currently disabled:
Select count (*) from users where easstatus0
6.3. Query #3 -- Total users in EAS currently enabled:
Select count (*) from users where easstatus=0
6.4. Query #4 – Data Size Query
USE EAS
------
CREATE TABLE #btTemp (UncompressedSize BIGINT, CompressedSize BIGINT, ItemsCount BIGINT)
INSERT INTO #btTemp
SELECT
SUM(CAST(pl.UNCOMPRESSEDSIZE AS BIGINT))
, SUM(CAST(pl.COMPRESSEDSIZE AS BIGINT))
, COUNT_BIG(*)
FROM REFER r WITH (NOLOCK)
JOIN PROFILE p WITH (NOLOCK) ON p.MSGID=r.MSGID
JOIN PROFILELOCATION pl WITH (NOLOCK) ON pl.MSGID=p.MSGID
------
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(CONVERT(DECIMAL, CompressedSize))/(1024*1024*1024) CompressdSizeGB
, SUM(CAST(ItemsCount AS BIGINT)) ItemsCount
FROM #btTemp
--drop temp table
DROP TABLE #btTemp
7. SourceOne Sizing Queries
7.1. SQL Query for Compressed and Uncompressed Values for Exchange Mailbox Types
------
CREATE TABLE #btTemp (UncompressedSize BIGINT, CompressedSize BIGINT, ItemsCount BIGINT)
INSERT INTO #btTemp
SELECT
SUM(CAST(m.MsgSize AS BIGINT)) UncompressedSize
, (SELECT
SUM(CAST(fm.Compressed AS BIGINT))
FROM (SELECT
MessageID
, MIN(CompressedMsgSize) Compressed
FROM FolderMessage WITH (NOLOCK)
GROUP BY MessageID
) fm
) CompressedSize
, COUNT_BIG(*) ItemsCount
FROM Route r WITH (NOLOCK)
JOIN Message m WITH (NOLOCK) ON m.MessageID=r.MessageID
JOIN EmailAddress e WITH (NOLOCK) ON e.EmailId=r.EmailId
WHERE e.Type=1
------
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(CONVERT(DECIMAL, CompressedSize))/(1024*1024*1024) CompressdSizeGB
, SUM(CAST(ItemsCount AS BIGINT)) ItemsCount
FROM #btTemp
--drop temp table
DROP TABLE #btTemp
7.2. SQL Query for Compressed and Uncompressed Values for ALL Archived Mailboxes
USE ES1Archive
------
CREATE TABLE #a360Temp (UncompressedSize BIGINT, CompressedSize BIGINT, ItemsCount BIGINT)
INSERT INTO #a360Temp
SELECT
SUM(CAST(m.MsgSize AS BIGINT)) UncompressedSize
, (SELECT
SUM(CAST(fm.Compressed AS BIGINT))
FROM (SELECT
MessageID
, MIN(CompressedMsgSize) Compressed
FROM FolderMessage WITH (NOLOCK)
GROUP BY MessageID
) fm
) CompressedSize
, COUNT_BIG(*) ItemsCount
FROM Route r WITH (NOLOCK)
JOIN Message m WITH (NOLOCK) ON m.MessageID=r.MessageID
JOIN EmailAddress e WITH (NOLOCK) ON e.EmailId=r.EmailId
------
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(CONVERT(DECIMAL, CompressedSize))/(1024*1024*1024) CompressdSizeGB
, SUM(CAST(ItemsCount AS BIGINT)) ItemsCount
FROM #a360Temp
--drop temp table
DROP TABLE #a360Temp
8. EmailExtender
8.1. SQL Query for Uncompressed Size – Exchange Mailboxes Only
USE EmailXtender
------
CREATE TABLE #a360Temp (UncompressedSize BIGINT, ItemsCount BIGINT)
INSERT INTO #a360Temp
SELECT
SUM(CAST(m.MsgSize AS BIGINT)) ItemsUncompressedSize
, COUNT_BIG(*) cntExItemsAll
FROM Route r WITH (NOLOCK)
JOIN Message m WITH (NOLOCK) ON m.MD5HashKey=r.MD5HashKey
WHERE r.EmailId IN (
SELECT
ea.EmailId
FROM EmailAddress ea WITH (NOLOCK)
WHERE SUBSTRING(ea.[EmailAddress], 0, CHARINDEX(':"', ea.[EmailAddress]))='EX'
)
------
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(ItemsCount) ItemsCount
FROM #a360Temp
--drop temp table
DROP TABLE #a360Temp
SQL Query for Uncompressed Size – ALL Archived Mailboxes
USE EmailXtender
------
CREATE TABLE #a360Temp (UncompressedSize BIGINT, ItemsCount BIGINT)
INSERT INTO #a360Temp
SELECT
SUM(CAST(m.MsgSize AS BIGINT)) ItemsUncompressedSize
, COUNT_BIG(*) cntExItemsAll
FROM Route r WITH (NOLOCK)
JOIN Message m WITH (NOLOCK) ON m.MD5HashKey=r.MD5HashKey
------
--Display the results
SELECT
SUM(CONVERT(DECIMAL, UncompressedSize))/(1024*1024*1024) UncompressdSizeGB
, SUM(ItemsCount) ItemsCount
FROM #a360Temp
--drop temp table
DROP TABLE #a360Temp
9. MessageOne Archive sizing
The storage report is found by selecting the Admin menu, then Historical Mail and then the Storage Report options on the left-hand menu. This will detail the number of messages and their associated volume.
10. Mx-Logic Archiving Sizing
The storage report can be produced by logging into the McAfee Control Console and selecting the Overview tab then analyzing the Email Archiving Summary report. The last piece of information showing in this report is the Total Storage Size, which represents the total amount of archived data within the Mx-Logic environment.
BitTitan Page 15