| Author |
Topic |
|
studrew
Starting Member
2 Posts |
 Posted - 2007-08-15 : 13:51:36
|
| Hello all,We are currently using reporting services on our production sql05 database. I would like to offload this to another server for better performance. I was thinking about using a maintenance plan to nightly copy over the database and running the reports off that. Is there a better way to do this? I heard of mirroring the database but never tried it before... any help apprec... ThanksStudrew |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-15 : 13:54:18
|
| There are different options depending on requirements. Do you need up-to-minute copy of the db or does 1 day old copy work for your reporting? Mirroring does come with an overhead on the primary server.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2007-08-15 : 15:45:24
|
| Replication is good for this. That's what we do. Snapshot replication for small, infrequently changing tables. transaction replication for larger, frequently changing tables. |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-08-15 : 16:57:31
|
| If you have Enterprise edition on a secondary machine you can mirror from the main to the 2nd server and then snapshot the mirror database for reporting purposes.IMO, this is easier to maintain than replication over the long term. It also worked quite well in my proof of concepts that I have done using ~80GB databases with a steady throughput.If you go this route, the most important piece of the design is to ensure that the mirror'd databse on the secondary server is on the fastest disks you can afford. Most of the queries for a DSS system are not going to be able to get thier datab from the snapshot and will go to the mirror for the data.... |
|
|
|
studrew
Starting Member
2 Posts |
Posted - 2007-08-15 : 17:57:03
|
| Thanks for the replies. Our most demanding reports can work on 1 day old data which is why I was thinking about nightly copies. Mirroring sounds very interesting. I assume it will work as a fail over if the primary server goes down right? How much overhead will mirroring add to the primary? |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-08-15 : 18:04:09
|
| Yes, it will work as a secondary server, so you get the bonus of a DR type of operation in addition.Mirroring is low overhead when set to synchronous mode (one way commit) which is the way I performed all of my POC work... |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-15 : 18:08:16
|
quote:
Originally posted by studrew
Thanks for the replies. Our most demanding reports can work on 1 day old data which is why I was thinking about nightly copies. Mirroring sounds very interesting. I assume it will work as a fail over if the primary server goes down right? How much overhead will mirroring add to the primary?
IT depends on how you set it up. There are different options available. Check out books on line and you will understand what I mean.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-08-15 : 18:13:41
|
| If static one day old data is acceptable, backup & restore may be the easiest option if the report consumers can deal with static day-old data.However, they usually don't stay satisfied with day-old static data for very long. :) |
|
|
|
|
offload reporting to another db server?