Summary
This document contains the following information:
Microsoft SQL Server 2000 Data Store Replication
Removing SQL Replication and Using the Subscriber Server as the Master Server
Changes in Windows 2003 for Successful Data Store Replication
Microsoft SQL Server 2000 Data Store 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 and click the Configure Publishing, Subscribers, and 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, select the Trans box adjacent to the database to be replicated.
6. Right-click the database name and go to New > Publication. This starts the Create Publication Wizard. On this page, make sure to select 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. Select 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, both the Show and Publish All check boxes must be selected. This enables all tables in the right screen. The following 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.
15. Right-click the published database name in the Publications folder of the database and select Push new subscription. This starts the new subscription wizard.
16. Select the Show advanced option check box.
17. Choose the subscriber from the list.
18. Choose the destination database to be replicated to from the list.
19. On the Set Distribution Agent Location page, choose to run the agent at the distributor.
20. Set the Distribution Agent Schedule to continuously.
21. Select Yes and select the Start the Snapshot Agent box on the Initialize Subscription screen.
22. Select Immediate updating on the Updatable Subscriptions screen.
23. Make sure that the required services listed are started on the Distributor.
24. Click Finish to complete the Push Subscription Wizard.
25. Make sure that the following tables on the replicated database are listed:
• DATATABLE
• INDEXTABLE
• KEYTABLE
• DELETETRACKER (for Presentation Server 3.0 and 4.0)
• 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 that 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 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
Important: If the IMA data store is not installed on the default instance of the SQL server, for <Distributor> value, please type <Distributor\Instance> in the above stored procedure. E.g. sp_link_publication ‘MySQLServer\SecondInstance’,‘IMADatabase’,‘MyPublication’,0,’SA’,’MyPassword’
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
1. 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.
1. 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.
2. 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
b. Select the appropriate server
c. Expand databases
d. Expand appropriate database
e. Click on Tables
f. Right-click KEYTABLE in right pane and select Design Table
g. Click on nodeid
h. 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
3. Make sure DATATABLE 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 DATATABLE in right pane and select Design Table.
a. Click dummyid.
a. Repeat KEYTABLE step h above.
4. 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.
When attempting to set up replication using multiple subscribers or to add a subscriber to a currently configured distributed data store, the following error occurs when attempting to make any change to the data store (add Citrix admin, publish an app, and so on):
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 two subscription databases.
2. Right-click the DATATABLE --> Design Table
3. Click the Manage Relationships icon on the tool bar and on the Relationships tab, clear the 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.
CTX110738 – How to Upgrade a Replicated SQL 2000 MetaFrame XP Feature Release 3 Environment
CTX111656 – SQL Replication Test Tool Version 4.2
