Durmus Cesur Ph.D., PMP, PE, GISP, ESRI Authorized & Certified Instructor,

CompTIA CTT+, ESRI Enterprise Associate, ESRI ArcGIS Desktop Associate and Professional

President/Owner DZ CIS, LLC

Module I:

Introduction to Enterprise Geodatabase

Enterprise Geodatabase - Definition Geodatabase

Collection of geographic datasets

Native data structure for ArcGIS

Some benefits

Establishing relationships among features

Enforcing data integrity through domains

Generating intelligent features through topology and geometric networks

Enterprise geodatabase

Implementation of geodatabase using Enterprise databases for multiple users and uses

Geodatabase Elements Behaviors: Domains, attribute defaults, subtypes, connectivity

and relationship rules, split/merge policies etc.

Toolboxes (tools, models, scripts)

Feature datasets with associated spatial reference Feature classes

Geometric networks

Topology

Terrain, survey, GPS, representations, schematics

Networks

Relationship classes

Raster datasets

Tables (Non-spatial objects)

Geodatabase types Personal

File

Enterprise

Desktop

Workgroup

Enterprise

Comparison of Geodatabases Personal File Desktop Workgroup Enterprise

Platform Windows Any Windows Windows Any (depending on the database)

Storage platform

In Access database

In folders/files

MS SQL Server Express

MS SQL Server Express

Data files

Storage capasity

2 GB 1 TB 4 GB 4 GB Unlimited

Functionality

Desktop format

Improved Desktop format

Project level use or replication

Departments, Teams

Organization-wide (enterprise)

Enterprise GIS Database Server Connections & Models Connections:

Direct connect

Application server connect

OLE DB

Models:

Single spatial database model

Multiple spatial database model

Class setup ArcInfo 10 Desktop

ArcSDE for SQL Server 2005/2008

SQL Server 2005/2008

Module 2

Introduction to SQL Server

SQL Server 2008 Component Databases SQL Server components

System databases: Stores internal information related to the management & maintenance of the database Master

Model

Msdb,

Temp,

Distribution (optional when SQL Server is configured as distributor database for replication)

User databases: Stores user information Generate one or more databases to store user information

SQL Server Component Databases – Database and Data files Database

Logical data structure

Physical storage transparent to the user

Implement object security at this level

Data files

Primary files (.mdf)

Secondary (.ndf)

Transaction log files (.ldf)

SQL Server Client/Server Operation Client applications (e.g., ArcGIS Desktop and others)

Server processes (e.g. gsvr)

Memory pool of the server

SQL Server Instance Instance

Default instance: Hostname

Named instance: Hostname\Instance name

Each instance has its own system databases (completely separate from each other)

Used to attach correct database engine by ArcSDE

SQL Server Memory Management SQL server dynamically allocates memory

Default allocation of all available memory

Limit buffer cache memory

Set maximum server memory

2GB

Use Address Windowing Extensions (AWE) for servers with over 4 GB physical memory

Transaction Log Records all data manipulation language statements

such as Select, Insert, Update, Merge etc.

Keep them separate from the data files

Monitor growth, do not autogrow unrestricted (limit the growth) and truncate when needed

Switch recovery model for large data loads and otherwise implement full recovery model and back it up

File groups Logically store data within database

Primary is the default one

Filegroups are for:

Back up of large databases

Improve performance by controlling physical object placement

Data striping

Scalability

One or more files support them

proportional fill strategy is used across all the files within each filegroup based on the free space available in each file

Data File Storage Physical storage

Data file > Extent > Page

Data files are .mdf, .ndf, .ldf

Extents and pages auto allocated by SQL Server

Logical storage

Database > Filegroup > Table

Containers for data

Authentication Types of authentication

Windows

Windows logins are used

Mixed

SQL Server or windows logins are used

Can use any mode with ArcSDE depending on the need/requirements

SQL Server Security Login provides access to SQL Server

Builtin\Administrator Automatically assigned sysadmin role

Default for all windows administrators

System Administrator (sa) Enabled with mixed mode authentication

User provides access to a database DBO Database owner

Created in every database

Other users can be mapped to DBO

SQL Server Security Fixed roles

Predefined roles used to simplify administration

Two types as fixed server and database roles

Fixed server roles

Manages instance level permissions

Sysadmin has full administrative privileges

Fixed database roles

Used to manage database level permissions

For more flexibily user-defined roles are generated

Statement & Object Permissions Statement permissions to generate database objects

Data Definition Language (DDL) statements

e. g., Create table etc.

Granted at the database level

Managed with SQL Server tools

Object permissions to select and edit database objects

Data Manipulation Language (DML) statements

e. g., select, update, insert etc.

Granted at object level

Managed with ArcSDE tools

SQL Server Management Studio SQL Server Administration Application

Used to manage, administer all SQL Server components

Relational engine

Analysis services

Reporting services

Integration services

Notification services

Mobile databases

SQL Server Management Studio Management Studio Components

Object Browser

Browse and manage SQL Server components

Query Editor

Create, edit and execute queries

Module 3 Installing, Configuring, and

Optimizing ArcSDE

ArcSDE Installation Set up:

Run setup.exe (located under the SQL Server folder)

Configure ArcSDE

Post installation can be done either:

Using wizard

Using manual installation

ArcSDE Environment Auto installation using wizard

Generate SQL Server database

Generate ArcSDE administrator (sde or sa= dbo)

Manual installation

Use SQL Server Management Studio to:

Generate a SQL Server database

Generate ArcSDE admin user (sde or sa=dbo or other) and grant permissions

Generate additional filegroups for data and log files storage

ArcSDE Administration Login for ArcSDE background process

Owns and manages ArcSDE repository

It can be either

SQL Server login (sde)

Windows users (dbo)

ArcSDE Schema for Repository and Database Can reside in SDE or DBO schema

DBO user not require additional permissions since any login mapped to dbo has admin privileges and member of fixed server sysadmin role

Can connect using windows or SQL Server authentication

Connection information is used to generate the database

Spatial database is required for spatial data dictionary

Contains repository tables maintained by ArcSDE

Give it a unique database name (single database model) or sde (multi database model)

ArcSDE Repository & Configuration Files Generates repository tables and stored procedures

Owned by SDE or dbo

Optionally configure before executing

giomgr.defs

dbtune.sde

ArcSDE Configuration files

Text files located under %SDEHOME%\etc directory include:

Giomgr.defs > imported into server_config table (during post install)

Dbtune.sde > imported into dbtune table (during post install)

Dbinit.sde

Can alter these files using ArcSDE commands

ArcSDE Authorization Required to start the service and direct connects to

ArcSDE

Stored in SDE_SERVER_CONFIG table

ArcSDE Service Adds service name and port number to the OS and ArcSDE

services files (system services and %SDEHOME%\etc\services)

Stores SDE password in the registry

Generates service dependency on SQL Server

Generates registration keywords ADMIN_DATABASE and SDE_DBA_USER

All spatial database communication through TCP/IP

Service name identifies ArcSDE on a host

Each service binds to one port on which listens for client connections

Add matching entry to client’s services files or use port number

Giomgr process Persistent ArcGIS Server (SDE) process that listens for

incoming requests Logs into SQL Server as SDE or DBO

One giomgr per spatial database

Terminate the processes with killp <pid> where pid is process id found by sdemon –o info –

I users

In shut down shut down sde, then SQL Server and in start up start SQL Server and then sde Sdemon –o start (Or windows net start <ArcSDE service

name>) Sdemon –o shutdown (Or windows net stop <ArcSDE service

name>)

Single and Multiple Spatial Database Model Comparison

Single Database Model Multiple database Model

Database vs. Geodatabase match

A separate database per geodatabase

Multiple databases per geodatabase

Service Separate service per database

Single service for multiple databases

Backup/recovery One database Multiple databases

User privileges Easier to admin Difficult to admin due to cross database mapping to single geodatabase

Repository schema Can be DBO schema Can not be owned by dbo (sde is the owner)

Support for cross database queries

Not supported (all geo/databases are separate)

Supported (all databases act as single geodatabase)

Module 4 Database Backup and Recovery

Database Backups Backup allows for data recovery

Generates a copy of the database to be used for regeneration and restore in the event of failure or user errors

Backup options Database backups

Full Differential Transaction log File or filegroup

GIS-based backups ArcSDE exports File geodatabase exports Geodatabase XML exports Other data format exports

Database Backups Decision on backups depends on:

Minimum acceptable loss (if there is any allowed)

Minimum acceptable downtime

Requirement to restore to point of failure

Size of the databases

Hardware capacity

Frequency of edits

Consideration for Backups Backup recommended before upgrades:

Major release

Minor release

Service pack

Patch

SQL Server Database Backup Types Full backups

Copying all database

Differential backups

Copying changes since the last full backup

Transaction log backups

Copying transaction log

File or filegroup backups

Copies only individual files or filegroups

For ArcSDE backup SDE repository with the data

Database Recovery Controls the backup and recovery operations

Property of individual databases

Three types of recovery

Simple: Recover to most recent database backup

Full: Recover to point of failure

Bulk-logged: Recover to last log backup

Simple recovery Relies on last database backup

Can recover only to last full or differential backup

Transaction log is not used for recovery

Minimal transaction logging

Does not allow for a point-in-time recovery

Not good for editing environment

Full Recovery Relies on database and transaction log backup

Allows point-in-time recovery

Requires log file backup

Appropriate for an editing environment

Minimal risk due to damaged data file(s)

Logs all operations

Bulk Logged Relies on database and transaction log backup

Bulk operations are minimally logged

Keep tracks of modified extents for backups but does not log bulk operations

Does not allow point-in-time recovery

Can recover up to last transaction log backup

Same as full recovery for ArcSDE

Restoring Databases Restoring depends on:

Frequency of backup

Type of backup

In single database model only need to restore a single database, in multiple database model need to restore sde and all spatial databases (need to recover all databases to the same point-in-time)

Detaching/Attaching Databases Removes and add database from/to current SQL Server

instance Includes data files and transaction log files

Can attach files to any SQL Server instance

Must stop ArcSDE service before this

Uses

Migration from development to production environment

Receiving existing spatial database from another source

Backup device

Scaling up ArcGIS Server (ArcSDE)

Detaching/Attaching Databases Can attach copied or detached databases

Need to synchronize user and login on the new servers

Generate service using post-install or manually

Do not attach log file

Module 4 Managing ArcSDE

ArcSDE Configuration ArcSDE configuration is stored in SQL Server database

under sde or DBO schemas

ArcSDE data dictionary tables store properties of geodatabase

SQL Server stored procedures manages data dictionary tables

Do not edit tables or calls stored procedures unless using multiversioned views

ArcSDE Data Dictionary Tables that store information on:

Feature classes

Raster datasets

Tables

Versions

Log files

Rules and behaviors

(They are prefixed either with SDE or GDB prefix)

ArcSDE Stored Procedures &Home Directory Stored procedures are:

Written in T-SQL

Can be viewed through SQL Server Management Studio

Manual execution is not recommended due to complex relations they manage/maintain for geodatabase

SDE home directory stores binary (located in %SDEHOME%\bin ) and configuration files for ArcSDE

%SDEHOME% path is stored in environmental variable

ArcSDE Binary Files Located in SDE home directory under bin folder

Contains administrative commands to generate, manage and monitor/maintain ArcSDE service

ArcSDE command with no arguments enables viewing usage for the command

Command syntax can be found in the administration commands and help files

sdemon and sdeservice commands sdemon manages ArcSDE

Used to: Check service status and connections

Display SDE_PROCESS_INFORMATION table

Optionally records transfer statistics when SDEPROCSTATS is set in SDE_SERVER_CONFIG table

Stop, start, pause, resume, kill service (can be done using killp and OS PID found in Task Manager > Processes tab)

sdeservice Manages ArcSDE Services (in windows)

Used to: Generate , delete, modify, list services

ArcSDE Configuration Files Located in %SDEHOME%\etc directory Controls configuration of the ArcSDE service Can be altered using SDECONFIG or SDEDBTUNE

commands Stored in the database as tables Dbtune.sde

Stores configuration parameters for ArcSDE repository Managed by SDEDBTUNE command

Giomgr.defs Stores configuration of server processes and memory Dynamic parameters Managed by SDECONFIG command

ArcSDE Service Configuration Files Stored in %SDEHOME%\etc directory

Read at service startup

Include:

Services.sde

Sets ArcSDE instance name and port number

Matches entry in OS services file

Dbinit.sde

Sets environment variables

Overrides OS environment variables

SDE_SERVER_CONFIG Table Records ArcSDE initialization parameters

Regulates resource usage

Update/Modify by ArcSDE Administrator using:

Sdeconfig command

Export, edit using text editor and import

ArcSDE Diagnostic Files Stored in %SDEHOME%\etc directory

Include:

giomgr_<Service>.log – Information on client connections

Sde_<Service>.log – Error messages for application server

Sdedc_SQL_Server.log - Error messages for direct connects

Upgrade.log – Error messages for updating database objects

To log errors

SET SDEVERBOSE=TRUE in dbinit.sde file

Upgrading ArcSDE Backup databases

Export customizations of DBTUNE and SDE_SERVER_CONFIG

Backup ArcSDE software

Run sdesetup –o upgrade command or use post-installation

Stop, delete and regenerate the service

ArcSDE Client and Server All spatial data accessed over TCP/IP

All data retrieved through SQL Queries

ArcSDE :

Can be on server or client as gsrvr or direct connect dll respectively

Rewrites client SQL to access supporting tables

Perform secondary spatial filtering

ArcSDE Client and Server ArcSDE clients are backward compatible

To use complete geodatabase functionality both client and server need to be the same release

In upgrades either

Upgrade clients and server simultaneously

Upgrade clients first

ArcSDE Connections Application server connection:

ArcSDE Service is needed on the server Giomgr listens for connection requests and for each connection a

separate gsrvr is spawned When connnection closed gsrvr process terminates Increases load on the server and decreases load on the client

Direct connection Connect directly to the database

Requires client and server to be the same release

Uses Microsoft Data Access Components (MDAC) No ArcSDE Service required (no giomgr and gsrvr process)

and software included with the client used Increases load on the client and decreases load on the server

ArcSDE Connections For application server connections, specify

Server name Port number or service name User name and password for database authentication and for OS authentication

windows user privileges are used (no user name and password needed since these are provided through windows login and authenticated from the OS)

Version (default version for SDE schema is sde.DEFAULT and for DBO schema is dbo.DEFAULT)

For direct connection, specify sde:sqlserver:<server name> (in service box) Database name

OLE DB connections are COM-based data access interfaces Providers available with ArcGIS clients (distributed through MDAC) Read-only and non-spatial (can be customized through programming to make

it read/write)

Module 5 Managing Vector and Raster Data

in ArcSDE

Geodatabase

A relational database using object oriented analysis and design principles and features for the storage, query and manipulation of spatial (geographic) data and associated attributes

Contains both vector and raster data Logically appears as one table with spatial column (Shape) Physically stored in more than one table Named as database.owner.table_name

Geodatabase General Properties

Contains specific storage structures for features in the raster, vector and other format spatial data Contains collections of features, attributes, and

relationships among features Stores tables (object classes), feature classes, feature

datasets, raster datasets, raster catalogs, raster mosaics, topologies, networks and others. Implements various behaviors (functionality based on rules

such as domains, subtypes, relationships, and topology, geometric networks has been implemented)

Vector Data Storage Main objects Attributes stored in business table Geometries stored in feature table Grid tiles and envelopes stored in spatial index table

2D grid system used to partition the space Improves performance during retrieval Geometry envelope determines the tile sizes Calculated based on average feature size

Supporting objects ArcSDE column indexes

Named as R<id>_SDE_ROWID_UK

i table generates unique IDs Business table has triggers

SDE_LAYERS and SDE_TABLE_REGISTRY Tables sde_layers table stores names and properties of layers

with spatial column

Sde_table_registry keeps track of ArcSDE tables

Can list entries in this table using sdetable –o describe_reg command

Data Storage in ArcSDE Data storage controlled using configuration keyword Keyword is located in the SDE_DBTUNE table Entries underneath the keyword store parameters for table and index

generation Specified during loading Can generate additional keywords for custom loading Some keywords are defined by default Geometry storage controlled by GEOMETRY_STORAGE parameter

SDEBINARY: BLOB on F table (default storage scheme) OGCWKB: OGC Well-known library GEOMETRY: SQL Server 2008 spatial type

Cartesian coordinates

GEOGRAPHY: SQL Server 2008 spatial type Longitude/latitude coordinates

Dbtune Table Parameters ##DEFAULTS keyword is used to set default storage ##<Keyword> keyword for custom storage *_CLUSTER_* cluster an index

1 = yes, o = no Shape column is default on B table

*_INDEX_* index storage and fill factor With FILLFACTOR = # ON <filegroup>

Table storage *_STORAGE On <filegroup>

Inline BLOB data in the datapage *_TEXT_IN_ROW Descriptive text to appear in user interface UI_TEXT

Text string required for ArcGIS or ArcObjects

Image Data Type Storage SQL Server text, ntext, image data types stored on

text/image pages Row stores 16-byte pointer

Use TEXT_IN_ROW parameter Store smaller image strings in row on data page

Can store between 24 and 7000 bytes (default 256)

Stores pointer to the page in the case of too large strings

Large text_in_row may generate too many data pages and extents

Will be depreceated by Microsoft in future releases of SQL Server

Vector Data Storage Parameters The parameters in dbtune table/file include:

Parameters related to business table, prefixed with B_ Parameters related to feature table, prefixed with F_ Parameters related to spatial index table, prefixed with S_ Parameters related to adds table, prefixed with A_ Parameters related to delete table, prefixed with D_

Filegroups are used to control object placement The following are observed related to feature class, raster pixel,

and versioning related tables: Feature class tables: Read/Write, much smaller than raster,

contention with versioned tables can occur Raster pixel tables: Very large, Read-only Versioning tables: Read/Write, grow quickly, contention with

feature class table can occur.

ArcSDE Raster Data Storage Rasters are stored in geodatabase

Logically one table stores rasters and has one raster column Smallest unit is pixel, each pixel has a numeric value and they are

arranged in rows and columns

They are subdivided for geodatabase storage and in the case of multiband rasters multiple variables are stored

Raster datasets can be stored as individual rasters, raster mosaics or raster catalogs and feature classes can have attribute type of raster

All data stored in ArcSDE raster format in associated tables ArcSDE subdivides raster into tiles (blocks) for storage Each raster tile, band, pyramid level is stored in one row

These identified by ROW_NBR and COL_NBR Numbers starts from upper-left corner

Raster Pyramids Reduced resolution copies of the raster

Improves query performance

Each level tiled to be stored in raster blocks

Pyramids used in visualization not in analysis

Pixel dimensions double at each pyramid level

ArcSDE computes optimal number of pyramids based on the width and height of the raster

Raster Storage Schema Raster datasets stored in seven (7) related tables which

include: Business table AUX table: Stores statistic and color map BLK table: Stores pixel data BND table: Stores band information RAS table: Stores column records Vector footprint: F & S tables

In SDE rasters are registered in repository tables which include: SDE_RASTER_COLUMNS

SDE_LAYERS SDE_TABLE_REGISTRY

Supporting Objects for Raster Datasets & Storage Parameters Supporting objects: Index and ID generators Spatial column triggers (footprint) SDE_BND_X SDE_BLK_X SDE_AUX_X Storage parameters: B_X: Business table RAS_X: Raster table BND_X: Raster band table AUX_X: Raster auxillary table BLK_X: Raster block table F_X: Feature table S_X: Spatial index table

Raster Compression and Loading Rasters are compressed:

At generation time Different compressions may affect the usability of the datasets to reduce storage size, to improve query performance

Raster loading: Use fastest processor computer Calculate DBMS statistics during loading Generate raster statistics after load Make recovery model simple Disable auto close and auto shrink Maintain statistics manually to speed up loading Increase buffer sizes to increase throughput

ArcSDE Log Files References collection of IDs (i.e., selected records)

Stores IDs for efficient re-use

Stored in a database table

Used when:

Selected set > = 100

Versioning

Geodatabase replication

