Microsoft® Site Server Service Pack 2 supplies the updates needed for Site Server 3.0 and Site Server 3.0, Commerce Edition to work with Microsoft® SQL Server™ 7.0. With Service Pack 2, you can upgrade SQL Server 6.5-based installations of Site Server to use SQL Server 7.0, or you can set up new Site Server installations using SQL Server 7.0. This document provides the primary information you will need for using Site Server 3.0 and Site Server 3.0 Commerce Edition, with SQL Server 7.0.
Most Site Server components are fully compatible with SQL Server 7.0, with the following exceptions and special conditions:
Example: a partial subtree search originating from ou=Members, where the query filter specifies that the LDAP Server should return all objects with givenName=bob*.
Be sure to review “What’s New in SQL Server 7.0” in the SQL Server Books Online (installed with SQL Server 7.0).
For information about large deployments of SQL Server 7.0, see SQL Server 7.0 on Large Servers in the “Server Architecture” section of the SQL Server Books Online.
The following Microsoft® Site Server 3.0, Commerce Edition features require separate databases for their storage needs:
Important You cannot migrate Site Server data from an Access database to a SQL Server database.
Develop a clear deployment plan before you actually implement an upgrade or new installation using SQL Server 7.0. If you are upgrading from SQL Server 6.5, take some time to determine the most efficient upgrade approach. Whether you are upgrading or creating a new installation, identify the approach that you will use to secure the SQL Server 7.0 computers and the databases on them, and how users and services will access the data they need.
The process of upgrading your installation consists of multiple phases:
For examples of how this process could be implemented, see the Appendix, “Upgrade Strategies.”
SQL Server 7.0 offers two approaches for upgrading:
Regardless of the approach you use, when the upgrade process is complete, two separate installations of SQL Server exist, including two separate sets of the same data. The SQL Server 6.5 and SQL Server 7.0 installations are independent of each other from that point forward.
Your system must meet the following infrastructure requirements before you can upgrade:
After you have determined your upgrade approach, the following sections of this document will guide you through the upgrade process:
In addition, you should be familiar with the section “Upgrading from an Earlier Version of SQL Server” in the SQL Server Books Online.
If you are upgrading databases that are involved in replication, you must use the side-by-side upgrade approach. See Replication and Upgrading in the “Upgrading from an Earlier Version of SQL Server” section of the SQL Server Books Online.
If you are upgrading databases that are involved in failover support, you must dismantle the cluster before upgrading and rebuild the cluster after the upgrade is complete. See “SQL Server Failover Support” in the SQL Server Books Online.
When you are implementing security for your Microsoft® SQL Server™ installation, you need to consider how you will support the following types of access:
SQL Server supports two types of login authentication:
After successful connection to SQL Server, the security mechanism is the same for both modes. For information about security inside SQL Server 7.0 (login IDs, user IDs, and permissions), see “Setting Up Database Access” later in this document.
During server registration, the local SQL Server installation is automatically set to use Windows NT Authentication. You can change the settings to also allow SQL Server Authentication.
There are two main factors that will control the way you need to secure your system:
These factors affect the following variables (which are the most important for anonymous access):
The following diagram shows how these factors and variables interact.
Considerations for setting up Windows NT accounts
The following sections, “Mixed Mode” and “Windows NT Authentication Mode,” provide details about the decisions and techniques mentioned in the diagram.
Important Regardless of the configuration you use, secure the SQL Server data and log files. Make sure that the account used by the MSSQLServer service has full control of these files. For more information, see SQL Server File Permissions in the “Managing Security” section of the SQL Server Books Online.
For more information, see “Security Architecture” in the “Managing Security” section of the SQL Server Books Online.
When using Mixed Mode authentication, there are several ways to ensure access to the SQL Server database. Any of the following methods can be used, depending on the needs of your configuration:
If SQL Server is installed on the same computer as IIS, when SQL Server attempts to validate the IIS user account (whether it is IUSR_computername or an authenticated user) the account is located in the local user database. Therefore, no problems with authentication occur. However, this configuration cannot be used with Personalization & Membership (P&M) because the Site Server LDAP Service cannot reside on the same computer as SQL Server 7.0.
If IIS and SQL Server are hosted on separate computers (the recommended configuration), the protocol used to connect the computers determines the type of authentication used.
If the Active Server Pages (ASP) file allows anonymous access, it is not possible to make a connection over Named Pipes to SQL Server on a different computer than the IIS computer unless IIS is being hosted on the domain controller.
If SQL Server resides on a separate server from IIS, the recommended way to provide a connection is to use TCP/IP. Although the connection between the two computers uses a non-authenticated protocol, access to SQL Server is granted on the basis of database login ID and password.
If you are using TCP/IP, do one of the following:
The IIS Setup program creates an IUSR_computername user account (where computername is the name of the server) and adds this account to the Guest local group. Any network activity performed by IIS is done under this account.
The domain controller’s user database is visible to all computers properly logged on to the Windows NT network, while any particular computer’s user database is visible to only that computer.
If IIS is installed on a primary domain controller or backup domain controller, the IUSR account is a domain account that is visible to all computers.
By default, the Guest account provides limited access. The administrator should make sure that the Guests group is not given any additional privileges. (Many companies have security procedures that do not allow the Guest account to be enabled.)
If SQL Server is configured for Mixed Mode authentication, it is possible for Commerce shopper pages to use a data source name (DSN) without a trusted connection and for Commerce management pages to use another DSN with a trusted connection. This configuration enables anonymous customers to visit the shopping site, but only managers to visit the manager pages.
Further reconfiguration of the network connectivity options should only be necessary if you are using a proxy server or a firewall system with the SQL Server installation. For more information about such configurations, see Connecting to SQL Server through Microsoft Proxy Server and Connecting to SQL Server over the Internet in the SQL Server Books Online.
For information about SQL Server security, see “Managing Security” in the SQL Server Books Online.
For information about setting up SQL Server, see “Managing Servers” in the SQL Server Books Online.
Some applications require Windows NT Authentication mode.
Windows NT Authentication mode allows a SQL Server to use Windows NT authentication methods to validate logins for all connections. Only trusted (multi-protocol or Named Pipes) connections are allowed. Windows NT Authentication enables a network user to log in to SQL Server without supplying a separate login ID or password.
When Windows NT Authentication mode is used, SQL Server treats a Windows NT administrator account as the sa account.
For a trusted connection to work, the IUSR_computername and computername\user accounts (for anonymous and authenticated users, respectively) need to be added to the Administrators group on the computer running SQL Server. These can be either domain-level accounts or local user accounts on Computer 2 (with the same login ID and password as on Computer 1).
There are some serious security concerns associated with this configuration. For example, when hosting multiple sites, you should not make a site operator a member of the Administrators group because an individual merchant should not have access to other merchants’ site files. In self-hosted environments, the privacy of other merchants’ data is not an issue; nevertheless, you may not want a site operator to have the rights and privileges of a member of the Administrators group.
If you are installing SQL Server 7.0 on a new computer, you will need to install the following components before you install SQL Server 7.0:
You do not need to reinstall Windows NT Server 4.0 if it is already installed as a standalone server or primary domain controller. These procedures are not recommended for backup domain controllers.
If you are installing SQL Server 7.0 on a SQL Server 6.5 computer, you only need to add the following components:
You should also set up any accounts that the computer will need in order to function in a secure environment. If you are using a SQL Server 6.5 computer, these accounts should already be in place.
If you are setting up a new Site Server/Internet Information Server system, you may also need to change the password of the IUSR_computername account on the IIS computers, or you may want to make the IUSR_computername account a domain account.
Note The following procedures assume that you are already logged on to Windows NT Server version 4.0 as an administrator, and that you have exited any programs that were running.
For additional information about installing Windows NT Server, see the Windows NT Server documentation.
On the CD-ROM, find the file nt4sp4.htm, and then open this file using Internet Explorer.
This file may open automatically when you insert the CD-ROM.
You will need to restart your computer to complete the configuration.
Note If you reinstall any Windows NT Server components later, you must reinstall Windows NT 4.0 Service Pack 4 as well.
You will need to restart your computer to complete the configuration.
SQL Server does not require the Windows NT Option Pack; you only need to install the Option Pack if you intend to install Site Server components (except Personalization & Membership) on the SQL Server computer. SQL Server performance will be better if the Windows NT Option Pack and the Site Server components are installed on other computers.
If you are modifying the IUSR_computername account (see Planning Security for SQL Server 7.0 Computers), you will need to use this procedure on each IIS computer. If an IIS computer is also running SQL Server 7.0, you do not have to use this procedure for that installation of IIS.
Note If any of the computer’s IIS Web sites are mapped to Membership Servers, the Edit button may not be available. You will need to unmap the Web sites, and then re-map them when you finish this procedure. For information about mapping and unmapping Web sites, see Mapping Application Servers to Membership Servers in the “Personalization & Membership” section of the Site Server 3.0 documentation.
For more information click the Help button.
If you are modifying the IUSR_computername account (see Planning Security for SQL Server 7.0 Computers), you will need to use this procedure to add the account to the SQL Server computer. If your IIS computers use accounts other than the default IUSR_computername account for this purpose, use this procedure to add each of the accounts.
As you install Microsoft® SQL Server™, observe the following requirements:
Important If you are using more than one SQL Server computer for the databases supporting a single partitioned container, all of the SQL Server computers supporting that container must use the same sort order. All of the SQL Server computers supporting a Membership Directory must use the same code page; this must be the same code page as the computer running the Membership Server.
If you install SQL Server 7.0 on a computer that runs Microsoft® Internet Information Server (IIS) or Site Server components other than P&M, observe the following additional requirements:
From a command prompt, execute the following command: net stop <service name>. You will also need to restart the services by executing net start <service name> after the upgrade is complete. The following services need to be stopped before the installation of SQL Server 7.0:
You will need to restart the computer in order for the setup program to finish. When the computer restarts, SQL Server 7.0 will be active. If you have installed SQL Server 7.0 on a SQL Server 6.5 computer, SQL Server 6.5 will still be intact. For information about using SQL Server 6.5 after installing SQL Server 7.0, see “Switching between Versions during the Upgrade” later in this document.
Important When you finish installing SQL Server 7.0, the sa account does not have a password. If you plan to use Mixed Authentication Mode, register the SQL Server installation and set the sa password (described in the following topics) as soon as possible.
The local SQL Server installation is registered automatically when you start SQL Server Enterprise Manager. Installations on other computers must be registered manually.
TCP/IP 1433
Note For information about configuring clients to use the TCP/IP network library by default, see Installing Client ODBC Driver and Network Libraries for SQL Server later in this document.
When you finish installing SQL Server 7.0, the sa account does not have a password. To use Mixed Authentication Mode, set the password as soon as possible after installation. If you are upgrading a SQL Server 6.5 system, set the new sa password to that of the SQL Server 6.5 sa account. You can change the password again after the upgrade is complete.
Note The sa login is used only if you are using Mixed Mode. In Windows NT Authentication mode, the Windows NT Administrator account fulfills this function.
Note The server must be registered to use SQL Server Enterprise Manager before the sa password can be changed.
For more information about the sa account, see “Managing Servers” in the SQL Server Books Online.
The SQL Server Upgrade Wizard can create SQL Server 7.0 databases to hold upgraded information. In addition to upgrading the database schema and data, it also transfers server configuration settings that are relevant to SQL Server 7.0, as well as all of the logon IDs and user IDs.
Note SQL Server 7.0 handles user accounts in a slightly different manner than SQL Server 6.5 does (see Setting up Database Access later in this document). After the upgrade is complete, you may want to review your login IDs and user IDs (in addition to routinely changing passwords) to make the best use of SQL Server 7.0 security. In addition, you should review the role and permission assignments in the SQL Server system.
If you are running a computer-to-computer upgrade, the SQL Server 6.5 computer is known as the export server and the SQL Server 7.0 computer is known as the import server. Both of these computers must reside in the same network domain. If you are running a side-by-side upgrade, the single computer functions as both the export server and the import server.
The computers involved in the upgrade must meet the following requirements:
Sufficient hard disk space must be available. SQL Server Upgrade Wizard estimates how much disk space is necessary. In addition to the disk space used by the SQL Server 7.0 application, you need approximately 1.5 times the size of your 6.5 databases. This space will accommodate:
When performing a computer-to-computer upgrade, only the computer with SQL Server 7.0 installed must meet the hard disk space and operating system requirements.
If you are low on disk space, you can use a tape backup in the upgrade process. Otherwise, the process uses a Named Pipe.
You can create database and log files before the upgrade, but it is much easier to allow the Upgrade Wizard to create files as needed. You can view and edit the following default database configuration in the wizard:
Important If you create the files manually, the databases must have the same names as in SQL Server 6.5.
It is recommended that you include the master database in your upgrade. This database includes a great deal of server configuration and security information. When upgrading the master database, you can configure:
The Upgrade Wizard also provides options for dealing with ANSI nulls. Make sure that the setting is compatible with that used by the original SQL Server 6.5 databases (this setting is especially important for Commerce Server and Ad Server databases).
Before you upgrade any databases, review the following checklist:
During the version upgrade process, the SQL Server Upgrade Wizard cannot create a login in SQL Server 7.0 for any SQL Server 6.5 logins using default databases that do not exist in SQL Server 7.0.
SQL Server 7.0 applies a database compatibility setting of 65 to all user databases newly upgraded from SQL Server 6.5. This setting instructs SQL Server 7.0 to perform certain functions in the way they were performed by SQL Server 6.5. To make the database fully compliant with SQL Server 7.0, you need to use the sp_dbcmptlevel system stored procedure to set the compatibility level to 70.
Site Server databases should be set to 70 compatibility immediately after they have been upgraded.
For complete information about backward compatibility and the database compatibility setting, see “Upgrading from an Earlier Version of SQL Server” in the SQL Server Books Online.
If you installed SQL Server 7.0 on the same computer as SQL Server 6.5, only one version of SQL Server can be active at a time. You can use the Microsoft SQL Server Switch command to change the active version. For example, if the Upgrade Wizard advises you to increase the size of the SQL Server 6.5 tempdb database, you can activate SQL Server 6.5, change the size of tempdb, and then reactivate SQL Server 7.0 and resume the wizard.
Important Unless you have used a tape drive during the upgrade, the upgrade process does not change the original SQL Server 6.5 databases. If you activate SQL Server 6.5 after you have upgraded the databases, the original databases will become operational.
Note When you change from one version of SQL Server to the other, make sure that the appropriate SQL Server services have started. You can check service status using the Services control panel. When SQL Server 6.5 is active, the MSSQLServer and SQLExecutive services should be running. When SQL Server 7.0 is active, the MSSQLServer and SQLServerAgent services should be running.
For more information, see “Upgrading from an Earlier Version of SQL Server” in the SQL Server Books Online.
Important Make sure that no clients attempt to access the database while you are upgrading it, or the upgrade may not operate properly. For example, stop any LDAP Services that use the database.
When the Upgrade Wizard finishes, Microsoft SQL Server 7.0 appears on the Start menu instead of Microsoft SQL Server 6.5.
After upgrading, check the output logs. In particular, make sure that the correct files were upgraded, and that no errors occurred while the server settings, data, or login IDs were transferred.
Important Membership Directory databases require additional updates (provided as part of Site Server Service Pack 2) before they are ready for use. See Updating the Membership Directory Databases in the “Updating Site Server to Work with SQL Server 7.0” section of this document.
For SQL Server 6.5, check that MSSQLServer and SQLExecutive have started.
For SQL Server 7.0, check that MSSQLServer and SQLServerAgent have started.
Note You can only use this procedure on a computer where both SQL Server 6.5 and SQL Server 7.0 are installed.
Microsoft® Site Server Service Pack 2 can only be installed on computers already running at least one Site Server 3.0 component.
Service Pack 2 contains all of the updates since the release of Site Server 3.0. When you install Service Pack 2, only the relevant files are applied to the existing Site Server components.
Before you update Site Server with Service Pack 2, review Readme.htm on the Service Pack 2 compact disk. For a list of hot fixes in this release and other related Knowledge Base articles, see Readme_KB.htm on the Service Pack 2 compact disk.
Use SS3SP2.exe (in the directory \x86 or \Alpha on the Service Pack 2 compact disk) to install Service Pack 2. SS3SP2.exe is an executable file that decompresses the Service Pack 2 files into a temporary directory, and then runs the Update.exe command. SS3SP2.exe supports the following command line switches.
| Switch | Description | |
| /q | Quiet mode (decompresses files with no screen presence) | |
| /C:Update.exe<option> | Runs Update.exe, which supports the following optional parameters: | |
| /m | Unattended mode | |
| /q | Quiet mode (unattended with no screen presence) | |
| /u | Uninstall mode | |
| /f | Force applications to close at shutdown | |
| /n | Do not create uninstall directory | |
| /z | Do not restart the computer after update if a file was locked during the update | |
| /t:<path> | Specifies the temporary directory name | |
| /c | Extracts files only | |
| /? | Views these options | |
Use this procedure on each Site Server computer, especially the LDAP Servers.
The installation program creates several registry values on the LDAP Servers. For information about these registry values, see the Site Server Service Pack 2 Readme. Any changes to the values are effective only when the LDAP Service instance is started or restarted.
After you have upgraded your Membership Directory databases to SQL Server 7.0, you must run a pair of scripts to update those databases to work with Site Server Service Pack 2. These scripts are installed on the Site Server computers when you run ss3sp2.exe. The service pack setup program places these scripts in a directory called %systemroot%\Site Server 3.0 SP2\Scripts.
Run the following scripts on each Membership Directory database:
A SQL Server supporting a Service Pack 2 LDAP Service will record harmless Windows NT Application Event messages until the scripts have run.
If you create a new Membership Server and Membership Directory using a Site Server installation that has already been upgraded to Service Pack 2, then you do not need to run mcis2upd.sql against that Membership Directory database. You will need to run instwab7.sql.
Note If you accidentally run instwab7.sql on a database that has not been upgraded and that you do not intend to upgrade, you can reverse the effect of the script by running remwab7.sql on that database.
Important Make sure that the %systemroot%\Site Server 3.0 SP2\Scripts directory is in a location where you can access it using SQL Server Query Analyzer. You can share the \Scripts directory so that it is accessible to applications running on any of your SQL Server computers, or you can copy the directory and its contents to the SQL Server computers.
If the script runs successfully, it will return the following message (the message may be displayed more than once):
The command(s) completed successfully.
If you need to connect to a different SQL Server computer, on the File menu, click Connect. Type the name of a SQL Server computer that is running one of the Membership Directory databases. Click Use SQL Server authentication, type the username and password for the database, and then click OK.
Note After you modify the Membership Directory, you must stop and restart the LDAP Service. To do this, you can use net stop ldapsvc and net start ldapsvc at a Windows NT command prompt.
If the script runs successfully, it will return the following message (it may be displayed more than once):
The command(s) completed successfully.
If you need to connect to a different SQL Server computer, on the File menu, click Connect. Type the name of a SQL Server computer that is running one of the Membership Directory databases. Click Use SQL Server authentication, type the username and password for the database, and then click OK.
If the script runs successfully, it will return the following message (it may be displayed more than once):
The command(s) completed successfully.
If you need to connect to a different SQL Server computer, on the File menu, click Connect. Type the name of a SQL Server computer that is running one of the Membership Directory databases. Click Use SQL Server authentication, type the username and password for the database, and then click OK.
If you are using a Microsoft® SQL Server™ database with Site Server Analysis, and if you are installing Analysis on client computers, you must install the SQL Server 7.0 client Microsoft Open Database Connectivity (ODBC) driver and network libraries on each client platform. If you plan to run Analysis only on the Microsoft® Windows NT® Server platform on which SQL Server is installed, it is not necessary to perform this step.
If you are setting up a new client computer, these files need to be installed before installing Analysis. This is only required for client computers because the SQL Server installation process automatically loads these files on the Windows NT Server platform when SQL Server is installed.
When you run the SQL Server setup program, it installs all of the client Net-Libraries on the client computer, and sets Named Pipes as the default client Net-Library. Use the SQL Server Client Network Utility to change the default Net-Library.
For more information, see the following topics in the SQL Server Books Online:
Use this section for new Microsoft® Site Server installations. A single Site Server installation can require multiple SQL Server databases, as described in “Planning Your Implementation or Upgrade” earlier in this document. After you have created and configured the databases as described here, they will function normally with Site Server and you can refer to the Site Server documentation for further information.
Use the values in the following table as guidelines.
| Database | Data File Size |
| Ad Server | 10 MB |
| Commerce Server | 20 MB |
| Membership Directory | 40 MB |
| Analysis | 50 MB |
Use the values in the following table as guidelines.
| Database | Log File Size |
| Ad Server | 5 MB |
| Commerce Server | 5 MB |
| Membership Directory | 10 MB |
| Analysis | 10 MB |
Important If you do not use the Truncate log on checkpoint option, the log file will eventually fill up as a result of normal activity, causing database operations to stop. You can prevent this by periodically using the BACKUP LOG command to save and truncate the log file.
The new database is now ready for further configuration.
To configure a Commerce database manually, see “To manually load the schema into a Commerce database” later in this document.
If you already have one or more fully configured Ad Server database, see “To copy an existing schema into a new Ad Server database” later in this document.
If you need to create additional Ad Server databases (after fully configuring the first Ad Server database), you can copy the existing database schema to the new database.
If you have not already created an ODBC data source name (DSN) for this database, do so now (see “Setting Up ODBC System Data Sources” later in this document). Remaining tasks and procedures involving the Ad Server database are described in your Site Server documentation. In particular, refer to the Ad Server Scenarios topic in the “Advertising Server” section of the Site Server, Commerce Edition documentation.
Normally, Commerce Server’s Site Creation Wizard will load the schema into the Commerce database for you. Loading the schema manually is necessary only if you did not select the Load Schema into database check box on the Site Creation Wizard’s Output Options page.
If you selected both the Price Promotions and the Cross Promotions check boxes when you ran the wizard, two database tables are needed to support promotions. The wizard generates a SQL script named wiz_schema.sql that you can execute to create one or both of these tables.
If the script executed successfully, the following message appears:
This command did not return data, and it did not return any rows.
If you have not already created a DSN for this database, do so now (see “Setting Up ODBC System Data Sources” later in this document). Remaining tasks and procedures involving the Commerce database are described in your Site Server documentation. In particular, refer to the “Commerce HowTo” section of the Site Server, Commerce Edition documentation.
To help you set up the Analysis database on SQL Server, Site Server includes the Database Setup Wizard. This setup program can create a SQL Server database for you, as well as load it with Analysis table definitions, views, and stored procedures.
Important If you use the Database Setup Wizard to create the database, some of the wizard steps will differ from those listed here, and you may be required to restart the SQL Server computer.
Note If you installed Site Server 3.0 over Site Server 2.0, the Database Setup Wizard automatically upgrades the Site Server 2.0 Analysis database for use by Site Server 3.0.
The following procedure assumes you have created an Analysis database using the SQL Server configuration instructions provided in this chapter, and you are ready to update your existing SQL Server database with Analysis data.
Note If this command does not appear on the menu, then you may have to install the SQL Server support components of Analysis. Run the Site Server Setup program, and in the list of components to add, select SQL Server Database Support.
If you have not already created your Analysis database, you can choose Typical or Custom in order for the Database Setup Wizard to create the database for you.
The Database Setup Wizard populates the Analysis tables with default data.
Remaining tasks and procedures involving the Analysis database are described in the Site Server documentation. In particular, refer to the “Analysis” section of Getting Results with Microsoft Site Server.
Your databases and database management system can reside on a computer other than the computer that runs the Microsoft® Site Server components. However, if you distribute your databases over different computers, you must set up a different ODBC data source name (DSN) for each Analysis, Commerce, or Ad Server database. You may also want to set up separate DSNs to restrict database access.
Note For best performance when Microsoft® SQL Server and Microsoft® Internet Information Server (IIS) are running on a single computer, use a system DSN and set the server name to (local). This prevents IIS from looking for SQL Server on the network.
IIS and SQL Server can communicate using DSNs. TCP/IP must be enabled both in SQL Server and in the DSN on the computer running IIS.
If SQL Server is configured for Mixed Mode authentication, it is possible for Commerce shopper pages to use a DSN without a trusted connection and for Commerce management pages to use another DSN with a trusted connection. This configuration enables anonymous customers to visit the shopping site, but only managers to visit the manager pages.
Note If you are using SQL Server Authentication, when you create a DSN to connect to the database, be sure to select With SQL Server authentication using a login ID and password entered by the user. Otherwise the DSN might attempt to override the SQL Server Authentication setting.
This procedure assumes you have not yet installed the Microsoft® Windows NT® 4.0 Option Pack. If you have installed it, you will be prompted for additional information.
When you create the system DSNs, be sure to note what they are. Commerce Server and Ad Server require this information in order to function correctly.
This section describes the login IDs that you need to access SQL Server, and the user IDs you need to configure for database access. Each user or service has a login ID, and a user ID in each database that the user or service needs to use.
Login IDs are the accounts that control access to SQL Server. If you are using Mixed Mode authentication, you can use SQL Server logins. If you are using Windows NT Authentication mode, you will be using Windows NT user names and passwords. If a user attempts to connect to SQL Server providing a blank login name, SQL Server automatically uses Windows NT Authentication.
Each login ID has a default database. After you assign a default database, you can create a user ID in that database and assign that user ID to a database role. By default, a user ID has the name of its associated login ID. When a login connects to SQL Server, the default database becomes the current database for the connection, unless the connection request specifies that another database be made the current database.
User IDs identify users within a database. A user ID is specific to a particular database. All permissions and ownership of objects in the database are controlled by the user ID. To access a database, a login ID must be associated with a user ID in that database.
SQL Server 7.0 uses roles to collect users into a single unit against which you can apply permissions. It can also apply permissions to Windows NT groups. The two types of predefined roles are fixed server and fixed database. Fixed server roles are defined at the server level and exist outside of individual databases. Each database has a set of fixed database roles.
SQL Server logins, users, roles, and passwords can contain from 1 to 128 characters.
For more information about setting up database access, see the following topics in the SQL Server Books Online:
The service administrator account sa is assigned to the System Administrators (sysadmin) fixed server role and cannot be changed. It should not be used routinely. A person with direct access to the database could steal or modify data or account permissions in the Membership Directory (for example, the bypass-ACL-checking privilege could be granted to an account, thereby giving it total control). Create new SQL Server logins with hard-to-guess account names and passwords, and make these accounts members of the System Administrators fixed server role. Write the login names and passwords down and put them in a safe. Never leave this information written down in an easily accessible place.
Members of System Administrators can perform any activity in SQL Server. You do not have to specify which databases these users or services can use. If access permission is not explicitly assigned (and a user ID created in the database), the user or service uses the dbo user ID. This user ID is a member of each database’s db_owner role.
For a login ID that is a member of a role other than System Administrators, you should specify a default database for the login ID (other than master). You must specify which databases that login ID can use. SQL Server will automatically create a user ID in each database you specify, using the name of the login ID as the name of the user ID. You can then assign the user ID to one or more database roles. The fixed database role db_owner has all permissions in the database.
Important You must create databases and login IDs before you can create user IDs.
The following table lists the recommended server roles and database roles for different types of Site Server users and services.
| User/Service | Server Role | Database | Database Role |
| Analysis database owner | Sysadmin | Analysis database | Db_owner |
| Analysis database users | Not assigned | Analysis database | Public (default) |
| LDAP Service | Sysadmin | Membership Directory root database and partition databases | Db_owner |
| Commerce pages | As appropriate | Commerce database | As appropriate |
| Ad Server pages | As appropriate | Ad Server database | As appropriate |
Note Creating a separate login ID for Analysis database users allows you to control users’ access to restricted areas of SQL Server.
Note When Mixed Mode authentication is used, the Commerce Server site’s connection string contains the data source name (DSN) or user name and password that enables access to the database.
For more information about login IDs, user IDs, and roles, see Setting up Security Accounts in the “Managing Security” section of the SQL Server Books Online.
Note You do not have to select specific databases or set up user IDs for this user or service. The user or service will have access to all of the databases in this SQL Server installation using the dbo user ID, which belongs to the db_owner role.
If you want to specifically deny this user or service access to SQL Server, click Deny access. The default setting is Grant Access.
Note After you have created user IDs for users, you must assign object and statement permissions for the users.
Every object in SQL Server is owned by a user (identified by a database user ID). With the exception of the system administrator and the database owner, no one can gain access to a database or other object until they have been granted the appropriate permissions.
Only members of the db_owner role can create accounts and assign permissions. Permissions can only be granted on a database-by-database basis.
Observe the following guidelines when assigning permissions to Site Server services and users:
For more information about permissions, see the following topics in the “Managing Security” section of the SQL Server Books Online:
This section contains guidelines and techniques for improving the performance of your system, including:
Backing up critical data is essential to maintaining a secure, reliable system. You must take appropriate steps to protect the safety of the data in the databases, especially when you use Membership Authentication. The following databases should be backed up regularly and frequently:
In addition, the password encryption key (PEkey) must be backed up to ensure that the LDAP service always has the ability to read passwords from the Membership Directory database.
For detailed information about creating backup devices and backing up SQL Server databases, see “Backing up and Restoring Databases” in the SQL Server Books Online.
As you configure a new installation of Site Server, you may want to load a large amount of data into the Membership Directory. To improve performance when loading this data, you can use a script containing the following set of commands to temporarily drop the following SQL Server indexes:
DROP INDEX Object_Attributes.IND_int_Aid
go
DROP INDEX Object_Attributes.IND_vc_Aid
go
DROP INDEX Object_Attributes.IND_date_Aid
go
DROP INDEX Object_Attributes.IND_Aid
go
When you have finished loading data, use a script containing the following set of commands to recreate the indexes:
CREATE INDEX IND_int_Aid ON dbo.Object_Attributes(i_Val, i_Aid) with FILLFACTOR=70
GO
CREATE INDEX IND_vc_Aid ON dbo.Object_Attributes(vc_Val, i_Aid) with FILLFACTOR=70
GO
CREATE INDEX IND_date_Aid ON dbo.Object_Attributes(dt_Val, i_Aid) with FILLFACTOR=70
GO
CREATE INDEX IND_Aid ON dbo.Object_Attributes(i_Aid) with FILLFACTOR=70
GO
The Membership Directory has been designed to provide fast direct lookup. However, to allow for other types of searches, the SQL Server database is configured with many indexes. In addition to a clustered index (fast direct lookup), the SQL Server database is configured with indexes for each data type (integer, date/time, or string). Updating these indexes can slow the performance of SQL Server. The most performance-intensive indexes are those on the Object_Attributes table. If, for example, you know that you will only be doing direct lookups (such as for authentication) you can increase your Add/Modify throughput significantly by dropping some of the other indexes (using SQL Enterprise Manager).
Another tuning characteristic is the index fill factor. The higher the fill factor, the more free space is reserved in the index and the less often SQL Server needs to adjust the indexes when adding new objects. By default, the fill factor is set to 70 percent. If you anticipate more than 5 percent writes, use a fill factor lower than 70 percent. The indexes will take up more space, but you will get better write performance while maintaining full query support and performance. Use SQL Enterprise Manager to change the fill factor.
The following table lists the indexes required for each data type (integer, date/time, or string). Each column gives the approximate Add performance gained by removing or tuning the index. Actual performance will vary depending on the makeup of your data and your hardware configuration.
| Purpose of Index | Index Name | Fill Factor for 1% Writes | Fill Factor for >10% Writes | Approx. Performance Gain from Dropping Index |
| Searching on Integer Attributes | Ind_Int_Aid | 0% | 50% | 20% |
| Searching on Date/Time Attributes (such as CreateTime, ModifyTime) | Ind_DT_Aid | 0% | 50% | 20% |
| Searching on String Attributes (such as anything other than the unique object identifier) | Ind_VC_Aid | 0% | 50% | 100+% |
If you are using a SQL Server system dedicated to Site Server databases, you can manually adjust the following settings in order to optimize the performance of the SQL Server database:
Note If you stop and restart SQL Server, the LDAP Service may not be able to reconnect to the database on its first attempt, especially if your Membership Directory is very large. It will reconnect on subsequent attempts.
Note For best performance when SQL Server and Internet Information Server (IIS) are running on a single computer, use a system DSN and set the server name to (local). This prevents IIS from looking for the SQL Server on the network.
Configuring the SQL Server memory is perhaps the best way to improve the performance of SQL Server immediately.
Memory allocation must be balanced with all of the processes running on a computer, because all processes call upon the same pool of available memory. Thus, allocating too much memory for any one process can adversely affect system performance. For this reason, it is recommended that you use SQL Server on a dedicated computer for optimal performance. Dedicating a computer to SQL Server allows all memory not required by the operating system to be allocated to SQL Server.
However, not everyone needs the power of a dedicated SQL Server computer. For small and medium-sized installations, multiple Microsoft Site Server components (except for the Site Server LDAP Service) can be installed on the same computer with no degradation in performance.
Unlike previous versions of SQL Server, SQL Server 7.0 dynamically allocates and frees memory. The automatic settings normally will produce the best performance. If you need to set different values, you can specify a range of memory for SQL Server to work within (using the max server memory and min server memory options).
The following table indicates the recommended configuration if SQL Server is on a dedicated computer. The appropriate value should fall within the range specified by your SQL Server installation.
| System Memory | SQL Server Memory Allocation |
| 32 MB | 16 MB |
| 48 MB | 28 MB |
| 64 MB | 40 MB |
| 128 MB | 100 MB |
| 256 MB | 216 MB |
| 512 MB | 464 MB |
If you install Site Server components on the same computer as SQL Server, a portion of the available system memory must be available for those components when they are running. For example, the following table indicates the recommended configuration if SQL Server shares a computer with Usage Import.
| System Memory | SQL Server Memory Allocation |
| 32 MB | 8 MB |
| 48 MB | 16 MB |
| 64 MB | 24 MB |
| 128 MB | 32 MB |
| 256 MB | 64 MB |
| 512 MB | 128 MB |
If enough memory is available, increasing the minimum memory per query can improve the system efficiency for query operations. The default setting is 1024 K.
For information about memory usage in SQL Server, see the following topics in the SQL Server Books Online:
The following steps describe how to modify SQL Server configuration options using the SQL Enterprise Manager. If you prefer, you can implement any of these modifications by using the sp_configure system stored procedure call.
SQL Server 7.0 databases are stored as files. Logical database devices are no longer used. Each database consists of its own set of files, which consists of data files and transaction log files. You can set the growth increment for each database (the amount that the database expands at one time). You can also set a maximum size for each file.
Note Monitor the growth of database files so that the hard disk does not run out of space.
By default, the temporary storage database, tempdb, grows and shrinks automatically. For a typical installation, the default size of the tempdb data file (tempdb.mdf) is 8 MB. The default size of the tempdb log file (templog.ldf) is 5 MB.
For information about database files, see “Database Architecture” in the SQL Server Books Online.
Maintaining locks on objects in the database is another internal use of the memory allocated to SQL Server. Changes in the number of locks have a less dramatic effect on overall performance than allocating user connections. However, reducing the number of locks can provide additional memory for the memory pool.
SQL Server 7.0 manages locks automatically. You can set a value for the maximum number of locks and this value will override the automatic setting. If the number of locks is set too low, the following error might occur intermittently when running Site Server components:
Error: ???, Severity: ??, State: ?
???.
If this error occurs, you can slightly increase the number of locks available to the system. You must stop and restart SQL Server in order for this change to take effect.
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘locks’, [number]
GO
RECONFIGURE
where [number] is the new number of locks.
If the script runs successfully, it will return the following message (the message may be displayed more than once):
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option changed. Run the RECONFIGURE statement to install.
Important Only experienced SQL Server administrators should attempt to use this procedure. In addition, this procedure requires that SQL Server be stopped and restarted.
Commerce Server and Ad Server sites interact more directly with their databases than other components of Microsoft® Site Server, Commerce Edition, and you can modify those interactions directly by working with the Commerce or Ad Server site code or with SQL Server options. Therefore, this section includes additional information about optimizing performance.
A Commerce Server site populates its pages with data obtained dynamically from its database or databases. Commerce Server sites depend on highly efficient database access for many operations.
The performance impact of communicating with the database is probably the most significant element in a configuration. Improving database interaction, therefore, is critical for improving performance.
To most effectively optimize SQL Server performance, focus on the areas that will yield the largest performance increases in the widest variety of situations. SQL Server includes an intelligent, cost-based query optimizer that can quickly determine the best access method for retrieving the data, including the order in which to join tables, and whether or not to use indexes that might be on those tables.
There are a variety of options for improving SQL Server database interaction. The greatest benefit in SQL Server performance can usually be gained when you optimize the following areas:
The biggest performance problems are often caused by deficiencies in these areas. By concentrating on these areas first, you can often achieve large performance improvements with a relatively small time investment.
Note For best performance when SQL Server and Internet Information Server (IIS) are running on a single computer, use a system DSN and set the server name to (local). This prevents IIS from looking for the SQL Server on the network.
For information about optimizing SQL Server performance, see the topic Optimizing Database Performance in the SQL Server Books Online.
Commerce Server uses Microsoft® ActiveX® Data Object (ADO) for internal database access within its components. ADO helps with performance related to user connections and provides fast interaction with the database, improving accessibility and manipulation of data in a database server.
To take the best advantage of ADO, you must configure it properly. This is especially critical for Commerce Server because Commerce Server utilizes ADO for its internal objects.
Note ADO can now be used with the OLE DB Provider for SQL Server instead of the Microsoft OLE DB Provider for ODBC over the SQL Server ODBC Driver.
Traditional database applications create a single connection to the database that is used for the duration of the application. Because of the stateless nature of the Web, however, a Web-based database application must open and close a new connection on each page, increasing the number of connections to the database.
Connection pooling maintains open database connections and manages connection sharing across different user requests to maintain performance and to reduce the number of idle connections. On each connection request, the connection pool first determines whether there is an idle connection in the pool. If so, the connection pool returns that connection instead of making a new connection to the database. Connection pooling is turned on by default.
Note Unlike previous versions, SQL Server 7.0 does not use temporary stored procedures. SQL Server 7.0 will accept SQL Server 6.5-based code that requires temporary stored procedures, but it is recommended that you revise this code.
For information about connection pooling and SQL Server 7.0, see the following topics in the SQL Server Books Online:
The optimizer’s goal is to optimize input/output (I/O). There are a number of reasons for I/O bottlenecks. You can use the SQL Server performance counters, as well as some of the tools discussed in the Using Performance Monitoring Tools topic in the “Commerce Server” section of the Site Server, Commerce Edition documentation, to uncover I/O bottlenecks in your configuration.
SQL Server allows block fetches of binary data only when all timestamp, image, text, and other long data is specified after all non-long data. This can be accomplished without modifying a database schema by simply reordering the column names in the SQL text, instead of using a *. For more information, see the topic SQLGetData (ODBC) in the “Building SQL Server Applications” section of the SQL Server Books Online.
Microsoft® SQL Server™ provides its own support for monitoring SQL Server installations.
You can monitor the status of SQL Server installations, performance information, databases, and view server and database configuration parameters.
Using SQL Server Profiler (formerly known as SQL Trace), you can view the performance of three types of database information:
Configuration information includes database options, sizes, and permissions. The types of configuration information you can access include:
SQL Server Profiler provides information about events occurring dynamically against your database (such as user activity). You can also get such information from Windows NT Performance Monitor. Using these counters, you can determine the load on the database and make decisions for scaling to include modifications to your system configuration, additional SQL Server computers, or increased memory or processors for your existing servers.
Of particular interest is Cache Hit Ratio, which should ideally show that the cache is maximized. Under ideal circumstances, all queries to the database are cached in memory. This reduces costly database I/O.
For more information about SQL Server counters, see SQL Server Objects and Counters in the “Administering SQL Server” section of the SQL Server Books Online.
Database space information is useful for determining whether the database space allocated for the content is sufficient. The administrator can also set up an automatic warning to indicate when a database gets close to being full.
Verify that the following services are running:
Microsoft® SQL Server™ names are derived from the name of the computer where SQL Server is installed. You may get a SQL Server error if the characters in the computer name are characters that SQL Server does not allow. SQL Server names have the following requirements:
For example, a SQL Server database could have one of the following names: _GIZA, _$GIZA, or GIZA_2.
In a Membership Directory database, table and column names must only contain characters that are valid in the Membership Directory. For a list of valid characters, see Creating New Objects in the Membership Directory in the “Personalization & Membership” section of the Microsoft® Site Server 3.0 documentation.
This section contains a list of messages that commonly occur in the course of Ad Server operations, and recommended courses of action for each.
| Possible causes | Possible solutions |
| When using Microsoft® Windows NT® Authentication, the ODBC data source name (DSN) is configured to use Windows NT authentication using the network login ID instead of username and password. | To reconfigure the DSN:
On the Start menu, point to Settings, and then click Control Panel. Double-click ODBC Data Sources. On the System DSN tab, double-click the DSN you want to check. On the second screen of the Microsoft® SQL Server™ DSN Configuration Wizard, click With SQL Server authentication using a login ID and password entered by the user. At the bottom of the screen, supply the account name and password that Ad Server should use to connect to the database. In the ODBC Microsoft SQL Server Setup dialog box, click Test Data Source. |
| Possible causes | Possible solutions |
| Default database not specified in ODBC DSN. | To specify the default database:
On the Start menu, point to Settings, and then click Control Panel. Double-click ODBC Data Sources. On the System DSN tab, double-click the DSN you want to check. On the third screen of the Microsoft SQL Server DSN Configuration Wizard, click the Change the default database to list, and click the Ad Server database. In the ODBC Microsoft SQL Server Setup dialog box, click Test Data Source. |
| Possible causes | Possible solutions |
| Invalid login ID or password has been specified for the database. | To check the login ID and password for the database:
On the Start menu, point to Settings, and then click Control Panel. Double-click ODBC Data Sources. On the System DSN tab, double-click the DSN you want to check. In the Microsoft SQL Server DSN Configuration Wizard, verify that the password and login ID match the password and login ID given in the connection string. Verify that the correct default database is specified. In the ODBC Microsoft SQL Server Setup dialog box, click Test Data Source. |
| Database computer is not set up with proper IUSR_computername account in Windows NT. | Check the Windows NT accounts on the SQL Server computer. |
| Possible causes | Possible solutions |
| Problem with the Named Pipes network library | To change the client configuration to use TCP/IP transport:
Make sure SQL Server is running (note that SQL Server has to be configured to use TCP/IP, as well). On the Start menu, point to Settings, and then click Control Panel. Double-click ODBC Data Sources. On the System DSN tab, double-click the DSN you want to change. On the second panel of the SQL Server DSN Configuration Wizard, click Client Configuration. In the Network libraries column, click TCP/IP, and then in the Computer box, type the name of the SQL Server computer. Click OK. |
| Possible causes | Possible solutions |
| The ad samples installed with AdServer read the connection string from the Site.csc file (created in the site\config directory during setup). Because of this, changing the DSN name, SQL Server login ID, or login ID password after running setup makes these pages generate errors when they try to connect to the database. | You must explicitly set the ConnectionString property in the Global.asa file because there is no way to directly edit the Site.csc file. |
| The DSN name was incorrectly specified when setting the ConnectionString property. | To check the DSN:
On the Start menu, point to Settings, and then click Control Panel. Double-click ODBC Data Sources. On the System DSN tab, double-click the DSN you want to check. In the Microsoft SQL Server DSN Configuration Wizard, verify that the correct server, password and login ID, and default database are specified. In the ODBC Microsoft SQL Server Setup dialog box, click Test Data Source. Verify that the DSN, username, and password used in the connection string in Global.asa are correct and match the information you entered in the ODBC Data Source Administrator. |
| Possible causes | Possible solutions |
| SQL Server was stopped. | Start SQL Server (making sure that the MSSQLServer and SQLServerAgent services are running). Close the current AdManager application. Restart AdManager. |
A full tempdb database can cause varied problems. Because the SQL Server tempdb database provides storage for temporary tables and other temporary working storage needs, when the database can no longer grow, no more operations can be performed. By default, tempdb grows automatically. However, it may reach a maximum size restriction or it may run out of disk space (especially if the tempdb files share disk space with other database files).
For information about SQL Server tempdb usage, see “Optimizing tempdb Performance” in the SQL Server Books Online.
| Possible causes | Possible solutions |
| The SQL Server tempdb database is not at least 4 MB in size, and does not have the Truncate Log On Checkpoint option selected. | Reconfigure the tempdb database. |
The interactions between the Personalization & Membership (P&M) components of Microsoft® Site Server and Microsoft® SQL Server™ are particularly complex. This section lists problems you may encounter that are specific to the P&M components (such as the LDAP Service and the Membership Directory database).
The following event sequence in an Event Log of an LDAP Server indicates that you have not run mcis2upd.sql against one or more of your Membership Directory databases. If this event sequence appears, make sure that you have run mcis2upd.sql against all of your target databases. To verify that all supporting databases have been updated, monitor the event log for further occurrences of this sequence.
| Source | Event ID | Description |
| PM Directory Store | 30 | SQL Error: HRESULT: 80040e14 Minor Code: 2812 Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Stored procedure 'sp_QueryTimestamps' not found. SQLErrorInfo: 2812, 37000 |
| PM Directory Store | 150 | Missing timestamp stored procedure indicates that the master database for this server may not have been updated with the mcis2upd.sql script. Please run this script from your Site Server 3.0 Update for the MCIS 2.0 installation site |
When you are creating a Membership Directory using a new SQL Server database, remember:
Note If you receive the error message “The New Membership Server Wizard has failed to create a new LDAP Service. Continue?”, click No, and then use the preceding checklist to re-check your configuration.
This section presents a pair of examples of upgrade configurations. One example is of a system where the computer-to-computer upgrade approach is appropriate. The other example is of a system where the side-by-side upgrade approach is appropriate. The size and deployment of the system and its components play an important role in determining which approach to use.
Consider a system with less than 500,000 users, where the LDAP service and SQL Server 6.5 have been installed on a single computer (called the Membership Directory computer).
Because SQL Server 7.0 needs to be on a computer separate from the LDAP service, you would need to add a dedicated SQL Server 7.0 computer to this configuration. Run a computer-to-computer upgrade, and then configure the LDAP service to use the new SQL Server computer. After the upgrade, the original Membership Directory computer now functions as a dedicated LDAP server computer.
You cannot run a side-by-side upgrade in this case, because the LDAP service resides on the SQL Server 6.5 computer. You must set up a new computer, and run a computer-to-computer upgrade. The following diagram illustrates the upgrade configuration.
The logistics for such an upgrade involve the following:
Consider a system with approximately 2,000,000 users. Such a system would have five SQL Server 6.5 computers: one for the Membership Directory root database, and one for each of four partition databases. The system also has two dedicated LDAP Service computers.
If you were to run computer-to-computer upgrades, the logistics would involve the following:
If you were to run side-by-side upgrades, the logistics would involve the following:
In this case, the side-by-side upgrade approach is more efficient than the computer-to-computer upgrade. The following diagram illustrates the configuration needed for upgrading a system with five SQL Server computers, using the side-by-side upgrade approach.
Information in this document, including URL and other Internet web site references, is subject to change without notice. The entire risk of the use or the results of the use of this resource kit remains with the user. This resource kit is not supported and is provided as is without warranty of any kind, either express or implied. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
© 1999-2000 Microsoft Corporation. All rights reserved.
Microsoft, ActiveX, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries/regions.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.