Access to SQL Server Upgrade for your CRM Software

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

CRM Testimonials

Access to SQL Server Express - Upgrade Amphis Customer CRM Software to use a true Client Server Database

You can use Amphis Customer CRM Software with the free version of Microsoft SQL Server called SQL Server Express (or the full paid version of SQL Server if you prefer). This means you can support many users in true client-server fashion which gives superior performance and scalability improvements over using Microsoft Access. See Reasons to Upgrade to SQL Server Express

What does this cost ? Absolutely nothing. Microsoft SQL Server Express is totally free. Amphis Customer CRM Software 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.

Please note: Microsoft SQL Server Express 2012 supports database sizes up to 10Gb. If your database size ever exceeded 10Gb you would need to purchase the full SQL Server product from Microsoft.

For information on installing Microsoft SQL Server Express, see Install SQL Server Express 2012

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

Microsoft Windows Connection Limits

Please note that running SQL Server on Windows XP or Windows Vista may limit the number of connections to SQL Server to 10. Windows 7 may limit the number of connections to 20. If you upgrade a PC from Windows XP or Windows Vista to Windows 7, the 10 connection limit may still apply. Ideally SQL Server should be run on Windows Server. If you have more than 10 users connecting to SQL Server but Windows restricts the number of connections to 10, one or more users may experience network problems.

Upgrade to SQL Server for Superior Performance and Scalability

This 3 minute 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. This 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

Note: You are not actually changing the Access database file (customer.mdb). You are creating a new SQL Server database based on the contents of the Access database. To benefit from this you need to change the configuration of the client PCs to connect to the new SQL Server database instead of the Access database. The Access database is still there but you shouldn't be connecting to it once you have created the SQL Server database.

Connecting to the new SQL Server database

If you go into Tools->Configuration and click Upgrade to SQL Server you can enter a SQL Server connection string and click the Test Connection button to check that the connection works ok. If the connection is successful, you can click OK to upgrade the Amphis Customer configuration to switch to SQL Server using the connection string you entered.

Here is an example connection string that connects to Sql Server Express on your local PC.

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

Here is an example connection string that connects to Sql Server Express on Server1.

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

Problems using SQLNCLI - The SQLNCLI provider is not registered on the local machine

You can also use a different provider if your client PC doesn't have SQLNCLI installed.

Just replace...

Provider=SQLNCLI;

with...

Provider=SQLOLEDB;

For example...

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

Note that the database name must exactly match the name of the Sql Server database you created. If you created a database called customerSql then you need to change the connection string to specify Database=customerSql

If you have Microsoft Access installed, you can use the Microsoft Access facility to Upgrade your Access Database to SQL Server but for more recent versions of SQL Server Express you may need to use the SQL Server Migration Assistant for Access.

Please upgrade to the latest version of Amphis Customer before upgrading to SQL Server. For the latest version information, see Latest CRM Version.

Access to SQL Server

For detailed instructions on upgrading your Amphis Customer database to SQL Server Upgrade to SQL Server

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.

For a checklist of steps to take to upgrade from Microsoft Access to SQL Server see Upgrade from Microsoft Access to Microsoft SQL Server Checklist

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.

Client PC Configuration

Once you have configured your client PCs to connect to the new SQL Server database instead of the Access database, the configuration (from Tools->Configuration) should look like this on all the client PCs.

CRM with SQL Server

Remote Access to your Data

It is possible to set up SQL Server so that you can connect to the database remotely giving you access to your data from multiple locations.

Microsoft SQL Server Migration Assistant for Access

There is also a Microsoft SQL Server Migration Assistant for Access. This tool is useful if you are trying to upgrade an Access database to a more recent version of SQL Server which the Microsoft Access Upsizing Wizard doesn't support. See SQL Server Migration Assistant for Access.

Amphis Customer SQL Server Forum

If you have migrated to SQL Server or are considering doing do, please keep an eye on the Amphis Customer SQL Server Forum here for the latest information.

Microsoft SQL Server Language Setting

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

To check this, run Microsoft SQL Server Management Studio...

SQL Server Management Studio

Go to Security->Logins and select the Login you are using. Then check the properties to see if the Default Language is British English.

SQL Server British English

If your dates are in British format dd/mm/yy but the SQL Server Login you are using is configured to be the default of English then your SQL queries will fail because the dates will be invalid as it will treat them as mm/dd/yy.

If you are not sure which Login to change to British English we suggest you change them all.

If you change the Login settings you will need to restart Amphis Customer.

Unwanted Disallow Zero Length Constraints

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

Important - check all the tables and delete all the "Disallow Zero Length" Constraints from all the tables - not just the ones shown here.

SQL Server Disallow Zero Length Constraint

To find those constraints to delete, run SQL Server Management Studio and locate the Databases entry near the top.

SQL Server Databases

Click + to expand databases. Click + to expand your database which is probably called Customer.

SQL Server Customer Database

Click + to expand the tables.

SQL Server Tables

For each table, Click + to expand the Constraints. Delete any constraints that end with disallow_zero_length.

SQL Server Constraints

Then restart Amphis Customer.