MS SQL Server 2000 Datastore Replication

The following steps are all done in the Enterprise Manager.
First, create a new database on the SQL server that will be used for the replica.
Ensure that the database user is the same on the master database server & the replica server and is given db owner rights.
In the Enterprise Manager on the server that is to be used for the master database, right-click the replication folder & click the Configure Publishing, Subscribers, & Distribution Wizard.

• 1. Select the current server to be its’ own distributor on the Select Distributor page.
  2. Keep the default Snapshot folder.
  3. On the Customize the Configuration page choose No, use the following default settings.
  4. Click Finish to complete the wizard.

5. Right-click the Replication Monitor, select the Publication Databases tab, check the
“Trans” box adjacent to the database to be replicated.
6. Right-click the database name, go to New/ Publication. This will start the Create
Publication wizard. On this page make sure to check the “show advanced options…”
box & click next to begin.


7. The next screen is the Choose Publication Database screen. Highlight the database to
be replicated & click Next.

• 8. Select the Transactional publication radio button as the publication type.
  

9. Check the Immediate updating checkbox.

10. For Subscriber Types select “Servers running SQL Server 2000”
11. The Specify Articles screen is one of the most important screens of the process. For
the “Tables” Object Type, the check boxes for both the “Show” & “Publish All” must
be checked. This will enable all tables in the right-hand screen.

The above illustration shows the tables for Citrix MetaFrame Presentation Server XP.

This illustration shows the tables to be published with Citrix MetaFrame Presentation Server 3.0 � and Citrix Presentation Server 4.0 �

12. The Article Issues on the next screen are not an issue here so just click
next.

13. The Publication name can be anything but in keeping with the Advanced Concepts
Guide, name it “MFXPDS”.

14. Select No on the Customize the Properties of the Publication window.
15. Click Finish on the final screen of the wizard.

The publication is now created.
16. Right-click on the published database name in the Publications folder of the database
and select “Push new subscription”. This will start the new subscription wizard.

17. Check the Show advanced options… check box.

18. Choose the subscriber from the list on the next screen.
19. Choose the destination database to be replicated to from the list on the next screen.
20. On Set Distribution Agent Location page choose to run the agent at the distributor.
21. Set the Distribution Agent Schedule to “continuously”.

22. Select Yes & check the box by the “Start the Snapshot Agent….” on the Initialize
Subscription screen.

23. Select Immediate updating on the Updatable Subscriptions screen.

24. Make sure that the required services listed in the shot above are started on the
Distributor.
25. Click Finish on the next screen to complete the Push Subscription Wizard.

26. Make sure that the following 8 or 9 tables on the replicated database are
listed:
DATATABLE
INDEXTABLE
KEYTABLE
DELETETRACKER (for MPS 3.0 & CPS 4.0)
MSreplication_objects
MSreplication_subscriptions
MSsubscription_agents
MSsubscription_properties
If the tables are not all there you must delete the replication setup & start
again. The dtproperties table will show up if you have used the database diagram wizard in the Enterprise Manager.
For servers in a different farm that are going to be put in the current one, run chfarm & select the server with the replicated database.
If there is to be a fresh installation of Metaframe XP, select the replicated database server when prompted.
For a server in the current farm that is to be configured to use the new db, create a new dsn file on the server which points to the replicated SQL server. Then use the dsmaint config command from a command prompt to re-point the IMA Service to the new datastore.
Due to Microsoft security concerns, the SA account password is not passed to the subscription SQL server during the replication process so the following command must be run from the Query Analyzer tool on the subscriber pointing to the Metaframe XP datastore database on the subscription SQL2000 server:
sp_link_publication '’, '', '
', 0, 'SA', '
'
Where:

• Distributor = The name of the distributor server
  Database = The name of the published database on the distributor

Publication = The name of the publication that is to be linked
Pwd = The password for the SA account on the distributor
From Microsoft SQL Books Online:

• “When setting up a push subscription using the Push Subscription Wizard in SQL Server Enterprise Manager or the sp_addsubscription stored procedure, the default configuration uses dynamic RPC at the Subscriber. The dynamic RPC defaults to using the sa login with no password. This is done to avoid sending logins or passwords over the network, and can be changed at the Subscriber using sp_link_publication.”

Removing SQL replication and using the subscriber server as the master server.

