Partition scheme issue

[lerry75]

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

[Brian Donahue]

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/content/knowledgebase/SQL_Compare/KB200708000113

Hopefully this explains the behavior.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[lerry75]

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 Donahue]

Is there a reason for the table rebuild listed in the warnings tab of the synchronization wizard?
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[lerry75]

I can't see any reason to rebuild partitions.
As you can see here



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:



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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

[Brian Donahue]

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 Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[lerry75]

Thanks!

[Brian Donahue]

Issue number is SC-6051.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[lerry75]

Hi,

any news on the issue?