Site Server - Using Site Server with SQL Server 7.0
August 1999
Introduction
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:
The LDAP Service (Personalization & Membership) must be installed on a separate computer from SQL Server 7.0. MDAC 2.1, which is installed by the SQL Server 7.0 setup process, is not compatible with the LDAP Service.
There are several known issues relating to the use of the Microsoft® Active Channel™ Multicaster (ACM) Service in a SQL Server 7.0 environment. At this time, Microsoft does not support the ACM Service when installed on a SQL Server 7.0 computer.
For most LDAP commands, upgrading the Membership Directory to SQL Server 7.0 will provide equal or enhanced performance over SQL Server 6.5. However, if you perform subtree searches in the Membership Directory that search on attributes other than the Relative Distinguished Name (RDN), performance may not be better than on SQL Server 6.5.
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.
Planning Your Implementation or Upgrade
The following Microsoft® Site Server 3.0, Commerce Edition features require separate databases for their storage needs:
Personalization & Membership (P&M) requires a database for storing Membership data.
Analysis requires a database for storing usage data.
Commerce Server requires a database for storing sample site data.
Ad Server requires a database for sample advertisement data.
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.
Planning the Upgrade
The process of upgrading your installation consists of multiple phases:
Install SQL Server 7.0 on the SQL Server 6.5 computer or on another computer in the same domain as the SQL Server 6.5 computer. See the discussion later in this section for guidelines regarding the approach to use.
Shut down all services that need access to the databases.
Upgrade the databases from SQL Server 6.5 to SQL Server 7.0, using the SQL Server Upgrade Wizard or customized upgrade scripts.
Set the database compatibility level of each database to 70. This setting can only be changed by the stored procedure sp_dbcmptlevel.
Upgrade the Site Server components to Site Server Service Pack 2.
Update the Membership Directory databases with special scripts. After these scripts have run, the databases can work with Site Server Service Pack 2.
Start all services.
For examples of how this process could be implemented, see the Appendix, "Upgrade Strategies."
SQL Server 7.0 offers two approaches for upgrading:
Side-by-side upgrade (single computer). This method involves installing SQL Server 7.0 on your computer running SQL Server 6.5. The Upgrade Wizard uses a direct pipeline if enough disk space is available. Otherwise, the wizard exports data to a tape, reclaims space, and then imports and upgrades the data. Both versions of SQL Server and their databases remain intact, although only one version can function at a time. You can use this approach with Site Server as long as the computer is not running the Site Server LDAP Service.
Computer-to-computer upgrade. This method involves installing SQL Server 7.0 on a separate computer, and connecting the SQL Server 6.5 computer and the SQL Server 7.0 computer using Named Pipes. The upgrade process copies data to the SQL Server 7.0 computer. Both versions of SQL Server and their databases remain intact and functional. This approach adds computers to your system, but it is the recommended approach to use if your LDAP Service shares a computer with SQL Server 6.5.
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:
Hard disk space. In addition to the hard disk space used by SQL Server 7.0, you need approximately 1.5 times the disk space used by the SQL Server 6.5 databases. If you are using the computer-to-computer approached, only the SQL Server 7.0 computer must meet the hard disk space and operating system requirements.
Named Pipes protocol. For a computer-to-computer upgrade, the two computers must be able to communicate using a Named Pipe. For a side-by-side upgrade, Named Pipes are recommended, but the upgrade process can use a tape drive to get around this requirement.
After you have determined your upgrade approach, the following sections of this document will guide you through the upgrade process:
"Preparing the SQL Server Computer"
"Installing SQL Server 7.0 and Upgrading Existing Databases"
"Updating Site Server to Work with SQL Server 7.0"
In addition, you should be familiar with the section "Upgrading from an Earlier Version of SQL Server" in the SQL Server Books Online.
Upgrading Specialized Configurations
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.
Planning Security for SQL Server 7.0 Computers
When you are implementing security for your Microsoft® SQL Server™ installation, you need to consider how you will support the following types of access:
Administrative access to computers and databases.
Service access to SQL Server and databases.
Anonymous site user access to data.
SQL Server supports two types of login authentication:
Microsoft® Windows NT® Authentication Mode. SQL Server validates that the user has a valid Windows NT account.
Mixed Mode (Windows NT Authentication and SQL Server Authentication). SQL Server can maintain its own separate database of logins, or accept Windows NT accounts as logins. Many Site Server components (such as the P&M LDAP Service) have been most extensively tested using SQL Server 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:
Number and function of computers (Is SQL Server installed on a dedicated computer?)
Authentication type for SQL Server
These factors affect the following variables (which are the most important for anonymous access):
Protocol (TCP/IP or Named Pipes)
Windows NT accounts. You may need to use one or both of the accounts below:
IUSR_ computername. This can be a local or domain account, and may or may not need to be a member of the Administrator group on the SQL Server computer.
Guest. Use this with the Named Pipes configuration only. If you use TCP/IP, you do not need the Guest account.
The following diagram shows how these factors and variables interact.
.gif)
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.
Mixed Mode
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:
Install SQL Server on the same computer as Microsoft® Internet Information Server (IIS) (use this approach with Site Server components other than P&M).
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.
Install SQL Server on a different computer than IIS, and do one of the following:
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.
Use TCP/IP to connect the two computers (this method is recommended for use with the Site Server LDAP Service).
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:
On the SQL Server computer, add a Windows NT local user account with the same name as the local user account on the computer hosting IIS (IUSR_computername). The account passwords must match exactly.
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.
Make the IUSR_computername account a member of the domain where SQL Server resides.
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.
If you cannot use TCP/IP, enable the Windows NT Guest account on the remote SQL Server computer.
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.
Windows NT Authentication Mode
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.
Preparing the SQL Server Computer
Preparing a New Computer
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:
Microsoft® Windows NT® Server 4.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.
Windows NT 4.0 Service Pack 4
Microsoft® Internet Explorer 4.01 Service Pack 1 (available on the Windows NT 4.0 Service Pack 4 CD-ROM).
Preparing a SQL Server 6.5 Computer
If you are installing SQL Server 7.0 on a SQL Server 6.5 computer, you only need to add the following components:
Windows NT 4.0 Service Pack 4
Internet Explorer 4.01 Service Pack 1
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.
You may want to enable the Windows NT Guest account.
You may need to set up the Windows NT IUSR_ computername account.
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.
To install Windows NT Server 4.0
Run the Windows NT Server 4.0 Setup program and follow the online instructions. Observe the following requirements:
Install Windows NT Server 4.0 only as a standalone server, or primary domain controller.
Use only alphanumeric characters in the computer name because SQL Server supports a limited character set in server computer names.
Format the disk partition as a Windows NT File System (NTFS) partition to allow the setting of access controls (permissions) for security purposes.
Install ODBC Driver for SQL if that option is offered by Windows NT Server Setup.
For additional information about installing Windows NT Server, see the Windows NT Server documentation.
To install Windows NT 4.0 Service Pack 4
Insert the Windows NT 4.0 Service Pack 4.0 CD-ROM into the appropriate drive.
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.
In the menu pane, click Install Service Pack 4.
Follow the instructions provided by the setup application.
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.
To install Internet Explorer 4.01 Service Pack 1
Insert the Windows NT 4.0 Service Pack 4.0 compact disc into the appropriate drive.
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.
In the menu pane, click Microsoft Internet Explorer 4.01 Service Pack 1.
In the content pane, click Install IE 4.01 SP1 for Intel-based Systems or Install IE 4.01 SP1 for Alpha-based Systems, depending on which type of system you have.
Follow the instructions provided by the setup application.
You will need to restart your computer to complete the configuration.
To install the Windows NT 4.0 Option Pack
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.
Insert the Windows NT 4.0 Option Pack compact disc into the appropriate drive.
A warning will appear that Windows NT 4.0 Option Pack has not been fully tested on Windows NT 4.0 Service Pack 4. Click Yes to continue. This warning may appear more than once. An introductory screen appears automatically.
Follow the instructions provided by the setup application.
Restart your computer to complete the configuration.
To enable the Windows NT Guest account on the remote SQL Server computer
Log on to the SQL Server computer as an administrator.
On the Start menu, point to Programs, point to Administrative Tools (Common), and then click User Manager for Domains.
If necessary, select the domain that contains the SQL Server computer. On the User menu, click Select Domain, and then type or click the appropriate domain.
Under User name in the top part of the window, double-click the Guest user account, clear the Account Disabled check box, and then click OK.
To set a password for the anonymous account
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.
On the Start menu, point to Windows NT 4.0 Option Pack, point to Microsoft Internet Information Server, and then click Internet Service Manager.
Click the name of the computer, and then on the Action menu, click Properties.
Click the Directory Security tab.
Under Anonymous Access and Authentication Control, click Edit.
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.
Make sure the Allow Anonymous Access check box is selected, and then click Edit.
In the Anonymous User Account dialog box, type the anonymous logon user name and password for the account you want to use. Typically, you designate the user name as IUSR_computername.
Select the Enable Automatic Password Synchronization check box to match passwords with the anonymous account created in User Manager for Domains.
For more information click the Help button.
To add the anonymous user account to the computer running SQL Server
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.
Log on to the SQL Server computer as an administrator.
On the Start menu, point to Programs, point to Administrative Tools (Common), and then click User Manager for Domains.
If necessary, select the domain that contains the SQL Server computer. On the User menu, click Select Domain, and then type or click the appropriate domain.
On the User menu, click New User.
For User name, type the name of the account (IUSR_computername by default). Type the same password used for that account on the IIS computer.
Clear the User Must change Password at Next Logon check box, select User Cannot Change Password, select Password Never Expires, and then clear Account Disabled.
Click Add, close the Add User dialog box, and then close User Manager for Domains.
Installing SQL Server 7.0 and Upgrading Existing Databases
As you install Microsoft® SQL Server™, observe the following requirements:
When you install SQL Server, you must set it up to use the Named Pipes and TCP/IP sockets protocols. SQL Server 7.0 now uses these protocols by default (SQL Server 6.5 did not).
Sufficient licenses must be available for all connecting computers and virtual servers that will be running.
Do not install SQL Server 7.0 on a computer that runs the Microsoft® Site Server LDAP Service, part of the Personalization & Membership (P&M) component of Site Server. The Site Server LDAP Service cannot function correctly with ADSI 2.1, which installs with SQL Server 7.0.
Although both Commerce Server and Ad Server will work on case-sensitive installations of SQL Server, P&M requires a case-insensitive code page.
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:
Stop all Site Server services before installing SQL Server 7.0 and restart the services after installation is complete.
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:
IIS Admin (command line name: iisadmin)
Site Server Content Deployment (command line name: crs)
Site Server Gatherer (command line name: gthrsvc)
Site Server Search (command line name: sssearch)
If you are installing both the Windows NT Server 4.0 Option Pack and SQL Server 7.0, install the Option Pack before installing SQL Server 7.0. This is a change from the SQL Server 6.5 installation process.
To install SQL Server 7.0
Insert the SQL Server 7.0 CD-ROM into the appropriate drive. An introductory screen appears automatically.
Click Install SQL Server 7.0 Components.
Click Database Server – Standard Edition.
Click Local Install, and then click Next.
Follow the online instructions, accepting defaults, except as indicated:
You can set the option to run the SQL Server Upgrade Wizard immediately following setup. However, before you run the wizard, be sure you have read "Upgrading SQL Server 6.5 Databases" later in this document and "Upgrading from an Earlier Version of SQL Server" in the SQL Server Books Online.
Select a Character Set and Sort Order that match those of your installation of SQL Server 6.5. If you are installing SQL Server 7.0 on a SQL Server 6.5 computer, these options should automatically be set appropriately.
The Named Pipes, TCP/IP Sockets, and Multi-Protocol network libraries should be selected automatically. Make sure these options are selected, and set the TCP/IP port number to that of your SQL Server 6.5 installation. The default is 1433.
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.
To register remote SQL Server installations with SQL Server Enterprise Manager
The local SQL Server installation is registered automatically when you start SQL Server Enterprise Manager. Installations on other computers must be registered manually.
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, and click SQL Server Group.
On the Action menu, click New SQL Server Registration.
In the Server box, type the name of the SQL Server computer, and then click the type of authentication it uses. If necessary, type a user name and password in the Login Name and Password boxes.
Click OK.
To check that SQL Server can use TCP/IP connections
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Server Network Utility.
Check the Active server network library configurations list for the following information:
TCP/IP 1433
If the list does not have a TCP/IP entry, click **Add**.
- In the **Add Network Library Configuration** dialog box, click **TCP/IP**.
- In the **Port number** box, type **1433** or another port number, and if necessary, type an address in the **Proxy address** box.
- Click **OK**.
Click OK.
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.
Setting the SA Password
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.
To set the sa password
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, double-click SQL Server Group, and then double-click the name of the SQL Server computer.
Double-click Security, click Logins, and then in the contents pane, click sa.
On the Action menu, click Properties.
In the Password box, type the new password, and then click OK.
Upgrading SQL Server 6.5 Databases
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.
Upgrade Configurations
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:
The Named Pipes protocol must be available.
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:
The name and file path
The initial size of the file
The autogrow increment
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:
Server configuration, including login and remote login registrations, and server configuration options that are relevant to SQL Server 7.0.
Replication settings
SQLExecutive settings
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).
Preparation Checklist
Before you upgrade any databases, review the following checklist:
Install SQL Server 7.0 as described previously in this document.
Back up the SQL Server 6.5 database files (all .dat files, including master).
Run the appropriate database consistency checks.
In your SQL Server 6.5 installation, set tempdb to at least 10 MB (25 MB recommended).
Ensure that all database users have logins in the master database (where system logins reside).
Disable any startup stored procedures.
Ensure that you upgrade all databases with cross-database dependencies at the same time.
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.
Stop replication and make sure that the transaction log is empty.
Stop all services that access the databases to be upgraded.
If you are running a computer-to-computer upgrade, assign a domain username and password to the MSSQLServer service in your 6.5 and 7.0 installations instead of using the local system account or a local user account. The domain user account should belong to the Administrators group of both the computers involved in the upgrade.
Backward Compatibility
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.
Switching Between Versions During the Upgrade
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.
To upgrade a database
On the Start menu, point to Programs, point to Microsoft SQL Server Switch, and then click Upgrade.
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.
On the title screen, click Next.
Follow the online instructions, accepting defaults, except as indicated:
To verify the upgraded data, click Validate successful object data transfer.
You should upgrade all of the databases on a single computer at once, including master, model, msdb, and tempdb.
Under System objects to transfer, make sure that Server configuration and SQL Executive settings are selected, and check that the ANSI Nulls and Quoted Identifiers settings match those used in the SQL Server 6.5 databases (in SQL Server 6.5, these options are controlled using the SET statement; ANSI Nulls is also available as a database property).
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.
To set the backward compatibility level to 70
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.
If prompted, type the name of a SQL Server computer that is running one of the upgraded Membership Directory databases, and then click OK.
In the toolbar of the Query window, click the DB list, and then click the name of the database you want to update.
In the Query window, type sp_dbcmptlevel 70 and then click the green arrow button in the toolbar.
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.
To change the active version of SQL Server
On the Start menu of the SQL Server computer, point to Programs, point to Microsoft SQL Server-Switch, and then click the inactive version.
Check that the necessary services are running. On the Start menu, point to Settings, click Control Panel, and then double-click Services.
For SQL Server 6.5, check that MSSQLServer and SQLExecutive have started.
For SQL Server 7.0, check that MSSQLServer and SQLServerAgent have started.
To start a service, click the name of the service and then click Start.
To close the Services control panel, click OK.
Note You can only use this procedure on a computer where both SQL Server 6.5 and SQL Server 7.0 are installed.
Updating Site Server to Work with SQL Server 7.0
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.
Updating the Site Server Components
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 |
To upgrade Site Server
Use this procedure on each Site Server computer, especially the LDAP Servers.
Insert the Microsoft Site Server Service Pack 2 compact disc into the appropriate drive.
On the CD-ROM, find the appropriate directory (\x86 or \Alpha).
Run SS3SP2.exe and follow the instructions on the screen.
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.
Updating the Membership Directory Databases
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:
Mcis2upd.sql. Updates the newly upgraded database to work with Service Pack 2 LDAP servers.
Instwab7.sql. Addresses performance issues that arise when running Windows Address Book (WAB) in a SQL Server 7.0 environment.
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.
To update Membership Directory databases using mcis2upd.sql
On the Start menu of the SQL Server computer, point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.
If prompted, 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.
On the File menu, click Open, and navigate to the directory containing the scripts.
Click mcisd2upd.sql, and then click Open.
In the toolbar of the Query window, click the DB list, and then click the name of the Membership Directory database.
To run the script, click the green arrow button.
If the script runs successfully, it will return the following message (the message may be displayed more than once):
The command(s) completed successfully.
Repeat steps 5 and 6 for each partition database.
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.
To update Membership Directory databases using instwab7.sql
On the Start menu of the SQL Server computer, point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.
If prompted, type the name of a SQL Server computer that is running one of the Membership Directory databases, and then click OK.
On the File menu, click Open, and navigate to the directory containing the scripts.
Click instwab7.sql, and then click Open.
In the toolbar of the Query window, click the DB list, and then click the name of the Membership Directory database.
To run the script, click the green arrow button.
If the script runs successfully, it will return the following message (it may be displayed more than once):
The command(s) completed successfully.
Repeat steps 5 and 6 for each partition database.
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.
To revert Membership Directory databases using remwab7.sql
On the Start menu of the SQL Server computer, point to Programs, point to Microsoft SQL Server 6.5, and then click Query Analyzer.
If prompted, type the name of a SQL Server computer that is running one of the Membership Directory databases, and then click OK.
On the File menu, click Open, and navigate to the directory containing the scripts.
Click remwab7.sql, and then click Open.
In the toolbar of the Query window, click the DB list, and then click the name of the Membership Directory database.
To run the script, click the green arrow button.
If the script runs successfully, it will return the following message (it may be displayed more than once):
The command(s) completed successfully.
Repeat steps 5 and 6 for each partition database.
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.
Installing Client ODBC Driver and Network Libraries for SQL Server
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:
"Network Connectivity Options" in the "Managing Servers" section
"Communication Components" in the "Client/Server Architecture" section
To install client ODBC driver and network libraries
Insert the SQL Server 7.0 compact disc into the appropriate drive on the client computer. An introductory screen appears automatically.
Click Install SQL Server 7.0 Components.
Click Database Server – Standard Edition, click Local Install, and then click Next.
Click Custom, and then click Next.
Under Components, make sure that only the Client Connectivity checkbox is selected, and then click Next.
Follow the remaining on-screen instructions.
To change the default Net-Library
On the Start menu of the client computer, point to Programs, point to Microsoft SQL Server 7.0, and then click Client Network Utility.
Click the Default network library list, and click the name of the net library you want to use (usually TCP/IP).
Click OK.
Setting Up New Databases
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.
To create an empty SQL Server database
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, double-click SQL Server Group, double-click the name of the SQL Server computer, and then click Databases.
On the Action menu, click New Database.
In the Name box, type a name for the database. SQL Server automatically uses this name to fill out the Database file information (such as file name and path).
In the Database file section, click the Initial size box for the new file, and type a new value for the initial file size.
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
Check that the growth options are set the way you want them.
If you do not want the file to grow automatically, make sure that Automatically grow file is not selected.
If you want the file to grow in megabyte increments, make sure that Automatically grow file is selected, click In megabytes, and type a number of megabytes to use as the growth increment. If you want to limit the size of the file, click Restrict filegrowth (MB), and type the maximum size of the file (in megabytes).
If you want the file to grow by a certain percentage, make sure that Automatically grow file is selected, click By percentage, and type a percentage to use as the growth increment. If you use this option, you must set a maximum file size. To do this, click Restrict filegrowth (MB), and type the maximum size of the file (in megabytes).
Click Transaction Log, and then in the Transaction log file section of the dialog box click the Initial size box for the new log file, and type a new value for the initial file size.
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
Check that the growth options are set the way you want them, and then click OK.
After the database has been created, double-click Databases, click your new database, and on the Action menu, click Properties.
Click Options, and then in the Settings section, click Truncate log on checkpoint. This step prevents the log file from filling up.
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.
Click OK.
Configuring a New Database for Use with Site Server
The new database is now ready for further configuration.
For new Membership Directory databases, you will need to run the instwab7.sql script (see "To update Membership Directory databases with instwab7.sql" earlier in this document), and then refer to "Creating Membership Servers" in the "Personalization & Membership" section of the Site Server documentation.
For new Analysis databases, see "To set up the Analysis database" later in this document.
For new Commerce databases, see the "Commerce HowTo" section of the Site Server, Commerce Edition documentation.
To configure a Commerce database manually, see "To manually load the schema into a Commerce database" later in this document.
For new Ad Server databases, see the "Ad Server" section of the Site Server, Commerce Edition documentation.
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.
To copy an existing schema into a new Ad Server database
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.
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers. Double-click SQL Server Group, double-click the name of the SQL Server computer, and then click Databases.
On the Action menu, point to All Tasks, and then click Import Data.
Specify the source database by performing the following steps:
Click Next to get to the Choose a Data Source page.
In the Server box, type the name of the SQL Server computer running the existing Ad Server database, and then click the appropriate authentication method for that database. If needed, type a username and password in the Login ID and Password boxes.
Click the Database list, click the name of the source database, and then click Next.
Specify the destination database by performing the following steps:
In the Server box, type the name of the SQL Server computer running the new Ad Server database, and then click the appropriate authentication method for that database. If needed, type a username and password in the Login ID and Password boxes.
Click the Database list, click the destination database, and then click Next.
Make sure that the Copy table(s) from the source database option is selected, and click Next.
Click Select All to select all of the source tables, and then click Next.
Click Next again, and then click Finish.
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.
To manually load the schema into a Commerce database
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.
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.
If prompted, type the name of the SQL Server computer that is running the Commerce database, and then click OK.
On the File menu, click Open, and navigate to the \Inetpub\wwwroot\sitename\Config\SQL\SQLSvr\ folder.
Click wiz_schema.sql, and then click Open.
In the toolbar of the Query window, click the DB list, and then click the Commerce database.
To run the script, click the green arrow button.
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 set up the Analysis database
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.
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.
On the Start menu, point to Programs, point to Microsoft Site Server, Analysis, and then click Database Setup - SQL Server.
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.
Click Next.
Select Update an existing database, and then click Next.
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.
In the Server box, type the name of the SQL Server computer, and then in the Database box, type the name of your Analysis database (for example, SSAnalysis).
In the Login ID box type a valid SQL Server logon ID (that has access rights to the database), and in the Password box, type the password for that login ID.
Click Connect.
Click Next at the remaining prompts, and then click Finish.
The Database Setup Wizard populates the Analysis tables with default data.
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.
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.
Setting Up ODBC System Data Sources
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.
To create a system data source name
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.
On the Start menu, point to Settings, click Control Panel, and then double-click ODBC Data Sources.
In the ODBC Data Source Administrator dialog box, click System DSN, and then click Add.
In the list of drivers, click SQL Server, and then click Finish.
In the Name box, type a name for the DSN, and then in the Description box, type a description (such as the purpose of this particular DSN).
In the Server box, type the name of the SQL Server computer running the database that this DSN will serve, and then click Next.
Click the With SQL Server login option, and then if the DSN must use a TCP/IP connection, click Client Configuration.
In the Network libraries column, click TCP/IP.
In the Computer name box, type the name of the SQL Server computer, and then in the Port number box, type the port that SQL Server is using on that computer.
Click OK.
Click the Connect to SQL Server option, and in the Login ID and Password boxes, type the login ID and password of a SQL Server login that has access to the appropriate databases. Click Next.
Click Change default database to, click the list, and click the name of the database that you want this DSN to access.
Click Next, click Finish, and then click Test Data Source to make sure that the new DSN works properly.
Click OK in the SQL Server ODBC Data Source Test dialog box, click OK in the ODBC Microsoft SQL Server Setup dialog box, and then click OK in the ODBC Data Source Administrator dialog box.
Setting Up Database Access
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:
Logins in the "Client/Server Architecture" section
Setting up Security Accounts in the "Managing Security" section
Logins, Users, Roles, and Groups in the "Database Architecture" section
Security Architecture in the "Managing Security" section
To configure SQL Server to use Mixed Mode authentication
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, double-click SQL Server Group, and then click the name of the SQL Server computer.
On the Action menu, click Properties, and then click Security.
Under Authentication, click SQL Server and Windows NT.
Click OK.
Creating Login IDs and User IDs
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.
To set up sysadmin-level database access for a user or service
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Security, and then click Logins.
On the Action menu, click New Login.
In the Name box, type the login name that the user or service will use.
If the login ID is a Microsoft® Windows NT® account, click Windows NT authentication, and in the Domain box, type the domain where the account resides.
If the login ID is a SQL Server login, click SQL Server authentication, and in the Password box, type a password for the login.
Click Server Roles, and click System Administrator.
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.
Click OK.
To set up database access for a user or service
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Security, and then click Logins.
On the Action menu, click New Login.
In the Name box, type the login name that the user or service will use.
If the login ID is a Windows NT account, click Windows NT authentication, and in the Domain box, type the domain where the account resides.
If you want to specifically deny this user or service access to SQL Server, click Deny access. The default setting is Grant Access.
If the login ID is a SQL Server login, click SQL Server authentication, and in the Password box, type a password for the login.
Click the Database list and click the name of the primary database that the user or service will use.
To assign the user or service to one or more fixed server roles, click Server Roles, and click each appropriate server role.
Click Database Access, click the database that the user or service will need to use, and then click the role that the user or service needs to have in that database (repeat for additional databases).
Click OK.
Note After you have created user IDs for users, you must assign object and statement permissions for the users.
Assigning Permissions
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:
Analysis database owner. It is recommended that this user be a member of the db_owner role in the Analysis database. If this user is not a member of the System Administrators server role or of the db_owner database role and you want that user to be able to create a database, you must assign CREATE DATABASE statement permissions (by assigning the user to the Database Creators server role). If you do not, the system administrator is the only user who can create a database.
Analysis database users. You must grant these users the following permissions on the Analysis database:
Select
Delete
Execute
Insert
Update
LDAP Service (Membership Directory root and partition databases). The LDAP Service accounts should be members of the db_owner role. You do not have to explicitly set permissions for these accounts.
Commerce pages. Assign the Commerce pages permissions in the Commerce database as needed.
Ad Server pages/Ad Server database/as appropriate. Assign the Ad Server pages permissions in the Ad Server database as needed.
For more information about permissions, see the following topics in the "Managing Security" section of the SQL Server Books Online:
Setting Up Security Accounts
Managing Permissions
To assign CREATE DATABASE statement permissions to the Analysis database owner
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, double-click SQL Server Group, and then double-click the name of the SQL Server computer. Double-click Security, click Logins, and then in the content pane, click the login ID you need to modify.
On the Action menu, click Properties, and then click Server Roles.
Click either System Administrators or Database Creators. The System Administrators role has full permissions for the entire SQL Server installation; the Database Creators role is more restrictive.
Click OK.
To assign object permissions for Analysis database users
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Databases, and then double-click the name of your database.
Click Users, and then in the content pane, click the name of the user whose permissions you want to change.
On the Action menu, click Properties, and then click Permissions.
To grant a permission to an object, click the check box for that permission.
When you have finished, click OK.
To assign statement permissions for Analysis database users
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Databases, and then click the name of your database.
On the Action menu, click Properties, and then click Permissions.
Click the user ID of the user whose permissions you want to change, and click the check box for that permission.
When you have finished, click OK.
Optimizing and Maintaining Databases
This section contains guidelines and techniques for improving the performance of your system, including:
Backing up data
Copying large data sets
Tuning indexes
Configuration settings for improving performance
Backing Up Critical Data
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:
Membership Directory
Analysis
Commerce Server
Ad Server
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.
To set up a backup destination
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Management, and then click Backup.
On the Action menu, click New Backup Device.
In the Name box, type the name of the backup device, and in the File Name box, type the directory path and file name for the backup file.
Click OK.
To back up a SQL Server database
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Databases, and then click the name of your database.
In the content pane, click backup database.
Make sure that the Database - Complete option is selected, and then in the Backup To box, click a backup destination.
Click OK.
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.
Copying Large Data Sets into the SQL Server Database
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
Tuning SQL Server Indexes
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+% |
Optimizing Performance
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:
Memory allocation, including query memory
Database file growth
Locks
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 Allocation
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.
Server Memory Allocation
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 |
Minimum Memory per Query
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:
Server Memory Options
Optimizing Server Performance Using Memory Configuration Options
To configure the SQL Server memory allocation
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, double-click SQL Server Group, and then click the name of the SQL Server computer.
On the Action menu, click Properties, and then click Memory.
To allow SQL Server to manage memory dynamically, click Dynamically configure SQL Server memory, and then move the Minimum and Maximum sliders to the values you want.
To use a fixed memory allocation, click Use a fixed memory size and then move the slider to the value you want.
Click OK.
Note 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.
To allocate query memory
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers, double-click SQL Server Group, and then click the name of the SQL Server computer.
On the Action menu, click Properties, and then click Memory.
In the Minimum query memory box, click the dial arrows to increase or decrease the value.
Click OK.
Controlling Database File Growth
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.
To change the growth increment of a database
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Enterprise Manager.
Double-click Microsoft SQL Servers and double-click SQL Server Group. Double-click the name of the SQL Server computer, double-click Databases, and then click the name of your database.
On the Action menu, click Properties.
In the Database files list, click the file you want to configure.
In the File properties section, set the file growth options that you want.
If you do not want the file to grow automatically, make sure that Automatically grow file is not selected.
If you want the file to grow in megabyte increments, make sure that Automatically grow file is selected, click In megabytes, and type a number of megabytes to use as the growth increment. If you want to limit the size of the file, click Restrict filegrowth (MB), and type the maximum size of the file (in megabytes).
If you want the file to grow by a certain percentage, make sure that Automatically grow file is selected, click By percentage, and type a percentage to use as the growth increment. If you use this option, you must set a maximum file size. To accomplish this, click Restrict filegrowth (MB), and type the maximum size of the file (in megabytes).
Click OK.
Allocating Locks
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.
To allocate locks
On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.
If prompted, type the name of a SQL Server computer that is running one of the Membership Directory databases, and then click OK.
In the Query window, type the following script:
sp_configure 'show advanced options', 1
GO RECONFIGURE GO sp_configure 'locks', [number] GO RECONFIGURE
where \[*number*\] is the new number of locks.
To run the script, click the green arrow button in the toolbar.
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.
When the script is complete, stop and restart SQL Server.
Important Only experienced SQL Server administrators should attempt to use this procedure. In addition, this procedure requires that SQL Server be stopped and restarted.
Optimizing Performance for Commerce Server
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.
General Optimizing Techniques
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:
Logical database and table design
Index design
Query design
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.
Configuring ADO and Connection Pooling
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:
What's New
DTS Programming
SQL Server ODBC Driver Programmer's Reference
Removing I/O Bottlenecks
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.
Binary Data Considerations
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.
SQL Server Monitoring Tools
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
Event information
Database Space information (including queries, indexes, and progress reports)
Configuration Information
Configuration information includes database options, sizes, and permissions. The types of configuration information you can access include:
A list of databases on a server.
A graphical representation of all databases on a server.
Information about an individual database.
Event Information
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
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.
Troubleshooting
Services
Verify that the following services are running:
MSSQLServer
SQLServerAgent (formerly SQLExecutive)
Computer Names, Database Table Names, and Table Column Names
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:
Spaces are not allowed.
The first character must be either an alpha character or an underscore.
Characters following the first character can include, alpha, numeric, symbols, or underscore characters.
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.
Ad Server Problems with SQL Server
This section contains a list of messages that commonly occur in the course of Ad Server operations, and recommended courses of action for each.
Error: ODBC: Login Failed, User Not Defined as Valid User of a Trusted SQL Server Connection
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: |
Error: ODBC: Invalid Object Name: as_constants
Possible causes |
Possible solutions |
|---|---|
Default database not specified in ODBC DSN. |
To specify the default database: |
Permission to Database Denied
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: |
Database computer is not set up with proper IUSR_computername account in Windows NT. |
Check the Windows NT accounts on the SQL Server computer. |
Error: ConnectionWrite: GetOverlappedResult()
Possible causes |
Possible solutions |
|---|---|
Problem with the Named Pipes network library |
To change the client configuration to use TCP/IP transport: |
Error: Data Source Name Not Found and No Default Driver Specified
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: |
Error: Database Open Failed, Specified SQL Server not found
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. |
Tempdb Too Small
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. |
P&M Problems with SQL Server
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).
Upgrade Issues
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 |
SQL Server Checklist
When you are creating a Membership Directory using a new SQL Server database, remember:
Each computer running Microsoft® Internet Information Server (IIS) (and Site Server) has a Microsoft® Windows NT® account that IIS uses for anonymous access. Each such account must also exist on the SQL Server computer. For more information about this account, see "Configuring the Anonymous Access Account" in the IIS documentation.
SQL Server must be set up to use the Named Pipes and TCP/IP Sockets network protocols. You can set these options using SQL Server Setup.
SQL Server must be set up to use a case-insensitive code page.
The Site Server LDAP Service must be able to log on to the database. Set up a SQL Server login that the LDAP Service can use, and grant it full permissions for the database. You must set up this login before you create the Membership Directory.
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.
Appendix: Upgrade Strategies
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.
Small System
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.
.gif)
The logistics for such an upgrade involve the following:
One additional computer.
One install sequence for SQL Server 7.0.
One upgrade sequence for the databases from the Membership Directory computer to the SQL Server 7.0 computer.
One LDAP service reconfiguration (assuming that there are no partition databases). This reconfiguration provides the LDAP service with the location of the new database.
No leftover computers (the old Membership Directory computer now functions as an LDAP Server).
Example: to upgrade a small system to SQL Server 7.0 using the computer-to-computer approach
Set up a new SQL Server 7.0 computer.
Stop the LDAP Service.
Run the computer-to-computer upgrade. Check that SQL Server services are running
Upgrade the Application server and Membership Directory computers to Service Pack 2.
Run the SQL scripts supplied with Site Server Service Pack 2.
Configure the LDAP service to use the upgraded database. By default, the database name and login information stay the same; only the computer name has changed.
Start the LDAP Service.
Large System
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:
Five new computers
Five installs (one SQL Server 7.0 install sequence for each of the 5 new computers)
Five upgrade sequences (from each SQL Server 6.5 computer to its corresponding SQL Server 7.0 computer)
10 LDAP service reconfigurations (five connections per LDAP Server, for a total of 10 connections).
Five leftover computers (the original SQL Server 6.5 computers).
If you were to run side-by-side upgrades, the logistics would involve the following:
No new computers
Five installs (one SQL Server 7.0 install sequence for each of the SQL Server 6.5 computers)
Five upgrade sequences (each on a single computer)
Some amount of additional disk space/RAID arrays (to support the additional application and databases)
No LDAP service reconfigurations (the upgraded databases have not changed location)
No leftover computers
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.
.gif)
Install SQL Server 7.0 on each SQL Server 6.5 computer. Set the sa password of each SQL Server 7.0 installation to match the sa password of the SQL Server 6.5 installation on that computer.
Copy the SQL scripts from the Site Server Service Pack 2 CD-ROM (the scripts reside in \x86\). Place the scripts in a temp directory that is accessible to all of the SQL Server installations.
Stop the LDAP services.
On each SQL Server computer, run a single-computer upgrade. Check that the SQL Server services are running.
Upgrade the LDAP services to Site Server Service Pack 2.
Run the SQL Server scripts on all of the appropriate databases.
Start the LDAP services.