Where to put the historical data?

Question

Where to put the historical data?

john.mcginley

SSC Journeyman

Points: 81
More actions
July 24, 2017 at 2:40 pm

#324618

Good day!
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.
Thanks!
John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Not sure there are any easy answers here. If you put the data that sits in SQL 2005 under the auspices of a SQL 2012 box, you'll need to test to be sure that everything continues to work as expected, and that your Cognos version can handle a connection to SQL 2012. Just restoring the database on the SQL 2012 box will leave it in SQL 2005 compatibility mode, ... something you can change, but need to understand the consequences of.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Arsh SSCertifiable Points: 6345 More actions · Answer 2

If I were you , I would go with option 2 , more so considering scalability etc. .Since the SQL to SQL will have no issues due to backward compatibility , the only concern would be the compatibility with Cognos version , which you've to test.

Thanks..
Arshad

john.mcginley SSC Journeyman Points: 81 More actions · Answer 3

Thanks sgmunson and Arsh,

"No easy answer" is exactly the point. We're choosing to choosing to move the data to the new server. We've tested for Cognos compatibility and we're fine. In the end the needs of Cognos seemed to outweigh the needs of SQL Server (which were largely a wash, although your point is well taken, Arsh). If some of the data are on a different (newer) server, when Cognos needs the data, it can go to two two different resources rather than bogging one resource down. Thanks for your replies!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

john.mcginley - Monday, July 24, 2017 2:40 PM
Table partitioning would be the "A" answer here but we can't do that with Standard Edition.

Sure you can. Look into "Partitioned Views". I actually like them better than Partitioned Tables. They have a lot of the same capabilities if you look at things with a different set of eyes.

As for still using Windows 2003... you really need to upgrade. 2003 is a penetration waiting to happen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.mcginley SSC Journeyman Points: 81 More actions · Answer 5

I'll look into partitioned views. The server will be decommissioned after this has finished.

Thanks Jeff!