Opened 11 years ago

Closed 3 years ago

#869 closed defect (fixed)

SQL Azure Support for SQLServer Provider

Reported by: crispinatime Owned by: jng
Priority: major Milestone: 3.8.0
Component: SQLServer Spatial Version: 3.8.0
Severity: 3 Keywords:
Cc: External ID:

Description

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/


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!)


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


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

Attachments (1)

Azure.patch (4.1 KB ) - added by crispinatime 11 years ago.

Download all attachments as: .zip

Change History (4)

by crispinatime, 11 years ago

Attachment: Azure.patch added

comment:1 by crispinatime, 11 years ago

Component: FDO APISQLServer Spatial
Owner: changed from gregboone to danstoica

comment:2 by jng, 3 years ago

Owner: changed from danstoica to jng

Extra notes (for self) about detecting whether the current db is in SQL PAAS or not:

https://stackoverflow.com/questions/48447954/how-should-a-t-sql-script-detect-if-its-running-in-azure-sql-database-or-on-pre

comment:3 by jng, 3 years ago

Resolution: fixed
Status: newclosed

In 8024:

Add experimental support for Azure SQL PaaS. Original patch by Crispin Hoult of Linknode. Revisions made by me to convert the "Am I on azure?" flag from a pre-processor symbol to a runtime check (via checking whether the value of the SQL expression ServerProperty('Edition') is "SQL Azure").

Fixes #869

Note: See TracTickets for help on using tickets.