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