English

How To: Perform a manual post installation of ArcSDE for SQL Server 2005/2008 - single database model

Summary

Instructions provided describe how to install ArcSDE with SQL Server 2005/2008 and create a geodatabase schema owned by the SDE user. These steps are not meant to take the place of the Post Installation Wizard, but are meant to offer an optional approach for installing ArcSDE. This article provides a better understanding of what tasks are carried out by the Post Installation Wizard and helps to troubleshoot issues that arise should the Post Installation Wizard fail.

Note:
ArcSDE 9.1 Service Pack 1 is the minimum service pack required to use ArcSDE 9.1 against SQL Server 2005; a link to the 9.1 service pack download page is available in the Related Information section below. Subsequent versions of ArcSDE natively support SQL Server 2005.

A summary of the detailed steps to follow are below:

· Create a new database and SDE login, and give the SDE login access to the new database.
· Ensure that the SDE user has a default schema named SDE.
· Grant the SDE user necessary permissions.
· Populate the repository / authorize software.
· Create and start an ArcSDE service.

Procedure

  1. Create a new database
    Start Management Studio for SQL Server and open the instance. Right-click Databases, click New database, then give the database a suitable name and owner. Other properties can be set here as well, such as data, log file locations, etc.
    Warning:
    In an SDE schema geodatabase it is not a recommended practice to have the SDE user create/own the database. The SA server login is a good choice for the owner of user databases. Also, it is not recommended to name the database SDE, as this creates a multiple spatial database model geodatabase.
  2. Create SDE login and assign default schema
    In SQL Server 2005/2008, the concept of user schemas is very important for ArcSDE. Where data is stored depends on what default schema the user that loaded the data is set to. The following shows how to create a login and assign a default schema to a user as it is added to the database.

    Under the instance security menu, right-click Logins and select 'New login'. Type 'SDE' for the login name and select SQL Server authentication.
    Note:
    IMPORTANT PASSWORD INFORMATION:
    There are three check boxes under the general page for creating a new login.
    Use discretion if these need to be unchecked when proceeding with the installation as leaving options checked such as 'User must change password at next login' may result in issues later in the installation process.
    
    · Enforce password policy.
    · Enforce password expiration.
    · User must change password at next login.
    

    Click the User Mapping page and check the database created in step 1. This creates an SDE user in the corresponding database.

    Now type 'SDE' for the Default Schema for the SDE user in the database. This creates a schema in the database and sets this schema as the default for the SDE user.
    Warning:
    Do not grant the SDE login any server roles. Do not grant the SDE user any extra database roles (default role of 'Public').
  3. Grant SDE user necessary permissions
    To populate and manage the repository tables in the database, the SDE user needs four permissions. To grant these, right-click the newly created database and select Properties. Select the Permissions page. Ensure that the SDE user is selected under users or roles. Proceed to check all of the following permissions below under Explicit permissions for the SDE user:

    • Create table
    • Create view
    • Create function
    • Create procedure

    Keep this Database Properties dialog box open to add the next permission.

    Optional step (required for ArcSDE 9.1 with SQL Server 2005):
    Grant View server state permission
    This new permission must be granted for the entire instance for each user. In the Database Properties dialog box on the Permissions page, click the hyperlink under the server name 'View server permissions'. This opens the Server/Instance Properties dialog box on the Permissions dialog box.
    [O-image] Server permissions
    Since the Permissions dialog box is already active, select the SDE login under Logins or roles and scroll down to View server state under Explicit permissions for SDE. Click the check box under the Grant column for this permission and click OK to close the Server Properties dialog box.
    Note:
    Connections to the service cannot be monitored by way of sdemon command line tools for logins that do not have this permission granted at the instance level of the database.
  4. Create the ArcSDE repository tables
    The ArcSDE repository tables, also known as the metadata tables, store and manage all the properties of the geodatabase in ArcSDE. To create these tables (plus ArcSDE stored procedures, views, and functions), type the following at a DOS command prompt and press enter: sdesetup.

    Enter the command without any parameters to return its syntax. Use the '-o install' option to create the repository. Be sure to include the -l option with the path of the .ECP authorization file.
    Note:
    Software authorization can be done with an .ECP file or an ECP registration number. The physical file is required for the manual method that uses the command line tools.

    Usage of the '-o install' option:

    • 9.2 and newer:
    Code:
    sdesetup -o install -d SQLSERVER -H <sde_directory> -u <DB_Admin_user> -p <DB_Admin_password>
    -D <database> -s datasource -l <path to .ecp authorization file>

    • Pre-9.2:
    Code:
    sdesetupmssql -o install -u <DB_Admin_user> -p <DB_Admin_password>
    -D <database> -s datasource -l <path to .ecp authorization file>

    For more information about this command, refer to the ArcSDE Administration Command Reference, which was installed with ArcSDE. Refer to the 'Administrative Commands' section and 'Alphabetical Listing of Commands' for the 'sdesetup' command.
  5. Create the ArcSDE Service
    ArcSDE connections to the database can be managed by an application server process known as the 'giomgr'. Create a service for those needing to connect using a 3-tier connection. To create the service manually, start by adding this line to the following files:
    Code:
    esri_sde         5151/tcp

    %SDEHOME%\etc\services.sde
    %WINDIR%\system32\drivers\etc\services

    5151 is the port reserved by Esri for ArcSDE. The default name for the service is 'esri_sde'. The service name can be changed, but port 5151 is the only one reserved for ArcSDE. Be aware that if other port numbers are selected, there is potential for conflicts with other applications or services.

    To create the service, run the following from a command prompt:

    sdeservice -o create
    The usage for this command follows:
    Code:
    sdeservice -o create -d SQLSERVER,<SQLSERVERINSTANCE> -p <SDE_DBA_Password> [-i <service>]

    Assign an ADMIN_DATABASE
    An admin database must be assigned to each service when using the single database model. This is the database that stores the ArcSDE repository and is needed for the service to know which database to store data in.

    Use the following syntax to register the ArcSDE service with an ADMIN_DATABASE:
    Code:
    sdeservice -o register -r ADMIN_DATABASE -v <Name of database> -d SQLSERVER 
    -p <SDE_DBA_Password> [-i <service>] 

    Run sdeservice -o list to verify that this took effect and is set up correctly.
  6. Start the service
    Select any one of the following methods to start the service:

    · net start <service name>
    · sdemon -o start -i <service name> -p <sde password>
    · Start service from Windows Services control panel

    Should the service fail to start, examine the log files in %SDEHOME%\etc folder for more information. SDE_<service_name>.log and giomgr_<service_name>.log are available sources of information.

Related Information