Setting up SqlCacheDependency for your DotnetNuke Installation


This article describes settings that enable DotNetNuke database for advanced database caching with XsltDb

To enable SqlCacheDependency usage you need to do the following
  1. Enable service broker for your DotNetNuke database
    • This is required if you are using notifications like tableName(AField = @SomeValue)
  2. Enable SqlCacheDependency in your web.config
    • This is required if you are using polling model indicating just table name after #<numer>

1. MS SQL Service Broker

Just open database properties, choose options and and change "Broker Enabled" seting to true.

ServiceBroker.PNG

Attention! Changing this option required database lock so you may have troubles enabling service broker on production database. If it fails to change the option you may need to connect to the database exclusively and try again. The following script rollbacks all current transactions and turns service broker on:


ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [Database Name] SET ENABLE_BROKER;
GO

ALTER DATABASE [Database Name] SET MULTI_USER;
GO
In some cases you may need to run the following:


-- For a number of reasons you may need to setup a password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<some password>';
GO

-- If you are using limited MS SQL account run this:
ALTER DATABASE [Database Name] SET TRUSTWORTHY ON;
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <dbUser>;
GO

2. Enable SqlCacheDependency in web.config

At <system.web> node you must create the following configuration:


    <caching>
      <sqlCacheDependency enabled="true" pollTime="500">
        <databases>
          <add name="XsltDbCache" connectionStringName="SiteSqlServer" pollTime="500"/>
        </databases>
      </sqlCacheDependency>
    </caching>
  • XsltDb Uses XsltDbCache node to get a connection string to your DotNetNuke database. By default DotNetNuke connection string has "SiteSqlServer" name. If you changed it - you must use correct one here.
  • pollTime is another important parameter. This is minimal interval for getting notifications from database. So after change has occured in database web server will show expired page up to pollTime milliseconds. So I recomment to from 500 to 3000 ms.

Last edited Feb 1, 2011 at 6:39 AM by findy, version 12

Comments

No comments yet.