Administration for sql server an introduction
Download
1 / 56

Administration for SQL Server - An Introduction - PowerPoint PPT Presentation


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.

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

Download Presentation

Administration for SQL Server - An Introduction

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

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

Geodatabase

ArcSDE technology


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

  • http://www.esri.com/software/arcgis/geodatabase/storage-in-an-rdbms.html


Agenda

Installing and Configuring ArcSDE

Connecting to the Geodatabase

Managing Users and Privileges

Geodatabase Maintenance Tasks

Storage Type Comparison

Troubleshooting

Resources


Installing and Configuring ArcSDE


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

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

  • Verify the system requirements are met:

    • Operating system version & patch level

    • DBMS version & patch level

    • See http://resources.arcgis.com/content/arcsde-sql-server-database-requirements

  • 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

  • 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

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%


SDEHOME

Server libraries and executables

Configuration and log files


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

  • 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

      • CREATE TABLE

      • CREATE PROCEDURE

      • CREATE FUNCTION

      • CREATE VIEW


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


Choosing between SDE and DBO Schema


Single Geodatabase Model

ArcSDEservice

  • Each geodatabase in one stand-alone SQL Server database

  • Each geodatabase has own ArcSDE service (optional)

DB

Repository and data


Can have several single database GDBs

SQL Server Instance

5151

5153

5154

DB5

DB3

DB4


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

  • 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


ArcSDE Post-Installation Wizard

Demo


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

  • 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 http://help.ArcGIS.com 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>,

"PREREQUISITE_CHECK", "UPGRADE")


Connecting to the Geodatabase


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

RDBMS

Direct Connect DLL

giomgr service

SDE Cmds

gsrvr

SQL

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

  • 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

  • Connection syntax for Service parameter

    sde:sqlserver:<server_name>

    sde:sqlserver:<server_name>\<instance_name>

  • Specify name of database

    • Otherwise, user’s default database is chosen


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

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 sde92-directconnect.zip

  • Downloads at http://resources.esri.com

    • Search for “geodatabase direct connect client setup”


Users and Privileges


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

Database

Login

User

Schema

SQL Server Instance


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

Database

Login

User = “sam”

Schema = “sam”

SQL Server Instance


Creating Logins, Users and Schemas

USE [master]

CREATE LOGIN [fred] WITH

PASSWORD=N'password4fred',

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF;

GO

USE [vtestdbo]

GO

CREATE USER [fred] FOR LOGIN [fred];

GO

CREATE SCHEMA [fred]

AUTHORIZATION [fred];

GO

ALTER USER [fred]

WITH DEFAULT_SCHEMA=[fred];

GO

GRANT CREATE PROCEDURE, CREATE TABLE,

CREATE VIEW, REFERENCES TO [fred];

GO

Use SQL Server tools

  • SQL Server Management Studio (SSMS) object browser

  • Transact-SQL statements:

    • CREATE LOGIN, CREATE USER, CREATE SCHEMA, ALTER USER, GRANT


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

Types of permissions

  • Statement permissions – Manipulate database objects (DDL)

    • Assigned by database administrator

    • CREATE TABLE, CREATE PROCEDURE, CREATE VIEW etc.

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

    • Assigned by object owner

    • SELECT, INSERT, UPDATE, DELETE, EXECUTE


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


Managing Users and Permissions

Demo


Geodatabase Maintenance


Backups

  • 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: http://msdn.microsoft.com/en-us/library/ms175477.aspx


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 Geodatabase

  • To compress, use Geoprocessing Tool

    • From ArcCatalog, ArcToolbox or script

  • Must be performed by ArcSDE Administrator


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: http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx

    • SQL Server 2005: http://technet.microsoft.com/en-us/library/cc966419.aspx


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 Types


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

  • 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

  • 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


Troubleshooting


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

  • 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

    • http://msdn.microsoft.com/en-us/library/ms187885.aspx

  • Default trace

    • When enabled, continuous lightweight tracing

    • Provides troubleshooting data the first time failure occurs

    • Examine trace file with Profiler or T-SQL

    • http://msdn.microsoft.com/en-us/library/ms175513.aspx


Resources


Product Information

  • ArcGIS Help in the ArcGIS.com Resource Center

    • Web-based help on many topics

    • http://resources.arcgis.com/content/web-based-help

  • Geodatabase blog

    • What’s new in ArcSDE and the geodatabase

    • http://blogs.esri.com/Dev/blogs/geodatabase/default.aspx

  • ESRI Support Center

    • Contacting support analysts, submitting support requests

    • http://support.esri.com/

  • ESRI ArcSDE / ArcGIS Server User Forums

    • http://forums.arcgis.com/forums/32-Geodatabase-amp-ArcSDE

    • http://forums.arcgis.com/forums/8-ArcGIS-Server-General


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?

Evaluation Forms

Your feedback is important


ad
  • Login