How should I keep my own logs?
-
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?
-
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?
-
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.
-
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.).???
-
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. -
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.
-
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