Dynamics Community Forum Thread Details

Analysis Cubes on SQL Cluster

I have MS Dynamics GP installed on an application server (GPDEV01). The database and analysis services are installed on a SQL Cluster in its own instance (C3GP\GP). Integration Services is running on the Cluster Node (SQLC3N1).

I installed the Analysis Cubes data warehouse on C3GP\GP. Everything installs and setups as expected. However, when I run the job to build the cubes the job fails. It cannot find the Packages because it is looking for the packages on C3GP\GP (the instance) instead of SQLC3N1 (where Integration Services is running).

I scripted out the job and replaced all instances of C3GP\GP in the job to point to SQLC3N1. The first portion will now run but fails upon the package job looking for \GP\DynamicsGP_GP_CreateUserDefinedFunctions.

Again the job is expecting the package to be in C3GP\GP. I have a lot of companies in my database (over 200) and I do not want to open each package and change the server value in order to build cubes.

It appears the software expects the databases, analysis services, and integration services to all be running on a single server.

Any suggestions on how to configure this?

WOW, that is a good question.

How about trying this.
1) Take the backups from your GPDEV01 (Production) and restore them to C3GP\GP (Warehouse) .
2) Create a job on your Warehouse server to restore each transaction log backup of you Production server.
3) Schedule the transaction log restore job create in step 2 to run after the Production transaction log backup occurs. I usally backup at 10 past the hour and retore 40 minutes past the hour but you can change that as needed.
4) Set the schedule for the Integration / Analysis jobs on your Warehouse server to run after the log restore job on the Warehouse server.

Now, that sounded a lot harder as to the timing that it really is. My transaction log backups on my production takes a few minutes. The only reason I wait so long is that 30 minutes apart sounded good at the time.

Also, you could actually place the log restore commands as a step 1 in the first job that refreshes the cubes. That way the logs will always be finished restoring before the cube refresh.

Note: You may even notice a performance boost in Microsoft Dynamics GP as the Productin SQL Server is not being directly referenced by the Analysis Cubes or Integration Services. They are just looking at their local copy of the databases.

Let me know how it goes, and it this fixes everything for you please mark the question as answered.

Quick Links

Leaderboard