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, July 4, 2015 11:10 PM
Points: 132, Visits: 371
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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 3,050, Visits: 4,673
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
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, July 4, 2015 11:10 PM
Points: 132, Visits: 371
I like your suggestion. Thank you very much.
Post #1400319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse