1 / 56

Administration for SQL Server - An Introduction

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 .

mike_john
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Administration for SQL Server - An Introduction Wendy Wallace & Thomas Dunn ESRI – Redlands

  2. 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

  3. 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

  4. Agenda Installing and Configuring ArcSDE Connecting to the Geodatabase Managing Users and Privileges Geodatabase Maintenance Tasks Storage Type Comparison Troubleshooting Resources

  5. Installing and Configuring ArcSDE

  6. 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

  7. 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

  8. 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>

  9. 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

  10. 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%

  11. SDEHOME Server libraries and executables Configuration and log files

  12. 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

  13. 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

  14. 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

  15. Choosing between SDE and DBO Schema

  16. Single Geodatabase Model ArcSDEservice • Each geodatabase in one stand-alone SQL Server database • Each geodatabase has own ArcSDE service (optional) DB Repository and data

  17. Can have several single database GDBs SQL Server Instance 5151 5153 5154 DB5 DB3 DB4

  18. 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)

  19. 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

  20. ArcSDE Post-Installation Wizard Demo

  21. 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

  22. 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")

  23. Connecting to the Geodatabase

  24. 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)

  25. 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

  26. 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

  27. 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

  28. 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”

  29. Users and Privileges

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. Managing Users and Permissions Demo

  37. Geodatabase Maintenance

  38. 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

  39. 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

  40. Compressing the Geodatabase • To compress, use Geoprocessing Tool • From ArcCatalog, ArcToolbox or script • Must be performed by ArcSDE Administrator

  41. 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

  42. 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

  43. Spatial Data Types

  44. 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

  45. 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

  46. 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

  47. Troubleshooting

  48. 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

More Related