July 6, 2012 at 1:54 am
We have a production database which is hosted within our company, but in a separate domain. We have access to this domain only via FTP.
We want to keep an up-to-date read-only copy of this database on our domain - we will use this for reporting. At the moment, we are doing this via an automated backup-FTP-restore process, which runs daily (database is not huge - < 10GB).
This is a third-party database, so we cannot make DDL changes to it, nor can we enable CDC (of course, for the copy database, we can do whatever we choose).
We'd like to make the process more elegant, and more frequent, but the FTP-only communication requirement seems to be a limiting factor in this.
Ideally, we'd also like to be able to track any changes which are made to the database, though we can do this the old-fashioned way if required (using date created, date modified, max(Id) etc). The changes will be fed into a data warehouse.
Does anyone have any ideas on how we might improve our existing process?
Thanks for any input.
July 6, 2012 at 2:05 am
For keeping the read-only copy up to date, could you not do a sort of make shift logshipping configuration, assuming that the recovery model is FULL or you are allowed to change the recovery model.
I've not used CDC so cannot comment on this, but can you put CDC on a database which is in standby mode, and when transaction logs are replayed they are captured by CDC so you can play them back on the data warehouse?
July 6, 2012 at 2:20 am
anthony.green (7/6/2012)
For keeping the read-only copy up to date, could you not do a sort of make shift logshipping configuration, assuming that the recovery model is FULL or you are allowed to change the recovery model.I've not used CDC so cannot comment on this, but can you put CDC on a database which is in standby mode, and when transaction logs are replayed they are captured by CDC so you can play them back on the data warehouse?
Thanks Anthony. Your ideas are along the same lines as I was considering, but I don't know. More research needed.
July 6, 2012 at 8:37 am
why not implement a log shipping configuration, this would be very easy to setup and maintain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply