SQL Azure Support for SQLServer Provider

[crispinatime]

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

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. 

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:
​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

[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

[jng]

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