November 23, 2011 at 8:41 am
Hi All,
I have an application that uses a SQL 2005 DB that is currently 40.6 GB in size. The full backups are currently 33.6 GB. The nightly transaction logs range from 55 GB to 65 GB. The application files are directly related to the data in the database. My current project is to come up with a plan to get the files and database restored to the DR environment which is in a separate state. The biggest issue is that the files and database need to be moved at the same time (at least backups taken at the same time and then moved). The full backups and the transaction logs are too large to move over the network which makes me think of using differential backups. But differential backups use the LSN of the last full backup. We will be driving personally the first initial full backup to the DR location via external drive. So my problem is: how do I get the DB changes to the DR on a nightly basis along with the application files?
My first thought is: is there a way to make the differential use a different LSN? However the backups are decided to be done, I will be using script to execute.
Thank you so much in advance,
tshaffer
November 23, 2011 at 8:55 am
Depending on what you have the ability to change or implement, there are many different options. Log shipping or database mirroring + log shipping. Moving to SQL 2008 or adding a 3rd party backup solution which would provide compressed backups. You can use log shipping and use the backup that you are moving via external storage as the initialization point for the restores. You'd just have to keep all of the primary logs after that backup to apply to the DR side.
November 23, 2011 at 9:04 am
I can't use log shipping or mirroring because, from what I understand, those are automated processes. I can't move the data to the DR until the files can go as well and vice versa. The DR environment will have a possible 24 hour loss of data, meaning it will be synced on a nightly basis. The process will be to shut down the application, do the DB backup, copy files and DB backup to DR, bring application back up. All this will be done through vb.net code. I should mention that I am a developer that has never had the privilege of working with a SQL DBA so I am learning and doing on my own.
November 23, 2011 at 9:05 am
As has been said, there are many options. You could logship, mirror, replicate, do nightly diffs and restores. It depends on what level of change you can implement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2011 at 9:25 am
Just because you can lose 24 hours worth of data doesn't mean you have to. I would seriously rethink this requirement that the databases and app data have to go at the same time. You are limiting your options with real gain.
CEWII
November 23, 2011 at 9:35 am
If the data is restored to the DR in an automated process without the application files going with it through the day and the building that production is in burns down in the middle of the day and we have to use the DR for production, The application will fail since the files and data are not in sync. There is no way around that.
November 23, 2011 at 9:42 am
Then build a process to detect what data you do have and back out the references to data you don't. The position that there is NO way around this is not the right mindset. There are very few things simply not possible. If oyu don't want to consider this then thats ok, your choice. But you might look at bringing over the app data more often as well.
I have dealt and written several large systems that have mixed DB and file dependencies and this is really ot rocket science..
CEWII
November 23, 2011 at 11:50 am
Please explain what you mean about data and files. I'm confused about what each has to do with the other. Are your application files changing through out the day?
November 23, 2011 at 12:32 pm
Are your application files changing through out the day?
Yes, this application uses the DB as well as files to store different kinds of data. Some data in the DB have fields that relate to data that is in files kept on the application server. As changes are made through the day, both the data in the DB and the data in the files are modified and kept in sync.
Therefore, I have been given a window each night that I am allowed to shut the application down while I transfer the files and data to the DR.
I'm open minded about a solution, but I can't move files while the system is in use and since I can't transfer a full DB backup nighly or even weekly, I'm not sure what good either differential backups or transaction logs would do for me.
If there were a way to alter the differential_base_lsn that the Differential would use, I could probably do that, but I don't think that there is.
November 23, 2011 at 12:41 pm
We don't really have enough information to make many suggestions. There are 3rd party utilities that allow you to sync file systems, so everytime the data file (not the database) is updated, the sync process copies the updated file to the DR facility. That timing could coincide with the log shipping process which would keep the two pieces in sync.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply