Enterprise Geodatabase in ArcGIS Pro

• The tutorial demonstrates how to deploy an enterprise geodatabase for a real estate business in Linn County, Iowa.
• The goal is to transition from a file geodatabase to an enterprise geodatabase which helps with multiuser access and location market analysis.

Requirements

• ArcGIS Pro Standard or Advanced
• ArcGIS Server (installed and authorized)
• Microsoft SQL Server (or another supported RDBMS)
• SQL Server Management Studio (SSMS) is needed for SQL server interaction

Tutorial Outline

• Create a Multiuser Geodatabase (10 minutes): Deploy the geodatabase as the database administrator.
• Migrate Real Estate Data (15 minutes): Load data as the data owner.
• Create Data Users and Assign Privileges (15 minutes): Create users with specific privileges.

Creating a Multiuser Geodatabase

• The goal is to create an enterprise geodatabase by using SQL Server from ArcGIS Pro.
• First is a System Requirements Review.
• SQL Server is used as our Relational Database Management System (RDBMS).
• This tutorial uses SQL Server, but other RDBMS like Oracle or PostgreSQL can be used with variations.
• It's assumed SQL Server (or another RDBMS) is already installed with a database administrator account.

Deployment Components

• SQL Server is installed on our machine.
• An ODBC driver allows communication between ArcGIS and SQL Server.
• We need database administrator account for the geodatabase creation.
• ArcGIS Server installed and authorized to retrieve needed keycodes files.
• We will use ArcGIS Pro, and SQL Server Management Studio (SSMS) to connect with the SQL Server.
• Other RDBMS like Oracle, IBM Db2, PostgreSQL, and SAP HANA can also be used.

Note:

• Base ArcGIS Enterprise deployment documentation and the "Get started with ArcGIS Enterprise Builder" tutorial are a good starting point.

SQL sa User

• Access to a database administrator account is essential.
• On SQL Server the user is named SA (system administrator).
• The database administrator's main duties:
• Has the highest level of privileges.
• Creates databases, users, and roles.
• Assigns database privileges, and can block connections.
• Sees all locks in the geodatabase.
• Backs up and recovers data.
• Configures storage.
• We will use the RDBMS-specific client where database management happens, and that client is SSMS.

Connecting to SQL Server via SSMS

• Click the Search button in the windows taskbar, then type SSMS, and under Best Match, click on the program
• Select SQL Authentication mode to create an SA account.
• Verify that Database Engine is selected for Server type.
• Type the SQL Server instance name for Server name.
• Verify that SQL Server Authentication is selected for Authentication.
• Enter "sa" for Login and the password for the sa user for Password.
• Once connected, inspect the server's properties by right-clicking the server's name.
• Familiarize yourself with the instance details, which are needed to create the enterprise geodatabase.

Creating the Enterprise Geodatabase

• Next is creating the enterprise geodatabase in SQL Server as the data storage in ArcGIS Pro.
• Download the "RealEstateData_MLS.zip" file and unzip to your computer.
• Open the "RealEstateData_MLS.aprx" project in ArcGIS Pro.
• Then sign in to your ArcGIS account.
• The project contains data for this workflow.

Geoprocessing Tools

• Create Enterprise Geodatabase: Sets up a database to use as a geodatabase.
• Enable Enterprise Geodatabase: adds geodatabase behavior to an existing database.
• To start from scratch, use the "Create Enterprise Geodatabase" tool.

How to create

• Find Geoprocessing under the View tab, then Windows.
• In Geoprocessing, search Create Enterprise Geodatabase.
• In the tool, set Database Platform to SQL Server.
• Type SQL Server instance name for instance.
• Type ListingsDB for the database.
• For Authentication, leave Operating System Authentication unchecked.
• Type "sa" for Database Administrator and give the password.
• Ensure the Sde Owned Schema box is checked
• Enter a password for the Sde user.

SQL Keycodes File

• Point to the keycodes files made from authorizing ArcGIS Server.
• Those keycodes are found in C:\Program Files\ESRI\License<release#>\sysgen in Windows.
• Click Run to make the database.

Schema Notes

• The difference between SDE or DBO schema may vary for each organization.
• We split the geodatabase/database administrator duties.
• Unchecking "Sde Owned Schema" selects DBO-owned schema.
• For a more detailed comparison use the Comparison of geodatabase owners in SQL Server.

Connecting to the Geodatabase:

• In ArcGIS Pro, connect to the ListingsDB geodatabase as the database administrator (SA).
• This helps manage the geodatabase.
• In the View tab in Windows, select Catalog Pane.
• Right-click the Database folder and select New Database Connection.
• Pick SQL Server for Database Platform.
• Enter the instance of your SQL server.
• Pick Database authentication for Authentication Type.
• Give username as "sa", and its associated password.
• Then check save user/password.
• Finally give the database name as ListingsDB.
• The new LINNSQLRLT.sde connection will appear soon.

SDE

• This file is used to store database details and credentials of the connecting user, which facilitates subsequent secure connections.
• It's best practice to name this file like database name_database user.sde.
• Rename LINNSQLRLT.sde to ListingsDB_SA.sde.
• Create a data connection for the geodatabase administrator SDE user.
• In catalog, right click the database, and select new database connection.
• Again verify the SQL Server is chosen
• Verify connection and database details like previously
• When saving it, select save user/password, enter database name ListingsDB, and save.
• Rename the new connection to ListingsDB_GDBAdmin.sde.
• It’s common to use this connection for database administration, but we won’t be doing that.
• You have now successfully created and connected to the new geodatabase.

Data Migration

• Going forward, you will set up the enterprise geodatabase for the real estate business by the following steps: - Creating a database user named GIS that will act as the owner of the data. - Creating a database connection. - Using the new database to transfer current real estate data.
• First assign ownership of the data.

Create a Data Owner User

• You'll create a user who works as the data owner for ListingsDB enterprise geodatabase.
• The owner is responsible for loading and managing data.
• Having this will isolate general database duties from common editing duties.
• Use the Create Database User tool, which requires the SA account.
• In Catalog, under Databases, right click ListingsDB_SA.sde, and select to administration, then select connect database User.
• Verify that ListingsDB_SA.sde is correct for Input Database Connection.
• Enter "GIS" for the Database User and a strong password.
• Once the tool has finished, make a database connection as the GIS user.
• Verify SQL server and other previously mentioned information
• Enter the GIS Username and Password
• This will give the data owner an account.
• Now open the catalog pane, find and click the new connection, renaming it ListingsDB_GIS.sde.

Note:

• The Databases folder contains the RealEstateData_MLS.gdb data.
• We will migrate data soon from the geo database to the new database.

Import Real Estate Data

• You'll now utilize the GIS user to upload real estate data into the ListingsDB geodatabase.
• The data is in the RealEstateData_MLS.gdb file.
• The geodatabase contains four feature classes, which are the following:
• RealEstateBLL
• RealEstateParcel
• RealEstatePoliticalTownship
• RealEstateSubdivision
• To load data we will use the Feature Class to Geodatabase tool.
• The conversion tool box contains the tool we will need.
• First right-click ListingsDB_GIS.sde under the import menu, and choose the right option.
• For input be sure to import the files from the RealEstateData_MLS file, selecting each file from BLL to Subdivision
• Select the new gdb, ListingsDB_GIS.sde as output
• If everything is right press run.
• Then right click and refresh the output database to see the content.
• All four data sets have now been copied, and are owned by the GIS User.

Creating Additional Database Users

• You will create four accounts, one for each employee, using the SA account.
• Sarah
• Scott
• Lisa
• Sandra
• You will start by creating a user for Sarah.
• Right click the SA database connection, and open "Create Database User"
• Do this to create the four employees, and their connections via the standard format
• Open the panel in catalog, and expand your new databases to confirm

Assigning Privileges

• There are two methods to grant the permissions
• You can assign permissions to individuals, or groups

Granting Permissions to Individuals

• Verify the GIS.sde database connection is connect and opened.
• For a single data user, right click the feature in question, for example BLL and chose properties
• Choose privileges for the specific dataset.
• After selecting the user verify what privileges they should have
• If they are adding and deleting features, be sure to mark those, otherwise they should have read only.
• You have now granted the user privileges to the folder and data.

Alternative: Roles

• Another way to grant privileges is to create roles, and grant roles data access.
• This can be more efficient with on boarding/off boarding.
• Open create role under the SA account database connection,
• Name the role relevant to the dataset name.
• For multiple names enter them in commas, don't add space.
• After you've made your roles, open the data file/folder under the GIS account.
• Search for the change privileges tool, and select who can perform what.
• You will now test the four new database accounts.

Testing all User Privileges

• Under catalog, refresh the four new accounts.
• The data access is now separated based on specific duties.
• All employees now have access to appropriate data at specific access levels.
• The Sa account still is the main account that is able to grant/revoke permissions.
• In the final part, you created data accounts for all personnel with appropriate levels of database access.