PREREQUISITES
- Setup the Azure Storage
- Setup the Certificate and credential
Refer here: http://www.sqlpanda.com/2014/10/sql-server-2014-cloud-enhancements-1.html
Auto management Backup on Database
- Available in SQL 2014
- FULL and T-Log backup supported
- Full or Bulk Logged mode supported
If we try to backup the simple recovery model db, we will get below error.
Msg 45207, Level 17, State 1, Procedure sp_add_task_command, Line 211
SQL Server Managed Backup to Windows Azure cannot configure the database, 'NorthWind',
because the recovery model of the database is 'Simple'.
SQL Server Managed Backup to Windows Azure is only supported on Full and Bulk-Logged recovery models.
--- Enable auto backup to Azure storage on single DB
use master;
go
SELECT * FROM sys.credentials where name='sqlpandaAzureCred'
go
select name,recovery_model_desc from sys.databases
--- Enable
EXEC msdb.smart_admin.sp_set_db_backup
@database_name ='AzureDB'
,@enable_backup = 1
,@retention_days = '30' -- Min 1 and Max 30
,@credential_name = 'sqlpandaAzureCred'
-- ,@encryption_algorithm = 'NO_ENCRYPTION'
,@encryption_algorithm = 'AES_256'
,@encryptor_type= 'Certificate'
,@encryptor_name='BackupCert'
--- Checking Database Meta data
SELECT * FROM msdb.smart_admin.fn_backup_db_config ('AzureDB')
WHERE is_managed_backup_enabled = 1 and is_dropped = 0
-- Performing a backup on Demand for a DB configured with Managed Backup
Exec MSDB.smart_admin.sp_backup_on_demand @Database_name = 'AzureDB',@type = 'DATABASE'
-- Disable SQL Server Managed Backup
EXEC msdb.smart_admin.sp_set_db_backup
@database_name='AzureDB'
,@enable_backup=0;
GO
-- Enable SQL Server Managed Backup
EXEC msdb.smart_admin.sp_set_db_backup
@database_name='AzureDB'
,@enable_backup=1;
GO
Management backup will auto create the folder as HOSTNAME-INSTANCENAME format , ex
sgc4-sgc4sql2014
Auto management Backup on Instance
Enable auto management backup on the instance is similar as it on database.
- Only new database created after auto management backup enable would add to the backup list.
- New database may take up to 15 minutes before it appear on the backup list.
- Database level configure override the instance level. This provide more granular control.
-- Enable Managed Backup at Instance Level
EXEC msdb.smart_admin.sp_set_instance_backup
@enable_backup = 1
,@retention_days = '30' -- Min 1 and Max 30
,@credential_name = 'sqlpandaAzureCred'
-- ,@encryption_algorithm = 'NO_ENCRYPTION'
,@encryption_algorithm = 'AES_256'
,@encryptor_type= 'Certificate'
,@encryptor_name='BackupCert'
-- Checking Instance level Backup
SELECT * FROM msdb.smart_admin.fn_backup_instance_config ()
-- Look at all the databases in the instance where this
-- SMART backup is configured.
SELECT * FROM msdb.smart_admin.fn_backup_db_config (Null)
WHERE is_managed_backup_enabled = 1 and is_dropped = 0
Other management procedure
- msdb.smart_admin.sp_backup_master_switch
- msdb.smart_admin. sp_backup_master_switch
- msdb.smart_admin.sp_set_instance_backup
- msdb.smart_admin.sp_set_parameter
-- Backup Files created as part of Managed Backup ***
SELECT * FROM msdb.dbo.smart_backup_files
-- To Pause Managed Backup
EXEC msdb.smart_admin.sp_backup_master_switch @new_state=0;
GO
-- To resume Managed Backup
EXEC msdb.smart_admin. sp_backup_master_switch @new_state=1;
GO
-- Disable Managed Backup
EXEC msdb.smart_admin.sp_set_instance_backup
@enable_backup=0;
-- Change scan frequence
EXEC msdb.smart_admin.sp_set_parameter
@parameter_name = 'SSMBackup2WAFrequency',
@parameter_value = '60' --Unit in seconds
By default, SQL Server scan every 15 minutes to see if there are any new databases.
Check the admin event
-- view all admin events
Use msdb;
Go
DECLARE @startofweek datetime
DECLARE @endofweek datetime
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
DECLARE @eventresult TABLE
(event_type nvarchar(512),
event varchar (512),
timestamp datetime
)
INSERT INTO @eventresult
EXEC smart_admin.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek
SELECT * from @eventresult
-- WHERE event_type LIKE '%admin%'
order by timestamp
[SSMBackup2WAAdminXevent] Database Name = N/A, Database ID = 0,
Stage = CentralLoop, Error Code = 99999,
Error Message = Keyword not supported: 'applicationintent'.,
Additional Info = SSMBackup2WA central processing algorithm hit an exception.
The failure might be transient. Operation will be retired.
I could not find any useful information from google and the new database does not appear in the Azure .
Applicationintent seems to be Always-on related but this sql server is not configured for always-on.
I have to thank for sharing this blog, really helpful to all.
ReplyDeleteAzure Training center in Chennai | Azure course in Chennai | Windows Azure course in Chennai | AWS course in Chennai | AWS Certification in Chennai | DevOps Training | DevOps course in Chennai
Very impressive by reading this post
ReplyDeleteazure certification training course chennai
Great Article. Thank you for providing such a unique and valuable information to your readers. I really appreciate your work. Oracle Manufacturing Cloud training in bangalore
ReplyDelete