ALTER TABLE SWITCH PARTITION on a replicated table

A customer wants to implement table partitioning on a replicated table.

They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.

So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).

What are his architectural or practical solutions to using table partitioning and replication?

thx

I'm sorry but i did not understand your question exactly.

Did you mean something like : "What are the benefits of using Table Partitioning with Replication?" ?

Ekrem ?nsoy

MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE

|||

BOL says that SWITCH PARTITION cannot be used on a replicated table.

So if my customer wants to implement a partition function/scheme and switch a partition out on a monthly basis, it appears that this cannot be done.

What other options would they have to roll data on and off their table on a monthly basis (other than SELECT INTO the archive table followed by a DELETE on the original table)?

thx

|||That is the only option. SWITCH is not supported on replicated tables. The reason for this is very straightforward. A SWITCH operation is a metadata only operation. These do not get picked up by replication and so can not be applied to the subscriber. Since it can not be applied to a subscriber, allowing this operation would create a subscriber which is out of synch with the publisher with no way of putting it back in synch. So, if it is replicated, you have to use either an insert or a select into in order to move the data to the other table and then come back and delete it off the source table thereby allowing it to flow through replication.

alter replication triggers

I have a customer application where I utilize merge replication between
sqlserver2000 and pocketpc's that are running sqlserverce2.0 (vb.net 2003).
It appears that the customer has some tables that they use to keep track of
who modifies the data in certain tables. they want the same functionality
from me.
Since I'm utilizing replication I'm not 100% sure how to accomplish this.
I looked at the trigger that replication creates for on insert . I was
thinking I could add the code necesary to populate the log table there, but
am afraid of screwing up the replication.
I'm not very strong on triggers, anybody have any ideas/suggestions?
Thanks
In SQL Server 2000 you can have multiple triggers on a table, so I'd just
create your own separate ones which can be added using sp_addscriptexec or
as part of the article's properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Alloocation Errors in Database

