Informix SDE - DOC by pengxiang

VIEWS: 7 PAGES: 12

									               SQL Server-SDE POC




                Department of Water Affairs and Forestry




Version: 1.0
                                                       SQL Server-SDE

                         Corporate Spatial Data for the Department of Water
                                              Affairs and Forestry (DWAF)


                                         TABLE OF CONTENTS
1.     POC SUMMARY ................................................................................................................................ 4

     1.1       POC OVERVIEW ........................................................................................................................ 4
     1.2       SQL-SERVER VS. INFORMIX ................................................................................................... 4

2.     DOCUMENT OVERVIEW ............................................................................................................... 5

     2.1       INTRODUCTION ........................................................................................................................... 5
     2.2       ACRONYMS AND ABBREVIATIONS ........................................................................................... 5

3.     WHAT IS SDE? ................................................................................................................................... 6

     3.1       ESRI BACKGROUND ................................................................................................................. 6
     3.2       ARCSDE BACKGROUND........................................................................................................... 6

4.     SDE ON SQL-SERVER ................................................................................................................... 78

     4.1       INTRODUCTION .........................................................................................................................78
     4.2       INSTALLATION ON SQL SERVER ............................................................................................78
     4.3       PERFORMANCE ON SQL SERVER .........................................................................................89
     4.4       DIRECT CONNECTION ............................................................................................................. 89
     4.5       NO VERSION PROBLEMS ......................................................................................................... 89
     4.6       SUPPORT THE METADATA SERVER......................................................................................... 9
     4.7       SQL SERVER CAN BE EASILY MANAGED ............................................................................... 9

5.     SQL SERVER VS. INFORMIX ..................................................................................................... 10

     5.1       ADVANTAGES OF SQL SERVER-SDE .................................................................................. 10
     5.2       DISADVANTAGES OF SQL SERVER-SDE ............................................................................ 10
     5.3       ADVANTAGES OF INFORMIX-SDE.........................................................................................10
     5.4       DISADVANTAGES OF INFORMIX-SDE .................................................................................. 10

6.     RECOMMENDATIONS ................................................................................................................. 11
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                            Affairs and Forestry (DWAF)


1. POC SUMMARY

1.1      POC OVERVIEW
The following table summarises the findings of the SQL Server-SDE:
                         Finding
 Install SDE 9.0         Install seamless;
                         No reboot required
                         Can reuse the Informix SDE license
 Load of layers          Easy and seamless
 Upgrade to SDE 9.1      Easy and seamless
 Load of Raster Images Easy and seamless
 Integration with IMS    Easy and seamless
 (WEB)
 Setup on client PC      Easy and seamless


1.2      SQL-SERVER VS. INFORMIX
The following table compares SQL Server-SDE with the Informix-SDE:
                            SQL Server-SDE               Informix-SDE
Performance                 YES (4-5 times faster        NO (4-5 times slower than
                            than Informix on DWAF        SQL Server on DWAF
                            servers see 4.3 for details) servers see 4.3 for details)
IMS (Web) – Setup /         YES (Speed is acceptable NO (Speed is too slow;
Deployment                  Easy & Seamless)             lookup tables needs to be
                                                         reconfigured resulting in
                                                         hours of rework)
Versioning                  YES (No known issues)        NO (Problematic)
Licenses                    YES (1 per Enterprise        NO (1 per database
                            none required per database instance – no direct
                            instance – through direct    connections supported)
                            connections)
Raster Images               YES (Load 14Gigs and         NO – (Experienced
                            tested successfully)         problems with Informix
                                                         spatial blade during tests)
Training                    YES                          NO (Discontinued)
Metadata Server             YES                          NO
Synchronise to Informix Yes                              Yes
Synchronise to SQL-         Yes                          No (Spatial blade)                                Field Code Changed

Server                                                                                                     Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
           Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   4 of 12
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                            Affairs and Forestry (DWAF)



