Partition scheme issue

lerry75lerry75 Posts: 6
edited October 5, 2012 3:41AM in SQL Compare Previous Versions
Hi All,

I have exactly the same definition of a table in two different databases (pre-production and production). They are partitioned on the same partition schema, but, being different environments, the underlying partition functions work on different values.

The default behavior of SQL Compare (ignore Filegroups, partition schemes and partition functions checked) is ok: tables are considered equal.
If I disable that option, SQL Compare considers that as different objects, even if in sync script window there are no differences.
In deployment wizard it try to drop all indexes, rebuild all partitions and re-create indexes.

Of course, I need to take care of filegroups and partition schemes, but I don't want to rebuild all the partitions in production!

Is there a way to avoid that behavior?

Thanks

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    There are some circumstances where SQL Server will not allow you to alter a table, and in that case, the table has to be created from scratch and the data from the original table copied over. There are a few aspects of partitions that will make SQL Compare rebuild the table. They're all listed here:

    http://www.red-gate.com/supportcenter/c ... 0708000113

    Hopefully this explains the behavior.
  • Unfortunately the case is not listed there.
    They are exactly same tables, with same indexes on the same partition scheme.

    Ignoring "Filegroups, partition schemes and partition functions", SQL Compare consider them equal (no script generated).
    Not ignoring, SQL Compare find some "ghost" differences and generate a script that rebuild all partitions.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Is there a reason for the table rebuild listed in the warnings tab of the synchronization wizard?
  • I can't see any reason to rebuild partitions.
    As you can see here

    redgate1.jpg

    even if tables appear equal, they are in "objects that exist in both but are different".
    When I go through Deployment Wizard, I see this warning:

    redgate2.jpg

    And this is an extract of deployment script:
    PRINT N'Dropping index [FX_FinanceDM_AccountBalances_DW_BatchID] from [FinanceDM].[AccountBalances]'
    GO
    DROP INDEX [FX_FinanceDM_AccountBalances_DW_BatchID] ON [FinanceDM].[AccountBalances]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Dropping index [IX_FinanceDM_AccountBalances_MK_BusinessLinesID] from [FinanceDM].[AccountBalances]'
    GO
    DROP INDEX [IX_FinanceDM_AccountBalances_MK_BusinessLinesID] ON [FinanceDM].[AccountBalances]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Dropping index [IX_FinanceDM_AccountBalances] from [FinanceDM].[AccountBalances]'
    GO
    DROP INDEX [IX_FinanceDM_AccountBalances] ON [FinanceDM].[AccountBalances]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Dropping index [IX_FinanceDM_AccountBalances_Accountid] from [FinanceDM].[AccountBalances]'
    GO
    DROP INDEX [IX_FinanceDM_AccountBalances_Accountid] ON [FinanceDM].[AccountBalances]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Dropping index [IX_FinanceDM_AccountBalances_MK_DatesID_TradeDate] from [FinanceDM].[AccountBalances]'
    GO
    DROP INDEX [IX_FinanceDM_AccountBalances_MK_DatesID_TradeDate] ON [FinanceDM].[AccountBalances]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Altering [FinanceDM].[AccountBalances]'
    GO
    ALTER TABLE [FinanceDM].[AccountBalances] REBUILD PARTITION = ALL
    WITH (DATA_COMPRESSION = ROW)
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating index [IX_FinanceDM_AccountBalances_MK_DatesID_TradeDate] on [FinanceDM].[AccountBalances]'
    GO
    CREATE CLUSTERED INDEX [IX_FinanceDM_AccountBalances_MK_DatesID_TradeDate] ON [FinanceDM].[AccountBalances] ([MK_DatesID_TradeDate]) WITH (DATA_COMPRESSION = ROW) ON [PS_Datamart] ([DW_BatchID])
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating index [FX_FinanceDM_AccountBalances_DW_BatchID] on [FinanceDM].[AccountBalances]'
    GO
    CREATE NONCLUSTERED INDEX [FX_FinanceDM_AccountBalances_DW_BatchID] ON [FinanceDM].[AccountBalances] ([DW_BatchID]) WITH (DATA_COMPRESSION = ROW) ON [PS_Datamart_Index] ([DW_BatchID])
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating index [IX_FinanceDM_AccountBalances_MK_BusinessLinesID] on [FinanceDM].[AccountBalances]'
    GO
    CREATE NONCLUSTERED INDEX [IX_FinanceDM_AccountBalances_MK_BusinessLinesID] ON [FinanceDM].[AccountBalances] ([MK_BusinessLinesID_BusinessLineID]) INCLUDE ([DW_BatchID], [MK_DatesID_TradeDate]) WITH (DATA_COMPRESSION = ROW) ON [PS_Datamart_Index] ([DW_BatchID])
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating index [IX_FinanceDM_AccountBalances] on [FinanceDM].[AccountBalances]'
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [IX_FinanceDM_AccountBalances] ON [FinanceDM].[AccountBalances] ([MK_DatesID_TradeDate], [MK_UCRAccountsID_AccountID], [DW_BatchID]) WITH (DATA_COMPRESSION = ROW) ON [PS_Datamart_Index] ([DW_BatchID])
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating index [IX_FinanceDM_AccountBalances_Accountid] on [FinanceDM].[AccountBalances]'
    GO
    CREATE NONCLUSTERED INDEX [IX_FinanceDM_AccountBalances_Accountid] ON [FinanceDM].[AccountBalances] ([MK_UCRAccountsID_AccountID]) INCLUDE ([MK_CurrenciesID_CurrencyCode], [MK_DatesID_TradeDate]) WITH (DATA_COMPRESSION = ROW) ON [PS_Datamart_Index] ([DW_BatchID])
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Sorry about this - I'm not a DBA so I don't understand this fully.

    If you say that you don't need to rebuild the table, I'll log an issue with development about it.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Issue number is SC-6051.
  • Hi,

    any news on the issue?
Sign In or Register to comment.