/
Timecard Plus Microsoft SQL Server Migration Guide

Timecard Plus Microsoft SQL Server Migration Guide

Nov 02, 2017

Introduction

This guide is intended to provide instruction on migrating the Timecard Plus databases to a new server. These instructions will need to be repeated and completed for each of the timecard plus databases: RtHTC and RtRNB. (noted by the [DB_NAME] placeholders throughout this guide)

If PayStub is being used, follow these steps for RtEPS, also. This DB might not be on the same SQL server as RtHTC and RtRNB and might be named RtEPS_HWY in some cases.

If you have any questions or problems, please contact RtVision Support (support@rtvision.com or 320-632-0760)

Backup the Database and move it to the new server.

  • On the "old" server, From the Start menu select All Programs ==> Microsoft SQL Server 20XX ==> SQL Server Management Studio (Express) from the Start menu.
  • Connect to the SQL Server using an Administrative Account.
  • Expand the Object Browser on the upper left side of the screen, and select the databases object. Right mouse click on the database you would like to backup and select Tasks
  • BACKUP
    • On the General page, click the Remove button in the Destination section.
    • Click the Add button in the Destination section and browse to the location that you would like to back up the database to.
  • After the location has been selected, click the OK button at the bottom of the screen to start the backup process. You should receive a success message when it has completed.
  • Copy this file over to the "new" server

Create the Database on the new server

  • On the "new" server, open Microsoft SQL Server 20XX ==> SQL Server Management Studio (Express) from the Start menu.
  • Click the Connect button, then click on the server name in the left frame.
  • Right click on Databases, select New Database
  • Fill in [DB_NAME] for the database name and Click the OK Button at the bottom.

Create the [DB_NAME] account for the Database

  • In SQL Server Management Studio, expand the left tree view under Security and right click the logins entry, select new login.
  • Enter [DB_NAME] for the login name.
  • Select SQL Server Authentication Radio Button and (Contact RtVision for the password) enter the password in the password and confirm password text boxes.
  • Uncheck the enforce password policy checkbox.
  • Select [DB_NAME] for the default database.
  • Select the User Mappings page on the left side of the screen and select the [DB_NAME] database from the list.
  • Click the OK button at the bottom to create the user.

Create the [DB_NAME]_Admin account for the Database

  • In SQL Server Management Studio, expand the left tree view under Security and right click the logins entry, select new login.
  • Enter [DB_NAME]_Admin for the login name.
  • Select SQL Server Authentication Radio Button and (Contact RtVision for the password) enter the password in the password and confirm password text boxes.
  • Uncheck the enforce password policy checkbox.
  • Select [DB_NAME] for the default database.
  • Select the User Mappings page on the left side of the screen and select the [DB_NAME] database from the list and ALSO select the db_owner entry under the Database Role Membership section at the bottom.
  • Click the OK button at the bottom to create the user.

Restore the Database on the new server

  • Right click [DB_NAME] database, select Tasks, select Restore, select Database
  • Mark the radio button labeled "From Device", then click the browse button
  • Click the Add button
  • Browse to the database backup from the previous step and click Ok
  • Check the Restore check box in the Select the Backup Sets to Restore area at the bottom.
  • Click on the Options page (left side), then select Overwrite the existing database and click Ok. You should receive a restore was successful message when complete. Click the OK button.

Fix the issue of Orphaned Users on the SQL Database

  • Right click on the [DB_NAME] database and select New Query and type in the following commands
    • EXEC sp_change_users_login 'Auto_Fix', '[DB_NAME]'
    • EXEC sp_change_users_login 'Auto_Fix', '[DB_NAME]_Admin'
  • Select the Query menu and the Execute option. It should execute the query and produce results similar to the following
The row for user '[DB_NAME]' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1
The number of orphaned users fixed by adding new logins and then updating users was 0.
The row for user '[DB_NAME]_Admin' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Enable TCP/IP in SQL Server 20XX

  • Open the Start menu ==> Programs ==> Microsoft SQL Server 20XX ==> Configuration Tools ==> SQL Server Configuration Manager
  • Expand SQL Server 20XX Network Configuration, and the click Protocols for MSSQLSERVER.
  • In the list of protocols, right-click TCP/IP and choose Enable. You will get a message that states "Any changes made will be saved, however, they will not take effect until the service is stopped and restarted." and click OK at the message.
  • On the left hand side click SQL Server 20XX Services, right-click the SQL Server (MSSQLSERVER) and the right hand side and select Restart.

Notify RtVision of the completed migration

RtVision may need to make additional changes on the eGram server.

Please send an email to support@rtvision.com with the new SQL Server IP Address.