I support a datamart… and I was instructed to consume tables from a DW that is in another datacentre that we have access through linked server…
I suggested using replication cause it’s the easiest for me, but I was not authorized; and now I find myself doing this process every time we need to consume a new table:
1- Copying the entire table from DW to our DM through the ‘sql server wizard’. 2- Recreating in my datamart the indexes that the table had in the DW. 3 - I create an SP like this… for example:
DECLARE @SystemModStamp AS DATETIME
SELECT @SystemModStamp = MAX(SystemModStamp)
FROM [dbo].[Lead]
SELECT * INTO #temp
FROM [LinkedServer_DW].[SF].[dbo].[Lead]
WHERE SystemModStamp >= @SystemModStamp
DELETE FROM [dbo].[Lead]
FROM [dbo].[Lead] AS L
INNER JOIN #temp AS t
ON L.Lead_id= t.Lead_id;
INSERT INTO [dbo].[lead]
SELECT * FROM #temp
DROP TABLE #temp
4-Put the previous SP on a job that will run every 6 hours.
The problem I see is that when it started it was just a few tables they requested. Now I am asked to add one new table every other day, and now I have around 80 tables on my datamart through this process.
Questions: -is this approach ok/professional? -to complete all the steps it takes me a few hours, because every step itself takes time, and I am extra cautious with every action I do. -Some tables do not have systemmodstamp or updatedate; so I am doing full transfers (drop, and insert all of it again).
Anyway, they are not huge tables, the largest one has 50 million records… and most of them are less than 100,000 records…
Is there any reason not to use Replication in this case?
You're re-implementing what looks like snapshot replication, but not putting all of the consideration that years worth of development from MS into it. Which isn't a knock on you. It's not clear to me why replication has been dismissed out of hand. But, as with all things, it seems that a cost/benefit analysis needs to be done. You know what the cost of the current way of doing it is in terms of time and effort expended by you. If the people providing direction want to be honest, they'll also look at the cost of implementing snapshot replication and compare. My guess is that the latter will come out on top because you ultimately just end up adding a table to a publication and that's about the extent of the setup (even though there is an initial setup that needs to be done).