How To: Manual post-installation of ArcSDE for Microsoft SQL Server 2000 - single database model
Instructions provided describe how to create an ArcSDE geodatabase in Microsoft SQL Server using manual methods in place of the ArcSDE for Microsoft SQL Server Post Installation wizard. The ArcSDE software installation must be installed on the server and an authorization file for ArcSDE must be available. Normally, once the software installation is complete, the ArcSDE for Microsoft SQL Server Post Installation wizard starts automatically and guides the user through the process of creating a database and generating the ArcSDE repository tables. This process can also be performed manually if desired.
Follow the steps below.
- Create a new database
Start Enterprise Manager for SQL Server and open up the instance. Right-click on Databases > New database and give the database a suitable name. Click on the other tabs in the dialog box if it is necessary to alter the locations or file growth methods of the datafiles and transaction logs.
- Create the SDE login
If this is the first ArcSDE geodatabase to be created on the server, create an SDE login. To create this new login go to Security > Logins > New Login. Enter 'SDE' for the name, select an authentication mode and a default database, which can be the database created in Step 1. SQL server authentication is preferred for the SDE user. Select this option and set a password for the SDE user. Navigate to the 'Database Access' tab and check the box associated with the database created in Step 1. This adds the SDE user to the database.
Do not grant the SDE login any server roles. Do not grant the SDE user any database roles, except for default of 'Public'.
- Create a new login
- Assign default database
- Database access
- Grant database permissions to the SDE user
The SDE user requires certain database-level permissions in order to create and manage the SDE and geodatabase metadata tables. Right-click on the database Properties, navigate to the permissions tab, and grant the SDE user the following four permissions:
- Create table
- Create view
- Create stored procedure
- Create function
It is not recommended to load data into the SDE schema. Create another user to serve as the data owner and grant them Create Table and Create Procedure in the database. Verify the user is granted login access to the database.
View the granted database-level permissions.
- 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, run 'sdesetupmssql' at a command prompt.
Enter the command without any parameters to return its syntax. Use the '-o install' option to create the repository.
Usage of the '-o install' option:
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 Developer Help in the EDN Documentation Library at:
Refer to the 'Administrative Commands' section for the 'sdesetup' command.
- Create the ArcSDE Service
ArcSDE connections to the database are typically brokered by an application server known as the 'giomgr', which is implemented as a Service on windows platforms. To create the service manually, add this line,
to the following files:
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
Here is the usage for this command:
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 which stores the ArcSDE repository.
sdeservice -o register -r ADMIN_DATABASE -v <Name of database> -d SQLSERVER
-p <SDE_DBA_Password> [-i <service>]
Run sdeservice -o list to verify this took effect and is setup correctly.
- 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 panel
Should the service fail to start, examine the logfiles in %SDEHOME%\etc folder for more information. SDE_<service_name>.log and giomgr_<service_name>.log are available sources of information.