planning: enterprise geodatabase solutions john alsup christopher zent
Post on 20-Dec-2015
227 Views
Preview:
TRANSCRIPT
Planning: Enterprise Geodatabase SolutionsPlanning: Enterprise Geodatabase Solutions
John AlsupJohn Alsup
Christopher ZentChristopher Zent
AgendaAgenda
• OverviewOverview• Database Design Database Design • Data MaintenanceData Maintenance• Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 22
What is a Geodatabase?What is a Geodatabase?
• A database or file structure used to store, query and A database or file structure used to store, query and manipulate spatial data.manipulate spatial data.
• Data and functionalityData and functionality
• Three types:Three types:
– File geodatabaseFile geodatabase– Personal geodatabasePersonal geodatabase– ArcSDE geodatabaseArcSDE geodatabase
• DB2DB2
• InformixInformix
• OracleOracle
• PostgreSQLPostgreSQL
• SQL Server SQL Server
2009 Technical Workshop2009 Technical Workshop 33
ImagesImages
VectorsVectors
TopologyTopology
NetworksNetworks
TerrainTerrain
SurveysSurveys
CADCADDrawingsDrawings
AddressesAddresses
27 Main St.27 Main St.
AttributesAttributes
ABCABC
3D Objects3D Objects
107’107’
DimensionsDimensions
AnnotationAnnotation
Enterprise GISEnterprise GIS
• GIS technology regarded by users and IT as key to GIS technology regarded by users and IT as key to business operations business operations
– May be considered mission criticalMay be considered mission critical
• Mainstream IT – deployed and managed like any other Mainstream IT – deployed and managed like any other IT systemIT system
– Architecture, Interfaces, Development tools, Deployment Architecture, Interfaces, Development tools, Deployment strategies, Standardsstrategies, Standards
• Integrated with other enterprise systems Integrated with other enterprise systems
• Requires a higher level of planning, integration, testing Requires a higher level of planning, integration, testing and supportand support
2009 Technical Workshop2009 Technical Workshop 44
What is an Enterprise Geodatabase?What is an Enterprise Geodatabase?
• DataData– Serves data promptly and efficientlyServes data promptly and efficiently– Supports multiple users and departments concurrentlySupports multiple users and departments concurrently– Provides seamless dataProvides seamless data– Centralized data managementCentralized data management– Data integrityData integrity
• FunctionalityFunctionality– SQL supportSQL support– Collaborative editing, and long transactionsCollaborative editing, and long transactions– Quality control and quality assuranceQuality control and quality assurance– Infrastructure for distributing and replicating dataInfrastructure for distributing and replicating data– Integrates spatial and business data with other systemsIntegrates spatial and business data with other systems– Leverages existing GIS and IT skills and resources Leverages existing GIS and IT skills and resources
2009 Technical Workshop2009 Technical Workshop 55
ArcObjectsArcObjectsEnterprise GeodatabaseEnterprise Geodatabase
2009 Technical Workshop2009 Technical Workshop 66
Geodatabase Geodatabase System SchemaSystem Schema
GDB_ tablesGDB_ tablesArcSDE tablesArcSDE tables
Miscellaneous TablesMiscellaneous TablesLog filesLog files
SearchingSearchingSpatial ProcessingSpatial Processing
TempTemp
Geometric Network TablesGeometric Network Tables
Topology tablesTopology tables
Raster tablesRaster tables
Business tablesBusiness tablesFeature tablesFeature tables
Spatial Index tablesSpatial Index tables
A and D tablesA and D tables
User User SchemasSchemas
Non-Spatial Non-Spatial Business TableBusiness Table
ArcMapArcMap
ArcGIS ArcGIS ServerServer
ArcCatalogArcCatalog
ArcIMSArcIMS
ArcGIS ArcGIS EngineEngine
ArcGISArcGISExplorer*Explorer*
Native SQL*Native SQL*
Organizational GIS ConfigurationsOrganizational GIS Configurations
2009 Technical Workshop2009 Technical Workshop 77
Department File Servers
Distributed Client/ServerDepartmental GIS
Parks
Utilities
ArcGIS Desktops
Assessor WAN
ArcSDE
IT
Centralized Data Warehouse
Data WarehouseDepartmental GIS Operations
Centralized Data Sharing
WAN
ArcGIS Desktops
Parks
Utilities
Assessor
ArcSDE
ITCentralized Database
Centralized DatabaseEnterprise GIS Operations
Centralized Data Administration
WAN
ArcGIS Desktops, Terminals and Browsers
Parks
Utilities
Assessor
ArcGIS Server/WTS(server consolidation)
Why Plan an Enterprise Geodatabase? Why Plan an Enterprise Geodatabase?
• Some key reasons:Some key reasons:
– Foundation for enterprise-wide use of GIS. Foundation for enterprise-wide use of GIS.
– Geodatabase projects are complexGeodatabase projects are complex
– Enterprise geodatabases and GIS application design Enterprise geodatabases and GIS application design requires diligent alignmentrequires diligent alignment
– Large geodatabase projects span organizational groups Large geodatabase projects span organizational groups and disciplinesand disciplines
– Impacts almost every part of an enterprise GIS solutionImpacts almost every part of an enterprise GIS solution
2009 Technical Workshop2009 Technical Workshop 88
Spatial data is a key component of an enterprise GIS architecture . . . Spatial data is a key component of an enterprise GIS architecture . . . . . . delivery of spatial data must be fast, and this requires planning.. . . delivery of spatial data must be fast, and this requires planning.
Geodatabase Project ScalesGeodatabase Project Scales
• Larger Multi-phased ApproachLarger Multi-phased Approach– Elaborate, large databasesElaborate, large databases– Custom applicationsCustom applications– Large user baseLarge user base– Potentially outsourced, dedicated project managementPotentially outsourced, dedicated project management
• Lighter Workgroup ApproachLighter Workgroup Approach– Evolve the geodatabase, gradually upgrade data and applicationsEvolve the geodatabase, gradually upgrade data and applications– COTS application functionality where possibleCOTS application functionality where possible– Built in-house, part-time project managementBuilt in-house, part-time project management
All enterprise geodatabase projects require planning …All enterprise geodatabase projects require planning …
2009 Technical Workshop2009 Technical Workshop 99
AgendaAgenda
• OverviewOverview• Database Design Database Design • Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• Data MaintenanceData Maintenance• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 1010
Some Considerations on DesignSome Considerations on Design
• Core enterprise GIS design taskCore enterprise GIS design task
• Foundation and blueprint for the capabilities of the GISFoundation and blueprint for the capabilities of the GIS
• Development of the “data model”Development of the “data model”
• Data models sets the limits for application functionalityData models sets the limits for application functionality
• Data maintenance is expensiveData maintenance is expensive
• PerformancePerformance
Geodatabase design impacts almost every area of the enterprise GIS...Geodatabase design impacts almost every area of the enterprise GIS...
11112009 Technical Workshop2009 Technical Workshop
Challenges and RisksChallenges and Risks
• Application development has a critical dependencyApplication development has a critical dependency
• Normalization in the data modelNormalization in the data model
• Updating the model “downstream” is expensiveUpdating the model “downstream” is expensive
• Thorough review of model among teamsThorough review of model among teams
• Optimizing for publication and maintenanceOptimizing for publication and maintenance
2009 Technical Workshop2009 Technical Workshop 1212
Geodatabase DesignGeodatabase Design
• Elements of good geodatabase designElements of good geodatabase design
– Data model reflects requirementsData model reflects requirements
– Scalable Scalable
– Avoids redundant storage of data itemsAvoids redundant storage of data items
– Efficient access to dataEfficient access to data
– Maintains data integrity over timeMaintains data integrity over time
– Clearly documentedClearly documented
– Provides for analysis and behaviorProvides for analysis and behavior
13132009 Technical Workshop2009 Technical Workshop
Data Modeling MethodologyData Modeling Methodology
Conceptual Design Tasks:• Identify business needs• Identify thematic layers• Identify required
applications• Leverage data model
template• Document
Conceptual Conceptual ModelModel
Logical Logical ModelModel
PhysicalPhysicalModelModel
Three StagesThree Stages
Logical Design Tasks:Logical Design Tasks:• Define tabular database Define tabular database
structurestructure• Define relationshipsDefine relationships• Determine spatial propertiesDetermine spatial properties• DocumentDocument
Physical Design Tasks:• Create and implement model
design • Generate physical schema in
the DBMS• Testing and validation• Document
14142009 Technical Workshop2009 Technical Workshop
Conceptual ModelConceptual Model
• Identify and DocumentIdentify and Document::
• Business needs - requirementsBusiness needs - requirements
• Thematic layersThematic layers
• Required applications and system interfacesRequired applications and system interfaces
• Leverage existing model templatesLeverage existing model templates• Pre-designed schema of data objectsPre-designed schema of data objects
• Best practicesBest practices
15152009 Technical Workshop2009 Technical Workshop
ArcGIS Data Models Web site:ArcGIS Data Models Web site: http://support.esri.com/datamodelshttp://support.esri.com/datamodels
• Over 25 industry-specific data Over 25 industry-specific data modelsmodels
• Conceptual and logical diagrams, Conceptual and logical diagrams, sample geodatabase schemassample geodatabase schemas
– Feature and object classesFeature and object classes– AttributesAttributes– RelationshipsRelationships– Network functionalityNetwork functionality– ValidationsValidations
• Case studiesCase studies
• Tips and Tricks documentsTips and Tricks documents
• Developed and maintained by user Developed and maintained by user and industry communitiesand industry communities
16162009 Technical Workshop2009 Technical Workshop
• Refine conceptual model based on documented Refine conceptual model based on documented requirementsrequirements
• Define and clarify all feature classes, tables, attributes and Define and clarify all feature classes, tables, attributes and relationship classesrelationship classes
• Use subtypes to control object behavior Use subtypes to control object behavior
• Attribute domains and complex codingAttribute domains and complex coding
• Define network and topological properties and rulesDefine network and topological properties and rules
• Define spatial reference propertiesDefine spatial reference properties
• Map placement considerationsMap placement considerations
Logical Model DesignLogical Model Design
17172009 Technical Workshop2009 Technical Workshop
Logical Model DesignLogical Model Design
• Identification of database rules, categories and data Identification of database rules, categories and data integrityintegrity
• Complex data types, network connectivity and Complex data types, network connectivity and topologytopology
• DocumentationDocumentation– DiagramsDiagrams
– Data dictionaryData dictionary
– Source data mappingSource data mapping
– Naming conventionsNaming conventions
18182009 Technical Workshop2009 Technical Workshop
Important ConsiderationsImportant Considerations
• RDBMS Geometry Storage FormatRDBMS Geometry Storage Format
– Key design decision with functionality and performance trade-offsKey design decision with functionality and performance trade-offs
• ST_Geometry is the defaultST_Geometry is the default
• Some are platform-specific – e.g., Oracle SDOSome are platform-specific – e.g., Oracle SDO
• External systems and interfaces – key for enterprise GISExternal systems and interfaces – key for enterprise GIS– CRM, WMS, Financials, Reporting CRM, WMS, Financials, Reporting
– Number of interfaces depends upon the organizationNumber of interfaces depends upon the organization
– Consider data sharing - field data types, naming and lengthConsider data sharing - field data types, naming and length
19192009 Technical Workshop2009 Technical Workshop
Mixed RDBMS EnvironmentsMixed RDBMS Environments
• Some things to considerSome things to consider– Field Names, length and Field Names, length and
keywordskeywords– Field Data Types and Field Data Types and
LengthsLengths– Database behaviorsDatabase behaviors
2009 Technical Workshop2009 Technical Workshop 2020
Oracle
IT
SQL Express
SQL Enterprise
WAN
Parks
Utilities
Assessor
DB2
Physical Model DesignPhysical Model Design
• Implementing the physical geodatabase - prototype, Implementing the physical geodatabase - prototype, test, review, and refinetest, review, and refine
• Documenting the design for distribution and Documenting the design for distribution and efficient updatingefficient updating
• Test, refine and tune data model design for Test, refine and tune data model design for deploymentdeployment
21212009 Technical Workshop2009 Technical Workshop
Creating StructureCreating Structure
• Look to existing toolsLook to existing tools– CASE and UML tools – Visio, Rational CASE and UML tools – Visio, Rational
Rose, etc.Rose, etc.
– Other tools (some free) and samples may Other tools (some free) and samples may work depending on approachwork depending on approach
• Inheritance, re-use of objects through Inheritance, re-use of objects through abstract and concrete classesabstract and concrete classes
Physical Model
XMI(XML
Design)
Geodatabase
22222009 Technical Workshop2009 Technical Workshop
Data Modeling ToolsData Modeling Tools
• VisioVisio• Rational RoseRational Rose• Free ESRI Tools on ArcScripts:Free ESRI Tools on ArcScripts:
• ArcGIS DiagrammerArcGIS Diagrammer• GDB Xray GDB Xray • Geodatabase DiagrammerGeodatabase Diagrammer• Geodatabase DesignerGeodatabase Designer
2009 Technical Workshop2009 Technical Workshop 2323
Free Tools are not supportedFree Tools are not supported
Testing and RefiningTesting and Refining
• Small pilot data migration with sample dataSmall pilot data migration with sample data
• Application testing – Test workflowsApplication testing – Test workflows– FunctionalityFunctionality– PerformancePerformance– Flexibility and consistencyFlexibility and consistency
• Team review and demonstrationTeam review and demonstration– Show how tasks are performed using GISShow how tasks are performed using GIS– Show maps, reports, online demosShow maps, reports, online demos
24242009 Technical Workshop2009 Technical Workshop
Data PlanningData Planning
• Migration and Conversion Migration and Conversion – Migration deals with moving existing geospatial data between Migration deals with moving existing geospatial data between
different GIS environments or platforms different GIS environments or platforms
– Conversion refers to development of new data by creating new Conversion refers to development of new data by creating new digital geospatial datadigital geospatial data
– Conversion is typically more significant and costly than migrationConversion is typically more significant and costly than migration
• Data procurementData procurement– LandbaseLandbase– ImageryImagery
• Data loadingData loading– Tools – In-house or outsourcedTools – In-house or outsourced– Procedures Procedures
25252009 Technical Workshop2009 Technical Workshop
AgendaAgenda
• OverviewOverview• Database Design Database Design • Data MaintenanceData Maintenance• Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 2626
Overview of Data MaintenanceOverview of Data Maintenance
• Plan and manage the maintenance workflow in the Plan and manage the maintenance workflow in the geodatabasegeodatabase
• Key Tasks Key Tasks – Analyze and build on business process requirementsAnalyze and build on business process requirements– QA/QCQA/QC– Design your maintenance strategyDesign your maintenance strategy– Plan for versioningPlan for versioning– Define maintenance workflowsDefine maintenance workflows
27272009 Technical Workshop2009 Technical Workshop
Consider QA / QCConsider QA / QC
• Ensure data is captured, loaded and maintained Ensure data is captured, loaded and maintained accuratelyaccurately
• Quality AssuranceQuality Assurance– Review data to discover errors and perform data cleaning Review data to discover errors and perform data cleaning
activities to improve quality.activities to improve quality.
• Quality Control Quality Control – Ensure data products are designed to meet or exceed data Ensure data products are designed to meet or exceed data
requirements.requirements.
• QA/QC PlanQA/QC Plan– VersioningVersioning
– Manual and automated procedures Manual and automated procedures
– ValidationsValidations
28282009 Technical Workshop2009 Technical Workshop
Versioning and Multiuser GeodatabaseVersioning and Multiuser Geodatabase
• Defining versioning specifications and workflows:Defining versioning specifications and workflows:
– Versioning structureVersioning structure
– Reconcile, post, compress regimesReconcile, post, compress regimes
– Edit volumes, version durationsEdit volumes, version durations
2009 Technical Workshop2009 Technical Workshop 2929UC 2006 Tech SessionUC 2006 Tech Session 2929
DEFAULT
DEFAULTDEFAULT
Non-Versioned EditingVersioned Editing
All impact performance…All impact performance…
Considerations for VersionsConsiderations for Versions
• Decide how versions will be handled:Decide how versions will be handled:– LifespanLifespan– ReconcilingReconciling– Conflict managementConflict management– Naming conventionsNaming conventions– StructureStructure
• Staging or QC version between user versions and DEFAULTStaging or QC version between user versions and DEFAULT• SecuritySecurity• Versions for groups or departmentsVersions for groups or departments
• Workflow Management Systems for Handling VersionsWorkflow Management Systems for Handling Versions– Can provide workflows and efficiencies , some examples:Can provide workflows and efficiencies , some examples:
• Job Tracking for ArcGIS (JTX)Job Tracking for ArcGIS (JTX)• ArcFM and Network Engineer – In the Utility AreaArcFM and Network Engineer – In the Utility Area
2009 Technical Workshop2009 Technical Workshop 3030
Data Maintenance and Editing WorkflowsData Maintenance and Editing Workflows
• A data maintenance strategy is essential for A data maintenance strategy is essential for consistent data qualityconsistent data quality
– QA/QCQA/QC– Versioning strategyVersioning strategy– Editing workflowsEditing workflows
• Editing Workflows are part of the business modelEditing Workflows are part of the business model
– Business needsBusiness needs– Data and schema changesData and schema changes– ESRI and non-ESRI client accessESRI and non-ESRI client access
2009 Technical Workshop2009 Technical Workshop 3131
User WorkflowsUser Workflows
• Document with Use CasesDocument with Use Cases
• A description of the task you need to perform:A description of the task you need to perform:
• ““Add new parcel”, “Update new asset”Add new parcel”, “Update new asset”
• Evaluate business needs:Evaluate business needs:
– What data needs to be edited and in what orderWhat data needs to be edited and in what order
– Tracking of data changesTracking of data changes
– Conflict detection and resolutionConflict detection and resolution
• Security – user roles, etc. Security – user roles, etc.
• QA/QC steps – enforced through application or QA/QC steps – enforced through application or databasedatabase
2009 Technical Workshop2009 Technical Workshop 3232
“Add new service”
Use case
Version update
Geodatabase
AgendaAgenda
• OverviewOverview• Database Design Database Design • Data MaintenanceData Maintenance• Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 3333
Key DecisionsKey Decisions
• System AvailabilitySystem Availability• Connectivity and AccessConnectivity and Access• Database ArchitectureDatabase Architecture• Replication and ClusteringReplication and Clustering• StorageStorage• VirtualizationVirtualization
2009 Technical Workshop2009 Technical Workshop 3434
System AvailabilitySystem Availability
–Primary availability Primary availability hourshours• 24x7/36524x7/365• Epic “Five 9’s”?Epic “Five 9’s”?
–Fail-over optionsFail-over options• Manual –vs.– automatedManual –vs.– automated
–Maintenance windowsMaintenance windows• CompressCompress• Reconcile serviceReconcile service• Posting servicePosting service• Database schema changesDatabase schema changes
2009 Technical Workshop2009 Technical Workshop 3535
Number of 9sNumber of 9s Percentage Percentage AvailabilityAvailability
DowntimeDowntime
11 98.9%98.9% 3 Days, 18 3 Days, 18 Hours, 20 Hours, 20 minutesminutes
22 99.0%99.0% 3 Days, 15 3 Days, 15 hours, 36 hours, 36 minutesminutes
33 99.9%99.9% 8 hours, 46 8 hours, 46 minutesminutes
44 99.99%99.99% 53 minutes53 minutes
55 99.999%99.999% 5 minutes5 minutes
Geodatabase Connection ArchitecturesGeodatabase Connection Architectures
2009 Technical Workshop2009 Technical Workshop 3636
`` `
ArcSDE Libraries
SQL QueriesSpatial Data types
Direct Connect ArcSDE Connect(“Application Server”)
ArcSDE Libraries
Geodatabase(Database Server)
Why connection architecture is importantWhy connection architecture is important
• Affects system resources on server sideAffects system resources on server side–Direct Connect uses less on the database serverDirect Connect uses less on the database server
• SQL AccessSQL Access–May help you decide on storage formatsMay help you decide on storage formats–Use database views when using SQL AccessUse database views when using SQL Access
• Gives the administrators more control of what is accessedGives the administrators more control of what is accessed• More control over how updates are performedMore control over how updates are performed• Per-defined queriesPer-defined queries
2009 Technical Workshop2009 Technical Workshop 3737
Data AccessData Access
• Essential TasksEssential Tasks– Identify non-GIS application needsIdentify non-GIS application needs
• GIS attribute dataGIS attribute data
• Business reports based on GIS data or processingBusiness reports based on GIS data or processing
• Reading GIS Geometry dataReading GIS Geometry data
• Will updates of attribute data occurWill updates of attribute data occur
• Will updates of geometry occurWill updates of geometry occur
– Define and configure the application interfaces based on Define and configure the application interfaces based on application needsapplication needs• Network configuration (host and ports)Network configuration (host and ports)
• Client libraries (e.g. SQLNet, Java libs, ArcSDE client libs, Client libraries (e.g. SQLNet, Java libs, ArcSDE client libs, etc.) etc.)
2009 Technical Workshop2009 Technical Workshop 3838
Database ArchitectureDatabase Architecture
• Multiple instances on the same physical hardwareMultiple instances on the same physical hardware– They are competing for all system resourcesThey are competing for all system resources– All background process duplicated (wasteful)All background process duplicated (wasteful)– One bad apple can spool the bushelOne bad apple can spool the bushel
• One runaway process can affect all databasesOne runaway process can affect all databases
– Better than virtualization!Better than virtualization!
• Volume of dataVolume of data– If indexes are used properly, this should not be an issueIf indexes are used properly, this should not be an issue
• Schemas and data ownershipSchemas and data ownership• AccessAccess
– Affects temporary and log tables Affects temporary and log tables
2009 Technical Workshop2009 Technical Workshop 3939
Network ConsiderationsNetwork Considerations
• BandwidthBandwidth• LatencyLatency• Number of Routers/Switches/FirewallsNumber of Routers/Switches/Firewalls• How much data will need to be pushed through the How much data will need to be pushed through the
networknetwork– CITRIX ClientCITRIX Client– Thick ClientThick Client– ReplicationReplication
• SecuritySecurity
2009 Technical Workshop2009 Technical Workshop 4040
InfrastructureInfrastructure
• Building the hardware and software infrastructure Building the hardware and software infrastructure for the Geodatabase instance, and all the related for the Geodatabase instance, and all the related data servicesdata services
• Essential TasksEssential Tasks– Hardware SizingHardware Sizing
• Identify hardware and software requirements based functional Identify hardware and software requirements based functional and system needsand system needs– Development and testDevelopment and test– ProductionProduction– LicensingLicensing– System capacity and growthSystem capacity and growth– Storage needsStorage needs– Host CPU, RAMHost CPU, RAM– Network throughputNetwork throughput
2009 Technical Workshop2009 Technical Workshop 4141
ClusteringClustering
•Why use a ClusterWhy use a Cluster–Fault ToleranceFault Tolerance–Load balancingLoad balancing–ScalabilityScalability
•Why not to use a ClusterWhy not to use a Cluster–License CostLicense Cost–Does not protect from disk failureDoes not protect from disk failure–Does not protect from Network failuresDoes not protect from Network failures
2009 Technical Workshop2009 Technical Workshop 4242
Data ReplicationData Replication
•Data replicationData replication–Why replicate? Why replicate?
–RecoveryRecovery–MobilityMobility–AccessibilityAccessibility–Performance/load balancingPerformance/load balancing–ScalabilityScalability–Network load reduction (be careful here)Network load reduction (be careful here)–Network isolation during a disasterNetwork isolation during a disaster
–Key Question!Key Question!• Will edits need to be performed on replicas?Will edits need to be performed on replicas?
2009 Technical Workshop2009 Technical Workshop 4343
Data ReplicationData Replication
• Essential TasksEssential Tasks– RequirementsRequirements
• Identify replication uses and benefitsIdentify replication uses and benefits• Identify data to be replicatedIdentify data to be replicated• Identify QoS requirementsIdentify QoS requirements
– how fast should changes replicate?how fast should changes replicate?– how frequent is acceptable?how frequent is acceptable?
– Analysis and DesignAnalysis and Design• Define replication architectureDefine replication architecture
– ImplementationImplementation• Prototype and test architecture (crucial)Prototype and test architecture (crucial)
– Key data modificationsKey data modifications– Typical and peak loadsTypical and peak loads
• Procure, install, and configure replication architectureProcure, install, and configure replication architecture
– TestTest
• Test replication accuracy and responsivenessTest replication accuracy and responsiveness
2009 Technical Workshop2009 Technical Workshop 4444
Data ReplicationData Replication
• Data replicationData replication–Review replication optionsReview replication options
• Device level, OS level, DBMS level, GeodatabaseDevice level, OS level, DBMS level, Geodatabase• RDBMS TypesRDBMS Types
– SnapshotSnapshot
– Multi-master/mergeMulti-master/merge
– TransactionalTransactional
– HybridHybrid
– Cannot edit using RDMBS replicas, only parent can be editedCannot edit using RDMBS replicas, only parent can be edited
2009 Technical Workshop2009 Technical Workshop 4545
Geodatabase ReplicationGeodatabase Replication
• Decide what is going to be replicatedDecide what is going to be replicated– Specific feature classes and feature datasetsSpecific feature classes and feature datasets
• Decide on data to be replicatedDecide on data to be replicated– CompleteComplete– By areaBy area– By attributeBy attribute
• Decide on type of replicationDecide on type of replication– Checkout/checkinCheckout/checkin– One wayOne way
• VersionedVersioned• Non-versionedNon-versioned
– Two wayTwo way
• How to perform synchronizationHow to perform synchronization– On line or off lineOn line or off line
2009 Technical Workshop2009 Technical Workshop 4646
Data Replication cont’dData Replication cont’d
• DeliverablesDeliverables– Document requirements and designDocument requirements and design– Full cycle of prototypingFull cycle of prototyping
• Procure and configure replication software/hardwareProcure and configure replication software/hardware
• Build master databaseBuild master database
• Modify data, and measure success and performance of replica Modify data, and measure success and performance of replica
– Configured and tested replication systemConfigured and tested replication system
2009 Technical Workshop2009 Technical Workshop 4747
AgendaAgenda
• OverviewOverview• Database Design Database Design • Data MaintenanceData Maintenance• Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 4848
Data SecurityData Security
• Making the geodatabase instance secure from Making the geodatabase instance secure from unauthorized usersunauthorized users
• Essential TasksEssential Tasks– Review DBMS authentication schemesReview DBMS authentication schemes
• Integrated with OS and network domain securityIntegrated with OS and network domain security
• Standard DBMS securityStandard DBMS security
• Mixed modeMixed mode
• Users and rolesUsers and roles
– Identify anticipated users (GIS and business applications), Identify anticipated users (GIS and business applications), and accessible objectsand accessible objects
2009 Technical Workshop2009 Technical Workshop 4949
Data SecurityData Security
• Essential Tasks cont’dEssential Tasks cont’d– Define and create DBMS usersDefine and create DBMS users– Define and create DBMS rolesDefine and create DBMS roles– Create security monitorCreate security monitor– Update DBMS security patchesUpdate DBMS security patches– Does network traffic need to be encryptedDoes network traffic need to be encrypted
• DeliverablesDeliverables– Document of users, roles, and object access.Document of users, roles, and object access.– Configured users and rolesConfigured users and roles
2009 Technical Workshop2009 Technical Workshop 5050
Data Security cont’dData Security cont’d
• Challenges and RisksChallenges and Risks– Sharing a DBMS login Sharing a DBMS login
• SDE_logfile contention pointSDE_logfile contention point
• Difficult to identify which process belongs to which userDifficult to identify which process belongs to which user
• Security Security
– Access to too many objects can impact performanceAccess to too many objects can impact performance
• NoteNote– It’s easier to grant access to users later, than it is to It’s easier to grant access to users later, than it is to
revoke later.revoke later.
2009 Technical Workshop2009 Technical Workshop 5151
Some key pointsSome key points
• Permissions are set on objects at the root level in the Permissions are set on objects at the root level in the GeodatabaseGeodatabase
– All members of a Feature Dataset must share the same permissoinsAll members of a Feature Dataset must share the same permissoins
• Row Level SecurityRow Level Security– Also called Fine Grained AccessAlso called Fine Grained Access
– Does not work well in editing environmentsDoes not work well in editing environments
– Big problems for editing Geometric Networks and Network DatasetsBig problems for editing Geometric Networks and Network Datasets• Data corruptionData corruption• Data lossData loss
– Difficult to implement and manageDifficult to implement and manage
• Some security measures are better implemented at the application Some security measures are better implemented at the application levellevel
– Data exportData export
– Row LevelRow Level
2009 Technical Workshop2009 Technical Workshop 5252
AgendaAgenda
• OverviewOverview• Logical Design Logical Design • Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• Data MaintenanceData Maintenance• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 5353
Database Maintenance Database Maintenance
• Common TasksCommon Tasks–BackupsBackups–StatisticsStatistics–FragmentationFragmentation–CompressCompress–Batch ReconcileBatch Reconcile
2009 Technical Workshop2009 Technical Workshop 5454
Data Backup & Recovery ConsiderationsData Backup & Recovery Considerations
–Key considerationsKey considerations• System availabilitySystem availability• Backup sizesBackup sizes• Speed of recoverySpeed of recovery• TransportabilityTransportability• Acceptable loss of editsAcceptable loss of edits• Consistency Consistency • Affects on performanceAffects on performance
2009 Technical Workshop2009 Technical Workshop 5555
Database StatisticsDatabase Statistics
• All RDBMS optimizers use statistics (metadata) All RDBMS optimizers use statistics (metadata) to develop execution planto develop execution plan
• Many DBA’s want to estimate as opposed to Many DBA’s want to estimate as opposed to computecompute
– QuickerQuicker– Estimating only works well if data is uniformEstimating only works well if data is uniform
• Better statistics, better execution planBetter statistics, better execution plan• Key questionsKey questions
– How long will it takeHow long will it take– When can it be performedWhen can it be performed– Can it be down while users are connectedCan it be down while users are connected
2009 Technical Workshop2009 Technical Workshop 5656
FragmentationFragmentation
–Index FragmentationIndex Fragmentation• When to rebuild?When to rebuild?
–One of the great mysteries of lifeOne of the great mysteries of life• How to rebuild (Oracle bug in rebuild command)How to rebuild (Oracle bug in rebuild command)
–On lineOn line–Off line (Saves redo log)Off line (Saves redo log)–Drop and recreateDrop and recreate
–Table FragmentationTable Fragmentation• Rarely causes problemsRarely causes problems
• Only a concern when reading a large number of blocks Only a concern when reading a large number of blocks
2009 Technical Workshop2009 Technical Workshop 5757
Database MonitoringDatabase Monitoring
• Monitoring geodatabase componentsMonitoring geodatabase components• Version/Stat infoVersion/Stat info
– Replication versionsReplication versions– State infoState info– State lineage infoState lineage info– Number of featuresNumber of features– Size of delta tablesSize of delta tables
• Data access timeData access time– Monitor performance of queries, especially spatialMonitor performance of queries, especially spatial
• Spa-stats or spatial statsSpa-stats or spatial stats
2009 Technical Workshop2009 Technical Workshop 5858
AgendaAgenda
• OverviewOverview• Logical Design Logical Design • Infrastructure Design and Data DistributionInfrastructure Design and Data Distribution• Data MaintenanceData Maintenance• SecuritySecurity• Database MaintenanceDatabase Maintenance• PerformancePerformance
2009 Technical Workshop2009 Technical Workshop 5959
PerformancePerformance
• DeliverablesDeliverables– Document requirementsDocument requirements– Execute performance, analyze, optimize iterationsExecute performance, analyze, optimize iterations– Tuning DBMS, tuning applicationTuning DBMS, tuning application– Scaling strategyScaling strategy
• Scale out vs. upScale out vs. up
• Challenges and RisksChallenges and Risks– Data too granular Data too granular
• Group features Group features
– Overloading your applicationOverloading your application• Overloading application table of contentsOverloading application table of contents
• Building batch-like operations into applicationBuilding batch-like operations into application
– ... many others (please attend the performance related GDB ... many others (please attend the performance related GDB sessions at the conference. This is an important topic).sessions at the conference. This is an important topic).
2009 Technical Workshop2009 Technical Workshop 6060
Performance ObjectivesPerformance Objectives
• Define performance metricsDefine performance metrics–Identify key tasksIdentify key tasks–Establish initial goalEstablish initial goal
• Proto-type databaseProto-type database–Reasonable sample databaseReasonable sample database–Spatial densitySpatial density–Model behaviorModel behavior–Spatial reference and boundsSpatial reference and bounds
2009 Technical Workshop2009 Technical Workshop 6161
Data Performance and ScalabilityData Performance and Scalability
• Measure, assess, and optimize the performance of key Measure, assess, and optimize the performance of key functionality using the geodatabase instance.functionality using the geodatabase instance.
• Essential TasksEssential Tasks– Review anticipated data loadsReview anticipated data loads
• Volume (data file growth management)Volume (data file growth management)• Volatility (storage partitioning)Volatility (storage partitioning)
– Identify key business transactionsIdentify key business transactions• Maintenance operationsMaintenance operations• Publication operationsPublication operations
– Identify performance requirements for key business transactionsIdentify performance requirements for key business transactions• Response timeResponse time• Initial and scheduled user loadsInitial and scheduled user loads• ThroughputThroughput• TestingTesting
2009 Technical Workshop2009 Technical Workshop 6262
PerformancePerformance
• Trace each processTrace each process– Capture traces for each work functionCapture traces for each work function– Look for large volumes of records returned (Increased fetches)Look for large volumes of records returned (Increased fetches)– Look for frequently repeated queriesLook for frequently repeated queries– Consider using a Schema CacheConsider using a Schema Cache– Consider using a Map cacheConsider using a Map cache
• Reduces queries for same dataReduces queries for same data
• Affects snapping/editingAffects snapping/editing
2009 Technical Workshop2009 Technical Workshop 6363
PerformancePerformance
• Geodatabase designs Geodatabase designs – Potential performance issues related to database designPotential performance issues related to database design
• RelationshipsRelationships– Both # and TypeBoth # and Type– Schema Cache can help reduce performance costSchema Cache can help reduce performance cost
• Size of data stored in recordsSize of data stored in records
• Projection on the flyProjection on the fly
• Number of records returned in a queryNumber of records returned in a query
• Density of data, both number of features and number of verticesDensity of data, both number of features and number of vertices
• Application designApplication design– Can have a significant affect on performance; e.g., Can have a significant affect on performance; e.g.,
• Frequently opening a tableFrequently opening a table
• Retrieving features one at a time vs bulk Retrieving features one at a time vs bulk
2009 Technical Workshop2009 Technical Workshop 6464
Question & AnswerQuestion & Answer
Contact info:Contact info:
John AlsupJohn Alsupjalsup@esri.com
Christopher ZentChristopher Zentczent@esri.com
2009 Technical Workshop2009 Technical Workshop 6565
top related