Chapter 8   Configuring Microsoft SQL Server Databases

This chapter describes how to use Microsoft SQL Server databases in the SilverStream Application Server environment. It includes information about using Microsoft SQL Server as a SilverMaster database or as an application database.

    For the latest information on supported database versions, see the SilverStream Application Server release notes. In Windows, you can access the release notes from the SilverStream program group or from SilverStream online help.

Setting up Microsoft SQL Server databases   

SilverStream connects to Microsoft SQL Server using the ODBC driver supplied by Microsoft as part of the SQL Server client. There are different versions of this ODBC driver.

To make a Microsoft SQL Server database available to SilverStream:

1. Install the Microsoft client onto the machine where the SilverStream Server resides.

2. Create the database and configure SQL Server.

3. Create a database login to be used by the SilverStream Server.

4. Create an ODBC System DSN entry for SilverMaster on the machine where the SilverStream Server resides.

5. Install the SilverStream Server and choose Microsoft SQL Server as the SilverMaster type.

       For information about installing the SilverStream Server, see Installing SilverStream on Windows.

Step 1: Install the Microsoft SQL Server client   

You must have the Microsoft SQL Server client software installed on the PC where the SilverStream Server will be. Your client must be configured to connect to the destination SQL Server database.

Step 2: Create the database and configuring SQL Server   

Before you can install SilverStream, you must first create the actual SQL Server database (and underlying database devices). You can create these objects using either ISQL or a graphical tool such as SQL Enterprise Manager.

These notes describe how to use SQL Enterprise Manager to create the objects. You need to connect to SQL Server using SA or an account with SA privileges in order to create these objects.

To create a Version 6.5 database:

1. From SQL Enterprise Manager, connect to the destination SQL Server.

2. Right-click on the database devices folder and select New Device.

3. Create a Database Device called SilverMasterData and specify a size of 30 MB.

4. Create another 30 MB device called SilverMasterLog.

5. Right-click on the Databases folder and select New Database.

6. Create a database called SilverMaster using the devices you created (SilverMasterData for the data and SilverMasterLog for the transaction log).

   If you are not going to back up this database regularly, enable the Truncate Log on Checkpoint option: Right-click on the database and select Edit. This option is found on the Options tab.

7. For SQL Server 6.5, you must verify the size of the TEMPDB database by right-clicking on the TEMPDB database and selecting Edit.

   The size of the database is shown on the Database tab. If the size of your TEMPDB database is less than 50 MB, extend it by increasing the size of your MASTER device and choosing Expand the database (this option is found on the same tab as the Data Size value).

8. You must verify that you have an appropriate amount of memory allocated to SQL Server. This can be done through SQL Enterprise Manager.
   • From SQL Enterprise Manager, right click on the server name and select Configure.

   • From the Configuration tab, select Memory. This value is in 2K pages (therefore, the value shown times two equals the number of kilobytes of memory allocated to SQL Server). SilverStream recommends the following formula: (System memory in megabytes / 2) * 512 = number of 2K pages to allocate to SQL Server. You must be logged in to SQL Server as SA or equivalent. SQL Server must be restarted to apply this change.

     NOTE   If you over-allocate memory, SQL Server will not start.

To create a Version 7 database:

1. From SQL Server Enterprise Manager, right click on databases and select New Database and give the new database a name, such as SilverMaster.

2. In File Properties either allow for unrestricted file growth or allow at least 30 MB for the database.

3. Select the Transaction Log tab and allow unrestricted file growth or allow at least 30 MB for the log file.

4. Click OK to create the database.

5. Select the TEMPDB and right-click and select Properties. Verify that the unrestricted file growth is selected or that there is at least 50 MB allotted for it.

6. Verify that you have the appropriate amount of memory allocated to SQL Server. This can be done through the SQL Enterprise Manager.

7. Highlight the SQL Server, right-click and select Properties.

8. On the SQL Server properties dialog, select the memory tab.

9. Verify that either Dynamically configure SQL Server memory is selected or, if you are using a fixed amount of memory for SQL Server, subtract 64 MB from the total physical RAM in the machine and allocate this amount to the SQL Server (if the machine is only running SQL Server). If other server applications are also running on the machine (SilverStream, MS Exchange, and so on) you must also subtract the amount of memory needed for those applications.

Step 3: Create the SilverUser login   

Create a login called SilverUserLogin.

Note the following:

• The login cannot have a null password.

• Set the default database for this login to the SilverMaster database you just created. If you do not, you receive error messages during the SilverStream Server Startup stating "The database schema for SilverMaster is out of synch."

• For SQL Server 6.5, you must alias this account to DBO for the SilverMaster database. For SQL Server 7, create a login using a SQL Server authentication mode and enable the db_owner role for the desired database on the Database Access tab.

• The SilverUserLogin must have the following permissions: Create Table, Create View, Create SP, Create Default, Create Rule, Backup DB and Backup Log.

Step 4: Create the ODBC System DSN entry   

To create the ODBC System DSN for a Version 6.5 database:

1. Configure an ODBC System DSN entry for the SilverMaster or application database.

2. Add an entry using SQL Server as the driver type.

3. Enter the appropriate Server name for the target SQL Server.

4. Enter the destination database in the Default Database field.

5. Click the Options button and deselect the Create temporary stored procedures for prepared SQL statements and drop the stored procedures check box for use with SilverStream.

   If you do not deselect this option SilverStream will not function properly with the database.

To create the ODBC System DSN for a Version 7 database:

1. Add an entry using SQL Server as the driver type.

2. Enter the Server name for the target SQL Server.

3. Enter the SQL Server user authentication information for the SilverUserLogin and click Next.

4. Select the proper default database name.

5. Deselect Use ANSI quoted identifiers.

6. Deselect Use ANSI nulls, paddings, and warnings and click Next.

7. Accept the proper language, and region settings options and click Finish.