Session保存在Sql server数据库中实现跨站共享Session（实际应用：单点登录、负载均衡）

前一阵写了Session的保持方式以及Nginx的负载均衡,今天我们把两者结合起来实现一下Session的共享。

1.配置各个服务器上的网站站点：

　　本地配置了一个端口为8787的站点，在另一台机器（192.168.0.160）上配置了一个8383端口的站点。

2.配置nginx负载均衡（上篇文章已经讲过了，再这里不详细说明了）：

#user  nobody;
worker_processes  4; #nginx进程数，建议设置为等于CPU总核心数

#error_log  logs/error.log;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;

#pid        logs/nginx.pid;


events {
    worker_connections  1024; #单个进程最大连接数（最大连接数=连接数*进程数）
}


http {
    include       mime.types;
    default_type  application/octet-stream;

    #log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
    #                  '$status $body_bytes_sent "$http_referer" '
    #                  '"$http_user_agent" "$http_x_forwarded_for"';

    #access_log  logs/access.log  main;

    sendfile        on;
    #tcp_nopush     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    #gzip  on;

    #服务器集群名称为Jq_one
    upstream Jq_one {
    server  192.168.0.160:8383; 
    server  127.0.0.1:8787; 
    }

    server {
        listen       8083;
        server_name  127.0.0.1;

        #charset koi8-r;

        #access_log  logs/host.access.log  main;

        location / {
            root   html;
            index  index.aspx index.html index.htm;
        #指向集群名称为Jq_one
        proxy_pass         http://Jq_one; 
        #设置主机头和客户端真实地址，以便服务器获取客户端真实IP
        proxy_set_header   Host             $host; 
        proxy_set_header   X-Real-IP        $remote_addr; 
        proxy_set_header   X-Forwarded-For  $proxy_add_x_forwarded_for;
        }

    #静态资源缓存设置
    location ~ \.(jpg|png|jpeg|bmp|gif|swf|css)$
        { 
            expires 30d;
            root /nginx-1.9.3/html;#root:
            break;
        }


        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        #location ~ \.php$ {
        #    root           html;
        #    fastcgi_pass   127.0.0.1:9000;
        #    fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
        #    include        fastcgi_params;
        #}

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    }


    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}


    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    server_name  localhost;

    #    ssl_certificate      cert.pem;
    #    ssl_certificate_key  cert.key;

    #    ssl_session_cache    shared:SSL:1m;
    #    ssl_session_timeout  5m;

    #    ssl_ciphers  HIGH:!aNULL:!MD5;
    #    ssl_prefer_server_ciphers  on;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}

}

3.设置站点的session保存到数据库中（不在细说）：

 设置session保存在[ASPNETSession]SQLSERVER数据库中

4.修改ASPNETSession：

把[TempGetAppID]存储过程，把WHERE AppName = @appName这个条件全部注释，不验证这个站点，因为同一浏览器访问不同站点时asp.net默认还是会再建一个

只是后六位不同唯一，（后六位为站点APPID的十六进制）

存储过程的Sql如下：

 CREATE PROCEDURE [dbo].[TempGetAppID]
    @appName    tAppName,
    @appId      int OUTPUT
    AS
    SET @appName = LOWER(@appName)
    SET @appId = NULL

    SELECT @appId = AppId
    FROM [ASPNETSession].dbo.ASPStateTempApplications
  --  WHERE AppName = @appName --Session跨站点共享

    IF @appId IS NULL BEGIN
        BEGIN TRAN        

        SELECT @appId = AppId
        FROM [ASPNETSession].dbo.ASPStateTempApplications WITH (TABLOCKX)
      --  WHERE AppName = @appName
        
        IF @appId IS NULL
        BEGIN
            EXEC GetHashCode @appName, @appId OUTPUT
            
            INSERT [ASPNETSession].dbo.ASPStateTempApplications
            VALUES
            (@appId, @appName)
            
            IF @@ERROR = 2627 
            BEGIN
                DECLARE @dupApp tAppName
            
                SELECT @dupApp = RTRIM(AppName)
                FROM [ASPNETSession].dbo.ASPStateTempApplications 
                WHERE AppId = @appId
                
                RAISERROR('SQL session state fatal error: hash-code collision between applications ''%s'' and ''%s''. Please rename the 1st application to resolve the problem.', 
                            18, 1, @appName, @dupApp)
            END
        END

        COMMIT
    END

    RETURN 0                                           

然后重启Sql server数据库以及各个站点

5.测试结果：

访问http://127.0.0.1:8083/login.aspx?name=12345256，其中name的值保存在session中，通过http://127.0.0.1:8083/default.aspx显示出来。

session数据库中只有一条session记录。