Red Gate forums :: View topic - Partition scheme issue
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Partition scheme issue

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
lerry75



Joined: 21 Sep 2012
Posts: 5

PostPosted: Fri Sep 21, 2012 9:15 am    Post subject: Partition scheme issue Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Mon Sep 24, 2012 4:49 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
lerry75



Joined: 21 Sep 2012
Posts: 5

PostPosted: Mon Sep 24, 2012 5:11 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Tue Sep 25, 2012 10:17 am    Post subject: Reply with quote

Is there a reason for the table rebuild listed in the warnings tab of the synchronization wizard?
Back to top
View user's profile Send private message
lerry75



Joined: 21 Sep 2012
Posts: 5

PostPosted: Wed Sep 26, 2012 10:02 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Wed Sep 26, 2012 10:21 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
lerry75



Joined: 21 Sep 2012
Posts: 5

PostPosted: Wed Sep 26, 2012 10:23 am    Post subject: Reply with quote

Thanks! Smile
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Wed Sep 26, 2012 10:25 am    Post subject: Reply with quote

Issue number is SC-6051.
Back to top
View user's profile Send private message
lerry75



Joined: 21 Sep 2012
Posts: 5

PostPosted: Fri Oct 05, 2012 8:41 am    Post subject: Reply with quote

Hi,

any news on the issue?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group