March 30, 2009 at 11:20 pm
hi Friends,
Actually i m not a DBA but the situation given to me is just perfact for this forum category....
I work in One of the product development Company..We have dveloped our ERP and we have many clients also....
We have Crystal reports as reporting tool and sql 2005 as backend..
Now See the scene is like this...
Few of our clients have 4-5 years Data gethered on their dastabse....
Now the database has become very large so it is responding slow for any damn reports execution...
Now as per the solution we thinking that we should break the current database in to 2 or 3 databases based on years means every two years data will have the new database....And also we should automate the data moving process based on the conditions like if its two years old it should move to the differnet server.
Now how to achive this is the question adn our problem?
Means first how to break this database and then how to automate the data moving on daily basis...
Thanks & Regards,
Mithun Gite
March 31, 2009 at 3:45 am
Hi
Sure you can split your database into several year-based databases but it might become difficult to handle data depending on their referencial integrity. I don't know your database but what would you do if you have a parent object with a earlier update/creation date than one of its linked children but the child has a link to another parent which needs to be moved to an older database?. I would prefer another solution.
Either split database files
If not already done you should add additional data-spaces on different HD-devices and move your indexes to a separate disk. Try to put some of your main tables to separate disks. This can bring a performance boost without any changes from outside.
Or split only some tables
If there are some main tables you may consider to split only them. So the most parts of your database structure stays as it is and you can put the split tables onto separate HD-devices.
Or a Data-Warehouse
This would be the best approach in most instances. You create a Warehouse-Database beside your OLTP database. The structure of this database is more flatted than the operative one.
* As first this makes you able to move your reporting to the new database. Due to the flat structure and writes by other processes your reports will be much faster.
* It should be no problem to hold many years of data within this database. We have some DW databases with many of hundreds of gigs (sure, I don't know your database sizes...).
* You may be able in future to create OLAP cubes for deeper reportings
Greets
Flo
March 31, 2009 at 12:25 pm
Hi
In simple words in stead of splitting teh database i would go with breaking the tables in Horizontal way i.e. either by creating partitions or breaking the tables like Current Year, 1-3 years table and 1-10 years table.
Thanks -- Vijaya Kadiyala
May 8, 2009 at 3:14 pm
In a lighter scale, create a transactional replication for your production DB and use that replicated DB for your Crystal reports. In that way you will NOT create any blocking on your production DB.
Other ways:
1. Partitioning table
2. creating cubes
Owel714
MCTS, MCITP
May 9, 2009 at 4:24 pm
mithun.gite (3/30/2009)Few of our clients have 4-5 years Data gethered on their dastabse.... Now the database has become very large so it is responding slow for any damn reports execution...
Let me offer you an alternative solution.
The scenario you describe shows a company running a bunch of databases with no actual DBA support -that's like letting the kids play with no adult supervision!
A database that gets slower as it grows shows a database not designed to scale up. Most probably databases are not properly designed/maintained/tunned therefore they show signs of stress.
How big is any one of those "very large" databases, are those Terabytes size databases?
If the answer is No, then just hire a DBA, let the guy/gal work on the performance issue.
How about that?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply