How to make Windows SharePoint Services use a preexisting database as the content database

Article translations Article translations
Article ID: 888041 - View products that this article applies to.
Expand all | Collapse all

On This Page

INTRODUCTION

This article describes how to make Microsoft Windows SharePoint Services use a preexisting 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. (Applicable only in Windows SharePoint Services 2.0, SharePoint Portal Server 2003, Windows SharePoint Services 3.0 and Microsoft Office SharePoint Services 2007)
  • You use multiple instances of SQL Server.
  • You use different storage area networks (SANs) to store content.

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 in SQL 2000, 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.
To run a query in SQL Query Analyzer in SQL 2005 or in SQL 2008, follow these steps:
  1. 1. Click Start, point to All Programs, point to Microsoft SQL Server 2005/2008, and then click Management Studio.
  2. 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. Click New Query, and then 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.

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.

If you are running Windows SharePoint Services 2.0

  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 follow 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.

If you are running Microsoft SharePoint Foundation 2010

  1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint 4.0 Central Administration.
  2. Click Application Management, and then click Manage Content databases under Databases.
  3. On the Manage Content Databases page, click Add a content database.
  4. In the Web Application area, click the name of the Web application to which you want to add the content database.
  5. In the Database Name and Authentication area, specify the name of the database, the name of the database server, and the authentication method.
  6. 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 in the Maximum number of sites that can be created in this database 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 follow 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.

If you are running Windows SharePoint Services 3.0

  1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
  2. Click Application Management, and then click Content databases under SharePoint Web Application Management.
  3. On the Manage Content Databases page, click Add a content database.
  4. In the Web Application area, click the name of the Web application to which you want to add the content database.
  5. In the Database Name and Authentication area, specify the name of the database, the name of the database server, and the authentication method.
  6. 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 in the Maximum number of sites that can be created in this database 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 follow 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.

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.

You may also see the following error in Windows SharePoint Services 3.0:
Event Details
Source:  Windows SharePoint Services 3
ID:  5586
Message: Unknown SQL Exception 208 occurred. Additional error information from SQL Server is included below.
Invalid object name 'dbo.SystemVersion'.
This is a known issue and the error can be ignored.


In Microsoft SharePoint Foundation 2010, you may see following error:
Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: mm/dd/yyyy hh:mm:ss
Event ID: 4972
Task Category: Database
Level: Critical
Keywords:
User: domain\user
Computer: machine.domain_name.com
Description:
The SQL Server collation on SQL Server instance 'Domain\DB' is not supported. Microsoft SharePoint Foundation 4 requires a Latin1 General dictionary order, case insensitive, accent sensitive, kana sensitive, and width sensitive collation (Latin1_General_CI_AS_KS_WS).

MORE INFORMATION

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

For more information about how to configure and administer Windows SharePoint Services, see the Microsoft Windows SharePoint Services Administrator's Guide. To obtain the Microsoft Windows SharePoint Services Administrator's Guide, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=a637eff6-8224-4b19-a6a4-3e33fa13d230&displaylang=en
For more information about how to deploy Windows SharePoint Services 3.0 in an environment in which database administrators (DBAs) create and manage databases, visit the following Microsoft Web site:
http://technet2.microsoft.com/windowsserver/WSS/en/library/c7647e52-2178-4d3d-9376-84b2c9a35a1e1033.mspx?mfr=true
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. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Properties

Article ID: 888041 - Last Review: May 13, 2010 - Revision: 7.0
APPLIES TO
  • Microsoft Office SharePoint Portal Server 2003
  • Microsoft Windows SharePoint Services 2.0
  • Microsoft Office SharePoint Server 2007
  • Microsoft Windows SharePoint Services 3.0
  • Microsoft SharePoint Foundation 2010
  • Microsoft SharePoint Server 2010
Keywords: 
kbhowto kberrmsg kbconfig KB888041

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com