Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Design suggestion Expand / Collapse
Author
Message
Posted Sunday, December 23, 2012 11:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 11:12 PM
Points: 129, Visits: 327
Hi,

I have to keep in a login history table the user's operating system / operating system version / browser / browser version / device type (mobile/pc etc) / device.
In case there are around a million entries a month and I have to keep two years of data,
should I use dictionary tables or should I keep the full strings descriptions in the login history table?

Thank you.
Post #1399809
Posted Monday, December 24, 2012 9:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 1,959, Visits: 2,893
Let's assume you can get all that information.

You have conflicting needs: you don't want to slow down the login process, but you also don't want to store full string descriptions for everything for the entire history

You could consider a two-level system: the initial logging captures everything with full strings. Then another process periodically pulls that data into a permanent history table, assigning identifiers in place of strings as required. Then the initial log data is deleted.

This allows you to have a minimal effect on logins but still save space and enhance analysis by assigning codes later.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1399929
Posted Wednesday, December 26, 2012 11:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 11:12 PM
Points: 129, Visits: 327
I like your suggestion. Thank you very much.
Post #1400319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse