Backup
(1)
Bit
(1)
Database
(1)
PKs
(1)
Thaks
(1)
Developmentenvironment
(1)
Transactional
(1)
Publicaiton
(1)
replication from backup file
Asked By
19-Nov-09 11:43 AM
hi, how do this,
i have server a and server b, a is publication adn b is subscription, i only
select some tables to sync, but other no-selected tables may have some
updates in server a. what is the good way to have complete server b sync
with server a? backup data in server a every week and reintial server b
after the backup and keep transation replicate for the selected tables?
please advice. thanks...
Can you set up a separate publication/subscription for these tables?
Hilary Cotter replied to
20-Nov-09 07:49 AM
Can you set up a separate publication/subscription for these tables?
Thanks Hilary,I cannot setup publication or those not frequence changed
Thanks Hilary,
I cannot setup publication or those not frequence changed tables, because
they do not have primary key and it is a big jobs to modify for now.
so how to handle those tables? Is it log shiping + replication good for
this? Please advice again.
sync
If you need to change the data on the subscriber (edga developmentenvironment
Paul Ibison replied to
21-Nov-09 11:12 AM
If you need to change the data on the subscriber (edga development
environment refresh) then I tend to use snapshot replication out of hours or
backup and restore. Backup and restore is easiest and will not have to be
maintained each time there is a change made to the schema on the main box.
If the data needs to be read-only on server2 then log shipping with standby
mode is a great solution.
HTH,
Paul Ibison
Thanks Paul,"Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Thanks Paul,
message
or
standby
Can I do this: still setup transation replicaiton between two server, every
weekend, fullbacku backup server a nd restore to server b and restart agent
to sync? not sure the agent will know when/where to begin sync. Please
advice.
The problem with using transactional in this way is that the data on
Paul Ibison replied to
23-Nov-09 01:48 PM
The problem with using transactional in this way is that the data on the
subscriber needs to remain read only. Not sure if this is ok for you but in
the case of a dev environment it does not work. The other downside is that
the transactional commands will accumulate on the distributor unless they
are applied to the subscriber and this can massively inflate the database
files.
HTH,
Paul Ibison
Thanks."Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Thanks.
in
Is it transactional subscrible read only?
I have setup transactional already, now If I change publication to snapshot
to Backup, what is the procedures to reconfig subscriber can get the backup
and begin to sync.
Please advice.
Not too sure what you are proposing "publication to snapshot to Backup"..
Paul Ibison replied to
25-Nov-09 02:10 PM
Not too sure what you are proposing "publication to snapshot to Backup"..
can you clarify pls.
Thanks,
Paul
"Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Sorry,
I changed publication to initial from backup. Please advice my procedures
are correct:
1. stop subscriber agent
2. stop publication agent
3. backup full database in publication
4. enable publication agent
5. copy to subscriber server
6. restore database from backup
7. eneable subscriber and sync
Is it right? if next time I change publication tables(not include sync),
then I have to repeat that procedures again?
Is it better way? Thanks.
Sorry, I am still not clear here.....
Paul Ibison replied to
25-Nov-09 03:29 PM
Sorry, I am still not clear here.....
(1) does the data on the subscriber need to be read-only or read-write?
(2) if read-write, do you want the changes to go to the publisher or be
discarded?
(3) when you add new articles to the source database why are you considering
taking a backup? Adding them to the publicaiton will be sufficient, and then
running the snapshot agent to get them transferred over.
(4) can data changes conflict between the publisher and subscriber?
Thaks,
Paul
Thanks Paul,"Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Thanks Paul,
message>
Sorry, I am still not clear here.....
Read only
Read only, bu in case publisher failed, I want to use subscriber data as
production(I may use logshiping later for this). may lost some data.
the subscriber mainly needs to support reporting.
considering
then
Some tables/prodecures cannot add to articles(no primary...) , it may be a
big changes for me.
So I am thinking backup file as intial snapshot, if changes made to those
non-sync talbes/procedurs, then re inital the process.
No, no need from subscriber back to publisher.
Please advice.
OK - if it is a reporting solution then I need to know the required latency.
Paul Ibison replied to
25-Nov-09 04:13 PM
OK - if it is a reporting solution then I need to know the required latency.
If you can cope with a bit of latency (say a day) then I'd use log shipping
and ship the logs out of hours. Standby mode will allow read access.
Mirroring and database snapshots are another posibility. You can create the
snapshots as you need them, but the problem is that the snapshot name will
be changing and so will your connection details if you are using this
continually.
If this level of latency is too large the transactional sounds good. it will
not cope with missing PKs and some schema changes are prohibited, which
might make things a bit tricky for you.
HTH,
Paul Ibison
Thanks paul,"Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Thanks paul,
latency.
shipping
the
Yes, I want to try transactional with inital from backup file, the backup
will do once a week to pickup the missing PKs table and some schema changes,
other sync data can support almost real time reporting.
Please advice my procedures
are correct:
1. stop subscriber agent
2. stop publication agent
3. backup full database in publication
4. enable publication agent
5. copy to subscriber server
6. restore database from backup
7. eneable subscriber and sync
Is it right?
Initialize from backup is not required here tho'.
Paul Ibison replied to
25-Nov-09 04:42 PM
Initialize from backup is not required here tho'. This is for peer-to-peer or
subscribers with a huge amount of data.
Just add any new tables to the publication, run the snapshot agent then the
distribution agent to send them down.
Schema changes will go down automatically to the subscriber.
HTH,
Paul Ibison
Thanks Paul,"Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Thanks Paul,
or
the
That's my problem, some tables cannot add to the publication, For those
table, how to transfer/sync to subscriber?
You would have to use snapshot or merge for these tables, or add a surrogatePK.
Paul Ibison replied to
26-Nov-09 01:36 PM
You would have to use snapshot or merge for these tables, or add a surrogate
PK.
HTH,
Paul Ibison
Thanks Paul,"Paul Ibison" <Paul.Ibison@ReplicationAnswers.Com.
Thanks Paul,
surrogate
So will be two publications, one for transaction and one for snapshot?
Yes.Regards,Paul
Paul Ibison replied to
27-Nov-09 03:18 PM
Yes.
Regards,
Paul