Post reply

How should I keep my own logs?

  • January 9, 2007 at 7:40 am

    #122223

    I have a DB where a few tables keep track of every visit to my web site. It writes a record with Date, User Id, and other Keys.

    The thing is, this table have grown so much that I am beginning to think is better to put my user visits "log" in another DB. One reason is because I make a monthly backup and I really do not need to backup "that data". The other reason is because I am thinking about creating a DW, OLAP and BIRT stuff.

    I think there will be no problems of breaking my web apps if I rewrite queries using the full path indicating new localtion of moved tables as in

    SELECT * FROM SERVERNAME.DBNAME.dbo.TableName

    instead of

    SELECT * FROM dbo.TableName

    Also, for every direct call to the table I could create a view and name after the original table name. So, if a table is called TABLE1, once I move the table to a new Database I could write a view with name TABLE1.

    Can someone advice if I am one the right track? Or with objections to this approach?

  • January 9, 2007 at 8:20 am

    #682031

    I don't see why this would be a problem (aside from creating a bug in the web application).

    The real problem here is why do you backup only once a month?!?!?!

    Can you really afford to lose one month of data?

  • January 9, 2007 at 8:59 am

    #682043

    I did not express myself in full detail: I do backup daily.

    However, at beginning of every month I have to create a DB with last month data. Since my web site logs are not needed because they are precisely, logs, they are live, all the time collecting data..well..i do not need those in the newly created DB for every month. So, I thought, let's put these in another DB.

  • January 9, 2007 at 9:08 am

    #682046

    I wouldn't expect any problems with that approach (without fully knowing your system).

    Now to conclude... You only backup daily (I do too but with trans backups every 30 minutes >> we can afford that because there's not a lot of data inputed in a single day.).???

  • January 9, 2007 at 9:30 am

    #682062

    Those Logs I am referring to keep track of user, sessionid, session start and end date, visited page and/or page group...that kind of stuff.

    I intend to use them in Analisys Server. Web apps just capture data and write data to them...nothing else. But, it is hell when one tries to write a regular OLTP report.

    I hope web programmers do not complain they will have to rewrite queries hard coded in their pages. Those who use views and sprocs will be OK, I guess, after rewriting statements using full path like I mentioned in the beginning.

  • January 9, 2007 at 9:52 am

    #682073

    Shouldn't be any trouble... they'll just have to make a 2nd connection string and use that string in a few pages... if the app is well coded it won't be too hard to do.

  • January 9, 2007 at 11:49 am

    #682129

    I am assuming that this is a table/s in the current db.  You could also create views of the same name as the table which point to table/s in another db.  As long as you set up permissions on the tables in the new database that would require no changes to code.  I'll give a short example.

    original table: pubs.dbo.tblnames (firstname,lastname)

    new table: northwind.dbo.tblnames (firstname,lastname)

    new view: create pubs.dbo.tblnames as select * from northwind.dbo.tblnames

    sp_rename pubs.dbo.tblnames, tblnamesold

    create view here.

    Tom

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply