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

Question

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.

Answer 1

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.

Answer 2

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.