4 SQL Server 2014 Cloud Enhancements (2) : Auto management Backup

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

During the testing I get this strange error:

[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.

Reference

• http://technet.microsoft.com/en-us/sbs/dn449491(v=sql.100).aspx
• http://geekletter.com/tag/sql-server-2014/