Estou trabalhando em um pacote SSIS que atualizará os bancos de dados dev dos backups de produção. O pacote está em vigor há anos, mas estamos implantando grupos de disponibilidade na instância dev, portanto, as tarefas precisam ser alteradas para funcionar com as réplicas primárias e secundárias. Background: todas as instâncias são SQL Server 2014 SP2. Uma instância de produção (PD1), duas instâncias dev (DV3 e DV7) e uma instância de utilitário (DV1), onde o pacote SSIS é implantado no SSISDB e onde o trabalho do SQL Agent é executado. No pacote SSIS, depois que os backups completos são obtidos e colocados em um compartilhamento de rede da produção, o próximo conjunto de tarefas (em um pacote SSIS separado) precisa descobrir qual nó hospeda a réplica primária. Estou seguindo os passos listados aquipara fazer esse trabalho, e tudo funciona muito bem se for codificado (o que, é claro, não funcionará se/quando ocorrer um failover). A única maneira que consegui pensar em abordar isso é, em tempo de execução, avaliar qual instância é primária e prosseguir com as tarefas (que, a propósito, são todos objetos "Execute SQL Task" - exigindo sua própria definição de conexão ). Vi alguns exemplos de como usar expressões em ConnectionStrings e variáveis, mas não consigo descobrir como definir o valor retornado da consulta abaixo no SQL Server para retornar a instância correta para definir a string de conexão no pacote.
select cs.replica_server_name
from sys.dm_hadr_availability_replica_states rs
join sys.dm_hadr_availability_replica_cluster_states cs
on rs.replica_id = cs.replica_id
join sys.dm_hadr_name_id_map n
on rs.group_id = n.ag_id
where rs.role = 2
and n.ag_name = 'DVAG001'
Idealmente, haveria dois desses gerenciadores de conexões, um para o primário e outro para o secundário, que eu poderia usar nos objetos "Execute SQL Task". Então tudo funcionaria perfeitamente. Pode muito bem haver outras maneiras de fazer isso, mas não tenho muita experiência em SSIS ou desenvolvimento fora do TSQL.
ATUALIZAÇÃO: O processo acima é feito todos os dias... e é por isso que estou usando um pacote SSIS e SQL Agent. Assim, todo o processo tem que ser automatizado e ter todos os valores necessários - principalmente a determinação dos estados das réplicas (primário e secundário) - dentro do pacote para que nenhuma intervenção manual seja necessária.
Vamos supor que você tenha um grupo de disponibilidade com apenas uma réplica secundária (você pode expandir facilmente este exemplo para mais réplicas)
1. Conexões
Você precisará de 3 conexões:
2. Variáveis
Crie 2 variáveis com tipo de dados String
3. Consulta
Para mapear um conjunto de resultados de consulta para uma variável usando Executar Tarefa SQL, sua consulta precisa retornar apenas uma linha. Você mapeará cada coluna para a variável que desejar. Sabendo disso, alterei sua consulta para o seguinte:
(Sinta-se à vontade para alterá-lo como desejar, apenas lembre-se de retornar apenas uma linha)
4. Mapeando o conjunto de resultados para variáveis
No execute SQL Task Editor, cole a consulta acima, defina a conexão OLEDB 'AG listener' e altere o conjunto de resultados para 'Single row'
Em seguida, vá para a guia 'Conjunto de resultados' e mapeie as colunas para as variáveis
5. Altere as cadeias de conexão
Clique no gerenciador de conexões 'Primário' e vá até a aba 'Propriedades', depois clique em 'Expressões', selecione a propriedade 'Nome do Servidor' e adicione sua variável na Expressão.
Repita o processo para o gerenciador de conexões 'Secundário'
6. Verifique o processo
Para validar, você pode adicionar outras etapas e usar pontos de interrupção para verificar como suas variáveis estão sendo executadas. Os próximos passos dependem de você. Agora você tem uma conexão apontando para cada servidor do seu AG.
Como estratégia alternativa, posso considerar simplificar as coisas sem a necessidade de parâmetros SSIS usando a execução T-SQL (sem parâmetros necessários). SSIS, especialmente pacotes legados, sempre parecem tão propensos a erros e bugs que eu simplesmente não quero tocá-los se não for necessário:
R Para começar, considere executar sua "Verificação de AG primário" nos próprios servidores de produção, em vez dos servidores de desenvolvimento.
B. Em seguida, importe seus pacotes SSIS para produção se eles ainda não estiverem lá -- para serem executados lá. De qualquer forma, pode-se considerar as restaurações dev do prod mais como um processo de produção.
C. E, finalmente, evite ao máximo alterar seus pacotes SSIS herdados executando o trabalho do Agente T-SQL que invoca a execução do pacote SSIS (restauração do banco de dados) de cada AG primário "potencial" usando o seguinte código - ou algo parecido - - conforme mostrado na Listagem A.
Listagem A: Execute o pacote SSIS (restauração do banco de dados) somente quando o SQL Server for o principal -- por meio de um trabalho do SQL Agent.
Observação:
Se estou entendendo bem, os servidores dev sempre terão o mesmo nome. São apenas os nomes dos servidores de produção que têm o potencial de mudar. Dado isso, acredito que o código mostrado na Listagem A permitirá que você realize o que precisa com um pouco menos de tempo e esforço. Você pode até pegar carona nessa execução no final de seus backups SQL para obter uma atualização mais imediata dos ambientes de desenvolvimento.