| Author |
Topic |
|
chris_cs
Posting Yak Master
223 Posts |
 Posted - 2010-07-09 : 15:44:48
|
| Hi Guys,We are starting to experience some performance issues on our primary SQL server when running certain reports. We have had our db for a while now so the volume of our financial data is becoming quite large.I'm starting to think about offloading the reporting burden for our server, so I wondered which approach would be the best.At first I thought about using snapshots but unfortunately we don't have enterprise edition.What are my options?Thanks in advance for any advice! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2010-07-09 : 15:51:39
|
| If you could provide me with some articles that would be great.I'm planning on using a separate server to host the reporting data so this may be a good way to go. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-09 : 15:58:09
|
| My articles are only for the immediate sync part. You have to first decide if you want to use transactional replication. In my opinion, transactional replication is the best solution for reporting needs. I wouldn't even consider a snapshot since it still uses the same hardware (CPU, memory). Plus transactional replication gives you more options, such as using different indexes, different stored procedures, etc...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2010-07-09 : 16:03:09
|
| Ok that makes sense.I'll have a read up on this and see if it will be suitable for us. Out of interest, are there any other methods I could use? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-09 : 16:07:18
|
| Some people use log shipping, but I think that is a bad choice. Each time log shipping has to restore the transaction log to the secondary system, it has to kick everyone off the database. That wouldn't be acceptable on my reporting system! Our users need constant access to the system.Another option is to perform backup/restore on a regular basis, but that only would be acceptable if your data can be 24 hours old. If your database is big, backup/restore would be unacceptable as the duration would be too long to get it online again. Instead of backup/restore and if your data can be old, you could use snapshot replication.My users need up to date information 24 hours a day, so transactional replication is the only option.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2010-07-09 : 16:15:23
|
| I'd have to speak to my relevant colleagues to decide that. I thought about log shipping but didn't think that would be suitable.I have a feeling that I'd need the data to be as up to date as possible. Although, depending on how 'live' the data would be on my new server when using transactional replication, I could keep some reports looking at the production database. At least I would be reducing the majority of the burden. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2010-07-09 : 16:21:37
|
| Ok great. In that case it looks like I should go down that route. Thanks for the advice! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Offloading of reporting