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/c ... 0708000113

Hopefully this explains the behavior.

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?

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
@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 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.

lerry75

Thanks!

Brian Donahue

Issue number is SC-6051.

lerry75

Hi,

any news on the issue?