/*==================================================================================
Curso: SQL SERVER 2019
https://www.udemy.com/course/draft/3957796/?referralCode=FB10D369E786D9FE8A48

Instrutor: Sandro Servino
https://www.linkedin.com/in/sandroservino/?originalSubdomain=pt
https://filiado.wixsite.com/sandroservino

AZURE - Serviço na Nuvem da Microsoft
EXTRAS, ELASTIC POOLS - logins, users, groups e jobs

Uma alternativa ao logic apps para rodar tarefas em uma database ou varias databases.

O Azure Job Agent por exemplo permite que você execute consultas SQL ou tarefas como reindexacao, dbcc, etc 
em vários bancos de dados de uma vez, através de jobs.

Os procedimentos de execucao de jobs e schedules podem ser executados tambem em simples SQL Databases.

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview
https://www.youtube.com/watch?v=HMvBVCMeApw

==================================================================================*/

-- VAMOS AO LAB

-- PASSOS

-- 1. Vamos acessar o servidor primario srvep1.database.windows.net que foi configurado no elastic pool FAILOVER GROUP, como por exemplo o que foi
-- criado nas aulas passadas: fgsrvep.database.windows.net

- 2. Precisamos de 2 logins:
--ElasticJobUser executará as consultas em cada banco de dados de destino do usuario
--ElasticJobMaster será executado no banco de dados que ira armazenar os job, no caso o banco ElasticJobDB, que será criado.
- Estes devem ser criados em cada servidor de destino, srvep1.database.windows.net e srvepsecond.database.windows.net

USE MASTER
-- ElasticJobUser
CREATE LOGIN ElasticJobUser WITH PASSWORD = 'anywayLmA5iikevKXy8wS=';
GO
-- ElasticJobmaster
CREATE LOGIN ElasticJobMaster WITH PASSWORD = 'anywayLmA5iikevKXy8wS=';
GO

-- No servidor primario, crie um banco de dados chamado 
-- ElasticJobDB que ira receber os jobs necessários para rodar nossas tarefas.

CREATE DATABASE ElasticJobDB 
GO

-- 3. No Azure pesquisar Elastic Job agents e criar. Crie com um nome elasticjobagent1, por exemplo e vamos selecionar o banco criado ElasticJobDB
-- onde serao ja criados procedures de sistema.

-- 4. Pesquisar failover group fgsrvep e adiciona a base de dados ElasticJobDB no Failover Group. Ira replicar esta base de dados para o servidor secundario.

-- 5. se conectar no server srvep1.database.windows.net e na base de dados ElasticJobDB mas sem usar o comando USE ElasticJobDB 
CREATE USER ElasticJobMaster FOR LOGIN ElasticJobMaster WITH DEFAULT_SCHEMA = dbo;
GO
EXEC sp_addrolemember N'db_owner', N'ElasticJobMaster';
GO

-- OBS> REPARE QUE O USUARIO FOI REPLICADO COM USER DA MESMA TABELA NO SERVIDOR SECUNDARIO srvepsecond.database.windows.net

- 6. O Elastic Job Agent usará credenciais para se conectar aos bancos de dados de destino e fazer login como o usuário Elasticuser.
-- Rodar no banco de dados ElasticJobUser do SERVER PRIMARIO. Quando fizer isto isto será replicado para o servidor secundário.
 
-- Create a db master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD='anywayLmA5iikevKXy8wS=';
GO
-- Create a database scoped credential for job execution.
CREATE DATABASE SCOPED CREDENTIAL ElasticJobUserCredential WITH IDENTITY = 'ElasticJobUser', SECRET = 'anywayLmA5iikevKXy8wS=';
GO
-- Create a database scoped credential for the master user
CREATE DATABASE SCOPED CREDENTIAL ElasticJobMasterCredential WITH IDENTITY = 'ElasticJobMaster', SECRET = 'anywayLmA5iikevKXy8wS=';
GO

-- 7. Rodar em cada banco de usuario, servidor primario, para dar acesso ao user ElasticJobUser para rodar os jobs, por exemplo para poder fazer reindexacao, dbcc, criar tabelas,...
-- 7.1 Vamos por exemplo nos conectar ao banco db2 e dar permissao para o elasticjobuser para poder ler e gravar em todas as tabelas, mas poderiamos dar permissao
--     por exemplo de db_owner.

CREATE USER ElasticJobUser FOR LOGIN ElasticJobUser WITH DEFAULT_SCHEMA = dbo;
GO

--give suitable permissions to the Job user. In my case read and write
EXEC sp_addrolemember N'db_datareader', N'ElasticJobUser';
EXEC sp_addrolemember N'db_datawriter', N'ElasticJobUser';
GO

-- OBS> REPARE QUE O USER FOI CRIADO NO SERVIDOR SECUNDARIO, DENTRO DA BASE DE DADOS db2.

--8. Agora que configuramos as contas para executar Elastic Jobs, podemos criar grupos-alvo, jobs e agendas

-- RODAR NO SERVER PRIMARIO, NA BASE ElasticJobDB. NAO PRECISA RODAR NO SECUNDARIO.

-- 8.1 VAMOS CRIAR O Target group, que sera um grupo que reunira todos os bancos que iremos querer rodar jobs.
EXEC jobs.sp_add_target_group 'ShardServerGroup1';

- Adicionamos alvos ao grupo. Podem ser servidores SQL ou bancos de dados individuais em um servidor

- Podemos adicionar um servidor completo como membro alvo. O job será executado em todos os bancos de dados desse servidor
- Neste caso, ele precisa saber o nome da credencial do login do Jobmaster porque usará esse login para enumerar os nomes dos bancos de dados nesse servidor

-- CONECTAR NA BARRA DE BOTOES AO BANCO ElasticJobDB
EXEC jobs.sp_add_target_group_member
      @target_group_name = 'ShardServerGroup1'
    , @target_type = 'SqlServer'
    , @refresh_credential_name='ElasticJobMasterCredential'
    , @server_name='srvep1.database.windows.net';

EXEC jobs.sp_add_target_group_member
      @target_group_name = 'ShardServerGroup1'  
    , @target_type = 'SqlServer'
    , @refresh_credential_name='ElasticJobMasterCredential'
    , @server_name='srvepsecond.database.windows.net';


-- DICA
-- Mais provável, uma vez que o servidor pode ter outros bancos de dados que não queremos ter como alvo, iremos apenas adicionar os bancos de dados que queremos ter como alvo.
- Isso não requer o parâmetro de atualização de credencial. Ele usará o ElasticJobUserCredential que criamos anteriormente
EXEC jobs.sp_add_target_group_member
      @target_group_name = 'ShardServerGroup1'
    , @target_type = 'Sqldatabase'
    , @server_name='srvep1.database.windows.net'
    , @Database_name = 'db2' -- PODERIA AO INVES DE DAR PERMISSAO DE RODAR EM TODOS OS BANCOS DE DADOS DO SERVIDOR, PODERIA SER APENAS NO BANCO ESPECIFICO DB2
 
EXEC jobs.sp_add_target_group_member
      @target_group_name = 'ShardServerGroup1'
    , @target_type = 'Sqldatabase'
    , @server_name='srvepsecond.database.windows.net'
    , @Database_name = 'db2';

--DICA, PODEMOS INCLUIR UM NOVO BANCO DE DADOS ESPECIFICO DO GRUPO 
EXEC jobs.sp_add_target_group_member
      @target_group_name = 'ShardServerGroup1'
    , @target_type = 'Sqldatabase'
    , @server_name='srvep1.database.windows.net'
, @Database_name = 'sqlelasticpoolsdbs'
, @Membership_type='include';

EXEC jobs.sp_add_target_group_member
      @target_group_name = 'ShardServerGroup1'
    , @target_type = 'Sqldatabase'
    , @server_name='srvepsecond.database.windows.net'
, @Database_name = 'sqlelasticpoolsdbs'
, @Membership_type='include';

-- visualizar os membros de um grupo
SELECT target_group_name, membership_type, target_type, server_name,database_name,target_id FROM jobs.target_group_members
WHERE target_group_name='ShardServerGroup1';

