Microsoft SQLServer Replication across Internet
Contact Data One

This article describes establishing Microsoft SQLServer replication to a remote (backup) site in another location to a commercial deployment.  This improves reliability and reduces risk for commercial hosting in a similar way to database clustering or mirroring.

We host a highly available production MS SQLServer deployment for PeterPans as an online booking platform for travel products.  We will move to a mirrored database eventually which provides the best reliability (cheap clustering) with safe failover but for now have decided to opt for the less expensive live backup or replicated copy of the production server to replicate across the internet to our office in Maroochydore, Qld Australia.

Hence the scene we are describing is best described here:

  • Windows Servers 2003 service pack 3 at both ends. Firewalls opened on port 1433 where applicable.
  • SQLServer 2005 service pack 3 as current latest
  • Production Servers hosted in Fujitsu data centre (here after PRODSQL). Web server + SQLDatabase server.
  • Office located in Maroochydore Queensland Australia through adsl router modem (here after OFFICESQL).  
  • Single port used for sql connection 1433

This has been quite a struggle (even with the internet) and thought it may help out others, plus documents it for our reference later.

First Step Prerequisites

1.    Ensure the firewall at the office port forwards tpc/udp port 1433 to OFFICESQL.  I have found it necessary to run both sql instances as default in order to connect through the firewall on ONE port .

2.    Ensure firewalls on production are opened on port 1433 and that you can connect to it from remote clients.

3.    Ensure latest service pack (3 for sqlserver 2005) is applied to both instances.

4.    Ensure that the default instance is used for both versions of sqlserver. In theory you can do it on defined instances but you need to open more ports (less secure) and I had trouble getting this to work.

5.    Ensure sql server can be accessed from both the ejiserver using sqlenterprise manager instance and visa versa. NOTE this may require fixed ip addresses to be updated to hosts file in windows/system32/hosts  At both machines for ds5128 and for ejiserver etc.

6.    Ensure sqlserver accounts are setup at both ends. NOTE the sa or administrator account is necessary at the production server to do this as it fails for permissions reasons otherwise.


sqlserver replication


sqlserver replication

Local adsl modem settings....

 sqlserver replication


sqlserver replication

Microsoft SQLServer Replication Creation

Ok at this point you should be able to connect from either sqlserver machine to the other freely using sa passwords at either.

Issues to overcome

Triggers are by default used for audit tracking from tables where changes are made to audit table copies.  These must be marked as notforreplication in order for these not to be fired and to cause replication errors.


ALTER TRIGGER [update_Agent] ON [Agent].[Agent]





    INSERT INTO [Audit].[Agent.Agent] (






























        from inserted

b) Not for replication must be applied to indexes created as well. As these are automatically created they conflict as defined in this article.

Explicit value must be specified for identity column in table 'TPP.User' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column

This KB seems to relate to the error.

Now the workaround apparently is to disable the NOT FOR REPLICATION option for the identity column. Which is what we want to do anyway so that the identity value seed is updated at the subscriber database by replication.

Running the following sp in the publisher

exec sys.sp_identitycolumnforreplication <table objectid), 0

on each table that has a identity columns will change the NFR option. 

I have a short script that will apply this for all tables as follows:

-- turn not for replication to OFF on idendity field

use [TPP]

EXEC sp_msforeachtable @command1 = '

declare @int int

set @int =object_id("?")

exec sys.sp_identitycolumnforreplication @int, 0'

Create SqlServer Replication Publication

1.    Log onto production box

2.    Right click replication, local publications, new publication in SQLServer Management Studio. Select the database you wish to replicate.


sqlserver replication

3.    Select transactional replication.

 sqlserver replication

4.    Select all options next

 sqlserver replication

5.    Next


sqlserver replication

6.    Next

 sqlserver replication

7.    Tick schedule snapshot agent to run at following times. I choose once / week with transactional nightly but that's optional.

 sqlserver replication

8.    As we do not run a domain in prod we use a local account as follows.

 sqlserver replication

9.    Not use administrator account and password. OK. Then FINISH.

sqlserver replication


10.    Enter publication name. TESTPUBLICATION.

 sqlserver replication

11.    Click close

The newly created publication should appear under local publications.

sqlserver replication


NOTE: this diagram has 2 publications active.

Modifications Necessary

Note this is basic and will only include data by default.  To make the database more complete it is necessary to include more articles. These couldn’t be chosen first but can now.

12.    Right click TESTPUBLICATION and select properties. Select articles on left hand side.


sqlserver replication

13.    Select right hand button articleproperties/select set properties of all tables.


 sqlserver replication

14.    Make all objects and settings to TRUE. Right down to the destination object section.

 sqlserver replication

15.    Select OK.

Create SqlServer Replication Subscription

This is best achieved on the production server also but can be done on the office server if you wish.

Note this is a push subscription which is the easier of the 2 systems to get running.

Here is a short script (also on production) to do this quickly. NOTE sa password is changed. This is creating it from TESTPUBLICATION to a database called TPPReplication.

-----------------BEGIN: Script to be run at Publisher 'DS5128'-----------------

use [TPP]

exec sp_addsubscription @publication = N'TESTPUBLICATION', @subscriber = N'ejiserver', @destination_db = N'TPPReplication', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'TPPPublication', @subscriber = N'ejiserver', @subscriber_db = N'TPPReplication', @job_login = N'ds5128\administrator', @job_password = sapassword, @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = SUBSCRIBERPASSWORD, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090520, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'


-----------------END: Script to be run at Publisher 'DS5128'-----------------

To perform this manually…

1.    Right click new subscription on the testpublication


sqlserver replication


2.    Next

 sqlserver replication

3.    Next

 sqlserver replication

4.    Next. Tick server ds5128 (in this instance) and select database that is being subscribed to.

 sqlserver replication

5.    Click add subscriber select mssql subscriber

 sqlserver replication

6.    Enter officeserver and sa user and password. Connect

sqlserver replication


7.    Select officeserver and database within that to replicate to. Click Next.

 sqlserver replication

8.    Click security.

 sqlserver replication

9.    Fill out user details and click OK.

 sqlserver replication

10 . Click Finish. Click finish again if necessary.


sqlserver replication

sqlserver replication

Monitoriing SqlServer Replication Subscription

Two things must happen to create copy on the local office machine. 

a) You must create a snapshot of the database.

b) You must monitor subscriptions.

1. Right click publication/view 'snap shot agent status'

sqlserver replication

2. Click Start to generate it NOTE this will take some time but will complete a copy of the database for replication.

3. Right click subscription appearing under replication\localpublications\TESTPUBLICATION\TPPUATReplication select view synchronisation status.  This will take some time to synchronise and will only happen after the snapshot has been created, then bukl copied across to the replicated database.  This takes fora  1gig database approx 30 minutes through an adsl2 line in Australia.


Once settled individual transactions will appear here....

sqlserver replication

If you have comments to contribute to this article please contact us.