Access to Read Only SQL Database
RDM 14.0.6.0 64-Bit
DataSource SQL Server 2016 Std
Hi All,
We have the requirement to configure a replica of the RDM database to another SQL instance, that can be accessed from another department of the Company.
In the main Instance we have configured an Availability Group for HA purposes, but bearing in mind that we're using a SQL Std edition, we can't make the secondary accessible even as a Read-Only.
To meet that requirement, we tested with Log Shipping and SQL Replication.
With Log Shipping and Transactional Replication, we faced issues from the RDM client as it seems that needs Write permissions for the users, at least during the authentication.
Finally, we tested Merged replication that allows you to have write access to the secondary. That works fine, but sometimes we're getting a message during the login, stating that the database schema is corrupt, after a refresh.
Despite after clicking on FIX IT, everything seems working fine, it's not acceptable for our users to get this message sometimes.
So, my question is:
Does anybody have experience in this scenario? How do you manage to refresh the database and make it accessible from other RDM clients.
Thanks in advance,
Eloi
All Comments (4)
Hello,
Our database is one with highly controlled access and extensive logging. Those logs are written first to the database, which makes it impossible to use a read-only system.
You havent described why this must be put in place, is it for compliance or for providing another team with the full copy? For the latter, is it because of a geographically distributed team or rather for respecting lines of authority?
My suggestion would have been to use our Synchronizer family of entries to keep two different databases in sync.
I will wait on further input from you before going further.
Best regards,
Maurice
Hi Maurice,
Thanks for your reply.
The teams will be on the same geographical location, this is purely to have separate security zones.
Could you provide me with further info about your Synchronizer family or alternative ways to do that?
Thanks,
Eloi
Hello,
The first part is to schedule an export using our powershell module. The second part is to create a Synchronizer in your "replica" that will ingest the file generated at step one.
I will ask the team to ensure they publish a KB for each step, whats your deadline for this?
Maurice
Hi Maurice,
The idea is to have this in place during February.
Thanks for your support,
Eloi