"SQLServer复制需要有实际的服务器名称才能连接到服务器,请指定实际的服务器名"

叁歲伎倆 2022-08-21 02:18 129阅读 0赞

最近在学习 SQL SERVER的高级复制技术 的时候，遇到一个小问题，就是用本地SQL SERVER连接服务器的数据库时，在查看复制功能的发布服务器时，连接不上，弹出一个错误提示框架，如下：  
![29151695_1402544566aDtA.png][]  
原来在自己本地的实例是一切操作正常的，一连接到远程服务器时就出现这个问题，后来在网上找了下，发现有这两种情况：  
一、自己本身的计算机名称修改过，导致与服务器不一致。  
二、在直接用IP地址连接远程服务器时，如果不用别名来连接的话，就会出现上面这个错误提示框。  
  
第一种情况下：  
在SQL SERVER里面执行下面语句：

<table style="word-wrap:break-word; color:rgb(102,102,102); border:1px solid silver; width:1139px; font-size:12px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; min-height:inherit!important"> 
 <tbody style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important"> 
  <tr style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important"> 
   <td style="word-wrap:break-word; margin-top:0px; margin-right:0px; margin-left:0px; padding:3px; border:1px solid silver; word-break:break-all; letter-spacing:0.1px; border-collapse:collapse; margin-bottom:0px!important; width:auto!important; overflow:visible!important; line-height:1.1em!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; outline:rgb(0,0,0)!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; min-height:inherit!important"> 
    <div style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px!important; position:relative!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important"> 
     <div style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px 1em 0px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important; background-color:rgb(244,244,244)!important">
       use master 
     </div> 
     <div style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px 1em 0px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important">
       go 
     </div> 
     <div style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px 1em 0px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important; background-color:rgb(244,244,244)!important">
       select&nbsp;@@servername; 
     </div> 
     <div style="word-wrap:break-word; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; margin:0px!important; outline:rgb(0,0,0)!important; overflow:visible!important; padding:0px 1em 0px 0px!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:inherit!important">
       select&nbsp;serverproperty('servername') 
      <br style="word-wrap:break-word"> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

使用上面的语句查询服务器的名称和实际计算机的名称，如果两者不一致，就需要修改。  
不一致就执行下面的语句：

sp_dropserver 'old_server_name' go sp_addserver 'current_computer_name','local'

修改完后，重启SQL SERVER服务。  
  
第二种情况下：  
为远程服务器增加“别名”，使用别名代替IP地址来连接远程数据库服务器。（我自己用的是SQL SERVER2012）  
打开SQL SERVER2012的配置管理器，定位到“别名”服务选项，如下：  
![29151695_1402545250ziwJ.png][]  
  
右键--->新建别名：  
![29151695_140254535745x9.png][]  
这里的这个"别名"就用服务器名称，就是用上面的SQL 语句查询出来的名称：（select  @@servername;），IP地址就是这台服务器的访问地址。  
  
  
接着在本机的hosts添加相应的IP地址和别名：  
C:\\Windows\\System32\\drivers\\etc  
  
hosts:  
  
IP地址 别名  
  
例如：  
  
172.168.1.15 sniper  
  
修改完，保存.  
  
  
都修改完后， 重启一下SQL SERVER服务。  
  
再次连接的时候，使用“别名”代替IP地址去连接服务器即可：  
![29151695_1402545566SSwD.png][]  
  
至此，问题解决！

[29151695_1402544566aDtA.png]: http://blog.itpub.net/attachment/201406/12/29151695_1402544566aDtA.png
[29151695_1402545250ziwJ.png]: http://blog.itpub.net/attachment/201406/12/29151695_1402545250ziwJ.png
[29151695_140254535745x9.png]: http://blog.itpub.net/attachment/201406/12/29151695_140254535745x9.png
[29151695_1402545566SSwD.png]: http://blog.itpub.net/attachment/201406/12/29151695_1402545566SSwD.png