asked
Using SSIS in SQL 2005 for Data warehousing
I have SQL 2005 servers(Enterprise) located at different locations which are used locally as production servers. Now I want to build a Cerntral Database Server(Enterprise) which would merge the data from these servers having the same database structures but different data and create a central DataWarehouse. Furthermore data from this DW can be used to create small data marts(express). Can I use SSIS for the same? How efficient would it be related to network and bandwidth since I am using 1Mbps netwok links (ipsec) between the servers?Is there any other efficient solution for the same??
You can use SSIS fro populating the central data warehouse as SSIS is designed for that purposes. But the performance through the 1 Mmbps if you would like to read the data directly will be probably bad and consume the bandwidth. Better would be to export data which needs to be sent from the source system eg. to flat files, compress the flat files, transfer them to destination location using eg. ftp or file share (depends on your configuration) and then decompress them and load to the final database. So in fact you will have a SSIS packages on the source systems which will populate the flat files and on the destination system you will have SSIS packages which will populate the destination database from the flat files.
I guess SQL Server Replication is best suited for this scenario..
http://msdn.microsoft.com/en-us/library/ms151198.aspx
Thanks for your quick response Pavel but I would also want to know that will flat files be efficient enough to transfer data for a transactional database? Also what should be the ideal time intervals at which the flat file generation and restoration can be done considering the fact that the database increases by 50 MB per day and also reports need to be pulled from the central DW showing the latest possible data? Also is there any other direct method for the same??
If you need nearly online data in the central database, then some kind of replication could be the way as e.g. @Kannan stated. If it is enough for you to load the data e.g. daily, then I would go by the flat files. In case of replication, you need to consider the number of Publishers and capacity of your lines (as you stated it's only 1 Mbps line). If the line is only for the synchronization, then it should not a problem, but if it is used also for other purposes, then it will be a limit.
Also I was mentioning the Flat Files as you wrote you would like to build a Data Warehouse from multiple sources having the same structure.. Generally DW have a quite different structure from the production OLTP databases and mostly it's enough to load data into the DW once a day, but this depends on the business needs. I't not a problem to make the load several times as day or even continuous, but all this depends on the production system load, lines capacity and other factors.
You may want to consider Log shipping in this instance with an adequate compression method to ease the transport of the files over the network.
Check out this article for standby servers but also releveant for your requirement: https://www.sqlservercentral.com/articles/log-shipping-vs-replication
Thanks guys..extending to the above scenario say I have 2 similar structured tables having identity column as primary key in two different database servers and I am merging the data from these servers into DW using SSIS (flat files)...will it throw an exception?? How can it be resolved without changing the table structure?? Also does updations in the table structures (addition of column or addition of constraint or data type change etc) get migrated from from individual server to the DW using SSIS (flat files as mentioned above)?
First of all, if the destination table will have this identity filed set as primary key, then you cannot insert two same values into that destination table as it will throw violation of primary key. If you want to avoid this, will have to update the identity filed in one of the tables and reseed to appropriate value. e.g. 1 000 000 000. Then the identity values on the other server will start from 1 000 000 000 and not collide with identity values from first server. Other option is to add another field in the destination table which will distinguish between the two servers and primary key in that destination table will the the new field with the combination of the identity value. Related to the structure changes.. Those changes will not be propagated using the flat files as the flat files have no information about the data types. So if you change data types in the original tables, you will have to update the tables also in the destination and also update the SSIS packages to handle those new data types.
