Installation: choosing and setting up your ProductCart database

You can use ProductCart either with a Microsoft Access or a MS SQL database. MySQL or Oracle databases are not supported. There is no price difference based on which database you use in terms of your ProductCart license.

Although using a MS Access database works just fine for many small businesses, MS Access was never designed to power a Web store, and therefore has several, structural limitations that can impact its performance. Therefore, whenever possible, you should use a MS SQL database for your ProductCart-powered store.

The main difference between MS Access and MS SQL is that the former does not support concurrent queries. That is, when multiple store visitors attempt to update the database (e.g. place an order) at the exact same time, MS Access puts them in a queue and process them one at a time.

In addition, MS Access has a structural limitation to 256 users connected to the database at any given time. These are users that are connected to the database, not actually 'updating' information in the database. There can be up to 256 users browsing the site (thus querying the database), but only 1 user writing data to the database at any given time.

So if any of those users decides to purchase (performing an action that requires an “update” to the database), the database gets locked up until the update has been performed. During that time, other users will not be able to write information to the database. So the rest of the users experience a small delay. After that, the site goes back to normal speed.

Due to the structural limitations mentioned above, you should use a MS SQL database for your ProductCart-powered store. The cost of having a MS SQL database available as part of a Windows Web hosting plan has dropped dramatically over the years, and you can now find many hosting packages offering it as part of packages that cost under $10/month.

A ProductCart-powered store typically requires around 50MB of SQL storage (around 20MB upon installation, but then the figure grows as products, customers, and order data is saved to the database).

Also, when making a decision, please note that moving from MS Access to MS SQL is not a simple process. Performing a database migration, especially once you have a lot of data stored in the database (products, customers, orders), typically requires the assistance of a database administrator and can cost several hundred dollars in consulting fees.

If you opt to use Access, there is nothing that you need to do is terms of preparing the database. The database is already setup and ProductCart will connect to it using the database connection string that you will specify during the activation process, as explained later in this chapter.

If you opt to use SQL, make sure to setup the database right after moving the files to the server that will host the store, but before you attempt to activate the product. Otherwise you will get an error during the activation process.

For an explanation of the tasks that you need to perform to use ProductCart with a SQL database, please see the corresponding section later in this chapter.

If you are not comfortable setting up the SQL database:

• If you are buying ProductCart from Early Impact, you can choose the installation service before or after you purchase the software, and the SQL database will be setup for you;
• If you are buying ProductCart from an Early Impact reseller, ask them for assistance with this task.

This document is meant as a general guideline for installing and setting up a Microsoft SQL Server database for Early Impact's ProductCart shopping cart software. This is by no means a comprehensive guide for running a MS SQL Server-based database. For in-depth information on SQL Server, you should consult the documentation included with SQL Server or consult your Web hosting provider.

If you have a Web hosting account that includes space on a shared SQL server, a database probably has already been setup for you, or there is a tool in the Web hosting account’s administration area that allows you to easily create one. If that’s the case, skip this section and go directly to “Running the SQL Script”.

If you need to create a new database, follow these directions.

• In MS SQL Enterprise Manager, right-click on the “Databases” folder in the Server Manager window and select “New Database…”.
• In the new database dialog box that appears, enter the name of the new database that you are going to use for ProductCart.
• Click on “OK” to save the changes.

You can now run the SQL script located in the productcart/database folder on your desktop. The script will create all the objects you need in the database.

• If you created the database using a tool provided directly by the Web hosting company, that same tool will likely contain a utility that allows you to run a query. Load (or copy and paste) the ProductCart SQL script in that utility and run it.
• Otherwise you can use a desktop application such as MS Enterprise Manager or MS SQL Management Studio Express. See how.

If a user name and password for the database were created through your Web hosting account administration area, you can skip this section as the user you setup already has the right permissions.

Otherwise, make sure that the database user that is used in the connection string that connects ProductCart to your SQL database has “ownership” rights on all the database objects: create a new User or select a User that has full permissions on all the tables in the ProductCart database. Make sure you are using SQL authentication, not Windows authentication.

You can connect to your SQL database using a DSN or DSN-less connection. We recommend using a DSN-less connection. Make sure that the user used in the connection string has been setup for SQL authentication, not Windows authentication.

DSN-Less connection to a MS SQL database

Provider=sqloledb;Data Source=SERVER-IP,1433;Initial Catalog=DB-NAME;User Id=USER;Password=PWD;
 
 “SERVER-IP” is the server’s IP address
 “DB-NAME” is the name of the database

 “USER” and “PWD” are the user name and password that grant access to it

DSN Connection to a MS SQL database

 DSN=SQLDSN;UID=USER;PWD=PWD
 
 “SQLDSN” is the name of the DSN
 “USER” and “PWD” are the user name and password that grant access to the database

If you are using a SQL database that has been set up to use a regional setting different from the default one (United States), please note that there may be an issue in the way dates are saved to the database, due to a date format conflict.

If your SQL database has been setup to use the United Kingdom’s regional settings, located the file includes/SQLFormat.txt. The variable SQL_Format determines the format to use. The default value for this variable is 0. Change the value to 1.

These security recommendations can help prevent certain types of SQL injection attacks, in case any vulnerability existed at the source code level. Read more.

Moving from an Access to a SQL database once the database has been populated and orders have been processed on the store is still possible, but it can be difficult and it may require hiring a database administrator (DBA). If you have a high-traffic Web site and therefore expect several concurrent users on your store (i.e. browsing and buying at the exact same time), you should definitely setup ProductCart to use a SQL database from the start.

If you need to move from an Access to a SQL database, you will need to contact a database administrator to assist you with the task. Early Impact can provide some recommendations based on feedback received from other ProductCart users.