Tag Archives: SQL Server

Introducing CLR Stored Procedures in SQL Server 2012




This guide demonstrates how to use SQL Server Common Language Runtime (CLR) integration with SQL Server 2012. In this guide, the following two assemblies will be created using C#:

  • MathAsm
  • StoredProceduresAsm


About Common Language Runtime (CLR)

The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Developers use compiled OO languages like C# or Visual Basic .NET to write code and to have the code executed as if it were a T-SQL procedure, function, or trigger. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005, SQL Server 2008, and SQL Server 2012 uses CAS to help secure the managed code and prevent compromise of the operating system or database server.

Enable CLR in SQL Server

In SQL Server 2005, SQL Server 2008, and SQL Server 2012, the Common Language Runtime (CLR) is off by default. In an effort to improve security, Microsoft has turned many features “off by default”. This is a big change from the old policy of turning every feature on so that developers weren’t discouraged from using the feature due to difficulties in getting the feature to work.

EXEC sp_configure 'show advanced options', '1'

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Command(s) completed successfully.

EXEC sp_configure 'clr enabled', '1'

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.


Command(s) completed successfully.

EXEC sp_configure 'show advanced options', '0'

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.


Command(s) completed successfully.

Compile and Build Assembly

In order to develop SQL CLR assemblies for SQL Server, you must have and utilize the .NET Framework 3.5 installed on your development computer. If you do not have the .NET Framework version 3.5 installed on your development computer, you must install it if you want to development SQL CLR assemblies for SQL Server. SQL Server 2005, SQL Server 2008, and SQL Server 2012 support only those assemblies that target the 2.0, 3.0, 3.5, or 4.0 version of the .NET Framework.

As mentioned in the Introduction, this guide will compile and build two assemblies using C# that will be integrated with SQL Server 2012. The source code for both assemblies is presented below.


using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class Math {

    public static SqlDouble Factorial(SqlDouble x) {
        SqlDouble y = 1.0;
        while(x > 1.0) {
            y *= x;
            x -= 1;
        return y;


using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures {

    ///  /// Execute a command and send the resulting reader to the client /// 
    public static void GetVersion() {
        using (SqlConnection connection = new SqlConnection("context connection=true")) {
            SqlCommand command = new SqlCommand("select @@version", connection);
            SqlDataReader r = command.ExecuteReader();

    public const double SALES_TAX = .086;

    public static SqlDouble addTax(SqlDouble originalAmount) {
        SqlDouble taxAmount = originalAmount * SALES_TAX;
        return originalAmount + taxAmount;

Copy the source code files above to your source directory. For example:

C:\> copy C:\Users\SQLServerAdmin\Downloads\*.cs C:\Programming\c#\SQLCLRIntegrationExample\src\
C:\Users\SQLServerAdmin\Downloads\Math.cs C:\Users\SQLServerAdmin\Downloads\StoredProcedures.cs 2 file(s) copied.

There are two methods to compile the C# source code; Microsoft Visual Studio and the C# command-line compiler. This guide uses the C# command-line compiler.

To use the C# command-line compiler, navigate to the appropriate .NET Framework directory. For example:

C:\> cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319

From this directory, compile the two SQL CLR source code files into an assembly (dll).

SET SRC_PATH=C:\Programming\c#\SQLCLRIntegrationExample\src
SET DLL_PATH=C:\Programming\c#\SQLCLRIntegrationExample\assemblies

csc /target:library /out:%DLL_PATH%\Math.dll %SRC_PATH%\Math.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

csc /target:library /out:%DLL_PATH%\StoredProcedures.dll %SRC_PATH%\StoredProcedures.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

 Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\assemblies 03/26/2013 12:13 AM <DIR> . 03/26/2013 12:13 AM <DIR> .. 03/26/2013 12:13 AM 3,584 Math.dll 03/26/2013 12:13 AM 4,096 StoredProcedures.dll 2 File(s) 7,680 bytes 2 Dir(s) 51,610,902,528 bytes free

Grant Permissions for External Access

In order to create an assembly with EXTERNAL_ACCESS (or UNSAFE) permission set, you need extra permissions in the database. This can be achieved by setting the TRUSTWORTHY bit in the database (ALTER DATABASE[DevDB]SET TRUSTWORTHY ON); however, this is not a preferred option as it can cause other undesired side effects. Note that if your assembly does not require resources outside of the database (for example, writing to a file), you do not need to assign an EXTERNAL_ACCESS permission set to the assembly in order to execute the SQL CLR in the database. You only need to assign an EXTERNAL_ACCESS permission set to the assembly when accessing resources outside of the database server.

If your assembly will need access to resources outside of the database, use the following method as the preferred alternative to setting the TRUSTWORTHY bit in the database as explained above.

  1. Create a .NET strong name key file by using the sn.exe tool.
    C:\> SET SN_TOOL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\sn.exe"
    C:\> SET KEY_PATH="C:\Programming\c#\SQLCLRIntegrationExample\keys"
    C:\> %SN_TOOL% -k %KEY_PATH%\extSQLKey.snk
    Copyright (c) Microsoft Corporation. All rights reserved.
    C:\> dir %KEY_PATH%
     Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\keys 03/26/2013 12:29 AM <DIR> . 03/26/2013 12:29 AM <DIR> .. 03/26/2013 12:29 AM 596 extSQLKey.snk 1 File(s) 596 bytes 2 Dir(s) 51,610,288,128 bytes free
  2. In the master database, create a master key (if one does not already exist).
    USE [Master]
    ENCRYPTION BY PASSWORD = 'some_secure_password';
    Command(s) completed successfully.
  3. Still in master, create an asymmetric key.
    FROM FILE = 'C:\Programming\c#\SQLCLRIntegrationExample\keys\extSQLKey.snk';
    Command(s) completed successfully.
  4. Also in the master database, create a login from the asymmetric key.
    Command(s) completed successfully.
  5. Give the login just created EXTERNAL ACCESS ASSEMBLY permission.
    Command(s) completed successfully.
  6. Build your assembly like before but this time sign it with your strong name key.
    cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319
    SET SRC_PATH=C:\Programming\c#\SQLCLRIntegrationExample\src
    SET DLL_PATH=C:\Programming\c#\SQLCLRIntegrationExample\assemblies
    SET KEY_PATH=C:\Programming\c#\SQLCLRIntegrationExample\keys
    csc /target:library /keyfile:%KEY_PATH%\extSQLKey.snk /out:%DLL_PATH%\StoredProcedures.dll %SRC_PATH%\StoredProcedures.cs
    Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.
    csc /target:library /keyfile:%KEY_PATH%\extSQLKey.snk /out:%DLL_PATH%\Math.dll %SRC_PATH%\Math.cs
    Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.
     Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\assemblies 03/26/2013 12:13 AM <DIR> . 03/26/2013 12:13 AM <DIR> .. 03/26/2013 01:00 AM 3,584 Math.dll 03/26/2013 01:00 AM 4,096 StoredProcedures.dll 2 File(s) 7,680 bytes 2 Dir(s) 51,609,612,288 bytes free
  7. The assembly is now ready to be deployed. The assembly is signed with a strong name key, and the strong name key has an asymmetric key created from it and there is a login created from that symmetric key with the necessary permission set.


Create Assemblies in SQL Server

With the assemblies built, the next step is to create the assembly in a SQL Server database along with the external procedure and/or function wrappers to run the assembly.


Use [DevDB]

CREATE ASSEMBLY MathAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\Math.dll'

    EXTERNAL NAME MathAsm.Math.Factorial;

Command(s) completed successfully.


Use [DevDB]

CREATE ASSEMBLY StoredProceduresAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\StoredProcedures.dll'

CREATE PROCEDURE GetVersion AS EXTERNAL NAME StoredProceduresAsm.StoredProcedures.GetVersion;

CREATE FUNCTION addTax(@originalAmount FLOAT)
    EXTERNAL NAME StoredProceduresAsm.StoredProcedures.addTax;

Command(s) completed successfully.

Note that the external reference for creating the procedure and wrapper functions is in the form:

[EXTERNAL NAME] assembly_name.class_name.method_name

Run Example Assemblies in SQL Server

With the assemblies and procedures / functions wrappers created in SQL Server, run the following T-SQL to execute the assemblies.

SELECT [DevDB].[dbo].[Factorial](5) AS "Factorial"

Factorial ----------------- 120

EXEC GetVersion

(No column name) ----------------------------- Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Business Intelligence Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

SELECT [DevDB].[dbo].[addTax](10.00) AS "Original + Tax"

Original + Tax ----------------------------- 10.86

Drop Assemblies from SQL Server

The following T-SQL can be used to drop the previously created objects in the example Class.


Use [DevDB]

DROP FUNCTION [Factorial];


Command(s) completed successfully.


Use [DevDB]



DROP ASSEMBLY [StoredProceduresAsm];

Command(s) completed successfully.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff’s other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor’s degree in Computer Science and Mathematics.


Configure SQL Server Express to allow remote tcp/ip connections on port 1433

The following article explains how to allow SQL Server Express to accept remote connections over TCP/IP for port 1433. By default, when SQL Server Express is installed it gerates a random port to listen on. In addition, SQL Server Express only listens for connection on localhost. Using the SQL Server Configuration Manager, you will need to tell SQL Server Express to use port 1433.


To allow SQL Server Express to accept remote connections, please follow these steps:

1) Log into your server through Remote Desktop Connection (instructions for connecting to your server through RDC can be found here).

2) Click Start, Programs, Microsoft SQL Server 2005/2008/2012 and select SQL Server Configuration Manager.


3) Select SQL Server Network Configuration


4) Double click on Protocols for SQLEXPRESS


5) Right click TCP/IP and select Properties

6) Scroll down to IPAll make sure TCP Dynamic Ports is blank and that TCP Port is set to 1433.

7) Click OK

