原因
服务器名修改后,sqlserver表中保存的服务器名并不会跟着修改,需要手动修改
方法
用sa账号登录后可以对数据库操作,但是不能使用发布订阅等功能
查看原服务器名和新服务器名,如果相同,可以正常登录
select @@servername
select serverproperty('servername')
手动将原服务器名修改为新服务器名,成功后重启sqlserver服务,重复步骤2
--将两者协调一致, 再重启 SQL Server 服务
if serverproperty('servername') <> @@servername
begin
declare @server sysname
set @server = @@servername
exec sp_dropserver @server = @server
set @server = cast(serverproperty('servername') as sysname)
exec sp_addserver @server = @server , @local = 'LOCAL'
END
过程不顺利的解决办法
报错
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'oldServerName'.
查看当前所有连接
-- Query to check remote login
select
srl.remote_name as RemoteLoginName,
sss.srvname
from sys.remote_logins srl join sys.sysservers sss on srl.server_id = sss.srvid
断开连接
-- Query to remove the remote login
--Default Instance
sp_dropremotelogin 'oldServerName'
GO
--Named Instance
sp_dropremotelogin 'oldServerName\instancename'
GO
报错
There is no remote user '(null)' mapped to local user '(null)' from the remote
server 'oldServerName'.
查看发布订阅
-- Query to check if the SQL Instance databases are involved in replication
select name,is_published,is_subscribed,is_distributor from sys.databases
where is_published = 1 or is_subscribed =1 or is_distributor = 1
GO
如果有数据
列出发布的服务器名和发布数据库
sp_helpdistpublisher
列出所有subscribers.
sp_helpsubscriberinfo
删除所有subscribers,每一个都要执行一次
sp_dropsubscriber 'SubscriberName'
列出所有publishers
sp_helppublisher
删除所有publishers,每一个都要执行一次
sp_dropdistpublisher 'publishersName'
报错
Invalid object name 'dbo.MSmerge_agents'
按表语句在distribution建表
create table msmerge_agents
(id int, name nvarchar(100),
publisher_id smallint,
publisher_db sysname, publication sysname,
subscriber_id smallint, subscriber_db sysname,
local_job bit, job_id binary(16),
profile_id int, anonymous_subid uniqueidentifier,
subscriber_name sysname,
creation_date datetime,
offload_enabled bit,
offload_server sysname,
sid varbinary(85),
subscriber_security_mode smallint,
subscriber_password nvarchar(524),
publisher_login sysname,
publisher_password nvarchar(524),
job_step_uid uniqueidentifier
)
CREATE table MSmerge_subscriptions
(
publisher_id smallint,
publisher_db sysname,
publication_id int,
subscriber_id smallint,
subscriber_db sysname,
subscription_type int,
sync_type tinyint,
status tinyint,
subscription_time datetime
)
执行
exec sp_dropdistributiondb @database = N'distribution'
GO
重复步骤3
参考文档
https://blog.csdn.net/godbugs/article/details/50232749
http://www.itkeyword.com/doc/1014338758769575x776/system-query-sql%20serverserversqlserverdatabase
https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/
http://blog.chinaunix.net/uid-16909016-id-120125.html