aiDeeva.com

Category: Backup & Recovery

  • Types of Modern World Database Administrators

    1. System DBA

    • Responsibilities:
      • Focus on the physical and technical aspects of database management.
      • Install, configure, and upgrade database software.
      • Manage the operating system and hardware that the database runs on.
      • Monitor system performance and manage system resources.
      • Implement and manage database security.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL, DB2
      • Operating Systems: Linux, Windows, Unix
      • Virtualization: VMware, Hyper-V
      • Cloud Platforms: AWS, Azure, Google Cloud Platform (GCP)
      • Cloud Databases: Amazon RDS, Azure SQL Database, Google Cloud SQL, Amazon Aurora
      • Cloud Storage: Amazon S3, Azure Blob Storage, Google Cloud Storage
      • Monitoring Tools: Amazon CloudWatch, Azure Monitor, Google Stackdriver
      • Backup Solutions: AWS Backup, Azure Backup, Google Cloud Backup and DR

    2. Database Architect

    • Responsibilities:
      • Design the overall database structure and architecture.
      • Develop and maintain database models and standards.
      • Plan for scalability and performance improvements.
      • Work with application developers to design and optimize queries.
      • Ensure data integrity and normalization.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL, MongoDB
      • Modeling Tools: ERwin, Microsoft Visio, Lucidchart
      • Data Warehousing: Amazon Redshift, Snowflake, Google BigQuery
      • ETL Tools: AWS Glue, Azure Data Factory, Google Dataflow
      • Cloud Platforms: AWS, Azure, Google Cloud Platform (GCP)
      • Infrastructure as Code (IaC): AWS CloudFormation, Azure Resource Manager (ARM) templates, Google Deployment Manager

    3. Application DBA

    • Responsibilities:
      • Focus on managing and optimizing the database from the application’s perspective.
      • Work closely with developers to understand the database needs of applications.
      • Tune SQL queries and database performance for applications.
      • Ensure database changes and deployments are aligned with application requirements.
      • Manage database objects such as tables, indexes, and views used by applications.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL
      • Application Servers: AWS Elastic Beanstalk, Azure App Service, Google App Engine
      • ORM Tools: Hibernate, Entity Framework, Sequelize
      • Performance Tuning: AWS RDS Performance Insights, Azure SQL Database Advisor, Google Cloud SQL Insights
      • Version Control: AWS CodeCommit, Azure Repos, Google Cloud Source Repositories

    4. Development DBA

    • Responsibilities:
      • Support development projects by creating and managing development databases.
      • Collaborate with development teams to design database schemas.
      • Develop and optimize stored procedures, functions, and triggers.
      • Participate in code reviews and ensure best practices for database programming.
      • Assist in testing and deploying database changes.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL
      • Development Languages: PL/SQL, T-SQL, Python, Java, C#
      • Version Control: Git (GitHub, GitLab, Bitbucket)
      • CI/CD Tools: AWS CodePipeline, Azure DevOps, Google Cloud Build
      • Testing Tools: JUnit, pytest, SQL Unit Test

    5. Data Warehouse DBA

    • Responsibilities:
      • Manage data warehouse environments.
      • Design and implement ETL (Extract, Transform, Load) processes.
      • Optimize the performance of data warehouse queries and reports.
      • Ensure data quality and integrity within the data warehouse.
      • Work with BI (Business Intelligence) tools and support data analytics needs.
    • Technologies:
      • Data Warehousing: Amazon Redshift, Snowflake, Google BigQuery, Azure Synapse Analytics
      • ETL Tools: AWS Glue, Azure Data Factory, Google Dataflow
      • BI Tools: AWS QuickSight, Microsoft Power BI, Google Data Studio
      • SQL: Advanced SQL, Window Functions, Analytical SQL
      • Cloud Platforms: AWS, Azure, Google Cloud Platform (GCP)

    6. Operational DBA

    • Responsibilities:
      • Focus on the day-to-day operation and maintenance of databases.
      • Monitor database performance and troubleshoot issues.
      • Perform regular backups and ensure data recovery processes.
      • Manage database user accounts and permissions.
      • Implement and manage database security policies.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL, DB2
      • Backup Solutions: AWS Backup, Azure Backup, Google Cloud Backup and DR
      • Monitoring Tools: Amazon CloudWatch, Azure Monitor, Google Stackdriver
      • Automation Scripts: Shell scripting, PowerShell, AWS Lambda, Azure Functions
      • Cloud Platforms: AWS, Azure, Google Cloud Platform (GCP)
      • Security Tools: AWS IAM, Azure AD, Google Cloud IAM

    7. Cloud DBA

    • Responsibilities:
      • Manage databases hosted in cloud environments (e.g., AWS, Azure, Google Cloud).
      • Ensure optimal configuration and performance of cloud-based databases.
      • Manage cloud-specific database services like Amazon RDS, Azure SQL Database, etc.
      • Implement cloud-specific security and compliance measures.
      • Monitor and manage cloud resource usage and costs.
    • Technologies:
      • Cloud Platforms: AWS, Azure, Google Cloud Platform (GCP)
      • Cloud Databases: Amazon RDS, Azure SQL Database, Google Cloud SQL, Amazon Aurora, Google BigQuery, Azure Cosmos DB
      • Infrastructure as Code (IaC): Terraform, AWS CloudFormation, Azure Resource Manager (ARM) templates
      • Monitoring Tools: AWS CloudWatch, Azure Monitor, Google Cloud Monitoring
      • Security Tools: AWS IAM, Azure AD, Google Cloud IAM

    8. DevOps DBA

    • Responsibilities:
      • Integrate database management with DevOps practices.
      • Automate database deployment and configuration using scripts and tools.
      • Collaborate with DevOps teams to ensure continuous integration and delivery (CI/CD) of database changes.
      • Implement monitoring and logging for databases as part of the DevOps pipeline.
      • Ensure database environments are consistent across development, testing, and production.
    • Technologies:
      • CI/CD Tools: AWS CodePipeline, Azure DevOps, Google Cloud Build, Jenkins
      • Configuration Management: Ansible, Puppet, Chef
      • Containerization: Docker, Kubernetes, AWS EKS, Azure AKS, Google Kubernetes Engine (GKE)
      • Scripting Languages: Bash, Python, PowerShell
      • Monitoring Tools: Prometheus, Grafana, AWS CloudWatch, Azure Monitor, Google Cloud Monitoring

    9. Performance Tuning DBA

    • Responsibilities:
      • Focus on optimizing database performance.
      • Analyze and tune SQL queries for efficiency.
      • Monitor and optimize database indexes and storage.
      • Identify and resolve performance bottlenecks.
      • Work with developers and other DBAs to implement performance improvements.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL
      • Performance Tools: Oracle AWR, SQL Server Profiler, EXPLAIN (PostgreSQL), MySQL Performance Schema
      • Indexing Tools: DBMS_STATS (Oracle), SQL Server Index Tuning Wizard
      • Monitoring Tools: AWS RDS Performance Insights, Azure SQL Database Advisor, Google Cloud SQL Insights

    10. Security DBA

    • Responsibilities:
      • Ensure databases are secure from internal and external threats.
      • Implement and manage database encryption, authentication, and authorization.
      • Conduct security audits and vulnerability assessments.
      • Develop and enforce database security policies and procedures.
      • Monitor for security breaches and respond to incidents.
    • Technologies:
      • Database Systems: Oracle, SQL Server, MySQL, PostgreSQL
      • Security Tools: AWS IAM, Azure AD, Google Cloud IAM, Oracle Data Vault, SQL Server TDE, pgcrypto (PostgreSQL)
      • Auditing Tools: AWS CloudTrail, Azure Security Center, Google Cloud Audit Logs
      • Encryption: SSL/TLS, TDE (Transparent Data Encryption)
      • Authentication: Kerberos, LDAP, Active Directory

  • T-SQL Script to get the Symantec NETBACKUP Version and Patch Information

    You can execute below script to get Symantec NETBACKUP version and patch information.

    set nocount on

CREATE TABLE #OUTPUT(
	[SQLInstanceName] [nvarchar](128) NULL,
	[WindowsServerName] [varchar](30) NULL,
	[NetBackupClientVersion] [varchar](255) NULL,
	[NetBackupPatchVersion] [varchar](255) NULL,
	[NetbackupStatusCheck] [varchar](28) NULL
) 

declare @regkeyval varchar(20), @value varchar(255), @rc int
exec @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Veritas\NetBackup\CurrentVersion\',
  @value_name='VERSION',
  @regkeyval=@value output

set @value =isnull(@value,'-')

declare @Pvalue varchar(255)
exec @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Veritas\Patches\NetBackup\',
  @value_name='PatchLevel',
  @regkeyval=@Pvalue output

set @Pvalue =isnull(@Pvalue,'-')

if @value <> '-'
insert into #OUTPUT(SQLInstanceName,WindowsServerName,NetBackupClientVersion,NetBackupPatchVersion,NetbackupStatusCheck)
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,@value as NetBackupClientVersion 
,@Pvalue as NetBackupPatchVersion
,'NetBkup is installed' as NetbackupStatusCheck 
else
insert into #OUTPUT(SQLInstanceName,WindowsServerName,NetbackupStatusCheck,NetBackupClientVersion,NetBackupPatchVersion)
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,'NetBkup nt installed' as NetbackupStatusCheck
,'0' as NetBackupClientVersion
,'0' as NetBackupPatchVersion 

