SQL資料緩存依賴總結

以前隻聽過SQL server資料緩存依賴，但一直沒使用，由于項目需要，才研究了一番，發現了一個很詭異的問題，竟然是一個操作順序問題導緻的。

SQL server資料緩存依賴有兩種實作模式，輪詢模式，通知模式。

1 輪詢模式實作步驟

此模式需要SQL SERVER 7.0/2000/2005版本以上版本都支援

主要包含以下幾步：

1.1 使用aspnet_regsql指令行或SqlCacheDependencyAdmin來配置連接配接資料庫

ALTER DATABASE <DatabaseName> SET ENABLE_BROKER; //啟用 ServiceBroker，需要在資料庫中執行，或者在資料庫右鍵屬性，選項中修改ENABLE BROKER為true

//注意修改時，需要關閉所有和此資料庫關聯的視窗，否則修改不成功。

報如下錯誤：

Alter failed for Database 'pu'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Database state cannot be changed while other users are using the database 'pu'

ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5070)

aspnet_regsql -S <server> -U sa -P sa -d <database> -ed 啟動資料庫的資料緩存依賴功能

aspnet_regsql -S <server> -U sa -P sa -d <database> -t <table> -et 啟動資料表的資料緩存依賴功能

注意：系統預設不能識别aspnet_regsql，.net 4.0中aspnet_regsql的預設路徑為C:\Windows\Microsoft.NET\Framework\v4.0.30319，需要首先把目前目錄修改為C:\Windows\Microsoft.NET\Framework\v4.0.30319，才可以執行此指令。

1.2 配置檔案

[html] view plain copy print ?

<?xmlversion="1.0"?>
<configuration>
<connectionStrings>
<add name="PubsConnectionString"connectionString="Data Source=10.32.153.165; Initial Catalog=pubs;uid=sa;[email protected];"providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilationdebug="true"targetFramework="4.0"/>
<caching>
<sqlCacheDependencyenabled ="true"pollTime ="1000">
<databases>
<!--name:必需的 String 屬性。要添加到配置集合中的 SqlCacheDependencyDatabase 對象的名稱。
此名稱用作 @ OutputCache 指令上 SqlDependency 屬性的一部分。
pollTime:設定 SqlCacheDependency 輪詢資料庫表以檢視是否發生更改的頻率（以毫秒計算）。這兒是一個測試,是以設為10秒,請加大此值
connectionStringName 必選的 String 特性。為資料庫設定連接配接字元串名稱。在 connectionStrings 元素（ASP.NET 設定架構）配置節中引用連接配接字元串。-->
<addname="Pubs"connectionStringName = "PubsConnectionString"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>

<?xml version="1.0"?>

</connectionStrings>

<system.web>

<!--name:必需的 String 屬性。要添加到配置集合中的 SqlCacheDependencyDatabase 對象的名稱。

此名稱用作 @ OutputCache 指令上 SqlDependency 屬性的一部分。

pollTime:設定 SqlCacheDependency 輪詢資料庫表以檢視是否發生更改的頻率（以毫秒計算）。這兒是一個測試,是以設為10秒,請加大此值

connectionStringName 必選的 String 特性。為資料庫設定連接配接字元串名稱。在 connectionStrings 元素（ASP.NET 設定架構）配置節中引用連接配接字元串。-->

</databases>

</sqlCacheDependency>

</caching>

</system.web>

</configuration>

注意：connectionStrings，和caching兩節的關系，caching節的connectionStringName需要和connectionStrings中的name對應的。

1.3 SqlCacheDependencyAdmin使用

[csharp] view plain copy print ?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Caching;
using System.Configuration;
using System.Data.SqlClient;
namespace TestWebSqlCacheDependency
{
public partial class _Default : System.Web.UI.Page
{
string key = "model_type";
protected void Page_Load(object sender, EventArgs e)
{
TextBox1.Text = test();// test();
}
private string test()
{
//從緩存中取值
string model = null;
if (HttpRuntime.Cache[key] !=null)
{
model = HttpRuntime.Cache[key].ToString();
}
if (model ==null)
{
//取資料
model = getDBValue();
//啟用更改通知
SqlCacheDependencyAdmin.EnableNotifications(
ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString);
//連接配接到 SQL Server 資料庫并為 SqlCacheDependency 更改通知準備資料庫表
SqlCacheDependencyAdmin.EnableTableForNotifications(
ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString,"TableTest");//第二個參數是要監控的表名稱
//制定緩存政策
SqlCacheDependency scd = new SqlCacheDependency("Pubs","TableTest");
//注意此處的Pubs需要要配置檔案的caching節下的databases節下的name對應，而不是随便寫的，目前個人測試的結論就是這樣。第二個參數是要監控的表名稱
//插入緩存
HttpRuntime.Cache.Insert(key, model, scd);
}
return model;
}
private string getDBValue()
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString);
cn.Open();
SqlCommand cd = cn.CreateCommand();
cd.CommandText = " select top 1 TableTest.col2 from TableTest ";
object o = cd.ExecuteScalar();
cn.Close();
if (o != null)
{
return o.ToString();
}
return null;
}
}
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.Caching;