-- 9. AGORA VAMOS CRIAR UM JOB

-- CONECTAR ElasticJobDB, servidor primario
EXEC jobs.sp_add_job @job_name='db2Job1'
    , @description='Run Query on db2';
 
-- VAMOS ACRESCENTAR STEPS AO JOB

EXEC jobs.sp_add_jobstep
      @job_name='db2Job1'
    , @command = N'SELECT @@Servername;'
    , @credential_name= 'ElasticJobUserCredential'
    , @target_group_name='ShardServerGroup1'

-- Visualizar o job 
USE ElasticJobDB
SELECT [job_name]
    , [step_id]
    , [command_type]
    , [command]
    , [credential_name]
    , [target_group_name]
FROM [jobs].[jobsteps]
WHERE job_name = 'db2Job1';

-- Executar o job
-- conectar na ElasticJobDB
exec jobs.sp_start_job 'db2Job1'
 
-- Veja a execução mais recente. Não há saída ou log retornado do trabalho, portanto, não há como ver o que foi feito. Nós apenas obtemos sucesso ou fracasso
-- mas poderia ser uma reindexacao, dbcc, um proc para alterar os dados de uma tabela

SELECT is_active
    , lifecycle
    , last_message
    , target_type
    , target_resource_group_name
    , target_server_name
    , target_database_name
, target_elastic_pool_name
FROM jobs.job_executions
WHERE job_name = 'db2Job1'
AND job_execution_id = (SELECT job_execution_id FROM jobs.job_executions WHERE step_id IS NULL and create_time =
        (SELECT MAX(create_time)  FROM jobs.job_executions WHERE step_id IS NULL))
ORDER BY start_time DESC;
GO

--Podemos criar um job que grava a saída de nossa consulta em uma tabela no banco de dados de trabalhos
EXEC jobs.sp_add_job @job_name ='db2Job1log', @description='Get Row Counts';
GO

EXEC jobs.sp_add_jobstep
@job_name='db2Job1log',
@command= N'SELECT @@Servername as servername;',
@credential_name='ElasticJobUserCredential',
@target_group_name='ShardServerGroup1',
@output_type='SqlDatabase',
@output_credential_name='ElasticJobMasterCredential',
@output_server_name='azpasdbsep1.database.windows.net',
@output_database_name='ElasticJobDB',
@output_schema_name='dbo',
@output_table_name='tablog', -- vai guardar o log nesta tabela
@retry_attempts = 2;


-- Execute o job
exec jobs.sp_start_job 'db2Job1log'
 
--Visualize o historico de execucao com sucesso ou falha. 
SELECT is_active
    , lifecycle
    , last_message
    , target_type
    , target_resource_group_name
    , target_server_name
    , target_database_name
, target_elastic_pool_name
, start_time
FROM jobs.job_executions
WHERE job_name = 'db2Job1log'
ORDER BY start_time DESC;
GO

-- SE APARECER ALGUM ERRO DE FIREWALL PARA LIBERAR SEU IP
-- Update server-level firewall setting to create a range of allowed IP addresses
exec sp_set_firewall_rule N'freeUSERNAME', 'IPINICIAL', 'IPFINAL';  
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database?view=azure-sqldw-latest
-- Pode demorar alguns minutos para ser liberado

-- VERIFIQUE TABELA DE LOG NO BANCO ElasticJobDB

--AGENDAR Jobs
DECLARE @ScheduleStartTime DATETIME2 = DATEADD(mi,1,getdate());
DECLARE @ScheduleEndTime DATETIME2 = DATEADD(mi,45,getdate());
EXEC jobs.sp_update_job @job_name = 'db2Job1log',
                     @schedule_interval_type = 'Days',
                     @schedule_interval_count = 1,
                     @schedule_start_time = @ScheduleStartTime,
@schedule_end_time = @ScheduleEndTime,
@enabled = 1;

-- RETORNAR AO PORTAL PARA VERIFICAR A LISTA DE EXECUCOES.
-- PESQUISER Elastic Job agent, selecione o elastic job agent criado, clique depois no menu Overview e veja as tarefas rodando.


-- FIM LAB