Use SQL 2008 SSRS and SSAS on a SQL 2000 Instance and Database?

2
votes

The ERP system that I use is "stuck" in SQL 2000. The vendor states that it will not work, even in 2000 compatibility mode with any version of SQL higher than 2005. Due to corporate red tape I cannot use SQL 2005.

Can I install 2008 on the same server as a separate instance and then use the tools of 2008 to create SSRS and SSAS projects?

Can they read directly from the SQL 2000 databases?

If not, could I transfer data from the 2000 instance to the 2008 instance with log shipping or perhaps a simple daily back up and restore?

If necessary, I could set up 2008 on a separate server and then would attempt to move data from 2000 to 2008 periodically and run SSRS and SSAS on that. Would this work?

Any input would be appreciated.

2
sql-serverreporting-servicesssas
Can you say who the ERP vendors is, so we can avoid ever doing business with them (unless maybe they have a later version that does work with 2005, and certain companies won't upgrade). – John Saunders
@John: Amen. I want to know what ERP vendor is telling you SQL 2008 can't access SQL 2000 data. – Eric
Wow, that was fast. The ERP Product is called Made2Manage. They have an upgraded version which works with SQL 2008. However, there have been serious problems with it so we are not going to upgrade for some time. I am one version behind and they told me that it will not operate on SQL 2008 even in 2000 compatibility mode. – DavidStein
depending on how updated you need your data to be, you could build the cubes straight from the sql2000 instance. Where i work they have a similar problem and every night the cubes are build taking the data from the other instance, a star schema is obtained trough views, it was done this way just to avoid the etl process... – Alan Featherston

2 Answers

2
votes

Yes, on all accounts.

SQL Server is absolutely backwards compatible, and can use SQL Server 2000 as a source for SSAS and SSRS. It uses the SQL 10.0 driver, which can connect to 2000 instances with no issue.

That being said, I'd look into using SSIS to bring data into a 2008 instance and star it out, since typically, ERP systems are in 3NF, which is not very suitable for SSAS. You're much better off with a star schema for this.

0
votes

There is no reason I can see that this is not possible unless you're doing some form of failover clustering, and then you would just need to put the instance of SQL 2008 on a separate server. SSAS and SSRS should be able to access the SQL 2000 database natively with no need for ETL that I can think of.