anyone ?

I've populated reporting DB's in the past using different techniques:

1. Have Transactions in Data Tier Write to OLTP AND Reporting DB Simultaneously

2. Log Shipping

3. Restore BAckups Nightly

4. Replication

5. DTS

My question is what is the BEST Practice in SQL2k5.

I'm all gung ho about using Mirroring but am not certain that this will be the best way to go.

My thoughts are something along the lines of this

1. have OLTP Mirrored to what I'll call a "Staging" DB. This is NOT to be used for Failover just as a means of accessing read only data for the Reporting DB

2. Have 2 DBs on another box that are flattened (Denormalized) for reporting (ReportA and ReportB)

3. Use SSIS to Populate the Report DBs in alternating sequences every XXX minutes (60 minutes let say)

4. Use Logic to let the Reporting Application Figure out which of the Two reporting DB's is "live". The Currently loading DB will be offline while it's being loaded and we'll alternate between the two (Exact method TBD but this wont be rocket science)

Now, I know this will "Work" but is this the best way to go about this ?

I do NOT want to use SSIS to populate the Reporting system directly from our OLTP system due to contention issues etc.

thoughts ?

Hello Gregory,

I've thought of using Database Mirroring for reporting as well, but there were a couple things holding me back. First off, you can't use the mirrored database directly, you have to create database snapshots of the mirrored database that you can use instead. Also, each database snapshot is independent of each other, so any time you create a new one, your applications/SSIS package will have to take this into account and reference the new snapshot. Secondly, the disk space. Unless you manage this well, you could eat up a lot of drive space.

Currently, I am using #3 from your list, restoring nightly backups. The problems I am having with this option is the length of the database restore, during which, the reporting database is offline. I'm up to a 21GB db backup file that I am restoring to another server, and it takes about 3 hours. Another problem I have is that my users would like the data to be more up-to-date.

Eventually, I think, I will be moving to asynchronous replication.

Hope this helps.

Jarret

|||

jarrett,

I guarantee you can speed up your backup "Dramatically" by backing up to multiple files (4 is probably a good # to start with).

Then restore from those 4 files on the target box.

I bet your backup and restore run in about 10 minutes (depending on the disk IO subsystem you're using)

try it out and see what happens.

thanks for the post.

GAJ

|||

Can anyone else comment on this thread\question ?