Introduction
In the first part of our setup guide, you will configure SQL and SSAS. Complete the entire guide before you move on to installing our software.
Configure the SQL server
Kepion SI - Ceate login
1. Launch SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
Note: Refer to the Permissions section in the Overview to determine the credential to use for your Kepion SI.
2. Expand Security, right-click on Logins, and select New Login.
3. Enter your Kepion SI credentials for the Login name.
Note: The username should be in the form of DOMAIN\UserName. For example, CORP\KepionSI.
Kepion SI - Set permissions
Next, we need to set the permissions needed by our Kepion SI on both the server and database levels. If you currently do not have any Kepion databases in your environment, you can return to the Database Permissions section later.
Server permissions
1. From the Select a page pane, go to Server Roles and select dbcreator to enable it. Enabling dbcreator allows the Kepion SI to create new applications in the Kepion UI.
Alternatively, you can use the following SQL script to grant the permission instead:
USE MASTER;
EXEC sp_addsrvrolemember N'<KEPION_SI>', N'dbcreator'
Attention: Replace <KEPION_SI> with your credentials in all scripts, including the one above.
2. Right-click the server and select New Query.
3. (Only for Kepion versions below 6.1.230000.0) Copy and paste the script below into the query window and select Execute. This script grants the Kepion SI the VIEW_SERVER_STATE permission to cache database assets on the Web Server.
USE MASTER;
GRANT VIEW SERVER STATE TO [<KEPION_SI>];
GO
Attention: Granting the VIEW_SERVER_STATE permission exposes your server and information to unnecessary risk. Refer to this article for why newer versions of Kepion do not require this permission.
4. Copy and paste the script below into the query window and select Execute. This script sets the Default Schema to dbo, so that all databases created under this service identity are generated correctly.
USE MASTER;
GO
IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<KEPION_SI>')
BEGIN
CREATE USER [<KEPION_SI>] FOR LOGIN [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];
Database permissions
Note: If you don't have any Kepion databases, you can skip this step. We will create the database Kepion_System later, with the required permissions by default.
In addition to configuring the login at the server level, we'll also need to ensure the Kepion SI is configured properly at an individual database level. This will require:
- Mapping users to the Kepion databases currently in your environment.
- Setting their permissions to db_owner.
- Ensuring the Default Schema is set to dbo.
1. In SSMS, under Security > Logins, right-click your Kepion SI and select Properties.
2. Navigate to the User Mapping tab.
3. In the Map column, select the checkbox of the Kepion database. In the lower box, select db_owner.
4. Select OK.
You can also perform the same actions with the following script instead:
USE [<KEPION_APPLICATION_DATABASE>]
IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<KEPION_SI>')
BEGIN
CREATE USER [<KEPION_SI>] FOR LOGIN [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];
EXEC sp_addrolemember N'db_owner', N'<KEPION_SI>'
Note: You must configure these permissions and default schema with any future databases you add.
SSAS SI - Create login
As mentioned before, the SSAS SI only needs permissions on the SQL Server. This is for both proactive caching and data processing during the Deploy step to work correctly.
1. Open the Services App.
2. Identify the SSAS SI by locating the SQL Analysis Services Server.
3. Right-click the server and select Properties.
4. Go to the Log On tab and copy the account name.
5. Return to SSMS.
5. Expand Security, right-click Logins, and select New login.
6. Paste the SSAS SI account you copied in Step 3 to the Login name field.
7. Select OK to close the window.
SSAS SI - Set permissions
We have to configure permissions for the SSAS SI at both the server and database levels.
Server permissions
1. Open SSMS, connect to the database engine and the server where Kepion application databases will be restored to.
2. Open a new query window.
3. Run the following SQL statements to grant the required permissions. Be sure to replace <SSAS_SI> with the respective service identity:
USE MASTER;
GO
GRANT ALTER TRACE TO [<SSAS_SI>];
GRANT CONNECT SQL TO [<SSAS_SI>];
GRANT VIEW ANY DATABASE TO [<SSAS_SI>];
Database permissions
If this is your first time setting up Kepion, you likely do not have any application databases. We recommend you proceed with the rest of the article and refer to part three of the setup guide once you have Kepion applications.
Restrict SQL memory
Restricting the amount of memory used by the SQL and SSAS Servers is important to ensure a predictable and stable environment. Make sure to follow our recommendations below:
Production environment
| Number of Users | Web Server | SQL Server | SSAS Server |
|---|---|---|---|
| Small < 10 | 2+ GB | 4+ GB | 4+ GB |
| Medium < 50 | 4+ GB | 6+ GB | 6+ GB |
| Large > 50 | 6+ GB | 8+ GB | 8+ GB |
Development environment
| Number of Users | Web Server | SQL Server | SSAS Server |
|---|---|---|---|
| Small < 10 | 2+ GB | 3+ GB | 3+ GB |
1. In SSMS, right-click on the server in the Object Explorer pane and select Properties.
2. Select the Memory page and set the Maximum server memory field.
Tip: There are 1000 MB in 1 GB. For example, if you want to set 4 GB of memory, enter 4000.
Configure SSAS
Now that we have our Kepion SI and SSAS SI credentials with the necessary permissions to access the SQL Server, the next step is to configure our Kepion SI credentials to have administrator privileges on the SSAS Server.
Add Kepion SI to SSAS
1. Connect to the SSAS Server from SSMS.
2. Right-click on the server and select Properties.
3. Go to the Security page and select Add to include Kepion SI as an SSAS administrator.
4. Enter your Kepion SI.
5. Select OK.
(Optional) Incremental deployment
In instances where you cannot have Kepion SI as an admin on the SSAS server, please configure the following in each of your Kepion application SQL databases: Set the IncrementalOLAPDeploy to 1 in the table ApplicationSettings.
This instructs the Kepion Server to never drop the existing OLAP database when deploying changes to OLAP. Once this is set, please ensure Kepion SI has admin rights to each of the OLAP databases associated with a Kepion application.
If this is not a concern for your environment, feel free to skip this step.
Restrict SSAS memory
Note: Please refer to the memory configuration recommendations here.
1. In SSMS, right-click the server in the Object Explorer pane and select Properties.
2. Go to the General page and configure the Value column for Memory \ TotalMemoryLimit.
Tip: This field is entered as a percentage of the available memory on the server. For example, if your server has 20 GB of memory, you can use "20" to represent 20% (or 4GB) of 20 GB.
Next Steps
In the first part of this guide, we have configured and set up your SQL and SSAS Servers to run Kepion. All that's left is to install our software.
As mentioned in the introduction, we strongly recommend most users follow the standard installation procedure. It's a simple and easy automatic installation that will, for the most part, set up Kepion for you. We do have instructions for manual and multitenant installations for those who need to have more control of the installation process or set up multiple users on a single server.