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 (and might be faster to get up and running with), 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. Therefore, if you are undecided, definitely opt to use a MS SQL database from the start.

Some Web hosting companies allow you to use MS SQL Express as part of the hosting package that they offer. Please note that although MS SQL Express is perfectly fine to create a testing or development store using ProductCart, it should never be used to power a production store. MS SQL Express was never designed to support a live Web application. It was created by Microsoft as a free tool that Web developers and designers can use while creating and testing a Web application. It should only be used as such.

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.

We recommend using ProductCart with a MS SQL database. If you opt to use MS SQL, make sure to setup the database right after moving the files to the server that will host the store, and before you attempt to activate the product. That is: do not run the ProductCart Setup Wizard unless you have setup the database. Otherwise you will get an error during the activation process.

If you are installing ProductCart locally on your desktop for testing or development purposes, see the section below about using MS SQL Express.

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

The following information was obtained from this Web site: http://www.great-seo.co.uk/sqloledb-connection-strings/

If you need to use an OLE-style connection string to connect to a SQL Server database, but you’re having trouble connecting, you can use the SQL Native Client Provider instead. So rather than:

Provider=SQLOLEDB;Server=myhost;Database=mydatabase;Persist Security Info=False;User ID=xxxx;Password=yyyy

you can use:

Provider=SQLNCLI;Server=myhost;Database=mydatabase;Persist Security Info=False;User ID=xxxx;Password=yyyy

The provider should match your version of Native Client. If you are running SQL Server 2008 R2, the provider is SQLNCLI10, as per: http://msdn.microsoft.com/en-us/library/ms130822.aspx

If you are running ProductCart on your desktop, you can use the free version of MS SQL to run your store for testing and development purposes. Your ProductCart license allows you to set up a separate store for that purpose.

However, please note that connecting to a MS SQL Express database on your desktop can be a bit tricky, especially on Windows Vista. The following instructions might help:

• Download and install MS SQL Express with Management Tools
  • The installation process is somewhat lengthy. You can use the default selections on most windows.
  • One setting that you will need to change is the following: you must allow for both Windows Authentication and SQL Authentication. If you forget to set this setting during installation, you can change it later with MS SQL Server Management Studio.
• See the Microsoft Web site for detailed installation instructions (e.g. see these How To guides)
• Connect to SQL server locally using SQL Server Management Studio.
  • Use Windows authentication to do so.
  • Make sure that you are connecting to the instance of SQL server that you specified during the installation of MS SQL Express (you might have more than one instance of SQL server installed on your computer (use the ”<Browse for more…>” option to see a list of local servers).
  • Make sure that the server allows both Windows and SQL authentication. To do so, right click on the server instance, click on “Properties”, select the “Security” section, and make sure that the authentication setting is set as follows:

• Create a new login by selecting Security > Logins > New Login
  • Use a unique login name. For example, you could use “pcStoreAdmin”.
  • Select SQL Server authentication
  • Enter a password
  • Uncheck the 3 check boxes Enforce Password Policy, Enforce password expiration, and User must change password at next login, unless you want to use those features. This is just a test/development environment, so they are typically not needed.
  • Click OK to save the new Login
• Create a new database, and run the SQL query to setup the tables and fields used by ProductCart
• Create a new user for this new database by selecting Security > Users > New User
  • For the user name and login name, use the new login you just created (e.g. “pcStoreAdmin”).
  • Under “Owned Schemas”, check db_accessadmin and db_securityadmin
  • Under “Role Members”, check db_accessadmin, db_owner, and db_securityadmin
  • Save the new user

You should be ready to go. Now… we have heard of issues finding a database connection string that works successfully. If the traditional SQL server connection strings mentioned above do not work, try the following one, which we have used successfully on local ProductCart installations on Windows Vista. Replace <COMPUTER_NAME>, <DATABASE_NAME>, <USER_NAME>, and <USER_PASSWORD> with the correct values that apply to your database. </code>

Provider=SQLOLEDB.1;Data Source=<COMPUTER_NAME>\SQLEXPRESS;Initial Catalog=<DATABASE_NAME>;User ID=<USER_NAME>;Password=<USER_PASSWORD>;

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, locate 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.