Administration for sql server an introduction
1 / 56

Administration for SQL Server - An Introduction - PowerPoint PPT Presentation

  • Updated On :

Administration for SQL Server - An Introduction. Wendy Wallace & Thomas Dunn ESRI – Redlands. SQL Server. Technical Workshop Scope. ArcGIS. Managing enterprise SQL Server geodatabases Prerequisites Working knowledge of ArcSDE technology basics Working knowledge of SQL Server basics .

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Administration for SQL Server - An Introduction' - mike_john

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Administration for sql server an introduction l.jpg

Administration for SQL Server - An Introduction

Wendy Wallace & Thomas Dunn

ESRI – Redlands

Technical workshop scope l.jpg

SQL Server

Technical Workshop Scope


  • Managing enterprise SQL Server geodatabases

  • Prerequisites

    • Working knowledge of ArcSDE technology basics

    • Working knowledge of SQL Server basics


ArcSDE technology

Arcsde technology l.jpg
ArcSDE Technology

  • Implements geodatabases in multiple brands of multi-user RDBMS

    • Standardizes data access from ArcSDE clients

    • Leverages data management capabilities of RDBMS

    • Leverages multiuser security of RDBMS

  • Provides data versioning (long transactions)

    • Multi-user simultaneous access and editing

    • Replication

    • Archiving

  • A core component of ArcGIS Server


Agenda l.jpg

Installing and Configuring ArcSDE

Connecting to the Geodatabase

Managing Users and Privileges

Geodatabase Maintenance Tasks

Storage Type Comparison



Three editions of arcsde technology l.jpg
Three Editions of ArcSDE Technology

  • Enterprise GDB solution for large organizations

    • Unlimited number of concurrent users

    • Requires 3rd party DBMS software

  • ArcSDE for Microsoft SQL Server Express Licensed for ArcGIS Server Workgroup

    • For small departments & organizations

    • Supports 10 concurrent users (editors & data readers)

  • ArcSDE for Microsoft SQL Server Express Licensed for ArcGIS Desktop/Engine

    • For 1 editor + 2 data readers

Sql server releases supported by arcsde l.jpg
SQL Server Releases Supported by ArcSDE

ArcSDE 10 and 9.3.1 fully support

  • SQL Server 2008 (all including R2)

  • SQL Server 2005 (SP2 and SP3)

    ArcSDE 9.3.1 is the last release that supports

  • SQL Server 2000

  • Windows 2000

Pre installation tasks l.jpg
Pre-installation Tasks

  • Verify the system requirements are met:

    • Operating system version & patch level

    • DBMS version & patch level

    • See

  • Find the SQL Server instance name

    • Used by ArcSDE to attach to the correct database instance

    • Default instance name = Hostname

    • Named instance: Hostname\<Instance Name>

Check sql server authentication mode l.jpg
Check SQL Server Authentication Mode

  • Windows Authentication only

    • Can only connect with Windows login credentials

    • Default authentication mode for SQL Server

  • Mixed Mode: SQL Server or Windows login

    • Can connect with Windows login or a login maintained within SQL Server instance

    • Administration for SQL Server logins is performed with SQL Server tools (e.g. SSMS)

    • SDE-schema geodatabases require a login named SDE, so Mixed Mode authentication is needed

  • Mode can be changed after SQL Server installation

Install arcsde software l.jpg
Install ArcSDE Software

The first part of the ArcSDE installation

  • Writes software files in the SDEHOME directory

  • Modifies system environment variables

    • Creates %SDEHOME% - location of ArcSDE files

      • Default: “C:\Program Files\ArcGIS\ArcSDE\sqlexe”

      • Another location can be specified

    • Appends %SDEHOME%\bin to %PATH%

Slide13 l.jpg


Server libraries and executables

Configuration and log files

Arcsde post installation wizard l.jpg
ArcSDE Post-Installation Wizard

  • Defines database and SDE admin user

    • Provides a choice of schema (SDE or DBO)

    • Creates a SQL Server database

  • Creates geodatabase repository

    • Creates ArcSDE metadata tables (the ArcSDE repository) in the database, thus creating a geodatabase

  • Authorizes ArcSDE

    • With software license

  • Creates ArcSDE service

Part 1 define database and sde admin user l.jpg
Part 1: Define Database and SDE Admin User

  • Create SQL Server database to contain a geodatabase

  • If using SDE schema:

    • Creates a SQL Server login called SDE

    • Adds SDE user to the database

    • Assigns permission to SDE user





Part 2 repository setup l.jpg
Part 2: Repository Setup

  • Repository: A collection of database objects owned by the ArcSDE admin user that store the state of the geodatabase

  • Repository can be stored on the DBO schema orthe SDE schema

  • Schema choice relates to who is the ArcSDE Admin user

Single geodatabase model l.jpg
Single Geodatabase Model


  • Each geodatabase in one stand-alone SQL Server database

  • Each geodatabase has own ArcSDE service (optional)


Repository and data

Slide19 l.jpg

Can have several single database GDBs

SQL Server Instance







Advantages to single spatial database model l.jpg
Advantages to Single Spatial Database Model

  • Simpler to create geodatabases

    • Just use the post installation wizard

  • Simpler to manage database objects and security

    • Object security is implemented at database level

    • Easier to keep track of users, schema, permissions

  • Simplifies backup and restore

    • Single operation to backup entire geodatabase

    • Single database to restore

  • Recommendation: Create all new GDBs with single database model (do not use SDE for database name)

Custom configuration files l.jpg
Custom Configuration Files

  • May specify during post installation

    • ArcSDE will use default values unless custom files are provided

    • Files are located in %SDEHOME%\etc

  • giomgr.defs

    • Configuration of server processes, values written to table SDE_server_config during geodatabase creation

  • dbtune.sde

    • Storage configuration parameters, values written to table SDE_dbtune during geodatabase creation

  • dbinit.sde

    • Service-specific environment variables

    • File is read each time ArcSDE service starts

Upgrade workflow in arcsde 10 l.jpg
Upgrade Workflow in ArcSDE 10

  • Backup database

  • Stop any ArcSDE services

    • Use sdeservice –o list for description of existing services, save output for step 6

  • Uninstall existing ArcSDE software

    • Allow installer to delete services, which must bere-created after software is upgraded

  • Install new version of ArcSDE

  • New for 10: Use ArcCatalog or Python script to run Upgrade Geodatabase

    • This replaces repository upgrade from Post-Installation wizard or sdesetup –o upgrade command

  • Re-create ArcSDE services

Upgrade geodatabase l.jpg
Upgrade Geodatabase

  • Requires direct connect to geodatabase

  • Must be performed by user with DBO permissions

    • For single database model, db_owner can do it

  • Automated prerequisite check determines if geodatabase is upgradable

    • See topic ‘Upgrading a geodatabase in SQL Server’ for details & requirements

      Also see technical workshop: Enterprise Geodatabase Configuration, Upgrade & Direct Connect Strategies

# Process: Upgrade Geodatabase

arcpy.UpgradeGDB_management(<Connection file>,


Arcsde technology client server architecture l.jpg
ArcSDE Technology Client / Server Architecture

Application Server: gsrvr process on server performs work, communicating with RDBMS using SQL

Direct Connect: gsrvr functionality provided by direct connect DLL on client

ArcGIS Client

ArcGIS Client

ArcSDE Technology


Direct Connect DLL

giomgr service

SDE Cmds



Client makes requests of ArcSDE with SDE commands

ArcSDE makes requests of RDBMS with SQL commands

Client Server

(may be same machine)

Using an application server connection l.jpg
Using an Application Server Connection

  • Server name, or IP address of ArcSDE server

  • Service (port number or ArcSDE service name)

  • Database Name \ Instance Name

    • Always specify name of database

Using direct connect l.jpg
Using Direct Connect

  • Connection syntax for Service parameter



  • Specify name of database

    • Otherwise, user’s default database is chosen

Which connection type should be used l.jpg
Which Connection Type Should Be Used?

  • Test to see what works best at your site

    • If client resources are constrained, try Application Server connections

    • If server resources are constrained, try direct connections

    • Both can be used simultaneously

  • Direct Connect advantages

    • Server resources are spared

    • ArcSDE service and gsrvrs are not required

Connection compatibility l.jpg
Connection Compatibility

10 and 9.3 direct connect is backward compatible

  • 10 clients can connect to 9.2 or newer

    • Direct connect drivers provided with 10

    • Older clients cannot direct connect due to major repository table changes

  • 9.3.1: Install ArcGIS Pre 9.3 GDB Direct Connect drivers

    • 9.3.1 clients can connect to 9.0 or newer geodatabases

  • 9.2 SP5 and higher clients can connect to 9.3

    • Need

  • Downloads at

    • Search for “geodatabase direct connect client setup”

Logins users and schemas l.jpg
Logins, Users and Schemas

  • Login – Instance-level authentication to connect

  • User – Database-level authorization to access data

    • A login is associated with a user in each database

  • Schema – Database-level logical grouping of data

    • A user has a default schema in a database





SQL Server Instance

Arcsde data owners user name schema name l.jpg
ArcSDE Data Owners: User Name = Schema Name

Creating a data owner in SQL Server

  • Add a login to the instance

  • Create a user in the database

  • Associate the login to the user in database

  • Create a schema with a matching name in database

  • Assign the schema as the user's default schema



User = “sam”

Schema = “sam”

SQL Server Instance

Creating logins users and schemas l.jpg
Creating Logins, Users and Schemas

USE [master]







USE [vtestdbo]


CREATE USER [fred] FOR LOGIN [fred];











Use SQL Server tools

  • SQL Server Management Studio (SSMS) object browser

  • Transact-SQL statements:


Creating logins users and schemas35 l.jpg
Creating Logins, Users and Schemas

  • Logins can be SQL Server logins

    • Create these using SQL Server tools.

  • Logins can be Windows logins

    • Add to SQL Server with SQL Server tools

    • Can be individual Windows login or Windows Group

    • Watch for strict password policies in SS2008

Permissions l.jpg

Types of permissions

  • Statement permissions – Manipulate database objects (DDL)

    • Assigned by database administrator


  • Object permissions– See or change database objects (DML)

    • Assigned by object owner


User permissions recommendations l.jpg
User Permissions: Recommendations

  • Use role-based security to access data

    • Grant permission to role

    • Give users role membership

    • Users can move in and out of roles without having to modify permission of object

    • Data owner cannot be a role – Must be a user

  • Use built-in database roles for database-wide permissions

    • Db_datareader – SELECT on all tables

    • Db_datawriter – INSERT, UPDATE, DELETE on all tables

    • Database-level EXECUTE permissions to run all stored procedures

Backups l.jpg

  • Prepare a restore plan that can be accomplished in a timeframe that meets your business needs

  • Prepare a backup plan that supports your restore plan

  • Backup on a regular basis

  • Practice restoring from your backup sets onto another machine before you really need to recover lost data

  • Read the SQL Server backup overview:

Compressing the geodatabase l.jpg
Compressing the Geodatabase

  • Removes unnecessary rows from tables related to versioned editing

    • Delta and versioning repository tables

    • Reduces overall retrieval times

  • Improve performance by compressing regularly

    • Nightly or weekly dependent on number of edits

    • Update database statistics after running compress

    • Users connected during a compress will be excluded

Compressing the geodatabase42 l.jpg
Compressing the Geodatabase

  • To compress, use Geoprocessing Tool

    • From ArcCatalog, ArcToolbox or script

  • Must be performed by ArcSDE Administrator

Statistics about indexes and column data l.jpg
Statistics about Indexes and Column Data

  • Statistics indicate index usefulness to Query Optimizer

    • Current statistics lead to increased I/O performance

  • Update statistics after

    • Loading / appending data or substantial edits to data

    • Altering geodatabase schema

    • Before and after geodatabase compress

  • Microsoft technical articles about Statistics

    • SQL Server 2008:

    • SQL Server 2005:

Statistics and arcsde l.jpg
Statistics and ArcSDE

  • Update statistics on user data

    • ArcGIS Analyze tool or Analyze geoprocessing tool

  • Update statistics on ArcSDE repository tables

    • Especially SDE_states, SDE_state_lineages, SDE_mvtables_modified tables

    • Use SQL Server tools for this

Spatial data types46 l.jpg
Spatial Data Types

SQL Server 2008 introduced two spatial storage types

  • Geometry

    • Supports any coordinate system

    • Supports any spatial extent

    • Calculations done on Cartesian (flat Earth) model

  • Geography

    • Supports a set of geographic coordinate systems

    • Maximum extent is global

    • Calculations done on ellipsoid (round Earth) model

Advantages to using spatial types l.jpg
Advantages To Using Spatial Types

  • Additional options from within ArcGIS for display / analysis

    • Query layers, definition queries

  • Server-side processing options

    • Creating, updating features using stored procedures

  • Creating spatial views shareable by all users

  • Making simple features accessible through SQL

  • More information in advanced technical workshop

Creating feature classes with spatial types l.jpg
Creating Feature Classes with Spatial Types

  • Select type using DBTUNE configuration keywords

  • Spatial indexes increase spatial query performance

  • Details provided in the Advanced Administration workshop

  • Register existing spatial tables with the geodatabase to use as full-fledged feature classes

  • Detailed in technical workshop and demo theater presentation on using Spatial Types

Arcsde log files l.jpg
ArcSDE Log Files

  • sde_<service_name>.log

    • Application server connections: time, user, exit status

  • sdedc_SQL Server.log

    • Like sde_<service_name>.log, for direct connect

  • giomgr_esri_sde.log

    • giomgr connections events – gsrvrs spawned

  • upgrade.log

    • Messages generated during ArcSDE upgrade

  • sde_setup.log

    • Messages generated by geodatabase repository setup

      Logs are located in %SDEHOME%\etc folder

Sql server log files l.jpg
SQL Server Log Files

  • SQL Server error log: ERRORLOG

    • Errors from batch commands, backup and restore operations, other server-wide processes

    • New log created on restart or with sp_cycle_errorlog

    • ERRORLOG.n – Previous error logs


  • Default trace

    • When enabled, continuous lightweight tracing

    • Provides troubleshooting data the first time failure occurs

    • Examine trace file with Profiler or T-SQL


Product information l.jpg
Product Information

  • ArcGIS Help in the Resource Center

    • Web-based help on many topics


  • Geodatabase blog

    • What’s new in ArcSDE and the geodatabase


  • ESRI Support Center

    • Contacting support analysts, submitting support requests


  • ESRI ArcSDE / ArcGIS Server User Forums



Related workshops l.jpg
Related Workshops

  • Enterprise Geodatabase Configuration, Upgrade, and Direct Connect Strategies

    • Wednesday 3:15 & Thursday 10:15, Room 32-B

  • Using SQL and Spatial Data Types with the Geodatabase

    • Wednesday 10:15 & Thursday 1:30, Room 4

  • Enterprise Geodatabase – Tips and Tricks

    • Tuesday 1:30 & Thursday 8:30, Room 6-D

  • ArcGIS Server Performance and Scalability – Testing Methodologies

    • Wednesday 10:15 & Thursday 3:15, Room 31-C

Questions l.jpg

Evaluation Forms

Your feedback is important