How to verify Legal Holds are in sync between Discovery Accelerator and Enterprise Vault Stores.

Synchronization between Discovery Accelerator and Vault Store databases is a one-to-one relationship. Meaning, for every item 'On Hold' in a Discovery case, there will be a corresponding 'Hold' of that item in one of the Vault Stores.  
 If there is a concern the databases might be out of synchronization, provided below are two SQL queries that can be run in order to validate the databases are synchronized.  The numbers produced by the two queries should match.  If they do not match, there is an issue with the synchronization between the databases.  Contact Veritas Technical Support for assistance.

Note: There is an additional column in the second query that gives the total of items that failed to be placed on hold.  The most common reason for items failing to be placed on hold is the item was deleted prior to the case being put on hold.  



SCRIPT 1:   Determines the summary count of items that are currently on Legal Hold within the Vault Stores


Important Notes:  If crossing multiple SQL servers, interconnect the Sql servers by running the following command: EXECUTE sp_addlinkedserver SQLServerName
                           If using an Instance, enclose the Sql server name \ instance with brackets.   Example:  [SQLServerName\Instance1].EVVaultStore.dbo.tablename

Instructions:
1.  Replace the SQLServerName with the with the name of the appropriate SQL server name hosting the EV Vault Store.

2.  Replace the EVVaultStoreXXX with the appropriate Vault Store database name.  

3.  The script below has been written for 7 Vault Stores.  Remove or Add  SELECT statement lines to match the environment.

4.  Make sure the first SELECT has 2 open parenthesis (( and the last SELECT has 2 closing parenthesis )) without the + sign.

5.  Using SQL Query Analyzer, copy the text below into a New Query window and execute the script.



DECLARE @Summary1 int
SET @Summary1 =
((SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore001.dbo.HoldSaveset) AS new_table) +
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore002.dbo.HoldSaveset) AS new_table) +
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore003.dbo.HoldSaveset) AS new_table) +
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore004.dbo.HoldSaveset) AS new_table) +
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore005.dbo.HoldSaveset) AS new_table) +
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore006.dbo.HoldSaveset) AS new_table) +
(SELECT COUNT(*) FROM (SELECT DISTINCT SavesetIdentity, HoldGroupIdentity FROM SQLServerName.EVVaultStore007.dbo.HoldSaveset) AS new_table))

SELECT @Summary1 AS 'Legal Hold Count for All Vault Stores across servers'






SCRIPT 2:   Determines the summary count of items that are currently on Legal Hold within the Discovery Accelerator Customer databases.


Important Notes:  If crossing multiple SQL servers, interconnect the Sql servers by running the following command: EXECUTE sp_addlinkedserver SQLServerName
                           If using an Instance, enclose the Sql server name \ instance with brackets.   Example:  [SQLServerName\Instance1].EVVaultStore.dbo.tablename

Instructions:
1.  Replace the SQLServerName with the with the name of the appropriate SQL server name hosting the DA databases.

2.  Replace the EVBADiscoveryXX with the appropriate database name.  
Note:  There are two (2) locations per SELECT query that must be changed.

3. There are two (2) variables being calculated with this query.  EACH SET of DECLARE statements will have the Customer database names repeated.
Example: EVBADiscovery01 will be under the DECLARE @Summary2 int set of SELECT statements as well as under the DECLARE @Summary2a int set of SELECT statements.

4.  The script below has been written for 3 DA Customer databases.  Remove or Add  SELECT statement lines to match the environment.

5.  Make sure the first SELECT has 2 open parenthesis (( and the last SELECT has 2 closing parenthesis )) without the + sign.

6.  Using SQL Query Analyzer, copy the text below into a New Query window and execute the script.



DECLARE @Summary2 int
SET @Summary2 =
((SELECT COUNT(*) FROM
SQLServerName.EVBADiscovery01.dbo.tblintDiscoveredItems
tidi JOIN
SQLServerName.EVBADiscovery01.dbo.tblCase tc
ON tc.CaseID = tidi.CaseID
WHERE tidi.LegalStatus = 426
AND tc.LegalHoldState = 400) +

(SELECT COUNT(*) FROM
SQLServerName.EVBADiscovery02.dbo.tblintDiscoveredItems
tidi JOIN
SQLServerName.EVBADiscovery02.dbo.tblCase tc
ON tc.CaseID = tidi.CaseID
WHERE tidi.LegalStatus = 426
AND tc.LegalHoldState = 400) +

(SELECT COUNT(*) FROM
SQLServerName.EVBADiscovery03.dbo.tblintDiscoveredItems
tidi JOIN
SQLServerName.EVBADiscovery03.dbo.tblCase tc
ON tc.CaseID = tidi.CaseID
WHERE tidi.LegalStatus = 426
AND tc.LegalHoldState = 400))





DECLARE @Summary2a int
SET @Summary2a =
((SELECT COUNT(*) FROM
SQLServerName.EVBADiscovery01.dbo.tblintDiscoveredItems
tidi JOIN
SQLServerName.EVBADiscovery01.dbo.tblCase tc
ON tc.CaseID = tidi.CaseID
WHERE tidi.LegalStatus = 420
AND tidi.LegalHoldID IS NOT NULL) +

(SELECT COUNT(*) FROM
SQLServerName.EVBADiscovery02.dbo.tblintDiscoveredItems
tidi JOIN
SQLServerName.EVBADiscovery02.dbo.tblCase tc
ON tc.CaseID = tidi.CaseID
WHERE tidi.LegalStatus = 420
AND tidi.LegalHoldID IS NOT NULL) +

(SELECT COUNT(*) FROM
SQLServerName.EVBADiscovery03.dbo.tblintDiscoveredItems
tidi JOIN
SQLServerName.EVBADiscovery03.dbo.tblCase tc
ON tc.CaseID = tidi.CaseID
WHERE tidi.LegalStatus = 420
AND tidi.LegalHoldID IS NOT NULL))


SELECT @Summary2 AS 'Legal Hold Count for All DA Customer databases' , @Summary2a AS 'Items not on hold but have a Legal Hold ID'