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.

 

 

Local adsl modem settings....

 

 

 

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.

a)

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

    FOR INSERT, UPDATE

            NOT FOR REPLICATION

    AS

   

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

        [auditDateTime],

        [auditIsRowDeletion],

        [agentId],

        [groupId],

        [userId],

        [agentStatusId],

        [currencyId],

        [isInternational],

        [logServiceMessages],

        [name],

        [MoneyDirectCode],

        [updatedBy],

        [updatedDate],

        [inactive]

    )

        SELECT

        GETDATE(),

        0,

        inserted.agentId,

        inserted.groupId,

        inserted.userId,

        inserted.agentStatusId,

        inserted.currencyId,

        inserted.isInternational,

        inserted.logServiceMessages,

        inserted.name,

        inserted.MoneyDirectCode,

        inserted.updatedBy,

        inserted.updatedDate,

        inserted.inactive

        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.

http://support.microsoft.com/kb/908711

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.

 

3.    Select transactional replication.

 

4.    Select all options next

 

5.    Next

 

6.    Next

 

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

 

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

 

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

 

10.    Enter publication name. TESTPUBLICATION.

 

11.    Click close

The newly created publication should appear under local publications.

 

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.

 

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

 

 

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

 

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'

GO

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

To perform this manually…

1.    Right click new subscription on the testpublication

 

  

2.    Next

 

3.    Next

 

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

 

5.    Click add subscriber select mssql subscriber

 

6.    Enter officeserver and sa user and password. Connect

 

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

 

8.    Click security.

 

9.    Fill out user details and click OK.

 

10 . Click Finish. Click finish again if necessary.

 

Monitoriing SqlServer Replication Subscription

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