on 2021 Feb 10 3:40 PM
I have a remote SQLA 16 database involved in SQL Remote replication that is reaching the maximum 1TB size (4K Page Size, single DB Space) and want to unload and reload to alter the page size and number of DB Spaces. I've tested this and it all works fine, after setting the offsets I was able to receive and send a series of test replication messages.
The issue is it takes two to three days to perform the unload and reload on the hardware I'm working on and I don't want to disable a production system for that long.
I could re-extract but that would interfere with replication for the period of extraction, by procedure we shutdown all external activity on the consolidated when extracting.
Then I had this great idea! I'll unload and reload a backup then apply the transaction logs from the online database, switch the databases and I'm done. Problem is the logs don't apply to the rebuilt database.
Is there something I'm missing, should this work, is there a hidden switch I could use?
In v16, your only option is to apply transactions to a backed up and rebuilt copy of a database is to translate log files to SQL and apply them to the rebuilt database. This will almost certainly change the log offsets where operations were applied and the ending log offset of the transaction log, so it's nor replication/synchronization friendly.
Imagine the process below, which I think (i.e. I haven't actually tested this) will allow you to rebuild a backup of a v16 consolidated database, and while the backup is being rebuilt, you can still make changes to the consolidated database AND allow dbremote to run in RECEIVE ONLY mode, and SQL Remote will continue on it's merry way after the rebuild. It involves doing some unsupported magic that I will NOT explain on the forum, but I think this might work. I've simplified the process by assuming the database contains only cons.db and cons.log in the same directory, but it should easily extrapolate to your environment.
Jim/Volker/Breck : Can you see any issues with this process? The key takeaway here is that by running dbremote in receive only mode during the rebuild, the only updates to the SYSREMOTEUSER table will involve log offsets from the remote databases, not log offsets from the consolidated database.
Reg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Not mentioned, but should be obvious :
Edited my steps. The original steps would have left a log offset gap because I was executing dblog -x -z using the wrong offsets at the wrong time of the rebuild.
No don't think this will work I would think
8. rebuild backup\\cons.db 9. Stop engine hosting prod\\cons.db 10. move prod\\*.* justincase\\*.* ---> Paranoid people are more likely to keep their jobs 11. dbtran -n apply.sql prod\\cons.log 12. dbisql -c dbf\\cons.db read apply.sql 13. erase backup\\cons.log 14. dblog -x 0 -z #### -ir -is backup\\cons.db. ** Last active prod\\cons.log 15. move backup\\cons.db prod\\cons.db 16. *** perform magic on prod\\cons.db so SYSREMOTEUSER matches SYSREMOTEUSER on justincase\\cons.db 17. start engine hosting prod\\cons.db
In my understanding you cannot apply logs from "different databases", and the rebuilt database is different from the original one.
Nevetheless, you might be able to translate the log of the original database to a SQL file and feed that (resp. the statetements done after the rebuild) to the rebuilt database. That should be quite easily doable as long as you prevent any SQL Remote activity after the backup copy is made. (So it would allow to work on with the old production database, which seems to be what you try to achieve.)
I do not know whether there are ways to allow SQL Remote activity in the "old database" after the backup copy is made, but certainly Reg will know.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Volker is correct, you cannot apply a transaction log from a different database to another database.
We solved this problem in v17 with dbunload -ao, which allows you to automate the process of rebuilding an online database against a backup copy of the database, and the applying the logs from the production database to the rebuilt backup, ignoring the "different database" problem, since we're in control of rebuild + applying of logs. This doesn't help you today at v16 though, because the initial database in this process must be v17. It does mean you'll only ever need to do this process once though, since once your DB is upgraded to v17, dbunload -ao will be a great feature for rebuilding large databases.
Reg
Ah, wasn't aware of that big improvement... (yes, sometimes I'm missing those old SA blogs...)
User | Count |
---|---|
71 | |
11 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.