belle’s sql musings

Drop and Re-Add Server Registration When Renaming Computer

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting by belle on October 31, 2008

Problem:

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 alternate name are not supported. Specify the actual server name, ‘SQLVM\SQL02′. (Replication.Utilities)

Scenario:

1. We configured database mirroring on 2 SQL Server instances.

2. We changed the computer name from SQLVM to SQL2K5.

3. We tried launching the Database Mirroring monitor, but we got the following error:

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 
alternate name are not supported. Specify the actual server name, 'SQLVM\SQL02'. 
(Replication.Utilities)

If you just look at the error, it sounds a bit misleading because we don’t have replication configured in either of the servers.

Resolution:

-- 1. drop old server/instance name
EXEC sp_dropserver [sqlvm\sql01] 
GO
 
-- 2. add newserver/instance name
-- local meaning we're adding the local machine
EXEC sp_addserver [mysql2k5\sql01], local 
GO
 
-- 3. Restart SQL Server service

Tagged with:

leave a comment

Issue: Length of LOB data to be replicated exceeds configured maximum 65536

Posted in Issues/Troubleshooting by belle on September 22, 2008

Error:

Length of LOB data (78862) to be replicated exceeds configured maximum 65536

Scenario:

We published some articles that use varchar(max) and a lot of XML data types for the columns. When we enabled replication, we got the error Length of LOB data (78862) to be replicated exceeds configured maximum 65536

Solution:

Increase the size that can be replicated. This is applicable for transactional replication only.

T-SQL: 
EXEC sp_configure ‘max text repl size’, 2147483647

SSMS (excerpt from BOL):

    1. In Object Explorer, right-click a server and select Properties.
    2. Click the Advanced node.
    3. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

Reference:

BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3056cf64-621d-4996-9162-3913f6bc6d5b.htm

Tagged with:

1 comment