I have a database which fed to web application, batches and reports too.
Due to huge processing in web application, batches; I would like to separate reports database in a separate server and web application and batches will be having one year data on that server.
So, below is my thinking
--> I will move all data on reports database and current database will be having one year data.
--> I need to transfer daily data on reports database i.e. from current database to reports database.
Now, the question is that, I can easily this task using SSIS (.dtsx) package but I need to dig all bases like I can achieve this by log shipping, replication, mirroring and failover clustering.
Can you please put your stamp for other alternatives regardless SSIS (.dtsx)?
Important Note: the reports database can be on separate network then through .dtsx; I can export data into flat files and place on FTP from source and on destination, I can schedule another .dtsx file which import data from flat files after get files from FTP location.
But, can I achieve log shipping, replication etc. on separate network?