January 14, 2011 at 9:33 am
I have a 170GB DB and a 80GB DB that needs to be replicated, moved, restored, etc to a reporting server.
First, I used the wonderful SQL Agent to backup and restore, recreate users, run SP's, etc which worked fine but now the DB's are getting so big that if something happens anywhere in this process I don't have enough time to correct it.
Second, I tried replication, talked with Microsoft and we came to the conclusion that replication won't work because there are too many schema changes, etc. so $250 later I'm still using the backup/restore process.
Third, and currently, I am manually taking a SAN snapshot from our production server and attaching it to the report server every night which makes reports always a day behind but I just figured out everytime it takes a snapshot, SQL sees it as a backup so it messes up my differential backup schedule and did I mention I have to do this every night MANUALLY. 😀
Is anybody doing something similiar in your organization or know of a better way???!?
January 14, 2011 at 9:49 am
Have you considered Log Shipping?
I was a little concerned that there were a lot of schema changes, in a production system schema should be fairly static.. Are you doing live DEV in this environment?
CEWII
January 14, 2011 at 9:55 am
Any reason why you cannot report directly from prod (for a little while).
January 14, 2011 at 10:01 am
I actually do log ship a few databases to our report server but for these databases that I'm having problems with I can't because they can't be read-only.
We used to report straight off the prod server and used to have terrible performance. 🙂
January 14, 2011 at 10:03 am
No offense but 170 GB is still a rather small db. Performance shouldn't be all that bad if the ssrs queries are well written.
I was thinking about that option as a failover in case something goes wrong.
January 14, 2011 at 10:13 am
I agree 170GB isn't huge, and yes, I'm sure some of the SSRS queries are not written very well, but the bossman wanted a seperate reporting server so we created one and reporting performance increased (reporting is a HUGE deal)
I just find it hard to believe out of all the 500GB - 1TB databases that no one has a seperate reporting server - ?
January 14, 2011 at 10:34 am
SQL08Kid (1/14/2011)
I actually do log ship a few databases to our report server but for these databases that I'm having problems with I can't because they can't be read-only.We used to report straight off the prod server and used to have terrible performance. 🙂
I'm a little concerned that a report server ISN'T read-only, what is being written to it and could you write that to another database to keep this one clean.
CEWII
January 14, 2011 at 11:20 am
Agreed Elliott. Our developers every now and then will write to this database for testing purposes. We have a test server as well but for some reason they need to write to the report server DB also.
January 14, 2011 at 11:43 am
This strikes me as a bad practice. Having been on both sides (Dev and admin) I find that Devs should VERY rarely be able to modify a production system at all.
It seems like you are having to do a fair amount of working around to allow something that they shouldn't be doing in the first place.
If they need to change data to test then they can get another copy, make the painful process be the one-off and not the main process that you NEED. They'll complain but will eventually come to understand it..
CEWII
January 14, 2011 at 12:06 pm
Use DoubleTake...
January 14, 2011 at 12:16 pm
SQL08Kid (1/14/2011)
I agree 170GB isn't huge, and yes, I'm sure some of the SSRS queries are not written very well, but the bossman wanted a seperate reporting server so we created one and reporting performance increased (reporting is a HUGE deal)I just find it hard to believe out of all the 500GB - 1TB databases that no one has a seperate reporting server - ?
The real perf problem might have been from processing the data on the same machine as the oltp server.
I do have a separate reporting machine to do the crunch work, but I still report straight off prod. And yes we have 12+ 1M rows tables with 200 columns in them. Server is virtualized 32 bit standard sql 2005 with only 2 proc. We have over 50 employees working the ERP system and we don't have any problems... as long as the queries are tuned correctly.
January 14, 2011 at 12:24 pm
If you have a lot of schema changes, then replication can be a hassle. However, have you considered instead using SSIS and building more of a reporting warehouse? It's not a good solution in the short term, but having a good, denormalized set warehouse of your data can be a great way to get insight into your business.
On the other hand, how often do you make schema changes? Replication will push some schema changes through.
January 14, 2011 at 12:50 pm
Did they ever consider database mirroring.
I know it very much depends on the volume of your db load, but it if you are on Ent Edtn, you could (re)create a snapshot of the mirror to serve as read only copy for reporting purposes.
(very much enhanced with sql2008 !)
No hassle like with replication.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2011 at 1:33 pm
Elliott Whitlow (1/14/2011)
This strikes me as a bad practice. Having been on both sides (Dev and admin) I find that Devs should VERY rarely be able to modify a production system at all.It seems like you are having to do a fair amount of working around to allow something that they shouldn't be doing in the first place.
If they need to change data to test then they can get another copy, make the painful process be the one-off and not the main process that you NEED. They'll complain but will eventually come to understand it..
CEWII
Just talked to one of the developers and the SP's that I run after restoring are actually creating tables in the database that the reports access...therefore it can't be read only.
January 14, 2011 at 3:49 pm
I have a 1TB database that I restore nightly to a report server. The process I use is:
Split the backup BCV backup volume
Mount the BCV volume on the report server
Restore the databases (and log files to bring to current)
Unmount the BCV volume
Sychronize the BCV volume
I then run any processes that need to update the reporting version of the database. Then, I set all databases to read only.
I use Litespeed for my backups, which reduces the size of the backup files to about 180GB. The total process takes about 6 hours to restore the TB database, log files and ancillary databases.
Any reports that require real-time data are set up to run on the production system and are optimized as well as we can make them. These reports only access current data - and are written so the users cannot run them for previous data.
All other reports are run on the report server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply