Create a geodatabase in SQL Server—ArcGIS AllSource

Prerequisites

Before you create a geodatabase in SQL Server, do the following:

Confirm that the ArcGIS, SQL Server, and operating system combinations you want to use are compatible.
Download the SQL Server ODBC client required to connect to the version of SQL Server you'll use to store the geodatabase.
Obtain an ArcGIS Server keycodes file and place it in a location you can access from the ArcGIS client you'll use to create the geodatabase.
Determine who will create the geodatabase, as that affects who creates the database and which tool you run to create the geodatabase. Follow the instructions that apply to your situation.
- If you are the SQL Server database administrator and geodatabase administrator, follow the instructions in the next section.
- If the database administrator and geodatabase administrator are two different people, follow the instructions for the SQL Server administrator to create the database and the geodatabase administrator to create the geodatabase.

You are the SQL Server and geodatabase administrator

If you perform the role of both the database administrator and geodatabase administrator and, therefore, know the password for both logins, you can use the Create Enterprise Geodatabase geoprocessing tool or ArcPy function to create a geodatabase in a SQL Server database.

The Create Enterprise Geodatabase tool allows you to set up a geodatabase, and it creates the database objects and grants required privileges for you. To achieve this, the tool uses the following default settings and creates the following database objects:

It creates the database files (MDF and LDF) in the default SQL Server location on the server.
It sets the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON.
It uses the default database file sizes you have defined for the SQL Server model database or 500 MB for the MDF file and 125 MB for the LDF file, whichever is greater.
If you choose to create a geodatabase in a schema named sde, the tool creates an sde database-authenticated login in the SQL Server instance, creates an sde user in the database and maps it to the sde login, creates an sde schema in the database, and grants the sde user privileges to create a geodatabase and drop connections to the SQL Server instance.
It creates the geodatabase objects inside the SQL Server instance.

Before you run the tool or function to create a geodatabase, you must install and configure a SQL Server instance.

Install and configure SQL Server

Before you create a geodatabase, SQL Server must be installed and the instance configured. How you configure the instance depends on the needs of your organization.

Note:

The SQL Server instance must use a collation that is not case sensitive, and the database cannot use a Turkish collation.

Once you (or your IT department) install SQL Server, ensure that the SQL Server instance accepts remote connections; by default, remote connections are not enabled.

Consult the Microsoft documentation for SQL Server installation and configuration information.

After SQL Server is installed and configured, install ArcGIS and SQL Server clients.

Install clients

You can install ArcGIS AllSource (Desktop Standard or Desktop Advanced) or ArcGIS Server (enterprise edition) and run a Python script to create a geodatabase. To run the Create Enterprise Geodatabase geoprocessing tool instead of a script, install ArcGIS AllSource.

Note:

You need an ArcGIS Server (enterprise edition) keycodes file to authorize the geodatabase in the next section. Even if you do not run a Python script from an ArcGIS Server machine to create the geodatabase, install and authorize ArcGIS Server to get the keycodes file. You may have to copy the keycodes file from the ArcGIS Server machine to a location the Create Enterprise Geodatabase geoprocessing tool can access.

In most cases, your ArcGIS clients are installed on a different computer than SQL Server. Therefore, you must install a supported SQL Server client on the ArcGIS client computer to connect to the database. See ArcGIS system requirements for SQL Server to determine which version of the SQL Server client to install to connect to the version of SQL Server you are using.

To connect to SQL Server on a Microsoft Windows machine, you can download the SQL Server client from My Esri or from the Microsoft Download Center. To connect to SQL Server on a Linux machine, download the SQL Server client files from the Microsoft Download Center. Follow the instructions from Microsoft to install the SQL Server client on the ArcGIS client machine.

Follow these steps to install and configure the client you need to create the geodatabase. If you do not have permissions to install software, have your IT department perform these steps.

Install the ArcGIS client you will use to create the geodatabase.

Follow the instructions provided in the software installation guides.
Install a SQL Server client on the ArcGIS client computer.

When you install the SQL Server client on a 64-bit operating system, use the 64-bit SQL Server client executable. If you run the 32-bit SQL Serverclient on a 64-bit operating system, installation will fail.

Now you can create a geodatabase.

Create a geodatabase

You can run the Create Enterprise Geodatabase tool from ArcGIS AllSource (Desktop Standard or Desktop Advanced), or run a Python script from an ArcGIS AllSource (Desktop Standard or Desktop Advanced) or ArcGIS Server (enterprise edition) client to create both a database and geodatabase.

Run the Create Enterprise Geodatabase tool

If you have access to ArcGIS AllSource, you can run a geoprocessing tool to create the geodatabase.

Follow these steps to create a geodatabase using the Create Enterprise Geodatabase tool in ArcGIS AllSource:

Start ArcGIS AllSource.
Open the Create Enterprise Geodatabase tool.

You can search for or browse to this tool, which is located in the Geodatabase Administration toolset of the Data Management toolbox. See Find a geoprocessing tool for general information about opening geoprocessing tools.
Choose SQL Server from the Database Platform drop-down list.
In the Instance text box, type the name of the SQL Server instance to which you will connect.
In the Database text box, type the name of the database where you want to store the geodatabase.

If a database with that name does not exist in the SQL Server instance, the tool creates it. If the database exists, the tool uses it to store the geodatabase.
Connect to SQL Server as a system administrator. Use either an operating system-authenticated login that is a member of the sysadmin fixed server role in SQL Server, or type a database username and password for a sysadmin user.
- To authenticate as a database user who is in the sysadmin role, type the sysadmin username in the Database Administrator text box and the corresponding password in the Database Administrator Password text box.
- To authenticate using a sysadmin operating system-authenticated login, check Operating System Authentication. You must be signed in to Windows with the correct login to use this option.
Choose the schema that will contain the geodatabase.
- If you want a user named sde to be the geodatabase administrator and the geodatabase to be stored in the sde user's schema, check Sde Owned Schema.
- If you want the dbo user to be the geodatabase administrator and the geodatabase to be stored in the dbo schema, uncheck Sde Owned Schema.
For more information about storing the geodatabase in either the sde or dbo schema, see Comparison of geodatabase owners in SQL Server.

If you choose an sde-owned schema, the Geodatabase Administrator text box is populated with sde. If a user with that name does not exist, the tool creates the user and its corresponding schema and grants the user the privileges required to create a geodatabase.

If you are using database authentication and a dbo schema to store the geodatabase, type the name of a user who is dbo in the SQL Server instance in the Geodatabase Administrator text box.

If you are using operating system authentication to connect to the database, your current login must be in the SQL Server sysadmin fixed-server role to create a dbo-schema geodatabase.

Type a password for the geodatabase administrator in the Geodatabase Administrator Password text box.

If the geodatabase administrator you specified already exists in the database, you must type the correct password for the existing user; this tool does not change the password.
To specify a file for the Authorization File field, browse to and choose the keycodes file that was created when you authorized ArcGIS Server (enterprise edition).

This file is created in the \\Program Files\ESRI\License<release#>\sysgen folder on Windows servers and /arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License<release#>/sysgen on Linux servers. Copy the keycodes file to a location you can access with the Create Enterprise Geodatabase tool. If you have not already done so, authorize ArcGIS Server now to create this file.
Click Run.
Messages related to geodatabase creation are written to the sde_setup.log file, which is created in the directory specified for your %TEMP% variable on the computer where the tool is run. If you have any problems creating a geodatabase, check this file to troubleshoot the problem.

A database and log files are created in the default SQL Server location if you did not specify an existing database. A geodatabase is created in the database. If you chose an sde-schema geodatabase, a database-authenticated sde login, database user, and schema are created.

Next, you can create a user who can load data into the geodatabase.

Run a Python script

You can copy, save, and run the script provided here to create a geodatabase from ArcGIS AllSource (Standard or Advanced) or ArcGIS Server (enterprise edition).

Tip:

For information about running Python from an ArcGIS Server machine, see ArcGIS Server and ArcPy.

Create a text file on the ArcGIS client machine and copy the following script into the file, providing options and information specific to your site:

"""
Name: create_enterprise_gdb.py
Description: Create an enterprise geodatabase in SQL Server with provided DBMS connection information
Author: Esri
"""

# Import system modules
import arcpy, os

# Local variables
# Replace the path with the location of your keycodes file
# Replace all arcpy.management.CreateEnterpriseGeodatabase information except "SQL_SERVER" and license with values specific to your site
license = os.fsencode("\\Program Files\\path\\to\\authorization_file\\keycodes")

try:
    arcpy.management.CreateEnterpriseGeodatabase("SQL_SERVER",
                                                 "gisprod",
                                                 "entgdb",
                                                 "DATABASE_AUTH",
                                                 "sa",
                                                 "N0pe3king!",
                                                 "SDE_SCHEMA",
                                                 "sde",
                                                 "sdepwdGLSCfhjWXQGQ",
                                                 "",
                                                 license

)
    for i in range(arcpy.GetMessageCount()):
        arcpy.AddReturnMessage(i)
    arcpy.AddMessage("+++++++++\n")
except:
    for i in range(arcpy.GetMessageCount()):
        arcpy.AddReturnMessage(i)

Replace values as noted in the script.
Save the file with a .py extension.
Run the script.
You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for your TEMP or TMP variable on the computer where the script is run. If you have any problems creating a geodatabase, check this file to troubleshoot the problem.

You now have a geodatabase in SQL Server.

Next, you can create a user who can load data into the geodatabase.

The SQL Server administrator creates the database; the geodatabase administrator creates the geodatabase

If the person creating the geodatabase does not have access to the database administrator's password, the database administrator must create the database and the geodatabase administrator's login, user, and schema. If the geodatabase is to be stored in the sde schema, the database administrator creates an sde login, user, and schema. If the geodatabase is to be stored in the dbo schema, the database administrator creates a login and user, and assigns the user to be the owner of the database. See Comparison of geodatabase owners in SQL Server for information about geodatabase storage options.

The geodatabase administrator then connects to the database as the geodatabase administrator and creates a geodatabase using the Enable Enterprise Geodatabase geoprocessing tool or a Python script.

The Enable Enterprise Geodatabase tool takes a database connection file as input and creates the geodatabase system tables, views, functions, and procedures.

Install and configure SQL Server

Before the geodatabase administrator can create a geodatabase, the SQL Server administrator must do the following:

Install SQL Server and configure the instance. How you configure the instance depends on the needs of your organization. Check the ArcGIS system requirements before you proceed to ensure the database management system, ArcGIS, and hardware combinations you want to use are supported. Configure the SQL Server instance to accept remote connections; by default, remote connections are not enabled. Consult the Microsoft documentation for SQL Server installation and configuration information.
Note:
The SQL Server instance must use a collation that is not case sensitive, and the database cannot use a Turkish collation.
Create a database.
Set the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON. If the SQL Server administrator does not set these options to ON but grants the geodatabase administrator the ALTER permission in the database, the Enable Enterprise Geodatabase tool changes these settings on the database when it is run. If the SQL Server administrator does not set these options, and the geodatabase administrator does not have sufficient permissions to change them, geodatabase creation fails.
Add or create a login that is mapped to a database user named sde or to a database user who is the database owner.
If using an sde-schema geodatabase, create a schema named sde in the database. Set this as the sde user's default schema.
If using an sde-schema geodatabase, the sde user must have, at a minimum, the following privileges: CREATE FUNCTION, CREATE PROCEDURE, CREATE TABLE, and CREATE VIEW. For other privileges, see Privileges for geodatabases in SQL Server.

Install clients

The geodatabase administrator (or your IT staff) can install ArcGIS AllSource (Desktop Standard or Desktop Advanced) or ArcGIS Server (enterprise edition) to create a geodatabase by running a Python script. To run the Enable Enterprise Geodatabase geoprocessing tool instead of a script, install ArcGIS AllSource.

Note:

You need an ArcGIS Server (enterprise edition) keycodes file to authorize the geodatabase in the next section. Even if you do not run a Python script from an ArcGIS Server machine to create the geodatabase, you must install and authorize ArcGIS Server to get the keycodes file. You may need to copy the keycodes file from the ArcGIS Server machine to a location that the Enable Enterprise Geodatabase geoprocessing tool can access.

In most cases, ArcGIS clients are installed on a different computer than SQL Server. Therefore, you must have a supported SQL Server client installed on the ArcGIS client computer to connect to the database. See ArcGIS system requirements for SQL Server to determine which version of the SQL Server client to install to connect to the version of SQL Server you are using.

To connect to SQL Server on a Microsoft Windows machine, you can download the SQL Server client from My Esri or from the Microsoft Download Center. To connect to SQL Server on a Linux machine, download the SQL Server client files from the Microsoft Download Center. Follow the instructions from Microsoft to install the SQL Server client on your client machine.

Install the ArcGIS client you will use to create the geodatabase.

Follow the instructions provided in the software installation guides.
Install a SQL Server client on the ArcGIS client computer.

When you install the SQL Server client on a 64-bit operating system, use the 64-bit SQL Server client executable. If you run the 32-bit SQL Serverclient on a 64-bit operating system, it will fail.

