Monitoring NearPoint Index Movement using SQL

Here’s a quick SQL query to show the status of currently active mailbox index movement tasks:

select * from TaskStatus
where TaskName = 'IndexMove'
AND ProcessedCount = 0
AND FailedCount = 0

Note that the ProcessedCount will equal 0 for all currently active jobs and previously failed jobs. To list failed mailbox index relocation attempts, set the FailedCount = 1, e.g.,

select * from TaskStatus
where TaskName = 'IndexMove'
AND ProcessedCount = 0
AND FailedCount = 1

SQL Query to Identify Top 10 Largest NearPoint Mailbox Indices

Here’s a rather complicated SQL query to list the top 10 largest mailbox indices on your NearPoint server:

SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics
FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
AND LV.LogicalVolumeID=IL.LogicalVolumeID
ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC

To find the top 10 largest mailbox indices on a particular index volume, include the WHERE condition AND LV.SharePath LIKE '%INDEX1%' where INDEX1 is the share name of one of your index volumes:

SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics
FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
AND LV.LogicalVolumeID=IL.LogicalVolumeID
AND LV.SharePath LIKE '%INDEX1%'
ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC

Note that the mailbox index chunk size in contained the XML data stored in the IndexStastics column in the MimosaContext..IndexLocation table, e.g.,

<Root>
  <IndexState>0</IndexState>
  <CurrentChunkSize>786</CurrentChunkSize>
  <Asset AssetId=&quot;1001&quot;>
    <IndexStateUpdateTS>2011-12-16 18:19:50</IndexStateUpdateTS>
    <StartMessageItemId>1002</StartMessageItemId>
    <EndMessageItemId>34725997</EndMessageItemId>
    <StartRecordTS>2011-12-16 18:20:02</StartRecordTS>
    <EndRecordTS>2012-05-17 08:04:21</EndRecordTS>
    <StartReceivedDate>2011-03-08 16:51:39</StartReceivedDate>
    <EndReceivedDate>2012-05-17 07:28:59</EndReceivedDate>
  </Asset>
</Root>

To sort by the current chunk size, you have to use the XQUERY notation highlighted below:

SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics
FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
AND LV.LogicalVolumeID=IL.LogicalVolumeID
AND LV.SharePath LIKE '%INDEX1%'
ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC

For more information about working with XML in SQL, check out the following article on SQL Server Performance:

Practical Introduction to XML Manipulation Using XQuery in SQL Server

Recommended Boot Order for NearPoint

The following is taken from an Autonomy support article entitled “Recommended boot order for the NearPoint server and SQL server.”

Boot order is important. If the servers are re-booted out of order, NearPoint may experience a connection loss to the SQL databases or a Peer may be inadvertently promoted to Master.

Boot order for non-Grid environment (i.e. one NearPoint server)

  1. Power Down the NearPoint server
  2. Power Down the SQL server
  3. Start the SQL server
  4. (Once SQL is completely up) Start the NearPoint server

Boot order for Grid environment (i.e. more than one NearPoint server)

  1. Power Down the NearPoint Master server
  2. Power Down the NearPoint Peer servers
  3. Power Down the SQL server
  4. Start the SQL server
  5. (Once SQL is completely up) Start the NearPoint Peer servers
  6. Start the NearPoint Master server

It is somewhat counter-intuitive that the peer nodes in a grid environment should be powered on before the master node.

NearPoint eDiscovery Client Install Path

A quick way to determine the client installation path for the NearPoint eDiscovery client is to look for the "ClientInstallPath" key in the web.config file on your NearPoint eDiscovery server.

  1. Open the web.config file located in the C:Program Files (x86)MimosaeDiscoveryMsieDAService using your favorite text editor.
  2. Search for key="ClientInstallPath" in the section.
  3. The client installation URL is contained in the value parameter, e.g.,