select * from #OUTPUT

  • How to restart an Interrupted Database Restore in SQL Server?

    Steps to restart an Interrupted Database Restore in SQL Server

    There may scenario when you are restoring VLDB database backup and the restore database command interrupted due network failure, power failure, SQL Server Service restart or cluster failover. In this kind of scenario you can easily start the interrupted restore from the point of failure using WITH RESTART command.

    Before you start restoring the database using WITH RESTART option check database is in Restoring State not in any other state.

    You can check the database state either from SSMS object explorer or by executing below T-SQL command

    select name,state_desc from sys.databases

    Once confirmed that database is in restoring state execute the backup command WITH RESTART clause

    RESTORE DATABASE [AdventureWorks]
FROM DISK ='D:\Backup\AdventureWorks.bak'
WITH RESTART, STATS = 20
GO

  • Database Backup WITH CHECKSUM

    Backup WITH CHECKSUM

    When WITH CHECKSUM option is used while taking backup, the backup process will verify each page for checksums and torn page. In case bad page checksum found, the backup will stop. Using backup checksums may affect workload and backup throughput.

    If you want to continue the backup, you have to write CONTINUE_AFTER_ERROR in backup WITH clause.
    In case backup completes successfully it means no bad checksums.

    Example

    backup database sqldba to disk = 'c:\jspace\sqldba1.bak'
with checksum

    You can also use the 3023 trace flag for the check sum option, when 3023 trace flag is on WITH CHECKSUM option will be applied all the backups by default.

    DBCC TRACEON(3023)
GO                              

backup database sqldba to disk = 'c:\jspace\sqldbatraceflag.bak'

DBCC TRACEOFF(3023)
GO

    Let’s check if the 3023 trace flag applied the page checksum to above backup command or not. Execute below query for it.

    RESTORE HEADERONLY
FROM DISK = 'c:\jspace\sqldbatraceflag.bak'
GO

    HasBackupCheckSums value is 1, it means that WITH CHECKSUM option applied to backup using 3023 trace flag.
    checksum

  • T-SQL Script to Check the Native Compressed backup file size and Backup Compression Ratio

    First of all make sure backup compression is enabled on the SQL Server. You can execute below query to check the backup compression.

    select name,[description],value_in_use from sys.configurations where name like '%backup%'

    Execute below script to check the compressed backup file size and backup compression ratio.

    Declare @FromDate as datetime
-- Specify the from date value
set @FromDate = GETDATE() -1

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SQLServerName, 
   msdb.dbo.backupset.database_name,  
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
       WHEN 'I' THEN 'Differential'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
   msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
   CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0 
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date