Access to SQL Server Migration

Access to SQL Server Migration - Upgrade Amphis Customer to use a true Client Server Database

Download Contact Management Software     Reached the Final for a CRM Award in the Software Satisfaction Awards 2011 Reached the Final for a CRM Award in the Software Satisfaction Awards 2012

Migrate CRM Software to SQL Server

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. For information on installing it, see Install SQL Server Express 2012.

You need to install Microsoft SQL Server 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 .exe file from there.

For information on configuring SQL Server Authentication so your users can access the database, see SQL Server Authentication

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 configuration (in Tools->Configuration) to use SQL Server Express instead of the supplied Microsoft Access Database.

Here is a connection string 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.mdb file could look like this.

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

You need to upgrade the Amphis Customer configuration to use SQL Server instead of Microsoft Access. To do this, go to Tools->Configuration and click Upgrade to SQL Server. Enter your connection string and click Test Connection. If the connection is successful you can click OK which will switch the configuration to use SQL Server in the config.mdb database and store the SQL Server connection string.

Upgrade CRM to SQL Server

You can configure other PCs to use SQL Server as well by simply copying the config.mdb file from this PC to the other PCs.

If you have Microsoft Access installed, you can use the Microsoft Access facility to Migrate your Access Database to SQL Server. Note: if your version of Access does not support conversion of database files to SQL Server 2012 you can use the Microsoft SQL Server Migration Assistant for Access - see Microsoft SQL Server Migration Assistant for Access (SSMA).

IT Support Needed

Please note that setting up and maintaining Microsoft SQL Server is an advanced topic and we would suggest consulting an IT Professional to help you with this. The IT Professional needs to have a full understanding of Microsoft SQL Server including SQL Logins, SQL Users, networking and security, and SQL Server backups.

Amphis Software Support are happy to provide help with setting up Amphis Customer to use SQL Server but setting up Microsoft software such as Microsoft Windows, Microsoft Exchange, Microsoft Outlook or Microsoft SQL Server is beyond the support that Amphis Software Support can provide. We support software we have developed but we don't support software developed by Microsoft.

Upgrade to SQL Server 2012 for Superior Performance and Scalability

This 3 mins video shows how to upgrade (for free) the supplied Access database to a SQL Server Express 2012 (Free Edition) database for Superior Performance and Scalability, using the Microsoft SQL Server Migration Assistant for Access.

Upgrading to use a SQL Server database is recommended for more than about 10 or 12 users and allows you to expand the number of users beyond what a Microsoft Access database can handle.

CRM Software Video

Migrate Access to SQL Server using the Access Upgrade Wizard

If you have Microsoft Access installed, you may be able to use the Microsoft Access Upgrade Wizard, but if your version of Microsoft Access does not support upgrading to SQL Server 2012, then you will need to use the Microsoft SQL Server Migration Assistant for Access.See Microsoft SQL Server Migration Assistant for Access (SSMA).

Here are the steps you need to take to use the Microsoft Access Upgrade Wizard.

Step 1 Go to Tools->Configuration and click the top View folder button to locate the database file (customer.mdb).

Step 2 Shut down all instances of Amphis Customer.

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

Access Security Warning

Click Options. Select Enable this content and click OK

Access Enable Content

Step 4 Click the Database Tools tab.

Access Database Tools

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

Access to SQL Server Upsize

Migrate Access SQL

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.

Access Upsizing Wizard

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

Access Export SQL Server

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

Access SQL Upsizing

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

Access Upsize

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 Amphis Customer configuration in Tools->Configuration 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

Enable TCP-IP Protocol on the Server

You will need to go into the SQL Server Configuration Manager and enable the TCP-IP protocol on the server.

SQL Server Remote Connections Enable TCP-IP

SQL Server Backups

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

We recommend using SQL Server Management Studio Express to view the contents of the tables in the database and for performing regular backups.

SQL Server Express Limitations

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 10GB (in SQL Server Express 2012). 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.

SQL Server Date Format

Please note that if you live in a country that uses British date formats eg United Kingdom or Australia, you will need to change the Default Language for the Logins to British English.

SQL Server British English Dates

We have some Sql Server Migration Troubleshooting tips to help if you have problems migrating from Access to Sql Server or connecting to your SQL Server database.

Microsoft SQL Server Migration Assistant for Access

If your version of Microsoft Access does not support Upsizing to your version of Microsoft SQL Server, you can use the Microsoft SQL Server Migration Assistant for Access. See Microsoft SQL Server Migration Assistant for Access (SSMA).

Unwanted Disallow Zero Length Constraints

The upgrade to SQL Server may generate some "Disallow Zero Length" Constraints in the SQL Server database like these in the CUSTOMERJOB table. If it does, right-click on each one and select Delete to delete it (and check the other tables too).

SQL Server Disallow Zero Length Constraint

More Information

We have some short CRM Videos about Amphis Customer CRM Software and also a CRM Quick Guide with more details and screenshots. We have Prices, Customer testimonials and CRM Software Case Studies or why not Download our CRM Software Free 60 Day Trial and try it for yourself ?

Bookmark and Share