asked
SQL 2012 Data Transformation for Add-On App DB
Hi Guys, Im new here and in need of your help. this is the scenario i have in hand. we have our Main App DB and Add-On App DB currently sitting on same SQL Server 2012. the Main DB is region specific where some of the master data are based on local language. the Add-On DB data fully runs in english. when some orders are placed in main DB, the order information will be pushed to the Add-On DB.Once order is processed, the data will be pushed back to main DB. at the moment, we use C# Service and SP to move data between both DB and the data transformation is done via SP. but the service is unreliable as it seem to fail from time to time, causing data not being available in Add-On DB. to simplify this, we are looking at 2 options. SSIS and SQL Service Broker. i have not much knowledge on SQL Service Broker, however, i have attended classes on SSIS and was considering to use it for this scenario. what would be the best approach for this scenario and what setup is optimal to get the best performance out of it ?
Service Broker is more likely to give you a real time transmission of the data since you can set it up to fire in a more automated fashion than SSIS. SSIS will work fine, but it's going to only work on a timer or something. I'd lean towards service broker if picking between the two.
15 People are following this question.
