FILESTREAM (SQL Server)

Aplica-se a: SQL Server (todas as versões com suporte) – somente 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 de BLOB (objeto binário grande) varbinary(max) como arquivos no sistema de arquivos. As instruções do Transact-SQL podem inserir, atualizar, consultar, pesquisar e fazer backup dos dados do FILESTREAM. As interfaces do sistema de arquivos do Win32 fornecem acesso de streaming aos dados.

O FILESTREAM usa o cache do sistema NT para armazenar dados de arquivos. Armazenar arquivos em cache 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 SQL Server não é usado; portanto, essa memória está disponível para processamento de consulta.

FILESTREAM não é habilitado automaticamente quando você instalar ou atualiza o SQL Server. Você deve habilitar o FILESTREAM usando o SQL Server Configuration Manager e o SQL Server Management Studio. Para usar o 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 de modo 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 varbinary(max) FILESTREAM que armazenam os dados no sistema de arquivos. O tamanho e o uso dos dados determinam se você deve usar armazenamento de banco de dados ou armazenamento de sistema de arquivos. Se as condições a seguir forem verdadeiras, você deve considerar o uso de FILESTREAM:

  • Os objetos que estão sendo armazenados têm, em média, mais de 1 MB.
  • O acesso rápido para 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, normalmente, fornece melhor desempenho de streaming.

Armazenamento de FILESTREAM

O armazenamento de 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 varbinary(max) padrão de tamanhos de arquivo de 2 GB não se aplica aos BLOBs armazenados no sistema de arquivos.

Para determinar 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 para essa 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 armazenamento Mecanismo de Banco de Dados e armazenamento de sistema de arquivos.

Ao usar armazenamento de FILESTREAM, considere o seguinte:

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

Gerenciamento integrado

Como FILESTREAM é implementado como uma coluna varbinary(max) e integrado diretamente ao Mecanismo de Banco de Dados, a maioria das funções e ferramentas de gerenciamento do SQL Server funciona sem nenhuma modificação para dados FILESTREAM. Por exemplo, é possível usar todos os modelos de backup e recuperação com dados FILESTREAM e o backup dos dados FILESTREAM pode ser 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 exatamente como outros dados, com a concessão de permissões em níveis de tabela ou coluna. Se um usuário tiver permissão para a coluna FILESTREAM em uma tabela, ele poderá abrir os arquivos associados.

Observação

Não há suporte para criptografia em dados FILESTREAM.

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

Observação

Os logons do SQL não funcionarão com contêineres FILESTREAM. Somente uma autenticação NTFS ou ReFS funcionará com contêineres FILESTREAM.

Acessando dados BLOB com o Transact-SQL e o acesso a streaming do sistema de arquivos

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

Acesso ao Transact-SQL

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

  • Você pode usar uma operação de inserção para pré-popular um campo FILESTREAM com um valor nulo, vazio ou com dados embutidos relativamente curtos. No entanto uma quantidade grande de dados é transmitida de maneira mais eficiente em 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. Você não pode usar uma atualização em partes do Transact-SQL, implementada como UPDATE**.**Write() para executar atualizações parciais para os dados.
  • Ao excluir uma linha ou ao excluir ou truncar uma tabela que contém dados FILESTREAM, você também exclui os dados BLOB subjacentes do sistema de arquivos.

Acesso a streaming do sistema de arquivos

O suporte a streaming do Win32 funciona no contexto de uma transação do SQL Server. Dentro de uma transação, é possível usar funções FILESTREAM para obter um caminho do sistema de arquivos UNC lógico de um arquivo. Em seguida, use a API OpenSqlFilestream para obter um identificador de arquivo. Esse identificador pode então ser usado por interfaces de streaming de arquivo do 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, você não pode excluir ou renomear arquivos FILESTREAM por meio do sistema de arquivos.

Aviso

