This guide provides information about moving interworks.cloud Platform Database from one SQL Server instance to another instance.

Intro

Moving a SQL Server database from one server to another is, in general, a simple task accomplished using one of the following ways:

• detach the database and attach it on the other server
• backup database and restore  it on the other server

But, there are a few things than need to be taken into consideration when the moving process has to do with interworks.cloud Platform Database.

interworks.cloud Platform installation constitutes of several applications (BSS, Storefront, Web Services, Windows Services) deployed usually on multiple application servers. All applications are connected with interworks.cloud Platform Database in order to store/retrieve necessary data and deliver all business actions supported by the Platform. Any changes made to the interworks.cloud Platform Database deployment require an update of the database access configuration (connection string) for all interworks.cloud Platform applications for all application servers.

During the execution of any move process related to the database, it is crucial to keep configuration changes to a minimum and avoid any related failures.

Some additional considerations include:

• keeping the consistency of the existing SQL Server users, permissions, and role assignments between the existing and the new SQL Server instance
• ensure that interworks.cloud Platform Licensing is updated with the configuration changes in order to avoid lock on the installation
• ensure that next releases of interworks.cloud Platform will not have any issues

The next paragraphs provide a step-by-step process for the execution of the moving process for interworks.cloud Platform Database. The provided process ensures that all actions will be run smoothly and will not cause any further issues upon completion.

The provided process does not include any details for the installation/configuration of a new SQL Server instance, assuming that this task is already executed.

Preparation Phase

In this phase, we provide the steps that could be executed without affecting the availability of the Live environment.

Collect Credentials and Licensing Details

It is necessary to collect the following info that will be necessary during the execution of the current process:

• access details for Live environment's SQL Server(s) (sql user and RDP access)
• access details for new SQL Server instance(s) (sql user and RDP access)
• access details for all application servers
• interworks.cloud Platform License Key provided for activation of your installation. Please contact support@interworks.cloud in case you can not find this info.

Create Initial Databases

In order to prepare the new environment and validate the configuration, it is necessary to create an initial interworks.cloud Platform Database in a new SQL Server instance.

interworks.cloud Platform utilizes three databases that need to be present in the new SQL Server instance. Given that you select the default database name iCPBSS on the initial interworks.cloud Platform installation the databases that need to be present are:

• iCPBSS - Main Database
• iCPBSSLogs - Logs Database
• iCRM2Config - Config Database

Given that:

• the databases will not be used immediately by any interworks.cloud Platform applications
• we just want to prepare our new environment

we can use any database backup from the Live environment's databases and restore them in the new SQL Server instance.

Create Identical Sql Users, Track User Permissions and Role Assignments

In order to keep sql user permission issues to a minimum, it is necessary to export the sql users from the Live environment and create them to new SQL Server instance.

The following export/import process ensures that all necessary requirements are met.

Export SQL Server Users

In order to export the sql users from Live environment the following sql script should be executed on Live environment's SQL Server instance

Then, we can execute the following sql script in order to generate queries for creating the sql user with all the necessary details:

exec sp_help_revlogin

/* sp_help_revlogin script 
** Generated xxx xx xxxx  x:xxPM on xxxxxxxx */
 
-- Login: DummyLoginName1
CREATE LOGIN [DummyLoginName1] WITH PASSWORD = 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx HASHED, SID = 0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN 

-- Login: DummyLoginName2
CREATE LOGIN [DummyLoginName2] WITH PASSWORD = 
xxxxxxxxxxxxxxxxx HASHED, SID = 0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN 
 
-- Login: DummyDomain\IntegratedSecurityUser1
CREATE LOGIN [DummyDomain\IntegratedSecurityUser1] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: DummyDomain\IntegratedSecurityUser2
CREATE LOGIN [DummyDomain\IntegratedSecurityUser2] FROM WINDOWS WITH DEFAULT_DATABASE = [xxxxxx]

-- Login: DummyDomain\IntegratedSecurityUser3
CREATE LOGIN [DummyDomain\IntegratedSecurityUser3] FROM WINDOWS WITH DEFAULT_DATABASE = [xxxxxx]

