Published on Saturday, December 6, 2014

Application Deployment Guide

Companion for MS SQL Server

 

Prerequisites and Deployment Checklist

 

Prerequisites

 

The following prerequisites are required for the proper operation of the Companion for MS Analysis Server:

 

Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

Microsoft SQL Server 2008 Native Client

Microsoft Data Access Components (MDAC) 2.8 SP1

 

Most likely you will not have to install them since these Microsoft products usually are installed with the SQL Server installation.

 

 

 

 

Deployment Checklist 

 

Database location and size:

Select a centralized SQL Database server that will be used for storing collected data from all services.

The database size will vary based on your settings. The software is self-trimming, so old data (usually over 90 days) will be trimmed automatically. However please allow at least 1GB for the database.

 

Services Security:

Determine how the installed services will connect to the database server. If you are going to use SQL server authentication you can create the necessary SQL account with the Database Connection Wizard inside the ASDC Manager. If you are going to use windows integrated security, please pre- create an AD resource account which will be used by the installed services. If you are installing the software on one server and will be storing the data in a local database, then you can use Network service or LocalSystem. All of the security settings are configurable in the ASDC Manager.

 

Self-Updating Configuration:

If you are going to use the Enterprise Deployment scenario, please contact us and decide which IIS

server inside your network will be used for deployment and decide on the FQDN and URL path.

 

Firewalls:

The services run on the SSAS server itself. The clients establish secured communications authenticated by Active Directory on ports 10800 + 1 for each installed Instance. So a server with 10 installed SSAS instances will use ports 10800-10809. There is also the option to custom configure ports. This can be done by setting up the default ServerID=0 tcp channel to port=0, and then each SSAS instance’s tcp channel can be manually assigned.


 

SQL Server Data Collection (SSDC) Manager v6.0

 

Installation Options

 

The Companion for MS SQL Server consists of two applications:

1 SQL Server Data Collection Services (SSDC) Manager (back end services)

2 Companion for MS SQL Server (front end client)

 

SQL Server Data Collection Services (SSDC) Manager is installed on every SSAS server. Once installed it is used to install and configure the SQL Server Data Collection Service. The SSDC Manager supports multiple installed SSAS instances on the same server therefore once service is installed per SSAS Instance.

The Companion for MS SQL Server is the client application that you can install on the SSAS server as well or on any client machine. You need to install it once per machine and then connect to all of the installed SSDC services.

 

The applications can be installed in 3 ways. Smart Deployment (option 1 below) is the recommended way.

 

1.       Recommended - Start the installation from our Click-Once website

http://www.entsoftsol.com/downloads/CompanionForMSSQLServer/

 

            Start with SQL Server Data Collection Services (SSDC) Manager x86 or x64 installation. You can start the client from the SSDC Manager or from the Client link on this web site.

 

2.   Standard Contact us to request the setup .msi files for manual installation:

Server setup:

SQL_x64ServiceSetup.msi run on x64 bit servers (AMD64, x64) SQL_x86ServiceSetup.msi run on x32/x86 bit servers

SQL_IA64ServiceSetup.msi - run on Itanium based servers

Client Setup:

MSSQLCompanion.msi run on all Windows OS platforms (servers and workstations)

 

3 Enterprise - For large corporate clients that require version control, Enterprise Software Solutions offers a custom built Click-Once deployment package that runs from the corporate IIS servers. Installed services and clients self-update from the internal corporate servers only.

 

This document provides a walk through using the recommended method. Start by visiting our deployment web site at: http://www.entsoftsol.com/downloads/CompanionForMSSQLServer/

 

 


 

Start with the server installation the two buttons at the bottom of the list. Depending on your server OS type select x86 or x64 installation. Our services are optimized for best performance and run natively on the OS platform. Install the server component on every SSAS server that you want to work with. Itanium (ia64) platform is also supported. Please contact us for the latest build.

 

Finally install the client (first button in the list) on every server and/or workstation and connect to the AS Data

Collection Services running on each monitored SSAS server.

 

 

Install the Analysis Server Data Collection Services Manager

 

For the purposes of this walkthrough we will install the x64 server component. Click on the bottom link (Run

Companion for MS SQL Server x64 Server) and you select Run when prompted.

 

a At the Welcome Screen you are informed that it will install the SSDC Manager. Click Next

 

 

b Please choose the folder where you want the SQL Server Data Collection Manager to be installed. Once installed in this folder, the installation path will be remembered. If you uninstall the SSDC Manager and reinstall it, it will automatically point to this folder.

 


 

 

 

c Next, Next and then Close. The installation of the SQL Server Data Collection Manager is complete.

The setup automatically starts the SSDC Manager.

 

Configuration, security, licensing and auto updates

 

 

a Getting Started

When you start the SSDC Manager it will usually take you directly to the Database step or to the Services step. However at any time you can go back to the Getting Started and get a general overview of each step, the current version of the SSDC Manager and the installation path.

 

 


 

