uma actividade comum ao escrever consultas T-SQL está a ligar-se às bases de dados locais e a processar os dados directamente. Mas haverá situações em que você precisa para se conectar a um banco de dados remoto que está localizado em uma instância diferente no mesmo servidor ou em outro servidor físico, e processar seus dados em paralelo com o local de processamento de dados.,
SQL Server fornece-nos quatro métodos úteis para nos ligarmos aos servidores de bases de dados remotos, mesmo outros tipos de servidores de bases de dados, e pesquisar os seus dados dentro da sua declaração T-SQL. Neste artigo, vamos discutir estes quatro métodos e como usá-lo para consultar bancos de dados remotos do servidor SQL.
OPENDATASOURCE
o primeiro método a consultar uma base de dados remota do servidor SQL é a função OPENDATASOURCE T-SQL abaixo:
OPENDATASOURCE ( provider_name as char, init_string)
Onde o provider_name é o fornecedor OLE DB utilizado para aceder à fonte de dados., E o init_ string é o texto de conexão do servidor remoto.
Para ser capaz de usar o OPENDATASOURCE instrução, você precisará certificar-se de que o DisallowAdhocAccess chave do registro é definido como 0 para o provedor que você deseja conectar-se a outros que o SQL Server, que pode ser encontrado no seguinte caminho das Chaves do Registro :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>
você Também precisa habilitar os Consultas Ad Hoc Distribuídas opção de configuração avançada a qual é desativada por padrão no SQL Server., Se você tentar executar o abaixo consulta simples, que é usando o OPENDATASOURCE instrução T-SQL, você receberá o erro:
Msg 15281, Nível de 16, Estado 1, Linha 1
SQL Server bloqueou o acesso a INSTRUÇÃO ‘OpenRowset/OpenDatasource’ do componente ‘Ad Hoc Consultas Distribuídas’ porque esse componente está desativado como parte da configuração de segurança para este servidor. Um administrador de sistema pode activar a utilização de ‘consultas distribuídas ad Hoc’ usando sp_configure., Para mais informações sobre a possibilidade de activar ‘consultas distribuídas Ad Hoc’, procure ‘consultas distribuídas Ad Hoc’ em Linha nos livros do servidor SQL.
Como pode ver na mensagem de erro, a opção de configuração avançada de pesquisa distribuída ad Hoc deverá estar activa para abrir a ligação a um servidor remoto usando o OPENDATASOURCE.,
|
1
2
3
4
5
6
|
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
EXEC sp_configure ‘ad hoc distributed queries’, 1
RECONFIGURE
GO
|
Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE pode substituir o nome do servidor no nome de quatro partes da tabela ou ver em uma declaração de seleção, inserção, atualização ou DELETE. Ele também pode ser usado na instrução EXECUTE para executar um procedimento remoto armazenado.
OPENROWSET
a segunda forma de consultar uma base de dados hospedada num servidor SQL remoto é a função OPENROWSET T-SQL. A fim de usar o método ad hoc OPENROWSET, você precisa fornecer todas as informações de conexão que são necessárias para se conectar ao servidor SQL remoto e muitos outros recursos., Ele também pode ser usado para uma operação em massa através do provedor de massa embutido para ler dados de arquivos. OPENROWSET é usado na cláusula FROM como um nome de tabela nas declarações de seleção, inserção, atualização ou DELETE. Embora a consulta possa retornar vários conjuntos de resultados, OPENROWSET retorna apenas o primeiro.
a utilização do OPENROWSET necessita de activar a opção de configuração avançada de pesquisa distribuída ad Hoc, tal como a função OPENDATASOURCE.,979d8a2e”>
Você pode escrever o anterior OPENDATASOURCE consulta usando a função OPENROWSET da seguinte forma:
o Servidor Vinculado
UM servidor Vinculado do SQL Server é usado para o acesso remoto fontes de dados OLE DB, como uma instância do SQL Server localizado fora do SQL Server ou outros sistemas, tais como Oracle, Microsoft Access e Excel, e executar o distribuído de consultas T-SQL contra eles.,
um servidor ligado a um servidor SQL é diferente das consultas ad hoc, na medida em que as consultas ad hoc abrem uma ligação temporária com o servidor remoto e o fecham, onde o servidor ligado permanente está sempre disponível para uso. Quando o usuário executa uma consulta distribuída contra uma fonte de dados remota usando um servidor ligado, o motor do servidor SQL analisa esse comando e envia os pedidos para OLE DB. Este pedido pode ser uma consulta a executar ou uma tabela a ser aberta nesse servidor remoto.,
um servidor ligado pode ser configurado usando SQL Server Management Studio ou usando a instrução sp_addlinkedserver T-SQL.
A fim de configurar um servidor ligado usando SQL Server Management Studio, expanda o nó de objetos do servidor a partir da janela Object Explorer. Carregue com o botão direito no nó do servidor ligado e escolha o novo servidor ligado.
na página Geral da nova janela do servidor ligado, escolha um nome para o seu servidor ligado, em seguida, escolha o tipo de servidor a que precisa de se ligar usando esse servidor ligado., Seleccione o servidor SQL se conseguir ligar-se a uma instância remota do servidor SQL, ou escolha outra fonte de dados para seleccionar dos tipos de servidores OLE DB disponíveis da lista de fornecedores que não o servidor SQL. Se você escolher o servidor SQL como o tipo de servidor, o nome do servidor ligado deve ser o nome de rede do servidor SQL remoto.
preencha o campo Nome do produto com o nome do produto da fonte de dados OLE DB seleccionada, como o servidor SQL se estiver a ligar-se a um servidor SQL remoto., Digite o nome da fonte de dados selecionada no campo Fonte de dados, como o nome da instância do servidor SQL se você estiver conectando a uma instância remota do servidor SQL. Preencha o campo de cadeia de fornecedores com a cadeia de conexão específica do fornecedor OLE DB que identifica uma fonte de dados única. O campo de localização pode ser preenchido com a localização remota da base de dados como interpretado pelo fornecedor OLE DB. Digite o nome do catálogo a ser usado pelo fornecedor OLE DB no campo catálogo.,
não é obrigado a usar todos os argumentos descritos em conjunto, uma vez que os argumentos necessários dependem do Fornecedor seleccionado. Por exemplo, usando o provedor de servidor SQL, você vai usar apenas dois argumentos.
na página de segurança da nova janela do servidor ligado, indique o contexto de segurança que o servidor ligado irá usar para ligar o servidor SQL original à fonte de dados remota., Como o nome explica, nos logins do servidor Local para logins remotos do servidor mapeamentos parte da janela de segurança, você pode especificar uma lista de usuários que podem usar o servidor ligado para se conectar ao servidor remoto, mapeando esses usuários locais com logins remotos do servidor. Estes usuários podem ser via autenticação SQL Server ou um login de autenticação Windows.
para as logins que não foram definidas na lista de mapeamento, você tem que escolher o contexto de segurança para as suas ligações ao servidor remoto usando esse servidor ligado., A escolha do não ser feito impedirá qualquer usuário não incluído na lista anterior de usar esse servidor ligado. Se seleccionar ser feita sem usar um contexto de segurança, os utilizadores não incluídos na lista de mapeamento anterior ligar-se-ão ao servidor remoto usando esse servidor ligado sem especificar um contexto de segurança para eles. Se você escolher ser feito usando o contexto de segurança atual do login, então a conexão com o servidor remoto será estabelecida usando o usuário de conexão, que é a melhor escolha., No Be made using this security context option, especifique as credenciais de autenticação SQL Server que serão usadas para estabelecer conexão com o servidor remoto para os usuários não definidos na lista de mapeamento.
Existem muitas opções que você pode sintonizar dependendo dos seus requisitos na página de Opções do servidor da nova janela do servidor ligada. Por exemplo, configure a opção compatível com a colação como true se tiver a certeza de que a fonte de dados remota tem o mesmo conjunto de caracteres e ordenar a ordem que o servidor local., Por omissão, o servidor SQL avalia as comparações nas colunas de caracteres localmente se não usar esta opção. A opção de acesso de dados é usada para ativar e desativar o acesso de consulta distribuída para o servidor ligado. Para activar a chamada de procedimento remoto a partir do servidor indicado, defina o RPC como true, e para activar a chamada de procedimento remoto para o servidor indicado, defina o RPC como true.
decidir se a colação do servidor local ou remoto será usada na consulta é determinada pela opção Usar a colação Remota., Se o valor desta opção for verdadeiro, poderá indicar o nome da colação que será usado pelo servidor remoto na opção do nome da colação. Isto é aplicável se a fonte de dados remota não for o servidor SQL, onde poderá indicar qualquer nome de colação suportado pelo servidor SQL nesse campo.
Pode substituir o tempo-limite de autenticação remoto do servidor por omissão para esse servidor ligado, alterando a opção Tempo-Limite de ligação para qualquer valor maior que 0.Este valor especifica o tempo de saída em segundos para a ligação ao servidor ligado., Também poderá indicar o tempo-limite em segundos para as consultas que se ligam ao servidor ligado, alterando a opção Tempo-Limite da consulta para qualquer valor maior que 0. Isto irá sobrepor o tempo-limite da pesquisa remota do servidor ligado.
outra opção útil que é usada para iniciar uma transacção distribuída ao invocar um procedimento de armazenamento remoto, no qual esta transacção será gerida e protegida pelo MS DTC. Esta opção é chamada de permitir a promoção de Transações Distribuídas.,ele abaixo mensagem, caso contrário, uma mensagem de erro será exibida, mostrando que há algo impedindo a conexão que está sendo aberto:
Você pode facilmente criar o antigo servidor vinculado usando o sp_addlinkedserver instrução T-SQL passando os argumentos necessários da seguinte forma:
|
1
2
3
4
|
USE
IR
EXEC mestre.,dbo.sp_addlinkedserver @server = N’DEV_SQL’, @srvproduct=N’SQL Servidor’
IR
|
uma Vez que o servidor vinculado é criado com êxito, você será capaz de usá-lo especificando o nome de quatro partes que inclui: Linked_Server_Name.Remote_Database_Name.Esquema_name.Table_Name como no exemplo abaixo:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Perfil
GO
|
OPENQUERY
o último método do servidor SQL utilizado para se ligar a uma fonte de dados remota é a função OPENQUERY. É um método ad hoc alternativo para se conectar a um servidor remoto usando o servidor conectado. Para conexões mais frequentes ao servidor remoto, é melhor usar o servidor ligado em vez da função OPENQUERY.
a função OPENQUERY pode ser usada na cláusula FROM da instrução SELECT, INSERT, UPDATE ou DELETE substituindo o nome da tabela., É preciso dois argumentos; o nome do servidor vinculado e a consulta. Esses parâmetros não podem ser variável, da seguinte forma:
OPENQUERY ( linked_server ,’consulta’ )
Abaixo está um exemplo simples de OPENQUERY uso:
|
1
|
SELECT * FROM OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Perfil’)
|
a Comparação
Ao usar o Servidor Vinculado a consulta a um servidor remoto, o otimizador de consulta vai criar o plano de execução depois de classificar e dividir a consulta local e remota de consultas, as consultas serão executadas localmente e as consultas remotas serão enviados para o servidor remoto e, em seguida, combinados juntos para mostrar o resultado final para o usuário, como único conjunto de resultados., Outra desvantagem do servidor vinculado é que, nenhuma filtragem será aplicada no servidor remoto se a consulta tem onde cláusula, onde ele irá recuperar todos os registros da tabela remota e fazer a filtragem e a junção localmente.
no caso da função OpenQuery, o motor SQL não irá tentar classificar a consulta ou verificar o que ela fará, simplesmente irá enviar a consulta Como é para o servidor remoto. A processar a consulta SQL, gerando o plano de execução e toda a filtragem será realizada no servidor remoto.,
em geral, o OpenQuery é mais rápido do que o servidor ligado, uma vez que o motor de SQL não irá quebrar a consulta antes de enviá-la para o servidor remoto, mas útil apenas para conexões remotas menos frequentes.
Usando as funções OPENROWSET e OPENDATASOURCE, terá de especificar todos os detalhes da ligação, incluindo o nome de utilizador e a senha de cada vez que a usar. Embora estas funções não fornecem todas as funcionalidades ligadas ao servidor, como a gestão de segurança, consome menos recursos do seu servidor., Como essas funções abrem uma conexão única ao servidor remoto, é melhor usar o servidor conectado para acesso freqüente ao servidor remoto.
- Autor
- Posts Recentes
Também, ele está contribuindo com suas dicas de SQL em muitos blogs.,
Ver todos os posts por Ahmad Yaseen
- Como monitor Azure Dados de Fábrica – em 15 de janeiro de 2021
- Usando o Controle de Origem no Azure Dados de Fábrica de 12 de janeiro, 2021
- Usando Modelos no Azure Dados de Fábrica, 8 de janeiro de 2021