Create SQL Server Users

From the output query generated from the previous step, select the queries for the sql users that are used from interworks.cloud Platform installation.

Execute the selected queries and create the sql users at the new SQL Server instance.

Record SQL Server User Permissions and Role Assignments

User permissions and role assignments need to be recorded from the Live environment's SQL Server instance in order to be set to a new SQL Server instance.

Navigate to SQL Server > Security > Logins and note the users that referenced by the output query of the previous step.

For each of those users, right-click > properties and take note of the Server Roles and User Mappings.

Configure interworks.cloud Platform Installer

interworks.cloud Platform Installer should be installed and configured in new SQL Server in order to enable future upgrades of interworks.cloud Platform.

In case of Failover Cluster/Always-On configuration, the installation process should be executed for every SQL Server in the same group.

Install interworks.cloud Platform Installer

Download interworks.cloud Platform Installer available at https://downloads.interworkscloud.net/files/releases/installer/3.0.0/Cloud360Installer30.msi and install on the Server that new SQL Server instance is available.

Configure interworks.cloud Platform Installer

The configuration of interworks.cloud Platform Installer is available at {InstallerPath}\Cloud360.Installer.exe.config, were {InstallerPath} is the path selected for installation of interworks.cloud Platform Installer.

In order to keep consistency with the configuration of old SQL Server instance, is necessary to:

1. copy Cloud360.Installer.exe.config file from the old SQL Server to the new one
2. change Cloud360.Installer.exe.config file in new SQL Server to match the new environment. Configuration changes are necessary for the following keys
   • Database, that should match the name of the interworks.cloud Platform database
   • InstallFolder, that should match the root file path that the database files will be placed
   • DBBssMDFLocation, that should match the file path that the .mdf database file with be placed
   • DBBssLDFLocation, that should match the file path that the .ldf database file with be placed
   • DatabaseServer, that should be changed to the name of the new SQL Server instance name
   • InstallConnectionString, that should be changed in order Server property match to the name of the new SQL Server instance name
     
     

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<configSections>
		<section name="installer" type="Cloud360.Installer.Configuration.InstallerSection, Cloud360.Installer.Core"/>
	</configSections>
	<installer>
  <!-- Installed components -->
  <components>
   <component id="5ecf1a80-8a46-440f-9708-cf6f742f786a">
    <settings>
     <add key="ApplicationName" value="" />
     <add key="ComponentCode" value="bss database" />
     <add key="ComponentName" value="BSS Database" />
     <add key="ComponentDescription" value="Interworks Cloud Platform BSS Database is a prerequisite for the installation of Interworks Cloud Platform BSS Server." />
     <add key="Release" value="3.27.0" />
     <add key="Instance" value="" />
     <add key="InstallFolder" value="C:\CloudPlatform\BSS Database" />
     <add key="Installer" value="Cloud360-BssDatabase-Upgrade-3.27.0.zip" />
     <add key="InstallerType" value="Cloud360.Setup.BssDatabase3270" />
     <add key="InstallerPath" value="setup\setup.dll" />
     <add key="Database" value="iCPBSS" />
     <add key="NewDatabase" value="True" />
     <add key="DBBssMDFLocation" value="C:\CloudPlatform\BSS Database\DBData" />
     <add key="DBBssLDFLocation" value="C:\CloudPlatform\BSS Database\DBLogs" />
     <add key="DBBssDataBaseUsername" value="cloudadmin" />
     <add key="NewDatabaseUser" value="True" />
     <add key="DatabaseServer" value="localhost" />
     <add key="InstallConnectionString" value="Server=localhost;Database=master;Integrated Security=SSPI;" />
    </settings>
   </component>
  </components>
  <!-- Studio settings -->
  <settings>
   <add key="Log.FileName" value="Cloud360.Installer.log" />
   <add key="Web.Service" value="https://downloads.interworkscloud.net/Services/InstallerService.asmx" />
   <add key="Web.AutoCheck" value="false" />
   <add key="Web.Proxy.UseProxy" value="false" />
   <add key="Web.Proxy.Address" value="" />
   <add key="Web.Proxy.UserName" value="" />
   <add key="Web.Proxy.Password" value="" />
  </settings>
 </installer>
</configuration>

Service Broker feature is utilized by the interworks.cloud Platform for the execution of asynchronous tasks, e.g. Tenant Resellers, Back-ordering, etc

In order to successfully enable the Service Broker feature for interworks.cloud Platform, we need to ensure that:

• the database owner of the Main Database (iCPBSS) is 'sa' user
• service broker is enabled for Main Database (iCPBSS)

The following sql script could be used to check and fix these requirements:

DECLARE @DatabaseName varchar(max) = 'iCPBSS'

-- set 'sa' user as database owner
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name=@DatabaseName AND suser_sname( owner_sid )='sa')
BEGIN
		PRINT 'Change Database Owner to ''sa'''
        EXEC ('USE [' + @DatabaseName + '];EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;');
END


-- enable service broker if not enabled
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name=@DatabaseName AND is_broker_enabled = 1)
BEGIN
	PRINT 'Enabling service broker..'
	EXEC('ALTER DATABASE [' + @DatabaseName + '] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;')

	PRINT 'Reset service broker dialog pools'
	EXEC('DELETE [' + @DatabaseName + '].sb.DialogPool;')
END

In case of Always-On configuration, the above sql script should be executed prior addition of the database to Availability Group otherwise the following error will occur:

The operation cannot be performed on database "iCPBSS" because it is involved in a database mirroring session or an availability group. 
Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1468)

If the addition to Availability Group is already made, then execution of the following procedure is necessary:

1. remove the database from the availability group
2. execute the above sql script to enable Service broker on the primary node
3. collect db backup from the primary node
4. restore it to the secondary node(s)
5. add the database to the availability group

Network Configurations

interworks.cloud Platform Database should be accessible from all application servers that have interworks.cloud Platform component (Administration, BSS, Storefront).

In this step is necessary to verify that all application servers could connect to the new SQL Server instance.

The preferable way to accomplish this task is to use ODBC Data Sources application with SQL Server client driver available in every Windows server and try to connect to the target SQL Server instance.

Configure interworks.cloud Platform installer for all application servers

Similar to the changes made to {InstallerPath}\Cloud360.Installer.exe.config file at Configure interworks.cloud Platform Installer, an update of the installer configuration should be made to all application servers.

These changes can be made in the Live environment if there is no schedule to upgrade interworks.cloud Platform to a newer version via new installation or patch prior database moving process.

Configure interworks.cloud Platform applications connection strings

In this step, we focus on preparing the configuration changes for all interworks.cloud Platform applications that need to be switched to new SQL Server instance after we complete the final moving of the databases.

Keep in mind that no changes should be made to any configuration file of the Live environment at this phase.

The following table contains all files that need to be updated with the new connection string to the new SQL Server instance.

Check Billing Service execution and schedule

Billing Service execution is one of the most crucial processes of interworks.cloud Platform. It is necessary to ensure that our actions will not be made during Billing Service execution and will not affect the next Billing Service run.

In order to review Billing service schedule and total execution time we can use the following query:

USE [iCPBSS];
GO

SELECT * 
FROM tblBillingServiceCycles 
ORDER BY BillingStartedOn DESC
GO

The result contains details of the executions Billing Service performed for each day.

The most important columns for our review are BillingStartedOn and BillingFinishedOn

Based on BillingStartedOn column of the above results, we can clearly extract that billing service schedule is at 23:10 +02:00 every day.

Based on BillingFinishedOn column we can extract the following information:

• last billing service execution probably is still in progress as the first record has NULL value
• billing service execution is completed until 23:30 +02:00 for previous days

Reviewing records that reference previous month Billing service executions, you can find the days of the month that billing service needs more time to be completed as performs more billing actions (e.g. renew subscriptions, issue invoices). Usually, the last day of the month is the most heavy one.  It is better to avoid these days for performing a database switch process.

Identically, the best time to begin the database switching process is after billing service execution completes. Otherwise, it is better to take into consideration the next Billing service execution and keep a safe distance from the scheduled start.

Estimate downtime period

The execution of the next steps requires to set the Live environment unavailable for a period of time until we move the databases to new SQL Server instance and apply the necessary configuration changes.

The downtime period depends on the size of the database and the number of application servers that the configuration changes need to be applied.

In order to estimate the downtime period and schedule switching of databases you should take under consideration the following factors:

• Time required to collect fresh database backups from your Live environment
• Time required to copy the database backup to new SQL Server 
• Time required to restore the database backup to new SQL Server
• Time required to restore sql user permissions and role assignments using the preparation we made in Move SQL Server Users, User Permissions and Role Assignments
• Time required to update configuration files with new connection string using the preparation we made in Configure interworks.cloud Platform connection strings
• Billing Service schedule. You need to avoid scheduling of database switching during Billing Service execution and keep a safe distance from the next Billing service run. More details at Check Billing Service execution and schedule
  
  

Switching to new SQL Server

Using the experience from the previous steps and the pre-processing of necessary configuration changes, we are now ready to proceed with the database switch for interworks.cloud Platform.

The following paragraphs describe the steps necessary to complete the final switching of the database to new SQL Server instance.

Start Downtime - Stop all Websites and Windows Services

Stating with the switching process, all interworks.cloud Platform applications are required to be stopped from accessing the database.

Stop Websites

The following websites from all application servers need to be prevented from accessing the database:

• Administration
• BSS
• BSS Services
• Storefront V4

In order to safely prevent database access from interworks.cloud Platform websites is recommended to Stop them or Stop related Application Pools.

Stop Windows Services

interworks.cloud Platform Windows services are installed on the server(s) that the Administration component is installed.

Details on interworks.cloud Platform Windows services are available at Cloud Platform Window Services page.

All interworks.cloud Platform windows services should be stopped via  Administrative Tools > Services

Collect database backups

Create uncompressed database backups for the following databases from Live environment:

• Main Database (iCPBSS)
• Logs Database (iCPBSSLogs)

Take old databases Offline

Take all interworks.cloud Platform databases offline at old SQL Server instance in order to prevent any accidental access.

Copy database backups

Copy collected uncompressed database backups from the Live environment to new SQL Server in order to be used for restore procedure.

Restore databases

Use uncompressed database backups copied to new SQL Server and replace existing databases.

Check SQL Server Users Permissions and Role Assignments

Using the collected info from Record SQL Server User Permissions and Role step, check all sql user permissions and role assignments and adapt them to match those of Live's environment SQL Server instance.

Enable Service Broker

Execute sql script available at Service Broker Enablement

Update Connection Strings

Update interworks.cloud Platform applications configuration with a new connection string, i.e. apply configuration changes prepared in Configure interworks.cloud Platform connection strings

End Downtime - Start all Websites and Windows Services

Start all websites and windows services that stopped on steps:

• Stop Websites

• Stop Windows Services

Reset and Reactivate Licensing

Moving from SQL Server instance to another, will cause automatic deactivation of your installation by interworks.cloud Platform Licensing process.

You need to reset the installation's license and reactivate it. A valid interworks.cloud Platform license key should be available in order to continue with the activation of the installation.

Resetting of installation's license can be made with the following sql query executed at Main Database (iCPBSS):

USE [iCPBSS]
GO

UPDATE System
SET AuthData=NULL
GO

Now, we can reactivate the installation through Administration application, by selecting  link

The selection of the link, brings up the following modal in order to provide the necessary info regarding the activation of our installation.

All fields should be completed and a valid license key should be provided in the 'License Key' field.

By selecting the 'Activate' button, the activation process is started. In case of a valid license key and a successful activation, our installation has the following status

We can now start using our interworks.cloud Platform applications (BSS, Storefront, etc).

Configure interworks.cloud Platform installer for all application servers

In case installer configurations were not made during the preparation phase of Configure interworks.cloud Platform installer for all application servers, we need to make them now in order to enable future upgrades of the interworks.cloud Platform.