December 15, 2016 at 8:48 am
Hi
We have 10 databases on a server and I have a requirement to centralize the data into 1 database in realtime. I was looking at transactional replication and it works well, however, the source tables don't have any information to indicate a source name. So all the data from multiple databases doesn't have the source information to make sense of the data. Adding additional column to the source tables will fix the issue, but we have 250+ tables in each database and an application sitting on it, so adding the new columns will be a hassle.
Do you have any suggestions?
Thanks
December 15, 2016 at 1:29 pm
You could try replicating a view. The table being replicated to could have the source field. Then in the view you could calculate out the source by using @@SERVERNAME or some other identification.
Haven't tried it myself yet, but am thinking about it for a couple of my packages.
Good luck
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 15, 2016 at 1:49 pm
Hi Steve
I did try that too, but the @@SERVERNAME returns the source server and the view created in the subscriber returns the subscription server. The view itself will fetch information on the server/db it is residing in.
Thanks
December 15, 2016 at 4:04 pm
Here is something that you can try, publish stored procedure execution results. It executes at the publisher and sends the results to the subscriber and executes there.
Maybe you can do a select, concatenating the servername to the resultset and do an insert if the data.
https://msdn.microsoft.com/en-us/library/ms152754.aspx
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply