EV9 SQL query to determin storage and indexserver used

[GertjanA]

Hello SQL guru's,

In preparation for restructuring archives, I'd like to find out the following:

Archive information (Name and displayName), which storageserver, and which index-server (not the service, the actual servernames will do)

I have seen this:

https://www-secure.symantec.com/connect/forums/which-archivesmailboxes-are-associated-which-storage-...

I would like to have an overview of the archives and which storage server and which index server is being used. Reason for asking is that in the early days of this implementation, several archives have their storage being done on EV001, and indexing on EV002. I want to allign this (by moving the indexes), to remove as much as possible the outgoing queues in MSMQ. The environment will be upgraded to EV10 in the coming 6 months, but we will not use Index server groups.

As I am not an SQL guru, I am not totally sure on how to adjust the above mentioned query to show which server the indexing is being done.

Help is appreciated!

Gertjan.

[Pradeep-Papnai · Accepted solution]

Try the query below.

Select aa.ArchiveName, vse.VaultStoreName, ce.ComputerName as StorageServer, CE1.ComputerName as IndexingServer  
from Archive aa
 inner join IndexVolume IV
on aa.RootIdentity = iv.RootIdentity
 Inner join IndexRootPathEntry IRP
On iv.IndexRootPathEntryId = IRP.IndexRootPathEntryId
 Inner join IndexingServiceEntry ISE
on ise.ServiceEntryId = irp.IndexServiceEntryId
 inner join ComputerEntry CE1
on ce1.ComputerEntryId= ISE.ComputerEntryId
 inner join VaultStoreEntry vse
on VSE.VaultStoreEntryId = aa.VaultStoreEntryId
 inner join StorageServiceEntry sse
on sse.ServiceEntryId = vse.StorageServiceEntryId
 inner join ComputerEntry CE
on ce.ComputerEntryId = sse.ComputerEntryId

[Pradeep-Papnai]

Above query was just created in my lab (9.0 SP4) tested with 2 servers, worth to mention this needs to be run again Directory database (just add 'use EnterprisevaultDirectory').

[GertjanA]

Thanks!

Works like a charm.

Being the non-sql-guru as I am, if I now want to show only those archives where ce.computername is not equal to ce1.computername....

Would that be something like:

Where ce.computername <> ce1.computername?

[Pradeep-Papnai]

Many thanks Gertjan, Just tested, you can use one of the following lines
 
where CE.ComputerName <> CE1.ComputerName ---(list all archives where Storage & Indexing server are not same.)
where CE.ComputerName <> 'EVserver' ---- (if 'EVserver' as storage server needs to exclude)
where CE1.ComputerName <> 'EVserver' --(if 'EVserver' as indexing server needs to exclude)

[GertjanA]

Thanks!