So we have seven servers, one for each of our locations, each server has the exact same schemas/table setup. We were making a reporting server to be able to look at the data from all sites at once. In order to do this at first what we did was a daily load of each server unto the reporting server. We added a
SourceServerID column to each table and had that generated by the SSIS package. These were put into a database named ODS.
Our seven servers are going through an upgrade right now. With this upgrade changes are being made that will allow us to use replication instead of the daily load like we were before. With the replication we are creating a staging database on the reporting server for each of the seven servers naming them like SourceServer1. Then creating views on the ODS that do simple
Select... Union... type of query to combine the data from all the SourceServerX databases.
This is causing the reports we run to go much slower (ex: from 5 min. to 2 hours).
Could the slow down be due to pulling from the multiple SourceServerX databases as opposed to the one ODS database like before even though these are all on the same instance of SQL Server?
If we combined the tables into one database and then added the SourceServer name to the front of the tables would the views then run faster?
With replication could we combine the tables from all the SourceServers into one set of tables adding in the
SourceServerIDcolumn like we did for the daily load?
Is there a different method that would allow for better performance while maintaining the up to date data that replication provides?
Note: I had first put this on Stack Overflow (see https://stackoverflow.com/questions/45017951/database-merging-and-replication) but realized this might be a better location for this question.