using System.Configuration;

using System.Data.SqlClient;

namespace TestWebSqlCacheDependency

{

public partial class _Default : System.Web.UI.Page

{

string key = "model_type";

protected void Page_Load(object sender, EventArgs e)

{

TextBox1.Text = test();// test();

}

private string test()

{

//從緩存中取值

string model = null;

if (HttpRuntime.Cache[key] != null)

{

model = HttpRuntime.Cache[key].ToString();

}

if (model == null)

{

//取資料

model = getDBValue();

//啟用更改通知

SqlCacheDependencyAdmin.EnableNotifications(

ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString);

//連接配接到 SQL Server 資料庫并為 SqlCacheDependency 更改通知準備資料庫表

SqlCacheDependencyAdmin.EnableTableForNotifications(

ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString, "TableTest");//第二個參數是要監控的表名稱

//制定緩存政策

SqlCacheDependency scd = new SqlCacheDependency("Pubs", "TableTest");

//注意此處的Pubs需要要配置檔案的caching節下的databases節下的name對應，而不是随便寫的，目前個人測試的結論就是這樣。第二個參數是要監控的表名稱

//插入緩存

HttpRuntime.Cache.Insert(key, model, scd);

}

return model;

}

private string getDBValue()

{

SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString);

cn.Open();

SqlCommand cd = cn.CreateCommand();

cd.CommandText = " select top 1 TableTest.col2 from TableTest ";

object o = cd.ExecuteScalar();

cn.Close();

if (o != null)

{

return o.ToString();

}

return null;

}

輪詢模式的實質，就是在資料庫中多了一個表AspNet_SqlCacheTablesForChangeNotification，在需要監視改變的表也多了一個 Trigger，觸發器名稱為：表名_AspNet_SqlCacheNotification_Trigger，在每次表中有資料時，會觸發此觸發器，向 AspNet_SqlCacheTablesForChangeNotification表中插入資料，系統會隔一段時間查詢一次，發現有改變時，就會清空相對應的cache, caching節的pollTime其實就是查詢間隔，也就是說，如果此時間設定的很長，資料庫中的資料修改後，需要很長時間，才能清空對應的cache，最長延時可到達pollTime對應的時間，性能并不是很好。

2 通知模式實作步驟

SQL SERVER 2005（包括SQL SERVER 2005）以上的資料庫才可以使用。

2.1 啟用Service Broker

Select DATABASEpRoPERTYEX('資料庫名稱','IsBrokerEnabled') --檢測是否啟用了 ServiceBroker，1 表示已經啟用 0 表示沒有啟用

ALTER DATABASE <DatabaseName> SET ENABLE_BROKER; //啟用 ServiceBroker，需要在資料庫中執行，或者在資料庫右鍵屬性，選項中修改 ENABLE BROKER為true, 與輪詢模式完全一緻，但不要aspnet_regsql相應的腳本。

2.2 啟動調用SqlDependency.Start，結束時調用SqlDependency.Stop()

最好放進Global中，例如：

[csharp] view plain copy print ?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;
using System.Data.SqlClient;
using System.Web.Caching;
namespace WebTest2
{
public class Global : System.Web.HttpApplication
{
void Application_Start(object sender, EventArgs e)
{
#region SQL2005
SqlDependency.Start(ConnectionString_SQL2005);//推薦将這段代碼加到Global.asax的Application_Start方法中
#endregion
}
void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(ConnectionString_SQL2005);
}
}
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.SessionState;

using System.Data.SqlClient;

using System.Web.Caching;

namespace WebTest2

{

public class Global : System.Web.HttpApplication

{

void Application_Start(object sender, EventArgs e)

{

#region SQL2005

SqlDependency.Start(ConnectionString_SQL2005);//推薦将這段代碼加到Global.asax的Application_Start方法中

#endregion

}

void Application_End(object sender, EventArgs e)

{

SqlDependency.Stop(ConnectionString_SQL2005);

}

調試時注意一定要運作SqlDependency.Start()這句，否則就會出錯，是以測試時不要使用預覽模式。由于vs會啟動WebDev.WebServer40.EXE ，導緻 SqlDependency.Start()可能就沒有運作，是以調試時一定要把VS啟動的WebDev.WebServer40.EXE的前一次模拟服務停止了，使vs重新啟動WebDev.WebServer40.EXE，并運作SqlDependency.Start()。

2.3 使用

[csharp] view plain copy print ?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Caching;
namespace WebTest2
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = HttpRuntime.Cache["dt"]as DataTable;
if (dt == null)
{
using (SqlConnection sqlCon =new SqlConnection(WebConfigHelper.ConnectionString_SQL2005))
{
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlCon;
sqlCmd.CommandText = "select col2 from dbo.TableTest";
dt = new DataTable();
SqlCacheDependency scd = new SqlCacheDependency(sqlCmd);
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(dt);
HttpRuntime.Cache.Insert("dt", dt, scd);
sqlCon.Close();
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System;

using System.Configuration;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.Caching;

namespace WebTest2

{

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

DataTable dt = HttpRuntime.Cache["dt"] as DataTable;

if (dt == null)

{

using (SqlConnection sqlCon = new SqlConnection(WebConfigHelper.ConnectionString_SQL2005))

{

sqlCon.Open();

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = sqlCon;

sqlCmd.CommandText = "select col2 from dbo.TableTest";

dt = new DataTable();

SqlCacheDependency scd = new SqlCacheDependency(sqlCmd);

SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);

sda.Fill(dt);

HttpRuntime.Cache.Insert("dt", dt, scd);

sqlCon.Close();

}

GridView1.DataSource = dt;

GridView1.DataBind();

}

很明顯通知模式，配置檔案不需要任何特殊處理，資料庫中也沒有增加表 AspNet_SqlCacheTablesForChangeNotification和Trigger，性能也比輪詢模式好的多。隻要資料庫中資料有改變，cache中的對應資料就會清空，基本上是實時的，不像輪詢模式由pollTime決定。

通知模式需要注意的時，一定要在建立資料庫之前，把資料庫配置管理中的tcp/ip的那個配置改為able，否則就會出項很詭異的錯誤，能發出通知，但用戶端就是不能收到，無論資料庫中的資料怎樣改變，讀取的資料始終是緩存中的資料，但把tcp/ip的那個配置改為able後，再建立的資料庫就可以，而且好像隻有首次配置才會出現，當再次把able改為diable時在建立資料庫，再改為able時，就不會出現了，很奇怪。整整折騰了三天，才發現此問題！！

另外使用windows身份認證建立的資料庫也有可能導緻此問題，具體原因不明。是以應該使用SQL身份認證建立資料庫，不要使用windows身份認證建立資料庫。很奇怪後來在測試時，使用windows身份認證建立資料庫又可以了，是以具體原因目前不知道。

原因參考

http://www.cnblogs.com/rickie/archive/2006/12/21.html

另外通知模式的查詢語句也有一定的要求，這一定要注意。參考支援的 SELECT 語句

存儲過程不能包含SET NOCOUNT ON，WITH(NOLOCK)，否者資料不能緩存到Cache，每次都是重新讀取資料庫，不管資料有沒有改變。

通知模式的延時估計有幾百毫秒，也就是說，在更新資料庫後，立即讀取Cache資料不是空的，但我測試一秒後就為空了，不會影響實際使用的，但對單元測試會有影響，一定要Thread.sleep(1000),否則單元測試不能通過。

參考文章

SQL資料緩存依賴

SQL Server2005實作資料庫緩存依賴的實作步驟

Sql Server 2005/2008 SqlCacheDependency查詢通知的使用總結

SqlCacheDependency 類

使用 SqlCacheDependency 類在 ASP.NET 中緩存

啟用查詢通知

詭異問題詳細資訊

資料sqlcachedependency輪詢功能配置方法

caching 的 sqlCacheDependency 的 databases 元素（ASP.NET 設定架構）

caching 的 sqlCacheDependency 元素（ASP.NET 設定架構）

caching 的 database 的 add 元素（ASP.NET 設定架構

SQL Server 中的查詢通知 (ADO.NET)

為通知建立查詢

我在這篇文章的末尾分享一下我們最近做項目中關于通知模式中應用的一些經驗：

我們有一個複雜的查詢語句寫在一個存儲過程裡，但是這是通知模式所不支援的sql語句，是以我們HardCode了一段查詢兩張表的簡單查詢語句去達到通知模式中的監控兩張表，然後在注冊在cache中，下面是代碼：

對兩張表監控的SqlCacheDependency的應用 
public const string SqlDependencyCacheSql = "SELECT id FROM dbo.Products;SELECT id FROM dbo.ExpiredProducts;";


public static SqlCacheDependency GetSqlCacheDependency()
        {
            SqlCacheDependency sqlCacheDependency = null;
            using (SqlConnection sqlCon = new SqlConnection(dbConnectionString))
            {
                SqlCommand sqlCmdCache = new SqlCommand(SqlDependencyCacheSql, sqlCon);
                sqlCacheDependency = new SqlCacheDependency(sqlCmdCache);
                try
                {
                    sqlCon.Open();
                    sqlCmdCache.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
            }

            return sqlCacheDependency;
        }

public static Dictionary<string, Product> Products
        {
            get
            {
                if (HttpRuntime.Cache[CacheAllProduct] == null)
                {
                    SqlCacheDependency sqlCacheDependency = BrandPartnerDataAccess.GetSqlCacheDependency();
                    DataTable dt = DataAccess.GetAllProduct();
                    HttpRuntime.Cache.Insert(CacheAllProduct, ConvertToProductsDictionary(dt), sqlCacheDependency);
                }

                return HttpRuntime.Cache[CacheAllProduct] as Dictionary<string, Product>;
            }
        }

SQL資料緩存依賴總結

1 輪詢模式實作步驟

2 通知模式實作步驟

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

GridView終極用法(一)

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述