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: Saturday, October 18, 2014 6:01 AM
Points: 131, Visits: 338
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 2,194, Visits: 3,304
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
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: Saturday, October 18, 2014 6:01 AM
Points: 131, Visits: 338
I like your suggestion. Thank you very much.
Post #1400319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse