Wednesday, November 07, 2012 1:30 PM
Hey i've a large fact table which is stored in my Data Warehouse database/ server1. I need information from this Fact as well as other references data customers , supplier tables etc.. on another server2 where other databases uses this data.
My question is whats the best way to approach getting data from the fac t/ references tables over to my other server.
Am thinking, create another Datamart on my other server2 and populate it with the data from my Datawarehouse on a daily basics through an SSIS and T_SQL. Then i can use this Datamart as a references to my other database on this server2.
This will also pervent having a large fact and references table in each of the other databases on server 2
I'd just like to see what other have done in regards to this. As refering to fact and references table from one server to another in T-SQL is to slow..
Thanks in adcance
Wednesday, November 07, 2012 4:06 PM
You can add a package which will be executed after the fact table is loaded successfully. This will take the data from server1 and send them to the second server (server2).
You could also use snapshot replication. This is good if you are copying data from several tables, since you don't have to modify several packages.
Thursday, November 08, 2012 8:47 AM
Thanks i know its a simple enough question but am sure many people have the same issues in that they have duplicate data on many server moving around all the time.
Thanks for the reply.