The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.
How to suspend replication to a table with MSA database replication
Hi,
I am working with Sybase Replication 12.6 using MSA. There are reports that run against about 60 tables in the replicated database.
The application team wants to suspend updates to those tables while the reports are running. The report takes approximately one hour.
There are about 100 other critical tables in this database and they prefer not to suspend the connection for the entire database.
It seems I can create a 2nd database and define a subscription but there are a few tables that are extremely large that are not part of the report.
If I define a subscription for a 2nd replicated database, I need to perform a dump/load- so this replicated database will need to be as large
as the source database and unfortunately with Sybase 12.6, I can't shrink the database - totally wasted space.
Basically, what I need in addition to my MSA replicated database is a snapshot of the database or a subset of the database.
Is there a way to suspend updates for only the tables in the report for my current replicated database?
I thought about creating a database at primary with only views for the tables in the report then trying to replicate that database.
Then I can suspend connections to that database. Any reason this will not work?
Any other ideas on how to accomplish this?
THANK YOU!!!
I am working with Sybase Replication 12.6 using MSA. There are reports that run against about 60 tables in the replicated database.
The application team wants to suspend updates to those tables while the reports are running. The report takes approximately one hour.
There are about 100 other critical tables in this database and they prefer not to suspend the connection for the entire database.
It seems I can create a 2nd database and define a subscription but there are a few tables that are extremely large that are not part of the report.
If I define a subscription for a 2nd replicated database, I need to perform a dump/load- so this replicated database will need to be as large
as the source database and unfortunately with Sybase 12.6, I can't shrink the database - totally wasted space.
Basically, what I need in addition to my MSA replicated database is a snapshot of the database or a subset of the database.
Is there a way to suspend updates for only the tables in the report for my current replicated database?
I thought about creating a database at primary with only views for the tables in the report then trying to replicate that database.
Then I can suspend connections to that database. Any reason this will not work?
Any other ideas on how to accomplish this?
THANK YOU!!!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Hi,
Care to elaborate on your response? Are you just saying tune the report to get it to run in less time?
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
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?
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."
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.
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."
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."
You would indeed:
For materialisation... yes if you do this outside the replication system (i.e. bcp) then replication doesn't know about it, so you'd have to suspend replication into the replicate/middle database while you were bcp'ing to the reporting database. But you can do this entirely within the replication system. What do we care if it takes a couple of days to run? Keep running reports against the replicate/middle database like you are today, until the materialisation is done.
If you are using ASE 16.0.2 and RS 16.0.2 you have the exciting option of "direct materialisation" available to you. This is much faster and much more convenient. It does not need any special configuration or tuning, it is just a bit of extra syntax on the create database subscription command. The manuals are pretty clear on it.
In the solution I propose you cannot dump and load from the middle/replicate to the third/reporting database, because we make the reporting database much smaller - only as required to hold the data required for reporting.
- Create the new reporting database (it can be in the same ASE as the replicate database, or a different ASE on the same host, or a different ASE on a different host).
- Create all objects in that database needed for the reports (tables, indexes, views, and procedures). You do not need triggers, constraints, defaults, or referential integrity because all of that was taken care of in the original database.
- You create a repdef against the replicate database, for the reporting tables only.
- You mark those tables for replication. (They won't currently be.)
- You create a rep agent in the replicate (middle) database; it doesn't currently have one because it only receives replication and does not send replication anywhere.
- If you ever refresh the replication from the original, you must repeat the above two steps.
- You create a db sub, against this new db repdef.
For materialisation... yes if you do this outside the replication system (i.e. bcp) then replication doesn't know about it, so you'd have to suspend replication into the replicate/middle database while you were bcp'ing to the reporting database. But you can do this entirely within the replication system. What do we care if it takes a couple of days to run? Keep running reports against the replicate/middle database like you are today, until the materialisation is done.
If you are using ASE 16.0.2 and RS 16.0.2 you have the exciting option of "direct materialisation" available to you. This is much faster and much more convenient. It does not need any special configuration or tuning, it is just a bit of extra syntax on the create database subscription command. The manuals are pretty clear on it.
In the solution I propose you cannot dump and load from the middle/replicate to the third/reporting database, because we make the reporting database much smaller - only as required to hold the data required for reporting.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Distributed File System Replication (DFSR)
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
that'll be way less work than the direction you are going
the application team obviously has no idea of how a database works