一切福田,不離方寸,從心而覓,感無不通。

Category Archives: SQLServer

MS Sql Server数据库分区

对于Sql Server数据库分区问题相信很多人还是对其不时很了解,具体Sql Server数据库分区是怎么个情况?下文将为大家解答。 什么是数据库分区? 数据库分区是一种对表的横向分割,Sql server 2005企业版和之后的Sql server版本才提供这种技术,这种对表的横向分割不同于2000中的表分割,它对访问用户是透明的,用户并不会感觉的表被横向分割了。(2000中的表横向分割是建n个表例如按时间建表每月一个表,表名不同,最后需要做一个大视图) 为什么要分区? 显而易见分区是为了提高数据库的读写性能,提高数据库的效率; 分区是否总是可以提高效率? 分区是一把双刃剑,并不总能提高效率,这和具体情况有关系。 之所以有分区技术,分区技术用的好的话可以提高性能,是因为一方面分区把一大块数据分成了n小块,这样查询的时候很快定位到某一小块上,在小块中寻址要快很多;另一方面CPU比磁盘IO快很多倍,而硬件上又有多个磁盘,或者是RAID(廉价磁盘冗余阵列),可以让数据库驱动CPU同时去读写不同的磁盘,这样才有可能可以提高效率。 分区在有些时候并不能提高读写效率,比如说我们经常看到的按照日期字段去分区MSDN例子,这个实例中是按照记录的生成时间来分区的,把一年的数据分割成12个分区,每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能,因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是:这样分区是否可以提高读取性能呢?答案是不一定,要看根据什么字段来查询,如果是根据时间来查询,根据时间生成报表那么这种分区肯定会提高查询的效率,但是如果是按照某个客户查询客户最近1年内的账单数据,这样数据分布到不同的分区上,这样的话效率就不一定能提高了,这要看数据在同一个分区上连续分布的读性能高,还是CPU从几个磁盘上同步读取,然后在合并数据的性能更高一些,这和读取数据的记录数也有关系。 如何分区?用什么字段做分区依据? 具体如何分区和涉及的业务有关系,要看业务上最经常的写入和读取操作是什么,然后再考虑分区的策略。 既然与具体业务相关,我们就假定一个业务环境,假如我们要做一个论坛,对论坛的帖子和回复表进行分区。 论坛中最常见的写操作是1)发帖 2)回复帖子, 最常见的读操作是 1) 根据帖子id显示帖子详情和分页的帖子回复 2) 根据帖子版面帖子列表页根据版面id分页读取帖子列表数据 怎么分区更合适呢? 现在还没有准确答案,我有两种可能的方案,写下来,大家讨论看看。 方案1. 根据帖子ID区域段分区(1-300w一个分区、300w-600w一个分区…),这样理论上可以提高帖子详细页的读取速度,而对于写操作性能没有益处,对于根据版面id读取帖子列表页有可能有益 方案2. 根据版面id进行分区,这样对于写性能应该有提高,不同的分区对应不同的版面,当有两个版面同时有发帖回帖操作时,有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能,而对于帖子详细信息页没有性能影响。 多大的数据量才需要分区? 这个问题我只能说一个内部标准,如果一张表的记录超过在超过1000w,并以每月百万的数据量增长,那就需要分区。 上文从这几个方面解析了Sql Server数据库分区问题,现在对数据库分区大家应该都有一个大体的了解,这里介绍的只是Sql Server数据库分区的基本知识,对刚刚入门的初学者来说是很好的学习资料,希望能够帮到大家。

龙生   23 Mar 2011
View Details

教你如何在SQL Server数据库中加密数据

导读:为了防止某些别有用心的人从外部访问SQL Server数据库,盗取SQL Server数据库中的用户姓名、密码、信用卡号等其他重要信息,在我们创建SQL Server数据库驱动的解决方案时,我们首先需要考虑的的第一条设计决策就是如何加密存储SQL Server数据,以此来保证它的安全,免受被他人窥测。 SQL Server数据库中有哪一种支持可以用于加密对象和数据?从一开始就讨论一下SQL Server数据库欠缺什么是明智的,或者是对于SQL Server数据库中的加密部分你不应该做什么。 首先,SQL Server数据库有两个内置的密码函数——即,pwdencrypt() 和 pwdcompare()。同时,还有两个SQL Server数据库用来管理密码哈希的没有正式记录的函数:pwdencrypt() 将密码哈希过后进行存储; pwdcompare()将提供的字符串与哈希后的字符串进行比较。不幸的是,这个哈希函数不是非常安全,它可以通过字典攻击算法被破解(类似命令行应用程序!)。 这些函数随着SQL Server的版本发展而不断进行修改,这也是另一个没有使用它们的原因。早期版本的SQL Server对密码进行的哈希,在后来的版本中无法解密,所以如果你依赖一个版本中的函数,那么当升级的时候,所有你的加密数据就都没有用了,除非你可以首先对其解密——这也就违背了加密的最初的目的。 第二,你可能会尝试去创建一个针对你的数据库的自制的加密解决方案,但是有以下三个理由说明你不要这样做: 除非你是加密专家,否则胡乱编写的加密系统只会提供非常低级的价值不高的保护。新鲜的是,单向密码哈希或者 "ROTx "形式的加密几乎不需要费事就可以被轻松打败。 如果由于你自己的能力的缺乏而导致加密被破解,那么你的数据就完蛋了。你需要将所有的东西进行没有加密的备份,是吗?(即使你加密了,那里有没有安全漏洞?) 当市面上提供有专业级别的,具有工业强度的加密解决方案的时候,你就不值得花费时间去自己做。把你的时间用于构建一个好的,坚固的数据库,而不是再重新发明一次车轮。 那么,什么才是好的加密数据的方式呢? 对于新手,微软提供了一个自己生成的加密解决方案,CryptoAPI 。对于轻量级的加密,军用级别的安全就不在考虑范围之内,它具有相对容易实现的优势:管理员可以安装一个名为CAPICOM 的ActiveX 控制,它可以在T-SQL存储过程中提供CryptoAPI 功能。CAPICOM 支持各种类型的双向加密和单向哈希算法,所以管理员可以挑选最适合应用程序的问题的部分。 如果你对使用微软的解决方案不感兴趣,还有一些很好的第三方的方案可以使用。一家名为ActiveCrypt 的软件有限责任公司制造了XP_CRYPT ,它是SQL Server的插件,可以在视图、程序和触发器中通过扩展存储过程和用户自定义函数(在SQL Server 2000中)来完成加密。你可以下载一个支持无线的MD5,DES ,以及SHA1哈希的免费版本的应用程序; 其他的加密模型就是在比特深度上进行的。(完全版本是无限的。)在你自己的代码中,你可以使用XP_CRYPT,与ActiveX 控制一样(在受限的免费版本中)。对于ASP程序员来说,一个名为AspEncrypt 的组件提供了一种将高级加密整合到你的代码中的简单方式。 对数据库文件自身进行加密或者提供传输层上的安全保护怎么样?对于前者,大家可以在Windows系统中持续使用加密文件系统。然而,你必须保存加密密钥的备份,在出现问题的时候,这个数据有可能会丢失。对于后者,有IPSec和SQL Server自己的SSL加密,都是SQL Server和Windows自带的大家的主要精力应该放在避免以明文存储敏感数据,因为从数据库中抽取没有加密的数据同样是最容易受到攻击的薄弱环节。 SQL Server数据库的安全防护工作时很重要的,希望大家能从上文中学到保障SQL Server数据库安全的方法,做好SQL Server数据库的安全工作,确保SQL Server数据库中数据库信息的绝对安全。

龙生   23 Mar 2011
View Details

从外到内提高SQL Server数据库性能

如何提高SQL Server数据库的性能,该从哪里入手呢?笔者认为,该遵循从外到内的顺序,来改善数据库的运行性能。如下图:   第一层:网络环境 到企业碰到数据库反映速度比较慢时,首先想到的是是否是网络环境所造成的。而不是一开始就想着如何去提高数据库的性能。这是很多数据库管理员的一个误区。因为当网络环境比较恶劣时,你就算再怎么去改善数据库性能,也是枉然。 如以前有个客户,向笔者反映数据库响应时间比较长,让笔者给他们一个提高数据库性能的解决方案。那时,笔者感到很奇怪。因为据笔者所知,这家客户数据库的记录量并不是很大。而且,他们配置的数据库服务器硬件很不错。笔者为此还特意跑到他们企业去查看问题的原因。一看原来是网络环境所造成的。这家企业的客户机有200多台,而且都是利用集线器进行连接。这就导致企业内部网络广播泛滥,网络拥塞。而且由于没有部署企业级的杀毒软件,网络内部客户机存在病毒,掠夺了一定的带宽。不仅数据库系统响应速度比较慢,而且其他应用软件,如邮箱系统,速度也不理想。 在这种情况下,即使再花十倍、百倍力气去提升SQL Server数据库的性能,也是竹篮子打水一场空。因为现在数据库服务器的性能瓶颈根本不在于数据库本身,而在于企业的网络环境。若网络环境没有得到有效改善,则SQL Server数据库性能是提高不上去的。 为此,笔者建议这家企业,想跟他们的网络管理员谈谈,看看如何改善企业的网络环境,减少广播包和网络冲突;并且有效清除局域网内的病毒、木马等等。三个月后,我再去回访这家客户的时候,他们反映数据库性能有了很大的提高。而且其他应用软件,性能也有所改善。 所以,当企业遇到数据库性能突然降低的时候,第一个反应就是查看网络环境,看看其实否有恶化。只有如此,才可以少走冤枉路。 第二层:服务器配置 这里指的服务器配置,主要是讲数据库服务器的硬件配置以及周边配套。虽然说,提高数据库的硬件配置,需要企业付出一定的代价。但是,这往往是一个比较简便的方法。比起优化SQL语句来说,其要简单的多。 如企业可以通过增加硬盘的数量来改善数据库的性能。在实际工作中,硬盘输入输出瓶颈经常被数据库管理员所忽视。其实,到并发访问比较多的时候,硬盘输入输出往往是数据库性能的一个主要瓶颈之一。此时,若数据库管理员可以增加几个硬盘,通过磁盘阵列来分散磁盘的压力,无疑是提高数据库性能的一个捷径。 如增加服务器的内存或者CPU。当数据库管理员发现数据库性能的不理想是由内存或者CPU所造成的,此时,任何的改善数据库服务器本身的措施都将一物用处。所以,有些数据库管理专家,把改善服务器配置当作数据库性能调整的一个先决条件。 如解决部署在同一个数据库服务器上的资源争用问题。虽然我们多次强调,要为数据库专门部署一个服务器。但是,不少企业为了降低信息化的成本,往往把数据库服务器跟应用服务器放在同一个服务器中。这就会导致不同服务器之间的资源争用问题。如把文件服务器跟数据服务器部署在同一个服务器中,当对文件服务器进行备份时,数据库性能就会有明显的下降。所以,在数据库性能发现周期性的变化时,就要考虑是否因为服务器上不同应用对资源的争夺所造成的。 故,笔者建议,改善数据库性能时第二个需要考虑的层面,就是要看看能否通过改善服务器的配置来实现。 第三层:数据库服务器 当通过改善网络环境或者提高服务器配置,都无法达到改善数据库性能的目的时,接下去就需要考察数据库服务器本身了。首先,就需要考虑数据库服务器的配置。 一方面,要考虑数据库服务器的连接模式。提供了很多的数据库模式,不同的数据库连接模式对应不同的应用。若数据库管理员能够熟悉企业自身的应用,并且选择合适的连接模式,这往往能够达到改善数据库性能的目的。 其次,合理配置数据库服务器的相关作业。如出于安全的需要,数据库管理员往往需要对数据库进行备份。那么,备份的作业放在什么时候合适呢?当然,放在夜晚,夜深人静的时候,对数据库进行备份最好。另外,对于大型数据库,每天都进行完全备份将会是一件相当累人的事情。虽然累得不是我们,可是数据库服务器也会吃不消。差异备份跟完全备份结合将是改善数据库性能的一个不错的策略。 第四层:数据库对象 若以上三个层面后,数据库性能还不能够得到大幅度改善的话,则就需要考虑是否能够调整数据库对象来完成我们的目的。虽然调整数据库对象往往可以提到不错的效果,但是,往往会对数据库产生比较大的影响。所以,笔者一般不建议用户一开始就通过调整数据库对象来达到改善数据库性能的目的。 数据库对象有表、视图、索引、关键字等等。我们也可以通过对这些对象进行调整以实现改善数据库性能的目标。 如在视图设计时,尽量把其显示的内容缩小,宁可多增加视图。如出货明细表,销售人员可能希望看到产品编号、产品中英文描述、产品名字、出货日期、客户编号、客户名字等等。但是,对于财务来说,可能就不需要这么全的信息。他们只需要产品编号、客户编号、出货日期等等少量的信息即可。所以,能可浪费一点代码的空间,设计两张视图,对应不同部门的需求。如此,财务部门在查询数据时,不会为不必要的数据浪费宝贵的资源。 如可以通过合理设置索引来提高数据库的性能。索引对于提高数据的查询效率,有着非常好的效果。对一些需要重复查询的数据、或者数据修改不怎么多的表设置索引,无疑是一个不错的选择。 另外,要慎用存储过程。虽然说存储过程可以帮助大家实现很多需求。但是,在万不得已的情况下,不要使用存储过程。而利用前台的应用程序来实现需求。这主要是因为在通常情况下,前台应用程序的执行效率往往比后台数据库存储过程要高的多。 第五层:SQL 语句 若以上各个层面你都努力过,但是还不满足由此带来的效果的话,则还有最后一招。通过对SQL语句进行优化,也可以达到改善数据库性能的目的。 虽然说SQL Server服务器自身就带有一个SQL语句优化器。他会对用户的SQL语句进行调整、优化,以达到一个比较好的执行效果。但是,据笔者的了解,这个最多只能够优化一些粗略的层面。或者说,80%的优化仍然需要数据库管理员的配合。要数据库管理员跟SQL优化器进行配合,才能够起到非常明显的作用。 不过,SQL语句的调整对于普通数据库管理员来说,可能有一定的难度。除非受过专业的训练,一般很难对SQL语句进行优化。还好笔者受过这方面的专业训练,对这方面有比较深的认识。如在SQL语句中避免使用直接量。任何一个包含有直接量的SQL语句都不太可能被再次使用。我们数据库管理员要学会利用主机变量来代替直接量。不然,这些不可再用的查询语句将使得程序缓存被不可再用的SQL语句填满。这都是平时工作中的一些小习惯。 总之,笔者认为,在数据库性能调优的时候,若能够遵循如上的顺序,必定可以让我们少走冤枉路,不花无用功。其实,数据库调优并没有我们想象的这么难。只要我们能够掌握其中的诀窍,数据库调优将可以手到擒来。上文中讲到的提高SQL Server数据库性能大家要灵活运用,这些方法也不是什么灵丹妙药,要根据具体情况去操作,希望这篇文章在供大家参考的时候能够帮到大家。

龙生   23 Mar 2011
View Details

大内存SQLServer数据库的加速剂

导读:为数据库配置比较大的内存,可以有效提高数据库性能。因为数据库在运行过程中,会在内存中划出一块区域来作为数据缓存。通常情况下,用户访问数据库时,数据先会被读取到这个数据缓存中。当下次用户还需要访问这个数据时,就会从这个数据缓存中读取。因为在数据缓存中读取数据要比在硬盘上读取数据快几百倍。所以扩大数据库服务器内存,可以有效提高数据库性能,特别是操作大型数据库时效果更加明显。 但是,现在企业中普遍采用的数据库服务器都是32位的操作系统。而这个32位的操作系统却有最大内存的使用限制。通常情况下,标准的32位地址最多可以采用4GB的内存。若数据库管理员想让数据库系统采用更多的内存来提高数据库的性能,则就需要进行额外的配置。下面笔者就介绍两种常用的配置方式,让SQLServer数据库服务器支持大内存,让其成为数据库的加速剂。 一、让数据库应用程序支持3GB的内存空间 虽然操作系统支持4GB内存。可是,这并不会全部给数据库等应用程序使用。默认情况下,在32位操作系统中,将有2GB的内存空间是为操作系统所保留的。即使没有用完,其他应用程序也是不能够染指的。而包含SQL Server数据库在内的所有应用程序,只能过采用剩余的2GB内存空间。 但是,在实际应用中,操作系统往往用不着多大2G的内存。根据笔者的经验,一般只要为操作系统保留1G的内存已经足够其使用。只要没有病毒等不良因素作怪,这个内存不会被完全适用。如此的话,应用程序可以采用的内存空间就会多达3G,比原先整整多出一个G来。 要实现这个转变,其实很简单。在Windows操作系统中,有一个BOOT启动配置文件。为了让数据库服务器支持3GB的用户模式进程空间,必须在这个配置文件中,加入一个/3gb的参数,然后重新启动操作系统即可。这么设置之后,应用程序就可以寻址3GB的进程地址空间 ,而为操作系统保留1GB的内存空间。 有时候,这个小小的配置可以在很大程度上提高数据库的性能。记得有一次,笔者为一家企业优化数据库性能。笔者查看了用户的数据库环境之后,就建议用户增大数据库服务器的内存,从2G增加到4G。可是,效果并没有很大的改善。正当笔者束手无措的时候,就想到了改变操作系统与应用程序的内存分配方式。为此,笔者就更改了BOOT启动配置文件,只给操作系统保留1G的内存空间。重新启动后,数据库性能得到了很大的改善。 二、为SQLServer启用更高的内存支持 如果数据库应用程序内存寻址空间达到3GB后,数据库管理员还不满足的话,则就需要通过增加物理内存的方式,来提高应用程序的性能。若需要服务器操作系统突破其默认4GB内存空间的限制,支持4GB以上的内存空间,也不是不可能的。只是需要进行额外的配置,并且,其维护的工作量也比较大。 若想要SQLServer数据库支持4GB以上的内存寻址空间,则往往需要进行如下配置。 第一步:锁定内存页。 默认情况下,内存大小与操作系统的虚拟内存之间有一个正比例关系。在这里,数据库管理员只想增大服务器的物理内存,而不想对虚拟内存有什么影响。故需要锁定内存页。锁定内存页的主要作用就是确定哪些帐户可以使用进程将数据保留在物理内存中,从而阻止系统将数据分页到磁盘的虚拟内存中。默认情况下,这个选项的只为OFF。也就是说,在必要的时候,系统会将数据分页到硬盘的虚拟空间中。为了最大程度发挥内存的效用,就需要把这个选项开启。不过这数据库管理员往往需要寻求系统管理员的帮助,因为只有具有系统管理员权限的用户,才能够给更改这个选项。 第二步:启用Awe Enable选项。 默认情况下,即使服务器操作系统支持4GB以上的内存空间,可是数据库应用程序并不一定支持。为了让SQLServer应用程序也支持这个,就必须更改数据库的配置参数。也就是说,需要将这个选项的值设置为1,然后重新启动数据库系统。这个配置比较简单,只需要利用命令sp_configure 'awe enabled', 1即可。不过在进行这个配置之前,需要注意两个细节方面的内容。一是数据库用户需要这个操作的权限。二是这里有一个BUG,即在SQL Server数据库中会有一个错误信息。数据库管理员可以忽略这个信息。 第三步:限制文件系统缓存。 若增加的内存给操作系统或者其他应用程序用了,那么数据库管理员不是白忙一场吗?为此,数据库管理员还需要优化数据库系统内存的使用情况。如需要限制系统用于文件缓存的内存量。如要这么处理的话,只需要简单的三个步骤即可。 首先,数据库管理员在操作系统中,找到控制面板,并双击网络连接,然后选中本地连接。其次,双击本地连接,在弹出的对话框中,找到常规选项卡,单击属性。选中网络文件与打印机共享,并单击属性。最后,在弹出的对话框中,去掉“最大化网络应用程序数据吞吐量”复选框。一路按确认即可。这个简单的步骤,就可以优化数据库内存的使用率。 三、大内存维护管理几个关键点 在通常情况下,往往不需要启用4GB以上的内存。但是,若在服务器上,同时启用了其他的应用程序服务。如在一台服务器上同时有数据库应用程序、邮件应用程序、文件服务器等多个应用服务的话,则可能原有的4GB内存无法满足。系统管理员不得不对内存进行升级。但是,对内存升级之后,数据库管理员需要手工对内存的分配进行干预,以免SQLServer应用程序占用比较多的内存空间,而影响其他应用程序的性能。 1、配置max server memory选项。虽然说这个选项并不是必须要修改的,但是笔者仍强烈建议数据库管理员要修改这个选项。特别是数据库应用程序与其他应用程序共享同一台服务器时。因为启动SQLServer对大内存的支持后(将Awe Enabled设置为1),而且可用物理内存大于用户模式进程空间。则当启动数据库服务器时,运行的SQLServer实例将会占用几乎所有的可用内存(不管需不需要使用,数据库服务器程序会先锁定这些内存。这就叫占着茅坑不拉屎)。而这个max server memory选项就是用来配置其最大可以占用的内存数量。数据库管理员需要预先估算出一个合理的数值,然后进行配置。让数据库应用程序与其他应用服务能够共同改善,至少不能够对其他应用程序的性呢产生不良影响。在比较极端的情况下,可以在升级内存之前,先关闭数据库应用程序;然后启用其他应用程序服务。观测一段时间,看看他们所需要用到多少的内存。然后升级内存,并为其他应用程序至少保留以前所需要的内存空间。否则的话,就会对其他应用程序产生不良影响。牺牲其他应用程序的性能来提高数据库的性能,这是拆西墙补东墙的做法,不值得取。 2、多个SQLServer实例内存如何分配。往往在一个SQLServer数据库中,会配置多个数据库实例。一个数据库实例用来负责ERP系统的运行,另外一个则是给CRM系统使用。在同一个数据库系统中,有多个数据库实例,此时该如何在各个实例之间分配可用的内存呢?这基于操作系统的不同,又有所不同。如果数据库操作系统采用的是2000系列的,则需要为每个数据库实例配置max server memory选项。否则的话,其中某一个数据库实例就有可能锁定全部的可用内存。这主要是因为2000系列的服务器系统并不支持动态分配大内存。所以需要为每个数据库实例配置这个选项。但是,若数据库服务器采用的是2003系列的服务器操作系统,则不需要进行手工的配置这些参数。因为操作系统会动态地分配内存。也就是说,操作系统会按照总体系统要求平衡SQLServer各个实例之间内存的使用。不过为了提高数据库整体性能,最好还是为各个实例配置max server memory;而不让操作系统来搞平衡。 从上文中可以总结出大内存是好处多多,大内存加速了SQLServer数据库,使SQLServer数据库的操作性能更为的优化,实现了SQLServer数据库的高速发展。