The ArcGIS client is ready to connect to the database and create a geodatabase. Use one of the methods described in the next section to do this.

Create a geodatabase

The geodatabase administrator can run the Enable Enterprise Geodatabase tool from ArcGIS AllSource (Desktop Standard or Desktop Advanced), or run a Python script from an ArcGIS AllSource (Desktop Standard or Desktop Advanced) or ArcGIS Server (enterprise edition) machine to create a geodatabase in the existing database.

Run the Enable Enterprise Geodatabase tool

You can run the Enable Enterprise Geodatabase tool from ArcGIS AllSource to create a geodatabase in an existing SQL Server database.

Follow these steps to create a geodatabase using the Enable Enterprise Geodatabase tool in ArcGIS AllSource:

Start ArcGIS AllSource.
Connect to the SQL Server database as the geodatabase administrator.

Save the user's password on the Database Connection dialog box.
Open the Enable Enterprise Geodatabase tool.
See Find a geoprocessing tool for general information about opening geoprocessing tools.
Drag the database connection you created in step 2 into the Input Database Connection field.
Browse to the ArcGIS Server authorization file that was created when you authorized ArcGIS Server and add it to the Authorization File text box.

When you use the wizard to authorize ArcGIS Server, a keycodes file is written to the server where the software is installed. The keycodes file is created in Program Files\ESRI\License<release>\sysgen on Windows servers and /arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License<release#>/sysgen on Linux servers. Copy the file to a location the Enable Enterprise Geodatabase tool can access. If you have not already done so, authorize ArcGIS Server to create this file.
Click Run.
Messages related to geodatabase creation are written to the sde_setup.log file, which is created in the directory specified for your %TEMP% variable on the computer where the tool is run. If you have any problems creating a geodatabase, check this file to troubleshoot the problem.

A geodatabase is created in the database.

Next, the database administrator can create a user who can load data into the geodatabase.

Run a Python script

To create the geodatabase by running a script from ArcGIS AllSource (Desktop Standard or Desktop Advanced) or ArcGIS Server (enterprise edition) installed on a Windows machine, you can use the script provided here.

Tip:

For information about running Python from an ArcGIS Server machine, see ArcGIS Server and ArcPy.

Follow these steps to run a Python script to create a geodatabase in an existing SQL Server database:

Create a text file on the ArcGIS client machine and copy the following script into the file, providing options and information specific to your site:

# Name: enable_enterprise_gdb.py
# Description: Create an enterprise geodatabase in an existing SQL Server database

# Import system modules
import arcpy, os

# Local variables
# Replace paths with those appropriate to your site
license = "\\Program Files\\path\\to\\authorization_file\\keycodes"
tempdir = "C:\\temp\\"
connection_file_name = "egdb_connection.sde"

# Check for the .sde file and delete it if present
connection_file_name_path = os.path.join(tempdir, connection_file_name)

arcpy.env.overwriteOutput=True
if os.path.exists(connection_file_name_path):
	os.remove(connection_file_name_path)

# Create a connection to the geodatabase as the geodatabase administrator
# Replace all values except tempdir, connection_file_name, "SQL_SERVER", and "SAVE_USERNAME"
arcpy.CreateDatabaseConnection_management(tempdir,
                                          connection_file_name,
                                          "SQL_SERVER",
                                          "gisprod",
                                          "DATABASE_AUTH",
                                          "sde",
                                          "Tgdbst@rtsh3r3",
                                          "SAVE_USERNAME",
                                          "entgdb"
)
# Enable geodatabase
try:
    arcpy.EnableEnterpriseGeodatabase_management(connection_file_name_path, license)
except:
    for i in range(arcpy.GetMessageCount()):
        arcpy.AddReturnMessage(i)

Replace values as noted in the script.
Save the file with a .py extension.
Run the script.
You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for your TEMP or TMP variable on the computer where the script is run. If you have any problems creating a geodatabase, check this file to troubleshoot the problem.

You now have a geodatabase in the SQL Server database.

Next, the database administrator can create a user who can load data into the geodatabase.

ARCGIS

CAPABILITIES

BUY ARCGIS

INDUSTRIES

Support & Services

SELF-SERVICE

CONTACT US

ESRI STORIES

About Esri

About GIS

Commitment to Innovation

Note:

Note:

Run the Create Enterprise Geodatabase tool

Run a Python script

Tip:

Note:

Note:

Run the Enable Enterprise Geodatabase tool

Run a Python script

Tip: