Gerenciar metadados ao disponibilizar um banco de dados em outro servidor

Aplica-se a:SQL Server

Este artigo é pertinente nas seguintes situações:

  • Ao configurar as réplicas de disponibilidade de um grupo de disponibilidade de grupos de disponibilidade Always On.

  • Ao configurar o espelhamento de banco de dados de um banco de dados.

  • Ao preparar a alteração de funções entre servidores primário e secundário em uma configuração de envio de logs.

  • Ao restaurar um banco de dados para outra instância de servidor.

  • Ao anexar uma cópia de um banco de dados a outra instância do servidor.

  • Ao executar a atualização do mecanismo de banco de dados usando o método: migrar para uma nova instalação.

  • Ao migrar bancos de dados para o SQL do Azure (Máquina Virtual ou Instância Gerenciada).

Alguns aplicativos dependem de informações, entidades e/ou objetos que estão fora do escopo de um único banco de dados de usuário. Normalmente, um aplicativo tem dependências nos bancos de dados master e msdb, e também no banco de dados do usuário. Qualquer coisa armazenada fora de um banco de dados de usuário que seja necessária para o funcionamento correto daquele banco de dados deve estar disponível na instância do servidor de destino. Por exemplo, os logons de um aplicativo são armazenados como metadados no banco de dados master e precisam ser recriados no servidor de destino. Se um plano de manutenção de um banco de dados ou aplicativo depende de trabalhos do SQL Server Agent cujos metadados estão armazenados no banco de dados msdb, é necessário recriar esses trabalhos na instância do servidor de destino. De maneira semelhante, os metadados de um gatilho no nível de servidor são armazenados em master.

Ao mover o banco de dados de um aplicativo para outra instância de servidor, é necessário recriar todos os metadados dos objetos e entidades dependentes no master e no msdb na instância do servidor de destino. Por exemplo, se um aplicativo de banco de dados usar gatilhos em nível de servidor, apenas a anexação ou a restauração do banco de dados no novo sistema não será suficiente. O banco de dados não funcionará conforme esperado a não ser que os metadados desses gatilhos sejam recriados manualmente no banco de dados master.

Informações, entidades e objetos que são armazenados fora de bancos de dados de usuário

O restante deste artigo resume os problemas potenciais que podem afetar um banco de dados que está sendo disponibilizado em outra instância de servidor. Talvez você precise recriar um ou mais dos tipos de informações, entidades ou objetos apresentados na lista a seguir. Para ver um resumo, escolha o link do item.

Parâmetros de configuração de servidor

O SQL Server 2005 (9.x) e versões posteriores instalam seletivamente e iniciam serviços e recursos essenciais. Isso ajuda a reduzir a área da superfície de um sistema sujeita a ataque. Na configuração padrão de novas instalações, muitos recursos não estão habilitados. Se o banco de dados depende de qualquer serviço ou recurso que esteja desativado por padrão, esse serviço ou recurso deve ser habilitado na instância do servidor de destino.

Para obter mais informações sobre essas configurações e como habilitá-las ou desabilitá-las, confira Opções de configuração de servidor (SQL Server).

Credenciais

Uma credencial é um registro que contém as informações de autenticação necessárias para se conectar a um recurso fora do SQL Server. A maioria das credenciais consiste em um logon e uma senha do Windows.

Para obter mais informações sobre esse recurso, confira Credenciais (Mecanismo de Banco de Dados).

Observação

Contas proxy do SQL Server Agent usam credenciais. Para saber a identificação da credencial de uma conta proxy, use a tabela do sistema sysproxies .

Consultas de bancos de dados

Por padrão, as opções de banco de dados DB_CHAINING e TRUSTWORTHY estão OFF. Se qualquer uma delas estiver configurada como ON para o banco de dados original, talvez seja necessário habilitá-las no banco de dados na instância do servidor de destino. Para obter mais informações, veja ALTER DATABASE (Transact-SQL).

As operações de anexação e desanexação desabilitam o encadeamento de propriedades de bancos de dados para o banco de dados. Para obter informações sobre como habilitar o encadeamento, veja Opção cross db ownership chaining de configuração de servidor.

Para obter mais informações, confira também Configurar um banco de dados espelho para usar a propriedade confiável (Transact-SQL)

Propriedade de banco de dados

Quando um banco de dados é restaurado em outro computador, o logon do SQL Server ou usuário Windows que iniciou a operação de restauração passa a ser automaticamente o proprietário do novo banco de dados. Quando o banco de dados é restaurado, o administrador de sistema ou o novo proprietário do banco de dados pode alterar a propriedade do banco de dados.

Consultas distribuídas e servidores vinculados

Há suporte para consultas distribuídas e servidores vinculados para aplicativos OLE DB. Consultas distribuídas acessam dados de várias fontes de dados heterogêneos no mesmo ou em diferentes computadores. Uma configuração de servidores vinculados permite que o SQL Server execute comandos em relação a fontes de dados de OLE DB em servidores remotos. Para obter mais informações sobre esses recursos, confira Servidores vinculados (Mecanismo de Banco de Dados).

Dados criptografados

Se o banco de dados que está sendo disponibilizado em outra instância do servidor contiver dados criptografados e se a chave mestra do banco de dados estiver protegida pela chave mestra do serviço no servidor original, talvez seja necessário recriar a criptografia da chave mestra do serviço. A chave mestra do banco de dados é uma chave simétrica usada para proteger as chaves privadas dos certificados e as chaves assimétricas em um banco de dados criptografado. Quando criada, a chave mestra do banco de dados é criptografada com o algoritmo DES Triplo e uma senha fornecida pelo usuário.

Para permitir a descriptografia automática da chave mestra do banco de dados em uma instância do servidor, uma cópia dessa chave é criptografada usando a chave mestra do serviço. Esta cópia criptografada é armazenada no banco de dados e no master. Normalmente, a cópia armazenada no master é silenciosamente atualizada sempre que a chave mestra é alterada. O SQL Server tenta primeiro descriptografar a chave mestra do banco de dados com a chave mestra de serviço da instância. Se essa descriptografia falhar, o SQL Server pesquisará o repositório de credenciais em busca de credenciais de chave mestra que tenham o mesmo GUID de família do banco de dados cuja chave mestra é necessária. O SQL Server, em seguida, tenta descriptografar a chave mestra do banco de dados com cada credencial correspondente até que a descriptografia obtenha êxito ou não haja mais credenciais. Uma chave mestra não criptografada pela chave mestra de serviço deve ser aberta por meio da instrução OPEN MASTER KEY e uma senha.

Quando um banco de dados criptografado é copiado, restaurado ou anexado a uma nova instância do SQL Server, uma cópia da chave mestra do banco de dados criptografada pela chave mestra do serviço não é armazenada no master na instância do servidor de destino. Na instância do servidor de destino, você deve abrir a chave mestra do banco de dados. Para abrir a chave mestra, execute esta instrução: OPEN MASTER KEY DECRYPTION BY PASSWORD ='password'. Recomendamos que, em seguida, a descriptografia automática da chave mestra do banco de dados seja habilitada executando a seguinte instrução: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Essa instrução ALTER MASTER KEY fornece à instância do servidor uma cópia da chave mestra do banco de dados que é criptografada com a chave mestra do serviço. Para obter mais informações, confira OPEN MASTER KEY (Transact-SQL) e ALTER MASTER KEY (Transact-SQL).

Para obter informações sobre como habilitar a descriptografia automática da chave mestra de um banco de dados espelho, veja Configurar um banco de dados espelho criptografado.

Para obter mais informações, consulte também:

Mensagens de erro definidas pelo usuário

Mensagens de erro definidas pelo usuário residem na exibição do catálogo sys.messages . Essa exibição do catálogo é armazenada no master. Se um aplicativo de banco de dados depender de mensagens de erro definidas pelo usuário e o banco de dados for disponibilizado em outra instância do servidor, use sp_addmessage para adicionar essas mensagens definidas pelo usuário à instância do servidor de destino.

Notificações de eventos e eventos WMI (em nível de servidor)

Notificações de eventos em nível de servidor

Notificações de eventos em nível de servidor são armazenadas no msdb. Portanto, se um aplicativo de banco de dados depender de uma notificação de eventos em nível de servidor, essa notificação de evento deverá ser recriada na instância do servidor de destino. Para exibir as notificações de eventos em uma instância do servidor, use a exibição de catálogo sys.server_event_notifications . Para obter mais informações, consulte Event Notifications.

Além disso, notificações de eventos são entregues usando o Service Broker. Roteiros para mensagens de entrada não estão incluídos no banco de dados que contém um serviço. Em vez disso, roteiros explícitos são armazenadas no msdb. Se o serviço usar um roteiro explícito no banco de dados msdb para encaminhar mensagens de entrada para o serviço, quando você anexar um banco de dados em uma instância diferente, será necessário recriar este roteiro.

Eventos da Instrumentação de Gerenciamento do Windows (WMI)

O Provedor WMI para Eventos do Servidor permite o uso da WMI (Instrumentação de Gerenciamento do Windows) para monitorar eventos no SQL Server. Qualquer aplicativo que dependa de eventos em nível de servidor expostos por meio do provedor WMI do qual um banco de dados dependa deve ser definido no computador da instância do servidor de destino. O provedor de eventos WMI cria notificações de eventos com um serviço de destino definido no msdb.

Observação

Para obter mais informações, veja Provedor WMI para conceitos de eventos de servidor.

Para criar um alerta do WMI usando o SQL Server Management Studio

Como notificações de eventos funcionam para um banco de dados espelho

A entrega de notificações de eventos entre bancos de dados envolvendo um banco de dados espelho é remota, por definição, porque o banco de dados espelho pode efetuar failover. O Service Broker fornece suporte especial para bancos de dados espelhados na forma de roteiros espelhados. Uma rota espelhada tem dois endereços: um para a instância do servidor principal e um para a instância do servidor espelho.

Com a configuração de roteiros espelhados, você faz com que os roteiros do Service Broker reconheçam o espelhamento do banco de dados. Os roteiros espelhados permitem que o Service Broker redirecione conversas de forma transparente para a instância de servidor principal atual. Por exemplo, considere um serviço, Service_A que é hospedado por um banco de dados espelho, Database_A. Assuma que você precisa de outro serviço, Service_B, que é hospedado pelo Database_B, para dialogar com o Service_A. Para que esse diálogo seja possível, o Database_B deve conter uma rota espelhada para o Service_A. Além disso, o Database_A deve conter uma rota de transporte TCP não espelhada para o Service_B, que, ao contrário de uma rota local, permaneça válida após um failover. Essas rotas permitem que ACKs sejam retornados após um failover. Como o serviço do remetente é sempre nomeado da mesma maneira, a rota deve especificar a instância do agente.

O requisito de rotas espelhadas se aplica independentemente do fato de o serviço no banco de dados espelho ser o serviço iniciador ou o serviço de destino:

  • O serviço de destino está no banco de dados espelho, o serviço iniciador deve ter uma rota espelhada para retorno ao destino. No entanto, o destino pode ter uma rota normal de retorno ao iniciador.

  • Se o serviço iniciador estiver no banco de dados espelhado, o serviço de destino deverá ter um roteiro espelhado de retorno ao iniciador para entregar confirmações e respostas. No entanto, o iniciador pode ter uma rota normal para o destino.

Procedimentos armazenados estendidos

Importante

Esse recurso será removido em uma versão futura do SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. Em vez disso, use a Integração CLR.

Procedimentos armazenados estendidos são programados por meio da API de Procedimento Armazenado Estendido do SQL Server. Um membro da função de servidor fixa sysadmin pode registrar um procedimento armazenado estendido em uma instância do SQL Server e conceder permissão aos usuários para executar o procedimento. Os procedimentos armazenados estendidos só podem ser adicionados ao banco de dados master.

Os procedimentos armazenados estendidos são executados diretamente no espaço de endereço de uma instância do SQL Server e podem produzir perdas de memória ou outros problemas que reduzem o desempenho e a confiabilidade do servidor. Você deve pensar em armazenar procedimentos armazenados estendidos em uma instância do SQL Server que seja separada da instância que contém os dados referenciados. Você também deve considerar o uso de consultas distribuídas para acessar o banco de dados.

Importante

Antes de adicionar procedimentos armazenados estendidos ao servidor e conceder permissões de EXECUTE a outros usuários, o administrador do sistema deve examinar detalhadamente cada procedimento armazenado estendido para verificar se ele não contém código nocivo ou mal-intencionado.

Para obter mais informações, confira Permissões de objeto GRANT (Transact-SQL), Permissões de objeto DENY (Transact-SQL) e Permissões de objeto REVOKE (Transact-SQL).

Mecanismo de texto completo para propriedades do SQL Server

As propriedades são definidas no Mecanismo de Texto Completo por meio de sp_fulltext_service. Verifique se a instância do servidor de destino tem as configurações necessárias para essas propriedades. Para obter mais informações sobre essas propriedades, confira FULLTEXTSERVICEPROPERTY (Transact-SQL).

Além disso, se o componente de separadores de palavras e lematizadores ou o componente de filtros de pesquisa de texto completo tiver versões diferentes nas instâncias de servidor original e de destino, o índice e as consultas de texto completo poderão se comportar de maneira diferente. O dicionário de sinônimos também é armazenado em arquivos específicos da instância. Você deve transferir uma cópia desses arquivos para um local equivalente na instância do servidor de destino ou recriá-los na nova instância.

Observação

Quando você anexa um banco de dados do SQL Server 2005 (9.x) que contém arquivos de catálogo de texto completo a uma instância de servidor do SQL Server, os arquivos de catálogo são anexados das suas localizações anteriores com os outros arquivos de banco de dados, assim como ocorre no SQL Server 2005 (9.x). Para obter mais informações, veja Atualizar pesquisa de texto completo.

Para obter mais informações, consulte também:

Trabalhos

Se o banco de dados depender de trabalhos do SQL Server Agent, será necessário recriá-los na instância do servidor de destino. Os trabalhos dependem de seus ambientes. Se você planeja recriar um trabalho existente na instância do servidor de destino, a instância do servidor de destino talvez precise ser modificada para corresponder ao ambiente daquele trabalho na instância do servidor original. Os seguintes fatores ambientais são significativos:

  • O logon usado pelo trabalho

    Para criar ou executar trabalhos do SQL Server Agent, é necessário primeiro adicionar todos os logons do SQL Server exigidos pelo trabalho à instância do servidor de destino. Para obter mais informações, veja Configurar um usuário para criar e gerenciar trabalhos do SQL Server Agent.

  • Conta de inicialização do serviço do SQL Server Agent

    A conta de inicialização do serviço define a conta do Microsoft Windows na qual o SQL Server Agent é executado, bem como suas permissões de rede. O SQL Server Agent é executado como uma conta de usuário especificada. O contexto do serviço do Agent afeta as configurações do trabalho e seu ambiente de execução. A conta deve ter acesso aos recursos, como compartilhamentos de rede, exigidos pelo trabalho. Para obter informações sobre como selecionar e modificar a conta de inicialização de serviço, veja Selecionar uma conta para o serviço SQL Server Agent.

    Para operar corretamente, a conta de inicialização do serviço deve ser configurada para ter o domínio, o sistema de arquivos e as permissões do Registro corretos. Além disso, um trabalho pode precisar de um recurso de rede compartilhado que deve ser configurado para a conta de serviço. Para obter informações, veja Configurar contas de serviço e permissões do Windows.

  • O serviço do SQL Server Agent que está associado a uma instância específica do SQL Server tem seu próprio hive do registro e seus trabalhos normalmente são dependentes de uma ou mais configurações neste hive do registro. Para se comportar da maneira pretendida, um trabalho requer essas configurações do Registro. Se você usar um script para recriar um trabalho em outro serviço do SQL Server Agent, seu registro talvez não tenha as configurações corretas para este trabalho. Para que os trabalhos recriados se comportem corretamente em uma instância do servidor de destino, os serviços originais e de destino do SQL Server Agent deverão ter as mesmas configurações do registro.

    Cuidado

    A alteração de configurações do registro no serviço de destino do SQL Server Agent para manipular um trabalho recriado poderá ser problemática se as configurações atuais forem necessárias para outros trabalhos. Além disso, a edição incorreta do Registro pode danificar seriamente o sistema. Antes de fazer alterações no Registro, é recomendável fazer backup dos dados importantes no computador.

  • Proxies do SQL Server Agent

    Um proxy do SQL Server Agent define o contexto de segurança de uma etapa de trabalho especificada. Para que um trabalho seja executado na instância do servidor de destino, todos os proxies requeridos por ele devem ser recriados manualmente naquela instância. Para obter mais informações, veja Criar um Proxy do SQL Server Agent e Solucionar problemas de trabalhos multisservidor que usam proxies.

Para obter mais informações, consulte também:

Para exibir trabalhos existentes e suas propriedades

Para criar um trabalho

Práticas recomendadas para usar um script para recriar um trabalho

É recomendável iniciar gerando o script de um trabalho simples, recriando o trabalho no outro serviço SQL Server Agent e executando o trabalho para ver se ele funciona conforme previsto. Isso permite identificar incompatibilidades e tentar resolvê-las. Se um trabalho com script não funcionar conforme previsto no novo ambiente, é recomendável criar um trabalho equivalente que funcione corretamente neste ambiente.

Logons

O logon em uma instância do SQL Server requer um logon do SQL Server válido. Esse logon é usado no processo de autenticação que verifica se a entidade de segurança pode se conectar à instância do SQL Server. Um usuário do banco de dados para o qual o logon do SQL Server está indefinido ou está definido incorretamente em uma instância do servidor não pode fazer logon na instância. Esse usuário é um usuário órfão do banco de dados nessa instância do servidor. Um usuário do banco de dados pode se tornar órfão após um banco de dados ser restaurado, anexado ou copiado em uma instância diferente do SQL Server.

Para gerar um script para alguns ou todos os objetos na cópia original do banco de dados, é possível usar o Assistente para Gerar Scripts e, na caixa de diálogo Escolher Opções de Script , configurar a opção Logons de Script como True.

Permissões

Os seguintes tipos de permissão podem ser afetados quando um banco de dados é disponibilizado em outra instância do servidor.

  • Permissões GRANT, REVOKE ou DENY em objetos do sistema

  • Permissões GRANT, REVOKE ou DENY em instância de servidor (permissões em nível de servidor)

Permissões GRANT, REVOKE e DENY em objetos do sistema

Permissões para objetos de sistema, como procedimentos armazenados, procedimentos armazenados estendidos, funções e exibições, são armazenadas no banco de dados master e devem ser configuradas na instância do servidor de destino.

Para gerar um script para alguns ou todos os objetos na cópia original do banco de dados, é possível usar o Assistente para Gerar Scripts e, na caixa de diálogo Escolher Opções de Script, configurar a opção Gerar Script de Permissões em Nível de Objeto como True.

Importante

Se você gerar script de logons, as senhas não serão geradas no script. Se você tiver logons que usam a Autenticação do SQL Server, será necessário modificar o script no destino.

Os objetos do sistema são visíveis na exibição de catálogo sys.system_objects . As permissões em objetos do sistema são visíveis na exibição de catálogo sys.database_permissions do banco de dados master. Para obter informações sobre como consultar essas exibições de catálogo e conceder permissões de objeto do sistema, confira Permissões de objeto do sistema GRANT (Transact-SQL). Para obter mais informações, confira Permissões de objeto do sistema REVOKE (Transact-SQL) e Permissões de objeto do sistema DENY (Transact-SQL).

Permissões GRANT, REVOKE e DENY em uma instância de servidor

Permissões no escopo de servidor são armazenados no banco de dados master e devem ser configuradas na instância do servidor de destino. Para obter informações sobre as permissões de servidor de uma instância de servidor, consulte a exibição de catálogo sys.server_permissions , para obter informações sobre entidades de servidor, consulte a exibição de catálogo sys.server_principals, e para obter informações sobre associação de funções de servidor, consulte a exibição de catálogo sys.server_role_members .

Para obter mais informações, confira Permissões de servidor GRANT (Transact-SQL), Permissões de servidor REVOKE (Transact-SQL) e Permissões de servidor DENY (Transact-SQL).

Permissões em nível de servidor para um certificado ou chave assimétrica

As permissões em nível de servidor não podem ser concedidas diretamente a um certificado ou chave assimétrica. Em vez disso, as permissões em nível de servidor são concedidas a um logon mapeado que é criado exclusivamente para um certificado ou chave assimétrica específica. Portanto, cada certificado ou chave assimétrica que exija permissões em nível de servidor, precisa de seu próprio logon mapeado por certificado ou logon mapeado por chave assimétrica. Para conceder permissões em nível de servidor para um certificado ou chave assimétrica, conceda as permissões a seu logon mapeado.

Observação

Um logon mapeado só é usado para autorização de código assinada com o certificado ou chave assimétrica correspondente. Logons mapeados não podem ser usados para autenticação.

O logon mapeado e suas permissões residem no master. Se um certificado ou chave assimétrica residir em outro banco de dados que não seja o master, ele deverá ser recriado no master e mapeado para um logon. Se você mover, copiar ou restaurar o banco de dados para outra instância de servidor, deverá recriar seu certificado ou chave assimétrica no banco de dados master da instância do servidor de destino, mapeá-lo para um logon e conceder as permissões em nível de servidor necessárias ao logon.

Para criar um certificado ou chave assimétrica

Para mapear um certificado ou chave assimétrica para um logon

Para atribuir permissões ao logon mapeado

Para obter mais informações sobre certificados e chaves assimétricas, consulte Encryption Hierarchy.

Propriedade Trustworthy

A propriedade de banco de dados TRUSTWORTHY é usada para indicar se a instância do SQL Server confia no banco de dados e em seu conteúdo. Quando um banco de dados é anexado, por padrão e por segurança, essa opção é definida como OFF, mesmo se essa opção foi definida como ON no servidor original. Para obter mais informações sobre essa propriedade, confira Propriedade de banco de dados TRUSTWORTHY e para obter informações sobre como ativar essa opção, confira ALTER DATABASE (Transact-SQL).

Configurações de replicação

Se você restaurar um backup de um banco de dados replicado para outro servidor ou banco de dados, as configurações da replicação não poderão ser preservadas. Nesse caso, é necessário recriar todas as publicações e assinaturas depois que os backups forem restaurados. Para facilitar esse processo, crie scripts para suas configurações de replicação atuais e também para a habilitação e desabilitação da replicação. Para ajudar a recriar as configurações de replicação, copie esses scripts e altere as referências do nome do servidor para funcionarem para a instância do servidor de destino.

Para obter mais informações, confira Fazer backup e restaurar bancos de dados replicados, Espelhamento e replicação de banco de dados (SQL Server) e Replicação e envio de logs (SQL Server).

Aplicativos do Service Broker

Muitos aspectos de um aplicativo do Service Broker são movidos com o banco de dados. No entanto alguns aspectos do aplicativo devem ser recriados ou reconfigurados no novo local. Por padrão e por segurança, quando um banco de dados é anexo de outro servidor, as opções para is_broker_enabled e is_honor_broker_priority_on são definidas como OFF. Para obter informações sobre como definir essas opções como ON, confira ALTER DATABASE (Transact-SQL).

Procedimentos de inicialização

Um procedimento de inicialização é um procedimento armazenado marcado para execução automática e é executado sempre que o SQL Server é iniciado. Se o banco de dados depender de qualquer procedimento de inicialização, o procedimento deverá ser definido na instância do servidor de destino e ser configurado para ser executado automaticamente na inicialização.

Gatilhos (em nível de servidor)

Os gatilhos DDL acionam procedimentos armazenados em resposta a vários eventos DDL (Linguagem de Definição de Dados). Esses eventos correspondem, principalmente, a instruções Transact-SQL que começam com as palavras-chave CREATE, ALTER e DROP. Determinados procedimentos armazenados do sistema que executam operações do tipo DDL também podem disparar gatilhos DDL.

Para obter mais informações sobre esse recurso, consulte DDL Triggers.

Confira também

Bancos de dados independentes
Copiar bancos de dados para outros servidores
Desanexar e anexar bancos de dados (SQL Server)
Failover para um envio de logs secundário (SQL Server)
Troca de função durante uma sessão de espelhamento de banco de dados (SQL Server)
Configurar um banco de dados espelho criptografado
SQL Server Configuration Manager
Solucionar problemas de usuários órfãos (SQL Server)
Migrar para uma nova instalaçãoVisão geral da migração: SQL Server para SQL Server em VMs do Azure