SQL Server - A workaround for SS 2005 Replication with Partition Switch feature

Asked By Depp

19-Nov-09 11:01 AM

Hello there,

Actually, subject tells almost everything.

We have a database which is > 1TB in size and we use Partition Switch
feature for some tables for archiving purposes.

Now we would like to use a TDM application which is called (Oracle) Golden
Gate with this prod system so that we would use another server as a reporting
server. By the way, Golden Gate uses SQL Server' s Replication components and
subsystem.

However we know that Partition Switch support comes with SQL Server 2008 and
only for Transactional Replication as an enhancement.

Upgrading to SQL Server 2008 is not a solution for now and we need to find a
workaround for this problem. For the moment of partition switching, Golden
Gate services and the application which uses this database can be stopped at
a PDT.

Do you guys have any idea \ suggestion about this problem? Is there any
workaround that we can use Golden Gate with Partition Switch feature?

Note:
We will perform a test with a Premier Field Engineer from Microsoft on
Monday. We have an idea that seems might work theorically, however we are not
totally sure. You can find the steps below, what do you think about it?

- Stopping all Golden Gate services (Manager, Extracts, Pumps, Replicats)
- Running sp_removedbreplication
- Running Switch Partition command
- Somehow marking the database as a replicated database???
- Running Golden Gate Services...

20-Nov-09 07:56 AM

partition switching is supported in SQL Server 2005, however there is no GUI
for it in SQL Server 2005 Management Studio.

What I am confused about is - is the partitioned table in a subscription or
a publication?

If it is in the publication you should be able to drop the article from the
subscription, drop it from the publication, make the change, add it back to
the publication, add it back to the subscription doing a no sync, then do a
validation to determine what data is missing and then sync.

20-Nov-09 02:01 PM

Thanks for your time and reply.

I know that Partition Switching is supported, but you cannot perform it for
a replicated table. This feature seems to have come with SQL Server 2008 as
an enhanced feature.

The Partitioned table which we want to switch is marked as replicated by
Golden Gate so when we run ALTER TABLE ... SWITCH... command it says this is
unsupported as the table is being marked as replicated.

So, for the moment that this partitioned table is being switched, we need to
unmark its replication mark and after switching partition, we need to mark it
again as partitioned.

Another problem here is that, even though Golden Gate uses SQL Server
Replication components to make its job done, I see no Subscribers nor
Publications from under Replication node on Object Explorer in SSMS.

Do you think that there is a trick\workaround to fool SQL Server to unmark
the table as not being replicated so that we can perform partition switch and
then mark it again as nothing happened and start Golden Gate services so that
it would start replicating this table?

Thanks.

21-Nov-09 11:51 AM

There are some hacks you could try or you can do the SWITCH if you take the
table out of publication do the Alter and add it back. These are not going
to be supported by the vendor though. I'd see if you can get upgraded to SQL
2008?
Cheers,
Paul Ibison

23-Nov-09 07:45 AM

Thanks for your input Paul.

I think the thing that I should focus on is that I somehow must find a way
to remove the replication flag on that particular partitioned table so that I
can Switch that Partition. And during this operation as the Golden Gate
services will be down, they will not notice this operation I guess (Golden
Gate does not support DDL replication yet). After completing switch
operation, I again somehow must reflag that table as replicated so that
Golden Gate will be replicating it after its services started.

Do you guys have any this kinda hacking clear methods that may help us for
this particular situation?

Thanks.

23-Nov-09 01:54 PM

Can only really suggest supported means. Not 100% I could achieve this
easily in SQL 2005 anyway.

Perhaps you could use an indexed view that sits on top of the partitioned
table and just replicate the indexed view. This'll be schema bound by
definition but I am not sure if this prevents the use of SWITCH. Wotrh
setting up a simple test to see.

HTH,

Paul

24-Nov-09 02:21 AM

This is really a challenge for us that we have to overcome somehow.

When a table is added for replication from Golden Gate, it is somehow marked
as replicated but there is no any Publication, Subscription etc. I can not see
anything related to a replication configuration.

When sp_removedbreplication is executed for the database which is used for
Golden Gate replication, that table becomes available for partition switch.
We took a look at the sp_removedbreplication' s codes but we could not see
anything useful to remove the replication mark only for one table and marking
it again as replicated after partition switch.

I am gonna ask about this challenge to a Golden Gate support agent too,
however unfortunately I am not expecting to get a satisfactory reply.

Thanks again.