FillFactor, IsNotForReplication, DoAllowPageLocks is not supported when used as part of a data package
Error
Element Index: [xxxx] has an unsupported property FillFactor set and is not supported when used as part of a data package.
Element Index: [xxxx] has an unsupported property IsNotForReplication set and is not supported when used as part of a data package.
Element Index: [xxxx] has an unsupported property DoAllowPageLocks set and is not supported when used as part of a data package.
Cause
FillFactor is not supported on SQL Azure Database. NOT FOR REPLICATION is not supported on SQL Azure Database. ALLOW_PAGE_LOCKS is not supported on SQL Azure Database.
This query must return no rows:
select *
from sys.indexes
WHERE fill_factor<>0
Fix
To solve this issue, you must recreate the index (DROP and CREATE) since there is no option to alter the index with FillFactor = 0 (the value must be between 1 and 100). See
http://msdn.microsoft.com/en-us/library/ms177459.aspx for more information.
To automate this task, you can use RedGate SQL Compare and use this technic:
- Create an EmptyDB
- Compare the OriginalDB with EmptyDB, specifying in the following ignore options:
- Whitespace
- Fill factor and index padding
- WITH NOCHECK
- Filegroups, partition schemes and partition functions
- Extended Properties
- Collations
- User properties
- WITH element order
- The LOCK properties of indexes
- NOT FOR REPLICATION
- Database and server name in synonyms
- Execute the schema deployment on EmptyDB to have the same schema without the FillFactor - Now set the source the EmptyDB and destination the OriginalDB, specifying the following ignore options:
- Whitespace
- Extended Properties
- Collations
- User properties
- Database and server name in synonyms - Review the script that will be generated on the OriginalDB before executing the change
- Run the schema deployment on the OriginalDB
Part of the
https://www.dnnsoftware.com/Resources/Wiki/loc/edit/Page/SQL-Azure-Compatibility.aspx