SBS - Installing an ACCPAC Database on SQL2005

This process will work for both new and existing MS SQL database setups on ACCPAC.

Note that the methodology is pretty straight foward for a new setup: The process finishes after one has run the ACCPAC Setup Wizard to configure the company.

Our primary focus in this post is a very long struggle to get an existing ACCPAC client migrated to a new SBS installation that upgraded a peer to peer setup.

1. Create SQLUser in ADUC with a static password and no Exchange attributes
2. Create SQLUser2 in ADUC with a static password and no Exchange attributes
• Make sure to follow the SBS password security guidlines for the client's setup when creating the user passwords for these accounts.
• Note that their passwords must be set to not expire.
• Depending on the client configuration, further restrictions on these users can be initiated via OU and Group Policy Objects.
3. Create SQLUser in SQL Server Management Studio --> MySBS --> Security--> Logins and tie it to MySBSDomain\SQLUser
4. Create blank MYCSYS ACCPAC system Db in SQL2K5 with SQLUser as owner
5. Double click on the above created SQLUser in SQL2K5 manager and set MYCSYS as the default Database
6. Create blank MYCDAT ACCPAC data Db ini SQL2K5 with SQLUser as owner
7. Create SQLUser2 in SQL Server Management Studio --> MySBS --> Security--> Logins
8. Right click MYCSYS database and Properties
1. Permissions
2. Click on MySBSDomain\SQLUser
3. Verify "Connect" is checked
9. Click + MYCDAT (repeated for MYCSYS too) --> Security --> Users --> Right Click in the white space under the user list on the right and "New User"
10. User name: SQLUser2
11. Login name: MySBSDomain\SQLUser2
12. Set the Default schema to "dbo"
13. Leave Schemas owned by this user blank
14. Set "db_owner" under Database role membership
15. Click OK (just to repeat: do this for both MYCSYS & MYCDAT database Users)
• We now have two blank databases to work with that are ready for the ACCPAC setup wizard.
• If a mistake is made, all we need to do to start over is delete any tables under MYCSYS and MYCDAT --> Tables (click on the first table - NOT THE SYSTEM TABLES FOLDER - then SHIFT + the last table entry, right click and Delete.
16. For new installations, run the ACCPAC setup wizard and input all of the relevant dates, data, permissions, and users with their passwords.
• Note that there will be other setup steps that will need to be accomplished such as setting up security on the ACCPAC Database Setup console that are beyond the scope of this post.
17. For existing installations, keep things relatively simple: set the date as 1/1/200x, currency type, and then Next repeatedly until the process runs through creating the database tables.
18. Click on ACCPAC Database setup and both MYCSYS & MYCDAT will show up.
19. Run the ACCPAC Database Load utility to recreate the company data from the last backup made before the last server was downed.
20. If the Database Load utility somehow does not get all of the up to date data installed, the fix is very simple: Stop the default SQL server instance and copy the following files out of the old ACCPAC data directory:
• MYCDAT.mdf
• MYCDAT_log.ldf
• MYCSYS.mdf
• MYCSYS_log.ldf
21. Restart the default SQL instance service
22. Logon to ACCPAC on the server as the data's admin.
23. All should be good to go from there to install the software on the workstations and connect to the server.
• Note that the ACCPAC Database Setup utility on any workstation will muck up the server settings if the MYCSYS and MYCDAT entries are modified/deleted!
24. ODBC System DSN settings should be taken care of by the ACCPAC install.

After we were finished the above setup steps, we had our client's ACCPAC installation fully functional on the SBS 2003 Premium server with MS SQL 2005 SP1 installed.

If need be, for larger firms, we can initiate the SQL 2K5 install routine to install a dedicated SQL2K5 instance for ACCPAC's purposes prior to performing the above steps.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.