FillFactor, IsNotForReplication, DoAllowPageLocks is not supported when used as part of a data package

Return to previous page

  • 4/7/2015
  • 2677 Views

Comments

2677 Views

FillFactor, IsNotForReplication, DoAllowPageLocks is not supported when used as part of a data package

Last updated 4 years ago

Comments

Common

(Enter the content of this article below)

Advanced

 

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:

  1. Create an EmptyDB
  2. 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
  3. 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
  4. Review the script that will be generated on the OriginalDB before executing the change
  5. Run the schema deployment on the OriginalDB

Part of the http://www.dnnsoftware.com/Resources/Wiki/loc/edit/Page/SQL-Azure-Compatibility.aspx
Contents
No sections defined

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out