Transcript Automating ArcSDE Geodatabase Maintenance

AUTOMATING ARCSDE
GEODATABASE MAINTENANCE
2011 IGIC Conference
Muncie, Indiana
Presenter: Trent Pell, GISP
HISTORY
MCCOG is a Metropolitan Planning
Organization(MPO)
 MCCOG provides GIS and Planning support to
Madison County
 Approximately 130,000 in population
 Approximately 86,000 land parcels
 Approximately 450 sqaure miles
 County/Community in transition
 Largest City: Anderson

GIS OVERVIEW
Automation on a single SDE Geodatabase
 Versioned w/global ID’s
 Multiple Editors
 RDMS is SQL Server 2008 R2 (processor license)
 Windows 7 & Server 2008 R2 – 64 bit
 ArcGIS Server 10sp1 Standard Enterprise
Edition

WHY AUTOMATE?
Performance
 Streamlines process
 After hours
 Why not!

WHAT SHOULD BE AUTOMATED?
Rebuild Database Statistics
 Compress Database
 Reconcile & Post database versions
 Delete & Re-create Versions
 Sync Database changes to Web Geodatabase

PROCESS
Rebuild Statistics
 Compress Database
 Reconcile & Post versions to Default
 Kill all connections to Database
 Stop Web Services
 Delete all versions
 Compress Database
 Rebuild Statistics
 Re-create Versions

REBUILD STATISTICS

Analyze Tool

Updates database statistics of business tables,
feature tables, and delta(adds & deletes) tables
Create model & export as python script
 Run on client machine

STATISTICS
COMPRESS DATABASE

SDE command line


Sdeversion –o compress –u sde –p password –i
ServiceName -N
Batch file on server
COMPRESS DATABASE
Uncompressed State Tree
Compressed State Tree
RECONCILE & POST VERSIONS TO
DEFAULT
Reconcile Tool
 Create model & export as python script
 Run on client machine

RECONCILE & POST VERSION TO DEFAULT
KILL CONNECTIONS TO DATABASE

SDE command line
sdemon -o kill -t all -p password -N -i
Madison_Managed
 Batch file on server


Stop WebServices accessing database
AGSSOM arcscript command
 Batch file on server
 cd C:\Users\tpell\Desktop\AGSSOMv10.0
AGSSOM.exe -x WebServiceName

DELETE VERSIONS

SDE command line

Sdeversion –o delete –V VersionName –N –u
VersionOwnerName –p VersionOwnerPassword –i
ServiceName
Batch file on server
 Delete your QA version LAST!

DELETE VERSIONS
CLEANUP!
Compress database again
 Re-run Analyze/Re-build Statistics
 Re-Create Versions

SDE command line
 Batch file on server

FINAL COMPRESS
RE-CREATE VERSIONS
AUTOMATION
Utilize Windows Task Scheduler on both client
and server
 Allow ample time for each task to complete

AUTOMATION TIME-LINE
1am - Rebuild Statistics
 1:30am - Compress Database
 1:45am - Reconcile & Post versions to Default
 2am - Kill all connections to Database
 2:15am - Stop Webservices
 2:30am - Delete all versions
 2:45am - Compress Database
 3am - Rebuild Statistics
 3:30am - Re-create Versions

AUTOMATION – WINDOWS TASK SCHEDULE
AUTOMATION – TRIGGERS
AUTOMATION – ACTIONS
Browse to necessary program or file
 For python scripts browse to the python.exe
 Python scripts require arguments
 Argument is just the
python file
 For batch files just browse
to the .bat file

ONE STEP FURTHER

Automate changes to Web based applications
GETTING CHANGES TO THE WEB
Each dataset needs to be versioned with Global
IDs
 One way synchronization between internal
Geodatabase and ‘Web’ Geodatabase

TO THE WEB!
Stop all web services – AGSSOM batch file on
server
 Export, Compare & Import Schema Changes
between Managed & Web Geodatabase – python
script
 Sync changes between Geodatabases – python
script
 Start Webservices – AGSSOM batch file on server

SCHEMA CHANGES & REPLICATION
AUTOMATION TIME-LINE













1am - Rebuild Statistics
1:30am - Compress Database
1:45am - Reconcile & Post versions to Default
2am - Kill all connections to Database
2:15am - Stop Webservices
2:30am - Delete all versions
2:45am - Compress Database
3am - Rebuild Statistics
3:30am - Re-create Versions
3:45am - Export Replica Schema
4:15am - Compare & Import Replica Schema
4:45am - Sync Managed & Web Geodatabases
5:15am – Start Webservices
USEFUL TOOLS
PythonWin
 ArcScript AGSSOM tool
 Geodatabase Toolset or GDBT
 SDE command line is your friend

LESSONS LEARNED
Communicate
 TEST!
 Make Backups
 Local security settings



Ensure user on server has Log as a Batch Job
privleges under Local Security Policy>User Rights
Assignment
Be flexible with your time-line and
implementation
THANK YOU!
Trent Pell, GISP
GIS Coordinator
Madison County Council of Governments
[email protected]
765-641-9695