龙生   23 Mar 2011
View Details

改善SQL Server数据库查询速度慢的技巧

SQL Server数据库查询速度慢的原因有很多,常见的有以下几种: 1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2、I/O吞吐量小,形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好,没有优化 可以通过以下方法来优化查询 : 1、把数据、日志、索引放到不同的I/O设备上,增加读取速度,以前可以将Tempdb应放在RAID0上,SQL2000不在支持。数据量(尺寸)越大,提高I/O越重要。 2、纵向、横向分割表,减少表的尺寸(sp_spaceuse) 3、升级硬件 4、根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小,使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段。 5、提高网速。 6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。 配置虚拟内存:虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000时,可考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。如果另外安装了全文检索功能,并打算运行Microsoft搜索服务以便执行全文索引和查询,可考虑:将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。将SQL Server max server memory服务器配置选项配置为物理内存的1.5倍(虚拟内存大小设置的一半)。 7、增加服务器CPU个数;但是必须 明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务,就可以在处理器上运行。例如耽搁查询 的排序、连接、扫描和GROUP BY字句同时执行,SQL SERVER根据系统的负载情况决定最优的并行等级,复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT, DELETE还不能并行处理。 8、如果是使用like进行查询的话,简单的使用index是不行的,但是全文索引,耗空间。 like "a%" 使用索引 like "%a" 不使用索引用 like "%a%" 查询时,查询耗时和字段值总长度成正比,所以不能用CHAR类型,而是VARCHAR。对于字段的值很长的建全文索引。 9、DB Server 和APPLication Server 分离;OLTP和OLAP分离 10、分布式分区视图可用于实现数据库服务器联合体。 联合体是一组分开管理的服务器,但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器,以支持大型的多层 Web 站点的处理需要。有关更多信息,参见设计联合数据库服务器。(参照SQL帮助文件"分区视图") a、在实现分区视图之前,必须先水平分区表 b、在创建成员表后,在每个成员服务器上定义一个分布式分区视图,并且每个视图具有相同的名称。这样,引用分布式分区视图名的查询可以在任何一个成员服务器上 运行。系统操作如同每个成员服务器上都有一个原始表的复本一样,但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。 11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长,它会降低服务器的性能。 在T-sql的写法上有很大的讲究,下面列出常见的要点:首先,DBMS处理查询计划的过程是这样的: 1、 查询语句的词法、语法检查 2、 将语句提交给DBMS的查询优化器 3、 优化器做代数优化和存取路径的优化 4、 由预编译模块生成查询规划 5、 然后在合适的时间提交给系统处理执行 6、 最后将执行结果返回给用户。 SQL Server数据库查询速度慢给很多用户带来很多的不便,而且也影响了工作的进程,上面的方法就能彻底改变慢的状况,给SQL Server数据库查询加速。

