Before beginning the upsizing process, make sure that your host is certified with SQL server, or has equivalent experience. SQL Server is a complex and powerful enterprise level database application, and should be managed only by an experienced technician.
To begin, have the Server Administrator create an empty database and grant the StoreFront user Database Ownership rights. The user should also be given permissions to log in to the SQL server remotely.
Begin by downloading a copy of the StoreFront 2000 database to the local machine. Be sure to make a backup copy of the database in the web before you begin downloading; if your connection is lost in the middle of the transfer, the database could become corrupt.
Once the database has been downloaded, open it in Access and select Tools|Database Utilities|Upsizing Wizard. From the menu that appears, select Use Existing Database and click Next.
In the next menu, click on the Machine Data Source Tab, select New, System Datasource, and click Next. In the next screen, double-click SQL Server, then click Finish.
Enter the datasource name in the Name field (this can be anything, but I'd suggest keeping it fairly short)This datasource name will be used later when you wish to connect to your SQL Database and make any modifications. Leave the Description blank, and in the Server field enter the IP Address of the SQL Server. Click Next.
Select the radio button for Use SQL Server Authentication, then click Client Configuration. Under client configuration, select Use Named Pipes if you are connecting through a LAN; if you are connecting from a remote machine, through a dial-up or cable modem, select TCP/IP. You don't need to change the entry for the server alias. Click ok.
In the login and password fields, enter your SQL Server login and password and click Next.
On the next screen, check the "Change Default database to:" check box and choose your database from the drop down (this will be the database that the host created for you on the SQL Server). When the access database is upsized, this is the database that the information will be upsized into. Click next, then finish.
Click the Test button to test the connection. If the test is successful, proceed to the next step by clicking ok. If you encounter an error during the test, contact the SQL Server Administrator.
Doubleclick on the data source name you just created, then reenter your SQL Server username and password. Click the >> button to select all tables for upsizing. Click NExt.
Leave all settings on the next screen to the defaults, and click next.
Leave the radio button set to no application changes, and click finish.
Once the Access database has been upsized, there are 3 fields that need to have their data types changed. In Access, these data types are memo fields which are upsized as ntext fields. These fields will need to be changed to nvarchar fields. If you do not have enterprise manager, you will need to create an Access front end database for the SQL Database.
To do this, open Access and click cancel on the first screen so you do not select an existing Access database. Click on the new page icon then double click on the Project (Existing Database) option. Enter a name for the front end database and save it in any location, then click on Create.
The next window that will appear is the connection screen for the SQL Server Database. Enter the IP address of the SQL Server in the server name field. Uncheck the Blank Password check box and enter you SQL username and password in the appropriate fields. Click on the Test Connection button. If the test does not succeed, contact your SQL Server administrator. If the test is successful, click on the drop down box to select your database on the server. Highlight your database and click OK. This Access database is now a front end database for you SQL database and you have the basic functionality of the SQL Server Enterprise Manager to administer your database.
Now, you will need to change 3 fields. First, right click on the Admin table and select on design. Find the field named EMAIL_MESSAGE and highlight the datatype column. Change the datatype from ntext to nvarchar with a length of 1024 or greater. Then locate the SHIP_MESSAGE field in the customer table and the LONG_DESCRIPTION field in the product table and make the same changes.Once the database has been upsized and the fields have been changed, you'll need to convert the StoreFront standard web to StoreFront Pro. To do this, open the web that is to be converted in FrontPage and select StoreFront|Import Wizard. Import the StoreFront Pro files into the web, replacing ALL asp and inc files, as well as the global.asa file. Do NOT overwrite any htm files. Once the files have been imported, you'll need to apply a database connection. The remote site should still be open in FrontPage at this point, so click on Tools|Web Settings|Database tab. Click Add, select "Use system datasource on web server" then select your DSN from the list of available DSNs. Once this is done, click ok, then click on the Advanced button and enter your SQL server username and password in the appropriate fields. Now, click ok, ok once more, then highlight the new connection and click verify and apply. This should complete the process.