8) Make sure that port: 1433 is enable on your VDS firewall (instructions for enabling firewall ports can be found here).

9) Mixed mode authentication must also be enabled for remote connections (instructions for enabling firewall ports can be found here).

10) Make sure that the SQL Browser is enabled and running.



sql server 判断数据库,表,列,视图是否存在

1 判断数据库是否存在
if exists (select * from sys.databases where name = ‘数据库名’)
drop database [数据库名]

2 判断表是否存在
if exists (select * from sysobjects where id = object_id(N'[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [表名]

3 判断存储过程是否存在
if exists (select * from sysobjects where id = object_id(N'[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [存储过程名]
4 判断临时表是否存在
if object_id(‘tempdb..#临时表名’) is not null
drop table #临时表名

5 判断视图是否存在

PRINT ‘存在’
PRINT ‘不存在’
6 判断函数是否存在
— 判断要创建的函数名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function [dbo].[函数名]

7 获取用户创建的对象信息

SELECT [name],[id],crdate FROM sysobjects where xtype=’U’
xtype 的表示参数类型,通常包括如下这些
C = CHECK 约束
D = 默认值或 DEFAULT 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束(类型是 K)
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束(类型是 K)
V = 视图
X = 扩展存储过程

8 判断列是否存在
if exists(select * from syscolumns where id=object_id(‘表名’) and name=’列名’)
alter table 表名 drop column 列名

9 判断列是否自增列
if columnproperty(object_id(‘table’),’col’,’IsIdentity’)=1
print ‘自增列’
print ‘不是自增列’

SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(‘表名’) AND is_identity=1

10 判断表中是否存在索引
if exists(select * from sysindexes where id=object_id(‘表名’) and name=’索引名’)
print ‘存在’
print ‘不存在’

11 查看数据库中对象

SELECT * FROM sys.sysobjects WHERE name=’对象名’ SELECT * FROM sys.sysobjects WHERE name=’对象名’

How to setup linked servers for SQL Server and Oracle 64 bit client

Problem We have two SQL 2005 64-bit clusters in our organization:  a four-node production cluster and a two-node dev/test cluster.  We recently migrated a database from SQL 2000 that relied on an Oracle linked server for some of it’s computations.  No big deal, right?  Install the Oracle client software and create the linked server just like in SQL 2000 right?  Be sure to repeat the process on both nodes for failover-sake, right?  Wrong.  In the case of 64-bit SQL instances we have a few extra steps we need to take to get an Oracle linked server configured and working properly.

Solution 64-bit computing is coming of age.  In most cases, our installation and configuration processes do not change from 32-bit to 64-bit: setting up logins, creating SQL Agent jobs; nothing inherently different there.  Inconsistencies do exist however.  Take for example linked servers – more specifically linked servers to Oracle databases.  In 32-bit environments we simply install the Oracle client software, create the linked server, add remote logins and move on to the next project.  In the 64-bit world the process is slightly more difficult – and in the case of one critical step it impacts any existing users on the instance!

Process Creating Oracle Linked Server on a 64-bit SQL Instance:

  • Install Oracle 10g Release 2 64-bit client software (available from Oracle website)
  • Install Oracle 10g Release 2 64-bit ODAC software (available from Oracle website)
  • Restart SQL services
  • Configure OraOLEDB.Oracle provider
  • Create linked server
  • Add remote logins for linked server

The complete process is presented below:

Step One:  Installation of the Oracle 10g Release 2 64-bit client software

I will leave it up to you to secure the software by either download from their site or media available via any licensing agreements you may have with Oracle.  The important steps are outlined below.  You only need the barest install of the client.  Simply go ahead and select the InstantClient option from the opening dialog form and click Next.

The following screens simply require you to click Next when enabled.  These are in place to present the internal checks and summaries of work to be completed once you accept the install parameters.  Proceed through the screens, click Install, and then Finish once the installation is complete.

Step Two:  Installation Oracle 10g Release 2 64-bit ODAC software

Once you have an installed 64-bit Oracle Home you can proceed with installing the ODAC drivers for a 64-bit environment.  The OraOLEDB.Oracle driver installed in this step is what you need to set up your linked server.  You’ll find the setup.exe for this installation located in the unzipped Install folder.  I strongly suggest you review your previous installation of the client software via selecting the Installed Products button.  You want to verify that you have a valid 64-bit Oracle Home that you’ll be installing against during this process.  We would not be DBAs if we were not overly cautious.  It’s quite Darwinian:  the brash and risky don’t last long as Database Administrators.

If you accepted the defaults during the client installation, your “Installed Products” Inventory should appear as follows.  If so, then close the dialog and click Next to continue.  If not, then I suggest you revisit your installation of the client software, possibly de-installing and performing all steps presented previously once again.

We will be installing the OraOLEDB.Oracle driver that is included in the Oracle Data Access Components option below.  Select Next to continue.

Using the drop-down box provided, select the existing Oracle Home you just verified was created as a part of your 64-bit Oracle client installation.  The Path will automatically change accordingly.  Continue by clicking Next.

You will be presented with the following sub-components.  I’ve selected all three (you never know when these items may be useful and they are extremely lightweight) though you only really need to install the first two items.

If you do decide to install the Oracle Services for Microsoft Transaction Server component, you’ll be prompted for the port number to use.  The default is 2030.

Click Next and you’ll be presented with the familiar summary screen, listing all components to be installed.  Review, make changes if necessary and then click Install.  Once completed we can move back to familiar territory:  SQL Server Management Studio.

Step Three:  Restart SQL Service

This is the most unfortunate step in the entire process.  This is usually the last thing a DBA wants to do.  It is the option of last resort whenever there is an issue on a SQL Server instance.  However, it is necessary in this case, so be sure to follow your corporate procedures for downtime notifications and process.  That being said, let’s move past this horrid step!

Step Four:  Configure OraOLEDB.Oracle provider

I confess.  I was not aware of this process existing until I had to do this the first time.  In Object Explorer, navigate down the tree of the SQL instance we’ve been working with until you’re able to view the list of Providers under Linked Servers.  Select OraOLEDB.Oracle and right-click.

SQL Server Management Studio.

The Provider Options dialog box appears.  Enable the “Allow inprocess” option and close the dialog.  Now ask yourself:  “Self, what did I just do?  Some Internet Guy said click this and it will work.”  Let’s take a quick look at exactly what enabling this option does for a provider.  By default, OLE DB providers are instantiated outside of the SQL Server process.  While this protects SQL from any OLE DB errors, Microsoft SQL Server requires that the OLE DB provider run in-process for handling BLOB-ish data types (text and images).

Step 5:  Create Linked Server and Add Logins

Finally, something we’re all familiar with.  The T-SQL is simple, and identical to the 32-bit platform.

--Create the Oracle Linked Server:  EXEC sp_addlinkedserver @server, @srvproduct, @provider, @datasrc
--For example: If I want to create a linked server called LK_TIPS to the ORATIPS  --database on the SRVORA1 server, listening on the default port of 1521 my query  --would look like this: EXEC sp_addlinkedserver 'LK_TIPS', 'Oracle', 'OraOLEDB.Oracle', 'SRVORA1:1521/ORATIPS'

A quick explanation about the @datasrc parameter.  Since our environment is clustered I do not like relying on an Oracle TNSname.ora file for our connection information.  It is far cleaner to specify the data source in terms of the SERVER:PORT/DB_NAME format.

As for creating an associated login the format is:

–Create the Remote Login for the Oracle Linked Server: EXEC sp_addlinkedsrvlogin @rmtsrvname, @useself, @rmtuser, @rmtpassword

Briefly, the @useself parameter determines whether the current user in connects to the linked server source under its current context or via impersonation.  By setting the value to FALSE you’re stating that you wish to connect to the linked server with a supplied login/password combination that is being identified in the @rmtuser and @rmtpassword parameters respectively.  A value of TRUE would suggest that a login will use it’s own (current) credentials to connect to the linked server.

Creating a login for the LK_TIPS linked server we just created with a login of ‘sa’ and a password set to ‘password’ would look like this:

EXEC sp_addlinkedsrvlogin ‘LK_TIPS’, ‘FALSE’, ‘sa’, ‘password’

Before you get any crazy ideas, I don’t suggest using sa/password as a combination for any system!


Next Steps

  • Tweak T-SQL code provided to fit your environment
  • If this is being configured in a cluster, be sure to install the Oracle client and ODAC components identically on all nodes in the cluster.
  • Review related tips on linked servers on MSSQLTips.com.




我们都听说过RAID,也经常作为SQL DBA、开发人员或构架师在工作中讨论RAID。但是,其实我们很多人都对RAID的原理,等级,以及RAID是如何影响SQL Server性能并不甚了解。











很多人都明白IOPS是Input Output Operations per Second的缩写,但是将这个定义转换为实际的概念对于某些人就有点难了。





磁盘会有一些物理限制会限制磁盘能达到的IOP级别。这个限制是磁道寻址时间(seek time)和旋转延迟(rotational latency)。











HP 300GB 15k SAS drive(200刀)
转速 15000
平均磁道寻址时间 2.9ms
平均旋转延迟 1.83ms



IOPS = 1/(2.9ms + 1.83ms)    = 1/(4.73ms)     = 1/(0.00473)    = 211 IOPS





HP 300GB 7200 SATA drive(100刀)
转速 7200
平均磁道寻址时间 10ms
平均旋转延迟 2.1ms



IOPS = 1/(10ms + 2.1ms)    =  1/(12.1ms)         =  1/(0.0121)         = 82 IOPS




此外,文档显示如果你使用磁盘到其IOPS峰值,会产生请求队列而造成延迟(一个在SQL Server中非常邪恶的词汇,像躲避瘟疫一样躲避它)。


现在我已经知道了单独一个磁盘可以达到的IOPS数字,那么下一件事就是要满足生产环境下的SQL Server实例需要多少IOPS?

我获取这些数据仅仅通过在生产环境下查看PerfMon工具的physical disk: Disk Transfers/Sec 计数器。



驱动 平均IOPS 最大IOPS
数据和索引 2313 16,164
日志 81.5 1,127
TempDB 141 2,838




如果我们有100个300GB 15k SAS驱动器,我们不仅获得了30TB的存储量,还获得了16800的IOPS。

如果我们使用前面例子中较慢的磁盘,为了达到16800 IOPS,我们需要205块这样的驱动器,这使得我们需要比使用快速磁盘花更多的钱($20,000 vs $20,500),听上去很讽刺,不是吗?



现在,我们需要一堆磁盘来满足我们的速度或是容量需求,所以我们需要某种机制来将工作负载加到多个磁盘,实现这个目的的主要手段就是RAID。         RAID代表”Redundant Array <of> Inexpensive Disks”(译者注:这是最开始的定义,后来行业标准将I改为Independant,难道这是因为Inexpensive这个词妨碍了他们收取更多的钱?),RAID提供了将一堆磁盘连接起来使得逻辑上变为1个的方法。





RAID只为了两个目的:1)通过提高IOPS提高性能  2)提供容错。更高的容错性意味着更低的磁盘性能,同样,高性能方案也会降低容错性。






第一个,也是最基本的RAID级别是RAID 0.RAID 0强调为了解决IO的限制而将数据写入到磁盘阵列中。如果IO希望写100MB的数据,RAID0会将100MB数据写入到磁盘阵列的每个磁盘中。







RAID1也被称为”镜像”,因为其通过一个镜像磁盘来保证容错性。在镜像集中的每个磁盘都会有一个镜像磁盘,RAID 1写入的每一笔数据都会分别在两个磁盘中各写一份。这意味着任何一个磁盘除了问题,另一个磁盘就会顶上。用户的角度来看并不知道出现了磁盘崩溃。

RAID 1需要付出写入时的性能代价。每个写入IOP需要运行两次,但是对于读来说却会提升性能,因为RAID控制器对于大量数据请求会从两个磁盘中读取。



RAID 5也被称为”Striping With Parity)”,这种方式既可以通过磁盘分割(Striping raid0)来提高性能,也可以通过奇偶性(Parity)来提供容错,当一个磁盘崩溃后,奇偶数据可以通过计算重建丢失的数据。



  •     读取原始数据(1 iop)
  •     读取当前奇偶数据(1 iop)
  •     比较当前数据和新写入请求
  •     基于数据差异计算新的奇偶值
  •     写入新数据(1 iop)
  •     写入新的奇偶值(1 iop)

RAID 1+0

    RAID 1+0 和其名字所示那样,融合了RAID 0(磁盘分割)和RAID1(镜像)。这种方式也被称为:分割镜像。

RAID 1+0 由于将数据分割到多个磁盘中使得并且不像RAID5那样有奇偶效验码,所以写入速度非常快。


而对于RAID 1+0 存储的代价等同于RAID1 (镜像),在RAID1+0中只有一半的磁盘空间可以用于存储数据。


RAID 0+1

RAID 0+1 和RAID1 +0 是很像,它们都是通过磁盘分割和镜像来实现目的。他们的区别更加学术化,这里我们假设他们一样。

RAID 0+1和 RAID 1+0所付出的代价是一样的。



还有一些其它不常见的非标准RAID层级,RAID 2,3,4,6和RAID DP都和RAID5类似,他们都是通过分割和某种奇偶校验来提供性能上和容错。这些类似RAID 5的RAID层级的区别仅仅是它们如何写入奇偶数据。它们之中有些是通过保留一个磁盘来存储奇偶数据,还有一些是将奇偶数据分布到多个磁盘当中等等。如果需要,你可以去做这些研究,但对于我来说,我都称它们为”RAID 5”

还有一个值得讨论的非标准的RAID级别是RAID DP,DP的是”Dual Parity”的缩写,这和RAID 5很像但其将奇偶数据写入两次,这对于写入来说代价高昂,写入代价被提高到了6(每一次IO写请求需要6 IOPS)





RAID Level Fault Tolerance Read Performance Write Performance RAID Write Penalty Cost
0 None Good Excellent 1 Excellent
1 Good Good Good 2 Fair
5 Fair Good Poor 4 Good
1+0 Excellent Excellent Excellent 2 Poor
DP Good Good Terrible 6 Good




SQL Server文件 RAID级别
操作系统和SQL二进制文件 RAID 1
数据和索引 RAID 1+0 (如果预算不允许可以使用RAID 5)
日志 RAID 1+0
TempDB RAID 1+0
备份 RAID 5











缓存对于SQL Server来说非常重要,尤其是对于写来说。无论是何种RAID级别,都没有读取性能上的代价,所有的RAID层级都提高了读取数据的速度。而写入才是RAID的代价,你可以通过RAID缓存来缓存所有的写入操作,这极大的提高了写入性能。通常来说,有缓存的RAID控制器都带有电池,这使得即使断电,缓存的数据也不会丢失。

记住,SQL Server本身非常善于缓存读取,所以使用昂贵的RAID控制器中的缓存来缓存读取并没有什么意义。



还有一个值得考虑的因素是虚拟化,无论你喜欢与否,我们已经步入了虚拟化的世界。在VMWare环境下部署生产环境下的SQL Server实例变得越来越普遍。



很明显,我们还有一些信息没有讨论到,RAID对于SQL Server性能和容错的重要性不言而喻。

我希望本篇文章能够帮你理解RAID是如何影响你的SQL Server的性能。作为一个DBA或是数据库构架师来说,你必须明白当前RAID配置有着怎样的性能和容错性。



MSDN:     http://msdn.microsoft.com/en-us/library/ms190764.aspx

TechNet:     http://technet.microsoft.com/en-us/library/cc966534.aspx

Ed Whalen – PerfTuning.com http://www.perftuning.com/files/pdf/RAID1.pdf