Move SQL Server Databases for Sage 100 Premium

Move Sage 100 Premium databases to a new SQL Server

Create SQL user accounts

1. Create the MAS_User account on the new SQL instance.
2. Match the settings from the current SQL instance, Security, Users.
3. Create the MAS_Reports account on the new SQL instance.
4. Match the settings from the current SQL instance, Security, Users.

Back up and copy databases

1. On the source SQL Server, create a backup (.bak) file for MAS_SYSTEM and all MAS_XXX databases.
2. Copy all backup files to the new server.

Restore databases

1. Open Microsoft SQL Server.
2. Restore all .bak files.
3. Right-click each restored database and select Properties.
4. Select Files.
5. For Owner, select Browse.
6. Select MAS_USER.
7. Select OK.
8. Repeat these steps for each restored database.

Relink the MAS_Reports account

1. Open each company database (MAS_XXX, where XXX is the company code).
2. Delete the existing MAS_Reports user from the Security, Users tab.
3. Open SQL Instance, Security, Users.
4. Relink the MAS_Reports account to each company.
5. Grant Read Database and Public access.

Update Sage 100 Premium SQL settings

1. On the Sage 100 server, open the Sage 100 version folder.
2. Open Sage 100 Premium (Version) SQL Setting Utility.
3. Select Edit.
4. Update the server name to the new SQL Server.
5. Enter the server and instance name if you use a named instance.
6. Update the MAS_USER password.
7. Update the MAS_REPORTS password.
8. Select Apply.
9. Select SQL Server Authentication, using Login ID and Password Below.
10. Sign in with SA credentials.
11. Select OK.
12. Confirm that Sage reports the password reset successfully.

Verify the SQL settings file

1. Browse to the ....\MAS90\HOME directory.
2. Open MASSQL.SETTINGS with Notepad or Word.
3. Verify the server name matches the new SQL Server.
4. Verify the owner displays MAS_USER.

Validate the migration

1. Sign in to Sage 100 Premium.
2. Open Company Maintenance.
3. Create a new company code.
4. Activate GL and CI.
5. Sign in to the new SQL Server.
6. Verify the new database exists in the correct location.
7. Open MAS_SYSTEM.
8. Verify SY_SYSTEM contains the new company code.
9. If all records appear correctly, users can begin work.