Setting up served catalogues using SQL Connect
Manual: v5 - pages 266-276
Note that you need to have appropriate Portfolio Server and Portfolio Network clients installed and licenced before installing SQL Connect. The initial steps are then to set up SQL Server or Oracle 8i and then install the Portfolio SQL Connect software as detailed on pages 267-270 of the manual. You should be installing the SQL Connect software onto a computer that already has the Portfolio Server software installed. Now:
- Install the Correct ODBC Drivers.
- Create a DSN (Oracle only).
- Install Portfolio Server software if not already installed and add the server via the client 'Administer Servers' dialog. If this is your first use of Server, click the 'Serialize...' button In the Serialize dialog, add your Server licence code, save and exit. [Ignore this step if Server/SQL Connect licences already serialised.]
- Install Portfolio SQL Connect software if not already installed. If this is your first use of SQL Connect, click the 'Serialize...' button In the Serialize dialog, add your SQL Connect licence code, save and exit. [Ignore this step if Server/SQL Connect licences already serialised.]
- Set size of database in your SQL database software. In SQL Server 6.5 you must specify a maximum size for your database. As a general rule, estimate 15K/record for small (112x112) thumbnails and 30K/record for large (256x256) thumbnails. Since it is difficult to expand a database after it is created, it may be useful to leave room for unexpected growth. This requirement does not apply to SQL Server 7.x.
- Give the SQL database a name (any name but without any file extension).
- SQL Server 6.5 and 7.0: A user with administrative rights must be created for this database. This can either be the standard “sa” account, or another user with database owner (dbo_owner) rights. Make sure the “master” database is the default database for the dbo you’ve created.
- SQL Server 6.5 and 7.0: Portfolio Server will create one connection per user per database. Increase the maximum number of concurrent connections allowed by the system, if necessary. For example, if you are serving two Portfolio SQL catalogues from one SQL server, and each database has 50 users connected (even if they are the same set of users in each case) you’ll need at least 100 concurrent connections to the SQL server.
- Oracle 8i: The administrative user that you create must have the DBA role assigned to it.
- Administer your chosen Server via Portfolio Client.
- At the Portfolio Server Administration dialog, click the Serve SQL button.
- Provide the details of your SQL Catalogue: select the appropriate driver for your database. You can use the same User Name and Password as used in your Portfolio Server administration or different values.
- Click OK out of the Add Server dialog.
- Select your SQL Catalogue and click the Serve SQL button on the Serve SQL Database dialog.
- Select Yes/No to have the SQL Catalogue served whenever Portfolio Server is launched.
- Confirm the SQL Catalogue's User Name and Password.
- Close out of the various Catalogue Admin dialogs.
You will see that after the SQL Connect software has been installed and serialised, setting up and serving a new SQL Catalogue takes but a few minutes. The SQL Connect software handles the creation of a default set of Catalogue fields. Once the SQL Catalogue is created and served you can then carry out further customisation (e.g. Custom Fields, Saved Finds) as you would with a normal Catalogue.
Although using SQL Connect removes the 4 Gb size limit for an individual Catalogue, the module enables a catalogue of any size to be stored in an SQL database with all the benefits that brings - e.g. better performance with large numbers of concurrent users.
Installing the Correct ODBC Drivers
SQL Server 6.5 and 7.0: Install the ODBC and SQL Server drivers according to the instructions supplied with the Microsoft SQL Server.
Oracle 8i: Install the Oracle Client software (including ODBC and Oracle driver options). In the ODBC Control Panel, create a System DSN with the user name you created in step 2. Important: make sure the “Force Retrieval of Long Columns” option is checked (enabled).
When the ODBC driver has been verified (and the DSN created if this is an Oracle database) you can proceed with setting up Portfolio SQL Connect and Portfolio Server.
Create a DSN (Oracle only)
- Open the ODBC (32 bit) Control Panel (Start > Settings > Control Panel > ODBC (32 bit).
- In the "ODBC Data Source Administrator" dialog, select the "System DSN" tab.
- Click "Add."
- In the "Create New Data Source" dialog, select the Oracle ODBC Driver.
- Click "Finish." The Oracle ODBC Driver Setup dialog will be displayed.
- Enter the following information:
- Data Source Name: The name of the DSN you are adding.
- Service Name: The name of the Oracle instance, usually in the format "databasename.domain". Check with the Oracle Database Administrator for the correct name.
- UserID: A valid user ID for the Oracle database. This person must have database ownership rights.
- Force Retrieval of Long Columns: Must be enabled.
- All other fields: For the remaining fields, accept the default settings.
- The completed form should look something like the screen shot shown:
- Click "OK" to accept the changes and add the DSN.
Question: Setting up served catalogues using SQL Connect [FAQ00165.htm]
Last Update:- 01 June 2006
