Reporting database synchronization

We are building an application. We now have an ms sql database where we put all the data wee need. We want to use another database just for reporting that will be denormalized version of the main database, optimized for fast data retrieval. There are a couple of questions we have:

1. What type of database is best suited for reporting job (SQL, RavenDB)
2. How to synchronize these two databases?

As as data access we use NHibernate 3.2.

Please help. Thanks

You could use SQL Server Analysis Services to house a proper data warehouse, which from your question sounds like the way you're going. SQL Server Integration Services can be used to perform the ETL (Extract-Transform-Load) process that will populate and keep the warehouse up-to-date.

There are already a ton of good comprehensive guides on this process.

Start by choosing the database (or search engine) that will meet your reporting needs. Then build an ETL process to get the data into the reporting engine and to keep it updated. ETL means Extract-Transform-Load and there are tools that do all the work for you or you can write your own special purpose tool.

It helps if your DB schema provides a "last update" timestamp on every record so that you can roll your own ETL tools. You don't even need to keep this updated since SQL Server will do it for you if you have something like:

CREATE TABLE Orders
(
OrderId int NOT NULL PRIMARY KEY,
ProductName varchar(50) NOT NULL,
LastUpdated datetime NOT NULL DEFAULT GETDATE()
)

RavenDB generally isn't used for reporting, the normal practice is to flatten your RavenDB data into a table structure (using an index) and export that to SQL Server and use Crystal or SQL Reporting Services.