Version 7.0
Version 7.0
Contents of this Topic: Show
The Enterprise Edition of SEOM can use either an MS Access or SQL Server database as the application's data file, however, when the application is initially launched, an MS Access data (store) file is created by the program.
Follow the instructions in this article to convert the data file from a Microsoft Access database to an SQL Server database. The conversion process has been broken out into four sections; follow the sections in order.
If Microsoft SQL Server is already installed and configured on a computer in the networked SEOM configuration, then skip the first three steps and go to Convert the Order Manager's Microsoft Access Data File to an SQL Database.
Below are links to download pages for Microsoft SQL Server 2008 Express Edition and Microsoft SQL Server 2005 Express Edition, one of which must be installed on at least one of the workstations that will be running the Enterprise Edition of SEOM with an SQL database as the application's data file. Below each link to the SQL Server software in the following section are additional links for other required minimum software requirements for each version. If the installer of SQL Server does not offer the ability to download these components at the time of installation, the user can manually obtain them by clicking the appropriate link.
To obtain this product, you must first install the Microsoft Web Platform (WebPI), which allows you to select other application files that can be downloaded and installed at the same time. Click the first link below to begin the combined installation process and follow the directions in the section entitled, Installing/Configuring SQL Server. The other links are provided as a courtesy to obtain background information on each of the pre-requisite components but should not have to be used to download and install them.
SQL Server 2008 Express (either "with Tools" or "with Advanced Services" release)
You must install each of the first three components if they are not already installed on a each workstation that will be running SEOMwith an SQL data file. The Microsoft SQL Server Management Studio Express does not necessarily have to be installed on every PC in the configuration, but it should be installed on the server where the database will reside and on at least one administrator's workstation.
This section describes the steps necessary to install the SQL Server 2008 Express Edition via the Microsoft Web PI (Platform Installer). The installation process for SQL Server 2005 Express has different setup screens, however, all the necessary settings for SQL Server are covered in this example.
Obtain the desired version of SQL Server. In this example, SQL Server 2008 Express, is used. Click on the previous link to be taken to the download page. Use the links in the previous section of this article to obtain SQL Server 2005 and its pre-requisites and data base management software.
Click Install under the column labeled Runtime with Advanced Services or Runtime with Management Tools. In this example, Runtime with Advanced Services is used.
Figure 1
When the Microsoft Web Platform Installer window opens, click Get the Microsoft Web Platform to install the application. (A Windows Administrator ID must be logged on to perform this function.)
Figure 2
Choose Run or Save option when prompted to begin downloading the Web Platform Installer (wpilauncher_n.exe).
Figure 3
If Save is chosen, a popup appears to enter a location for the installation setup file. Click Save.
Close the Web Platform Installer window.
Navigate to the location of the installer and double-click it to begin the installation of SQL Server.
If Run is chosen the installation will automatically begin.
Figure 4
Figure 5
When the Microsoft Web Platform Installer application has been installed successfully, the following screen will appear to begin the installation of SQL Server Express 2008 with Advanced Services and the necessary pre-requisites.
Figure 6
Click Install at the bottom of the screen (pictured above) to begin the installation. Review the license terms by clicking View License Terms on the page pictured below. When finished, click Accept to continue downloading and installing the selected products.
Figure 7
You are prompted for SQL settings during the installation process. Choose which authentication mode to use and click Continue.
Windows Integrated Authentication - use Windows NT authentication (Windows login ids) to authenticate users (RECOMMENDED BY Stone Edge Technologies Inc.)
Mixed Mode Authentication - allows for both Windows NT and SQL LogonIDs to be used.
A user name and password must be created for the System Administrator of the SQL 2008 system that is installed.
SQL LoginIDs need to be created for each user and they are prompted by SEOM to login to SQL Server.
Figure 8
The following screen appears to show the progress of the installation:
Figure 9
Several other screens appear during the course of the product installations. When the installation process is complete, the following screen appears:
Click Finish to return to the Web Platform Products page and click Exit.
Launch SQL Server Management Tools from Start > Programs > SQL Server Management Studio.
By default, the SQL Server 2008 installer defined a Server Name in the form, MachineName\SQLExpress, where MachineName is the first level qualifier of the Full computer name on the Computer Name tab of the System Properties screen of the workstation or server.
In the figures below, the machine name is Sheryl2 and the SQL instance name is SQLEXPRESS:
Figure 11
Figure 12
Click Connect, pictured above, to open the open the SQL Server Management Studio software:
Figure 13
Right-click on the Databases folder and select New Database. The following screen appears:
Figure 14
Enter a name for the new database on the General page. Also set the Initial Size and Autogrowth values for the database:
Figure 15
Initial Size
If creating a database intended for conversion, consider the size of the existing SEOM database. Generally it is recommended the user set the initial size to that of the existing SEOM data file size, plus 25% (for example, allocate at least 1.25gb on SQL Server for a 1 gigabyte MS Access data file). This is to account for the additional overhead SQL Server needs to store metadata like table indexes and statistics.
Autogrowth
Autogrowth is a feature that allows SQL Server to automatically expand its data and log files as more space must be allocated. Otherwise, once a database used all storage space allocated during creation, the data or log files would need to be expanded manually or an error message would be displayed to the user that the database has run out of space.
The rate of these occurrences should be kept to a minimum if possible as the autogrowth process blocks other users from accessing the database during a period of file expansion, sometimes leading to timeout errors in SEOM.
In general, it is recommended autogrowth be enabled and set to grow by percentage. Optimal settings are difficult to determine ahead of creation. It should be noted that SEOM is often likely to add more than one megabyte of data during typical use, so multiple autogrowths (undesirable) would regularly be required if the default autogrowth size of "one megabyte" is kept.
Select the Options page and set the Collation and Recovery Model parameters for the database:
Figure 16
Collation
Suggested collation for SEOM compatibility is Latin1_General_CI_AS (sometimes listed as Latin1-General). In the New Database form, <server default> is the default option when setting database collation and represents the collation chosen when the server was installed. When in doubt, explicitly select a compatible collation as above.
Recovery Model
A recovery model of Full is suggested if the database is under heavy use by many users, as it provides the most options for recovery in the event of database corruption. A recovery model of Simple is suggested for lower-usage databases.
Click OK at the bottom of the page to create the database. The database appears in the Database Object List:
Figure 17
For the conversion to be performed, you must be logged in as a Windows administrator account that also has SQL Server administrative access to the database. For more information, click here.
Make a final backup copy of the MS Access store file by using Backup Data File at Main Menu > Settings > Data Functions.
Go to Main Menu > Settings > Data Functions > Convert to SQL Server.
Note: If this selection is not visible, the Enterprise Edition of SEOM is not installed.
At the next screen, the ODBC connection that SEOM needs to communicate with SQL Server is created, or an existing ODBC connection can be used:
Figure 18
To create the ODBC Connection, enter the name for a new ODBC data source (DSN). The name cannot contain any spaces or punctuation, other than underscores ( _ ). In this example, the new ODBC connection will be named SE_Store_File. Next, enter the name of the SQL Server instance (sheryl2\sqlexpress in this example). Finally, enter the name of the new database created in the previous section of this article (SE Store Data File).
To use an existing ODBC connection, click System DSNs and select one from the list of existing System DSNs. It is recommended that only System DSNs are used in conjunction with SEOM.
Enter a location in Network location for shared files (email templates, CustomReports.mdb, etc.).
Click the appropriate Continue button to begin converting the store data from the Access Database to the new SQL database. The progress of the conversion is seen in the lower left-hand corner of the screen.
Note: This process can take several hours to complete depending on the number of orders and/or products in the MS Access database. Perform this task off-hours when enough time can be allotted for its completion.
The program prompts for permission to create the SQL files. Click OK.
Figure 19
When the conversion is completed, the following message box appears:
Figure 20
Click OK.
The program asks if the new SQL database store file should be opened now:
Click Yes to view the data in the new SQL database. Perform some limited testing to verify the integrity of the database before other users login and begin using the SQL store file.
When satisfied with the data, use the SQL Server Management Studio to create a backup of the new SQL database. Create a scheduled task to perform backups of the new SQL database on a regular basis. For more information, click here.
Warning! Be aware that the original store file (Microsoft Access) is still usable after the conversion process. There are now two copies of the store file, one in Jet (Microsoft Access) and one in SQL Server format, creating the possibility of data loss if some workstations are still pointing to the old Access data file and any orders are processed in the interim! Going forward, all work should be performed in the new SQL database. It would be wise to remove the links to the old Access data file from each workstation's list on the Select a Store screen (Switch Stores).
Create the ODBC connections to the new SQL data file on any other workstations in the configuration and connect to the new store file. Resume processing orders.
Created: 1/28/11
Revised: 2/27/13
Published: 04/13/16