设为首页 收藏本站
查看: 895|回复: 0

[经验分享] SQL Server的FileStream和FileTable

[复制链接]

尚未签到

发表于 2015-6-27 14:51:31 | 显示全部楼层 |阅读模式
互联网时代数据是爆炸式增长,我们常常需要把结构化数据和非结构化数据(如文档,演示文稿,视频,音频,图像)存储在一起。通常有几种方案:  


1。在数据库中存储结构化数据,在文件系统中存储非结构化数据,然后数据库里有一个字段记录文件系统的路径,虽然这种方法成本合算,但它引入了额外的复杂度,因为你需要手动去保证跨关系和非关系系统管理事务的完整性。  


2。将结构化数据和非结构化数据都存储在数据库中,多年以来,数据库一直都支持存储非关系数据,如二进制大对象,或BLOB,SQL Server称之为varbinary数据类型,这样的好处是能充分利用数据库的特性(如事务支持,备份和恢复支持,集成安全性的支持,全文搜索支持等),但成本费用会更高,所需的磁盘空间更多,因为是存储在同一行数据里,存储和检索时间更长,对应用程序的整体性能也会有负面影响。(如果你写查询时用select * from xxx 就很慢了)  


3。SQL Server 2008中引入的FILESTREAM数据类型来存储非结构化数据,如文档,演示文稿,视频,音频,图像,数据库中存储的是文件系统上的一个指针。在SQL Server 2008中,新的FILESTREAM(文件流)特性是在现有的varbinary(max)数据类型之上实现的,你可以在服务器的文件系统上存储真实的数据,但可以在数据库上下文内管理和访问。自动保证了事务的完整性。  


4。SQL Server 2012的FileTable则进一步增强,它可以让应用程序通过引入FileTable整合其存储和数据管理组件,允许非事务性访问,提供集成的对非结构化数据和元数据的全文搜索和语义搜索 。  


下面详细谈谈这两项新功能。  


了解在SQL Server 2008中的FileStream  


FILESTREAM数据类型作为varbinary(max)列实现的,数据是存储在NTFS文件系统,数据库中存放的是指针。在这种情况下,存储不再是BLOB的2GB大小的限制,只是受制于NTFS文件系统的文件大小。FileStream是默认禁止的,所以你需要对varbinary(max)列指定FILESTREAM属性。这样SQL Server才不会把BLOB存到SQL Server数据库,而是存到NTFS文件系统。

将BLOB数据存储在NTFS文件系统上的带来了一些好处:  


和直接操作NTFS文件系统的数据流的性能一样

FILESTREAM数据没有使用SQL Server缓冲池的,因此SQL Server缓冲池的查询处理,并不会受到FILESTREAM数据的影响。

不再有BLOB的2G大小的限制。

事务的一致性。

SQLServer集成的安全模型。

备份和恢复时,会包含FILESTREAM BLOB数据。

支持全文搜索。

用SELECT,INSERT,UPDATE和DELETE语句,操作带FILESTREAM数据的表,性能没有变慢。

  为了使用这项新功能,我们首先需要在实例级别启用它(在安装过程中,或通过修改SQL Server实例的属性,或通过使用sp_configure来更改实例属性),然后创建或修改数据库,有一个文件组有FileStream属性,然后创建一个表带有varbinary(max)数据类型列,并指定FileStream属性。 具体请参考 http://www.iyunv.com/studyzy/archive/2009/02/23/1396712.html
  



了解SQL Server 2012的FileTable  


FileTable使用FILESTREAM的基础上进一步加强,它既允许直接的,事务性的,存取FILESTREAM列大型数据。FileTable表也可以配置为允许非事务性访问文件,而无需事先SQLServer授权。

FileTable是一种特殊类型的表,它的结构是固定的,不像普通用户表可以定义自己的字段,就好像它是一个文件夹中的文件系统。一个的FileTable包含的FileStream随着几个文件级属性(file_id,名称,路径,创建日期,修改日期,最后访问时间等),文件和目录层次结构的数据。这意味着FileTable中的每一行数据代表一个文件系统上的文件或目录。

FileTable在文件系统表现为一个indows共享目录,里面有文件和目录数据,你可以通过非事务性的文件访问(基于Windows API的应用程序能够访问文件,而无需SQLServer访问权限)。对于Windows应用程序,这看起来像一个正常的网络共享位置的文件和目录。应用程序可以使用Windows API来管理这个网络共享位置的文件和目录。

Windows API操作是非事务性的,和数据库是不相关。然而,FileTable是基于FileStream实现的,所以SQL的事务是支持的。FileTable也可以通过正常的Transact-SQL命令查询和更新。他们还集成了SQL Server管理工具和功能,如备份和恢复。

我们需要分开配置FileTable和FILESTREAM。这意味着,我们可以继续只使用FileStream的功能,而无需启用非事务性访问或创建FileTable。  


开始使用SQL Server 2012的FileTable

启用FileStream

USE master GO
EXEC sp_configure 'filestream access level',   2
Go
RECONFIGURE GO
--You can use this statement to see current
--config value and running value
EXEC sp_configure filestream_access_level;
GO  创建LearnFileTable数据库


USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LearnFileTable')
         DROP DATABASE   LearnFileTable
GO
CREATE DATABASE LearnFileTable
--Details of primary file group
ON PRIMARY
  ( NAME = LearnFileTable_Primary,
      FILENAME =N'D:\FileTable\LearnFileTable_Data.mdf',
      SIZE = 10MB,
      MAXSIZE = 50MB,
      FILEGROWTH =   5MB),
--Details of additional filegroup to be used to store data
FILEGROUP DataGroup
  ( NAME = LearnFileTable_Data,
      FILENAME =N'D:\FileTable\LearnFileTable_Data.ndf',
      SIZE = 10MB,
      MAXSIZE = 50MB,
      FILEGROWTH =   5MB),
--Details of special filegroup to be used to store FILESTREAM   data
FILEGROUP   FSDataGroup CONTAINS   FILESTREAM
  ( NAME = FileStream,
--FILENAME refers to the path and not to the actual file name.   It
--creates a folder which contains a filestream.hdr file and
--also a folder $FSLOG folder as depicted in image below
      FILENAME =N'D:\FileTable\FSData')
--Details of log file
    LOG ON
(Name = LearnFileTable_Log,
       FILENAME = 'D:\FileTable\LearnFileTable_Log.ldf',
      SIZE = 5MB,
      MAXSIZE = 25MB,
      FILEGROWTH =   5MB
)
WITH FILESTREAM (NON_TRANSACTED_ACCESS   = FULL, DIRECTORY_NAME = N'LearnFileTable')
--Other option for NON_TRANSACTED_ACCESS is READ_ONLY or OFF
GO  检查FileStream/FileTable


-- Check the Filestream/FileTable Options
SELECT DB_NAME(database_id), non_transacted_access,   non_transacted_access_desc, directory_name FROM sys.database_filestream_options
WHERE DB_NAME(database_id) = 'LearnFileTable' DSC0000.jpg
  创建FileTable


USE LearnFileTable
GO
CREATE TABLE MyFirstFileTable   AS FileTable
WITH
(
      FileTable_Directory =   'MyFirstFileTable',
      FileTable_Collate_Filename = database_default
);
GO  如果我们创建FileTable前没有启用FileStream,会报错
Msg 1969, Level 16, State 1, Line 1  Default FILESTREAM filegroup is not available in database ''  创建后,我们查询一下,没有记录
USE LearnFileTable   SELECT * FROM [dbo].[MyFirstFileTable] DSC0001.jpg
  在企业管理器选择FileTable,右键 "Explorer FileTable Directory" link as shown below:
DSC0002.jpg
  你会看到网络共享目录. 手动添加几个文件到该目录,我们返回SQLServer企业管理器看看有什么事情发生:
DSC0003.jpg
  再运行一次查询语句
USE LearnFileTable   SELECT * FROM [dbo].[MyFirstFileTable] DSC0004.jpg
  



总结  


在这篇文章中,我谈到了利用SQL Server的FileStream和FileTable功能存储非结构化的数据。 FILESTREAM功能在文件系统中存储​​非结构化数据,并把文件的指针保存在数据库,而FileTable进一步扩展了这一功能允许非事务性访问(访问文件,而无需事先授权,共享位置)。换句话说,有了这个功能,我们可以通过文件系统来管理非结构化数据,而不是在SQL Server管理,却依然可以在SQL Server中的事务访问这些文件。

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-80963-1-1.html 上篇帖子: 如何在SQL Server中使用正则表达式 下篇帖子: Sql Server查询性能优化之创建合理的索引(下篇)
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表