[Fdo-trac] [fdo-trac] #869: SQL Azure Support for SQLServer Provider

[FDO]

#869: SQL Azure Support for SQLServer Provider
----------------------------+-----------------------------------------------
   Reporter:  crispinatime  |       Owner:  gregboone
       Type:  defect        |      Status:  new      
   Priority:  major         |   Milestone:  3.8.0    
  Component:  FDO API       |     Version:  3.8.0    
   Severity:  3             |    Keywords:           
External_id:                |  
----------------------------+-----------------------------------------------
 Currently the SQLServer Provider will not connect to an online SQL Azure
 hosted database.

 SQL Azure solution contains the same datatypes, indexes etc (including
 spatial Geometry and Geography).  However, there are design elements that
 are not relevant when not running on physical hardware that mean some of
 the SQLServer commands and capabilities used in the FDO provider code
 cause connection and schema issues.

 This ticket contains a brief description about some changes to make the
 provider works with SQL Azure.  These have been tested with a sample FDO
 client (MapGuide v2.5).

 Note that these changes only make the connection and reader elements of
 FDO work with SQL Azure.  Schema swapping (use of "USE" is not possible -
 requires database re-connection) so it was not possible to pass the schema
 creation tests.  Therefore to utilise the reader required a database to
 already exist.

 SQL Azure databases may be created for free to use for testing with a
 Microsoft account.  See [http://www.windowsazure.com/en-us/pricing/free-
 trial/]

 [[BR]]
 The following issues are addressed in the accompanying patch file:

 1. SQL Azure Does Not Support FILEGROUP_NAME

 {{{
 FILEGROUP_NAME( groupId ):
 }}}

 This is used in FDO and  returns the name of the groupId. It does the
 query over the system table sys.filegroups such as:

 {{{
 select name from sys.filegroups where data_space_id= [groupId]
 }}}

 Both FILEGROUP_NAME function and sys.filegroups table haven’t been
 supported at current SQL Azure version.

 Solution: Temporarily replace it with the constant name ‘primary’
 (Still need another permanent solution!)

 [[BR]]
 2. SQL Azure Does Not Support Access to Other Databases

 {{{
     [DatabaseName].dbo.syscolumns
 }}}

 Solution: Assuming the restriction is to one database within Azure (part
 of the connection string), just use the current database by removing the
 [DatabaseName] part like:

 {{{
 databasename.dbo.syscolumns -> dbo.syscolumns.
 }}}

 [[BR]]
 3. SQL Azure Does Not Support 'sysindexes' Table

 {{{
 sysindexes
 }}}

 Solution: System table named sysindexes may be replaced by sys.indexes in
 SQL Azure.

 As a result, some SQL will be updated as follows:

 {{{
 sysindexes.id -> sys.indexes.object_id
 sysindexes.indid -> sys.indexes.index_id
 }}}


 ----

 In the patch file the changes for SQLAzure have been wrapped in a
 preprocessor IFDEF statement to allow for different builds.  Ideally the
 code can be reviewed and a single common set of SQL created for the
 provider but this may not be possible.
 The "SQLServerSpatialSchemaMgr" project requires the following pre-
 processor directive to be defined in order to build the provider for SQL
 Azure environment.

 {{{
 #ifdef SQL_AZURE_MODE
 }}}


 ----

 References:[[BR]]
 [http://blog.sqlauthority.com/2010/06/04/sql-server-generate-database-
 script-for-sql-azure/]

 [http://blogs.architectingconnectedsystems.com/blogs/cjg/archive/2011/12/26
 /SQL-Azure-supported-tables_2F00_views.aspx]

-- 
Ticket URL: <http://trac.osgeo.org/fdo/ticket/869>
FDO <http://fdo.osgeo.org/>
Feature Data Objects