Let’s start this week’s blog with an admission. I am not a SQL Database Administrator and I have no desire to become one. In my professional opinion being a DBA requires a special way of looking at the world and that extends to how your databases are structured, managed and monitored.
This blog is not aimed at educating DBAs, rather it offers the options for replication that can make Passwordstate Administrators lives easier. We’re also starting this blog from the perspective of what is achievable versus looking at business requirements, risk appetite and budget. You could think of this as a bottom-up approach versus the top-down approach referenced in https://blog.clickstudios.com.au/ha-auditing-records-and-syslog-servers/.
HA Options Available
As per our previous blog (link above), when implementing Passwordstate HA you require our HA license, even if you use Virtual Server Replication technologies for your implementation. The image below is again a simple logical view of the 2 types of HA that Passwordstate could be implemented with using a bottom-up approach. In both of the diagrams the databases are installed on the webservers to maintain simplicity.
Passwordstate HA with SQL Standard and SQL Express: The design on the left hand side has been created with the decision not to use SQL Server Standard Edition on the HA Server. Instead, SQL Server Express (at no cost) has been selected.
This will only support an Active / Passive HA implementation, with the Primary instance of Passwordstate being the Publisher and using SQL Standard. The Passive Secondary instance is the Subscriber and is using SQL Express. Using this model your only supported form of SQL replication between the Publisher and the Subscriber is Transactional.
Passwordstate HA with SQL Standard: The design on the right hand side has been created with SQL Server Standard Edition used for both the Primary instance and the Secondary instance. This now provides us some options.
This will support an Active / Passive HA implementation, or an Active / Active HA implementation. You could setup Transactional replication the same as on the Left Hand Side, or you could setup a Basic Availability Group. Using a Basic Availability Group, you specify both a Primary Replica and Secondary Replica on your Primary instance. Both the Primary and Secondary Replicas have a copy of the Passwordstate database.
A Secondary Replica, also containing a copy of the database, is setup on your Secondary instance. The Basic Availability Group handles replication between all databases and enables automatic failover of the databases using a SQL Listener, an Active Directory object created when you setup a Basic Availability Group.
Benefits of Basic Availability Groups
So, what are the advantages of using Basic Availability Groups? An immediate advantage is that you now have an Active / Active implementation of Passwordstate HA at the Database Level. Add a load balancer, placed in front of both of your webservers, and you can then distribute the load for a single URL between the two webservers. This will also enable automatic failover at the webserver level in the event one of them becomes unavailable.
Basic Availability Groups also handle database schema changes natively whereas Transactional replication requires a hands-on approach during upgrades to ensure schema changes are processed. By using Basic Availability Groups your Passwordstate Upgrades will become that much easier.
Supplemental Information
Basic Availability Groups in SQL Server Standard edition was introduced in SQL Server 2016. For information on how to configure either Transactional Replication or Setup Basic Availability Groups, please navigate to Support->Documentation on our website and select the documents you require.
If you’d like to share your feedback please send it through to support@clickstudios.com.au.