SEP SQL database questions.

[Migration User]

3 actually:

1. what EXACTLY is kept in the SQL database SEM uses? Data only, or other items -

2. what determines the size of the SQL database SEM uses and what is typical for an entity with 28 servers, 350 computers, using packages to update/upgrade groups and 12 virus defs on hand.

3. is there anything that is recommended to keep the size in check?

Is 3 gig out of line?

(oops, sorry, I guess that's 4+ questions!)

[Migration User]

 Hope this helps a little

Planning for Microsoft SQL database creation and management with Symantec Endpoint Protection 11.x
http://service1.symantec.com/SUPPORT/ent-security.nsf/docid/2008063014073748

[Rafeeq]

Have you checked the dbtool for calculating the size ? I think it applies to Embedded and sQL too..
https://www-secure.symantec.com/connect/forums/resource-requirements-endpoint-mgmt-console-real-world

I think its all SEPM which uses the DB, clients just post logs and no query to DB.

[Migration User]

our SQL person here is REALLY griping about the size of our database for 350 users and 28 servers, just under 3 gig.
From what I see, we're lucky, but he's totally convinced this is "outragous" and "must be fixed" by some means.
So I have to PROVE that this is correct, can't just say "because I said so".  I need hard numbers.........a note from my Doctor, note from Mom, the Pope and President O to prove it.

[Rafeeq]

If you have checked that excel sheet, the major difference would come when you change the hearbeat interval. the default is 5 but if you change that to 15 in that excel sheet DB size would decrease. I had this same question before and prachand had the answer for it.

Let me know if this discussion was helpful.

https://www-secure.symantec.com/connect/forums/heart-beat

[Migration User]

You could always give him the installation_guide.pdf file.  The RU5 guide, page 26 says that the system requirements is 4 gigs for the server itself and 4 gigs for the database.

As far as what it contains, it has a bunch of stuff, including, but not limited to:

-installation packages
-virus definitions
-policies
-log information
-alerts

There's a ton of stuff in the database.  From the standpoint of "this should only contain logs", then certainly, 4 gigs would be rather excessive.  However, knowing what all we have in it...it's really not.

[Migration User]

 A good portion is the logging data that is captured which can include a lot of things like alerts, heartbeats, and more...

That said, tell your DBA to stop meddling in security as the activity needed for a security database that does logging is much different from a transactional CRM or sales order tool.

[Migration User]

You can refer to the database schema if you want the exact and detail information on what is stored in the database.


http://service1.symantec.com/SUPPORT/ent-security.nsf/docid/2008063014073748


Please go through this in the above link  ,it will ans most of the questions

SQL Database Schema Information:
Please refer to the following "DB_Schema_Ref_Guide.pdf" for the database schema information.



Database Planning:
Please refer to the following "SEPM Estimation of Database Storage.xls" for information on projected database sizes based on management volume.

 "Symantec Endpoint Protection Database Sizing Tool.xls" is a non-supported tool provided for customer convenience to assist in estimating database size.

[Migration User]

Thanks for all the replies guys/gals - but I have to wonder if it's not jus the size he's upset with, but, well, I'll post part of his message here, as I think he's also looking for some "clean-up" tool or procedure, etc., too.  I think he's used to having to manually clean up after applications, deleting old records and all.
I personally don't think there is anything needed - but I'm not a SQL person, and know little about it. If he's comparing SEP database to others from the past, or those for "home-brew" programs, then he's got a point, but maybe someone here can tell me - is he right on this? 
Please read on........... this is a message I got - old records? I thought that was taken care of by SEM?
---------------------------------------------------------------

I’ve yet to see a database that can’t be cleaned up.

Old records are always a part of  a database and need removal.

I’d check and see what they have to say about that.

------------------------------------------------------------------

You know - pressure to find a solution to a problem I don't think exists - but I have to "prove it" and proving a negative can be tough.

[Migration User]

 The DBUnload tool was used back in MR2 when databases used to go upto 100gb
http://service1.symantec.com/SUPPORT/ent-security.nsf/docid/2008022616103648

[Migration User]

Is this no longer needed?
How is the dbase cleaed up now? SEM internally manage old data, compression, clean-up, etc.
I'll take a look and see what that doc says.......

[Migration User]

600 locks?
Our DBA says as of this morning when he checked, SEM had 600 locks in place and with the speed of our SQL server, that should never be necessary..........

Can someone explain this please?

[Migration User]

Our DBA also says that our SEP RU5 servers are sending 1.5MB of data to the DB every 15 minutes with no clients connected. He's also less than impressed with the potential DB size and the DB Excel spreadsheet is the best way to estimate the size, but locks are a normal part of SQL.
If you are talking deadlocks then that's a completely different story and is BAD.

Depending on your heartbeat and also how far back you need to generate reports for really dictates the DB size as well. Ours is currently scoped at 10GB from the tool but will be closer to 18GB in real life - and will only allow 10 days of reporting.

[Migration User]

Locks - 600 - what is SEP, or rather, SEP/M doing that it has 600 locks at a given time?
IS there THAT much activity?
We've only got 2 (two) SEP management servers and roughly 300-325 computers "on" at a given time.
We're small, yet this is big.........
Wondering how many changes can be taking place at any given point in time that requires 600 locks?

[Migration User]

Depends on your heartbeat interval - default is 2 minutes? multiplied by 300 computers.
If SEP saves it all up and sends it to the DB  every 15 minutes in a block like ours does then you have potentially thousands of events being written to the tables at the same time.

Your DBA should be able to tell you how regularly SEP sends updates to the DB.

[Migration User]

As I recall default is 5, either way, ours is set to 7 minutes - so not that frequent (I can't see a huge need for closer heartbeats personally).

[Migration User]

Just checked - our DB is around 15Gb in size.
We have c1000 machines with SEP installed.
Clients check in with server every 20 minutes.
Right now, I cannot remember how mant VDefs we keep, but we have increased the log retention, so that could account for the additioal size.
At time of posting (23:00 here) we only have 8 locks.

One thing I've never understood.... a trace on the DB shows the SEP application issuing a 'Select count(*) from CONNECTION_TEST' approximately 20-30 times a second.... (yes, 20-30 per second, even at this time of night) - how often does it need to be sure it is connected???

[Migration User]

SEP is hammering on our big SQL server pretty hard, and 600 locks does seem to be a crazy number.
Might account for the console being VERY VERY VERY (can't put in enough VERY's here!) slow........
Takes a minute or two to change tabs, for example. - that's my concern.
His concern is SEM hammering away with 600 locks on the database. That seems crazy to me....

[Migration User]

Checked mine again mid day - still only 8 locks.

Not sure it affects this, but wouldn't be surprised if it does... Are your clients in Push or Pull mode?

[Migration User]

Pull mode, 7 minute intervals, 10 minute randomization.......

[Migration User]

Similar to us - we use pull mode 20 min intervals with 5 min random.
Was just wondering if you were in Push mode, as I could see how that could make differences to DB activity.

From my experience, SEP certainly has a lot of DB activity - my SQL server handles 37 different DB's, some are enormous (measured in tens of Gb) - they include DB's such as all our internet activity monitoring, all software license auditing, enterprise vault store, call logging db, etc - even with all that, when doing a trace on all activity on the DB, SEP makes up at least 50% of all the issued queries/commands (with the 20-30 "select count(*) from connection_test" a second I mentioned earlier). Having said that, the DB server doesn't seem to be struggling, and I'm not overly concerned about it....