How to make Windows SharePoint Services use a pre-existing database as the content database (888041)


The information in this article applies to:

  • Microsoft Office SharePoint Portal Server 2003
  • Microsoft Windows SharePoint Services

IN THIS TASK

INTRODUCTION

This article describes how to make Microsoft Windows SharePoint Services use a pre-existing database as the content database. To do this, use SQL Query Analyzer to create the database and to set the permissions on the database. After you create the database, use Windows SharePoint Services Central Administration to add the database to the virtual server.

Use this method when you want to create a new database to use as a content database for Windows SharePoint Services. For example, you may want to do this in an environment where the following conditions are true:
  • You have one computer that is running Microsoft SQL Server 2000.
  • You use multiple instances of SQL Server.
  • You use different storage area networks (SANs) to store content.

back to the top

Step a: Use SQL Query Analyzer to create the database

Use SQL Query Analyzer to create a new content database for use with the virtual server in Windows SharePoint Services. You cannot use the Create Database Wizard in the SQL Server Enterprise Manager tool to create the new database. Instead, run the following two queries in SQL Query Analyzer. When you use the following queries, make sure that you do the following:
  • Replace each instance of WSS_Content in the queries with the name that you want to use for the database.
  • Replace each instance of AdminVsAppPoolIdentity in the query with the account that is the SharePoint Central Administration virtual server application pool identity. This account is also the database owner (dbo) of the database.
  • Replace each instance of ContentVsAppPoolIdentity in the query with the account that is the content virtual server application pool identity.
  • Replace each instance of Domain in the query with the domain name.
Use the following query to create the database:

-- Windows SharePoint Services Content Database Creation Script

-- TODO: Change the database name from WSS_Content to the name of the database that you want to use
CREATE DATABASE [WSS_Content] COLLATE Latin1_General_CI_AS_KS_WS 
exec sp_dboption [WSS_Content], autoclose, false
GO

Use the following query to configure permissions for the database that you created:

-- Windows SharePoint Services Content Database Creation Script
use WSS_Content

-- TODO: Change AdminVsAppPoolIdentity to the SharePoint Central Administration virtual server application pool account and change ContentVsAppPoolIdentity to the content virtual server application pool account.

DECLARE @AdminVsAppPoolIdentity nvarchar(128)
DECLARE @ContentVsAppPoolIdentity nvarchar(128)
SET @AdminVsAppPoolIdentity = N'Domain\AdminVsAppPoolIdentity'
SET @ContentVsAppPoolIdentity = N'Domain\ContentVsAppPoolIdentity'

EXEC sp_grantlogin @ContentVsAppPoolIdentity;
EXEC sp_grantlogin @AdminVsAppPoolIdentity;

-- The following line will fail with error 15007 if @AdminVsAppPoolIdentity is the Network Service account.  This is expected.
EXEC sp_changedbowner @AdminVsAppPoolIdentity;

-- The following line will fail with error 15063 if the account is not the Network Service account.  This is expected.
IF NOT EXISTS (SELECT * FROM sysusers WHERE name=@ContentVsAppPoolIdentity) EXEC sp_grantdbaccess @ContentVsAppPoolIdentity;

-- The following line will fail with error 15410 if the account is not the Network Service account.  This is expected.
EXEC sp_addrolemember 'db_owner', @ContentVsAppPoolIdentity;

To run a query in SQL Query Analyzer, follow these steps:
  1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. In the Connect to SQL Server dialog box, specify the instance of SQL Server that you want to connect to, specify the type of authentication that you want to use, and then click OK.
  3. Paste the query in the Editor pane of SQL Query Analyzer. Then, modify the query to use the name that you want for the database and to use the appropriate accounts.
  4. Click Execute Query.
back to the top

Step b: Add the content database to the virtual server

Use Windows SharePoint Services Central Administration to add the content database to the virtual server. To do this, follow these steps:
  1. Click Start, point to All Programs, point to SharePoint Portal Server, and then click SharePoint Central Administration.
  2. If you are running Microsoft Office SharePoint Portal Server 2003, click Windows SharePoint Services in the left pane.
  3. Under Virtual Server Configuration, click Configure virtual server settings.
  4. On the Virtual Server List page, click the name of the virtual server where you want to add the database.
  5. Under Virtual Server Management on the Virtual Server Settings page, click Manage content databases.
  6. Under Content Databases on the Manage Content Databases page, click Add a content database.
  7. In the Database Information area of the Add Content Database page, click Specify database server settings, and then specify the name of the database and the name of the database server.
  8. In the Database Capacity Settings area, type the number that you want in the Number of sites before a warning event is generated box, and then click OK.

    Note If you configured permissions for the database with the NT Authority\Network Service account, you have to add the Network Service account to the System Administrators role in SQL Server before you can perform this step. You cannot make the Network Service account the database owner (dbo) of the database. You have to add the Network Service account to the System Administrators role before you can add the database to the virtual server. After you add the database to the virtual server, remove the Network Service account from the System Administrators role in SQL Server.
back to the top

Troubleshooting

When you try to add a new database to the virtual server on the Manage Content Databases page of Windows SharePoint Services Central Administration, you receive one of the following error messages:
Database 'DatabaseName' already exists. (Error code: 1801).
Cannot resolve collation conflict for equal to operation (Error code: 446)
This issue occurs if the database that you created does not use the correct collation setting or the correct dbo. This issue occurs if you created the database by using the Create Database Wizard in the SQL Server Enterprise Manager tool. Instead of using the Create Database Wizard to create the database, use SQL Query Analyzer to run the query that is described in step a to create the database.

back to the top

MORE INFORMATION

For additional information about how to use SQL Query Analyzer, see SQL Query Analyzer Help.

For additional information about how to configure and administer Windows SharePoint Services, see the Microsoft Windows SharePoint Services 2.0 Administrator's Guide. To obtain the Microsoft Windows SharePoint Services 2.0 Administrator's Guide, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyID=a637eff6-8224-4b19-a6a4-3e33fa13d230&displaylang=en

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. back to the top
Modification Type:MajorLast Reviewed:7/5/2005
Keywords:kbhowto kberrmsg kbConfig KB888041 kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.