龙生   23 Mar 2011
View Details

浅析SQL Server 2008中的代码安全之六:对称密钥加密

证书和非对称密钥使用数据库级的内部公钥加密数据,并且使用数据库级内部私钥解密数据。而对称密钥相对简单,它们包含一个同时用来加密和解密的密钥。困此,使用对称密钥加密数据更快,并且用在大数据时更加合适。尽管复杂度是考虑使用它的因素,但它仍然是一个很好的加密数据的选择。 我们看一组例子: 示例一、创建对称密钥 对称密钥的特性是:在数据库会话中使用它对数据进行加密和解密前必须首先打开。 创建对称密钥使用如下命令:CREATE SYMMETRIC KEY  创建对称密钥。(http://msdn.microsoft.com/en-us/library/ms188357.aspx)

示例二、查看当前数据库中的对称密钥 使用目录视图sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)来查看。

示例三、修改非对称密钥的加密方式 你可以使用ALTER SYMMETRIC KEY(http://technet.microsoft.com/en-us/library/ms189440.aspx)命令修改对称密钥的加密方式。但执行前必须使用OPEN SYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)命令打开它。

示例四、使用对称密钥对数据进行加密和解密。 1、为了使用对称密钥对数据进行加密,必须首先打开它,然后使用函数EncryptByKey 加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx) 2、使用DecryptByKey来解密使用对称密钥加密的数据。注意DecryptByKey不像甩EncryptByKey,无须使用对称密钥GUID。因此,为了解密,必须打开正确的对称密钥会话,否则会显示null。 下面是一个例子:

查看未加密的数据: --解密数据

至此,好像已经大功告成了,别,千万别高兴得太早! 这里有个问题,如果恶意用户不知道CustomerID=13的PasswordHintAnswer列的真实值,但知道CustomerID=14的PasswordHintAnswer列的真实值,则完全可以通过恶意替换PasswordHintAnswer列而绕过加密!!此时,我们索性连CustomerID列作为验证列也一起加密,以绝后患 ! 注意:加密的验证列也可以由另一个相关表的列作为参数传入。 看一个完整的例子:

恶意替换开始:

此时,我们再查看:

