November 14, 2012 at 2:38 pm
Hi All,
I'm new to the DBA role (come from BI/SQL developer background) and my boss has asked for a dedicated reporting server.
The current setup is 1 database (27 Gigs) that is mirrored for HA.
The reports don't have to show 'upto' the minute info, they can show data from the previous night.
My first thought was to schedule a snapshot of the data on the mirrored database each night. Drop and recreate every evening but to my dismay they have the Standard Edition of SQL server 🙁
So now am left with the option of copying the backup every night and doing a restore to the reports server but was wondering how i could do this. If the backup is set to run at say 2am how will I know when its finished so i can copy it to the report server for restoring?
Or does anyone have any other probably better suggestions?
Update: should of mentioned the Database has to have 24/7 HA so the least performance hitting solution the better:)
Many thanks
November 14, 2012 at 2:52 pm
We perform a nightly backup and restore to our standby server. the way we answered the "How long till i move it?" was to set up the backup and let it run for a week then see how long it took. based on that time we scheduled the move on the time it took plus a little. we did the same process to figure out when to start the restore on our standby server.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 14, 2012 at 2:53 pm
What I would consider, is after taking a full backup at anytime, and restoring that to the other box. Then, I would create a Differential backup on an everyday schedule and restore that to be used for the report server data.
Why would you think that you must have the database on the report server box? You may consider creating another database on a less busy database box and make your reporting server use that.
I would ask the DBA group though before creating any database or taking a backup to use.
Andrew SQLDBA
November 14, 2012 at 3:34 pm
capnhector (11/14/2012)
We perform a nightly backup and restore to our standby server. the way we answered the "How long till i move it?" was to set up the backup and let it run for a week then see how long it took. based on that time we scheduled the move on the time it took plus a little. we did the same process to figure out when to start the restore on our standby server.
Hmm what happens when the database grows and takes longer to backup?
November 14, 2012 at 3:39 pm
That is why I suggested that you take only a Differential backup. That will stay some what smaller for a long time.
How many transactions per second are you receiving on this particular database?
Andrew SQLDBA
November 14, 2012 at 3:40 pm
AndrewSQLDBA (11/14/2012)
What I would consider, is after taking a full backup at anytime, and restoring that to the other box. Then, I would create a Differential backup on an everyday schedule and restore that to be used for the report server data.Why would you think that you must have the database on the report server box? You may consider creating another database on a less busy database box and make your reporting server use that.
I would ask the DBA group though before creating any database or taking a backup to use.
Andrew SQLDBA
Hi Andrew,
I don't think I must have the database on the report server box, maybe I worded my question wrong. I just want a dedicated reports database on a different server. Currently reports are run on the production database.
Your idea of taking a differential backup at the end of every day and restoring it sounds pretty good.
I have have just been looking into Transactional replication, have you guys got experience with this? would this work and what is the performance hit like?
Thanks
November 14, 2012 at 3:55 pm
bugg (11/14/2012)
capnhector (11/14/2012)
We perform a nightly backup and restore to our standby server. the way we answered the "How long till i move it?" was to set up the backup and let it run for a week then see how long it took. based on that time we scheduled the move on the time it took plus a little. we did the same process to figure out when to start the restore on our standby server.Hmm what happens when the database grows and takes longer to backup?
we currently have it in a couple of steps because we also restore a copy on our prod server in case one some one forgets a where clause on a delete (its happened). currently the last guy set it up like that and i have not had the time to make it better. that will probably be my january project. With CommandExec and multiple job steps you can create a single job that will backup copy and if you take the time to set it up, have the job launch the restore job on the report server.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 14, 2012 at 4:16 pm
Yes, I have experience with Log shipping, Mirroring, and others.
It really kind of depends on how many transactions per second, minute, hour, day, week that database is going to perform.
It is ok to put the database on the report server box. Nothing at all wrong with that. But if you plan on 1000 or so transactions per minute, then I wuold not use replication. I would still stick with a Differential backup. Why you ask....... Well, depending on the amount of hardware, and any other things that the prod box is being used for, will depend on what else you can ask of it during the active transaction period. replication does not happen on a one-for-one transaction. Meaning that the database engine will flag all the transaction to be sent out, and when the time comes, it will send them. But if you have a slow network, that could create a backup of moving the data.
Is this a 24/7 Prod Database box? How many transactions per a certain time is the box performing? There are so many dependencies that we really cannot tell you exactly what to do. I would suggest Replication only if the data in the reports are needed, if not, then don't task either box with that.
We really need more information from you to give you a more precise answer to this.
Andrew SQLDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply