Filtros de linha com parâmetros

 

Publicado: dezembro de 2016

Os filtros de linha com parâmetros permitem que diferentes partições de dados sejam enviadas a diferentes Assinantes sem a necessidade de criar múltiplas publicações (os filtros com parâmetros foram referidos como filtros dinâmicos em versões anteriores do SQL Server). Uma partição é um subconjunto das linhas de uma tabela; dependendo das configurações escolhidas ao criar um filtro de linha com parâmetros, cada linha de uma tabela publicada pode pertencer a uma partição somente (o que produz partições que não se sobrepõem) ou a duas ou mais partições (o que produzem partições que se sobrepõem).

Partições que não se sobrepõem podem ser compartilhadas entre assinaturas ou podem ser restringidas de modo que só uma assinatura receba uma determinada partição. As configurações que controlam comportamento de partição são descritas em "Usando opções de filtragem apropriadas", mais adiante neste tópico. Usando essas configurações você pode personalizar a filtragem com parâmetros de acordo com o aplicativo e os requisitos de desempenho. Em geral, partições que se sobrepõem permitem maior flexibilidade e partições que não se sobrepõem replicadas a uma única assinatura proporcionam melhor desempenho.

Os filtros com parâmetros são usados em uma única tabela e normalmente são combinados com filtros de junção para estender a filtragem a tabelas relacionadas. Para obter mais informações, consulte Join Filters.

Para definir ou modificar um filtro de linha com parâmetros, consulte definir e modificar um filtro de linha com parâmetros para um artigo de mesclagem.

Um filtro de linha com parâmetros usa uma cláusula WHERE para selecionar os dados apropriados a serem publicados. Em vez de especificar um valor literal na cláusula (como faria com um filtro de linha estático), você especifica uma das seguintes funções do sistema ou ambas: SUSER_SNAME() e HOST_NAME(). Funções definidas pelo usuário também podem ser usadas, mas devem incluir SUSER_SNAME() ou HOST_NAME() no corpo da função, ou avaliar uma dessas funções de sistema (como MyUDF(SUSER_SNAME()). Se uma função definida pelo usuário incluir SUSER_SNAME() ou HOST_NAME() no corpo da função, você não pode passar parâmetros para a função.

As funções de sistema SUSER_SNAME() e HOST_NAME() não são específicas para replicação de mesclagem, mas são usadas para por replicação de mesclagem para filtragem com parâmetros:

  • SUSER_SNAME() retorna informações de logon para conexões feitas a uma instância do SQL Server. Quando usado em um filtro com parâmetros, esse retorna o logon utilizado pelo Agente de Mesclagem para fazer a conexão com o Publicador (você pode especificar um logon ao criar uma assinatura).

  • HOST_NAME() retorna o nome do computador que está se conectando a uma instância do SQL Server. Ao usar um filtro com parâmetros, por padrão este retorna o nome do computador no qual o Agente de Mesclagem está sendo executado. Para assinaturas pull, este é o nome do Assinante; para assinaturas push, é o nome do Distribuidor.

    Também é possível substituir essa função com um valor que não seja o nome do Assinante ou do Distribuidor. Normalmente, os aplicativos substituem essa função com valores mais significativos, tais como um nome de vendedor ou uma identificação de vendedor. Para obter mais informações, consulte a seção "Substituindo o valor de HOST_NAME()" neste tópico.

O valor retornado pela função do sistema é comparado a uma coluna que você especifica na tabela que está filtrando, e os dados apropriados são baixados para o Assinante. Essa comparação é feita quando a assinatura é inicializada (de modo que apenas os dados apropriados estejam contidos no instantâneo inicial) e a cada vez que a assinatura é sincronizada. Por padrão, se uma alteração no publicador resulta em uma linha que está sendo movida de uma partição, a linha é excluída no assinante (esse comportamento é controlado usando o @allow_partition_realignment parâmetro sp_addmergepublication & #40. Transact-SQL & 41;).

System_CAPS_ICON_note.jpg Observação


Quando são feitas comparações para filtros com parâmetros, o agrupamento de banco de dados sempre é usado. Por exemplo, se o agrupamento de banco de dados não diferencia maiúsculas e minúsculas, mas o agrupamento de tabela ou coluna o faz, a comparação não diferenciará maiúsculas e minúsculas.

Filtrando com SUSER_SNAME()

Considere o tabela Employee no Adventure Works banco de dados de exemplo. Esta tabela inclui a coluna LoginID, que contém o logon para cada funcionário no formulário 'domain\login'. Para filtrar essa tabela de modo que os funcionários recebam só os dados relacionados a eles, especifique uma cláusula de filtro de:

LoginID = SUSER_SNAME()  

Por exemplo, o valor para um dos funcionários é 'adventure-works\john5'. Quando o Agente de Mesclagem faz a conexão com o Publicador, ele utiliza o logon especificado na criação da assinatura (neste caso, 'adventure-works\john5'). O agente de mesclagem então compara o valor retornado por suser_sname () com os valores na tabela e baixa apenas a linha que contém um valor de 'Adventure-works\john5' no LoginID coluna.

Filtrando com HOST_NAME()

Considere o HumanResources. Employee tabela. Suponha que essa tabela contenha uma coluna como ComputerName com o nome do computador de cada funcionário no formulário 'name_computertype'. Para filtrar essa tabela de modo que os funcionários recebam só os dados relacionados a eles, especifique uma cláusula de filtro de:

ComputerName = HOST_NAME()  

Por exemplo, o valor para um dos funcionários poderia ser 'john5_laptop'. Quando o agente de mesclagem se conecta ao publicador, ele compara o valor retornado por HOST_NAME () com os valores na tabela e baixa apenas a linha que contém um valor de 'john5_laptop' no ComputerName coluna.

Também é possível combinar as funções em um filtro. Por exemplo, se você quisesse assegurar-se de que um funcionário recebeu os dados apenas se usou seu logon em seu computador, a cláusula do filtro poderia ser:

LoginID = SUSER_SNAME() AND ComputerName = HOST_NAME()  

A menos que você esteja substituindo o valor HOST_NAME(), a filtragem com HOST_NAME() normalmente é usada somente com assinaturas pull. O valor retornado pela função é o nome do computador no qual o Agente de Mesclagem está sendo executado. Para assinaturas pull, o valor é diferente para cada assinatura; para assinaturas push, porém, o valor é o mesmo (todos os Agente de Mesclagems são executados no Distribuidor para assinaturas push).

System_CAPS_ICON_important.jpg Importante


O valor para a função HOST_NAME() pode ser substituído; portanto não é possível usar filtros que incluam HOST_NAME() para controlar o acesso a partições de dados. Para controlar o acesso a partições de dados, use SUSER_SNAME(), SUSER_SNAME() em combinação com HOST_NAME(), ou use filtros de linha estáticos.

Substituindo o valor HOST_NAME()

Como observado anteriormente, HOST_NAME() por padrão retorna o nome do computador que está se conectando a uma instância do SQL Server. Ao usar filtros com parâmetros, é comum substituir esse valor fornecendo um valor ao criar uma assinatura. Uma função HOST_NAME() retorna o valor que você especifica em lugar do nome do computador.

System_CAPS_ICON_note.jpg Observação


Se substituir HOST_NAME(), todas as chamadas para a função HOST_NAME() retornarão o valor que você especificou. Assegure-se de que outros aplicativos não estão dependendo de que HOST_NAME() retorne o nome do computador.

Considere o HumanResources. Employee tabela. Esta tabela inclui a coluna EmployeeID. Para filtrar essa tabela de modo que cada funcionário receba só os dados relacionados a ele, especifique uma cláusula de filtro de:

EmployeeID = CONVERT(int,HOST_NAME())

Por exemplo, a funcionária Pamela Ansman-Wolfe recebeu uma identificação de funcionário 280. Especifique o valor da identificação de funcionário (280 em nosso exemplo) para o valor HOST_NAME() ao criar uma assinatura para essa funcionária. Quando o agente de mesclagem se conecta ao publicador, ele compara o valor retornado por HOST_NAME () com os valores na tabela e baixa apenas a linha que contém um valor 280 no EmployeeID coluna.

System_CAPS_ICON_important.jpg Importante


A função HOST_NAME () retorna um nchar valor, você deve usar CONVERT se a coluna na cláusula de filtro é de um tipo de dados numéricos, como no exemplo acima. Por razões de desempenho, recomendamos não aplicar funções a nomes de colunas em cláusulas de filtro de linha com parâmetros, tais como CONVERT(nchar,EmployeeID) = HOST_NAME(). Em vez disso, recomendamos usar a abordagem mostrada no exemplo: EmployeeID = CONVERT(int,HOST_NAME()). Essa cláusula pode ser usada para o @subset_filterclause parâmetro do sp_addmergearticle, mas normalmente não pode ser usado no novo Assistente de publicação (o assistente executa a cláusula de filtro para validá-la, que falha porque o nome do computador não pode ser convertido em um int). Se você usar o Assistente para nova publicação, é recomendável especificar CONVERT(nchar,EmployeeID) = HOST_NAME() no assistente e, em seguida, usar sp_changemergearticle para alterar a cláusula EmployeeID = CONVERT(int,HOST_NAME()) antes de criar um instantâneo da publicação.

Para substituir o valor HOST_NAME()

Use um dos métodos seguintes para substituir o valor HOST_NAME():

Quando são usados filtros de linha com parâmetros em publicações de mesclagem, a replicação inicializa cada assinatura com um instantâneo de duas partes. Para obter mais informações, consulte Snapshots for Merge Publications with Parameterized Filters.

Há duas áreas-chaves sobre as quais você tem controle ao usar filtros com parâmetros:

  • Como os filtros são processados pela replicação de mesclagem, que é controlada por uma das duas configurações de publicação: usar grupos de partição e Manter alterações de partição.

  • Como os dados são compartilhados entre assinantes, que devem ser refletidos pela configuração de artigo Opções de partição.

Para definir opções de filtragem, consulte otimizar filtros de linha com parâmetros.

Definindo 'usar grupos de partição’ e 'manter alterações de partição’

Os dois o usar grupos de partição e Manter alterações de partição opções melhoram o desempenho de sincronização para publicações com artigos filtrados, armazenando metadados adicionais no banco de dados de publicação. O usar grupos de partição opção fornece maior melhoria de desempenho com o uso do recurso de partições pré-calculadas. Essa opção é definida como true por padrão, se os artigos da publicação de acordo com um conjunto de requisitos. Para obter mais informações sobre esses requisitos, consulte otimizar desempenho de filtro parametrizado com partições pré-calculadas. Se os artigos não atendem aos requisitos para usar partições pré-computadas, o Manter alterações de partição opção a é definida como true.

Definindo 'opções de partição’

Especifique um valor para o Opções de partição propriedade durante a criação de um artigo, de acordo com a maneira na qual os dados na tabela filtrada serão compartilhados pelos assinantes. A propriedade pode ser definida como um dos quatro valores usando sp_addmergearticle, sp_changemergearticle, e o Propriedades do artigo caixa de diálogo. A propriedade pode ser definida para um dos dois valores usando o Adicionar filtro ou Editar filtro caixas de diálogo, que estão disponíveis no Assistente de nova publicação e o Propriedades de publicação caixa de diálogo. A tabela a seguir resume os valores disponíveis:

DescriçãoValor em Adicionar filtro e Editar FiltroValor em Propriedades do ArtigoValor em procedimentos armazenados
Os dados nas partições estão sobrepostos e o Assinante pode atualizar colunas referenciadas em um filtro com parâmetros.Uma linha dessa tabela irá para múltiplas assinaturasCom sobreposição0
Os dados nas partições estão sobrepostos e o Assinante não pode atualizar colunas referenciadas em um filtro com parâmetros.N/A*Com sobreposição, não permitir alterações de dados fora da partição1
Os dados nas partições não estão sobrepostos e os dados são compartilhados entre assinaturas. O Assinante não pode atualizar colunas referenciadas em um filtro com parâmetros.N/A*Sem-sobreposição, compartilhados entre assinaturas2
Os dados nas partições não estão sobrepostos e há uma única assinatura por partição. O Assinante não pode atualizar as colunas referenciadas em um filtro parametrizado.**Uma linha dessa tabela irá para apenas uma assinaturaSem-sobreposição, única assinatura3

*Se a opção de filtragem subjacente for definida como 0, ou 1, ou 2, o Adicionar filtro e Editar filtro caixas de diálogo exibirá uma linha dessa tabela irá para múltiplas assinaturas.

**Se você especificar esta opção, somente poderá haver uma única assinatura para cada partição de dados nesse artigo. Se uma segunda assinatura for criada na qual o critério de filtragem da nova assinatura for resolvido para a mesma partição como a assinatura existente, a assinatura existente será cancelada.

System_CAPS_ICON_important.jpg Importante


O Opções de partição valor deve ser definido de acordo com como os dados são compartilhados pelos assinantes. Se, por exemplo, você especificar que uma partição é sem-sobreposição com uma assinatura única por partição, mas os dados forem então atualizados em outro Assinante, o Agente de Mesclagem poderá não funcionar durante a sincronização e poderá ocorrer uma não convergência.

Selecionando a opção de partição apropriada

Partições que não se sobrepõem funcionam em conjunção com partições pré-computadas para melhorar o desempenho em situações em que algumas limitações funcionais sejam aceitáveis. As partições pré-computadas aceleram os downloads para Assinantes, mas retardam os carregamentos. Partições que não se sobrepõem minimizam o custo de carregamento associado a partições pré-computadas. O benefício de desempenho de partições que não se sobrepõem é mais notável quando os filtros com parâmetros e de junção usados são mais complexos.

Considere os cenários seguintes ao decidir que opções de partição usar em uma publicação.

  • Adventure Works tem uma força de vendas móvel com cada vendedor responsável por clientes em um determinado código postal. O aplicativo requer que o código postal seja atualizado se um cliente se muda de um território de vendas para outro, de maneira o cliente que seja atribuído a um vendedor diferente. O filtro com parâmetros tem como base o código postal do cliente e a atualização remove o código postal da partição de um vendedor e insere-o na partição de outro vendedor. Isso requer partições que se sobreponham e que possam atualizar colunas referenciadas em um filtro com parâmetros. Essa opção maximiza a flexibilidade, mas poderia não ter desempenho tão bom quanto partições que não se sobrepõem.

  • Uma agência de empregos tem dados que são fornecidos a escritórios regionais em cada município do estado. Os dados não se sobrepõem; cada linha na tabela para a matriz do órgão está incluída em apenas uma partição, mas esta partição é enviada a escritórios múltiplos no mesmo município. A opção de partição que não se sobrepõe, com partições compartilhadas entre assinaturas, é apropriada, proporcionando uma melhoria de desempenho, se comparada com a partições que se sobrepõem, e ao mesmo tempo satisfazem os requisitos do aplicativo.

  • Se você tiver partições que não se sobrepõem e apenas uma assinatura recebe e atualiza os dados em uma partição, benefícios de desempenho adicionais podem ser obtidos. Esse cenário é comum para sistemas de pontos de vendas e aplicativos de força de vendas em que os dados são basicamente coletados no Assinante e carregados no Publicador. Considere um pacote tabela em um aplicativo de entrega: como cada pacote é carregado em um caminhão, o status do pacote é alterado no pacote tabela e a alteração é replicada de volta para a matriz. Drivers não atualizaria o status do mesmo pacote em dois caminhões diferentes, então o pacote tabela é um bom candidato para uma partição que não se sobrepõem com uma única assinatura por partição.

Considerações para partições que não se sobrepõem

Lembre-se das seguintes considerações ao usar partições que não se sobrepõem.

Considerações gerais
  • A publicação deve usar partições pré-computadas.

  • Uma linha deve pertencer a uma só partição.

  • Artigos não podem fazer parte de um registro lógico.

  • Não há suporte para parceiros de sincronização alternativos (esse recurso é preterido).

  • O Assinante não pode atualizar colunas referenciadas em um filtro com parâmetros.

  • Se uma inserção em um Assinante não pertencer à partição, não é excluída. Ela, porém, não será replicada a outros Assinantes.

  • Em algumas circunstâncias com partições que se sobrepõem, os intervalos de identidade são ajustados quando o Agente de Mesclagem insere dados. Com partições que não se sobrepõem, os intervalos podem ser ajustados durante as inserções por um usuário que tenha permissão para ajustar intervalos de identidade no banco de dados de assinatura. O usuário deve possuir uma tabela ou ser um membro do sysadmin função de servidor fixa, o db_owner função de banco de dados fixa ou db_ddladmin função de banco de dados fixa.

Considerações adicionais para partições que não se sobrepõem com uma única assinatura por partição
Considerações adicionais para filtros de junção
  • Em uma hierarquia de filtros de junção, um artigo com uma partição que se sobrepõe não pode aparecer acima de um artigo com uma partição que não se sobrepõe. Em outras palavras, um artigo pai deve usar partições que não se sobrepõem se o artigo de filho as usar também. Para obter informações sobre filtros de junção, consulte filtros de junção.

  • Um filtro de junção na qual a partição que não se sobrepõem é um filho deve ter o chave exclusiva propriedade definida como 1. Para obter mais informações, consulte Join Filters.

  • O artigo deve ter só um filtro com parâmetros ou filtro de junção. É permitido ter um filtro com parâmetros e ser o pai em um filtro de junção. Não é permitido ter um filtro com parâmetros e ser o filho em um filtro de junção. Também não é permitido ter mais que um filtro de junção.

  • Se duas tabelas do Publicador tiverem uma relação de filtro de junção e a tabela filho tiver linhas que não tenham nenhuma linha correspondente na tabela pai, uma inserção da linha pai faltante não fará com que as linhas relacionadas sejam baixadas para o Assinante (as linhas seriam baixadas com partições que se sobrepusessem). Por exemplo, se o SalesOrderDetail tabela tiver linhas sem nenhuma linha correspondente no SalesOrderHeader tabela e você insere a linha ausente no SalesOrderHeader, a linha é baixada no assinante, mas as linhas correspondentes em SalesOrderDetail não são.

Práticas recomendadas para filtros de linha baseados em tempo
Filtrar dados publicados
Filtrar dados publicados para replicação de mesclagem

Contribuições da comunidade

ADICIONAR
Mostrar: