Provisioning Services Database Redundancy – Mirroring or Replication?
Awhile back I wrote the blog post Provisioning Services 5.1 Specifiy Database Mirror Failover Partner…Huh? and got some good feedback from Pete Downing the Principal Product Manager for Provisioning Services. In his comments on my blog post he mentioned that he would like to discuss my views on SQL Server database mirroring versus replication of the Provisioning Services database. In this blog post I am going go over my views on database Mirroring versus Replication of the Provisioning Services database.
Provisioning Services 5.1 had a feature that would allow you to specify a database mirror failover partner when joining another Provisioning Server to the Provisioning Services farm. This was an undocumented feature that I couldn’t get to work. In the feedback I received from Pete Downing on my blog post he gave an explanation and more information on the specify database mirror failover partner feature. Here is Pete’s comments on that blog post “Appreciate your detailed and direct blog post here. As PM of Provisioning Services I enjoy posts like this because they provide “real” feedback. What you are seeing is unfortunately a bug and we are addressing. You will notice that we will release a SP1 in the next few weeks for PVS 5.1, however, you will notice this feature will be removed. We decided to do this because our DB caching feature is solving the use-case we set out to solve for the time being. We are not ditching this feature and will re-introduce it in the next release of PVS. We would like to re-introduce with solid best practices and documentation. Essentially this feature was to be an easter egg for some end-users to test and unfortunately got too exposed. Feel free to drop me an email where you feel very strongly about this topic. I’d love to discuss your view on mirroring versus replication.”. This feature has been removed since the release of Provisioning Services 5.1 Service Pack 1.
So before I state my views on mirroring versus replication, let’s go over SQL Server Mirroring and Replication.
SQL Server Mirroring
SQL Server Mirroring was first introduced in SQL Server 2005 and is also in SQL Server 2008. SQL Server Mirroring has a principal and mirror SQL Server. In some cases a witness SQL Server is needed. Mirroring works on top of Log Shipping. The database on the mirror SQL Server is offline until a failover is done. Depending on which Mirroring type you use failover can be automatic, manual, or forced.
There are 3 types of SQL Server Mirroring:
- High Availability – Automatic or Manual failover. Has full transaction safety with a synchronous transfer mechanism. A quorum and witness server are required. SQL Express can be used for the witness server.
- High Protection – Manual failover. Has full transaction safety with a synchronous transfer mechanism. A quorum is required but a witness server isn’t required.
- High Performance – Forced failover. Doesn’t have transaction safety, has an asynchronous transfer mechanism, and neither a quorum or witness is required.
If automatic failover is used the mirror SQL Server database will automatically become active without having to restore logs with the help of the witness SQL Server. When using SQL Server Mirroring the logs are continuously merged in the mirror database. SQL Server mirroring offers a very minimal downtime and cost-effective solution while only targeting/mirroring a single database.
SQL Server Replication
SQL Server Replication allows for multiple synchronized sets of the same database to be online at the same time. There are no standby servers when using SQL Server Replication. The publisher and subscriber SQL Servers are both active. Replication is basically copying and distributing data and database objects from one database to another database across local or remote data centers. The data is synchronized between the databases to maintain consistency. The remote data centers could be geographically dispersed across cities, states, or countries.
There are 3 types of SQL Server Replication:
- Transactional Replication – Typically used in server-to-server environments. Starts with a snapshot of the publication database objects and data. Incremental changes are propagated to the subscriber SQL Servers as they occur. Good for instances where the application requires low latency between the time changes are made at the publisher SQL Server and the changes arrive at the subscriber SQL Server. Also good for instances where the application requires access to the intermediate data states.
- Merge Replication – Typically used in server-to-client environments. Starts with a snapshot of the publication database objects and data. Data changes and schema modifications made at the publisher and subscribers are tracked with triggers. The subscribers are synchronized with the publisher and other subscribers when connected to the network. Good for when subscribers need to receive data, make changes offline, and synchronize changes later with the publisher and other subscribers.
- Snapshot Replication – Distributes data exactly as it appears at specific moment in time. It doesn’t monitor for data updates. The entire snapshot is generated and sent to the subscribers when synchronization occurs. Good for when data changes infrequently and replicating small volumes of data.
Each type of SQL Server Replication begins with an initial synchronization of the published database objects and data between the publisher SQL Server and subscriber SQL Servers.
Provisioning Services with SQL Server Mirroring
A good use case for SQL Server Mirroring of the Provisioning Services database is within a single data center for redundancy where SQL Server clustering isn’t used. A good example of this would be where the SQL Server is a virtual server and clustering is more complex in a virtual environment. Especially if there isn’t an iSCSI SAN to use for the cluster shared storage. Starting with Provisioning Services 5.1, enabling the Provisioning Services offline database support feature also adds redundancy if the Provisioning Services database server becomes unreachable or there is a hardware failure. Using SQL Server Mirroring would add an extra layer of database redundancy when used with the Provisioning Services offline database support feature. SQL Server Mirroring of the Provisioning Services database would be a good use case in virtual environments.
I see a better use case for SQL Server Mirroring with Provisioning Services for disaster recovery sites. Starting with Provisioning Services 5.0, a farm structure and sites were introduced. In your Provisioning Services farm you could have two sites setup. One site for production and one site for disaster recovery. You could have a mirror copy of your Provisioning Services database and Provisioning Servers at your disaster recovery site. When a disaster recovery test or actual disaster occurs, you failover to your disaster recovery Provisioning Services site and could be up with minimal downtime. Same as XenApp when using zones and SQL Server Mirroring.
I could see High Availability SQL Server Mirroring as the supported mirror type for the Provisioning Services database. SQL Server Mirroring high availability would give the option of automatic and manual failover. Having automatic failover would be good unplanned outages and manual failover would be good for planned outages when doing SQL Server maintenance or disaster recovery testing.
The biggest issue I see with changing Provisioning Services to be able to use SQL Server Mirroring is either changing how the registry entries for the database configuration are stored at HKLMSoftwareCitrixProvisioningServicesDatabase since they are currently in some kind of has format or change the Provisioning Services/database configuration to use a ODBC DSN file like XenApp has for the IMA Data Store database.
Provisioning Services with SQL Server Replication
The best use case I see for SQL Server Replication is when having a geographically distributed Provisioning Services farm. Remember that starting with Provisioning Services 5.0, a farm structure and sites were introduced. SQL Server Replication works perfect in this use case since all Provisioning Servers at each site are connected to an active database instance. Provisioning Servers connect to and use their local replicated database. This works out great for sites that are on opposite sides of the country. Like SQL Server Mirroring, the Provisioning Services offline database support features could be used along with SQL Server Replication to add an extra layer of database redundancy. Same as XenApp when using zones and SQL Server Replication.
Disaster recovery is another good use case for SQL Server Replication of the Provisioning Services database. In your Provisioning Services farm you could have two sites setup. One site for production and one site for disaster recovery. You could have a replicated copy of your Provisioning Services database and Provisioning Servers at your disaster recovery site. When a disaster recovery test or actual disaster occurs, you can use your disaster recovery Provisioning Services site and could be up with zero downtime since replication allows for all Provisioning Servers at each site to be connected to an active database instance. Same as XenApp when using zones and SQL Server Replication.
Just like the XenApp IMA Data Store database I see SQL Server Transactional Replication as the only supported replication type for Provisioning Services. Transactional replication is a must since changes made at the publisher are immediately replicated to the subscribers.
The biggest issue I see with changing Provisioning Services to be able to use SQL Server Replication is that not all tables in the Provisioning Services database have primary keys set on them. To use SQL Server Replication, each table has to have a primary key set on it to be able to be replicated. So the database will have to be modified to have primary keys set on all the tables for SQL Server Replication to be an option. Also there will have to be a process to set the replicated database as primary if the publisher SQL Server becomes unavailable in a disaster. This method could be the same as with the XenApp IMA Data Store but then again, the Provisioning Services/database configuration would have to be changed to use a ODBC DSN file.
So in conclusion I see both SQL Server Mirroring and Replication as options for database redundancy in Provisioning Services. Both options have a use case. Adding the feature to use SQL Server Mirroring, Replication, or both would cause some modifications to made to Provisioning Services and the backend database it uses. I personally would like to see both SQL Server Mirroring and Replication as redundancy options in Provisioning Services. Give the customer the option to choose which SQL Server redundancy option works best in their environment. High Availability mirror option for SQL Server Mirroring and Transactional replication for SQL Server Replication. Hopefully one or both of these options will be added to the next version of Provisioning Services.
If you have found this article interesting or if you have any other insights, please feel free to leave comments on this article.