All posts by 龙生

大内存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)来查看。

<ol class="dp-sql"><li class="alt">--查看当前数据库中的非对称密钥  </li><li>use DB_Encrypt_Demo  </li><li class="alt">go  </li><li>SELECT name, algorithm_desc FROM sys.symmetric_keys  </li><li class="alt">----结果返回  </li><li>/*  </li><li class="alt">name    algorithm_desc  </li><li>sym_Demo    TRIPLE_DES  </li><li class="alt">*/  </li><li> </li></ol>

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

<ol class="dp-sql"><li class="alt">use DB_Encrypt_Demo  </li><li>go  </li><li class="alt">--先用私钥密码打开对称密钥  </li><li>OPEN SYMMETRIC KEY sym_Demo  </li><li class="alt">DECRYPTION BY ASYMMETRIC KEY symDemoKey  </li><li>WITH PASSWORD = 'TestSYM456!' </li><li class="alt">--打开之后，先增加密码加密，取代原密钥  </li><li>ALTER SYMMETRIC KEY sym_Demo  </li><li class="alt">ADD ENCRYPTION BY PASSWORD = 'newnew!456' </li><li>--再删除非对称密钥加密  </li><li class="alt">ALTER SYMMETRIC KEY sym_Demo  </li><li>DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey  </li><li class="alt">--完成操作后，关闭对称密钥  </li><li>CLOSE SYMMETRIC KEY sym_Demo    </li></ol>

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

<ol class="dp-sql"><li class="alt">/***************************************************/  </li><li>USE DB_Encrypt_Demo  </li><li class="alt">GO  </li><li>--创建测试数据表，用于对称加密  </li><li class="alt">CREATE TABLE dbo.PWDQuestion  </li><li>(CustomerID int NOT NULL PRIMARY KEY,  </li><li class="alt">PasswordHintQuestion nvarchar(300) NOT NULL,  </li><li>PasswordHintAnswer varbinary(200) NOT NULL)  </li><li class="alt">GO  </li><li>--插入加密数据  </li><li class="alt">OPEN SYMMETRIC KEY sym_Demo  </li><li>DECRYPTION BY PASSWORD = 'newnew!456' </li><li class="alt">INSERT dbo.PWDQuestion  </li><li>(CustomerID, PasswordHintQuestion, PasswordHintAnswer)  </li><li class="alt">VALUES </li><li>(12, '您出生的医院名称？',  </li><li class="alt">EncryptByKey(Key_GUID('sym_Demo '), '杭州市一'))  </li><li>CLOSE SYMMETRIC KEY sym_Demo  </li><li class="alt"> </li></ol>

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

<ol class="dp-sql"><li class="alt">OPEN SYMMETRIC KEY sym_Demo  </li><li>DECRYPTION BY PASSWORD = 'newnew!456' </li><li class="alt">SELECT CustomerID,PasswordHintQuestion,  </li><li>CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer  </li><li class="alt">FROM dbo.PWDQuestion  </li><li>WHERE CustomerID = 12  </li><li class="alt">--打开后切记关闭！！！  </li><li>CLOSE SYMMETRIC KEY sym_Demo   </li></ol>

<ol class="dp-sql"><li class="alt">--不打开直接读取  </li><li>SELECT CustomerID,PasswordHintQuestion,  </li><li class="alt">CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer  </li><li>FROM dbo.PWDQuestion  </li><li class="alt">WHERE CustomerID = 12   </li><li> </li></ol>

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

