Search the Community
Showing results for tags 'SQL Server Analysis Service'.
In our last post we installed SCVMM Reporting. By default, you should now have all the SCVMM reports available in SCOM’s Reporting space. However, there is an additional set of reports that is not made available by default. These are the Forecasting Analysis reports, and the only way to be able to use them is with SQL Server Analysis Services (SSAS). Since SCVMM using the reporting feature of SCOM, we need to have SSAS installed on the SCOM Reporting server. In our lab example, we have SQL DB and Reporting installed on the same server that is running SCOM. So, we’re going to go through the steps of installing SSAS first, and then configuring SCVMM to use it. Install SSAS To start, log into your SCOM server. From there, mount the SQL Server ISO or extract it and run the setup.exe. Since we have already installed SQL, I will not repeat each/every step here, since the screens are identical. Just walk through the SQL installation process as if you were installing a new instance of SQL. The screens that you need to take note of are, first, the Installation Type. Instead of selecting ‘Perform a new installation of SQL Server’, choose ‘Add features to an existing instance of SQL Server’. Then click Next. You should next be on the Features Selection screen. On here, select the ‘Analysis Services’ feature, and then click Next. Continue clicking Next until you reach the ‘Analysis Services Configuration’ screen. On this screen, add the applicable user(s)/security group(s) for administrative access to Analysis Services. Identical to how I have setup SQL Server, I have added a Security Group to control access. Make the applicable configuration for your environment, and then click Next. IMPORTANT: The previous screen to this one (Server Configuration) is where we (in a normal SQL installation) would change the Collation (see my guide on SQL Installation). I have be unsuccessful in finding any official documentation from Microsoft about the proper Collation for SSAS, especially since SSAS does not have SQL Collation like the SQL Database and Reporting features does. Therefore, for my lab example, I have accepted the default SSAS Collation. Additionally, I have likewise been unable to find any official documentation from Microsoft about the proper Server Mode configuration for Analysis Servers in reference to SCVMM. Therefore, again, I am accepting the default Server Mode of ‘Multidimensional and Data Mining Mode’. Continue to click Next until you reach the Install screen, and then click Install. SQL Analysis Services AMO Install To avoid an error when attempting to enable SSAS in SCVMM, you must install the Analysis Management Objects for SQL on the SCVMM Management Server. In our lab example, we are using SQL Server 2012. Therefore, to obtain the AMO elements, we need to download and install the Microsoft SQL Server 2012 Feature Pack. You can find the SQL Feature Pack (specifically SQL 2012) here: http://www.microsoft.com/en-us/download/details.aspx?id=29065. Download the file to your SCVMM Management Sever. Now, start the installation by running the SQL_AS_AMO.msi file and click Run. On the Welcome screen click Next. Read and accept the License Agreement, and then click Next. Now all you have to do is click Install. Once the installation is complete, click Finish. We now have the SQL Analysis Management Objects installed, and can proceed with enabling the use of SSAS in SCVMM. NOTE: You may have to restart your SCVMM server after installing this, not because the installation prompts you to, but for everything to initialize properly. Enable Forecasting Analysis After you have SSAS installed, you can now enable SCVMM to use SSAS and forecasting analysis reports. Log into your SCVMM server, and launch the console. From there, navigate to Settings > System Center Settings > Operations Manager Server. Either right-click and choose Properties, choose Properties from the ribbon menu, or just double click on setting option. Since we already performed the integration steps between SCOM and SCVMM, you should see the Connection Status on the Details screen as “OK”. Click on the ‘SQL Server Analysis Services’ label. On this screen, check the ‘Enable SSAS’ checkbox. You will then be able to provide the SSAS server name and instance, along with the port, and account to use. In our lab example since we installed all elements on the SCOM server, our SSAS server name is ‘SCOM.SC.LAB’ and my instance name is ‘SCOMSQL’. Make the applicable entries for your environment, and then click OK. Notice that when you click OK, you may encounter the following message. It indicates that the SCVMM server needs to have the SQL Server Analysis Management Objects installed. Press OK if you encounter this error, and following the steps detailed in the ‘SQL Analysis Services AMO Install’ section of this document. IMPORTANT: Through extensive research, it appears that even if you have SQL Server 2012 installed, along with SQL 2012 Reporting, that you cannot in fact use the SQL 2012 Analysis Services Analysis Management Objects. This is currently applicable with version SCVMM 2012 SP1 UR2. It has been determined that you MUST use SQL 2008 R2 Analysis Management Objects. It is unknown if this will be corrected in a future SCVMM update. NOTE: I cannot take credit for this discovery/solution. Here is the reference page that led me to this conclusion: http://blogs.catapultsystems.com/lrayl/archive/2013/05/20/configuring-the-system-center-2012-sp1-vmm-sql-analysis-services-integration-with-operations-manager.aspx. So at this point, you need to repeat the steps for installing SQL Analysis Services AMO, but with version/feature pack SQL Server AMO for SQL 2008 R2; here is the link: http://www.microsoft.com/en-us/download/details.aspx?id=16978. Once you install SQL Server AMO for SQL 2008 R2, then repeat the steps already detailed for Enable Forecasting Analysis. Once you enable SSAS and enter the applicable information and click OK, if everything else is setup correctly the Operations Manager Settings dialog will automatically close. You can re-open it and confirm that SSAS is enabled. And that’s it. You now have SSAS enabled in SCVMM. The reports already exist in SCOM but now the Forecasting Reports can use the collected information to project data into the future. That completes the Installation Guides for SCVMM. The next part of the series will cover Configuration.