replication from backup file

[som...@js.com]

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...

[Hilary Cotter]

Can you set up a separate publication/subscription for these tables?

<som...@js.com> wrote in message
news:O$J7kdTaK...@TK2MSFTNGP02.phx.gbl...

[som...@js.com]

Thanks Hilary,

I can't setup publication or those not frequence changed tables, because
they don't have primary key and it's a big jobs to modify for now.
so how to handle those tables? Is it log shiping + replication good for
this? Please advice again.

"Hilary Cotter" <hilary...@gmail.com> wrote in message
news:B871156B-14CD-4907...@microsoft.com...

[Paul Ibison]

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

[som...@js.com]

Thanks Paul,

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)> wrote in
message

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.

[Paul Ibison]

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 doesn't 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

[som...@js.com]

Thanks.

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)> wrote in

message news:822C6DC3-DFE3-4E76...@microsoft.com...

Is it transactional subscrible read only?

I have setup transactional already, now If I change publication to snapshot
to Backup, what's the procedures to reconfig subscriber can get the backup
and begin to sync.
Please advice.

[Paul Ibison]

Not too sure what you are proposing "publication to snapshot to Backup"..
can you clarify pls.
Thanks,
Paul

[som...@js.com]

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)> wrote in

message news:2D468190-6E7B-407F...@microsoft.com...

> Not too sure what you are proposing "publication to snapshot to Backup"..
> can you clarify pls.

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.

[Paul Ibison]

Sorry, I'm 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

[som...@js.com]

Thanks Paul,

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)> wrote in
message>

Sorry, I'm still not clear here.....
> (1) does the data on the subscriber need to be read-only or read-write?

Read only

> (2) if read-write, do you want the changes to go to the publisher or be
> discarded?

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.

> (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.

Some tables/prodecures can't add to articles(no primary...) , it may be a
big changes for me.
So I'm thinking backup file as intial snapshot, if changes made to those
non-sync talbes/procedurs, then re inital the process.

> (4) can data changes conflict between the publisher and subscriber?

No, no need from subscriber back to publisher.

Please advice.

[Paul Ibison]

OK - if it's 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're using this
continually.
If this level of latency is too large the transactional sounds good. It'll
not cope with missing PKs and some schema changes are prohibited, which
might make things a bit tricky for you.
HTH,
Paul Ibison

[som...@js.com]

Thanks paul,

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)> wrote in

message news:uHAuwQhb...@TK2MSFTNGP02.phx.gbl...

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.

[Paul Ibison]

Initialize from backup isn't 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

[som...@js.com]

Thanks Paul,

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)>

That's my problem, some tables can't add to the publication, For those
table, how to transfer/sync to subscriber?

[Paul Ibison]

You would have to use snapshot or merge for these tables, or add a surrogate
PK.
HTH,
Paul Ibison

[som...@js.com]

Thanks Paul,

"Paul Ibison" <Paul....@ReplicationAnswers.Com.(donotspam)> wrote in
message news:D4826987-3A82-4876...@microsoft.com...

> You would have to use snapshot or merge for these tables, or add a
surrogate
> PK.

So will be two publications, one for transaction and one for snapshot?

[Paul Ibison]

Yes.
Regards,
Paul

<som...@js.com> wrote in message
news:%23m4d%23V4bK...@TK2MSFTNGP04.phx.gbl...