Partition scheme issue

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Partition scheme issue

Postby lerry75 » Fri Sep 21, 2012 9:15 am

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
lerry75
 
Posts: 5
Joined: Fri Sep 21, 2012 9:01 am

Postby Brian Donahue » Mon Sep 24, 2012 4:49 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby lerry75 » Mon Sep 24, 2012 5:11 pm

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.
lerry75
 
Posts: 5
Joined: Fri Sep 21, 2012 9:01 am

Postby Brian Donahue » Tue Sep 25, 2012 10:17 am

Is there a reason for the table rebuild listed in the warnings tab of the synchronization wizard?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby lerry75 » Wed Sep 26, 2012 10:02 am

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

Image

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:

Image

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
lerry75
 
Posts: 5
Joined: Fri Sep 21, 2012 9:01 am

Postby Brian Donahue » Wed Sep 26, 2012 10:21 am

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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby lerry75 » Wed Sep 26, 2012 10:23 am

Thanks! :)
lerry75
 
Posts: 5
Joined: Fri Sep 21, 2012 9:01 am

Postby Brian Donahue » Wed Sep 26, 2012 10:25 am

Issue number is SC-6051.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby lerry75 » Fri Oct 05, 2012 8:41 am

Hi,

any news on the issue?
lerry75
 
Posts: 5
Joined: Fri Sep 21, 2012 9:01 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests