Gerenciar metadados ao disponibilizar um banco de dados em outra instância do servidor (SQL Server)

Este tópico é relevante nas seguintes situações:

• Configurando as réplicas de disponibilidade de um grupo de Grupos de Disponibilidade Always On.

• Configurando o espelhamento de banco de dados para um banco de dados.

• Ao se preparar para alterar as funções entre servidores primários e secundários em uma configuração de envio de logs.

• Restaurando um banco de dados para outra instância do servidor.

• Anexando uma cópia de um banco de dados em outra instância de servidor.

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 mestre 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 mestre e devem ser recriados no servidor de destino. Se um plano de manutenção de aplicativo ou banco de dados depender de trabalhos do SQL Server Agent, cujos metadados são armazenados no banco de dados msdb , você deverá recriar esses trabalhos na instância do servidor de destino. Da mesma forma, os metadados de um gatilho no nível do servidor são armazenados no mestre.

Ao mover o banco de dados de um aplicativo para outra instância de servidor, você deve recriar todos os metadados das entidades e objetos dependentes no mestre e msdb na instância do servidor de destino. Por exemplo, se um aplicativo de banco de dados usar gatilhos no nível do servidor, apenas anexar ou restaurar o banco de dados no novo sistema não será suficiente. O banco de dados não funcionará conforme o esperado, a menos que você recrie manualmente os metadados para esses gatilhos no banco de dados mestre .

Informações, entidades e objetos armazenados fora dos bancos de dados de usuário

O restante deste tópico resume os possíveis problemas que podem afetar um banco de dados que está sendo disponibilizado em outra instância do servidor. Talvez seja necessário recriar um ou mais tipos de informações, entidades ou objetos listados na lista a seguir. Para ver um resumo, clique no link do item.

• Definições de configuração do servidor

• Credenciais

• Consultas entre bancos de dados

• Propriedade do banco de dados

• Consultas distribuídas/servidores vinculados

• Dados criptografados

• Mensagens de erro definidas pelo usuário

• Notificações de eventos e eventos WMI (Instrumentação de Gerenciamento do Windows) (no nível do servidor)

• Procedimentos armazenados estendidos

• Mecanismo de texto completo para propriedades do SQL Server

• Trabalhos

• Logons

• Permissões

• Configurações de replicação

• Aplicativos do Service Broker

• Procedimentos de inicialização

• Gatilhos (no nível do servidor)

Configurações do servidor

O SQL Server 2005 e versões posteriores instalam e iniciam seletivamente os principais serviços e recursos. Isso ajuda a reduzir a área de superfície que pode ser atacada de um sistema. Na configuração padrão de novas instalações, muitos recursos não estão habilitados. Se o banco de dados depender de qualquer serviço ou recurso desativado por padrão, esse serviço ou recurso deverá 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, consulte As Opções de Configuração do Servidor (SQL Server).

[Topo]

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 senha do Windows.

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

[Topo]

Consultas entre bancos de dados

As opções de banco de dados DB_CHAINING e TRUSTWORTHY são OFF por padrão. Se um deles estiver definido como ON para o banco de dados original, talvez seja necessário habilitá-los no banco de dados na instância do servidor de destino. Para saber mais, confira ALTER DATABASE (Transact-SQL).

As operações de anexação e desanexação desabilitam o encadeamento de propriedade entre bancos de dados para o banco de dados. Para obter informações sobre como habilitar o encadeamento, consulte a opção de configuração do servidor para encadeamento de propriedade entre bancos de dados.

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

[Topo]

Propriedade do banco de dados

Quando um banco de dados é restaurado em outro computador, o logon do SQL Server ou o usuário do Windows que iniciou a operação de restauração se torna o proprietário do novo banco de dados automaticamente. Quando o banco de dados é restaurado, o administrador do sistema ou o novo proprietário do banco de dados podem 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. As consultas distribuídas acessam dados de várias fontes de dados heterogêneas nos mesmos computadores ou em computadores diferentes. Uma configuração de servidor vinculado permite que o SQL Server execute comandos em fontes de dados OLE DB em servidores remotos. Para obter mais informações sobre esses recursos, consulte Servidores Vinculados (Mecanismo de Banco de Dados).

[Topo]

Dados criptografados

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

Para habilitar a descriptografia automática da chave mestra do banco de dados em uma instância de servidor, uma cópia dessa chave é criptografada usando a chave mestra de serviço. Essa cópia criptografada é armazenada no banco de dados e no mestre. Normalmente, a cópia armazenada no mestre é atualizada silenciosamente sempre que a chave mestra é alterada. O SQL Server primeiro tenta 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á no repositório de credenciais as credenciais de chave mestra que têm o mesmo GUID de família que o banco de dados para o qual ele requer a chave mestra. O SQL Server tenta descriptografar a chave mestra do banco de dados com cada credencial correspondente até que a descriptografia seja bem-sucedida ou não haja mais credenciais. Uma chave mestra que não é criptografada pela chave mestra de serviço deve ser aberta usando a 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 de serviço não é armazenada no mestre 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 a seguinte instrução: OPEN MASTER KEY DECRYPTION BY PASSWORD ='password'. Recomendamos habilitar a descriptografia automática da chave mestra do banco de dados executando a seguinte instrução: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Essa instrução ALTER MASTER KEY provisiona a instância do servidor com uma cópia da chave mestra do banco de dados criptografada com a chave mestra do serviço. Para obter mais informações, consulte 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 do banco de dados de um banco de dados espelho, consulte Configurar um Banco de Dados Espelho Criptografado.

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

• Hierarquia de criptografia

• Configurar um banco de dados espelho criptografado

• Criar chaves simétricas idênticas em dois servidores

[Topo]

Mensagens de erro definidas pelo usuário

As mensagens de erro definidas pelo usuário residem na exibição do catálogo sys.messages . Essa exibição de catálogo é armazenada no mestre. 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 na instância do servidor de destino.

[Topo]

Notificações de Eventos e Eventos de Instrumentação de Gerenciamento do Windows (WMI) (no nível do servidor)

Notificações de evento Server-Level

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

Além disso, as notificações de evento são entregues usando o Service Broker. As rotas para mensagens de entrada não estão incluídas no banco de dados que contém um serviço. Em vez disso, as rotas explícitas são armazenadas no msdb. Se o serviço usar uma rota explícita no banco de dados msdb para rotear mensagens de entrada para o serviço, ao anexar um banco de dados em uma instância diferente, você deverá recriar essa rota.

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

O Provedor WMI para Eventos de Servidor permite que você use a WMI (Instrumentação de Gerenciamento do Windows) para monitorar eventos no SQL Server. Qualquer aplicativo que dependa de eventos no nível do servidor expostos por meio do provedor WMI, nos quais um banco de dados se baseia, deve ser configurado no computador da instância do servidor de destino. O provedor de eventos WMI cria notificações de evento com um serviço de destino definido no msdb.

Para criar um alerta WMI usando o SQL Server Management Studio

• Criar um alerta de eventos WMI

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

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

Ao configurar rotas espelhadas, você torna o roteamento do Service Broker ciente do espelhamento de banco de dados. As rotas espelhadas permitem que o Service Broker redirecione conversas de forma transparente para a instância atual do servidor principal. Por exemplo, considere um serviço, Service_A, que é hospedado por um banco de dados espelhado, Database_A. Suponha que você precise de outro serviço, Service_B, que é hospedado por Database_B, para ter uma caixa de diálogo com Service_A. Para que essa caixa de diálogo seja possível, Database_B deve conter uma rota espelhada para Service_A. Além disso, Database_A deve conter uma rota de transporte TCP não espelhada para Service_B, que, ao contrário de uma rota local, permanece válida após o failover. Essas rotas permitem que os ACKs retornem após um failover. Como o serviço do remetente é sempre nomeado da mesma maneira, a rota deve especificar a instância do corretor.

O requisito para rotas espelhadas se aplica independentemente de o serviço no banco de dados espelhado ser o serviço iniciador ou o serviço de destino:

• Se o serviço de destino estiver no banco de dados espelhado, o serviço iniciador deverá ter uma rota espelhada de volta para o destino. No entanto, o destino pode ter uma rota regular de volta para o iniciador.

• Se o serviço de iniciador estiver no banco de dados espelhado, o serviço de destino deverá ter uma rota espelhada de volta para o iniciador para fornecer confirmações e respostas. No entanto, o iniciador pode ter uma rota regular para o destino.

[Topo]

Procedimentos armazenados estendidos

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

Os procedimentos armazenados estendidos são executados diretamente no espaço de endereço de uma instância do SQL Server e podem produzir vazamentos de memória ou outros problemas que reduzem o desempenho e a confiabilidade do servidor. Você deve considerar o armazenamento de procedimentos armazenados estendidos em uma instância do SQL Server 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.

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

[Topo]

Mecanismo de Full-Text para propriedades do SQL Server

As propriedades são definidas no mecanismo de Full-Text por 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, consulte 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 tiverem versões diferentes nas instâncias do servidor original e de destino, o índice de texto completo e as consultas poderão se comportar de forma diferente. Além disso, o dicionário de sinônimos é 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 criá-los novamente em uma nova instância.

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

• Fazer backup e restaurar índices e catálogos de texto completo

• Espelhamento de banco de dados e catálogos de texto completo (SQL Server)

[Topo]

Trabalhos

Se o banco de dados depender de trabalhos do SQL Server Agent, você precisará criá-los novamente 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, talvez a instância do servidor de destino precise ser modificada para corresponder ao ambiente desse trabalho na instância original do servidor. Os seguintes fatores ambientais são significativos:

• O logon usado pelo trabalho

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

• Conta de inicialização de 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 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 do serviço, consulte Selecionar uma conta para o Serviço do SQL Server Agent.

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

• O serviço SQL Server Agent, que está associado a uma instância específica do SQL Server, possui sua própria seção no registro, e seus trabalhos normalmente dependem de uma ou mais configurações nessa seção do Registro. Para se comportar conforme o esperado, 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, o registro poderá não ter as configurações corretas para esse trabalho. Para que os trabalhos recriados se comportem corretamente em uma instância de servidor de destino, os serviços originais e de destino do SQL Server Agent devem ter as mesmas configurações de registro.

  Cuidado

  Alterar as configurações do Registro no serviço sql server agent de destino para lidar com um trabalho recriado poderá ser problemático se as configurações atuais forem exigidas por outros trabalhos. Além disso, a edição incorreta do registro pode danificar severamente seu sistema. Antes de fazer alterações no registro, recomendamos que você faça backup de todos os dados valorizados no computador.

• SQL Server Agent Proxies

  Um proxy do SQL Server Agent define o contexto de segurança para uma etapa de trabalho especificada. Para que um trabalho seja executado na instância do servidor de destino, todos os proxies necessários devem ser recriados manualmente nessa instância. Para obter mais informações, consulte Criar um Proxy do SQL Server Agent e solucionar problemas de trabalhos multisservidor que usam proxies.

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

• Implementar Tarefas

• Gerenciamento de logons e trabalhos após a troca de função (SQL Server) (para espelhamento de banco de dados)

• Configurar contas e permissões de serviço do Windows (ao instalar uma instância do SQL Server)

• Configurar o SQL Server Agent (ao instalar uma instância do SQL Server)

• Implementar segurança do SQL Server Agent

Para exibir trabalhos existentes e suas propriedades

• Monitorar Atividade de Trabalho

• sp_help_job (Transact-SQL)

• Visualizar Informações da Etapa do Trabalho

• dbo.sysjobs (Transact-SQL)

Para criar um trabalho

• Criar um Trabalho

• Criar um Trabalho

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

Recomendamos que você comece criando um script de um trabalho simples, recriando o trabalho no outro serviço do SQL Server Agent e executando o trabalho para ver se ele funciona conforme o esperado. Isso permitirá que você identifique incompatibilidades e tente resolvê-las. Se um trabalho com script não funcionar conforme o esperado em seu novo ambiente, recomendamos que você crie um trabalho equivalente que funcione corretamente nesse ambiente.

[Topo]

Logons

Fazer logon em uma instância do SQL Server requer um logon válido do SQL Server. Esse login é usado no processo de autenticação que verifica se o principal pode se conectar à instância do SQL Server. Um usuário de banco de dados para o qual o logon correspondente do SQL Server é indefinido ou está definido incorretamente em uma instância de 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 de banco de dados poderá ficar órfão se um banco de dados for restaurado, anexado ou copiado para 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, você pode usar o Assistente para Gerar Scripts e, na caixa de diálogo Escolher Opções de Script , defina a opção Logons de Script comoTrue.

[Topo]

Permissões

Os tipos de permissões a seguir podem ser afetados quando um banco de dados é disponibilizado em outra instância do servidor.

• Conceder, revogar ou negar permissões em objetos do sistema

• Conceder, revogar ou negar permissões na instância do servidor (permissões no nível do servidor)

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

Permissões em objetos do sistema, como procedimentos armazenados, procedimentos armazenados estendidos, funções e exibições, são armazenadas no banco de dados mestre 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, você pode usar o Assistente para Gerar Scripts e, na caixa de diálogo Escolher Opções de Script , defina a opção Script Object-Level Permissões como True.

Os objetos do sistema são visíveis na visão de catálogo sys.system_objects. As permissões em objetos do sistema são visíveis na visã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, consulte GRANT System Object Permissions (Transact-SQL). Para obter mais informações, consulte REVOKE System Object Permissions (Transact-SQL) e DENY System Object Permissions (Transact-SQL).

Conceder (GRANT), Revogar (REVOKE) e Negar (DENY) permissões em uma instância de servidor

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

Para obter mais informações, consulte Grant Server Permissions (Transact-SQL), REVOKE Server Permissions (Transact-SQL), and DENY Server Permissions (Transact-SQL).

Permissões Server-Level para um Certificado ou Chave Assimétrica

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

O logon mapeado e suas permissões residem no mestre. Se um certificado ou chave assimétrica residir em um banco de dados diferente do mestre, você deverá criá-lo novamente no mestre e mapeá-lo 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 mestre da instância do servidor de destino, mapear para um logon e conceder as permissões necessárias no nível do servidor para o logon.

Para criar um certificado ou chave assimétrica

• CREATE CERTIFICATE (Transact-SQL)

• CRIAR CHAVE ASSIMÉTRICA (Transact-SQL)

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

• CREATE LOGIN (Transact-SQL)

Para atribuir permissões ao logon mapeado

• Permissões de servidor GRANT (Transact-SQL)

Para obter mais informações sobre certificados e chaves assimétricas, consulte Hierarquia de Criptografia.

[Topo]

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 de replicação não poderão ser preservadas. Nesse caso, você deve 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 habilitar e desabilitar a replicação. Para ajudar a recriar as configurações de replicação, copie esses scripts e ajuste as referências de nome do servidor para serem usadas na instância do servidor de destino.

Para obter mais informações, consulte Backup e Restauração de Bancos de Dados Replicados, Espelhamento e Replicação de Banco de Dados (SQL Server) e Envio e Replicação de Logs (SQL Server).

[Topo]

Aplicativos do Service Broker

Muitos aspectos de um aplicativo do Service Broker se movem com o banco de dados. No entanto, alguns aspectos do aplicativo devem ser recriados ou reconfigurados no novo local.

[Topo]

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, ele deverá ser definido na instância do servidor de destino e ser configurado para ser executado automaticamente na inicialização.

[Topo]

Gatilhos (no nível do servidor)

O DDL dispara procedimentos armazenados de fogo em resposta a uma variedade de 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 de DDL.

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

[Topo]

Consulte Também

Bancos de dados independentes
Copiar bancos de dados para outros servidores
Anexar e desanexar o banco 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)