Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
podgehb
Starting Member
18 Posts |
 Posted - 2006-09-18 : 12:32:05
|
| We have an Inventory database (SQL Server 2005) that supports our Inventory website and also all the inventory reports. The website slows down when large reports are run, especially reports that span many months (or even years). Therefore we have decided to split the database into a transactional database (for the website) and a reporting database (for the reports). And we'll put these databases on separate servers. We use Reporting Services for our reports.I know what we need to achieve, but am looking for help on how to move the data from the transactional database to the reporting database -- and what technologies I should be using (eg. SSIS, replication, stored procedures).Here is some background info: The transactional database will only need a weeks worth of data (which is "draft" data). At the end of the week, the users finalise this data (making it "finalised" data) which is copied to reporting tables and the original draft data is no longer needed. Although the transactional database will only need to store a weeks data, the reporting database will need to store the reporting data for years.Reports report on both draft data and finalised data. At the moment, a one report is able to show both type of data (because it's all in one database).As I see, this leaves us with options: (1) Keep the databases syncronised so the reporting database has all the data and therefore all the reports could be based on the reporting database. Only thing is: when old data gets deleted from the transactional database, that delete action should not be syncronised to the reporting database.(2) Only copy the data to the reporting database when a week is finalsed so that the transactional database only has draft data and the reporting database only has finalised data. This means that when a report wanted to show draft data it would look at the transactional database and if a report wanted to finalised data, it would look at the reporting database.Thanks, Craig |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-18 : 13:10:37
|
| "... all the reports could be based on the reporting database"If you transfer the data, say, once a day then the Reporting Database will be 1 day behind - that may be good enough, but if not then that might force your decision to separating the reporting between Finalised and DraftProvided the two servers can "see each other" I would suggest writing a SQL script to INSERT new rows, update existing rows that have changed and, if necessary, delete rows and then scheduling that to run once a day at a quiet period. Log the @@ROWCOUNT of each Insert/Update into a table, with the current date/time, so that you have an easy way to see the volume being transferred - useful when things go wrong to see if a particular operation (e.g. Insert to the customer table) stopped transferring any rows suddenly, or that each transfer should transfer 10 tables, but has only been transferring 8 for the last two days - clearly its been raising an error 80% of the way through.Kristen |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-09-18 : 14:18:03
|
| We do a very similar thing to this, when we transfer our data (every night in our case) we flatten it a little and pre-calculate some fields so for example the reporting system will have something like Date of Birth (Datetime)Year of Birth (int)Month of Birth (int)Day of Birth (int)etc.in one table. This means that the reports run more efficiently. You will also need to think about your indexing strategy as it can be very different in the reporting database to what you may have in your Inventory database.It may be worth your while taking a look at some of the OLAP stuff that SQL Server can do too.steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-19 : 03:15:43
|
| Has anyone tried this scenario on 2005? There have always been issues with reports slowing down SQL Server databases because of the shit locking mechanism. Now 2005 there is the multi version option which, as far as I can tell, makes a read operation non-blocking (the same as Oracle) which is much better in my opinion. However I have not had the opportunity to test out if it reduces the performance degradation I've seen with running large reports.Anyone tried this out? |
|
|
|
|
|
|
|
|
Moving data to a reporting database