Types of ArcSDE Log Files Session log file tables

Log file table for each connection (per session) SDE/DBO-owned pool or user-owned temporary table Default setting is user-owned Three tables generated as sde_logfiles, sde_logfile_data, and

sde_session Default architecture

Shared log file tables User-owned log file tables shared by all sessions connected

with the same DBMS login Two tables generated as sde_logfiles, sde_logfile_data Requires create table privileges that can be revoked after

generation of the log file May generate contention

Types of ArcSDE Log Files Stand-alone log file tables

Log file table for selection of each layer

Configure size for MAXSTANDALONELOGS

SDE/DBO-owned pool or user-owned temporary table

For SDE/DBO owned configure size for LOGFILEPOOLSIZE = <number>

For user-owned configure SESSION_TEMP_TABLE and set LOGFILEPOOLSIZE = <number>

ArcSDE Log Files Parameters HOLDLOGPOOLTABLES

Determines when tables are released

Used with session and stand-alone pools

When set to True table held for duration of the session and when set to False table released when selection set is removed

Log file contents are deleted when selection is unselected (cleared)

Log file storage is controlled by LOGFILE_DEFAULTS keyword stored in dbtune table

Used to separate log files into filegroups

Used only for shared log file architecture

ArcSDE Log Files Considerations Session/tempdb schema recommended architecture since:

CREATE TABLE permissions are not required

Log file tables are not managed in geodatabase

Transaction log is minimized

Keyset table

Provide mechanism for retrieving rows from an object

Implemented to speed up join of tables and populated when joining with > 100 records

Global tables generated in tempdb

Named as DBO.##keyset_<n>

Generated when needed (if there is none exist)

Module 6

Management of Versioned and Non-Versioned Geodatabases

Read-Only and Editable Databases Data could be editable (functional/operational layers ) or read-

only (raster and base layers), based on this various database features enabled.

In the case of editable layers there are two major types of editing functionality exist: Non-versioned editing

Short transaction model: Single edit session Edits made directly to feature class tables Single state of the database

No undo/redo possible

Versioned editing Long transaction model

Edits recorded in add, delete and other versioning related tables Many states of the same feature/database can exist

Undo/redo possible

Non-versioned Editing – Simple data only

Points, lines, polygons, annotations and relationships Topology, network can not be edited

Complex geometries can not be manipulated

Based on standard DBMS transaction model Edits committed as single transaction model Uncommitted edits execute database rollback Depending on the isolation level (for ArcgIS is read

uncommitted and for SQL Server read committed), locking (exclusive lock causing blocking of other editors) and concurrency (can not edit the same feature at the same time by multiple users) issues observed

Support database integrity constraints

Versioned Editing Extends short transaction model

Edits performed in multiple sessions

Enables multiple concurrent states of the database

Versions

Long transactions, multiuser environment, archiving

Complete isolation from other users and processes

Not affected by row-level locking

No data duplicated

ArcSDE versioning related tables keep track of changes

Replication uses the same framework (replica is a version)

Versioning and States Version: Abstraction for a unit of work

Can represent:

Snapshot of the database

Work order

Modification

Enables multiple users editing the data

No feature locks needed, no data duplicated

Only changes to versioned tables are recorded in Adds and Deletes tables

Each edit is numbered by state ID

State: Manage edits to the tables

Discrete snapshot of the database

Individual database transactions

Versioning Tables These tables manage geodatabase states and versions

Their storage controlled by DBTUNE keyword and can be modified

Include:

Repository versioning tables:

SDE_VERSIONS

SDE_STATES

SDE_STATE_LINEAGES

SDE_MVTABLES_MODIFIED

User versioning tables

Adds table

Deletes table

Business table

Versioning Tips Compression removes the unreferenced states and moves edits to business

table

Update statistics before and after compression for performance

Delta tables (Adds, Deletes tables) can grow quickly, therefore control their growth using dbtune keyword, separating them into different file group, and setting appropriate fill factor and compressing

Manage indexes and statistics for better performance on

Repository tables (states, state_lineages, mvtables modified)

Feature class tables (Business, F, S)

Delta tables (Adds and Deletes)

Thank you!!!