Administration for SQL Server - An Introduction

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

2. Technical Workshop Scope Managing enterprise SQL Server geodatabases Prerequisites Working knowledge of ArcSDE technology basics Working knowledge of SQL Server basics

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> It is very important that your system meet the system requirements for ArcSDE before installing it. There will probably be problems encountered down the road if the requirements are not met. These problems may not be seen until later, at a time that is usually least convenient. Please pay attention to the system requirements. It is very important that your system meet the system requirements for ArcSDE before installing it. There will probably be problems encountered down the road if the requirements are not met. These problems may not be seen until later, at a time that is usually least convenient. Please pay attention to the system requirements.

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

11. SQL Server installation defaults to Windows authentication mode. Without SQL Server authentication, an SDE-schema geodatabase cannot be created. SQL Server installation defaults to Windows authentication mode. Without SQL Server authentication, an SDE-schema geodatabase cannot be created.

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

13. ArcSDE 9.3.1 and later default installation path is C:\Program Files\ArcGIS\ArcSDE. This is different from versions before 9.3.1. Be aware of this change if you have scripts that reference %SDEHOME%. Scripts may need to be updated if you use the default location, as you may need to modify the install location to match what your scripts already use.ArcSDE 9.3.1 and later default installation path is C:\Program Files\ArcGIS\ArcSDE. This is different from versions before 9.3.1. Be aware of this change if you have scripts that reference %SDEHOME%. Scripts may need to be updated if you use the default location, as you may need to modify the install location to match what your scripts already use.

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

15. ArcSDE Post-Installation Wizard

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

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

18. Choosing between SDE and DBO Schema DBO user is Member of sysadmin role (instance-level) Specified as the owner of the database (database level) Multiple users can be DBO while retaining own logins Simplifies Windows authentication No need for a 'headless' SDE login DBO user is Member of sysadmin role (instance-level) Specified as the owner of the database (database level) Multiple users can be DBO while retaining own logins Simplifies Windows authentication No need for a 'headless' SDE login

19. Single Geodatabase Model Each geodatabase in one stand-alone SQL Server database Each geodatabase has own ArcSDE service (optional)

21. Choosing between Geodatabase Models Option 2: Multiple database model One geodatabase spread across multiple databases One of these per SQL Server instance Repository in a database whose name must be SDE

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

24. Comparing Administration of Different Models

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

26. Spatial Database Creation: DBO Schema DBO user already exists in all databases No SDE login created, so password not needed Single database model only Must supply database name Do NOT use database name SDE (reserved for multiple database model)

27. Spatial Database Creation: SDE Schema Wizard creates SDE login, needs new password Must supply a complex password for SDE login Database name defaults to SDE, resulting in multiple database model geodatabase Choose a different database name to create a single database model geodatabase

28. ArcSDE Post-Installation Wizard Demo

29. Repository Setup Creates ArcSDE repository objects Objects reside on SDE or DBO schema Table names Prefixed by �SDE_� or �GDB_�

30. Repository Creation Differences (DBO Schema) User creating repository must be DBO Use either Windows or SQL Server Authentication with Admin rights

31. Repository Creation Differences (SDE Schema) Create repository must be executed by SDE (SQL Server) user Post-Installer only supports SQL Server SDE user (not a Windows user named SDE)

32. Authorize ArcSDE ArcSDE must be authorized before use. ArcSDE keycode is written to the table SDE_server_config Keycode is checked When ArcSDE service starts Upon every direct connection

33. Create ArcSDE Service Windows Service Giomgr.exe listens for ArcSDE client connections Used for application server connections For each connection, creates child process called gsrvr.exe Not required if only using direct connect

34. Service Creation Differences (DBO Schema) Supply DBO user's login credentials If using Windows login must use full DOMAIN\login syntax

35. Service Creation Differences (SDE Schema) Supply SDE user's password

36. 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 Steps 2 & 6 are only required if using Application Server (versus Direct Connect).Steps 2 & 6 are only required if using Application Server (versus Direct Connect).

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

38. Connecting to the Geodatabase

39. ArcSDE Technology Client / Server Architecture

40. ArcSDE Technology Client / Server Architecture

41. ArcSDE Technology Client / Server Architecture

42. ArcSDE Technology Client / Server Architecture

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

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

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

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

47. Users and Privileges

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

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

50. Creating Logins, Users and Schemas Use SQL Server tools SQL Server Management Studio (SSMS) object browser Transact-SQL statements: CREATE LOGIN, CREATE USER, CREATE SCHEMA, ALTER USER, GRANT CREATE LOGIN � Creates the instance-level login CREATE USER [fred] FOR LOGIN [fred] � Creates the database user named fred, associating it to the instance-level login named fred. CREATE SCHEMA [fred] AUTHORIZATION [fred] � Creates a schema called fred owned by the database user called fred. ALTER USER [fred] WITH DEFAULT_SCHEMA [fred] � Sets the schema named fred to be default for the user named fred. Users that will create data in a geodatabase need the privileges CREATE PROCEDURE, CREATE TABLE, CREATE VIEW and REFERENCES. CREATE LOGIN � Creates the instance-level login CREATE USER [fred] FOR LOGIN [fred] � Creates the database user named fred, associating it to the instance-level login named fred. CREATE SCHEMA [fred] AUTHORIZATION [fred] � Creates a schema called fred owned by the database user called fred. ALTER USER [fred] WITH DEFAULT_SCHEMA [fred] � Sets the schema named fred to be default for the user named fred. Users that will create data in a geodatabase need the privileges CREATE PROCEDURE, CREATE TABLE, CREATE VIEW and REFERENCES.

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

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

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

54. Managing Users and Permissions Demo

55. Geodatabase Maintenance

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

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

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

59. 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 AUTO_CREATE_STATISTICS can be used, but be aware that statistics generation could happen at unexpected and inopportune times, resulting in temporary performance slowdowns due to overhead needed to compute statistics and some query recompiles that occur when statistics change. It may be better to update statistics during hours of reduced system load. AUTO_CREATE_STATISTICS can be used, but be aware that statistics generation could happen at unexpected and inopportune times, resulting in temporary performance slowdowns due to overhead needed to compute statistics and some query recompiles that occur when statistics change. It may be better to update statistics during hours of reduced system load.

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

61. Spatial Data Types

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

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

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

65. Troubleshooting

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

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

68. Resources

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

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

71. Questions? Evaluation Forms Your feedback is important

72. Suggestion for ending slide.Suggestion for ending slide.