1 / 72

Administration for SQL Server - An Introduction

Technical Workshop Scope. Managing enterprise SQL Server geodatabasesPrerequisitesWorking knowledge of ArcSDE technology basicsWorking knowledge of SQL Server basics. . SQL Server. ArcSDE technology. Geodatabase. ArcGIS. ArcSDE Technology. Implements geodatabases in multiple brands of multi-u

carr
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. 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 or the 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 be re-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.

More Related