Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

Enterprise vault SQL database Diagram

Created: 01 Aug 2013 • Updated: 16 Dec 2013 | 15 comments
sandeepk@jainamtech.com's picture
This issue has been solved. See solution.

HI,

I am using enterprise vault 9.0 for exchange archive. i want to know about SQL Database Diagram which is use in enterprise vault.

Operating Systems:

Comments 15 CommentsJump to latest comment

Rob.Wilcox's picture

This will help you get started, but the information you want isn't published - it would be like giving you the key to the bank vault :)

https://www-secure.symantec.com/connect/forums/enterprise-vault-8-sql-db

sandeepk@jainamtech.com's picture

Hi Rob,

Thank you for this information, but i want to more in details because of i saw there is lot of table in data base. Can you share table vise information like which data store in which table?

Rob.Wilcox's picture

Your question is a little bit broad.  You want me to go through *every* single table and explain what each contains? That's for the Directory database, vault store database, and fingerprint database?

..  sorry, but I think it's best to review the information on the link above, and then think of specific questions that you have.  Are there specific tables that are big, or contain no data, and you're curious about them?

sandeepk@jainamtech.com's picture

I want this information, because we have very limited SQL query to run on SQL server to gather user information, if I want run some query on SQL server for that I need Table information

Pradeep_Papnai's picture

As far as I know, There is no such link available publicly to know details about what each table of directory/VS database contain due to complexity of table relationship. With each verion / SP release we have minor or major changes in datbase design. We would happy to assist you if you have any specific requirment to know details about users, archive, task, server......etc.

If you are interested then please have a look query given in below forum.

http://www.symantec.com/connect/downloads/useful-s...

EV_Ajay's picture

Hi Sandeep,

It will be great if you share the output / result you are expecting from SQL query . After that it will be helpful to provide which table you require to get particular result.

 

Thanks,

Ajay

sandeepk@jainamtech.com's picture

Dear All,

Thank you very much for given me reply on my post.smiley

Friends some time it is very difficult to manage Client requirement, every time they think out of the boundary, I am talking about reporting, user history and audit purpose. For example below question

  1.  How many database & mailboxes are enabling in EV for journaling?
  2.  How many user’s mailbox enable and disable in last 3 months?
  3. Which mail restore & deleted by user that kind of information I want?
  4. How many time user mailbox quota hit for archiving in EV (as history)?
  5. How can i know the changes made by other person in enterprise vault server? For e.g. in policy and in setting
  6. Details about user PST migration report? For e.g. how many pst for each user mailbox has been migrated along with pst size and pst count, that kind of information I want?

Now tell me friends how can i get above information? And which query should I run on SQL server   

Pradeep_Papnai's picture

For most of your question can be answers if you enable EV auditing.

http://www.symantec.com/docs/TECH49054 (Auditing for Enterprise Vault for Microsoft Exchange)

After enabling the auditing, you need to define catergories in the properties of vault server.

Below query would give you details about the Journal mailbox name those are added in target
 
USE EnterpriseVaultDirectory
sELECT * FROM ExchangeMailboxEntry WHERE ExchangeMbxType = 2

GabeV's picture

The EV databases have lot of information. You need to know how EV works internally to understand every single table and where EV stores the data. Obviously, this information is not public. However, you can install EV reporting or enable EV auditing as EV-Counselor suggested. In addition, there are several technotes with SQL queries to pull data from the EV DBs. If you need a specific set of data for your customers, we can try to assist you. I will try to answer your questions:

  1. How many database & mailboxes are enabling in EV for journaling?

Use SQL management studio to see the EV DBs: EnterpriseVaultDirectory, DBs starting with EVVS^ and Monitoring database.

  1. How many user’s mailbox enable and disable in last 3 months?

I am not sure if you can get this information, but you can look at the ExchangeMailboxEntry table.

  1. Which mail restore & deleted by user that kind of information I want?

There is a report in EV reporting that can provide you this information.

  1. How many time user mailbox quota hit for archiving in EV (as history)?

You need to review auditing options under EV server properties.

  1. How can i know the changes made by other person in enterprise vault server? For e.g. in policy and in setting

You need to review auditing options under EV server properties.

  1. Details about user PST migration report? For e.g. how many pst for each user mailbox has been migrated along with pst size and pst count, that kind of information I want?

For PST migration, EV creates a report under the Enterprise Vault installation directory > Reports.

If you need a specific SQL query, I suggest you to explore reporting and auditing first. If not, you can post the question here or just open a ticket with support.

I hope this helps.

“Success is not final, failure is not fatal: it is the courage to continue that counts.”–Winston Churchill

Pradeep_Papnai's picture

Hi Sandeep,

Advance monitoring options will also give you many ways for monitoring/reports. Tech Note http://www.symantec.com/docs/HOWTO74545 (Advanced Strategies for Monitoring Enterprise Vault)

As an example PST miration completes with event 6741 (incomplete 6742), you can setup rules to send email to admin if PST migration complete or remain uncomplete.

Regards
EV-C

GabeV's picture

Hi Sandeep,

Any updates on this? Do you need more assistance regarding this topic?

“Success is not final, failure is not fatal: it is the courage to continue that counts.”–Winston Churchill

JesusWept3's picture

Btw, you know you can generate your own diagrams yeah?
click new diagrams, add the tables and you'll be good
the diagrams will link automatically because of the Foreign Key Relationships

I added a couple myself and it was just fine, cluttered and ugly but  its not going to be anything else

SQL Diagram.jpg

 

There are some relationships that are missing obviously
Like how Root.VaultEntryId maps to ExchangeMailboxEntry.DefaultVaultId

Just be sure not to add your own relationships as it would definitely take you out of a supported environment and would most likely cause issues

Dnaik79's picture

Hi Sandeep,

As it is better to create a scope with customer and deside what kind of report they required daily/monthly/yearly basis and just sync up with your SQL admin and do the required custmization from SQl database.Also you grab the more info if you enbaled EV audit from Ev application server.smiley

 

 

sandeepk@jainamtech.com's picture

 

This information is very helpful for all engineers

Thank you guys….smileysmiley.

 

 

GabeV's picture

You're very welcome

“Success is not final, failure is not fatal: it is the courage to continue that counts.”–Winston Churchill