October 17, 2011 at 3:35 am
I have been set in my ways for the last few years using SQL Server...
I have been working on a project that involves linked-servers and multiple SQL Server Databases.. Basically its a data-warehousing project.
So I am importing not all data, but the most import pieces required, like transactional information and some config information.
The way I approach it is get my linked servers setup then I write multiple stored procedures to select the data, clean the data then insert the data, I may have mulitple stored procedures to do the work.
I then setup a SQL Agent scheduled job, that executes the stored procedures in a certain order, etc...
So this is how I handle getting the data I require into the warehouse...
I should also mention that all the databases are stored at a single site, so replication is not required between sites.
Is there a better approach than this...? I have only worked on a few projects like this and it seems to get the job done ok...
Any feedback or idea's are appreciated.
Cheers.
October 17, 2011 at 6:25 am
Have you considerd using SSIS for this?
when building a data warehouse is if often considered good practice to use an ETL tool to load the data, of course it can be done with procedures but there are many advantages to using a tool.
October 17, 2011 at 10:45 pm
As steve mentioned SSIS is the good way to do this. if you go with linked server approach you will face some performance issue while copy data from across the servers. if you still want achieve this through the linked server try using distributed partition view.
Regards - Deepak
October 17, 2011 at 11:48 pm
SSIS is really the only way to go IMHO. Linked servers lead to all sorts of problems down the line: retiring hardware, scaling up and resolving performance issues just to name a few. Solving performance issues can be a reall killer using linked servers. Linked servers might perform OK now, but when your data volumes grow you will probably be in the position where you have to split the ETL logic across both servers. This leads to fragmentation of the logic for a sinlge ETL process. Much better to put all that into a single modularized SSIS package. If you use configurations wisely, targeting new hardware as it is commissioned becomes a breeze.
October 18, 2011 at 12:29 am
Thanks for the info, I will read up on SSIS
Regards
shanewiso
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply