Let's get this straight: In a prod environment you should be having DQL access to the DB's of the applications you support. Any other command belonging to DML, DDL etc are usually reserved to either DBA teams or you will need to go through your organization's formal deployment process. Do not cowboy the system, no exceptions.
From a resiliency point of view, the absolute bare minimum is to run two database instances on separate hosts. These are usually named prod and a prod-replication (or prod-replica/prod-repl). These instancess MUST live on entirely different infrastructure, be it different physical hosts or even better, different availability zones. If your primary replication is in the same rack or worse: on the same physical host (I have seen this happened (╯°□°)╯), you have not built resilient infrastructure, you created a single point of failure and a post-mortem document that will be very difficult and akward to explain after that outage that will render your db unusable.
The two-instance set-up is not only to switch to the healthy server during an outage. It also helps to solve an operational need: providing your support teams with read-only access to the data of the applications they manage. By offloading all those SELECT queries to replication, you provide your teams the visibility they need to do their jobs without consuming resources from the production DB.
Not only you will address the issue of ensuring availability and disaster recovery to your prod operation but will also address an overlooked but, necessary operational need: providing your operations team with DQL access to the data of the applications they support.
Making your operations team to file a ticket and wait for approval just to run a SELECT query is an auto sabotage that will cripple the sla to reply to business requests and slow down incident response. I can honestly just think of two reasons to do this, a dysfunctional IT culture or a lack of trust in the people running the systems.
"But what can I do if a table contains PII or sensitive data" I hear you ask. If a table contains data falling under GDPR or HIPAA regulations, the answer will never be to lock the door and throw away the key, but implementing modern security controls like data masking, column level security and/or granular roles to strike a balance between providing access to your ops teams and being compliant with security policies. Is not 2005 anymore, we have better tools, better practices. Lets keep discipline in operations and implement them.
This is an extract of a sort of practical crash course in SQL that I am working on. You can access it here: sql-crash-course
Happy coding,
Eduardo.
Top comments (0)