none
Sql Server2008R2 创建Mirror后有错误信息 RRS feed

  • 问题

  • 使用3台SqlServer R2 创建自动化的Mirror解决方案,但创建后在主node上进行故障转移后,看到状态正常,但客户端无法正常连接SqlServer,检查Windows日志及Sql的日志出现大量错误,已多次在全新的环境下操作,均出现此错误。

    环境:

    Sql Server node:

    Master:192.168.30.88

    Mirror:192.168.30.87

    Winess:192.168.30.80

    除WinnessOS版本为Windows2008R2外,另外2台为08R2Sp1

    Sql Server网络配置启用Tcp/ip(1433)及name pipe

    Windows应用日志:

    日志名称:          Application
    来源:            MSSQLSERVER
    日期:            2013/1/1 0:12:01
    事件 ID:         18456
    任务类别:          登录
    级别:            信息
    关键字:           经典,审核失败
    用户:            DEMO\SQLNODE87$
    计算机:           sqlnode88.demo.ibmsbdc.local
    描述:
    用户 'DEMO\SQLNODE87$' 登录失败。 原因: 无法打开明确指定的数据库。 [客户端: 192.168.30.87]
    事件 Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQLSERVER" />
        <EventID Qualifiers="49152">18456</EventID>
        <Level>0</Level>
        <Task>4</Task>
        <Keywords>0x90000000000000</Keywords>
        <TimeCreated SystemTime="2012-12-31T16:12:01.000000000Z" />
        <EventRecordID>149705</EventRecordID>
        <Channel>Application</Channel>
        <Computer>sqlnode88.demo.ibmsbdc.local</Computer>
        <Security UserID="S-1-5-21-733656226-4174739878-1904946613-1110" />
      </System>
      <EventData>
        <Data>DEMO\SQLNODE87$</Data>
        <Data> 原因: 无法打开明确指定的数据库。</Data>
        <Data> [客户端: 192.168.30.87]</Data>
        <Binary>184800000E0000000A000000530051004C004E004F0044004500380038000000070000006D00610073007400650072000000</Binary>
      </EventData>
    </Event>

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Sql Server日志:

    2013-01-01 00:00:31.27 登录          Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. 原因: 无法打开明确指定的数据库。 [客户端: 192.168.30.87]
    2013-01-01 00:00:31.59 登录          错误: 18456,严重性: 14,状态: 38。
    2013-01-01 00:00:31.59 登录          Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. 原因: 无法打开明确指定的数据库。 [客户端: 192.168.30.87]


    3333

    2012年12月31日 16:15