郎勒个郎!爽吧!虽然复制了相同的二进制数据,可是读取结果令攻击者大失所望啊! 示例五、删除对称密钥 命令:DROP SYMMETRIC KEY 删除指定的对称密钥( http://technet.microsoft.com/en-us/library/ms182698.aspx) 例子:

注意:如果加密密钥打开没有关闭,则drop失败。 小结: 1、本文主要介绍对称密钥的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。 2、对称密钥的特性是:在数据库会话中使用它对数据进行加密和解密前必须首先打开。 3、对称密钥可用于大数据的加密。 下文将主要介绍证书加密(Certificate Encryption) 原文链接:http://www.cnblogs.com/downmoon/archive/2011/03/15/1984352.html

龙生   23 Mar 2011
View Details

MSSQL 2005数据库安全设置

导读:MSSQL 2005数据库的安全问题受到人们的高度重视。拿到MS SQL 2005还没有一天,总的来说,MS SQL2005的安全性比SQL2000要强上不少,不过默认设置还是有和原来一样的漏洞,下面我们来修改一下默认设置,以确保MSSQL 2005数据库的安全。 拿到MS SQL 2005还没有一天,总的来说,MS SQL2005的安全性比SQL2000要强上不少,不过默认设置还是有和原来一样的漏洞,下面我们来修改一下默认设置。 1、安装MSSQL时使用混合模式,当然SA密码最好不能为空,在SQL2005中,可以对SA这个超级用户名进行修改或删除。 use master ALTER LOGIN [sa] WITH NAME=[zxs] /*修改SA帐号*/ sp_password "111111","123456","sa" /*修改SA密码*/ 使用以上命令可修改SA帐号,也可进行图形化的修改 使用安全的帐号策略。对SA或等同用户进行最强的保护,当然,包括使用一个非常强壮的密码,如下图 这里可以看到SQL 2005比SQL 2000 增强的地方了。 2、SQL的认证有Windows身份认证和混合身份认证。 在2005中,登陆SQL可以使用indows身份认证和混合身份认证方便,如果不希望系统管理员接触数据库的话,可以在安全性登录名是把系统帐号“BUILTIN\Administrators”删除。当然,还有别的多余的帐号也可一同禁止,只需要在帐号属性状态中把是否允许连接到数据库引擎改为拒绝,还有登录改为禁用即可,不过这样的坏处就是当你忘了SA的密码时,我也没办法帮你啦。 也可用命令sp_change_users_login "report" 搜索一下独立的帐户再删除 3、管理扩展存储过程 删除不必要的存储过程,因为有些存储过程能很容易地被人利用起来提升权限或进行破坏。 如果你不需要扩展存储过程xp_cmdshell请把它去掉。xp_cmdshell根本就是一个大后门。 如果不需要请丢弃OLE自动存储过程(会造成管理器中的某些特征不能使用),这些过程包括如下: sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty sp_OAMethod sp_OASetProperty sp_OAStop 去掉不需要的注册表访问的存储过程,注册表存储过程甚至能够读出操作系统管理员的密码来,如下: xp_regaddmultistring xp_regdeletekeyxp_regdeletevaluexp_regenumvalues xp_regread xp_regremovemultistring xp_regwrite 还有一些其他的扩展存储过程,你也最好检查检查。 在处理存储过程的时候,请确认一下,避免造成对数据库或应用程序的伤害。运行以下SQL语句可删除所有危险的SP。 DROP PROCEDURE sp_makewebtask exec master..sp_dropextendedproc xp_cmdshell /*命令行*/ exec master..sp_dropextendedprocxp_dirtree/*可以展开你需要了解的目录,获得所有目录深度*/ exec master..sp_dropextendedproc xp_fileexist/*用来确定一个文件是否存在*/ exec master..sp_dropextendedproc xp_getnetname /*可以获得服务器名称*/ exec master..sp_dropextendedproc xp_terminate_process exec master..sp_dropextendedproc sp_oamethod exec master..sp_dropextendedproc sp_oacreate exec master..sp_dropextendedproc xp_regaddmultistring exec master..sp_dropextendedproc xp_regdeletekey exec master..sp_dropextendedproc xp_regdeletevalue exec master..sp_dropextendedproc […]

龙生   23 Mar 2011
View Details

在SQL Server数据库开发中的十大问题

导读:在SQL Server中进行开发会让你身处险地,并且寻找快速解决方案。我们编辑了前十名关于SQL Server开发的常见问题。对常见的针对表和字段的名字约束进行探究。学习如何解决并发问题,并且在不了解T-SQL的情况下编写存储过程。查看这些解决方案的快速参考,并且了解更多内容。这些问题都是十分常见的,相信大家在工作中肯定遇到过同样的问题,接下来就让们一起探讨在SQL Server数据库开发中遇到的问题。 在SQL Server开发问题中你可能会问到的十个问题: 1、什么是常见的对表和字段的名字约束? 2、有没有可能在不了解T-SQL的情况下编写存储过程 3、T-SQL 中如何比较CLR 存储过程和函数的性能? 4、我如何在一个存储过程中使用另一个存储过程产生的结果? 5、我如何解决SQL Server 2005的并发问题? 6、在SQL Server 2005中用什么工具替代了查询分析器? 7、你能提供一些有关SQL 和T-SQL的详细信息吗? 8、SQL Server 2005 有没有新的索引类型? 9、我如何创建一个脚本在表中进行选择? 10、我如何列出那些没有记录的数据库表? 对于表和字段的常见名字约束 专家回答: SQL Server 2000下的表和字段名称有1到128字节的限制,并且遵循用于标识的规则。第一个字母必须是如下的一种: Unicode Standard 2.0中规定的字母。 Unicode对字母的定义包括:拉丁字母,从A到Z,除了来自其他语言的字母之外。 下划线(_),at符号(@),或者数字符号(#) 在SQL Server中以这些符号作为标识符的开始具有特殊的含义。一个以at符号(@)开头的标识符表示一个本地的变量或者参数。一个以数字符号(#)开头的标识符代表一个临时表或者过程。一个以两个数字符号(##)开头的标识符标识的是一个全局临时对象。一些Transact-SQL函数的名字以两个at符号(@@)开头。为了避免与这些函数混淆,推荐你不要使用两个at符号(@@)开头的标识符。接下来的字母可以是以下的任意几种: Unicode Standard 2.0定义的字母 来自基础拉丁文或者其他语音的十进制数字 at符号(@),美元符号($),数字符号(#),或者下划线 标识符绝对不能是Transact-SQL的保留字。SQL Server保留了一些大写和小写的保留字。内建的空间或者特殊的字母都不允许出现,尽管你可以在好的老版本的Northwind中看到它们包含了内建的空间。你必须通过把它们括在括号中才可以访问。 不具有任何T-SQL知识的情况下编写SQL Server2005存储过程吗? 作为过去几年里面微软试图用SQL Server 2005的.net集成来称霸市场的野心的结果,许多程序员都认为创建SQL Server存储过程不再必需T-SQL了。不幸的是(或者并非如此,这根据你的观点),这并不全是事实。在技术上是可以在不了解T-SQL的情况下创建存储过程的,但是没有T-SQL的话则无法访问任何的数据。 在CLR存储过程内部进行数据访问,是通过使用标准的ADO.NET类来完成的。开发人员会在应用程序层发现很多同样的没有用处的数据访问代码,这些代码会很轻易地转移到SQLCLR例程中去。当中间层的这些ADO.NET类需要使用T-SQL来访问数据的时候,在 CLR主机提供的环境中就会使用同样的类。 我要强调的是,从技术角度来说,不使用T-SQL来编写存储过程是可能的。那么有没有理由这么做呢?一种情况就是这是一个用来从普通文件或者网络服务中检索数据的CLR存储过程,并将数据格式设置为行集。这里可能就会用到不需要T-SQL的操作——但是这并不是对T -SQL存储过程能力的一个很好的比喻。 CLR存储过程vs. T-SQL存储过程 CLR存储过程和函数与T-SQL存储过程和函数相比,性能如何? 专家回答: 这里是用来比较T-SQL例程和CLR例程性能的一种常用的规则:用你的数据,在你的服务器上,测试两套配置,看看哪一个比较好。 就是说,许多人都运行过性能测试,一般的结果就是T-SQL在标准的CRUD(创建、读取、更新、删除)操作上表现要比好一点,而在复杂数学、字符串赋值和其他的超过数据访问的任务上,则是CLR的性能表现更好一点。 SQL Server 的专家Gustavo Larriera编辑了如下一些关于这个主题的有用链接: 在 SQL Server 2005中使用CLR Integration。 简单介绍在 SQL Server 2005中的 CLR Integration。 在 SQL Server 2005中对CLR 和T-SQL做出选择。 介绍 SQL Server 2005中的 CLR Integration。 SQL […]

龙生   23 Mar 2011
View Details

解析如何提高SQL Server 的安全性

导读:对抗黑客侵袭的方法有很多,SQL Server 数据库技术在发展,当然黑客技术也在不断更新,有一些步骤你可以用来使SQL Server 数据库对篡改数据和黑客攻击更有抵抗力。 下面的五个步骤将带您开始抵抗黑客保障数据库安全性的工作。 1、查找最新的服务程序包 时刻确保你装了最新的服务程序包。对于SQL Server 2000,这个补丁是SP3a 。记住,服务程序包是渐增的,所以如果你应用了SP3a ,你就不需要再应用其他任何在此之前的程序包,例如SP3, SP2 或 SP1。SP3a 是一个特殊的服务程序包,它是为那些没有进行过以前的任何更新而设计的安装程序,而SP3 则是为那些已经安装了SP1 或SP2准备的安装程序。 2、使用安全警报 补丁能够帮助你保护你的SQL Server 数据库免受许多威胁,但是他们的发布速度总是跟不上那些移动迅速的安全性问题的处理,例如Spammer 蠕虫。所以你就会想要使用微软公司的免费的安全通知服务,一封电子邮件服务就可以使你了解关于破坏安全的问题和怎样处理它们。 3、运行微软的基线安全分析器(MBSA) 这个工具对于SQL Server 和MSDE 2000 Desktop Engine 都是可用的,并且它可以在本地运行,也可以在网络中运行。它寻找密码,访问权限,访问控制清单和注册的问题,并且检查遗漏的安全程序包或服务程序包。你可以在TechNet上找到这个工具的相关信息。 4、删除SA和旧密码 人们犯的关于密码的最大的安全性错误就是对系统管理员(SA)密码不做任何修改。你可能很轻易的忽略安装文件中的剩余的配置信息,保护得很差的认证信息和其他一些敏感的数据就会遭黑客破坏。你必须删除在这个路经下的旧的安装文件:Program Files\Microsoft SQL Server\MSSQL\Install或Program Files\Microsoft SQL Server\MSSQL$\Install folders。还有,可以使用KILLPWD应用程序来找旧的密码并且删除它们。Knowledge Base文章263968对这个问题做了详细的说明。 5、监控连接 连接可以告诉你谁试图访问SQL Server,所以监视和控制连接是保护数据库安全的一个非常好的方法。对于一个大型的活动的SQL Server 数据库,可能会有太多的数据连接需要监控,但是监控失败的连接是非常有价值的,因为它们可能表现出使用的企图。你可以在企业管理器中在服务组上右击,然后选择Properties,记录下失败连接的日志。然后点击Security选项卡,在Audit Level下点击Failure来停止并重新启动服务器。 SQL Server系统身份验证方式 1. 用户标识与验证 用户标示和验证是系统提供的最外层安全保护措施。其方法是由系统提供一定的方式让用户标示自己的名字或身份。每次用户要求进入系统时,由系统进行核对,通过鉴定后才提供机器使用权。 对于获得上机权的用户若要使用数据库时数据库管理系统还要进行用户标识和鉴定。 用户标识和鉴定的方法有很多种,而且在一个系统中往往是多种方法并举,以获得更强的安全性。常用的方法有: 用一个用户名或者用户标识号来标明用户身份。系统内部纪录着所有合法用户的标识,系统验证此户是否合法用户,若是,则可以进入下一步的核实;若不是,则不能使用系统。 为了进一步核实用户,系统常常要求用户输入口令(Password)。为保密起见,用户在终端上输入的口令不显示在屏幕上。系统核对口令以验证用户身份。 用户标识与验证在SQL Server中对应的是Windows NT/2000登录账号和口令以及SQL Server用户登录账号和口令。 2. SQL Server身份验证方式 用户必须使用一个登录账号,才能连接到SQL Server中。SQL Server可以识别两类的身份验证方式,即:SQL Server身份验证(SQL Server Authentication)方式和Windows身份验证(Windows Authentication)方式。这两种方式的结构如图2所示。这两种方式都有自己的登录账号类型。 注意的是,如果在Microsoft Windows95/98/ME上使用SQL Server的Personal版,作为SQL Server宿主的Microsoft Windows95/98/ME系统只能使用SQL Server登录。因此,Windows NT/2000身份验证、域用户的账号和域组账号都是不可用的。 当使用SQL Server身份验证方式时, 由SQL Server系统管理员定义SQL Server账号和口令。当用户连接SQL Serve时,必须提供登录账号和口令。当使用Windows身份验证方式时,由Windows NT/2000账号或者组控制用户对SQL Server系统的访问。这时,用户不必提供SQL Server的Login账号和口令就能连接到系统上。但是,在该用户连接之前,SQL Serve系统管理员必须将Windows NT/2000账号或者Windows […]

龙生   23 Mar 2011
View Details

SQL Server:安全设计从头起

最基本的要点 如果你不能理解SQL Server security基本的概念,就马上先停止开发并先阅读这些开发准则,你不可能在不知道这些概念的基础上就能够正确地使一个数据库安全化。程序的安全正如一辆卡车一样。你具有一个发动机,一把钥匙,当钥匙打开发动机即发动机启动之后,就有可能发生的全部过程。如果你忽略了某些细节,驾驶过程中就会发生很多麻烦。在问题产生之后,你可以将卡车交给一个修理工,然而对于程序的安全问题,你自己将是数据库的修理工。 开始,你必须选择以下两种安全模式: Windows 认证模式:用户通过一个现成的Windows用户帐号来连接服务器。当一个用户试图与一个服务器连接的时候,SQL Server将会认证用户的Windows帐号的名称和密码。用户不能同时进入网络和SQL Server,只能进入其中的一个。这一方法也被称为一个被依赖的连接。 混合模式:这一模式将Windows认证模式和SQL Server认证联接在一起。用户可以通过一个Windows用户帐号进行连接,这正如Windows认证一样。但是,你也可以在SQL Server中直接建立用户帐号。每一个SQL Server帐号都存储了一个用户名和密码。 我们建议在可能的情况下都使用Windows认证模式。然而,混合模式很可以使用,特别是在SQL Server 7.0(或更早版本)都可以使用。.对于早期的SQL Server版本,SQL Server认证存在一些缺点。Windows认证已经集成了操作系统的安全系统,这就提供了比SQL Server认证更多的安全特性,很容易的使用,效率更加高,安全性更加好。在设计开始的时候,你应该认真考虑选择哪一种模式最为合适。 密码 无论采用哪一些模式,你应该记住在SQL Server中为系统管理者(sa)设置一个密码。当安装SQL Server时,安装程序会自动建立一个带有SQL Server注册名称(sa)和一个空白密码的管理用户。如果你保持这些用户设置原样而使用混合安全模式,任何具备一点SQL Server基础知识的用户都可以很容易地进入到你的数据库中并做任何他想要做的事情。如果你使用的是Windows认证模式,在理论上你无需为sa用户设置一个密码,因为SQL Server注册不会接受这些的设置。但是设置是一个很良好的操作,尤其是当你被迫转移到将来使用的混合模式的时候。 如果安全模式已经启动,注册将成为关键。你必须使用正确的方法来启动程序,注册也是同样的,如果你不能正确地输入用户名和密码,程序将不能连接到网络,同时也不能连接到SQL Server。 作为一个管理者,一旦你已经启动了程序,你就做好安全管理的准备。可以通过定义以下的特性而实现: 用户(帐号):一个SQL Serve安全帐号代表着一个唯一的用户。一个用户也只有一个Windows帐号或者一个SQL Server注册,这与数据库中的用户帐号相统一。 组(帐号):每一个用户都属于一个或多个组,这由认证模式决定。每一个组都具有特定许可。作为一个组的成员,你将获得所有组的许可。 对象所有权:所有权属于建立对象的用户。所有者可以将访问权限分配给用户。如果你是一个视的所有者,你还可以决定哪些用户可以通过视来查看数据。 许可:一个许可代表着具有执行某些操作的权利,比如打开一个视或者更改一个存储程序。SQL Server承认许可的三种状态:GRANT给你一个用户访问;REVOKE删除访问;DENY防止用户访问对象。 任务:这是一个SQL Server安全帐号,可以将帐号的集合作为一个简单的单元来处理。任务定义在特定数据库中用户可以做哪些和哪些不可以做。 从安全的角度而考虑 设计过程应该用效地定义哪些地方需要进行安全设置和如何设置。在这一过程之前你应该从两个方面考虑: 敏感数据; 可以查看敏感数据的人。 敏感数据包括所有可能的数据,包括整个数据库中的所有数据,虽然这样的安全级别很少存在。你的工作就是定义为敏感数据并进行保护。 你选择的认证模式和建立的注册将通过限制哪些用户可以进入到数据库而实施第一步安全步骤。 第二步就要列举可以访问数据库的所有用户,然后决定所有数据是否对所有的用户都适用。通常,你需要对一些数据进行保护,比如工资或者其他私人数据。这就意味着只有特定的用户可以访问和查看数据。你还可以设置哪些用户更改数据。 始终记住的一条规则是“最小权利”概念。如果有人在他的工作中不需要访问数据,那即不要给他访问的权限。应该避免所有的用户都具有sa用户的访问权限。 具体的建议 当进行安全设置的时候,经验也是一个很好的老师,但通常也会有适用于通用数据库的设计准则: 从开始就获得数据库和对象的所有权。当建立一个新的数据库时,你将会成为数据库的所有者,并能够设置数据库中所有发生一切。你可以以管理者的身份注册数据库。然而,对象的所有权属于建立对象的用户。虽然这可能造成所有权的转移,但可以确定这些的注册能够被用于建立所有的对象。 理解所有权链。这一安全特性防止用户建立自己的视而偷看一些敏感数据。例如,假设你建立一个从两个表中集中的数据的视,如果你是这两个表的所有者,当你允许其他用户使用视的许可时,SQL Server不会检查表的有关许可。 使用视和存储程序以分配给用户访问数据的权利,而不是让用户编写一些直接访问表格的特别查询语句。通过这种方式,你无需在表格中将访问权利分配给用户。视和存储程序也可以限制查看的数据。例如,如果你的雇员表格包含一些秘密的工资信息,你可以建立一个省略了工资栏的视。 如果用户从特定程序中进入你的程序,你可以建立程序任务。一个程序任务就是分配到特定程序的用户,并给予用户的有关许可。使用程序任务,用户不能直接地认证数据库,相反,他们先认证他们自己的程序,这就决定哪些程序任务与服务器相连接。 时刻注意程序补丁。不可否认的,程序补丁是一些诀窍的集合。程序的发布,更新等都会引入新的问题,使用程序补丁是防止外界干扰和保护数据的最好和最容易的方法。可以访问微软主页上的SQL Service Pack下载页,查看当前最新的程序补丁。 结论 程序安全机制是每一个开发人员都必须面临的问题。不要等到数据库开发过程中和使用中才考虑到安全问题──安全是设计过程中的重要组成部分。除此之外,不要随意地应用安全设置而希望达到最佳的效果,应该学会选择安全模式并正确地应用。SQL Server的安全设计就为大家讲解到这里,希望文章中的内容对您有帮助。

龙生   23 Mar 2011
View Details