Hi Rene,

Thank you very much for your help I was very busy so I just read your message, but I think I read it more carefully at home. It looks very interesting and somewhat complex at first sight.

Luis


>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