<ol class="dp-sql"><li class="alt">truncate table dbo.PWDQuestion  </li><li>go  </li><li class="alt">--添加两个未加密的行  </li><li>INSERT dbo.PWDQuestion  </li><li class="alt">(CustomerID, PasswordHintQuestion, PasswordHintAnswer)  </li><li>select 13, '您出生的医院名称？',cast('浙江妇保院' as varbinary)  </li><li class="alt">union all </li><li>select  14, '您出生的医院名称？',cast('浙江妇保二院' as varbinary)  </li><li class="alt">--打开对称密钥,连CustomerID列一起加密  </li><li>OPEN SYMMETRIC KEY sym_Demo  </li><li class="alt">DECRYPTION BY PASSWORD = 'newnew!456' </li><li>UPDATE dbo.PWDQuestion  </li><li class="alt">SET PasswordHintAnswer =  </li><li>EncryptByKey(Key_GUID('sym_Demo'),  </li><li class="alt">PasswordHintAnswer,1,--1表示使用验证器值  </li><li>CAST(CustomerID as varbinary))  </li><li class="alt">WHERE CustomerID in (13,14)  </li><li>--打开后切记关闭！！！  </li><li class="alt">CLOSE SYMMETRIC KEY sym_Demo  </li><li>--此时必须这样查看原数据  </li><li class="alt">OPEN SYMMETRIC KEY sym_Demo  </li><li>DECRYPTION BY PASSWORD = 'newnew!456' </li><li class="alt">SELECT CustomerID,PasswordHintQuestion,  </li><li>CAST(DecryptByKey(PasswordHintAnswer, 1,--1表示使用验证器值  </li><li class="alt">CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer  </li><li>FROM dbo.PWDQuestion  </li><li class="alt">WHERE CustomerID = 13  </li><li>--打开后切记关闭！！！  </li><li class="alt">CLOSE SYMMETRIC KEY sym_Demo   </li><li> </li></ol>

恶意替换开始：

<ol class="dp-sql"><li class="alt">/**********************************************************  </li><li>--我们用刚才的CustomerID = 13的PasswordHintAnswer列值  </li><li class="alt">--替换CustomerID = 14的PasswordHintAnswer列值，  </li><li>--再用刚才读取14的方法读取真实值**********************************************************/  </li><li class="alt">update dbo.PWDQuestion set PasswordHintAnswer=  </li><li>(select PasswordHintAnswer from   dbo.PWDQuestion where CustomerID = 14)  </li><li class="alt">where CustomerID = 13  </li></ol>

此时，我们再查看：

<ol class="dp-sql"><li class="alt">OPEN SYMMETRIC KEY sym_Demo  </li><li>DECRYPTION BY PASSWORD = 'newnew!456' </li><li class="alt">SELECT CustomerID,PasswordHintQuestion,  </li><li>CAST(DecryptByKey(PasswordHintAnswer, 1,--1表示使用验证器值  </li><li class="alt">CAST(CustomerID as varbinary)) as varchar(200))   </li><li>PasswordHintAnswer,PasswordHintAnswer as binaryValue  </li><li class="alt">FROM dbo.PWDQuestion  </li><li>WHERE CustomerID in(13,14)  </li><li class="alt">--打开后切记关闭！！！  </li><li>CLOSE SYMMETRIC KEY sym_Demo   </li></ol>

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

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DROP</font></strong></span><span> SYMMETRIC </span><span class="keyword"><strong><font color="#006699">KEY</font></strong></span><span> symDemoKey  </span></span></li></ol>

注意：如果加密密钥打开没有关闭，则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

构造SQL Server的安全门

SQL Server的数据安全是数据库的重中之重，数据库安全门一定要完善好，做好安全保护工作，以下就是构造SQL Server的安全门的方法。一、验证方法选择本文对验证(authentication)和授权(authorization)这两个概念作不同的解释。验证是指检验用户的身份标识;授权是指允许用户做些什么。在本文的讨论中，验证过程在用户登录SQL Server的时候出现，授权过程在用户试图访问数据或执行命令的时候出现。构造安全策略的第一个步骤是确定SQL Server用哪种方式验证用户。SQL Server的验证是把一组帐户、密码与Master数据库Sysxlogins表中的一个清单进行匹配。Windows NT/2000的验证是请求域控制器检查用户身份的合法性。一般地，如果服务器可以访问域控制器，我们应该使用Windows NT/2000验证。域控制器可以是Win2K服务器，也可以是NT服务器。无论在哪种情况下，SQL Server都接收到一个访问标记(Access Token)。访问标记是在验证过程中构造出来的一个特殊列表，其中包含了用户的SID(安全标识号)以及一系列用户所在组的SID。正如本文后面所介绍的，SQL Server以这些SID为基础授予访问权限。注意，操作系统如何构造访问标记并不重要，SQL Server只使用访问标记中的SID。也就是说，不论你使用SQL Server 2000、SQL Server 7.0、Win2K还是NT进行验证都无关紧要，结果都一样。如果使用SQL Server验证的登录，它最大的好处是很容易通过Enterprise Manager实现，最大的缺点在于SQL Server验证的登录只对特定的服务器有效，也就是说，在一个多服务器的环境中管理比较困难。使用SQL Server进行验证的第二个重要的缺点是，对于每一个数据库，我们必须分别地为它管理权限。如果某个用户对两个数据库有相同的权限要求，我们必须手工设置两个数据库的权限，或者编写脚本设置权限。如果用户数量较少，比如25个以下，而且这些用户的权限变化不是很频繁，SQL Server验证的登录或许适用。但是，在几乎所有的其他情况下(有一些例外情况，例如直接管理安全问题的应用)，这种登录方式的管理负担将超过它的优点。二、Web环境中的验证即使最好的安全策略也常常在一种情形前屈服，这种情形就是在Web应用中使用SQL Server的数据。在这种情形下，进行验证的典型方法是把一组SQL Server登录名称和密码嵌入到Web服务器上运行的程序，比如ASP页面或者CGI脚本;然后，由Web服务器负责验证用户，应用程序则使用它自己的登录帐户(或者是系统管理员sa帐户，或者为了方便起见，使用Sysadmin服务器角色中的登录帐户)为用户访问数据。这种安排有几个缺点，其中最重要的包括：它不具备对用户在服务器上的活动进行审核的能力，完全依赖于Web应用程序实现用户验证，当SQL Server需要限定用户权限时不同的用户之间不易区别。如果你使用的是IIS 5.0或者IIS 4.0，你可以用四种方法验证用户。第一种方法是为每一个网站和每一个虚拟目录创建一个匿名用户的NT帐户。此后，所有应用程序登录SQL Server时都使用该安全环境。我们可以通过授予NT匿名帐户合适的权限，改进审核和验证功能。第二种方法是让所有网站使用Basic验证。此时，只有当用户在对话框中输入了合法的帐户和密码，IIS才会允许他们访问页面。IIS依靠一个NT安全数据库实现登录身份验证，NT安全数据库既可以在本地服务器上，也可以在域控制器上。当用户运行一个访问SQL Server数据库的程序或者脚本时，IIS把用户为了浏览页面而提供的身份信息发送给服务器。如果你使用这种方法，应该记住：在通常情况下，浏览器与服务器之间的密码传送一般是不加密的，对于那些使用Basic验证而安全又很重要的网站，你必须实现SSL(Secure Sockets Layer，安全套接字层)。在客户端只使用IE 5.0、IE 4.0、IE 3.0浏览器的情况下，你可以使用第三种验证方法。你可以在Web网站上和虚拟目录上都启用NT验证。IE会把用户登录计算机的身份信息发送给IIS，当该用户试图登录SQL Server时IIS就使用这些登录信息。使用这种简化的方法时，我们可以在一个远程网站的域上对用户身份进行验证(该远程网站登录到一个与运行着Web服务器的域有着信任关系的域)。最后，如果用户都有个人数字证书，你可以把那些证书映射到本地域的NT帐户上。个人数字证书与服务器数字证书以同样的技术为基础，它证明用户身份标识的合法性，所以可以取代NT的Challenge/Response(质询/回应)验证算法。Netscape和IE都自动在每一个页面请求中把证书信息发送给IIS。IIS提供了一个让管理员把证书映射到NT帐户的工具。因此，我们可以用数字证书取代通常的提供帐户名字和密码的登录过程。由此可见，通过NT帐户验证用户时我们可以使用多种实现方法。即使当用户通过IIS跨越Internet连接SQL Server时，选择仍旧存在。因此，你应该把NT验证作为首选的用户身份验证办法。三、设置全局组构造安全策略的下一个步骤是确定用户应该属于什么组。通常，每一个组织或应用程序的用户都可以按照他们对数据的特定访问要求分成许多类别。例如，会计应用软件的用户一般包括：数据输入操作员，数据输入管理员，报表编写员，会计师，审计员，财务经理等。每一组用户都有不同的数据库访问要求。控制数据访问权限最简单的方法是，对于每一组用户，分别地为它创建一个满足该组用户权限要求的、域内全局有效的组。我们既可以为每一个应用分别创建组，也可以创建适用于整个企业的、涵盖广泛用户类别的组。然而，如果你想要能够精确地了解组成员可以做些什么，为每一个应用程序分别创建组是一种较好的选择。例如，在前面的会计系统中，我们应该创建Data Entry Operators、Accounting Data Entry Managers等组。请记住，为了简化管理，最好为组取一个能够明确表示出作用的名字。除了面向特定应用程序的组之外，我们还需要几个基本组。基本组的成员负责管理服务器。按照习惯，我们可以创建下面这些基本组：SQL Server Administrators，SQL Server Users，SQL Server Denied Users，SQL Server DB Creators，SQL Server Security Operators，SQL Server Database Security Operators，SQL Server Developers，以及 DB_Name Users(其中DB_Name是服务器上一个数据库的名字)。当然，如果必要的话，你还可以创建其他组。创建了全局组之后，接下来我们可以授予它们访问SQL Server的权限。首先为SQL Server Users创建一个NT验证的登录并授予它登录权限，把Master数据库设置为它的默认数据库，但不要授予它访问任何其他数据库的权限，也不要把这个登录帐户设置为任何服务器角色的成员。接着再为SQL Server Denied Users重复这个过程，但这次要拒绝登录访问。在SQL Server中，拒绝权限始终优先。创建了这两个组之后，我们就有了一种允许或拒绝用户访问服务器的便捷方法。为那些没有直接在Sysxlogins系统表里面登记的组授权时，我们不能使用Enterpris Managr，因为Enterprise Manager只允许我们从现有登录名字的列表选择，而不是域内所有组的列表。要访问所有的组，请打开Query […]

龙生 23 Mar 2011

View Details

如何在SQL Server数据库中成批导入数据

导读：在软件项目实施的时候，数据导入一直是项目人员比较头疼的问题。其实，在SQL Server中集成了很多成批导入数据的方法。有些项目实施顾问头疼的问题，在我们数据库管理员眼中，是小菜一碟。现在的重点就是，如何让用户了解这些方法，让数据导入变得轻松一些。下文介绍几种方法，让数据导入不在麻烦。第一种方法：使用Select Into语句若企业数据库都采用的是SQL Server数据库的话，则可以利用Select Into语句来实现数据的导入。Select Into语句，他的作用就是把数据从另外一个数据库中查询出来，然后加入到某个用户指定的表中。　在使用这条语句的时候，需要注意几个方面的内容。一是需要在目的数据库中先建立相关的表。如想把进销存系统数据库(SQLServer)中的产品信息表(Product)导入到ERP系统中的产品信息表(M_Product)中。则前期是在ERP系统的数据库中已经建立了这张产品信息表。二是这种方法只复制表中的数据，而不复制表中的索引。如在进销存系统数据中的产品信息表中，在产品编号、产品种类等字段上建立了索引。则利用Select Into语句把数据复制到ERP系统的表中的时候，只是复制了数据内容的本身，而不会复制索引等信息。三是这条语句使用具有局限性。一般情况下，这只能够在SQL Server数据库中采用。不过，对于SQL Server不同版本的数据库，如2008或者2003，还都是兼容的。若需要导入的对象数据库不是SQL Server的，则需要采用其他的方法。四是采用这条语句的话，在目的表中必须不存在数据。否则的话，目的表中的数据会被清除。也就是说，这个语句不支持表与表数据的合并。在SQL Server中，有一条类似的语句，可以实现这个功能。这条语句就是：Insert Into。他的作用就是把另外一张表中的数据插入到当前表中。若用户想要的时表与表数据的合并，则可以采用这条语句。两者不能够混淆使用，否则的话，很容易导致数据的丢失。五是以上两条语句都支持兼容的不同类型的数据类型。如在原标中，某个字段的数据类型是整数型，但是在目的表中这个字段的数据类型则是浮点型，只要这个两个数据类型本来就兼容的，则在导入的时候，数据库是允许的。第二种方法：利用Excel等中间工具进行控制虽然第一种方法操作起来比较简单，但是其也有一些缺点。如他只支持同一种类型的数据库;不能够对数据进行过多的干预等等。一般情况下，若用户原数据准确度比较高，不需要过多的修改就可以直接拿来用的话，则笔者就已采用第一种方式。但是，若在原数据库中，数据的准确度不是很高，又或者，有很多数据是报废的。总之，需要对原数据库的数据进行整理，才能够使用的情况，笔者不建议先导入进去，再进行更改。笔者在遇到这种情况时，喜欢利用Excle作为中间工具。也就是说，先把数据中原数据库中导到Excle中。有些数据库，如Oracle数据库，他不支持Excle格式。但是，我们可以把它导为CSV格式的文件。这种文件Excle也可以打得开。然后，再在Excle中，对记录进行修改。由于Excle是一个很强的表格处理软件，所以，其数据修改，要比在数据库中直接修改来得方便，来得简单。如可以利用按时间排序等功能，把一些长久不用的记录清楚掉。也可以利用替换等功能，把一些不规范的字符更改掉。这些原来在数据库中比较复杂的任务，在Excle等工具中都可以轻松的完成。等到表中的内容修改无误后，数据库管理员就可以把Excle表格中的文件直接导入到SQL Server数据库中。由于SQL Server与Excel是同一个父母生的，所以，他们之间的兼容性很好。在Sql Server中提供了直接从Excel文件中导入数据的工具。虽然这要借助中间工具导入数据，但是，因为其处理起来方便、直观，所以，笔者在大部分时候都是采用这种方式。第三种方式：使用数据转换服务导入数据数据转换服务是SQL Server数据库中提供的一个非常强大的工具。在SQLServer中，数据转换功能有一个图形用户接口，用户可以在图形界面中导入数据，并对数据进行相应的编辑。另外，数据转换服务还支持COM组件的编程接口。这也就是说，在前台应用程序开发的时候，可以直接调用数据转换服务。让用户通过前台应用系统，而不用在后台数据库系统进行任何的操作，就可以把数据导入数据库系统中去。在前台对数据库系统进行导入，有一个明显的好处，就可以预先对数据的合法性进行检查。如可以利用VB等脚本语言对数据进行检验、净化和一定的转换，以符合目的数据库的需要。如在员工信息表中的婚姻状况字段，在Oracle数据库系统中，可能是用0或者1来表示婚姻状况。0表示未婚，1表示已婚。而在SQL Server数据库中，则利用Y或者N来表示婚姻状况。Y表示已婚，N表示未婚。在导入数据的时候，若直接把Oracle数据库表中的数据导入到SQL Server数据库中，因为婚姻状况这个字段存储的内容类型不同，所以，不能够直接导。遇到这种情况的话，则就可以在导入数据之前，先利用脚本语言对数据类型进行验证。若不符合要求的，则可以通过脚本语言对数据进行一定的转换，把0转换为N，把1转换为Y等等。所以，有时候程序员在开发前台应用程序的时候，若要开发数据导入功能的话，我们都是建议采用这个数据转换服务。不但有现成的接口，而且，还可以对数据进行验证与一定程度的转换。另外，数据转换服务的数据导入效率非常的高。即使通过前台程序调用，其性能也比其他方法在同等条件下，要高一个档次。而且，随着数据量的增加，数据转换服务的优势会越来越明显。不过，在前台应用程序调用数据转换服务的时候，需要注意。数据转换服务提供的COM接口比较复杂，所以，前台程序调用数据转换服务的代码也比较复杂。若再加上一些脚本语言的话，可能处理起来更加的繁琐。故一般只有在大型系统上才会用到这个接口。若数据不多，否则不需要复杂验证与转换的话，利用这个接口是大刀小用，得不偿失。第四种方式：异构数据库之间的导入导出虽然第二种、第三种方式都可以完成异构数据库之间数据的导入导出作业。不过，在SQL Server中，还提供了另外一种解决方案。即直接在SQL Server数据库中连接到其他类型的数据库上，然后采用Select Into等语句实现数据的导入作业。在SQL Server中，提供了两个函数可以帮助我们实现对非SQL Server数据库的连接。这两个函数分别为Opendatesource与Openrowset。他们的功能基本相同，只是在细节上有所差异。如Opendatesource这个函数至能够打开源数据库的表和视图，而不能够对其进行过滤。若用户只想把源表中的部分数据导入到SQL Server数据库的表中，则不能对源表直接进行过滤。过滤的动作需要在SQL Server数据库中进行。而Openrowset这个函数，可以在打开对方数据库的表或者视图的时候，直接利用Where等条件限制语句对记录进新过滤。为此，在实际应用中，还是Openrowset这个函数使用的频率比较高。不过由于其需要用户写复杂的参数，而且，又不能够提供复杂的数据验证功能，所以在实际工作中用的并不是很多。在一些小的应用系统中，偶尔还可以见到其的踪影。在一些大的成熟的商业软件中，很少采用这种方式，对数据进行导入。有时候，选择多了，用户反而不知道如何下手。笔者平时最喜欢采用的是第二种处理方式。他比较直观，而且，可以对数据进行成批的更改与整理。但是，其缺陷就是效率比较低，特别是Excle软件对于处理大量记录的时候，速度比较慢。若这种方式行不通的话，则笔者比较倾向于采用数据转换的处理方式。这个操作起来虽然比较复杂，但是，其可以提供比较复杂的验证，而且可以在图形化的界面中对数据进行修改，同时效率也比较高。学会了上文中介绍的四种方法，数据导入将会变的容易很多，很高兴与大家分享这些方法，希望对大家有用。

龙生 23 Mar 2011

View Details

减少SQL Server数据库死锁的技巧

导读：如果两个用户进程分别锁定了不同的资源，接着又试图锁定对方所锁定的资源，就会产生死锁。此时，SQL Server数据库将自动地选择并中止其中一个进程以解除死锁，使得另外一个进程能够继续处理。系统将回退被中止的事务，并向被回退事务的用户发送错误信息。　大多数设计良好的应用都会在接收到这个错误信息之后重新提交该事务，此时提交成功的可能性是很大的。但是，如果服务器上经常出现这种情况，就会显著地降低服务器性能。由此可知，减少SQL Server数据库死锁是非常有必要的。为避免死锁，设计应用应当遵循一定的原则，包括：让应用每次都以相同的次序访问服务器资源。　在事务期间禁止任何用户输入。应当在事务开始之前收集用户输入。尽量保持事务的短小和简单。如合适的话，为运行事务的用户连接指定尽可能低的隔离级别。[适用于6.5，7.0，2000] 此外，对于SQL Server的死锁问题，下面是几则实践中很有用的小技巧。使用SQL Server Profiler的Create Trace Wizard运行“Identify The Cause of a Deadlock”跟踪来辅助识别死锁问题，它将提供帮助查找数据库产生死锁原因的原始数据。[适用于7.0，2000] 如果无法消除应用中的所有死锁，请确保提供了这样一种程序逻辑：它能够在死锁出现并中止用户事务之后，以随机的时间间隔自动重新提交事务。这里等待时间的随机性非常重要，这是因为另一个竞争的事务也可能在等待，我们不应该让两个竞争的事务等待同样的时间，然后再在同一时间执行它们，这样的话将导致新的死锁。[适用于6.5，7.0，2000] 尽可能地简化所有T-SQL事务。此举将减少各种类型的锁的数量，有助于提高SQL Server应用的整体性能。如果可能的话，应将较复杂的事务分割成多个较简单的事务。[适用于6.5，7.0，2000] 所有条件逻辑、变量赋值以及其他相关的预备设置操作应当在事务之外完成，而不应该放到事务之内。永远不要为了接受用户输入而暂停某个事务，用户输入应当总是在事务之外完成。[适用于6.5，7.0，2000] 在存储过程内封装所有事务，包括BEGIN TRANSACTION和COMMIT TRANSACTION语句。此举从两个方面帮助减少阻塞的锁。首先，它限制了事务运行时客户程序和SQL Server之间的通信，从而使得两者之间的任何消息只能出现于非事务运行时间(减少了事务运行的时间)。其次，由于存储过程强制它所启动的事务或者完成、或者中止，从而防止了用户留下未完成的事务(留下未撤销的锁)。[适用于6.5，7.0，2000] 如果客户程序需要先用一定的时间检查数据，然后可能更新数据，也可能不更新数据，那么最好不要在整个记录检查期间都锁定记录。假设大部分时间都是检查数据而不是更新数据，那么处理这种特殊情况的一种方法就是：先选择出记录(不加UPDATE子句。UPDATE子句将在记录上加上共享锁)，然后把它发送给客户。如果用户只查看记录但从来不更新它，程序可以什么也不做;反过来，如果用户决定更新某个记录，那么他可以通过一个WHERE子句检查当前的数据是否和以前提取的数据相同，然后执行UPDATE。类似地，我们还可以检查记录中的时间标识列(如果它存在的话)。如果数据相同，则执行UPDATE操作;如果记录已经改变，则应用应该提示用户以便用户决定如何处理。虽然这种方法需要编写更多的代码，但它能够减少加锁时间和次数，提高应用的整体性能。[适用于6.5，7.0，2000] 尽可能地为用户连接指定具有最少限制的事务隔离级别，而不是总是使用默认的READ COMMITTED。为了避免由此产生任何其他问题，应当参考不同隔离级别将产生的效果，仔细地分析事务的特性。[适用于6.5，7.0，2000] 使用游标会降低并发性。为避免这一点，如果可以使用只读的游标则应该使用READ_ONLY游标选项，否则如果需要进行更新，尝试使用OPTIMISTIC游标选项以减少加锁。设法避免使用SCROLL_LOCKS游标选项，该选项会增加由于记录锁定引起的问题。[适用于6.5，7.0，2000] 如果用户抱怨说他们不得不等待系统完成事务，则应当检查服务器上的资源锁定是否是导致该问题的原因。进行此类检查时可以使用SQL Server Locks Object: Average Wait Time (ms)，用该计数器来度量各种锁的平均等待时间。如果可以确定一种或几种类型的锁导致了事务延迟，就可以进一步探究是否可以确定具体是哪个事务产生了这种锁。Profiler是进行这类具体分析的最好工具。[适用于7.0，2000] 使用sp_who和sp_who2(SQL Server Books Online没有关于sp_who2的说明，但sp_who2提供了比sp_who更详细的信息)来确定可能是哪些用户阻塞了其他用户。[适用于6.5，7.0，2000] 试试下面的一个或多个有助于避免阻塞锁的建议：1)对于频繁使用的表使用集簇化的索引;2)设法避免一次性影响大量记录的T-SQL语句，特别是INSERT和UPDATE语句;3)设法让UPDATE和DELETE语句使用索引;4)使用嵌套事务时，避免提交和回退冲突。[适用于6.5，7.0，2000] 这就是我要为大家介绍的减少SQL Server数据库死锁的小技巧，学会了这些技巧，在以后的操作中将会更加方便快捷。原文链接：http://database.51cto.com/art/201103/247849.htm

龙生 23 Mar 2011

View Details

« Previous 1 … 392 393 394 … 401 Next »