SQL Server 改名 和 Replication, Distributor
来源:互联网 发布:php array key 编辑:程序博客网 时间:2024/05/14 08:19
连接SQL server replication需要使用真正的服务器名,即计算机名,不能使用IP,别名...,所以在连接SQLServer的时候就用:服务器名\实例名, 比如REXWU\SQL2005
如果计算机名改变了,那么原来的replication都会失效,需要删除。
如果计算机名改变了,SQL Server中原数据中保持的名字不会自动改变,仍然是旧名,所以需要来改名,这个过程稍微有些复杂:
具体参照: http://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
====
如果计算机名字修改了,还会影响到Replication中的Distributor,因为Distributor仍然使用的是原来的名字,这个时候在SQL Server Management Studio中Replication文件夹上右击就看不到configure distribution节点,这个时候需要Disable Publishing and Distribution to disable Distribution
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/3dc66370-292d-42e6-90ad-f78bbae68285
====
如果连接失效,参照下面:
Steps to change the server name for a SQL Server machine
Problem
In this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.
Solution
As you may know, the internal SQL Server name is based on the physical server name, but changing the physical server name does not update the metadata within SQL Server.
When connecting to SQL Server, we use the physical server name for the default instance and physical server name\INSTANCE Name for a named instance. If you change the physical server name, there is no need to re-install SQL Server to reset the physical server name. You can follow the below steps to update the SQL Server system metadata which stores the physical server name in sys.servers.
The below solution works for default and named SQL instances on a standalone machine (non-clustered). Also, you need to follow the steps for each SQL Server instance on the machine.
--------------------------------------------------------------------------------
Pre Update Steps
Check for Remote Logins
If remote logins exist you have to drop them first, if you do not drop the remote logins you will get the below error when executing the sp_dropserver stored procedure.
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'sqldbpool'.
You can run this query to get a list of remote logins that exist on the server.
-- 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 old_physical_server_name
GO
--Named Instance
sp_dropremotelogin 'old_physical_server_name\instancename'
GO
Check for Replication
SQL Server does not support renaming computers that are involved in replication. If the SQL Server is involved in replication you have to remove it first. If you do not remove replication, you will get the below error when updating SQL Server System metadata.
Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 68
There is no remote user '(null)' mapped to local user '(null)' from the remote
server 'sqldbpool'.
You can run this query to see if replication is in place. If so, you could script out the settings first so you can recreate afterwards and then remove replication.
-- 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
Check for Reporting Services
If SQL Server Reporting Service is installed and configured, you have to first stop the Reporting Services service.
Check for Database Mirroring
If database mirroring is enabled you have to remove mirroring and then re-establish mirroring once you have made the system metadata update.
--Query to find out the databases involved in DB mirroring
select database_id, db_name(database_id)
from sys.database_mirroring where mirroring_guid is not null
--Query to turn off the database mirroring
ALTER DATABASE SET PARTNER OFF
--------------------------------------------------------------------------------
Steps to rename SQL Server
Execute the below commands for each instance on the machine.
-- for a Default Instance
sp_dropserver
GO
sp_addserver , local
GO
--for a Named Instance
sp_dropserver <'old_physical_server_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
Restart the SQL Server services.
Execute the below query in each instance to verify the updated system metadata.
SELECT @@SERVERNAME
--------------------------------------------------------------------------------
Post Update Steps
Add the remote logins using the sp_addremotelogin command.
sp_addremotelogin [ @remoteserver = ] 'remoteserver'
[ , [ @loginame = ] 'login' ]
[ , [ @remotename = ] 'remote_name' ]
Reconfigure Replication if this was setup.
Reconfigure Database Mirroring if this was setup.
Reconfigure Reporting Services if this was setup and connect to the new server name as shown below.
If you also changed the Report Server computer name, you will have to edit the RSReportServer.config file and update the URLRoot setting. Do to this, open the RSWebApplication.config file and modify the ReportServerUrl setting to reflect the new server name.
Next Steps
Change application connection strings pointing to the server
Change linked servers pointing to the server
- SQL Server 改名 和 Replication, Distributor
- Find out distributor and publisher info in Sql Server replication (摘)
- sql server 数据库改名
- sql server 数据库改名
- sql server数据库改名
- SQL Server-- Replication
- SQL Server Replication
- SQL Server Replication II
- SQL server replication
- SQL Server Replication
- SQL Server订阅与发布 [进程无法连接到 Distributor]
- SQL Server 2000 数据库改名
- SQL Server 2000 数据库改名
- SQL Server 2000 数据库改名
- Pro SQL Server 2005 Replication
- SQL Server Replication 冲突逻辑
- SQL Server 2000 数据库改名(保准正确)
- sql server 2000 数据库彻底改名
- 安装jdk时环境变量的配置
- irrlicht资料
- CXF 服务调用之java客户端代码
- 关于phonegap 开发iOS 程序时 的ERROR whitelist rejection【不能请求服务端】
- Spring MVC 入门
- SQL Server 改名 和 Replication, Distributor
- Android menu.add()的使用说明
- Unix网络编程上卷--第5章
- JAVA 反射学习总结
- VC++6.0、VS2003、vs2005、vs2008程序依赖DLL
- struts2中用ajax问题
- Oracle中没有 if exists(...)
- 交通灯管理系统
- What do 'statically linked' and 'dynamically linked' mean?