SQL Server 2012 Enterprise vs Standard - use of partitioning?

13,576

Based on MSDN article Features Supported by the Editions of SQL Server 2012:

Only Enterprise version supports Partitioning of tables and indexes. It means that if any table or index is partitioned, then it cannot be imported into any other version. However a partition scheme and partition finction can exist without being used by any table or index. In this case import succeeds, as there are no partitioned tables or indexes.

Moreover, the RDBMS Managebility section tells, that Distributed partitioned views are supported by all versions to some extent. Thus permitting partition schemes and functions to exist as definition in all versions.

Share:
13,576
hitch
Author by

hitch

Updated on June 29, 2022

Comments

  • hitch
    hitch over 1 year

    I have been developing against SQL Server 2012 Enterprise, and came to migrate to production, where I found our hosting provider had installed Standard. I didn't think it should be a problem, as I hadn't implemented any enterprise specific features. However when I restored the DB it failed to activate, and in the Event Log, I found a message indicating the database couldn't be activated because it contained features not supported by the version. When I dug deeper, I found that it appeared that FTS or some other function had automatically created 5 partition functions and schemes.

    I then went through a time consuming process to remove the partitions functions and schemes, and could successfully restore the database on the Standard edition.

    After a while I backed up the DB (with no PFs or PSs), transferred it to my dev env, restored it (on SQL Enterprise), and after some time I found that a single partition function and scheme had been created. When I next came to backup and restore to prod, this time the database activated ok without error - even though there were partition functions and schemes.

    I have just run the following:

    SELECT feature_name FROM sys.dm_db_persisted_sku_features ;
    

    from here

    http://msdn.microsoft.com/en-us/library/cc280724.aspx

    and found that for the db with 5 partitions functions/schemes, Partitioning is listed as a version specific feature. When running the same against the db with 1 function/scheme, it's not listed.

    Is there something going on here that Auto created, FTS related partition schemes are ok on standard edition, but not manually created/other types? (keep in mind I never manually implemented partitioning)