I have a customer, who had a problem with their RAID which among others was
hosting their database. They managed to get the data recovered but the
database is facing a lot of problems. In their application they get the
following error :
"Could not continue scan with NOLOCK due to data movement". I get the same
error trying query some of the tables and I get a lot of errors about
allocation errors. I've tried CheckDB and CheckTable. Both reporting that
the errors are so severe that they are unrepairable. So my conclusion is
that the only solution is to restore the latest backup or is there any other
options.. The reason I'm asking is that my customer, of course, doesn't have
a backup!
It come to my mind that maybe a rebuld of the indexes could do the trick ?
Is it worth a try ?
Any suggestions ?
It's a SQL 2005 SP2.
Regards:)
Bobby HenningsenBobby
Remove NOLOCK hint from the queries
http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>I have a customer, who had a problem with their RAID which among others was
> hosting their database. They managed to get the data recovered but the
> database is facing a lot of problems. In their application they get the
> following error :
> "Could not continue scan with NOLOCK due to data movement". I get the same
> error trying query some of the tables and I get a lot of errors about
> allocation errors. I've tried CheckDB and CheckTable. Both reporting that
> the errors are so severe that they are unrepairable. So my conclusion is
> that the only solution is to restore the latest backup or is there any
> other
> options.. The reason I'm asking is that my customer, of course, doesn't
> have
> a backup!
> It come to my mind that maybe a rebuld of the indexes could do the trick ?
> Is it worth a try ?
> Any suggestions ?
> It's a SQL 2005 SP2.
> Regards:)
> Bobby Henningsen
>|||Hi Uri,
thanks for your answer. I'm very aware of this solution.. My problem is that
it's not the case here.. When using a simple "Select * from table" I get the
error. I'm not using NOLOCK and I'm not using "Read Uncommitted". This has
to be about allocation. And as you can se it's not even possible to run
CheckDB.. So far I've found a couple of tables with this problem.. And
CheckTable won't work, either.
All I wanna know is what my options are :) 'cause if only option is to
restore from a backup I know that my customer is pretty f.... :)
Bobby :)
Bobby Henningsen
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSN0feV5HHA.3940@.TK2MSFTNGP05.phx.gbl...
> Bobby
> Remove NOLOCK hint from the queries
> http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
>
> "Bobby Henningsen" <bobhen@.mail.dk> wrote in message
> news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>>I have a customer, who had a problem with their RAID which among others
>>was
>> hosting their database. They managed to get the data recovered but the
>> database is facing a lot of problems. In their application they get the
>> following error :
>> "Could not continue scan with NOLOCK due to data movement". I get the
>> same
>> error trying query some of the tables and I get a lot of errors about
>> allocation errors. I've tried CheckDB and CheckTable. Both reporting that
>> the errors are so severe that they are unrepairable. So my conclusion is
>> that the only solution is to restore the latest backup or is there any
>> other
>> options.. The reason I'm asking is that my customer, of course, doesn't
>> have
>> a backup!
>> It come to my mind that maybe a rebuld of the indexes could do the trick
>> ? Is it worth a try ?
>> Any suggestions ?
>> It's a SQL 2005 SP2.
>> Regards:)
>> Bobby Henningsen
>|||Bobby
It is really sad that he does not backup of the database as in that case it
would be better solution.
Since you have identified 'problematic' tables try to delete all indexes on
that table a re-create again See if it helped you
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:F1505CF4-E8EC-4F51-871C-2701374326C1@.microsoft.com...
> Hi Uri,
> thanks for your answer. I'm very aware of this solution.. My problem is
> that it's not the case here.. When using a simple "Select * from table" I
> get the error. I'm not using NOLOCK and I'm not using "Read Uncommitted".
> This has to be about allocation. And as you can se it's not even possible
> to run CheckDB.. So far I've found a couple of tables with this problem..
> And CheckTable won't work, either.
> All I wanna know is what my options are :) 'cause if only option is to
> restore from a backup I know that my customer is pretty f.... :)
> Bobby :)
> Bobby Henningsen
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uSN0feV5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Bobby
>> Remove NOLOCK hint from the queries
>> http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
>>
>> "Bobby Henningsen" <bobhen@.mail.dk> wrote in message
>> news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>>I have a customer, who had a problem with their RAID which among others
>>was
>> hosting their database. They managed to get the data recovered but the
>> database is facing a lot of problems. In their application they get the
>> following error :
>> "Could not continue scan with NOLOCK due to data movement". I get the
>> same
>> error trying query some of the tables and I get a lot of errors about
>> allocation errors. I've tried CheckDB and CheckTable. Both reporting
>> that
>> the errors are so severe that they are unrepairable. So my conclusion is
>> that the only solution is to restore the latest backup or is there any
>> other
>> options.. The reason I'm asking is that my customer, of course, doesn't
>> have
>> a backup!
>> It come to my mind that maybe a rebuld of the indexes could do the trick
>> ? Is it worth a try ?
>> Any suggestions ?
>> It's a SQL 2005 SP2.
>> Regards:)
>> Bobby Henningsen
>>
>|||Hi Bobby!
I noticed this in the MCT group, but I felt I didn't have much to suggest. However, just a few
thoughts:
Be prepared for "restore from backup" alternative. Yes, I hear what you are saying, no backups. But
this is at the psychological level, to set the expectation level right for your customer.
The database is most probably toast. So I'd export as much as possible into a new database. Say you
have for instance a SELECT INTO to the other database. Now, SQL Server will stop when it encounters
the physical corruption. So, for the corrupt tables, you have to work in steps. Like forcing an
index forwards and then backwards, and stopping before the corruption with a WHERE clause. Depending
on where the corruption exists, this might not even be doable (perhaps access to the data pages
isn't allowed at all because of the corruption). Looking at it from the bright side, you will tick
income, as you sit and do this... ;-). This work can be anything from mundane to a complete
nightmare or even not doable (depending of type of corruption, complexity and size of database).
I guess you can Google on tools to salvage a corrupt database. I know of one such tool
(http://www.officerecovery.com/mssql/). I've never used any such tools myself...
Open a case with MS support. Whether or not this will get you further than working on your own, I
don't know. But in most cases, the $'s for a support case is dwarfed by the value of the data.
Bobby, can you drop me an email? I have one private matter I like to talk to you about. See MCT
newsgroup if you can't figure out my email address... ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:F1505CF4-E8EC-4F51-871C-2701374326C1@.microsoft.com...
> Hi Uri,
> thanks for your answer. I'm very aware of this solution.. My problem is that it's not the case
> here.. When using a simple "Select * from table" I get the error. I'm not using NOLOCK and I'm not
> using "Read Uncommitted". This has to be about allocation. And as you can se it's not even
> possible to run CheckDB.. So far I've found a couple of tables with this problem.. And CheckTable
> won't work, either.
> All I wanna know is what my options are :) 'cause if only option is to restore from a backup I
> know that my customer is pretty f.... :)
> Bobby :)
> Bobby Henningsen
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uSN0feV5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Bobby
>> Remove NOLOCK hint from the queries
>> http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
>>
>> "Bobby Henningsen" <bobhen@.mail.dk> wrote in message
>> news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>>I have a customer, who had a problem with their RAID which among others was
>> hosting their database. They managed to get the data recovered but the
>> database is facing a lot of problems. In their application they get the
>> following error :
>> "Could not continue scan with NOLOCK due to data movement". I get the same
>> error trying query some of the tables and I get a lot of errors about
>> allocation errors. I've tried CheckDB and CheckTable. Both reporting that
>> the errors are so severe that they are unrepairable. So my conclusion is
>> that the only solution is to restore the latest backup or is there any other
>> options.. The reason I'm asking is that my customer, of course, doesn't have
>> a backup!
>> It come to my mind that maybe a rebuld of the indexes could do the trick ? Is it worth a try ?
>> Any suggestions ?
>> It's a SQL 2005 SP2.
>> Regards:)
>> Bobby Henningsen
>>
>|||On Thu, 23 Aug 2007 11:33:05 +0300, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Since you have identified 'problematic' tables try to delete all indexes on
>that table a re-create again See if it helped you
I would try that on a COPY of the database, but I sure would not want
to try it on the original. I would want to preserve the original
untouched - or at least with no more changes - and only experiment
with copies.
Roy Harvey
Beacon Falls, CT