b Database

If this is the first time that you are installing the Manager on this SSAS server, you will be welcomed with a screen that explains the initial steps necessary to get you started

 

 

If you have already installed the manager before, the database connection string is remembered and suggested that you use it.

 

Database Setup Wizard

 

The SSDC Manager uses a centralized SQL server database storage for all monitored SSAS instances. This allows you to compare server performance, generate centralized reports, review storage, execution times and more. If this is not the first time you are configuring the SSDC Manager the database connection string will be remembered for you. If this is the first time you will be asked to select the SQL server that you want to be used for the centralized database.

 

 

At this point you are asked for the server name and administrative credentials to connect to the server. These credentials will not be saved nor used after the wizard completes. They are used to establish an administrative connection to the SQL server in order to detect an installed database or to create a new one. Fill in the server name and authentication type and click Test Connection to confirm connectivity.


 

The SSDC Manager checks if the SQLDataCollectionDB exists on the server. If it does not exist it will ask you to create it.

 

 

 

 

The create process uses SQL statements to create the tables, stored procedures, views and functions, thus allowing you to install the database remotely.


 

 

 

If the database is found and it is in version 4, the SSDC Manager gives you the option to use the selected Database.

 

 


 

The database version is controlled by the installed services. They update it to the latest version when they self-update. The Client can also update the database if it is allowed to.

You do not have to manually upgrade the database. Click Next

 

Credentials for the installed services to use when connecting to the database

 

At this screen you are asked for the credentials that are to be used by the installed services connect to the database. If you use Windows Authentication, then the services configured security context will be used to connect to the SQL server: LocalSystem if locally installed database, NetworkService if database is on another server or DOMAIN\UserAccount to use a resource account. You can set the service’s credentials at the Service’s Credentials step. If you use SQL Server Authentication you are asked for a UserName and Password.

 

 

 

 

Once you provide the username and password to be used when connecting to the SQL Server click on the Test Connection button to verify the account. If you have provided an invalid account or the account does not exist, you will be prompted.


 

 

 

If you have not created the account already, you can type in a new username and set a password and click Create Login. The created login will be given dbo rights to the SQLDataCollectionDB database only.

 

 

 

 

Once the security credentials are configured, click Test Connection. When ready, click Next.

 

 

 

 

 

 

 

Summary

 

The Summary page gives you the configured database connection string that all of the locally installed services will use. Once you click Finish, the string is preserved in the registry so next time you start the SSDC Manager it will be reused. If you are concerned about passwords stored as text, please use Windows Integrated access to the SQL Server database. Click Finish.


 

 

 

 

 

c Services Credentials

The Services Credentials step allows you to select the security context of the AS Data Collection Service (InstanceName). The selected account will be used on each service that is installed at the Services Step. If you do not want to run all of your installed services in this single security account context, then skip this step, install the services and manually change the security context of each installed service. If using AD integrated authentication to connect to the database server make sure that each security context account for each installed service can access the SQLDataCollectionDB database.

If you have only one SSAS server and it is also the SQL database server, then you can choose Local System.

The newly installed services will run under the LocalSystem security context. If your database server is on another machine or if you have multiple SSAS servers that you want to monitor and want all of your data to be in a centralized storage, you can also choose NetworkService. Make sure that you give the NetworkService account access to the SQL database.


 

 

On most installations you would normally have a dedicated AD resource account that has administrative access rights to the SSAS instance and dbo owner rights to the SQLDataCollectionDB SQL database. In this case, please provide the DOMAIN\UserName and password credentials and click Apply to save the information. The ASDC Manager will test the account to confirm the password, but it will not test the database connectivity. Please make sure that this account has dbo owner rights to the database.

 


 

 

d)    License Manager

 

The SSDC Manager will first take you to the Services step so you can install the services first. Once the services are installed, please click on the Licenses step.

The grid shows all of the installed SSAS instances. You can request, import and export a license for each

SSAS instance. If you want to review all licenses (for all registered SSAS instances) check the check box

Show all licenses in the database.

 

 

 

 

 

 

Click on Request license and for now obtain 60 days license. Alternatively you can purchase per Instance with single user or Per Instance with Unlimited users:


 

 

 

Click on the request button and the license will be automatically acquired and updates in the database.

 

 

 

 

 

 

 


 

 

 

Now you can see the service licensed and the actual license content. You can export it and keep it safe. If you did not purchase your license online and have obtained it through email, use the Import License button to assign the license to the appropriate service.

 

 

 

You need to install the ASDC service per each installed SSAS Instance. Do the same step for any additional installed services.

 

e Service Manager

The manager will detect all installed SSAS instances and show them in the grid. You can install the AS Data Collection Service one instance at a time by clicking the Install button in the middle section. Alternatively you can click Install All and install the AS Data Collection Service on all SSAS instances. Use the Start All and Stop All to control all installed services at once. Review the Log file at the bottom if the installation does not go successfully. If you are installing in a failover clustering environment or if you have configured the services account in the Services Credentials step the username and password to be used as security credentials for the services will be used. If you want to change the service account credentials, please use go back to the Failover Clustering or Services Credentials and change it there. Once you apply the changes there, you will be prompted to restart all services so the new settings will be in affect. Alternatively you

can use the windows services applet in the control panel. Default installation logon user for the service is

Local System. Please make sure that Local System has administrative rights to the SSAS instance. Click Install All to install AS Data Collection Service (InstanceName) for each detected SSAS Instance.


 

 

 

 

 

Click Start All to start all installed AS Data Collection Service (InstanceName) services.

 


f Automatic Updates

Click on the automatic updates and you will be presented with the settings that manage how the services auto-update. You can disable the automatic updates by unchecking the check box Check for Update. You can specify the time of day when you want the services to self update. If your company requires version control and you want to control which versions of the services are updated, then you need to change the Update URL to your internal IIS endpoint where you can deploy the latest updates provided by ESS. All installed services are updated to the same version at the same time. If you want to manually check for an update, click Check for Update button. If there is a new version, it will show on the right and the button will change to Download and Install. Check the logs in the bottom Results panel.

 

 

 d 

 

g)  Failover Clustering

If you are installing the Manager in a Windows 2003 or Windows 2008 Failover Clustering Environment, then you will be positioned at the Failover Clustering step where you have to specify the Cluster related settings. For this walkthrough we are not setting up the Manager in a cluster environment. However we recommend that you create a resource AD account to be used by the SSDC Manager and install each service to run in the security context of this account. The SSDC Manager will install the necessary cluster resources and setup proper dependencies. Call us for details.


 

h Client Application

Finally, your services are configured, installed, licensed and ready to use all logging data to a centralized database. Now is time to start the client. You can start it by clicking on the first link which starts the Click- Once deployment. This is the recommended method. No installation necessary and it is self updating when new versions are available. For companies that require version control the link points to an internal IIS server. Alternatively you can use the second link that starts the MSI package. This method requires that when new versions are available, they are installed manually. More details on how to connect and monitor your SSAS instance are in the next section of this document.

 

 

 

 


 


 

Companion for MS SQL Server Client, v6.0

 

 

 

Starting or installing the Client

 

·    The client application can be installed from an .msi file. This installation makes the application available to all users that are sharing the machine, however, it will also require that any updates are updated manually.

·    The client application can also be started from the SSDC Manager application at the Client Application

Stage

·    The client can also be started from our or your internal IIS deployment web site:

http://www.entsoftsol.com/downloads/Setups/CompanionForMSAnalysisServer/

 

For the purposes of this walkthrough, we will use the recommended method starting the client from our deployment web site.

 

Once you click on the link to start the application it will first determine:

 

·    If it is not installed it will install the latest version

·    If it already installed it will check for a new version and prompt you to update it

 

 

 

 

Since this is first time that we are installing the client, you are promptly with the application installation windows allowing you to verify the publisher and decide if you want to proceed with the installation.

 

 

 

 

Click on the Publisher’s link and confirm that the singing certificate is valid and issued to Enterprise Software Solutions, LLC. Do not proceed with the installation if the certificate is not valid or not issued to Enterprise Software Solutions, LLC.

 

 

 

The client installs and automatically starts.

 

 


 


 

Registering a server

 

Once you see the client running, right click at the root node and select Add Analysis Server

 

 

 

 

 

Type in the name of your SSAS server instance and click Connect

 

 

 

 

 

 

The client automatically connects to the installed SQL Server Data Collection and registers the instance with the client.


 

 

 

If you have not obtained a license in the SSDC Manager, you will be prompted to either provide a license or obtain one over the internet (60 days full trial or purchase a license).

 

 

This completes the initial installation. Next, install the SSDC Manager on other SSAS servers and the client on other workstations and servers. Monitor and work with all of your SSAS instances from a centralized application.

 

 

Agentless Deployment

 

The service can also be installed on a machine other that the monitored server. The WMI and Performance Monitor ports need to be opened on the monitored server.  The installation is manual so please, contact use for assistance on making this configuration.  The installation uses the service EXE and the .NET “installutil.exe” tool to install the service.  The service configuration file may also need to be adjusted to point to the monitored server.  

 

 

Rate this article:
No rating
Comments (0)Number of views (5940)
Print
Constantin Ivanov
>

Constantin Ivanov

Constantin "Koce" Ivanov has been a developer for over 20 years. He is known in the industry for his MS SQL and SSAS skills. He is also a .Net and Web expert. In 1996, he has achieved his Microsoft Certifications (MSCE, MCSD, MCDBA, MCT). He has been working with MS SQL since 1996 and

Other posts by Constantin Ivanov
Contact author Full biography

Please login or register to post comments.