February 5, 2009 at 9:04 am
Hello,
I am looking for some help on the best way to do the following;
I have a SQL Server 2005 Standard on two servers, one is production and the other is not. I have some advance users that want the ability to build reports through access or the RR but I do not want the touching the live db as you would all agree. So my goal would be to at night back of the db, copy it to the second server and have it restored and set to read only so that that can tool around on this with their reports. I would like to make this as automated as possible. I have seen/heard of tools inside of SQL to do this but I have been unable to find any step by step to do this.
Would/Could someone help me out with this? The database in question is a Microsoft RMS HQ database, of course Microsoft does not support replication of this db. Not to say that would not work but they just do not support it. So if there is a way to do it that way as well I would be willing to try that as well.
Regards,
Mike
February 5, 2009 at 9:11 am
A couple of quick thoughts, MS may not support replication of the database in relation to their application but you are only doing this for reporting so, you could set up snapshot replication to handle this.
The second option would be to use backup and restore. If the database is not really big then I would just let it back up normally on the production server and put together a job on your reporting server and restore it on the reporting server by referencing the backup file from across the network. There may be some slowness but if the network is fairly quick and your database is not too large that should be sufficient.
Would that work?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 9:16 am
My database is about 6gig and I would agree with your second option (being it's a little eaiser and I can detach and do all the db functions i need on the live without having to break the replication). I can do this late at night there is nothing going on at this site, The servers are located on the same rack and gig switch, so the speed would be fine. I just have been unable to find the proper step by step docs to make it happen. Would you know of any good docs on doing this step by step?
Thank you for your help.
Regards,
February 5, 2009 at 9:25 am
Really the steps are fairly simple with one complexity that you want to ensure you have something in place to work around. So, a brief outline as follows;
1. Configure Backup database on production server (most likely / hopefully done already)
2. Configure Restore job on Reporting server
a. First step in job, check to be sure that you can read from the backup file by using something like restore fileheaderonly from disk = '\\productionserver\backuppath\file.bak' - There are probably a couple of ways to do this with some being more robust than others. If this returns an error you may want to build a loop to check back in a bit or reschedule the job or send an error. You can pick.
b. Second step is to do the restore.
You may want to have some alerting based on success / failure of the job so that you can know that it was completed and deal with any issues prior to the time that developers need access to the data.
Honestly, and most people avoid this, but snapshot replication would take care of all this for you and would be robust and would allow for alerting based on failure etc so, you may want to consider this a bit further.
You might be able to find something on the web that would be doing something similar to what I describe above. I'll try to poke around later if possible to see if I can find something.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 9:39 am
Funny you bring up snapshot, I read a little about this, but i was not sure if it you meet my needs (ie snap a shot of production server and return a full shot to the other server). I was concerned that it may not delvier all tables and data. But it sounds like from what your saying it will. Is that true?
If so the snapshot may be a little easier the the back/restore option. If it can all be done from SQL manager vs having to create netowrk folders for back and restore of the database. Would you not agree?
If I will need to find some step by steps on it. I am sure it's not too hard to setup a snapshot copy from one server to another. Another quick quesiton does it do a full backup/restore during a snapshot, or does it just update the secondary database with new data?
February 5, 2009 at 9:50 am
From BOL:
------------------------------------------------------------------------------------------
The Snapshot Agent performs the following steps:
Establishes a connection from the Distributor to the Publisher, and then takes locks on published tables if necessary:
For snapshot publications, locks are held during the entire snapshot generation process.
Writes a copy of the table schema for each article to a .sch file. If other database objects are published, such as indexes, constraints, stored procedures, views, user-defined functions, and so on, additional script files are generated.
Copies the data from the published table at the Publisher and writes the data to the snapshot folder. The snapshot is generated as a set of bulk copy program (BCP) files.
For snapshot and transactional publications, the Snapshot Agent appends rows to the MSrepl_commands and MSrepl_transactions tables in the distribution database. The entries in the MSrepl_commands table are commands indicating the location of .sch and .bcp files, any other snapshot files, and references to any pre- or post-snapshot scripts. The entries in the MSrepl_transactions table are commands relevant to synchronizing the Subscriber.
For merge publications, the Snapshot Agent performs additional steps. For more information, see How Merge Replication Initializes Publications and Subscriptions.
Releases any locks on published tables.
------------------------------------------------------------------------------------------
...and here is where I will somewhat contradict myself - 🙂 - but there is a higher cost with this process than with standard backup and restore. Read through the steps above and you will see that pretty clearly. However, it is robust.
Other thoughts of what you could do would be to use Log Shipping if your production database is in full recovery model and then just have the logs restored during the evening hours. This would be nice and robust right out of the box as well.
If I wasn't afraid of working through the nuances Log Shipping would be first choice, backup and restore second and snapshot replication third. Again, my primary reason for mentioning snapshot replication was due to the lack of "work" needed on the front-end.
Hope I didn't add confusion to this by suggesting things as I did.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 11:00 am
David,
No I see where you are coming from. The one problem I had but i have still yet to relate it to issue that I am currently working with MS on is that my db is in current simple recover. I did at one time try this and it appeared to work. But one day I came in and the log file for this db was max out on drive space and man what a headache that was on getting that resolved and I never found out why it spiked my log file to 65 gigs, yes 65 gigs.. It is usually at 700meg max. So my concern was that same issue arising...
But I agree about the kiss theory and I want it kept simple and easy so if I every have issue i do not have to go back and undo a whole bunch of tasks before I can maniplulate the master db.
February 5, 2009 at 11:11 am
When you switch the database to full recovery model you need to implement a proper backup plan for the transaction log to keep the log file size manageable. However, with that being said, you will need to ensure that you have adequate disk available to store the transaction log backups or that too will become a problem. Space is always the issue when it comes to databases.
Question - What are the recovery requirements if you have a problem with that database in the middle of the day? Is the business going to be comfortable with only having the data from the previous night backups? If so, then simple recovery may be enough for what you are doing but I would want that in an email and I would save that somewhere for future coverage. 😉
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 1:47 pm
I have local backups and network backups incase of failure. So it is pretty simple. They will have to reenter a day's worth of work if I have a day failure. I am currently looking at adding a noon backup so that the worse case a half day. But I can always recover sales by polling, its just po's etc.... So that is fine.
I have plenty of space over 100gigs between drives to play with. So that is clear as well.
February 5, 2009 at 1:50 pm
Then exercising hourly log backups may be the way to go and use that for log shipping to the other server. Have the logs applied at night and allow for the reporting database to be "online" during the day for reporting access. Gives you the coverage, and the reporting server you are looking for.
Just my thoughts but I think you will find it pretty easy to get it going.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 1:57 pm
2 Questions ?
1. What advantage does the log shippnig hourly vs once after working hours do for me.
2. What performance hit will the production server take if I do the hourly log shipping?
February 5, 2009 at 2:03 pm
The production server will only incur a small hit with the log backups but that will provide you with better protection throughout the day and will eliminate the possibility of having to have people re-enter data if there was an error.
Advantage.... Log shipping is a solid technology so, you don't have to "home grow" a restore process. I know that shouldn't be too hard but still log shipping is supported by MS so, you know that it is solid. AND, as already mentioned, you build yourself in some extra protection with enhanced backup architecture. If there is ever a failure your boss will love you. That should be reason enough. 😛
Obviously this is all opinion so, you should do what is best for your organization and your architecture.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 9:21 am
stimpy2300 (2/5/2009)
2. What performance hit will the production server take if I do the hourly log shipping?
If you do hourly log-shipping, the DB on your Reporting server will not be available to users when the logs get restored. That means that users will get interrupted every hour in the day.
From that point of view, you may be better off with the after-hour log shipping plan, while reporting server users would only be able to play with the data from previous day's restore.
February 6, 2009 at 9:24 am
sunny Brook (2/6/2009)
stimpy2300 (2/5/2009)
2. What performance hit will the production server take if I do the hourly log shipping?
If you do hourly log-shipping, the DB on your Reporting server will not be available to users when the logs get restored. That means that users will get interrupted every hour in the day.
From that point of view, you may be better off with the after-hour log shipping plan, while reporting server users would only be able to play with the data from previous day's restore.
You can have the logs applied at whatever time you want so, that wouldn't be true necessarily.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 9:39 am
Sorry, should have included this with previous reply - From BOL;
There are two options for configuration when you place the secondary database in standby mode:
You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.
---------------------------------------------------------------------------------------
You can also change the schedule that is created for restoring the transaction logs to accommodate the reporting window. I would have to play with this again to give specific details as I haven't used it in a while.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply