Setting up a Microsoft SQL Server database

Before you begin

Make sure that you have downloaded the MSSQL_25101_DBDUMP.zip file from BMC Electronic Product Distribution (EPD).

To prepare your Microsoft SQL Server database

1. Install the Microsoft SQL Server database on a remote server that is networked to the Kubernetes cluster on which you plan to install BMC Helix Innovation Suite.

   Important

   Make sure that your database is configured to use Unicode.

2. Create an instance of the database.
3. Set your SQL Server connections to allow TCP/IP:
   1. Open the SQL Server Configuration Manager.
   2. Click Network Configuration for your SQL Server instance.
   3. Make sure that TCP/IP is enabled.
   4. IP Addresses
   5. Restart all SQL Server services to effect this change.
4. Make sure that your database can accept network communication with the parameters entered in the installation.
   The BMC Helix Innovation Suite server uses JDBC to communicate with the database.

To configure a database that is not configured to extend automatically

1. Set the AR System data file size to at least 2 GB for one BMC Helix Service Management (ITSM) application.
2. Set the log file size to 2 GB or larger.

To configure a Microsoft SQL Server database

1. Unzip the MSSQL_25101_DBDUMP.zip file that you downloaded from EPD.
   The ARSystem-25101-RTM.Bak database dump file is displayed.
2. Unzip the SmartReporting_DBDUMP.zip file that you downloaded from EPD.
   The SmartReporting-210503HF-202305181219.Bak database dump file is displayed.

3. Download and update the following files:

   File	Description
   Drop_Innovation_Suite_database_and_users.sql	Use this file to clean up any existing AR System and BMC Helix ITSM: Smart Reporting databases and the ARAdmin and ReportAdmin users in your environment.
   Innovation_Suite_schema_creation.sql	In this file, update the RESTORE DATABASE [ARSystem] and RESTORE DATABASE [SmartReporting] commands with the paths where you have downloaded the ARSystem-25101-RTM.Bak file. For example, if you have downloaded the ARSystem-25101-RTM.Bak at C:\BACKUP\ location, update the RESTORE DATABASE [ARSystem] command as follows: RESTORE DATABASE [ARSystem] FROM  DISK = N'C:\BACKUP\MSSQL_ARSystem-23304-RTM.Bak' WITH  FILE = 1,  MOVE N'ARSystem_data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ARSys.mdf',  MOVE N'ARSystem_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ARSysLog.ldf',  NOUNLOAD,  STATS = 5

4. In your database server, copy the Drop_Innovation_Suite_database_and_users.sql, and Innovation_Suite_schema_creation.sql files to the required location such as C:\BACKUP\.

5. (Optional) Execute the Drop_Innovation_Suite_database_and_users.sql file by using the following command:

   sqlcmd -i Drop_Innovation_Suite_database_and_users.sql

6. Execute the Innovation_Suite_schema_creation.sql file by using the following command:

   sqlcmd -i Innovation_Suite_schema_creation.sql

Considerations for Microsoft SQL Server 2016 (SP2), 2017, or 2019

If you are using Microsoft SQL Server 2016 (SP2), 2017, or 2019 database you must create synonyms for the trace_xe_action_map and trace_xe_event_map system procedure before Running the database consistency checker utility.

To create the synonyms

1. Open SQL Server Management Studio (of relevant version).
2. Open a new query window.
3. Choose the BMC Helix Innovation Suite database.

4. Execute the following SQL statements:

   create synonym trace_xe_action_map for sys.trace_xe_action_map;

   create synonym trace_xe_event_map for sys.trace_xe_event_map;

Upgrading your Microsoft SQL Server to version 2016 (SP2), 2017, or 2019

To upgrade your Microsoft SQL Server to version 2016 (SP2), 2017, or 2019, consider the following requirements:

• You can upgrade to Microsoft SQL Server 2016 (SP2) from the following versions:
  • Microsoft SQL Server 2005 SP4 or later
  • Microsoft SQL Server 2008 SP3 or later 
  • Microsoft SQL Server 2008 R2 SP2 or later
  • Microsoft SQL Server 2012 SP1 or later
  • Microsoft SQL Server 2014 SP1 or later
• You can upgrade to Microsoft SQL Server 2017 from the following versions:
  • Microsoft SQL Server 2005 SP4 or later
  • Microsoft SQL Server 2008 SP3 or later 
  • Microsoft SQL Server 2008 R2 SP2 or later
  • Microsoft SQL Server 2012 SP1 or later
  • Microsoft SQL Server 2014 SP1 or later
  • Microsoft SQL Server 2016 SP2 or later
• You can upgrade to Microsoft SQL Server 2019 from the following versions:
  • Microsoft SQL Server 2005 SP4 or later
  • Microsoft SQL Server 2008 SP3 or later 
  • Microsoft SQL Server 2008 R2 SP2 or later
  • Microsoft SQL Server 2012 SP1 or later
  • Microsoft SQL Server 2014 SP1 or later
  • Microsoft SQL Server 2016 SP2
  • Microsoft SQL Server 2017 or later
• As a prerequisite you need .net Framework 3.5 Service Pack 1 or later. 
• For more reference information, refer to the following links:
  • https://msdn.microsoft.com/en-us/library/bb677622.aspx
  • https://msdn.microsoft.com/en-us/library/ms143506.aspx

Configuring Microsoft SQL AlwaysOn solution with AR System

You can configure Microsoft SQL AlwaysOn solution with AR System.

BMC has tested the Microsoft SQL AlwaysOn solution where three nodes, each running a single instance of SQL Server, participate in a single Windows Server Failover Cluster (WSFC) that spans across two data centers. This is a non-shared solution, and the nodes do not share any storage with another node. Each node runs a single instance of SQL Server and has its own copy of the data.

To configure Microsoft SQL AlwaysOn with AR System during installation

1. During installation, you must select the IS_DATABASE_ALWAYS_ON check box so that the AR System server can connect to the SQL server Always on solution. Also, for AR System DB host name and for AR System DB instance name specify the SQL AlwayOn listener details.
2. Complete the BMC Helix Service Management installation.
3. Add the AR System database to the Availability databases.
   This will create a copy of AR System database in all the instances of AlwaysOn solution.
4. Create an AR System database user on remaining instances (Refer to KB https://support.microsoft.com/en-us/kb/918992).

To configure Microsoft SQL AlwaysOn with AR System manually

1. Update SQL-Server-Always-On parameter to T. Learn about the parameter at Configuration settings S-Z.

2. Update Db-Host-Name parameter with the SQL AlwayOn listener details.
3. Restart theBMC Helix Innovation Suite container.
4. Add the AR System database to the Availability databases. This will create a copy of AR System database in all the instances of AlwaysOn solution.
5. Create an AR system database user on remaining instances (Refer to KB https://support.microsoft.com/en-us/kb/918992).

Enabling Transparent Data Encryption

You can enable Transparent Data Encryption (TDE) on your Microsoft SQL Server database. TDE (Transparent Data Encryption) provides an automatic mechanism to encrypt data, ensuring protection of your sensitive information. TDE works seamlessly for services and does not require any changes to your application. However, we recommend performing this operation during off-business hours for optimal results.

Consider the following points before you enable TDE on your database:

• Performance—TDE introduces minimal overhead due to the encryption and decryption processes. However, depending on your data volume, you may experience a slight performance impact, particularly during high input or output operations.
  We recommend conducting tests in your non-production environment to assess any potential effects on application performance.
• Backup and key management—Make sure to back up your keys and certificates and manage them securely. Losing access to these keys could make the encrypted data unreadable.
• Testing—Although TDE is designed to operate transparently, we advise testing this feature in your non-production environment before enabling it in production. This allows you to verify that there are no negative impacts on your operations.
• Database backup size—The size of your database backups will increase after enabling TDE.
• Key or certificate security for restores—Make sure you secure the keys and certificates for every restore operation in a new server.
   

Where to go from here