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:
This has been quite a struggle (even with the internet) and thought it may help out others, plus documents it for our reference later.
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....
Ok at this point you should be able to connect from either sqlserver machine to the other freely using sa passwords at either.
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]
FOR INSERT, UPDATE
NOT FOR REPLICATION
INSERT INTO [Audit].[Agent.Agent] (
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
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
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.
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'-----------------
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
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
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'
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....
If you have comments to contribute to this article please contact us.