Access to SQL Server Migration

You can use Amphis Customer CRM Software with the free version of Microsoft SQL Server called SQL Server Express. SQL Server Express can support many users in true client-server fashion which gives superior performance and scalability improvements over using a Microsoft Access Database. This is because SQL Server Express runs as a dedicated Windows service on a server whereas a Microsoft Access Database is a single file which gets shared across multiple PCs in a network. While we think Microsoft Access is a fantastic product and provides amazing functionality, we would recommend that for more than 10 users you should seriously consider migrating your Access Database to SQL Server Express. There is an Upsizing Wizard to make the migration from Access to SQL Server Express as easy as possible. Please see instructions below for Migrating an Access Database to SQL Server using the Upsizing Wizard.

Free Upgrade to SQL Server Express

Microsoft SQL Server Express is totally free and you can download it here. We recommend using SQL Server Express 2005 rather than SQL Server 2008 Express as it is easier to convert the Access Database to SQL Server Express 2005 (rather than 2008) using Microsoft Office. We also recommend you install Microsoft SQL Server Management Express which you can download from here You need to install Microsoft SQL Server 2005 Express and Microsoft SQL Server Management Express on a server that your clients can connect to. You may need to install these as an Administrator or else you will get an error. We run the Command Prompt as Administrator and then run the .msi file from there.

Amphis Customer supports Microsoft SQL Server Express at no extra charge. You just need to upgrade your existing Access Database to SQL Server Express and change the Amphis Customer.exe.config file (in c:/program files/amphis software/amphis customer v3.0) to use SQL Server Express instead of the supplied Microsoft Access Database.

An example SQL Server connection string is given in your existing Amphis Customer.exe.config file (commented out) which connects to SQL Server Express running on your local PC (which may be useful for a trial).

   Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=customer;Trusted_Connection=yes

For production use, you need to use the name of the server in the connection string eg if your server is called Server1, your connection string in the .config file could look like this.
   Provider=SQLNCLI;Server=Server1\SQLEXPRESS;Database=customer;Trusted_Connection=yes

You need to set useSQLServer to true in the .config file to use SQL Server instead of Microsoft Access.

You can use the Access 2007 facility to Migrate your Access Database to SQL Server.

Migrate Access to SQL Server

Here are the steps you need to take.

Step 1 Go to Tools->Folders->View Data Folder and locate the database file (customer.mdb). (If you have already moved the customer.mdb file, locate it in its new location).

Step 2 Shut down all instances of Amphis Customer.

Step 3 Open the database in Microsoft Access 2007. You may see a security warning like this.

Click Options. Select Enable this content and click OK

Step 4 Click the Database Tools tab.

Step 5 Click the SQL Server icon and select Create new database. Click Next.

Step 6 This is where you may need some help from your System Administrator - you need to specify where your SQL Server Express installation lives and how you will connect to it. For this example we are connecting to SQL Server Express on the server called Home. We recommend you call the database customer as in the screenshot. Click Next.

Step 7 Click the double arrow to specify that all tables are to be copied (as in the screenshot). Click Next.

Step 8 Select the options as in the screenshot. Click Next.

Step 9 Select the options as in the screenshot. Click Next.

Step 10 Click Next and click Finish.

Your SQL Server Express Database should now be created and if you run Microsoft SQL Server Management Studio Express and expand Databases, you should see it. Once you have changed your .config file as detailed above, you should be able to run Amphis Customer and connect to the SQL Server Express Customer Database.

You will need to configure SQL Server to allow remote connections. For more information click here

You will need to use SQL Server facilities to make regular backups of your Customer Database.

Also note that there are limitations with the free version of SQL Server (called SQL Server Express) namely, there is a size restriction on the database which is limited to 4GB. You will need to monitor the size of your customer database to check that it does not reach this limit. If it does, you would need to purchase a license from Microsoft for the full version of SQL Server.

There are other limitations eg SQL Server Express will only use 1GB of memory and will only use one CPU. For more details on the restrictions, please click here