October 27, 2015 at 5:15 am
SimonH (10/27/2015)
Could you not use a cached Lookup on the IDs, as opposed to a Merge Join
and then use an SSIS script (gives you access to C# hashbytes function which isn't limited to varchar(8000)) to compare for UPDATES.
There is a good article on it here.
I know, I wrote the article 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2015 at 5:22 am
LOL didn't notice that.
Great article though, I based My DW Incremental load around it.
October 27, 2015 at 5:51 am
So Server A has database ABC. Server B has database DEF. You need some of the data from DEF to be in ABC? Or is it just copied over to a incomplete DEF database on Server A?
I'm presuming Server X has DEF on it given that you mentioned that server but then said you couldn't touch it. But is that the same thing as getting copies of Server X's database backups? Usually, if the DBA / Server admins are worth their salt, they push copies of the backups to multiple places so if the primary backup storage goes down, there's a secondary available.
Or am I missing something about Server X besides the politics?
October 27, 2015 at 6:03 am
Brandie Tarvin (10/27/2015)
So Server A has database ABC. Server B has database DEF. You need some of the data from DEF to be in ABC? Or is it just copied over to a incomplete DEF database on Server A?I'm presuming Server X has DEF on it given that you mentioned that server but then said you couldn't touch it. But is that the same thing as getting copies of Server X's database backups? Usually, if the DBA / Server admins are worth their salt, they push copies of the backups to multiple places so if the primary backup storage goes down, there's a secondary available.
Or am I missing something about Server X besides the politics?
So the source database of the ERP system is on server A. I can't touch that one, not even read from it (yeah don't get me started).
But they are willing to restore a backup on another server B. So I can read from this server all I want. I need some of the data on my own server C. Between the two networks (A/B versus C) is a slow network connection.
I think now that calculating the delta on server B and transferring it to server C is the best option. On a daily basis, the delta should certainly be less than 1GB.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2015 at 6:09 am
Koen Verbeeck (10/27/2015)
So the source database of the ERP system is on server A. I can't touch that one, not even read from it (yeah don't get me started). But they are willing to restore a backup on another server B.
If they're willing to restore a backup on Server B, maybe they can send you a full backup to your local via SFTP and then send you daily differentials for restore? Then you could make a standby database on Server C that you can read from.
I think now that calculating the delta on server B and transferring it to server C is the best option. On a daily basis, the delta should certainly be less than 1GB.
Or you can do that.
October 27, 2015 at 6:20 am
Brandie Tarvin (10/27/2015)
Koen Verbeeck (10/27/2015)
So the source database of the ERP system is on server A. I can't touch that one, not even read from it (yeah don't get me started). But they are willing to restore a backup on another server B.If they're willing to restore a backup on Server B, maybe they can send you a full backup to your local via SFTP and then send you daily differentials for restore? Then you could make a standby database on Server C that you can read from.
We tried. Their firewall and network topology is apparently too complicated for this...
Again, don't ask. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2015 at 6:28 am
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply