Problem deploying 2005 OLAP cube to same server

I have both limited hardware resources and the need to rebuild SQL 2005 OLAP cubes on the same server where they are being accessed. Because of the time needed to rebuild the cube it is preferable not to build it in place but, rather, to build it in another database and then backup/restore to the original location.

There doesn't appear to be a lot of documentation related to operating in this manner. The assumption appears to be that the cube structure will be deployed to another server or a backup is restored to its original location.

That works just fine except that when the underlying data structures (dimensions on which is it built) are changed it 'invalidates' both cubes. All of the objects (cube partitions and dimensions) are MOLAP which I thought made the entire cube structure 'stand alone'. If it is restored to a different physical server this problem does not occur.

Is it in some way considering the dimensions shared? Is there a setting or set of clean-up that needs to be done? Is there a different way to do this other than backup/restore?

Thanks in advance for any help that you could provide.

I am not very clear on what problems you mention you get after restoring the database on the same server under different name.
Try installing SP2 and see if that solves your problem.

Another approach is to install second instance of Analysis Server on the same machine and use it for processing only. This should both save you the hardware and provide level of separation you are trying to achieve.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

problem copying database from sql server 2000 to sql server 2005

Hi,

I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.

Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.

What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?

What it is that I am missing?

Kindly help me.

Thanks, regards.

Shobha

You're really asking two questions, I think.

1. Why is servertwo not accessible?

With SQL Server 2005, we disabled a number of features by default to provide better security. You can easily adjust the settings at any time by using the Surface Area Configuration tool, available from the Start menu.

2. How do I move my databases over to servertwo?

There are several ways to do this. One way is to use sp_detach_db on serverone, then copy mdf and ldf files to servertwo and use sp_attach_db.

Paul

|||

Thanks for the reply Paul.

Is it that the datatbase should be exclusively available when doing sp_detach_db?

thanks

Shobha

|||

Hmm, good question, and a glance at BOL didn't pop the answer.

I don't believe you need exclusive access; I assume the operation is transactional so it'll wait for open transactions to complete before executing. But as I said above, the documentation of sp_detach_db doesn't make this clear. The documentation does list a few other requirements (e.g., if the database is replicated, it must be unpublished).

|||

Thanks Paul for that answer.

If you don't mind can I have sample code or instruction list on how to use the sp_deattach_db and sp_attach_db commands, this will be the first time that I will be using these commands since earlier I was working only with MSAccess database.

will be greatful if you can give me directions.

thanks