Replication Error: Configure Distribution Error using Renaming SQL Server and Remove replication db

Configure Distribution u can get following Error if Servername and network name not be equal:

Error:

SQL Server replication requires the actual server name to make a connection the server

Rename SQL Servername if @@servername and networkname are not equal

SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')

So u can’t able to configure replication

http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

Error:

SQL Server replication requires the actual server name to make a connection the server

Symptom:

When attempting to create a new replication publication the following error is produced:

New Publication Wizard

SQL Server is unable to connect to server 'SSSS'.

Additional information:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternative name are not supported. Specify the actual server name, 'OOOO'. (Replication.Utilities)

Where "SSSS" is the name of the current server, and (in my case) "OOOO" was the previous name of the same server.

Steps to reproduce this problem:

    i.        Microsoft SQL Server Management Studio

   ii.        Expand the server

 iii.        Expand Replication

 iv.        Right click "Local Publications" and select "New Publication ..."

Cause:

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME
go

This should return the name of the server. If it does not then follow the procedure below to correct it.

Remedy:

To resolve the problem the server name needs to be updated. Use the following:

sp_addserver 'real-server-name', LOCAL

if this gives an error complaining that the name already exists then use the following sequence:

sp_dropserver 'real-server-name'
go
sp_addserver 'real-server-name', LOCAL
go

If instead the error reported is 'There is already a local server.' then use the following sequence:

sp_dropserver old-server-name
go
sp_addserver real-server-name, LOCAL
go

Where the "old-server-name" is the name contained in the body of the original error.

Stop and restart SQL Server.

1.         Log on to the computer where you deployed SQL Server 2000 from the disk image, and then run the following Transact-SQL statement in SQL Query Analyzer:

2.  -- Use the Master database

3.  USE master

4.  GO

5.   

6.  -- Declare local variables

7.  DECLARE @serverproperty_servername     varchar(100),

8.      @servername                        varchar(100)

9.   

10. -- Get the value returned by the SERVERPROPERTY system function

11. SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

12.  

13. -- Get the value returned by @@SERVERNAME global variable

14. SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)

15.  

16. -- Drop the server with incorrect name

17. EXEC sp_dropserver @server=@servername

18.  

19. -- Add the correct server as a local server

EXEC sp_addserver @server=@serverproperty_servername, @local='local'

20.      Restart SQL Server.

21.      To verify that the SQL Server name and the network name of the computer are the same, run the following Transact-SQL statement in SQL Query Analyzer:

SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')

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 theReportServerUrl setting to reflect the new server name.
• How to remove a Replication database
•  
• sp_removedbreplication ‘DB Name'
• go
•  
  
• How to remove a SQL Server distribution database if other methods fail
• For various reasons, you may need to manually remove SQL Server replication.  Microsoft has a good KB article which shows how to do it.  It has always worked in the past, but I ran into a situation a couple of weeks ago where it couldn’t remove the distributor via sp_dropdistributor.  I don’t recall what error we were getting, but I didn’t have time to figure out what was wrong so with the help of a Microsoft engineer we were able to setup another distributor and then get replication working again.  Later I tried “drop database distribution” command on the original distribution database, but it said it couldn’t be dropped since it was being used for replication.  It no longer was being used for replication, so it seemed to be orphaned.
• Yesterday, I opened a case with Microsoft to see how I could get rid of the orphaned distribution database.  It’s a simple fix:
• use master
  go
  alter database distribution set offline;
  drop database distribution;

Scripts:

sp_helpserver

Select * from sys.servers

EXEC sp_helpremotelogin

If any remote login exists then following error comes

There is no remote user '(null)' mapped to local user '(null)' from the remote 
server 'sqldbpool'.

so kindly remove it using following T-SQL

EXEC sp_dropremotelogin 'servername '

-- 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

-- 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

SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')

sp_dropserver 'WELCOME-PC\SQL2005'

go

sp_addserver 'WELCOME-PC\SQL2005REPL', LOCAL

go

After above steps Servername and network name should be equal.

@@SERVERNAME global variable retains the original SQL Server computer name

 SERVERPROPERTY('ServerName') function in SQL Server to find the network name of the computer running SQL Server.

Kindly Check following equal or not as see below:

SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')

So now u can able to configure distribution,publication,subscription without error