Migrate Application Database

Introduction

It’s extremely easy to migrate Kepion applications from one environment to another. In this article, let's take a look at how to attach an application database and set up the necessary permissions in the new environment.

Example

Step 1. Backup the database in SQL Server Management Studio in the source environment.

Step 2. Restore the backup file to the target environment.

Tip: If you are migrating a database from a newer SQL instance to an older version, please refer to this article for backup and restoration information.

Step 3. Open Kepion Planning in your browser. Navigate to the APPLICATION tab under ADMINISTRATOR. Click Attach.

Step 4. In the pop-out window, enter the name or IP address of the SQL server in the target environment, and click Discover. Then you should see a list of databases that can be attached to Kepion. Select your database and click OK.

Tip: If you can't discover any databases, check out this troubleshooting article for possible reasons why.

Step 5. Now you should be able to find your application in the list below. If the application icon shows a green check mark, it means your application is online now. Click Deploy in the ribbon to generate an OLAP cube.

Step 6. Once the deployment is done, you are good to go!

Permissions

When you migrate Kepion application databases from one environment to another, please make sure the Kepion SI and SSAS SI in the new environment have the right permissions as well.

For each Kepion application database, apply the following:

Replace <KEPION_APPLICATION_DATABASE>, <KEPION_SI> and <SSAS_SI> with the respective context.

USE [<KEPION_APPLICATION_DATABASE>]
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];

IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<SSAS_SI>')
BEGIN
CREATE USER [<SSAS_SI>] FOR LOGIN [<SSAS_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<SSAS_SI>] WITH DEFAULT_SCHEMA = [dbo];

EXEC sp_addrolemember N'db_datareader', N'<SSAS SI>' 
EXEC sp_addrolemember N'db_owner', N'<KEPION_SI>'

Or you can manually map the Service Identities to each Kepion Application Database through the User Mapping tab, if you right-click the login and select Properties.

Be sure to select db_owner for each database mapped to the Kepion SI and db_datareader for each one mapped to the SSAS SI. Ensure as well that the Default Schema for both is set to dbo.

Note: This process needs to be done for every database added to your environment after setup. For more information, check out the Kepion Setup Guide.