August 31, 2004 at 7:55 pm
Hi, I am reading windows media server log files from 4+ servers to my central SQL server. This DB serves a web based statistic sites that reports on the log data.
The db is updated once a day in a one hour window. Is there any benefit to switching the db in and out of readonly mode? Is that even possible from within t-sql?
Does anybody have an strategies for dealing with this kind of daily updated db?
Thanks
Dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
September 3, 2004 at 8:00 am
This was removed by the editor as SPAM
September 8, 2004 at 12:54 am
Here are few scenarios and suggestions.
1. Is the website performing only log reporting? : If the web site is only performing log reporting, then I suppose there is no need for updates in the database. Hence, data can be appended parallely while the site is operational. Preferable is to set the transaction isolation level to "read committed" in this case as the user will not see dirty data while the inserts are happening (however performance will be decreased slightly than read-uncommitted). If updates are required, then please see the points below
2. Is a staging database creation possible ? : A Staging database can be created and all the updates that are to be done on that day can be batched out in that database. It would even be good if you can seperate out the inserts, updates and deletes seperately. Dunring the one hour window, you can complete the transactions and make the server up-to-datetime.
3. Is the site accessed during the one-hour window? : If the site can be placed "under maintenance", then we can do a full batch processing to update the data into the tables.
If the site needs to be operational during the one hour window, then, either the inserts and updates,deletes are to be done as seperate batches or a seperate strategy needs to be worked out, but more information is required.
4. Placing the database in read-only mode : In this scenario where there is only reporting done, I dont think database needs to be put in read-only mode. (anyway for update you need to get the database in operational mode which will be effort consuming)
5. Another idea : you can have two databases (if you can afford resources). 1=dbX, 2=dbY. The website can be pointed to dbX which has the data for today. Restore the backup of dbX as dbY database. Perform the ETL for getting the data updated in dbY. Backup datbase dbY. During the one-hour window re-configure the web to point to dbY. Now do the same process for dbX as we have done today. dbX will be used tomorrow.
6. If the reporing is html based plain reports you can also consider dumping the reports and make the use see the reports. For OLAP reporting however this will not work out.
All the best.
Cheers-Gan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply