April 14, 2010 at 10:50 am
Afternoon,
I am in the process of designing how our company can use Transaction log shipping between our production environment (primary server) and our development environment (secondary server) and have been asked by our technical lead if there is another way to delete files other than the file age on the secondary server?
I.e. if the size of the secondary server share was 1GB and the next transaction log copied to this share meant the overall size of the drive was greater than 1GB there would be a way to automatically delete/overwrite the oldest transaction log with the newest copied file?
Is this within the scope of the transaction log shipping GUI/stored procedures in SQL?
Thanks in advance,
Dan
April 14, 2010 at 10:59 am
Danb7183 (4/14/2010)
I.e. if the size of the secondary server share was 1GB and the next transaction log copied to this share meant the overall size of the drive was greater than 1GB there would be a way to automatically delete/overwrite the oldest transaction log with the newest copied file?
u need to be sure that the existing file from secondary that u plan to delete in such case, has been restored or else log shipping will break.
you can make use of xp_delete_file sp which deletes files prior to specific date/time...
April 14, 2010 at 11:08 am
I understand that it will potentially break the chain if a transaction log is deleted that is required for a restore to happen. The backups would be carried out every 15 minutes and copied every 15 minutes so the deletion of transaction logs that are required would be highly unlikely....I do already use the xp_delete_file for the deletion of production backups outside a certain time-frame 🙂
That was however not my question. Is it possible to overwrite files based on age within a network drive of a specific size?
April 14, 2010 at 11:16 am
Danb7183 (4/14/2010)
Is it possible to overwrite files based on age within a network drive of a specific size?
I understand your concern of having less mapped drive size, however cant think of a way to overwrite the files as the files will have different names.
you can write something like this in the copy procedure/job step:
If existingFile has been restored
then delete existing file: copy new file
else
do nothing. wait for next time the copy job runs
-- cant write the exact code now. totally drained.. gotta go home 😎
April 14, 2010 at 11:26 am
I have to admit I don't often see Log Shipping as a method used to maintain a Dev environment. I would ask why this is being done? Or was the explanation just unclear? I would tend to say let SQL manage this.
Can you explain further?
CEWII
April 14, 2010 at 1:15 pm
there is nothing in the logshipping process as supplied by the GUI to do it, you would have to write your own.
I suggest make it a process that is kicked off on failure of the copy job. then on success of the delete step(delete oldest file?) rerun the copy job (step 1)
---------------------------------------------------------------------
April 14, 2010 at 1:20 pm
I was looking at this in SQL 2008 and it does give you the options. Not sure if there is a difference. I haven't set up LS in 2005 for a LOOOONG time.
CEWII
April 14, 2010 at 1:33 pm
Elliott W (4/14/2010)
I was looking at this in SQL 2008 and it does give you the options. Not sure if there is a difference. I haven't set up LS in 2005 for a LOOOONG time.CEWII
Elliot, stop trying to catch me up on posts! 🙂
I haven't set up logshipping in 2008 yet but done it in 2005 plenty of times, there's no option to delete by file size there. Are you saying this is an option is 2008 (delete by something other than age)
---------------------------------------------------------------------
April 14, 2010 at 1:40 pm
Another thought, on the secondary transaction log backups are deleted by age from the datestamp of the file just restored, so set deletion variable to same as frequency of restores, then only log backup on secondary will be one waiting to restore
---------------------------------------------------------------------
April 14, 2010 at 1:44 pm
I was looking at it a bit ago and age is the only option. I guess I would ask why that is a problem? Disk is cheap, don't go cheap.. The process will manage the files itself if you let it. What is the business reason or technical reason for this whole thread?
CEWII
April 14, 2010 at 1:47 pm
And George, I shall continue to spar you for point counts.. 😛 I'm trying to get my overall rank under 60 this week..
CEWII
April 14, 2010 at 2:17 pm
Elliott W (4/14/2010)
And George, I shall continue to spar you for point counts.. 😛 I'm trying to get my overall rank under 60 this week..CEWII
darn it, only gone 30 mins and you got 8 more points. :pinch:
rolling sleeves up.............
😉
---------------------------------------------------------------------
April 14, 2010 at 2:50 pm
On guard!
CEWII
April 14, 2010 at 3:44 pm
Elliott W (4/14/2010)
I have to admit I don't often see Log Shipping as a method used to maintain a Dev environment. I would ask why this is being done? Or was the explanation just unclear? I would tend to say let SQL manage this.Can you explain further?
CEWII
I want to ensure consistency of data between our main Production DB and a DB on the development environment that is used for change control before these changes are rolled out to production.
Previously I put some T-SQL together that would at a specified interval to restore the development DB from the Production backups and although this worked it is now failing and in addition it was not efficient - every restore had to be started with a full restore which took around 10 mins in its self! So ideally I want the best way to maintain consistency of data with as little downtime as possible between the two environments/servers...the other option I considered was mirroring but this I understood wasn't possible between a standard edition of SQL Server and an Developer edition of SQL?
If you are able to suggest a better strategy or technique to maintain data consistency between two servers (and potentially more than one DB on the secondary server) I'm all ears!
Oh and stop hijacking my thread with your post counts + 1! 😉
Thanks,
Dan
April 14, 2010 at 3:49 pm
Elliott W (4/14/2010)
I was looking at it a bit ago and age is the only option. I guess I would ask why that is a problem? Disk is cheap, don't go cheap.. The process will manage the files itself if you let it. What is the business reason or technical reason for this whole thread?CEWII
It's not that this is a problem, I need sign off from the Technical lead for this process to be implemented and one of his questions was if we could 'overwrite' files within the network share with the newer files when it reaches capacity. I agree leaving SQL to it's own in this situation is the best option and will explain that a different technique of controlling the files within the folder will require additional development time 😛
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply