Hi Luis,

Yes, I have successfully replicated our db from Korea, LA and Philippines.
The first process which is the snapshot during replication setup is the problem here.
You can use some work around such as:
- downloading the snapshot to the same location.
- using ftp server for snapshot.
- restore the full backup, differential backup, and incremental backup then setup the subsciptions and specify not to initialize schema and data.

I'm using PULL subscriptions. You can use Pull/Push subscription.
But I prefer Pull subscriptions.

I have no problem with the speed because we have 3 T1 connection.
If in case there is a network connection broken problem, I have setup and Jobs in sql server to synchronize the replication in the subscriber server.

The following is the script I have used in the Jobs:

declare @lnIsRunning int,
@lcjobname varchar(250)

select @lcjobname = MyJobs_or_any_valid_job_name-6021EA29-5392-4953-854B-2D1C2FCBF8EE'
exec match.dbo.bfsp_isjobnRunning @lcjobname, @lnIsRunning OUTPUT
if @lnIsRunning in(0, 4, 5) -- not running. execute the jobs
exec msdb.dbo.sp_start_job @job_name = @lcjobname

This is my another customize SP (by the way, the sp below is not my idea, i have only see it in the web when I have search in google. I have made a little changes in the code.):

create procedure bfsp_isjobnRunning
@cjobname varchar(250),
@bIs_Running int output
as
begin
set nocount on

-- declare variables
declare @uJob_ID uniqueidentifier,
@nIs_SysAdmin int,
@cJob_Owner sysname,
@nExecution_Status int

-- initialize variables
select @bIs_Running = 0
select @uJob_ID = null
select @nIs_SysAdmin = 0
select @cJob_Owner = null
select @nExecution_Status = 0

-- set job id
select @uJob_ID = (select job_id from msdb..sysjobs where name = @cjobname)
if (@uJob_ID is null)
begin
raiserror('unknown job name', 16, 1)
return 1
end

-- create temporary table
create table #tresults(
uJob_ID uniqueidentifier not null,
nLast_Run_Date int not null,
nLast_Run_Time int not null,
nNext_Run_Date int not null,
nNext_Run_Time int not null,
nNext_Run_Schedule_ID int not null,
nRequested_To_Run int not null,
nRequest_Source int not null,
nRequest_Source_ID sysname null,
nRunning int not null,
nCurrent_Step int not null,
nCurrent_Retry_Attempt int not null,
nJob_State int not null)

-- set sysadmin flag
select @nIs_SysAdmin = isnull(is_srvrolemember(N'sysadmin'), 0)

-- set job owner
select @cJob_Owner = suser_sname()

-- populate #tresults
insert into #tresults
execute master.dbo.xp_sqlagent_enum_jobs @nIs_SysAdmin, @cJob_Owner

-- set execution status
select @nExecution_Status = (select nJob_State from #tresults where uJob_ID = @uJob_ID)

-- set is nRunning flag
select @bIs_Running = @nExecution_Status

-- drop #tresults
if exists (select 1 where (object_id('tempdb..#tresults') is not null) )
drop table #tresults

set nocount off

return 0
end

When you setup a Pull subscription, SQL server will create job in the subscriber server with similar to the following steps:
-Publisher PUBLISHERSERVER -PublisherDB [SAMMPLE_DB] -Distributor [ANYDBSERVER] -SubscriptionType 1 -Subscriber [SUBSCRIBER_DB_SERVER] -SubscriberSecurityMode 0 -SubscriberLogin [VALID_SQL_LOGIN] -SubscriberEncryptedPassword [ANY_ENCRYPTED_TEXT] -SubscriberDB [DESTINATION_DATABASE] -Continuous

Familiarize also Jobs in the Management - SQL Server Agent in the Enterprise Manager.


Once you can successfully run the snapshot there is no problem anymore in the network performance for the next transaction.
The difficult part here is the snapshot process espeacially if you have 50 to 100 Gig of data. I have tried 50 Gig of data and it makes my server hang during the snapshot process. Instead I of doing some snapshot I have backup my database (full, differential, incremental) and download it to other location and restore it. I have sacrifice a lot for that project.
My transactional replication always update the subscriber database everytime there is an update in the publication database.
So I'm using real time replication. I have schedule my the to check if the replication is running every 30 minutes.

Before you start replication it is better to study first the replication concept. You can read some replication guide in Books on-line. By the way to create transactional replication all of your table should have a primary key.

The following link can help you in replication:

http://www.sql-server-performance.com/pi_multiple_publishers_replication.asp
http://www.sql-server-performance.com/pi_replication_across_internet.asp.

Rene




>Hello Rene,
>
>Do you replicate from Korea to Los Angeles and Philippines? Wow! Isn't that slow or very slow? What happens when the connection between the servers is broken? Do I have to take any special considerations for my tables? Say, the type of primary keys or referential integrity?
>
>Sorry, for the many questions but I guess I need to do replication in the near future and I want to be very well documented.
>
>Thank you very much,
>
>Luis
>
>
>
>>Hi Luis,
>>
>>I have tried using replication in SQL Server 2000. I have use the transactional replication and it works fine.
>>I have no problem with it. The transactional replication works for me. I did not tried merge replication.
>>
>>I have tried replication of db from our data center in Korea to data center in Los Angeles and Philippines using transactional replication and it works fine.
>>
>>
>>
>>>I have a SQL Server DB that I want to be replicated in two other geographically distant buildings. What are the pre-requisites for the DB to be ready for replication? I want to use replication that updates, say, every 15 minutes data for all the DB tables.
>>>
>>>TIA,
>>>
>>>Luis