<appSettings>
...
<add key=&quot;LatestRCOAppVersion&quot; value=&quot;4.2.2120.0&quot;></add>
<add key=&quot;ClientInstallPath&quot; value=&quot;http://archive.domain.com:8080/msiclientinstaller/setup/setup.exe&quot;></add>
<add key=&quot;CMOClientInstallPath&quot; value=&quot;http://archive.domain.com:8080/msiclientinstaller/setup/setup.exe&quot;></add>
<add key=&quot;RCOClientInstallPath&quot; value=&quot;http://archive.domain.com:8080/msiclientinstaller/setup/setup.exe&quot;></add>
<add key=&quot;NearPointHostName&quot; value=&quot;archive.domain.com&quot;></add>

NearPoint SQL Server Authentication vs. Windows Authentication

While helping a customer resolve an issue with their Nearpoint eDiscovery installation this morning, I learned that Autonomy recommends against configuring NearPoint to use same-named SQL logins and domain accounts. Apparently this can somehow confuse NearPoint when it tries to apply certain updates to the SQL databases. Autonomy recommends either using the SQL ‘sa’ account (always a bad idea) or creating/renaming the existing same-named SQL login account so that it no loner matches the NearPoint service account (the domain account). After making this change, you also need to run the dbreconfig.ps1 PowerShell script to update the DB user accounts on the master node. Prior to running that script, you also need to explicitly grant the SQL login dbo rights to the NearPoint databases under the User Mapping section of the login properties.

The following is a quote from an Autonomy support engineer regarding this issue:

Not all Stored Procedures (SPs) that NearPoint runs need to connect using SQL Authentication, however NearPoint needs to connect to the databases and run certain SPs using SQL Authentication. From what I know, most of these are during upgrades and rollup installs. If the DBuser account is named the same as any NT account, NearPoint’s attempt to connect will default to Windows Authentication and will fail. There’s certain changes to the databases that need to be done as a db owner using SQL Authentication.

This could explain why you’ve had apparently so many problems with upgrades and rollup installs in the past.

Useful NearPoint SQL Queries

The following SQL queries apply to NearPoint version 4.x:

General Mailbox Information

To determine the number of folders in a user’s mailbox archive:

select * from MessageFolder (nolock)
where MailboxID = 16

or if you just want the count

select COUNT(*) from MessageFolder (nolock)
where MailboxID = 16

To find information on a given mailbox by name:

select * from MimosaContext..Mailbox (nolock)
where MailboxName like '%clare%'

To show the number items awaiting processing, use the following query (Note that I’m not really sure about this.)

select mailboxid, COUNT(*)  from mimosaexchangeitem_1..messagequeryprep (nolock)
group by mailboxid

Mailbox Indices

Number of folders in a user’s mailbox:

use MimosaExchangeItem_1
go;
select * from MessageFolder (nolock)
where MailboxID = 16

Location of the Index chunk(s) for a given mailbox, e.g., MailboxId = ‘34’

use MimosaContext
go
select MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location, IL.IndexPath 
from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      AND MB.MailboxId = '34'

Mailboxes queued for re-indexing:

use MimosaContext
go;
Select * from IndexTaskQueue
/* TaskType=23:  Queue for re-index; this task removes the indices from the original location */
/* TaskType=25:  Rebuilds index in new location */
Where TaskType = '23'

Mailboxes being re-indexed:

use MimosaContext
go;
Select * from IndexTaskQueue
/* TaskType=23:  Queue for re-index; this task removes the indices from the original location */
/* TaskType=25:  Rebuilds index in new location */
Where TaskType = '25'

Mailbox indices by location, mailbox id, store, etc.

use MimosaContext
go;
select MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location, IL.IndexPath  from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)  where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      /*AND MailboxStoreName LIKE '%MAILNO04%'*/
      /*AND MailboxName like 'HR%'*/
      /*AND MB.MailboxId = '1122'*/
      /*AND IL.IndexRelativePath LIKE '%INDEX2%'*/
      /*AND LV.SharePath LIKE '\MIMNVT02IORINDEX-02%'*/
        AND LV.SharePath LIKE '\MIMNVT02INDEX%'

Queue mailboxes for re-indexing based on location, mailbox id, etc.

use MimosaContext
go
 -- Declare variables
