June 16, 2010 at 11:17 am
Good morning all,
We have a large database (1.5TB) whose current data archiving scheme involves daily jobs which copy data from the OLTP database to an archive database on the same instance of SQL Server. The developers would like to put the archival database on a separate server and perform the same copy operation through a linked server.
My experience with this type of processes would tend to push me into using SSIS to move the data rather than a linked server. But I don't have a good understanding of the issues, if any, of pushing large amounts of archival data (500MB per process) through a linked server. Perhaps I am concerned for no good reason.
Any advice would be appreciated.
June 16, 2010 at 11:27 am
David Webb-200187 (6/16/2010)Any advice would be appreciated.
Did developer say why he wants to archive in a different box?
Is the box local or remote?
How big is the pipe in between the two boxes?
Is the same pipe used by other production processes?
SSIS or custom code should perform the same... if very well written custom code may perform better.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 16, 2010 at 12:25 pm
The archive of historical data is accessed in unpredictable ways by the research folks. We'd all like to get it off the OLTP instance.
The new archive server is on our local network (gig cards in the servers) and the lan segment is shared by other servers but is not near saturation.
June 16, 2010 at 12:32 pm
I see, not a pure archival resource but an archival/reporting one. Fair enough.
500 Meg is not a huge volume of data to be moved in a daily basis - provided bandwith is not an issue.
I would go ahead and test the process, probably keeping current archival process alive for a couple of weeks.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply