First let me give some back ground information on this issue. My company has a very old server (1999/2000) that is running SQL 2000 with AS. The hardware or server is ready to die and I have been asked to move this off the hardware and onto a newer hardware. The new server is in place, running Server 2003 R2 instead of Server 2000 and SQL 2000 with AS. I can get my data warehouse loading processes to run fine and all the data in the databases match exactly. There are some minor differences in the SQL patches and updates, which I will explain as I go. Here's the problem, I can get some cubes to process without any errors while others will not process (log file is attached, scroll to bottom to see the error) and then errors.
Here's what I have done:
1-Backed up and restored all the databases for the data warehouse and AS.
2-Migrated all DTS packages and pointed them to the new server.
3-Pointed the new AS to look at the new server databases instead of the old server.
4-Ran tests to check for row counts and tests to compare the data table by table.
5-Opened the Cube that fails to process and can look at the data in the supporting views and fact tables (not the processed data) without any errors. So I can see the raw unprocessed data.
6-Some of the objects use common dimensions, which I processed first, but according to one of the individuals that helped setup the data warehouse, cube processing order does not matter.
Here is the error lines from the log file:
Completed Processing Partition 'Demand Product 2009'. End time: 12:45:52 PM Duration: 0:14:49 Rows processed: 11405108
Completed Processing Cube 'Demand Product 2009'. End time: 12:45:52 PM Duration: 0:14:58
Committing transaction in Database 'SE_Demand_Sales'
Analysis Server Error: Connection to the server is lost; Time:11/9/2009 12:45:52 PM
Error(-2147221411): Process operation failed; Time:11/9/2009 12:45:52 PM
Error(-2147221423): Unable to open server object (Internal (Database
'SE_Demand_Sales' could not be opened on the server)); Time:11/9/2009 12:45:52 PM
Rolling back transaction in Database 'SE_Demand_Sales'
Analysis Server Error: Connection to the server is lost; Time:11/9/2009 12:45:53 PM
Okay I have looked up time out issues and have matched the settings on both servers, no change still get this exact error. I have even pushed out the time out window to the max settings, still get the same error. I am lead to think that the error is not a time out issue but some thing else. The account that this runs under has full permissions to the data warehouse and AS as an administrator. There are other things that I have tried but they as well did not work. Another thing that I tried was to open AS Manager on the old server and backup each AS Database and then restore those to the new server. I then changed where the data connections pointed and any other settings that pointed to the old server to point to the new server. Still get the same error. My next step is to call Microsoft. If anyone can suggest anything please do, would rather not call Microsoft. This is the last item in order to turn off the older server. One thing that I did try is that the old server is running SP3a so one fix that I found was to apply SP4, no change there either. Thanks