• 2. From the SQL Enterprise Manager on the distribution server, perform the following steps:
  • a. Expand the ‘databases’ node and the XP database.
    b. Delete the publication under the publications folder.
  2. The following tables may still be located in the datastore on the ex-subscription server:

MSreplication_objects

MSreplication_subscriptions
MSsubscription_agents
MSsubscription_properties

• If any of those 4 tables are still in the database, run the following stored procedure from the SQL Query Analyzer on the ex-subscription server in the context of the datastore:

Sp_subscription_cleanup ‘publisher’,’publication database’,’publication name’ to remove the tables.

• 
  3. Make sure KEYTABLE nodeid is established as an identity field, no default value, identity seed & increment of 1
  • a. Open SQL Server Enterprise Manager
    a. Select the appropriate server
    a. Expand databases
    a. Expand appropriate database
    a. Click on Tables
    a. Right click KEYTABLE in right pane and select Design Table
    a. Click on nodeid
    a. From the Columns tab on the bottom panel:
    i. Delete any default Value

ii. Select Yes (Not For Replication) in Identity field

iii. Set Identity Seed to 1
iv. Set Identity Increment to 1

• 4. Make sure DATATABLE dummyid is established as an identity field, no default value, identity seed & increment of 1
  • a. Repeat KEYTABLE steps above through step f.
    a. Right click DATATABLE in right pane and select Design Table
    a. Click on dummyid
    a. Repeat KEYTABLE step h above.

• 5. Make sure INDEXTABLE dummyid is established as an identity field, no default value, identity seed & increment of 1
  • a. Repeat KEYTABLE steps above through step f.
    a. Right click INDEXTABLE in right pane and select Design Table
    a. Click on dummyid
    a. Repeat KEYTABLE step h above.

Since replication is broken, it is assumed that the subscriber becomes the new master. There is no reconciliation available, so when these changes are made the Old Subscriber IS the new Master. If replication is established again, the database on the Old Subscriber/New Master must be used to replicate from.

Multi-Subscriber Replication
When attempting to set up replication using multiple subscribers or to add a subscriber to a currently configured distributed datastore the following error will occur when attempting to make any change to the datastore (add Citrix admin, publish an app, etc.):
Last Command:
----------------------------
{CALL sp_MSdel_KEYTABLE (63)}
Transaction sequence number and command ID of last execution batch are 0x0000034F0000006D000100000000 and 1.
Error Message:
-----------------------------
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__DATATABLE__nodei__35BCFE0A'. The conflict occurred in database 'CTXIMA', table 'DATATABLE', column 'nodeid'.
Error Details:
-----------------------------
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__DATATABLE__nodei__35BCFE0A'. The conflict occurred in database 'CTXIMA', table 'DATATABLE', column 'nodeid'.
(Source: HQ-TIMOTHYC03 (Data source); Error number: 547)
---------------------------------------------------------------------------------------------------------------
The row was not found at the Subscriber when applying the replicated command.
(Source: HQ-TIMOTHYC03 (Data source); Error number: 20598)
---------------------------------------------------------------------------------------------------------------
The workaround is to do the following steps from the SQL2000 Enterprise Manager:
1. Go to the individual tables on the master database and the 2 subscription databases.
2. Right click on the DATATABLE --> Design Table
3. Click the 'Manage Relationships' icon on the tool bar & on the Relationships tab uncheck the
box at the bottom of the window -- "Enforce relationship for replication"

Repeat the process for the KEYTABLE & the INDEXTABLE on all of the subscriber servers as well as the distribution server.
Changes in Windows 2003 for Successful Datastore Replication

• 1. Verify both Publisher and Subscriber SQL servers are in the same domain -
  If not please review Microsoft TechNet Article: 817064
  
  2. Verify MSDTC service is using the Network Services security account
  (Note This account uses a blank password)

• 3. Network DTC Access must be checked as well as Network Administration and Network Transaction.
  
  Administrative Tools->Component Service

• 4. Make sure the Distributed Transaction Coordinator service is running and functioning.

A reboot is required on both the Subscriber and the Publisher in order for the changes take place.
More Information
CTX110738 – How to Upgrade a Replicated SQL 2000 MetaFrame XP Feature Release 3 Environment
CTX109394 – Upgraded MetaFrame XP Servers to Presentation 3.0 or 4.0 using SQL Replication Fail to Replicate New Schema Changes

>> Microsoft SQL Server 2000 Data Store Replication