How to suspend replication to a table with MSA database replication

tune the report
that'll be way less work than the direction you are going

the application team obviously has no idea of how a database works

Hi,
Care to elaborate on your response?    Are you just saying tune the report to get it to run in less time?

it doesn't matter towards updates how long the report runs
once the report is started, if written correctly, it won't see any more updates

a report is usually a select query
a select query starts at certain point in time, and all data comes from that point in time
it disregards all changes from later in time

so in that context, why would devs want to stop systems updating items ?

i hope you aren't replicating a database just to run reports
because that would undermine the whole concept

it might be better for performance to run the reports on the replicated db
but you'd have to consider running the report at the correct time, ie when the replication is finished for that report

i could give an example of a report running for 21 hours ...
after we tweaked it, basically rewrote it from the ground up, it took 0.3 second
we deleted all replication/copying stuff and only left the selects in which were needed
replication/copying takes time and is usually not needed for reports

What you've been asked to do can't really be done without breaking replication.

Think about it - replication doesn't copy data, it copies and reapplies transactions, in the order that they committed. Transactions cannot be skipped - that breaks replication consistency and now the replicate doesn't match the source. Transactions cannot be only partially applied - that breaks the definition of a database transaction.

As soon as there is a transaction that writes to any of the tables they care about, you have to pause all replication. Anything else falls into one of the two scenarios above - it breaks transactional consistency (and now the database is entirely worthless), or it breaks replication consistency (and now must be fully resynced from the source).

IF the tables for these reports are entirely separate to every other table in the database - and I seriously doubt it, but let's imagine this is true - then you could define two separate database repdefs for the source and two separate db subscriptions, one to each db repdef. The first db repdef would be for all of the tables in the reports; the second would be for all other tables.

That would allow you to replicate one set independently of the other... but it only works if the two sets of tables have zero overlap. And if they have zero overlap... I would instead suggest you make them separate databases on separate ASEs.

TL;DR - you can strictly speaking stretch what MSA is capable of to do this, but you shouldn't, and you will regret it if you do.

I agree with the first responder - tune the replicate database. This is a standard problem with many standard answers. I would also ask what's the big deal with replication getting one hour behind?

Thanks Joe,

fyi: Here is the request:
Every night at 11 pm we run a scheduled task that runs several stored procedures used to capture the state of the products at that moment in time. The job takes about an hour or so to run, so the state of things can change while we’re running it. Accounting is pushing us to find a better way to take the inventory snapshot each night to reduce any data 'inconsistencies' caused during the long run time.

I agree, the simplest solution is suspend replication for the entire database, run the reports & resume replication.
We definitely will not intentionally skip any transactions.  These are not independent/separate tables - There is relational data in various tables.

The shop treats the current replicated database like it is critical production database so I wanted to provide them with what they need with the least possible impact to the current replicated environment.

But, it seems the best option may be the one you mentioned ...but requires more effort to implement and maintain.
"...then you could define two separate database repdefs for the source and two separate db subscriptions, one to each db repdef. The first db repdef would be for all of the tables in the reports; the second would be for all other tables."

You've said that the tables are not independent or separate though, so you cannot use two separate repdef/subscription pairs. Your database will quickly go out of sync and have incorrect data if you go down that path. It is not an option.

The best you can do without significant report tuning or changing how your databases work is to manually lock the tables needed for the snapshot report: put a BEGIN TRAN and LOCK TABLE ... EXCLUSIVE at the start of the report, and ensure you've locked each table. You can let replication run as normal. It will get as far as it can until it needs to write to one of the tables, and then it will be blocked and will wait for the report to complete.

A better solution is to make the report faster. It seems unlikely they are as fast as they can possibly get. Things to look at here are mostly indexes... the replicate database does not have to have the same indexes as the source, a trick that isn't used often enough.

If they really need to be able to run these reports without any interference with replication and vice versa then I suggest a second database (can be in the same ASE), sized only large enough to fit what's needed for these reports, and replicate out of your current replicate into that, and only for the tables required for the reports.

SOURCE -> MSA (all tables) -> REPLICATE -> MSA (reporting tables only) -> REPORTING

i.e. Report database is a subset of Replicate database. This allows you to always run replication into the replicate, but as required pause the further replication into the reporting database. Because this is a subset of tables it can be much smaller, and doesn't have to be a dump & load compatible size.

Thanks,

I like this solution - but please see question/issue below:
SOURCE -> MSA (all tables) -> REPLICATE -> MSA (reporting tables only) -> REPORTING

Please advise:
I would create/configure new replication database,
create database repdef for Report tables only
create subscription
BUT
How does this subscription materialize?  Do I have to do a dump/load of full database?[/b]
Are there any other options?  I believe if I am doing a bcp ( which will take some time)
I need to manually stop replication and I really need to suspend Production operations-   Sybase DOC includes the following:
"When you use the no materialization method, you can materialize the replicate databases using bcp, dump and load, mount and unmount, or other methods. Because Replication Server does not coordinate the initial database synchronization process, you will likely need to suspend database applications."

Thanks so much Joe!