CTX101739 - Microsoft SQL Server 2000 Data Store Replication This document was published at: http://support.citrix.com/kb/entry.jspa?externalID=CTX101739 Document ID: CTX101739, Created on: Apr 15, 2003, Updated: Jul 11, 2005 Products: Citrix MetaFrame XP 1.0 for Microsoft Windows 2000, Citrix MetaFrame XP 1.0 for Microsoft NT 4.0 Server Terminal Server Edition, Citrix MetaFrame XP 1.0 for Microsoft Windows 2003 MS SQL Server 2000 Data Store Replication The following steps are all done in the Enterprise Manager. The very first thing to do is to create a new database on the SQL server that will be used for the replica. Make sure that the database user is the same on the master database server and the replica server and is given database owner rights. In the Enterprise Manager on the server that is to be used for the master database, right-click the replication folder and click the Configure Publishing, Subscribers, and Distribution Wizard. 1. Select the current server to be the 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, enable the Trans check box adjacent to the database to be replicated. 6. Right-click the database name, go to New/ Publication which will start the Create Publication wizard. On this page make sure to enable the Show advanced options?check box and click Next to begin. 7. The next screen is the Choose Publication Database screen. Highlight the database to be replicated and click Next. 8. Select the Transactional publication radio button as the publication type. 9. Enable the Immediate updating check box. 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 and Publish All must be selected. This will enable all tables in the screen to the right. 12. The Article Issues on the next screen are not an issue here so 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 the published database name in the Publications folder of the database and select Push new subscription. This will start the new subscription wizard. 17. Enable 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 and enable the Start the Snapshot Agent? check box on the Initialize Subscription screen. 23. Select Immediate updating on the Updatable Subscriptions screen. 24. Make sure that the required services listed in the example 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 eight tables on the replicated database circled above are listed: ? DATATABLE ? INDEXTABLE ? KEYTABLE ? MSreplication_objects ? MSreplication_subscriptions ? MSsubscription_agents ? MSsubscription_properties If the tables are not all there, you must delete the replication setup and 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 and 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 database, 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 repoint the IMA Service to the new data store. 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 data store database on the subscription SQL2000 server: sp_link_publication '<Distributor>?, '<Database>', '<Publication>', 0, 'SA', '<Pwd>' 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 four 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 data store: 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, and increment of 1: a. Open SQL Server Enterprise Manager. a. Select the appropriate server. a. Expand databases. a. Expand appropriate database. a. Click Tables. a. Right-click KEYTABLE in right pane and select Design Table. a. Click 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 dummyid. a. Repeat KEYTABLE step h above. 5. Make sure INDEXTABLE dummyid is established as an identity field, no default value, identity seed, and increment of 1: a. Repeat KEYTABLE steps above through step f. a. Right-click INDEXTABLE in right pane and select Design Table. a. Click 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 SQL 2000 Enterprise Manager: 1. Go to the individual tables on the master database and the two subscription databases. 2. Right-click DATATABLE > Design Table. 3. Click the Manage Relationships icon on the tool bar and on the Relationships tab clear the check box at the bottom of the window -- Enforce relationship for replication. Repeat the process for the KEYTABLE and the INDEXTABLE on all of the subscriber servers as well as the distribution server. Changes in Windows 2003 for Successful Data Store Replication 1. Verify both Publisher and Subscriber SQL servers are in the same domain. If not, 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.