Friday, October 10, 2014

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

image_thumb[5]

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

image_thumb[6]

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. 

image

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates