Design suggestion

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Design suggestion

Design suggestion

Rate Topic

Display Mode

Topic Options

Author

Message

TommyF

Posted Sunday, December 23, 2012 11:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 9:35 PM
Points: 125, Visits: 288

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

ScottPletcher

Posted Monday, December 24, 2012 9:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1399929

TommyF

Posted Wednesday, December 26, 2012 11:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 9:35 PM
Points: 125, Visits: 288

I like your suggestion. Thank you very much.

Post #1400319

« Prev Topic | Next Topic »

Permissions