10-14-2013 01:26 AM
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:
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.
Solved! Go to Solution.
10-14-2013 03:56 AM
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
10-14-2013 03:56 AM
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
10-14-2013 04:08 AM
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').
10-14-2013 04:30 AM
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?
10-14-2013 05:08 AM
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)
10-14-2013 05:41 AM
Thanks!