Moving @ptitude SQL databases

Summary

This article will explain the procedure of moving an @ptitude SQL database from one SQL Server to another. The procedure consists of four main steps:

• Stop application users/services
• Backup SQL Database
• Restore the SQL instance 
• Config changes on App Server to point from the old SQL instance name to a new SQL instance

Solution

Stop application users and services

1. Before starting, ensure that all users are logged out from the system.

2. Stop all the @ptitude Services (Transaction Server, Microlog Service, IMx service, ETC)

   These may be installed on an APP server or local computer.
   
   

   

Backup SQL Database

1. Once Users and Services have been stopped,  back up the MS SQL Database to a .BAK file.

   SKFUSER database consists of two files, skfuser.mdf, Skfuser.mdf
   
   

2. Log in to SQL Management Studio as a user with Backup database privilege. The sa user will have this privilege.
   
   
   
3. In Management Studio, under Object Explorer click + to open a database.
   
   
   
4. The default file location is:

   C:\Program Files\Microsoft SQL Server\MSSQLXXXX\MSSQL\DATA
   
   

   
5. To find the location of the database manually, right-click on the skfuser database and select Properties.
   
   
6. Under Database Properties, select Files. Scroll to locate the files.
   
   
   
7. Right-click on the skfuser database and select Tasks > Back Up... to open the Backup Options.
   
   
8. Select Media Options, check the Back up to the existing media set and Overwrite all existing backup sets options
   
   
   
   
9. At the bottom of the screen ,you can see the location where the file will be saved.
   
   
   
   
10. Once the backup is completed successfully, click OK.
    
    
    
    
11. To detach the database, in SQL Management Studio, right-click on the skfuser and select Tasks > Detach... 
    
     
    
    

12. Check Drop Connections to close any open threads.
    
    
    
    

13. Move the skfuser MDF/LDF to the new Server. The default file location is: 
    

    C:\Program Files\Microsoft SQL Server\MSSQLXXXX\MSSQL\DATA
    
    

    
    
    

    
    
    

14. Place these files in a location you will have access to, as these are the @ptitude database files you will need.

Restore the SQL instance

1. Once the skfuser mdf and ldf are placed on the server. Log in to SQL Management Studio
   as an sa user.
   

   Note: We recommend using the sa user or sa equivalent user.  Do not use a Domain Admin account, as this will attach the database as that user. @ptitude Analyst needs the owner to be sa.
   
   

2. To attach the database, right-click on Database and select Attach...
   
   
   
   
3. Select ADD
   
   
   
   
4. This will open the browser window to the default location for MS SQL.
   
   
   

5. Select skfuser.mdf to add the skfuser.ldf automatically and click OK.
   
   
   
   

6. The files are attached in the SQL Instance, but the database is not ready yet.

   
   
   
   

7. Run the Rebuild User Login name script. This script will rebuild the skfusers in the database and place them in the SQL Security settings.
   

   This script is available in the following folder on a computer that has SKF @ptitude Analyst installed:

   C:\Program Files (x86)\SKF-RS\SKF @ptitude Analyst\DBAssist\SQL Server

   Rebuild_loginname_username_links.sql

   The file is also attached in this article.
   
   Note: This is a .SQL file. 
   

8. Go to File > Open > File and select Rebuild_loginname_username_links.sql
   
   
   

9. Click Execute
   
   
   It will return the following screen:
   
   

10. Once the script has been run, the skfuser database is ready to use.
    Note: Starting with 9MR3, only two users are needed for @ptitude Analyst to run properly.
    They are skfuser1 and skfuserts1.
    
    There is a script that will remove the extra created users in the database and in SQL Security. The script can be found on the following path:
    C:\Program Files (x86)\SKF-RS\SKF @ptitude Analyst\DBAssist\SQL Server “Remove_Database_Users.sql
    
    

Config changes on App Server to point from the old SQL Instance name to new SQL Instance

1. Set the Clients to the correct database and App server using the SKF @ptitude Analyst Configuration Tool on each client computer.
   
   
2. Select Database > Database Type and click Manage...
   
   
   
   
3. In the newly open Manage Connections window showing the existing connection, click Edit.
   
   
   
4. The Edit button will allow you to change DB connect name. This is the connection to the new Database server.
   
   
5. Click Save, and a new test connection window will pop up.
   Note: The user name must be skfuser1 with the proper password. Do not use any other user, as they will not have access to all skuser tables and procedures.
   
   
   
   
6. Click OK.
   If the Database connection test passes, you will get the following screen.
   
   
   If the Database connection test fails, you will get the following error.
   
   
   To solve this, check the DB connection string.
   
   
7. If the SKF Services location has changed, then you will need to point the Client to the new location.
   
   In the SKF @ptitude Analyst Configuration Tool Client, Select this option if this computer will use a Transaction Server previously installed on another workstation.This option should not be confused with the database client. A database client can be a Transaction Server Host and vice versa (although it is recommended to make the database host the Transaction server host).
   
   
   
   

8. Server Name – Enter the machine name or IP address of the computer where the Transaction Server is installed. 

   Server Port – Enter the TCP Port used for the communication with the Transaction Server host. This must match the same port configured on the Transaction Server host. Make sure your firewall software does not block this port.

The client is now configured for the new database server.

Contacting SKF Technical Support Group

For further assistance please open a support case using the Technical Support group's self-help portal at www.skf.com/cm/tsg. Once your support case is submitted, a technician will contact you to begin working on your issue. For urgent issues we are available at these times by phone:

• Monday through Friday, 5:00 a.m. to 4 p.m. Pacific Time -
  Phone: [phone] within the US or [phone] outside the US.
• Monday through Friday, 8:00 a.m. to 4:00 p.m. Central European Time -
  Phone: [phone].
• Monday through Friday, 7:30 a.m. to 4:30 p.m. India Standard Time -
  Phone: [phone].