O contêiner FILESTREAM é uma pasta gerenciada por SQL Server. Não adicione nem remova arquivos na pasta FILESTREAM manualmente ou por meio de outros aplicativos. 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 sistema de arquivos FILESTREAM acessa modelos de uma instrução Transact-SQL usando o arquivo aberto e fechado. A instrução inicia quando um identificador de arquivo é aberto e termina quando o identificador é fechado. Por exemplo, quando um identificador de gravação é fechado, qualquer possível gatilho AFTER registrado na tabela é acionado como se uma instrução UPDATE fosse concluída.

Namespace de armazenamento

No FILESTREAM, o Mecanismo de Banco de Dados controla o namespace do sistema de arquivos físico do 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 do Win32 e operar nos dados BLOB usando interfaces normais de sistema de arquivos do Win32. A função retornará NULL se o valor da coluna FILESTREAM for NULL.

Acesso a 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 anulada ou confirmada. Obtendo um token, o aplicativo associa 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 explicitamente iniciada.

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

Durabilidade transacional

Com FILESTREAM, na confirmação da transação, o Mecanismo de Banco de Dados verifica a durabilidade da transação de dados BLOB FILESTREAM que são modificados no acesso de streaming ao sistema de arquivos.

Semântica de isolamento

A semântica de isolamento é governada pelos níveis de isolamento da transação do Mecanismo de Banco de Dados. Há suporte para o nível de isolamento com confirmação de leitura para acesso ao Transact-SQL e ao sistema de arquivos. Há suporte para operações de leitura repetíveis, níveis de isolamento serializáveis e de instantâneo. Não há suporte para leitura suja.

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

Para permitir a realização de atualizações parciais, o aplicativo pode emitir um controle de 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 acionará uma cópia de conteúdo antigo no lado do servidor. Para obter melhor desempenho do aplicativo e evitar a ocorrência de tempos limites potenciais ao trabalhar com arquivos muito grandes, recomendamos usar E/S assíncrona.

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 feitas no identificador serão perdidas.

APIs do sistema de arquivos e níveis de isolamento com suporte

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 no qual o arquivo foi aberto e da versão do SQL Server na qual a transação está sendo executada. A tabela a seguir descreve os possíveis resultados para duas transações que estão acessando o mesmo arquivo.

Transação 1 Transação 2 Resultado no SQL Server 2008 Resultado no SQL Server 2008 R2 e em versões posteriores
Abrir para leitura. Abrir para leitura. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abrir para leitura. Abrir para gravação. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1.
Abrir para gravação. Abrir para leitura. A abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. Ambas realizadas com êxito.
Abrir para gravação. Abrir para gravação. A abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. A abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION.
Abrir para leitura. Abrir para SELECT. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abrir para leitura. Abrir para UPDATE ou DELETE. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1.
Abrir para gravação. Abrir para SELECT. A transação 2 é bloqueada até a transação 1 confirmar ou finalizar a transação, ou o bloqueio da transação atingir o tempo limite. Ambas realizadas com êxito.
Abrir para gravação. Abrir para UPDATE ou DELETE. A transação 2 é bloqueada até a transação 1 confirmar ou finalizar a transação, ou o bloqueio da transação atingir o tempo limite. A transação 2 é bloqueada até a transação 1 confirmar ou finalizar a transação, ou o bloqueio da transação atingir o tempo limite.
Abrir para SELECT. Abrir para leitura. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abrir para SELECT. Abrir para gravação. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam a transação 1. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam a transação 1.
Abrir para UPDATE ou DELETE. Abrir para leitura. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. Ambas realizadas com êxito.
Abrir para UPDATE ou DELETE. Abrir para gravação. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION.
Abra para SELECT com leitura repetida. Abrir para leitura. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abra para SELECT com leitura repetida. Abrir para gravação. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION.

Gravação de clientes remotos

O acesso do sistema de arquivos remoto a dados FILESTREAM é habilitado pelo protocolo SMB. Se o cliente for remoto, nenhuma operação de gravação será armazenada em cache pelo lado do cliente. Os operações de gravação sempre serão enviados ao servidor. Os dados podem ser armazenados em cache no lado de servidor. Recomendamos que os aplicativos em execução em clientes remotos consolidem pequenas operações de gravação em operações de tamanho maior. A meta é executar menos gravações.

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

Recomendações e diretrizes para aprimorar o desempenho de FILESTREAM

O recurso SQL SERVER FILESTREAM permite que você armazene dados binários grandes 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 esteja ajustado de forma ideal. Apresentamos as seguintes opções de ajuste disponíveis com base na perspectiva do sistema de arquivos:

  • Verifique a altitude do driver de filtro SQL Server FILESTREAM [por exemplo, rsfx0100.sys]. Avalie todos os drivers de filtro carregados para a pilha de armazenamento associada a um volume em que o recurso FILESTREAM armazena arquivos e verifique se o driver rsfx está 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 Quadro
    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 está com a propriedade "horário do último acesso" desabilitada para os arquivos. Esse atributo do sistema de arquivos está mantido no Registro:
    Nome da chave: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Nome: NtfsDisableLastAccessUpdate
    Tipo: REG_DWORD
    Valor: 1

  • Verifique se o servidor está com a nomenclatura 8.3 desabilitada. Esse atributo do sistema de arquivos está 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 do diretório FILESTREAM não têm criptografia do sistema de arquivos ou compactação do sistema de arquivos habilitada, pois isso pode introduzir um nível de sobrecarga ao acessar esses arquivos.

  • Em um prompt de comandos com privilégios elevados, execute instâncias fltmc e verifique se nenhum driver de filtro está conectado ao volume que você está tentando restaurar.

  • Verifique se os contêineres do diretório FILESTREAM não têm mais de 300.000 arquivos. Você pode usar as informações da exibição do catálogo sys.database_files para descobrir quais diretórios no sistema de arquivos armazenam arquivos FILESTREAM-related. Isso pode ser evitado ao ter vários contêineres. (Confira o próximo item do marcador para 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 um grande número de arquivos pode ser afetada por grandes índices NTFS, que também podem ficar fragmentados.

    • O fato de ter vários grupos de arquivos geralmente ajuda (o aplicativo usa particionamento ou tem várias tabelas, cada uma indo para o próprio grupo de arquivos).

    • Com o SQL Server 2012 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 ficará menor.

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

    O SQL Server 2012 dá suporte a vários contêineres por grupo de arquivos e pode facilitar ainda mais as coisas. Nenhum esquema de partição complicado poderá ser necessário para gerenciar um grande número de arquivos.

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

  • O MFT NTFS pode ficar fragmentado e isso pode causar problemas de desempenho. O tamanho reservado do MFT depende do tamanho do volume, de modo que isso pode ser encontrado ou não.

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

    • Você pode reservar mais espaço do MFT usando fsutil behavior set 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. Caso contrário, isso pode resultar na redução do desempenho para operações de backup e de restauração.

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

Habilitar e configurar o FILESTREAM
Criar um banco de dados habilitado para FILESTREAM
Criar uma tabela para armazenar dados FILESTREAM
Acessar dados FILESTREAM com Transact-SQL
Criar aplicativos clientes para dados FILESTREAM
Acessar dados do FILESTREAM com OpenSqlFilestream
Fazer atualizações parciais em dados do FILESTREAM
Evitar conflitos com operações de banco de dados em aplicativos de FILESTREAM
Mover um banco de dados habilitado para FILESTREAM
Configurar FILESTREAM em um cluster de failover
Configurar um firewall para acesso ao FILESTREAM

Compatibilidade do FILESTREAM com outros recursos do SQL Server
Exibições de gerenciamento dinâmico de fluxo de arquivos e FileTable (Transact-SQL)
Exibições de catálogo de fluxo de arquivos e FileTable (Transact-SQL)
Procedimentos armazenados de fluxo de arquivos e FileTable (Transact-SQL)