Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Moving data to a reporting database
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

18 Posts

Posted - 09/18/2006 :  12:32:05  Show Profile
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.



United Kingdom
22859 Posts

Posted - 09/18/2006 :  13:10:37  Show Profile
"... 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 Draft

Provided 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.

Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
2052 Posts

Posted - 09/18/2006 :  14:18:03  Show Profile
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)

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.



Don't worry head. The computer will do all the thinking from now on.
Go to Top of Page

Aged Yak Warrior

940 Posts

Posted - 09/19/2006 :  03:15:43  Show Profile
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000