Tag Archives: backups

How to Secure Your Azure SQL Database

Azure, Data, Database Administrator, SQL Server, , , , , , , , , , , , , , , ,
  1. Authentication and Authorization:
    • Use Azure Active Directory (Azure AD) authentication for better security.
    • Implement firewall rules to control access to your database.
    • Assign minimal permissions to users based on their roles (principle of least privilege).
  2. Encryption:
    • Enable Transparent Data Encryption (TDE) to protect data at rest.
    • Use Always Encrypted to secure sensitive data in transit.
    • Consider client-side encryption for additional protection.
  3. Auditing and Monitoring:
    • Enable Azure SQL Auditing to track database activity.
    • Set up Azure Monitor to receive alerts and insights.
    • Regularly review logs and audit trails.
  4. Network Security:
    • Isolate your database using Virtual Network Service Endpoints.
    • Restrict public access and use private endpoints.
  5. Patch Management:
    • Keep your database engine up to date with the latest security patches.
    • Regularly review vulnerability assessments.
  6. Backup and Recovery:
    • Implement automated backups and test recovery procedures (remember a backup is only theoretically there unless it has been tested and proven to work).
    • Store backups conforming to the 3-2-1 Backup Rule explained below (do not assume your backups are safe just because they are in the cloud).

The 3-2-1 Backup Rule: Ensuring Data Resilience

The 3-2-1 Rule is a robust strategy that emphasizes redundancy, resilience, and data availability. Here’s what it entails:

  1. Three Copies of Your Data:
    • Maintain the original data and create at least two additional copies.
  2. Two Different Types of Media for Storage:
    • Store your data on distinct forms of media (e.g., hard drives, tapes) to enhance redundancy.
  3. At Least One Copy Off-Site:
    • Safeguard one backup copy in an off-site location, separate from your primary data and on-site backups.

By adhering to this rule, you mitigate single points of failure, protect against corruption, and ensure data safety even in unexpected events or disasters

How to tell if Virtual Machine backups are running full database backups on a SQL Server instance

Database Administrator, dba, SQL Server, ssms, T-SQL, , , , , , , , , , , , , , ,

Some Virtual Machine backup solutions use the SQL Server VSS writer service to make SQL Server database backups as part of the VM backup.

You may not have had visibility on this process so to confirm if this is happening in your environment open up SQL Server Management Studio (SSMS) then in Object Explorer right click on the instance name, click on new query and run the script below. 

This query looks for entries in the physical_device_name field of msdb.dbo.backupmediafamily that are not directory paths.

SELECT @@SERVERNAME AS ServerName
	,bs.backup_set_id AS BackupSetId
	,bs.database_name AS DbName
	,bs.backup_start_date AS BackupStartDate
	,bs.backup_finish_date AS BackupFinishDate
	,CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size]
	,bs.[type] AS BackupType
	,CASE bs.[type]
		WHEN 'D'
			THEN 'Full Backup'
		WHEN 'I'
			THEN 'Differential Backup'
		WHEN 'L'
			THEN 'TLog Backup'
		WHEN 'F'
			THEN 'File or filegroup'
		WHEN 'G'
			THEN 'Differential file'
		WHEN 'P'
			THEN 'Partial'
		WHEN 'Q'
			THEN 'Differential Partial'
		END AS BackupTypeDescription
	,bmf.physical_device_name AS BackupFilePath
	,CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLogSeqNo
	,CAST(bs.last_lsn AS VARCHAR(50)) AS LastLogSeqNo
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK) ON bs.media_set_id = bmf.media_set_id
WHERE bs.[type] = 'D'
	AND bmf.physical_device_name NOT LIKE '%\%'
ORDER BY bs.database_name ASC
	,bs.backup_set_id ASC;

 

For the field BackupFilePath if you see GUID looking entries like {AQ8F5957-2804-4X05-9EE5-109EB87EBCAB}5 the VM backup is probably running full database backups on the SQL Server.

It might be time to review your recovery strategy as the VM backups could be covering the same ground as the SQL Server backups creating a lot of expensive redundancy due to unnecessary replication of work.