During a project building a XenDesktop 7.x environment, the customer requested that I use SQL replication between three servers instead of the SQL Always On feature for high availability. For this particular project, the Citrix environment was not a critical component of the business and the customer only had SQL 2016 Standard licenses. In order to use the database of one of the replicated servers during a failure of the main server, a DNS name would be used when creating the Citrix databases.
During this process, I ran into some very interesting issues and solution, which I would like to share in hope that it helps someone else.
I was able to replicate the PVS database as well as the XenDesktop Logging and Monitoring databases. However, the Transactional Replication of the XenDesktop Site database kept failing with an error message:
The replication process would fail and the Snapshot initiation process would restart. This cycle would repeat until eventually the SQL job would stop completely.
Our SQL admin was able to get replication to work by manually adding Tables and Functions to the subscriber before the snapshot scripts were called. However, I had to find a method of creating the replication using simple step-by-step instructions that the customer can follow in case of a disaster recovery.
What I found that worked with no issues was creating a Transactional Replication initialized from a database backup instead of a snapshot. I was also able to provide the customer with simple step-by-step instructions on how to use this method.
I am not a SQL admin at all so I had to use the assistance of others via a Google search. The article that I found most useful and followed was How to create a subscription for a Transactional Publication from a backup in SQL Server, by Kathleen Long.
I took the steps in the article and added some helpful information and screenshots for anyone that has limited knowledge of SQL like myself.
1. Create the publication on the Publishing Server.
a. When you get to the “Create snapshot….” option, unselect it.
b. Once publication is complete, go to Properties > Subscription Options and set “Allow initialization from backup files” settings to True.
2. Confirm that immediate_sync and allow_initialize_from_backup are set to value = 1.
a. Run SQL command to confirm values:
exec sp_helppublication @publication = 'NAME of PUBLICATION'
b. If values = 0, run the following commands to change each one:
EXEC sp_changepublication @publication = 'NAME of PUBLICATION'
, @property ='immediate_sync'
, @value = 'true'
EXEC sp_changepublication @publication = 'NAME of PUBLICATION'
, @property ='Allow_initialize_from_Backup'
, @value = 'true'
3. Create a full backup of the publication database to be replicated
4. Copy the full back up to the subscriber server.
5. Restore the full backup on the subscriber server.
6. On the PUBLISHER server, issue the command to create the subscription.
7. Because I used a service account to create the Citrix databases and manage SQL connections, I had to change the Agent Process Account within the Subscription Properties because by default it will use the SQL Agent account. I had to restart the SQL Agent Service for this change to take effect.
8. Launch Replication monitor and validate that data is synchronizing between the publisher and subscriber instances.