Reporting Database recommendation

Hi all

I was wanting is little advice..

I am in the process of designing as SQL environment (SQL 2008). It includes 3 server to handle the productions database incase of failover (2 in cluster and 1 mirror in a different DC) and 1 more for reporting.

My question is around the reporting DB. The reporting db needs to be almost in sync with the prod db (within 5-15 mins). The main db is 300Gb and has around 500 trans/sec

What is the best option to get a copy of the prod database(s) to a reporting db?


more ▼

asked Jan 18, 2010 at 08:52 PM in Default

sp_lock gravatar image

9.3k 26 28 31

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Log shipping is a nice option. You can put it in Standby mode, which means it'll act as a read-only environment for you, applying the next chunk of changes when there's no-one attached. You'd still want your ReportServer database on your main server, but your report data sources can be on the shipped one.

Just make sure that your reports get in and get out, so that you're not hanging around with a connection to a server that wants to apply the next log.

more ▼

answered Jan 18, 2010 at 09:00 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Hi Rob, What's your reasoning for putting the ReportServer database on the main server, please?
Jan 19, 2010 at 04:43 AM David Wimbush
Thanks Rob.. That was my initial thought, wanted an experts view! Thanks again!!!
Jan 19, 2010 at 05:41 AM sp_lock
David - Because report definitions should be considered "production", and backed up, etc. The other server is primarily for DR purposes, and used for reporting on the side.
Jan 19, 2010 at 06:48 AM Rob Farley
Thanks, Rob. Got it. I have separate production, standby and reporting servers with the ReportServer databases on the reporting one. I was worried I might be missing a trick!
Jan 20, 2010 at 12:06 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left

We use replication to do a synchronisation between the OLTP and OLAP data servers.

We did look at log shipping but the reporting solution doesnt give a long enough gap for it to take place, they are running reports almost constantly. Replication runs on different schedules according to how volatile the data is in the the various tables - code reference tables synch daily, others twice daily and the most important every 15 minutes. Report users are happy and never blocked from the database while a restore takes place.

Sometimes replication is a pain, getting stuck mid process but its never taken down either database and we just restart the agents and its off again.

I guess it depends on your load on the report server, if its only light and can cope with the up/down nature of logshipping then go with that, its certainly the easiest to implement and maintain.

more ▼

answered Jan 19, 2010 at 05:54 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Both log shipping (as mentioned by Rob) and replication make good solutions depending on the details of the situation. Normally I prefer replication. It permits a more fine grained control which is often invaluable, especially if dealing with limited bandwidth of limited space.
Jan 20, 2010 at 10:32 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

May I suggest that you build a proper Datawarehouse?

That is a separat database, with a new table structure, which is more suited towards reporting. You need SSIS jobs to Extract data from your production server, to Transform data to the new format, and to Load data into the new tables; google ETL, or read The Data Warehouse Toolkit by Ralph Kimball et al.

This way you get fast reporting, and something that can take in data from multiple databases.

more ▼

answered Jan 20, 2010 at 10:16 AM

Henrik Staun Poulsen gravatar image

Henrik Staun Poulsen
579 13 15 16

+1 Definitely the way to go. Writing reports based on OLTP databases is creating a rod for your own back. Get a solid database with the crap cleaned up and you're halfway there. Each report is simpler, quicker to develop, runs faster and is more reliable.
Jan 20, 2010 at 12:10 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left

I have to agree with Faterjack here. Logshipping is not in my mind suited for this type of reporting setup because you need to disconnect users all the time to restore new logs. If that is OK , then go for logshipping, otherwise replication is the way to go.

more ▼

answered Jan 19, 2010 at 07:42 AM

Steinar gravatar image

1.7k 3 4 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 18, 2010 at 08:52 PM

Seen: 2851 times

Last Updated: Jan 18, 2010 at 08:52 PM