全部回复

  • How did you set connection string in apps? Do apps use .net version2 and above?
    2012年12月31日 19:00
  • My ENV have three Servers ,OS are Windows2008R2,The .net FarmeWork 3.5.1 & IIS function are enable. The connection string auto create by Citrix Script .

    After install Citrix XenDesktop I have reconfig the Citrix DB config.The progress and script as blow:

    1.
    $controllers = Get-BrokerController | %{$_.DNSName}
    foreach ($controller in $controllers) {
        Write-Host "Disconnect controller $controller ..."
        Set-ConfigDBConnection –DBConnection $null –AdminAddress $controller
        Set-HypDBConnection –DBConnection $null –AdminAddress $controller
        Set-AcctDBConnection –DBConnection $null –AdminAddress $controller
        Set-ProvDBConnection –DBConnection $null –AdminAddress $controller
        Set-PvsVmDBConnection –DBConnection $null –AdminAddress $controller
        Set-BrokerDBConnection –DBConnection $null –AdminAddress $controller
    }


    2.
    $mirrorServer = 'sqlnode87.demo.ibmsbdc.local'
    $dbName = 'CitrixXenDesktopDB'

    foreach ($controller in $controllers) {
        Write-Host "Make login for $controller on $mirrorServer ..."
        Get-BrokerDBSchema –DatabaseName $dbName –ScriptType Login –AdminAddress $controller > add-login.sql
        sqlcmd -S $mirrorServer -Q ':r add-login.sql'
    }


    3.

    ‘Server=sqlnode88.demo.ibmsbdc.local; Failover Partner=Server=sqlnode87.demo.ibmsbdc.local; Initial Catalog=CitrixXenDesktopDB; Integrated Security=True; Network=dbmssocn’

    $cs =‘Server=sqlnode88.demo.ibmsbdc.local; Failover Partner=Server=sqlnode87.demo.ibmsbdc.local; Initial Catalog=CitrixXenDesktopDB; Integrated Security=True; Network=dbmssocn’


    foreach ($controller in $controllers) {
        Write-Host "Reconnect controller $controller ..."
        Set-ConfigDBConnection –DBConnection $cs –AdminAddress $controller
        Set-HypDBConnection –DBConnection $cs –AdminAddress $controller
        Set-AcctDBConnection –DBConnection $cs –AdminAddress $controller
        Set-ProvDBConnection –DBConnection $cs –AdminAddress $controller
        Set-PvsVmDBConnection –DBConnection $cs –AdminAddress $controller
        Set-BrokerDBConnection –DBConnection $cs –AdminAddress $controller
    }

     

    Result:

    PS C:\Users\Administrator.DEMO> $controllers = Get-BrokerController | %{$_.DNSName}
    PS C:\Users\Administrator.DEMO> foreach ($controller in $controllers) {
    >>     Write-Host "Disconnect controller $controller ..."
    >>     Set-ConfigDBConnection –DBConnection $null –AdminAddress $controller
    >>     Set-HypDBConnection –DBConnection $null –AdminAddress $controller
    >>     Set-AcctDBConnection –DBConnection $null –AdminAddress $controller
    >>     Set-ProvDBConnection –DBConnection $null –AdminAddress $controller
    >>     Set-PvsVmDBConnection –DBConnection $null –AdminAddress $controller
    >>     Set-BrokerDBConnection –DBConnection $null –AdminAddress $controller
    >> }
    >>
    Disconnect controller SQLNODE88.demo.ibmsbdc.local ...
    DBUnconfigured
    DBUnconfigured
    DBUnconfigured
    DBUnconfigured
    DBUnconfigured
    DBUnconfigured
    PS C:\Users\Administrator.DEMO> $mirrorServer = 'sqlnode87.demo.ibmsbdc.local'
    PS C:\Users\Administrator.DEMO> $dbName = 'CitrixXenDesktopDB'
    PS C:\Users\Administrator.DEMO>
    PS C:\Users\Administrator.DEMO> foreach ($controller in $controllers) {
    >>     Write-Host "Make login for $controller on $mirrorServer ..."
    >>     Get-BrokerDBSchema –DatabaseName $dbName –ScriptType Login –AdminAddress $controller > add-login.sql
    >>     sqlcmd -S $mirrorServer -Q ':r add-login.sql'
    >> }
    >>
    Make login for SQLNODE88.demo.ibmsbdc.local on sqlnode87.demo.ibmsbdc.local ...
    Login for controller [DEMO\SQLNODE88$] created
    PS C:\Users\Administrator.DEMO>
    PS C:\Users\Administrator.DEMO> $cs =‘Server=sqlnode88.demo.ibmsbdc.local; Failover Partner=Server=sqlnode87.demo.ibmsb
    dc.local; Initial Catalog=CitrixXenDesktopDB; Integrated Security=True; Network=dbmssocn’
    PS C:\Users\Administrator.DEMO>
    PS C:\Users\Administrator.DEMO>
    PS C:\Users\Administrator.DEMO> foreach ($controller in $controllers) {
    >>     Write-Host "Reconnect controller $controller ..."
    >>     Set-ConfigDBConnection –DBConnection $cs –AdminAddress $controller
    >>     Set-HypDBConnection –DBConnection $cs –AdminAddress $controller
    >>     Set-AcctDBConnection –DBConnection $cs –AdminAddress $controller
    >>     Set-ProvDBConnection –DBConnection $cs –AdminAddress $controller
    >>     Set-PvsVmDBConnection –DBConnection $cs –AdminAddress $controller
    >>     Set-BrokerDBConnection –DBConnection $cs –AdminAddress $controller
    >> }
    >>
    Reconnect controller SQLNODE88.demo.ibmsbdc.local ...
    OK
    OK
    OK
    OK
    OK
    OK
    PS C:\Users\Administrator.DEMO>


    Ethan Liang

    2013年1月1日 4:18
  • You may need work with citrix on config, that's what we did and took sometime for citrix to figure it out.
    2013年1月1日 5:13
  • 可能是你的客户端程序不支持Mirror。

    想不想时已是想,不如不想都不想。

    2013年1月1日 7:56
    版主
  • 谢谢你的回复,Citrix在他们的解决方案推荐使用Sql Server Mirror的方案来解决虚拟桌面或虚拟应用的高可用问题的,因此是支持的。

    Ethan Liang

    2013年1月1日 8:15
  • That's why you should work with citrix on this issue.
    2013年1月1日 16:14
  • I try open a case with this problem.

    Thanks.


    Ethan Liang

    2013年1月2日 3:11