Governing SQL
Database
Sakkurthi Sashank | AP21110010272
Prasanna Kumar Dheekonda | AP21110010174
Srinivas Mandadapu | AP21110010180
Sivamani Gopavarapu | AP21110010195
What is Governing SQL
Database
A "governing SQL database" generally refers to managing
and controlling SQL databases to ensure efficient, secure,
and compliant use of data.
Governing SQL databases encompass various practices,
policies, and tools used to oversee and administer
databases effectively.
It involves data security, access control, performance
optimization, backup and recovery, monitoring, auditing,
and compliance with data governance standards or legal
regulations.
01. Data Security and Access Control
Some core
02. Data Quality and Consistency
principles in 03. Compliance and Auditing
governing 04. Backup and Disaster Recovery
an SQL 05. Performance Monitoring and Optimization
database 06. Data Lifecycle Management
07. Documentation and Standards
Resource Governor
This feature in SQL Server helps ensure that resources are shared fairly and that requests don't overwhelm other
requests. It can be used to govern resources like CPU, memory, and physical IO
Windows Job Objects
This feature in Azure SQL Database allows a group of processes to be managed as a unit. It can be used to govern
working set caps, file virtual memory commit, CPU affinity, and rate caps.
Secoda
This tool can be used to set up data governance for SQL Lite, MySQL, and Microsoft SQL. Secoda can help with data
lineage, data quality, data privacy, and more.
Io-Tahoe
This tool can help improve data governance and regulatory compliance. It can help ensure that businesses and
technology understand critical data elements.
SQL Database
Management in
Production
Governing an SQL database in production applications
ensures that data is secure, reliable, and efficiently
managed.
It helps maintain data integrity, controls access, and
optimizes performance, which is essential for
applications that rely on fast, accurate data retrieval.
Governance also supports compliance with
regulations, minimizes downtime through effective
backup and recovery strategies, and enhances
scalability as data grows.
For production apps, this means smoother operations,
better user experiences, and reduced risk of data
breaches or compliance issues, all of which are critical
for maintaining trust and reliability.
Key Principles
Data Ownership: Who owns the data and who is responsible
for it. It determines who can access, modify, and delete data.
Security: Ensuring data is protected from unauthorized
access, modification, or loss. This includes encryption, access
controls, and secure communication.
Integrity: Maintaining the accuracy and consistency of data
throughout its lifecycle. This ensures that data is reliable and
trustworthy.
Compliance: Adhering to relevant laws, regulations, and
standards governing the use and protection of data, such as
DPDP
GDPR
HIPAA
Data Quality
Profiling: Analyzing the data to understand its
structure, content, and quality. It helps identify
mistakes, missing data, and inconsistencies.
Cleansing: The process of fixing or removing incorrect,
incomplete, or irrelevant data to improve its quality.
Master Data Management (MDM): Centralizing and
organizing critical business data to ensure consistency
and accuracy across systems.
Tools: ETL (Extract, Transform, Load) tools and data
integration platforms facilitate this process by
standardizing and importing data into a central
repository.
Governance
Framework
Policies: High-level guidelines for data management,
security, and compliance, ensuring consistency in data
practices across an organization.
Procedures: Detailed instructions for implementing
governance policies. These define how policies are
executed in day-to-day operations.
Standards: Specific criteria for how data should be
formatted, structured, and accessed to maintain
consistency and quality.
Technology: Tools and platforms that support the
governance framework, such as database management
systems (DBMS), auditing software, and encryption
technologies.
Implementing
Policies
Access Policies: Defining who has access to the data
and under what circumstances, based on roles and
responsibilities.
Retention Policies: Establishing how long data is kept
and when it should be archived or deleted, in
compliance with legal and organizational requirements.
Data Masking Policies: Obfuscating sensitive
information, so it remains useful for testing or analysis
but is not exposed to unauthorized personnel.
Data Security
Encryption: Encrypting data both at rest (when
stored) and in transit (when being transmitted across
networks). This ensures unauthorized users cannot
read or alter the data.
User Permissions: Assigning roles and permissions to
users based on the principle of least privilege,
meaning users only have access to the data necessary
for their tasks.
Auditing: Keeping a log of database activities (e.g.,
who accessed the data and when) for accountability
and troubleshooting.
Backup: Regularly creating copies of data to prevent
data loss due to accidental deletion, hardware failure,
or cyberattacks.
Governance
Tools
SSMS (SQL Server Management Studio): A tool used for
managing SQL Server databases, providing features like
querying, security, and auditing.
Azure Managed Instance: A fully managed SQL Server database
service in Microsoft Azure that simplifies governance, backup,
security, and compliance tasks.
Auditing Tools: Tools that track and log database activity for
compliance and security purposes, such as SQL Server Audit
or third-party solutions.
Monitoring &
Auditing
Tools: Software tools that allow real-time tracking of
database performance, access, and integrity, such as
SQL Server Profiler, Azure Monitor, and custom-built
solutions.
Audit Setup: The process of configuring audit logs to
capture important events like data access,
modifications, or policy violations.
Review Processes: Regularly reviewing audit logs and
monitoring alerts to detect and respond to suspicious
activity.
Alerts: Notifications or warnings generated when
certain thresholds or suspicious behaviors are
detected, helping prevent data breaches or non-
compliance.
Backup & DR
Planning
Backup Types: Full, differential, and incremental backups that
allow for the restoration of data in the event of a failure.
High Availability: Ensuring that database systems remain
operational and accessible even in the event of hardware or
software failures, through techniques like clustering or
replication.
RTO/RPO (Recovery Time Objective / Recovery Point
Objective): Metrics used to define how quickly data should be
restored (RTO) and how much data loss is acceptable (RPO).
Testing: Regularly testing backup and disaster recovery plans
to ensure that they will work as expected in case of an actual
disaster.
Thank you
very much!