It is recommended that the SQL Server-SDE be moved to the production environment
and that it be used and tested within Spatial and Land Information Management.




                                                                                                           Field Code Changed
                                                                                                           Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
           Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   5 of 12
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                             Affairs and Forestry (DWAF)



2. DOCUMENT OVERVIEW

2.1      INTRODUCTION
   The SDE software is the main component required to setup a corporate Gis in the
   department. After several problems relating to version incompatibilities other
   solutions were investigated.

   The SQL-server database platform is available in DWAF because it is used as the
   primary database for WEB applications. This database platform is also used
   extensively in Water Services and in the Forestry sections. Most of the Corporate
   applications purchased by DWAF runs on SQL-Server (Rational Rose- ClearCase;
   Hummingbird; Access Control)

   This document reports on the SQL server-SDE proof of concept (POC) that was
   conducted.

2.2      ACRONYMS AND ABBREVIATIONS
         Acronym/Abbreviation Definition
         SQL                  Structured Query Language is a standard
                              language invented in the 80’s to provide access to
                              a database.
         Features             Anything that can have a geographic coordinate
                              (moving or non-moving).
         SDE                  Spatial Database Engine
         ArcSDE               ArcInfo Spatial Database Engine (application from
                              ESRI).
                              This software adds GIS functionality to a client-
                              server database
         ArcGIS               ArcGIS is the latest GIS software from ESRI
                              based on the new Geodatabase architecture
         RDBMS                Relational Data Base Management System like
                              Informix or SQL-server.
         ESRI                 Environment Systems Research Institute
         GIS                  Geographic Information System
         SQL                  Structured Query Language is a standard
                              language invented in the 80’s to provide access to
                              a database.
         ArcCatalog           Application to help you organise the use your GIS
                              data                                                                         Field Code Changed
         ArcToolbox           Tools to perform GIS related analysis and                                    Formatted: French (France)
                              functions
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
            Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   6 of 12
                                  SQL Server-SDE

                  Corporate Spatial Data for the Department of Water
                             Affairs and Forestry (DWAF)


         ArcMap                         Application used to display GIS information


3. WHAT IS SDE?

3.1      ESRI GEO-DATABASE BACKGROUND
      ESRI is currently one of the world leaders in GIS technologies and at the
      forefront of GIS standards worldwide. In order to provide the highest level of
      quality, DWAF needs to utilise ArcGIS the Geo-Database technology to its
      fullest potential.

3.2      ARCSDE BACKGROUND




   What is ArcSDE?
   o ArcSDE is the tool that allows you to put your spatial data in a RDBMS (like
     Informix/SQL- server) and to use it in a multi-user environment.
   o All spatial data is stored in standard DBMS tables using data types available
     for the host database. (This means that all the database maintenance functions
     are available to manage the spatial data).
                                                                                                           Field Code Changed
   o ArcSDE does not supersede or replace any existing DBMS functionality.
     Instead, it uses and complements the base DBMS.                                                       Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
           Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   7 of 12
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                             Affairs and Forestry (DWAF)


   o ArcSDE performance hinges largely on how well the host database is
     configured. (If there is problems on the host DBMS then it will surface in the
     SDE software)
   o ArcSDE is OGC compliant (meaning that other GIS OGC compliant
     applications can utilise it).

   The following picture shows that ArcSDE forms the key building block and to
   establish a Corporate GIS system.
   ArcSDE drives:
   o Internet mapping (ArcIMS)                                                                             Formatted: Indent: Left: 0.28", Bulleted +
   o GIS desktop products (like ArcInfo, ArcEditor, ArcView)                                               Level: 1 + Aligned at: 0.08" + Tab after:
                                                                                                           0.33" + Indent at: 0.33"

  Without SDE your GIS system will never be manageable on a corporate level and
  will never be available to the masses.



4. SDE ON SQL-SERVER

4.1      INTRODUCTION
         There is currently no working backup or interim solution for the current SDE
         scenarios. Spatial and Land Information Management lost months of work
         because of the continued problems of the SDE environment. There is a very
         strong argument that the use of another RDBMS database might solve all
         these versioning and the other problems experienced on the Informix platform.

4.2      INSTALLATION ON SQL SERVER
         The installation of SDE on SQL server was conducted on 2 separate servers
         (development and test server) without any problems. Installation took about an
         hour per installation and no reboot is required.

         The following tasks were completed on the development server:                                     Formatted: Indent: Left: 0.5", Bulleted +
                                                                                                           Level: 1 + Aligned at: 0" + Tab after: 0.25" +
         o On the development server , SDE 9.0 was installed – (1 hour time);                              Indent at: 0.25", Tab stops: 0.75", List tab +
         o About 30 different spatial layers (coverages) were loaded (3 days).                             Not at 0.25"
         o Tests were conducted to proof that the SDE worked without problems                              Formatted: Indent: Left: 0.5", Bulleted +
         o The SDE 9.0 was upgraded to SDE 9.1 (1 hour)                                                    Level: 1 + Aligned at: 0" + Tab after: 0.25" +
                                                                                                           Indent at: 0.25", Tab stops: 0.75", List tab +
                                                                                                           Not at 0.25"
         The following tasks were completed on the test SQL server:
                                                                                                           Field Code Changed
         o Install of SDE 9.1 (1 hour)
                                                                                                           Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
            Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   8 of 12
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                             Affairs and Forestry (DWAF)


         o Copy all the layers from the development to test SDE (drag-drop
           operation)
         o Test layers on test SDE (all worked)
         o Load 1:500 000 raster images (4 hours)
         o Load 1:250 000 raster images (1 day)
         o Load 1:50 000 raster images 2228 -> 2428 (3 days and run out of disk
           space)
         o Load image raster layers back to development server (drag-drop operation)
         o Test layers with IMS (works seamlessly)
         o Test with Informix SDE:
              o Load layers from Informix to SQL Server (Test failed because
                   spatial index was corrupted on SQL Server)
              o Load layers from SQL Server to Informix (Test success)


         The following tasks were done on the production SQL server:
         o Motivation and approve for a system administrator on SQL Server (was                            Formatted: Indent: Left: 0.5", Bulleted +
            successful)                                                                                    Level: 1 + Aligned at: 0" + Tab after: 0.25" +
                                                                                                           Indent at: 0.25", Tab stops: 0.75", List tab +
         o Motivation for installation of SDE on SQL server (was unsuccessful)                             Not at 0.25"


4.3      PERFORMANCE ON SQL SERVER

Layer                        SQL Server SDE                       Informix SDE
Farm boundaries              2.23 seconds                         10.75 seconds
Western Cape
Farm boundaries RSA          7.5 seconds                          32.44 seconds

         SDE on SQL-server is 4-5 times faster than SDE on Informix making it
         ideally for WEB implementations.

         Why the big speed difference:
         o The memory, CPU and load of the servers were different                                          Formatted: Indent: Left: 0.5", Bulleted +
         o Scalability was not tested (that is the time for 10-100 concurrent users                        Level: 1 + Aligned at: 0" + Tab after: 0.25" +
                                                                                                           Indent at: 0.25", Tab stops: 0.75", List tab +
           doing spatial queries).                                                                         Not at 0.25"
         o The grid size of the Spatial Index on the Informix Spatial Blade defaults to
           0. (This can have a major impact on spatial queries).
         o These results may also change in the production environment (when
           compared between Informix (on 8 processors) and SQL-Server (2
           processors).                                                                                    Field Code Changed
                                                                                                           Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
            Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   9 of 12
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                             Affairs and Forestry (DWAF)


         This is therefore not a true benchmark but only an indication of the
         current situation on the test environments at DWAF

4.4      DIRECT CONNECTION
         SDE on SQL-server can connect directly to the database without the SDE
         service. This can have huge cost savings when SDE needs to be deployed to
         regions and CMA’s

4.5      NO VERSION PROBLEMS
         There are currently no version problems with SDE on SQL-Server. (This
         situation can possibly change with upgrade to SQL-Server 2005 or later)

4.6      SUPPORT THE METADATA SERVER
         SDE for Informix does not support Metadata Server (that is the show of
         metadata on the Internet). SDE on SQL-Server supports the Metadata Server
         that integrates with Arc/IMS

4.7      SQL SERVER CAN BE EASILY MANAGED
         The main advantage of SQL server is that it is very simple to manage and
         those skills for SQL server is accessible and available.




                                                                                                           Field Code Changed
                                                                                                           Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
            Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   10 of 12
                                   SQL Server-SDE

                 Corporate Spatial Data for the Department of Water
                              Affairs and Forestry (DWAF)




5. SQL SERVER VS. INFORMIX

5.1      ADVANTAGES OF SQL SERVER-SDE
         o No extra licenses are required.
         o Performance is currently faster in the test environment (see 4.3)
         o Currently no versioning problems on SQL-Server.
         o Forestry and Water Services can directly be accommodated because there
           production databases run on SQL-Server.
         o Integrates seamlessly with ArcIMS Web Services

5.2      DISADVANTAGES OF SQL SERVER-SDE
         o The WMS application is using Informix-SDE. (This however is not seen
           as a real disadvantage because there are no synchronisations between the
           Corporate SDE and the WMS SDE).
         o SQL Server (and other Microsoft products) seems to be the main target of
           both viruses and hackers.

5.3      ADVANTAGES OF INFORMIX-SDE
         o Informix has built in support for spatial types and spatial queries that can
           be done in the SQL syntax.
         o Is already established in DWAF

5.4      DISADVANTAGES OF INFORMIX-SDE
         There are a few disadvantages with the current Informix-SDE namely:
         o The performance is slower in the test environment (see 4.3)                                     Formatted: Indent: Left: 0.5", Outline
         o Version problems will always be a reality. (Except if SDE runs on its own                       numbered + Level: 1 + Numbering Style: Bullet
                                                                                                           + Aligned at: 0" + Tab after: 0.25" + Indent
            dedicated server)                                                                              at: 0.25", Tab stops: 0.75", List tab + Not at
         o An SDE license per database instance is required (R80 000) (because                             0.25"
            direct SDE connections are not possible yet).
         o It is not practical to use Informix with ArcIMS (speed and
            incompatibilities)
         o There is currently no DBA on Informix at DWAF that is trained in the
            SDE software
         o Training for Informix-SDE has been abandoned by ESRI.

                                                                                                           Field Code Changed
                                                                                                           Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
             Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   11 of 12
                                  SQL Server-SDE

                Corporate Spatial Data for the Department of Water
                            Affairs and Forestry (DWAF)




6. RECOMMENDATIONS

A SQL Server-SDE will provide the ideal backup solution for the occurring version
problems that occur on the Informix-SDE. It will directly support the Forestry and
Water Services business units (and will ensure that a uniform environment is used in
DWAF). It is possible to synchronise data from SQL-Server to Informix with a click
of a button (note however that the reverse is not so simple).

The performance of SQL server was a lot faster than Informix on the provided tests
environments. The Integration with IMS (WEB) is seamless. Although it is possible
to link IMS with the Informix-SDE it is not practical because all lookup table links
are lost and the performance degrading is unacceptable.

It is recommended that the SQL Server-SDE be moved to the production environment
and that it be used and tested within Spatial and Land Information Management.




                                                                                                           Field Code Changed
                                                                                                           Formatted: French (France)
                                                                                                           Formatted: French (France)
 Author :Cobus Olivier                                                                By: Olsen
                                                                           Last SavedChris Cobus Olivier
 C:\Docstoc\Working\pdf\5f44c7ec-f966-4f34-
 8575-
                                                        Last Saved Date: 2012-07-282006-08-022005-12-
 9310cc4ce5dc.docC:\_appl\source\SDE\Document
                                                                                                     01
 ation\SDE - SQL_Server.docD:\HOME\Temporary
 Internet Files\OLK137\SDE - SQL_Server.doc
           Privileged and confidential to the Department of Water Affairs and Forestry

                                              Page   12 of 12

								
To top