[THIN] KB: CTX101739 - Microsoft SQL Server 2000 Data Store Replication

  • From: "Jim Kenzig Kenzig.com" <jkenzig@xxxxxxxxx>
  • To: thin@xxxxxxxxxxxxx
  • Date: Tue, 23 Aug 2005 06:43:33 -0700 (PDT)

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. 


Other related posts:

  • » [THIN] KB: CTX101739 - Microsoft SQL Server 2000 Data Store Replication