We have a sales ETL and data warehouse running on SQL 2008R2 Standard Edition. There is also a historical database for older data on a SQL 2005 server. Historical data occasionally gets updated during nightly ETL if someone touches an old record in the ERP system. We build Cognos cubes with this historical data and the current data once a week on Saturday. Table partitioning would be the "A" answer here but we can't do that with Standard Edition.
We have to move the historical data because the box for that data is Windows Server 2003. BTW all servers are virtual, running VMware. Anyone have an opinion about the better of two options?
1) Create a new drive and put the historical data on the 2008 server. Now all the data is on one box - now nothing gets passed around on the network
2) Put the data on an available SQL 2012(SP3) data warehouse server - now it's faster than the old 2005 server.