DECLARE @MailboxID VARCHAR(150)
DECLARE @MailboxDB TABLE(MailboxID NVARCHAR(150), StorageGroupName NVARCHAR(100), MailboxStoreName NVARCHAR(100), MailboxName NVARCHAR(100), Location NVARCHAR(100))
INSERT INTO @MailboxDB (MailboxID, StorageGroupName, MailboxStoreName, MailboxName, Location)
SELECT TOP 38 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location 
from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      /*AND MailboxStoreName LIKE '%MAILNO04%'*/
      /*AND MailboxName like 'HR%'*/
      /*AND IL.IndexRelativePath LIKE '%INDEX2%'*/
      /*AND LV.SharePath LIKE '\MIMNVT02IORINDEX-02%'*/
      AND LV.SharePath LIKE '\MIMNVT02INDEX%'

-- Create a cursor for the databases on each ServerInstance (Inner Loop)
DECLARE dbCursor CURSOR FOR
SELECT MailboxID FROM @MailboxDB
OPEN dbCursor

FETCH NEXT FROM dbCursor
INTO @MailboxID

WHILE (@@FETCH_STATUS=0)
BEGIN
      SELECT @MailboxID

      exec msisp_AddIndexingtask @MailboxID,0,'','','CONSOLE',0,0,DEFAULT,DEFAULT,23,0

      -- Fetch Next Record
      FETCH NEXT FROM dbCursor
      INTO @MailboxID
END

To determine the index locations for a given mailbox (e.g., ‘AssetID=16’) use the following queries:

select * from MimosaContext..AssetIndexMap (nolock)
where AssetId = 16
 select * from MimosaContext..Indexlocation (nolock)
where MailboxId = 16

To identify mailbox indices which have been split into multiple chunks:

use MimosaContext
go
select MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location, IL.IndexPath  from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      AND IL.IndexRelativePath LIKE '%INDEX2%'

Re-index mailboxes consisting of more than one index chunk using the following script:

use MimosaContext
go

-- Declare variables
DECLARE @MailboxID VARCHAR(150)
DECLARE @MailboxDB TABLE(MailboxID NVARCHAR(150), StorageGroupName NVARCHAR(100), MailboxStoreName NVARCHAR(100), MailboxName NVARCHAR(100), Location NVARCHAR(100))

INSERT INTO @MailboxDB (MailboxID, StorageGroupName, MailboxStoreName, MailboxName, Location)
SELECT TOP 100 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location 
from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      AND IL.IndexRelativePath LIKE '%INDEX2%'

-- Create a cursor for the databases on each ServerInstance (Inner Loop)
DECLARE dbCursor CURSOR FOR
SELECT MailboxID FROM @MailboxDB
OPEN dbCursor

FETCH NEXT FROM dbCursor
INTO @MailboxID

WHILE (@@FETCH_STATUS=0)
BEGIN
      SELECT @MailboxID

      exec msisp_AddIndexingtask @MailboxID,0,'','','CONSOLE',0,0,DEFAULT,DEFAULT,23,0

      -- Fetch Next Record
      FETCH NEXT FROM dbCursor
      INTO @MailboxID
END

General

To extract XML code from a database field:

select cast(NPConfigDetails as XML) from MimosaContext..npconfiguration (nolock)

Fix for Mulitple Folders in a User’s Archived Mailbox

Confirm that there were multiple empty folders named “Inbox” in a single user’s archive.

–This is most likely due to archiving a mailbox with corruption. The folders were archived multiple times but the items were not.

Ran the following to clear the archived mailbox of empty folders:

--found mailbox ID of archived mailbox:
Select * from MimosaContext..Mailbox (nolock) Where MailboxName like '%Parriott%'

--mailboxid = 11

--changed databases to MimosaExchangeItem_1 and ran the following Stored Procedure:
exec msisp_DeleteEmptyFolders 802, 1
exec msisp_DeleteEmptyFolders MailboxID, 1
exec MimosaExchangeItem_1.dbo.msisp_DeleteEmptyFolders MailboxID, 1
exec msisp_DeleteEmptyFolders 11, 1

--&quot;11&quot; is the mailboxid and &quot;1&quot; is the item type &quot;folder&quot;. Changes to archived mailbox is immediate.