Impact on replication when a server goes into standby mode

We have the following scenario:

Server A replicates Database A to Server B.

Server C has Database A on it as well, but in standby mode. We are applying the transaction logs generated by Database A on Server A to the database on Server C leaving it in standby mode each time.

Let's say we had planned maintenance for Server Aand dumped the last set of transactions on Server A in standby mode to be applied to to Server C. What happens to the replica on Server B? When I start to use Server C, can I backup its transactions and apply them to Server A, and then have those transactions replicated to Server B? And then what do I do when the maintenance is complete so that I can swithc back to Server A and have the replication continue on as before the maintenance to Server B?

Thanls

In SQL 2000 unless you

1) have configured Server C to also ship the master, msdb, distirbution and publication databases
2) restore the last log with the keep_replication switch
3) take Server A completely offline
4) rename ServerC to ServerA and reboot ServerC(now ServerA)
5) run sp_replrestart
6) use the continue on data consistency error profile

it will not work.

If you also follow the sequence in a failback scenario it will also work.

In SQL 2005 you can configure Server A and C as log shipping failover partners (use a remote distributor and set the publisherfailoverpartner accordingly).

Note that it is possible to do the following in SQL 2000

1) ship logs to ServerC from Server A.
2) on failover restore the tail of A to C with the keep_replication switch
3) use the sp_replrestart command to pick up where you left off

There is more potential exposure to data loss with the above method.

impact of polling interval

Hi,
We have a setup with merge replication and polling interval of 10
seconds.
Frequently merge replication agent stops with deadlocks.
On another system we have default polling interval of 60 seconds. this
system does not
have the above issue.
Is it because of low value of polling interval ? what should be the
value of this parameter and waht is the impact of this parameter
change?
rgds,
amit
Basically it means it reads the msmerge_contents, and msmerge_tombstone
tables (among others) every 10 seconds looking for changes. It does sound
like this setting is not optimal for your topology. I would use the default
of 60 seconds.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mitsql" <amit.nimje@.tcs.com> wrote in message
news:1141010206.296301.156060@.j33g2000cwa.googlegr oups.com...
> Hi,
> We have a setup with merge replication and polling interval of 10
> seconds.
> Frequently merge replication agent stops with deadlocks.
> On another system we have default polling interval of 60 seconds. this
> system does not
> have the above issue.
> Is it because of low value of polling interval ? what should be the
> value of this parameter and waht is the impact of this parameter
> change?
> rgds,
> amit
>
|||Thanks a lot for the reply can you tell me if setting expiry as 14 days
can also have similar impact?

Impact of Existing replication

Scenario.
A db published for transactional replication will be backed up and restored
on a server in a different facility. There, all new replication will be
defined (completely different articles, completely different targets)
Question.
Will pre-existing replication prevent correct functioning of the db at the
time of restore and new replication being defined? Dropping replication
prior to backing up the db is not an option.
Thank you for your help
Oscar
Once restored, execute sp_removedbreplication @.dbname = 'MyPubDB', then
reconfigure replication...
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Oscar" wrote:

> Scenario.
> A db published for transactional replication will be backed up and restored
> on a server in a different facility. There, all new replication will be
> defined (completely different articles, completely different targets)
> Question.
> Will pre-existing replication prevent correct functioning of the db at the
> time of restore and new replication being defined? Dropping replication
> prior to backing up the db is not an option.
> Thank you for your help
> Oscar
>
|||Actually you want to execute:
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Chris" wrote:
[vbcol=seagreen]
> Once restored, execute sp_removedbreplication @.dbname = 'MyPubDB', then
> reconfigure replication...
> ChrisB
> MCDBA OCP
> www.MyDatabaseAdmin.com
> "Oscar" wrote:

impact of changing recovery model on a database that is currently"live"

I'm running sql server 2005 standard edition. We have given up on sql
server replication for various reasons and are gonig to implement
transaction log shipping. I need to switch the database to "Full"
recovery model but owing to a fascinating combination of circumstances
and indecisiveness the server has had replication disabled before the
new backup stratgey is in place. I don't want to wait another day
before I get a replication strategy going to I want to go to FULl
model right NOW. However the databse is in use and I don't want things
to be disrupted. The database server is only lightly loaded (8 cores,
only one running at any one time above 10% and disk queue about 0.1)
and a few seconds of latency is OK..
is it safe to switch recovery model to FULL during working hours on a
production database?
thanks,
sam
sam,
Switching to full mode will not add appreciably to the load on your server,
but it will keep the logs until they are backed up, so you may see grown in
your log (.ldf) files.
When switching to full mode, the first thing to do is a full backup. This
may cause about a 10% hit on the throughput of that server during the time
of the backup. Again, a small price to pay.
Then be sure to schedule regular transaction log backups so that your
transactions are saved and the log files do not grow excessively. (At least
once a day, but depending on your work and its critical nature it could be
as often as every 5 minutes.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:6ad20370-72c2-4fec-b623-81e35b63bc9d@.u69g2000hse.googlegroups.com...
> I'm running sql server 2005 standard edition. We have given up on sql
> server replication for various reasons and are gonig to implement
> transaction log shipping. I need to switch the database to "Full"
> recovery model but owing to a fascinating combination of circumstances
> and indecisiveness the server has had replication disabled before the
> new backup stratgey is in place. I don't want to wait another day
> before I get a replication strategy going to I want to go to FULl
> model right NOW. However the databse is in use and I don't want things
> to be disrupted. The database server is only lightly loaded (8 cores,
> only one running at any one time above 10% and disk queue about 0.1)
> and a few seconds of latency is OK..
> is it safe to switch recovery model to FULL during working hours on a
> production database?
> thanks,
> sam
|||ah thanks for that russell, the backup was also a worry.
but i was wondering about the actual moment of switch over, will that
cause any freeze or hang ups?
|||No, basically I understand it does two things.
1. Stops truncating the log
2. Increases the amount of logging done for BULK INSERTs. (If you don't
want this behaviour, the Bulk-Logged recovery model is similar to FULL, but
gives reduced coverage for the bulk inserts.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:dde0ef2e-c103-48fd-a5f7-df76652da702@.b1g2000hsg.googlegroups.com...
> ah thanks for that russell, the backup was also a worry.
> but i was wondering about the actual moment of switch over, will that
> cause any freeze or hang ups?
|||thanks for your advice. the switch over was completed with no fuss and
took only a few seconds..
sam
sql

impact of changing recovery model on a database that is currently

I'm running sql server 2005 standard edition. We have given up on sql
server replication for various reasons and are gonig to implement
transaction log shipping. I need to switch the database to "Full"
recovery model but owing to a fascinating combination of circumstances
and indecisiveness the server has had replication disabled before the
new backup stratgey is in place. I don't want to wait another day
before I get a replication strategy going to I want to go to FULl
model right NOW. However the databse is in use and I don't want things
to be disrupted. The database server is only lightly loaded (8 cores,
only one running at any one time above 10% and disk queue about 0.1)
and a few seconds of latency is OK..
is it safe to switch recovery model to FULL during working hours on a
production database?
thanks,
samsam,
Switching to full mode will not add appreciably to the load on your server,
but it will keep the logs until they are backed up, so you may see grown in
your log (.ldf) files.
When switching to full mode, the first thing to do is a full backup. This
may cause about a 10% hit on the throughput of that server during the time
of the backup. Again, a small price to pay.
Then be sure to schedule regular transaction log backups so that your
transactions are saved and the log files do not grow excessively. (At least
once a day, but depending on your work and its critical nature it could be
as often as every 5 minutes.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:6ad20370-72c2-4fec-b623-81e35b63bc9d@.u69g2000hse.googlegroups.com...
> I'm running sql server 2005 standard edition. We have given up on sql
> server replication for various reasons and are gonig to implement
> transaction log shipping. I need to switch the database to "Full"
> recovery model but owing to a fascinating combination of circumstances
> and indecisiveness the server has had replication disabled before the
> new backup stratgey is in place. I don't want to wait another day
> before I get a replication strategy going to I want to go to FULl
> model right NOW. However the databse is in use and I don't want things
> to be disrupted. The database server is only lightly loaded (8 cores,
> only one running at any one time above 10% and disk queue about 0.1)
> and a few seconds of latency is OK..
> is it safe to switch recovery model to FULL during working hours on a
> production database?
> thanks,
> sam|||ah thanks for that russell, the backup was also a worry.
but i was wondering about the actual moment of switch over, will that
cause any freeze or hang ups?|||No, basically I understand it does two things.
1. Stops truncating the log
2. Increases the amount of logging done for BULK INSERTs. (If you don't
want this behaviour, the Bulk-Logged recovery model is similar to FULL, but
gives reduced coverage for the bulk inserts.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:dde0ef2e-c103-48fd-a5f7-df76652da702@.b1g2000hsg.googlegroups.com...
> ah thanks for that russell, the backup was also a worry.
> but i was wondering about the actual moment of switch over, will that
> cause any freeze or hang ups?|||thanks for your advice. the switch over was completed with no fuss and
took only a few seconds..
sam

Immediate Updating Subscriber not Queuing updates

Hey all,
I have a problem with replication that I was hoping someone might be able to
shed some light on. Heres the scenario.
Server A, setup as a publisher + distributor. A publication was created and
marked for transaction replication with immediate updating with queued as a
failover.
Server B is the only subscriber. I had to run sp_link_publication in order
to get it to work.
I can successfully replication changes from A - B, and B - A. If B goes
down, and comes back up, it will re-sync no problem. My problem is that when
A goes down, I am not getting anything saved into the queue. When I try to
do any updating, all I get is 'SQL Server does not exist, etc'. There are no
other triggers on the table.
Thoughts?
One more thing, this is SQL 2000 SP3 (.818)
"MJM" wrote:

> Hey all,
> I have a problem with replication that I was hoping someone might be able to
> shed some light on. Heres the scenario.
> Server A, setup as a publisher + distributor. A publication was created and
> marked for transaction replication with immediate updating with queued as a
> failover.
> Server B is the only subscriber. I had to run sp_link_publication in order
> to get it to work.
> I can successfully replication changes from A - B, and B - A. If B goes
> down, and comes back up, it will re-sync no problem. My problem is that when
> A goes down, I am not getting anything saved into the queue. When I try to
> do any updating, all I get is 'SQL Server does not exist, etc'. There are no
> other triggers on the table.
> Thoughts?
|||you have to manually failover your subscriber from using immediate updating
to using queued updating. There is no automatic failover.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MJM" <MJM@.discussions.microsoft.com> wrote in message
news:EDD81CC4-9696-4AAB-A468-B8734DC08473@.microsoft.com...[vbcol=seagreen]
> One more thing, this is SQL 2000 SP3 (.818)
> "MJM" wrote:
able to[vbcol=seagreen]
and[vbcol=seagreen]
as a[vbcol=seagreen]
order[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
are no[vbcol=seagreen]

immediate updating isnt working

Im doing some testing on Immediate Updating Transactional Replication. I
generated a script to drop the Subscription and Article to a particular
table to add an Identity column on it. After that, I re-added the table to
the Publication and re-snapshotted the table. No problem and all is good at
this point. However now when I insert a record into the Subscriber, the row
isn't getting back to the Publisher *for this table only.* It is working for
other tables though. I scripted out the sp_addarticle for both this table
and a table that the immediate updating is working properly on and they are
identicle with the exception of the names. Whats the dealio? All ideas are
appreciated.
SQL2K SP3
TIA, ChrisR
I figured out whats up. The triggers for Insert, Update, and Deletes arent
being created like they were during the original snapshot. Does anyone know
why?
"ChrisR" <bla@.noemail.com> wrote in message
news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
> Im doing some testing on Immediate Updating Transactional Replication. I
> generated a script to drop the Subscription and Article to a particular
> table to add an Identity column on it. After that, I re-added the table to
> the Publication and re-snapshotted the table. No problem and all is good
at
> this point. However now when I insert a record into the Subscriber, the
row
> isn't getting back to the Publisher *for this table only.* It is working
for
> other tables though. I scripted out the sp_addarticle for both this table
> and a table that the immediate updating is working properly on and they
are
> identicle with the exception of the names. Whats the dealio? All ideas are
> appreciated.
> --
> SQL2K SP3
> TIA, ChrisR
>
|||how did you resync after making changes to your subscription.
I think it would be best to recreate your snapshot - it sounds like your
metadata is out of sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I figured out whats up. The triggers for Insert, Update, and Deletes arent
> being created like they were during the original snapshot. Does anyone
know[vbcol=seagreen]
> why?
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
> at
> row
> for
table[vbcol=seagreen]
> are
are
>
|||I re-snapshotted the table.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> how did you resync after making changes to your subscription.
> I think it would be best to recreate your snapshot - it sounds like your
> metadata is out of sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
arent[vbcol=seagreen]
> know
I[vbcol=seagreen]
particular[vbcol=seagreen]
table[vbcol=seagreen]
> to
good[vbcol=seagreen]
the[vbcol=seagreen]
working[vbcol=seagreen]
> table
they
> are
>
|||I realize that - but I think you need to re-snapshot the entire publication.
I had problems like this with merge replication, which could only be solved
by a complete resnapshotting and re-synchronization.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I re-snapshotted the table.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...
> arent
Replication.[vbcol=seagreen]
> I
> particular
> table
> good
> the
> working
> they
ideas
>
|||I was afraid of that. Just wanted to make sure we were on the same page.
Thanks Hillary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#BL4sBwLFHA.2796@.tk2msftngp13.phx.gbl...
> I realize that - but I think you need to re-snapshot the entire
publication.
> I had problems like this with merge replication, which could only be
solved[vbcol=seagreen]
> by a complete resnapshotting and re-synchronization.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <bla@.noemail.com> wrote in message
> news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...
your[vbcol=seagreen]
anyone[vbcol=seagreen]
> Replication.
is[vbcol=seagreen]
Subscriber,[vbcol=seagreen]
this
> ideas
>
sql

Immediate Synchronization?

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is
automatically applied to server B, rather than the replication happening at
set intervals?
Thanks.
Neil,
this is possible from subscriber to publisher in the case of immediate
updating subscribers. In other cases you have to accept a degree of latency,
which can be minimised by optimisation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Neil" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> Is it possible in SQL Server to have replication happen immediately as
> changes are made. That is, a change is made on server A, and that change
is
> automatically applied to server B, rather than the replication happening
at
> set intervals?
> Thanks.
>
|||I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Consider distributed transactions for this. You can also set your polling
> interval on your log reader and distribution agent to 1s, and your latency
> will be around 10-20s.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Neil" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> is
> at
>
|||Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @.@.errornum<>0
rollback tran
else
commit tran
It can be done using MS DTC as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Neil" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...[vbcol=seagreen]
> I'm using an Access 2000 front end which updates a lot of data through the
> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
> every situation. Anything else that can immediately update one server from
> the other?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
polling[vbcol=seagreen]
latency[vbcol=seagreen]
change[vbcol=seagreen]
happening
>
|||What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Distributed transactions are a coded solution where you do something like
> this
> begin trans
> insert into server1.database1.dbo.table1 (charcol) values ('test')
> insert into msaccessdb.table1 (charcol) values ('test')
> if @.@.errornum<>0
> rollback tran
> else
> commit tran
> It can be done using MS DTC as well.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Neil" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
> polling
> latency
> change
> happening
>
|||Neil wrote:
> What I mean is that I'm using bound forms in the MDB to a large extent.
> Updating of the data through the forms is handled automatically through the
> ODBC driver without code.
>
Hi Neil,
I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?
If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).
So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...
Damien

Immediate Synchronization?

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is
automatically applied to server B, rather than the replication happening at
set intervals?

Thanks.Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Neil" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> Is it possible in SQL Server to have replication happen immediately as
> changes are made. That is, a change is made on server A, and that change
is
> automatically applied to server B, rather than the replication happening
at
> set intervals?
> Thanks.|||I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?

Thanks.

"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Consider distributed transactions for this. You can also set your polling
> interval on your log reader and distribution agent to 1s, and your latency
> will be around 10-20s.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Neil" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
>> Is it possible in SQL Server to have replication happen immediately as
>> changes are made. That is, a change is made on server A, and that change
> is
>> automatically applied to server B, rather than the replication happening
> at
>> set intervals?
>>
>> Thanks.
>>
>>|||Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @.@.errornum<>0
rollback tran
else
commit tran

It can be done using MS DTC as well.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Neil" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
> I'm using an Access 2000 front end which updates a lot of data through the
> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
> every situation. Anything else that can immediately update one server from
> the other?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> > Consider distributed transactions for this. You can also set your
polling
> > interval on your log reader and distribution agent to 1s, and your
latency
> > will be around 10-20s.
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> > "Neil" <nospam@.nospam.net> wrote in message
> > news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> >> Is it possible in SQL Server to have replication happen immediately as
> >> changes are made. That is, a change is made on server A, and that
change
> > is
> >> automatically applied to server B, rather than the replication
happening
> > at
> >> set intervals?
> >>
> >> Thanks.
> >>
> >>|||What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.

"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Distributed transactions are a coded solution where you do something like
> this
> begin trans
> insert into server1.database1.dbo.table1 (charcol) values ('test')
> insert into msaccessdb.table1 (charcol) values ('test')
> if @.@.errornum<>0
> rollback tran
> else
> commit tran
> It can be done using MS DTC as well.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Neil" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
>> I'm using an Access 2000 front end which updates a lot of data through
>> the
>> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
>> every situation. Anything else that can immediately update one server
>> from
>> the other?
>>
>> Thanks.
>>
>> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> > Consider distributed transactions for this. You can also set your
> polling
>> > interval on your log reader and distribution agent to 1s, and your
> latency
>> > will be around 10-20s.
>>> > --
>> > Hilary Cotter
>> > Looking for a SQL Server replication book?
>> > http://www.nwsu.com/0974973602.html
>>> > Looking for a FAQ on Indexing Services/SQL FTS
>> > http://www.indexserverfaq.com
>> > "Neil" <nospam@.nospam.net> wrote in message
>> > news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
>> >> Is it possible in SQL Server to have replication happen immediately as
>> >> changes are made. That is, a change is made on server A, and that
> change
>> > is
>> >> automatically applied to server B, rather than the replication
> happening
>> > at
>> >> set intervals?
>> >>
>> >> Thanks.
>> >>
>> >>
>>>>
>>|||Neil wrote:
> What I mean is that I'm using bound forms in the MDB to a large extent.
> Updating of the data through the forms is handled automatically through the
> ODBC driver without code.
Hi Neil,

I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?

If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).

So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...

Damien

Images (jpgs) and Replication

Hi,
At the moment we have apx 15 images per item (apx 5000/client).
Currently, these are sent to us from the client to our lan so they can be
QC'd.
They go through the QC process and a database pointer is allocated to the
database item (in tblItemImages ( itemID INT, ImageFileName varchar(255),
ImageFolderID INT )
tblItemImages is uploaded to the webserver on a scheduled basis (noon &
midnight) and the images are FTP sync'd at the same time.
We may have a requirement to upload the images every hour (or every few
minutes?), in which case the ftp sync becomes a large overhead.
My question is, how feasable is it to store images in SQL Server and have
them replicated to a remote server (dedicated 1kbps line).
The images are apx 25-50KB each and only 3 of the 15 images are needed on
the webserver.
Getting the images in and out of the database shouldn't be so much of an
issue.Rebecca,
it shouldn't be a problem. I've done that on a database with loads of
image columns running merge replication with >20 subscribers, some of
them connected by a standard ISDN connection. So in your case it should
work without too much problems. But then again in my experience
replication always creates some (un)expected problems.
Markus|||Moving from storing the images as files to storing them in image columns
would require modifications to the database model and website programming.
Why is the FTP sync becomming an overhead; does the FTP utility support a
feature for only transferring files that don't exist on the destination or
have an updated timestamp?
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:434e3f98$0$136$7b0f0fd3@.mistral.news.newnet.co.uk...
> Hi,
> At the moment we have apx 15 images per item (apx 5000/client).
> Currently, these are sent to us from the client to our lan so they can be
> QC'd.
> They go through the QC process and a database pointer is allocated to the
> database item (in tblItemImages ( itemID INT, ImageFileName varchar(255),
> ImageFolderID INT )
> tblItemImages is uploaded to the webserver on a scheduled basis (noon &
> midnight) and the images are FTP sync'd at the same time.
> We may have a requirement to upload the images every hour (or every few
> minutes?), in which case the ftp sync becomes a large overhead.
> --
> My question is, how feasable is it to store images in SQL Server and have
> them replicated to a remote server (dedicated 1kbps line).
> The images are apx 25-50KB each and only 3 of the 15 images are needed on
> the webserver.
>
> Getting the images in and out of the database shouldn't be so much of an
> issue.
>
>|||Yes it does, but the number of images is enormous and it takes ages just to
bring down the image file names and timestamps to work out which ones need
to be uploaded.
images*items*clients
3*5000*5 = 75000
Altho we do it per client at the moment, so it's only 15000 at a time, but
it's still alot of data to bring down.
"JT" <someone@.microsoft.com> wrote in message
news:uu39a8%23zFHA.3812@.TK2MSFTNGP09.phx.gbl...
> Moving from storing the images as files to storing them in image columns
> would require modifications to the database model and website programming.
> Why is the FTP sync becomming an overhead; does the FTP utility support a
> feature for only transferring files that don't exist on the destination or
> have an updated timestamp?

Ignoring Source Field

Hi,

I am implementing a Transactional Replication btwn two SQL 2000 servers, which is using Data Transform services (DTS package) to manipulate the data. I need to ignore a source column from replication but that column field value needs to be appended with other field column.

For ex. Let two source fields be named FirstName and LastName. In the target database i need to merge those field values into a Single field called CustomerName. Also i don't want the two source fields to be replicated in the target.

I tried to ignore the field by unchecking the Column Transformation and Mapping screen, but then unable to append the field value to the other field.

Is there any other way to do the above?

Thanks in advance.

urs,
T. Jayakumar

I don't think there is a way to use a field in a transformation but not include it in the package data.

Phil Garding