July 5, 2006 at 9:27 am
I have a problem scenario, which is as under. It will be great if someone can help me in comingup with a good strategy to follow, regarding the same.
(Using SQL Server 2005)
1) Have a database on SQL Server 2005 which holds almost 8-9 yrs of data.
2) An application which uses this database for its day to day activities is moved from ClientServer to Webbased system.
3) Chances likely that more than 1000 users located at different locations may hit the databases at a time and need to constantly update the database with info which needs to be available immediately at a central location.
4) Not to forget that this database is being used by many other users/ clients for generating various reports which takes hours. These reports need to pull the latest updates.
5) The database needs to be accessed by clients(say located at California/Boston/Virginia/Dallas/Denver etc) to update certain info which should be immediately available for people at another branch (using Client Server version of the same application, the Server resides here) constantly to access the same for Approving/Rejecting and then generate reports.
6) Do not want to try Replication/ Data Archiving for now.
Any suggestions as to how to keep the database performance intact while allowing various users to access the database for constant updations/ Report generation.
July 5, 2006 at 1:21 pm
Try the new SQL2005 table partitioning as a starting point but read up on backing up of the database first.
If your system is web based and .NET based then you could consider having your reporting system in multiple layers.
A .NET2 WebService can run stored procedures asynchronously and you can also cache data on the webserver thereby taking the load off your SQL Server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply