Upgrade SQL Server or Install a New SQL Server

This article is intended to provide basic guidance for an in-place SQL server upgrade and/or migrating the Enterprise database to a new SQL server instance. If you need additional assistance, Microsoft's website offers the must up-to-date information. This process is outside the scope of Momentus support but we may be able to assist on a limited basis. If needed, you can submit a ticket with detailed information regarding the problem you are experiencing and we will try to assist.

Hardware & Software Minimum Requirements

Review the Technology Guidelines for SQL server requirements for the Momentus Enterprise database. 

In-Place SQL Server Upgrade

When you upgrade SQL server, the previous SQL server version is overwritten and no longer exists on your computer. Before you upgrade, back up SQL server databases and other objects associated with the previous SQL server instance.

Refer to this information from Microsoft for guidance on an in-place SQL server upgrade:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server-using-the-installation-wizard-setup?view=sql-server-2017

After you have upgraded the SQL server, change the compatibility level on the Momentus Enterprise database to the current version and set the Model database to SIMPLE recovery.

Migrate the Enterprise Database to a New SQL Server Instance

Follow the instructions included with the Microsoft SQL server documentation to install Microsoft SQL server. Ensure the SQL Server FullText Search service is installed and running prior to loading the Momentus Enterprise database. 

Momentus recommends you restore a backup copy of the Enterprise database on the new SQL server. Once the database is restored, you need to delete users from the restored database and add proxy users to the SQL server.

Once the migration process is complete, Momentus recommends testing all processes to ensure everything works as expected. Once everything is confirmed to be working, you can schedule the production cut over.

Backup the Enterprise Database

1. Open SQL Server Management Studio.
2. Right-click on Databases and select Tasks -> Back Up. The Back Up Database screen opens. The Database field defaults to the database you are backing up. You can use the default settings on this screen.
3. Choose Database for Backup component.
4. Choose Disk in the Back up to drop-down in the Destination section. The destination defaults to the standard SQL backup location. To backup to a different location:
   • Choose Remove.
   • Select Add. The Select Backup Destination screen opens.
   • Select where to place the backup.
   • Enter a name for the backup into the File name field.
   • Click OK to return to the Select Backup Destination screen.
   • Click OK to return to the Back Up database screen.
5. Click OK to begin the back up.

For more information on backing up a SQL database: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15

Restore the Enterprise Database on the New SQL Server 

1. Open SQL Server Management Studio.
2. Right-click on Databases and select Restore Database. The Restore Database screen opens.
3. Enter the database name into the the Database field in the Destination section.
4. Click the ellipses button [...] next to the Device field in the Source section. The Select backup devices screen opens.
5. Click the Add button.
6. Browse for the backup file.
7. Click OK.
8. Click OK on the Select backup devices screen. The file displays in the bottom section of the Restore Database screen. If you need to restore the database to a different location other than the default SQL path, proceed to step 9. Otherwise skip to step 12.
9. Click to the Files page. 
10. Click the ellipses […] button In the Restore As column.
11. Browse to the location for the database files on the server. Change only the path for each file. Do not change the file names or extensions. This path is the location of your database files. Make sure there is sufficient space available on the drive.
12. Click OK to begin the restore process.

For more information about restoring a SQL Database: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver15

Delete (Drop) Database Users on the New SQL Server

You can delete (drop) users from a restored database using two different processes. The first process is often used to delete a small number of users. The second process is often used to delete a large number of users. You can use whichever process best fits your needs.

 To delete a small number of users:

1. Open SQL Server Management Studio. You need to have SA or admin privileges.
2. Expand the database.
3. Expand Security (database not server).
4. Expand Users.
5. Select the user to delete.
6. Right-click and select Delete. Repeat steps 5 and 6 for each user you need to delete. You can delete any users except: dbo, guest, INFORMATION_SCHEMA, Master and sys.

To delete a large number of users:           

1. Open SQL Server Management Studio. You need to have SA or admin privileges.
2. Expand the database.
3. Expand Programmability.
4. Expand Stored Procedures.
5. Right-click on dbo_spx_SetEBMSusers.
6. Select Execute Stored Procedure.
7. Click OK on the screen that opens.
8. Select messages in the results screen. This gives you a list of users for drop, create and drop and create.
9. Highlight all the drop users.
10. Right-click and select Copy.
11. Paste into a text file. You can use Microsoft Notepad.
12. Remove the following users from the text document: dbo, guest, INFORMATION_SCHEMA, Master and sys
13. In the text document, click the Edit button.
14. Click Select All.
15. Click the Edit button.
16. Select Copy.
17. In SQL Server Management Studio, select the database.
18. Open a new query screen.
19. Paste the text file with the users to delete.
20. Execute the query. Only the users removed from the text document in step 12 (dbo, guest, INFORMATION_SCHEMA, Master and sys) remain.

After you execute the stored procedure, if you get a message that the user is not in the database, you can use the steps for deleting a small group of users to remove the user manually.

The latest Momentus Enterprise versions do not require you to add SQL users to the server level security. The only users you need to add to SQL server security are below. You need these users for Momentus authentication (SQL) or Active Directory/Windows authentication. Since you do not need to add users to server level security, this gives you a clean database.

Add Proxy Users

1. From within SQL Server Management Studio, expand server level Security.
2. Right-click and select New Login.
3. In the Login name field, enter USI76Admin. Login names in SQL Server are not case-sensitive.
4. Select the SQL Server authentication radio button.
5. In the Password and Confirm password fields, enter “ungerboeck”.
6. Uncheck the Enforce Password Policy checkbox.
7. Select User Mapping from the upper left corner of the screen.
8. In the top section of the User Mapping screen, check the Map checkbox beside the database name.
9. In the bottom section of the User Mapping screen, check the boxes next to DB_DDLADMIN, public and ebmsUSER roles.
10. Click the OK button to create the new user login.
11. Repeat steps 2-11 to create the remaining USI76@ proxy users using the required roles: 

    Proxy User Name	SQL Roles Required	Password
    usiadmin	dbo, ebmsUSER	ungerboeck
    USI76AUTH	Public, ebmsUSER	ungerboeck
    USI76INTERNAL	Public, ebmsUSER	ungerboeck
    USI76EXTERNAL	Public, ebmsUSER	ungerboeck

Active Directory/Windows Authentication

Active Directory groups is the easiest authentication to maintain within SQL server. You may want to review changing from Momentus Authentication to Active Directory/Windows Authentication in Enterprise when moving to a new SQL server. The main advantage to using Active Directory groups for Enterprise users is you only need to make a change to deactivate a user through Active Directory. Momentus recommends retiring the user in Enterprise; however, once the user is deactivated in Active Directory the user is not able to login to the network and is not able to login to Enterprise.

Once you add groups to Active Directory and you add the Enterprise users to the group, you can set them up in SQL server. When you move to a new SQL server instance, the groups are usually already set up. Generally, there are only a few groups you can easily add to the new SQL server. To add a group to SQL server, follow the same steps as you would to add a user except select search using the […] , select object type and mark group if not selected. Search for the group and select.

For the USI76@ proxy users, the Momentus Enterprise Login ID must match the network (Active Directory) login ID. Map the group to the database and set the role to ebmsuser. 

Additional information

When upgrading your SQL server or migrating your Enterprise database to a new SQL server instance, it is a good time to make sure you have a maintenance and backup plan in place. For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-ver15. A maintenance plan can enhance performance and overall user satisfaction. If you have additional questions about maintenance plans that you cannot find on Microsoft’s site, create a Momentus support ticket with detailed information regarding your questions and we can try to assist. 

If you are having problems logging in using USIADMIN with Momentus authentication:

1. Change the user passwords back to default (usiadmin, ungerboeck).
2. Run the below SQL statement. This resets v20 passwords back to default.

   UPDATE MM405_USER_MASTER_EXT
   SET MM405_SALT = '$2a$10$mWzrM7O3pHMVu/h.uBNuUu'
   , MM405_PASSWORD_HASH = 'Cf+JX5k5TcSIFTkiRSNdrg=='
   WHERE MM405_USER_ID = 'USI76AUTH'
   
   UPDATE MM405_USER_MASTER_EXT
   SET MM405_SALT = '$2a$10$mWzrM7O3pHMVu/h.uBNuUu'
   , MM405_PASSWORD_HASH = 'Cf+JX5k5TcSIFTkiRSNdrg=='
   WHERE MM405_USER_ID = 'USI76ADMIN'
   
   UPDATE MM405_USER_MASTER_EXT
   SET MM405_SALT = '$2a$10$mWzrM7O3pHMVu/h.uBNuUu'
   , MM405_PASSWORD_HASH = 'Cf+JX5k5TcSIFTkiRSNdrg=='
   WHERE MM405_USER_ID = 'USI76INTER'
   
   UPDATE MM405_USER_MASTER_EXT
   SET MM405_SALT = '$2a$10$mWzrM7O3pHMVu/h.uBNuUu'
   , MM405_PASSWORD_HASH = 'Cf+JX5k5TcSIFTkiRSNdrg=='
    WHERE MM405_USER_ID = 'USI76EXTER'

3. Log into Momentus Enterprise using usiadmin and change the passwords manually.