Partilhar via

FILESTREAM (SQL Server)

Aplica-se a:SQL Server - apenas Windows

O FILESTREAM permite que aplicativos baseados no SQL Server armazenem dados não estruturados, como documentos e imagens, no sistema de arquivos. Os aplicativos podem usar as APIs de streaming avançadas e o desempenho do sistema de arquivos e, ao mesmo tempo, manter a consistência transacional entre os dados não estruturados e os dados estruturados correspondentes.

O FILESTREAM integra o Mecanismo de Banco de Dados do SQL Server com um sistema de arquivos NTFS ou ReFS armazenando dados BLOB (objeto binário grande) varbinary(max) como arquivos no sistema de arquivos. Transact-SQL instruções podem inserir, atualizar, consultar, pesquisar e fazer backup de dados FILESTREAM. As interfaces do sistema de arquivos Win32 fornecem acesso de streaming aos dados.

FILESTREAM usa o cache do sistema NT para armazenar dados de arquivo em cache. O armazenamento em cache de arquivos no cache do sistema ajuda a reduzir qualquer impacto que os dados FILESTREAM possam ter no desempenho do Mecanismo de Banco de Dados. O pool de buffers do SQL Server não é usado; portanto, essa memória está disponível para processamento de consultas.

FILESTREAM não é habilitado automaticamente quando você instala ou atualiza o SQL Server. Você deve habilitar o FILESTREAM usando o SQL Server Configuration Manager e o SQL Server Management Studio. Para usar FILESTREAM, você deve criar ou modificar um banco de dados para conter um tipo especial de grupo de arquivos. Em seguida, crie ou modifique uma tabela para que ela contenha uma coluna varbinary(max) com o atributo FILESTREAM. Depois de concluir essas tarefas, você pode usar o Transact-SQL e o Win32 para gerenciar os dados FILESTREAM.

Quando usar FILESTREAM

No SQL Server, os BLOBs podem ser dados varbinary(max) padrão que armazenam os dados em tabelas ou objetos FILESTREAM varbinary(max) que armazenam os dados no sistema de arquivos. O tamanho e o uso dos dados determinam se você deve usar o armazenamento de banco de dados ou o armazenamento do sistema de arquivos. Se as seguintes condições forem verdadeiras, você deve considerar o uso de FILESTREAM:

  • Os objetos que estão sendo armazenados são, em média, maiores que 1 MB.
  • O acesso rápido à leitura é importante.
  • Você está desenvolvendo aplicativos que usam uma camada intermediária para a lógica do aplicativo.

Para objetos menores, o armazenamento de BLOBs varbinary(max) no banco de dados geralmente fornece um melhor desempenho de streaming.

Armazenamento FILESTREAM

O armazenamento FILESTREAM é implementado como uma coluna varbinary(max) na qual os dados são armazenados como BLOBs no sistema de arquivos. Os tamanhos dos BLOBs são limitados apenas pelo tamanho do volume do sistema de arquivos. A limitação padrão varbinary(max) de tamanhos de arquivo de 2 GB não se aplica a BLOBs armazenados no sistema de arquivos.

Para especificar que uma coluna deve armazenar dados no sistema de arquivos, especifique o atributo FILESTREAM em uma coluna varbinary(max ). Esse atributo faz com que o Mecanismo de Banco de Dados armazene todos os dados dessa coluna no sistema de arquivos, mas não no arquivo de banco de dados.

Os dados FILESTREAM devem ser armazenados em grupos de arquivos FILESTREAM. Um grupo de arquivos FILESTREAM é um grupo de arquivos especial que contém diretórios do sistema de arquivos em vez dos próprios arquivos. Esses diretórios do sistema de arquivos são chamados de contêineres de dados. Os contêineres de dados são a interface entre o armazenamento do Mecanismo de Banco de Dados e o armazenamento do sistema de arquivos.

Ao usar o armazenamento FILESTREAM, considere o seguinte:

  • Quando uma tabela contém uma coluna FILESTREAM, cada linha deve ter um ID de linha exclusivo não nulo.
  • Vários contêineres de dados podem ser adicionados a um grupo de arquivos FILESTREAM.
  • Os contêineres de dados FILESTREAM não podem ser aninhados.
  • Quando você estiver usando clustering de failover, os grupos de arquivos FILESTREAM devem estar em recursos de disco compartilhados.
  • Os grupos de arquivos FILESTREAM podem estar em volumes compactados.

Gestão Integrada

Como FILESTREAM é implementado como uma coluna varbinary(max) e integrado diretamente ao Mecanismo de Banco de Dados, a maioria das ferramentas e funções de gerenciamento do SQL Server funcionam sem modificação para dados FILESTREAM. Por exemplo, você pode usar todos os modelos de backup e recuperação com dados FILESTREAM e o backup dos dados FILESTREAM é feito com os dados estruturados no banco de dados. Se você não quiser fazer backup de dados FILESTREAM com dados relacionais, poderá usar um backup parcial para excluir grupos de arquivos FILESTREAM.

Segurança Integrada

No SQL Server, os dados FILESTREAM são protegidos da mesma forma que outros dados: concedendo permissões nos níveis de tabela ou coluna. Se um usuário tiver permissão para a coluna FILESTREAM em uma tabela, o usuário poderá abrir os arquivos associados.

Observação

A criptografia não é suportada em dados FILESTREAM.

Somente a conta na qual a conta de serviço do SQL Server é executada recebe permissões para o contêiner FILESTREAM. Recomendamos que nenhuma outra conta receba permissões no contêiner de dados.

Observação

Os logins SQL não funcionarão com contêineres FILESTREAM. Somente a autenticação NTFS ou ReFS funcionará com contêineres FILESTREAM.

Acesse dados BLOB com acesso ao streaming de Transact-SQL e sistema de arquivos

Depois de armazenar dados em uma coluna FILESTREAM, você pode acessar os arquivos usando transações Transact-SQL ou usando APIs do Win32.

Acesso Transact-SQL

Usando o Transact-SQL, você pode inserir, atualizar e excluir dados FILESTREAM:

  • Você pode usar uma operação de inserção para preencher previamente um campo FILESTREAM com um valor nulo, valor vazio ou dados embutidos relativamente curtos. No entanto, uma grande quantidade de dados é transmitida de forma mais eficiente para um arquivo que usa interfaces Win32.
  • Ao atualizar um campo FILESTREAM, você modifica os dados BLOB subjacentes no sistema de arquivos. Quando um campo FILESTREAM é definido como NULL, os dados BLOB associados ao campo são excluídos. Não é possível usar uma atualização em partes Transact-SQL, implementada como UPDATE.**Write(), para executar atualizações parciais nos dados.
  • Ao excluir uma linha ou excluir ou truncar uma tabela que contém dados FILESTREAM, você exclui os dados BLOB subjacentes no sistema de arquivos.

Acesso ao streaming do sistema de arquivos

O suporte de streaming do Win32 funciona no contexto de uma transação do SQL Server. Dentro de uma transação, você pode usar funções FILESTREAM para obter um caminho lógico do sistema de arquivos UNC de um arquivo. Em seguida, use a API OpenSqlFilestream para obter um identificador de arquivo. Esse identificador pode ser usado por interfaces de streaming de arquivos Win32, como ReadFile() e WriteFile(), para acessar e atualizar o arquivo por meio do sistema de arquivos.

Como as operações de arquivo são transacionais, não é possível excluir ou renomear arquivos FILESTREAM através do sistema de arquivos.

Advertência

O contêiner FILESTREAM é uma pasta gerenciada pelo SQL Server. Não adicione ou remova ficheiros na pasta FILESTREAM manualmente ou através de outras aplicações. Se você fizer isso, isso resultará em erros de backup e inconsistência. Para obter mais informações, consulte MSSQLSERVER_3056, MSSQLSERVER_7908 e MSSQLSERVER_7906.

Modelo de instrução

O acesso ao sistema de arquivos FILESTREAM modela uma instrução Transact-SQL usando o arquivo open e close. A instrução começa quando um identificador de arquivo é aberto e termina quando o identificador é fechado. Por exemplo, quando uma alça de gravação é fechada, qualquer gatilho possível AFTER registrado na tabela é acionado como se uma UPDATE instrução fosse concluída.

Espaço de nomes de armazenamento

Em FILESTREAM, o Mecanismo de Banco de Dados controla o namespace do sistema de arquivos físico BLOB. Uma nova função intrínseca, PathName, fornece o caminho UNC lógico do BLOB que corresponde a cada célula FILESTREAM na tabela. O aplicativo usa esse caminho lógico para obter o identificador Win32 e operar nos dados BLOB usando interfaces regulares do sistema de arquivos Win32. A função retornará NULL se o valor da coluna FILESTREAM for NULL.

Acesso ao sistema de arquivos transacionado

Uma nova função intrínseca, GET_FILESTREAM_TRANSACTION_CONTEXT, fornece o token que representa a transação atual à qual a sessão está associada. A transação deve ter sido iniciada e ainda não ter sido abortada ou confirmada. Ao obter um token, o aplicativo vincula as operações de streaming do sistema de arquivos FILESTREAM a uma transação iniciada. A função retorna NULL no caso de nenhuma transação iniciada explicitamente.

Todos os identificadores de arquivo devem ser fechados antes que a transação seja confirmada ou anulada. Se um identificador for deixado aberto além do escopo da transação, leituras adicionais no identificador causarão uma falha; gravações adicionais no identificador são bem-sucedidas, mas os dados reais não são gravados no disco. Da mesma forma, se o banco de dados ou instância do Mecanismo de Banco de Dados for desligado, todos os identificadores abertos serão invalidados.

Durabilidade transacional

Com o FILESTREAM, após a confirmação da transação, o Mecanismo de Banco de Dados garante a durabilidade da transação para os dados BLOB FILESTREAM modificados a partir do acesso ao streaming do sistema de arquivos.

Semântica de isolamento

A semântica de isolamento é regida pelos níveis de isolamento de transação do Mecanismo de Banco de Dados. O nível de isolamento confirmado por leitura é suportado para acesso a Transact-SQL e ao sistema de arquivos. Há suporte para operações de leitura repetíveis, serializáveis e níveis de isolamento de instantâneo. A leitura suja não é suportada.

As operações abertas de acesso ao sistema de arquivos não esperam por nenhum bloqueio. Em vez disso, as operações abertas falham imediatamente se não puderem acessar os dados devido ao isolamento da transação. As chamadas de API de streaming falham com ERROR_SHARING_VIOLATION se a operação aberta não puder continuar devido a violação de isolamento.

Para permitir que atualizações parciais sejam feitas, o aplicativo pode emitir um controle FS de dispositivo (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) para buscar o conteúdo antigo no arquivo ao qual o identificador aberto faz referência. Isso aciona uma cópia de conteúdo antiga do lado do servidor. Para um melhor desempenho do aplicativo e para evitar possíveis tempos limite quando você trabalha com arquivos muito grandes, recomendamos o uso de E/S assíncronas.

Se o FSCTL for emitido após o identificador ter sido gravado, a última operação de gravação persistirá e as gravações anteriores que foram feitas no identificador serão perdidas.

APIs do sistema de arquivos e níveis de isolamento suportados

Quando uma API do sistema de arquivos não pode abrir um arquivo devido a uma violação de isolamento, uma exceção ERROR_SHARING_VIOLATION é retornada. Essa violação de isolamento ocorre quando duas transações tentam acessar o mesmo arquivo. O resultado da operação de acesso depende do modo em que o arquivo foi aberto e da versão do SQL Server em que a transação está sendo executada. A tabela a seguir descreve os resultados possíveis para duas transações que estão acessando o mesmo arquivo.

Transação 1 Transação 2 Resultado no SQL Server 2008 (10.0.x) Resultados no SQL Server 2008 R2 (10.50.x) e versões posteriores
Aberto para leitura. Aberto para leitura. Ambos são bem-sucedidos. Ambos são bem-sucedidos.
Aberto para leitura. Aberto para gravação. Ambos são bem-sucedidos. As operações de gravação na transação 2 não afetam as operações de leitura realizadas na transação 1. Ambos são bem-sucedidos. As operações de gravação na transação 2 não afetam as operações de leitura realizadas na transação 1.
Aberto para gravação. Aberto para leitura. Abrir para a transação 2 falha com uma ERROR_SHARING_VIOLATION exceção. Ambos são bem-sucedidos.
Aberto para gravação. Aberto para gravação. Abrir para a transação 2 falha com uma ERROR_SHARING_VIOLATION exceção. Abrir para a transação 2 falha com uma ERROR_SHARING_VIOLATION exceção.
Aberto para leitura. Aberto para SELECT. Ambos são bem-sucedidos. Ambos são bem-sucedidos.
Aberto para leitura. Aberto para UPDATE ou DELETE. Ambos são bem-sucedidos. As operações de gravação na transação 2 não afetam as operações de leitura realizadas na transação 1. Ambos são bem-sucedidos. As operações de gravação na transação 2 não afetam as operações de leitura realizadas na transação 1.
Aberto para gravação. aberto para SELECT. A transação 2 bloqueia até que a transação 1 confirme ou termine a transação, ou o bloqueio da transação expire. Ambos são bem-sucedidos.
Aberto para gravação. Aberto para UPDATE ou DELETE. A transação 2 bloqueia até que a transação 1 confirme ou termine a transação, ou o bloqueio da transação expire. A transação 2 bloqueia até que a transação 1 confirme ou termine a transação, ou o bloqueio da transação expire.
Aberto para SELECT. Aberto para leitura. Ambos são bem-sucedidos. Ambos são bem-sucedidos.
Aberto para SELECT. Aberto para gravação. Ambos são bem-sucedidos. As operações de gravação na transação 2 não afetam a transação 1. Ambos são bem-sucedidos. As operações de gravação na transação 2 não afetam a transação 1.
Aberto para UPDATE ou DELETE. Aberto para leitura. A operação aberta na transação 2 falha com uma ERROR_SHARING_VIOLATION exceção. Ambos são bem-sucedidos.
Aberto para UPDATE ou DELETE. Aberto para gravação. A operação aberta na transação 2 falha com uma ERROR_SHARING_VIOLATION exceção. A operação aberta na transação 2 falha com uma ERROR_SHARING_VIOLATION exceção.
Aberto para SELECT com leitura repetível. Aberto para leitura. Ambos são bem-sucedidos. Ambos são bem-sucedidos.
Aberto para SELECT com leitura repetível. Aberto para gravação. A operação aberta na transação 2 falha com uma ERROR_SHARING_VIOLATION exceção. A operação aberta na transação 2 falha com uma ERROR_SHARING_VIOLATION exceção.

Write-through de clientes remotos

O acesso remoto do sistema de arquivos aos dados FILESTREAM é habilitado pelo protocolo SMB (Server Message Block). Se o cliente for remoto, nenhuma operação de gravação será armazenada em cache pelo lado do cliente. As operações de gravação serão sempre enviadas para o servidor. Os dados podem ser armazenados em cache no lado do servidor. Recomendamos que os aplicativos executados em clientes remotos consolidem pequenas operações de gravação em operações de tamanho maior. O objetivo é realizar menos gravações.

Não há suporte para a criação de exibições mapeadas de memória (E/S mapeadas pela memória) usando um identificador FILESTREAM. Se o mapeamento de memória for usado para dados FILESTREAM, o Mecanismo de Banco de Dados não poderá garantir a consistência e a durabilidade dos dados ou a integridade do banco de dados.

Recomendações e diretrizes para melhorar o desempenho do FILESTREAM

O recurso FILESTREAM do SQL Server permite armazenar dados de objeto binário grande varbinary(max) como arquivos no sistema de arquivos. Quando você tem um grande número de linhas em contêineres FILESTREAM, que são o armazenamento subjacente para colunas FILESTREAM e FileTables, você pode acabar com um volume do sistema de arquivos que contém um grande número de arquivos. Para obter o melhor desempenho ao processar os dados integrados do banco de dados e do sistema de arquivos, é importante garantir que o sistema de arquivos seja ajustado de forma otimizada. A seguir estão algumas das opções de ajuste disponíveis do ponto de vista do sistema de arquivos:

  • Verificação de altitude para o driver de filtro FILESTREAM do SQL Server (por exemplo, rsfx0100.sys). Avalie todos os drivers de filtro carregados para a pilha de armazenamento associada a um volume onde o recurso FILESTREAM armazena arquivos e certifique-se de que o driver rsfx esteja localizado na parte inferior da pilha. Você pode usar o programa de controle FLTMC.EXE para enumerar os drivers de filtro para um volume específico. Aqui está uma saída de exemplo do utilitário FLTMC: C:\Windows\System32>fltMC.exe filtros

    Nome do filtro Instâncias Num Altitude Moldura
    Sftredir 1 406000 0
    MpFilter 9 328000 0
    Luafv 1 135000 0
    FileInfo 9 45000 0
    RsFx0103 1 41001.03 0

  • Verifique se o servidor tem a propriedade "last access time" desabilitada para os arquivos. Este atributo do sistema de arquivos é mantido no registro: Nome da chave: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem Nome: NtfsDisableLastAccessUpdate Tipo: REG_DWORD Valor: 1

  • Verifique se o servidor tem a nomenclatura 8.3 desativada. Este atributo do sistema de arquivos é mantido no registro: Nome da chave: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem Nome: NtfsDisable8dot3NameCreation Tipo: REG_DWORD Valor: 1

  • Verifique se os contêineres de diretório FILESTREAM não têm criptografia do sistema de arquivos ou compactação do sistema de arquivos habilitada, pois eles podem introduzir um nível de sobrecarga ao acessar esses arquivos.

  • Em um prompt de comando elevado, execute instâncias fltmc e certifique-se de que nenhum driver de filtro esteja conectado ao volume onde você tenta restaurar.

  • Verifique se os contêineres de diretório FILESTREAM não têm mais de 300.000 arquivos. Você pode usar as informações da exibição de catálogo para descobrir quais diretórios no sistema de sys.database_files arquivos armazenam FILESTREAM-related arquivos. Isso pode ser evitado com vários contêineres. (Consulte o próximo item para obter mais informações.)

  • Com apenas um grupo de arquivos FILESTREAM, todos os arquivos de dados são criados na mesma pasta. A criação de arquivos de um número muito grande de arquivos pode ser afetada por grandes índices NTFS, que também podem se tornar fragmentados.

    • Ter vários grupos de arquivos geralmente deve ajudar com isso (o aplicativo usa particionamento ou tem várias tabelas, cada uma indo para seu próprio grupo de arquivos).

    • Com o SQL Server 2012 (11.x) e versões posteriores, você pode ter vários contêineres ou arquivos em um grupo de arquivos FILESTREAM e um esquema de alocação round-robin será aplicado. Portanto, o número de arquivos NTFS por diretório fica menor.

  • O backup e a restauração podem se tornar mais rápidos com vários contêineres FILESTREAM, se vários volumes armazenando contêineres forem usados.

    O SQL Server 2012 (11.x) oferece suporte a vários contêineres por grupo de arquivos e pode facilitar as coisas. Nenhum esquema de particionamento complicado pode ser necessário para gerenciar um número maior de arquivos.

  • Quando há um número muito grande de contêineres FILESTREAM em uma instância SQL, iniciar os bancos de dados com muitos contêineres FILESTREAM pode levar muito tempo para registrá-los no driver de filtro FILESTREAM. Espalá-los em vários volumes diferentes ajuda a melhorar o tempo de inicialização do banco de dados.

  • O NTFS MFT pode ficar fragmentado e isso pode causar problemas de desempenho. O tamanho reservado MFT depende do tamanho do volume, portanto, você pode ou não encontrar isso.

    • Você pode verificar a fragmentação MFT com defrag /A /V C: (alterar C: para o nome do volume real).

    • Você pode reservar mais espaço MFT usando o conjunto de comportamento fsutil mftzone 2.

    • Os arquivos de dados FILESTREAM devem ser excluídos da verificação de software antivírus.

      Observação

      O Windows Server 2016 habilita automaticamente o Windows Defender. Verifique se o Windows Defender está configurado para excluir arquivos Filestream. A falha em fazer isso pode resultar em menor desempenho para operações de backup e restauração.

      Para obter mais informações, consulte Configurar e validar exclusões para verificações do Windows Defender Antivírus.

Conteúdo relacionado

  • Last updated on