October 10, 2006 at 9:46 pm
I have just migrated a whole set of databases from a SQLServerver v7 box to a SQL2k box on WIN2k3. Problem is I had to do the rename tjing of my new boxto the old server name, and it aint working!!
oldserver name = sqlsvr1
new server = wimnt90
i renamed the new server with the oldserver name, I have the sqlserver service running as sqlserver1 but when I query the server name with SQL it thinks it is still WIMNT90. I have tried the sp_dropserver and addserver using the local option and that hasb't worked. any ideas anyone?
October 11, 2006 at 1:07 am
nearly there - not local
exec dbo.sp_dropserver oldname ( wimnt90 )
exec dbo.sp_addserver newname ( sqlsvr1 )
stop and restart sql service.
Be careful when using a sql server name which is not the same as the server name - you might also want to create an alias for the names to be on the safe side.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 13, 2006 at 11:12 am
A week ago, we just cut over from SQL7 to SQL8 from WINNT to WIN2003.
We feel your pain!
The key to getting these steps to work was shutting down EVERYTHING that is connecting to SQL server durring the renaming steps. You should beable to run SP_WHO2 and see only the ONE connection that you are using.
Some of these steps will not apply-- and there may be more depending on what you have running. Another aproach (my recomendation) is to detach all your databases, uninstall SQL, rename and re-install SQL then re-attach your databases.
1. Have all users and systems exit Boris SQL 7 and BorisNew SQL 2K
2. Verify full dB backups from previous night on BorisNew SQL 2K and Boris SQL 7
3. Create Boris SQL 7 dB differential backups with noinit
4. Create BorisNew SQL 2K dB differential backups with noinit
5. Run export scripts from BorisNew SQL 2K -> Management -> SQL Server Agent -> Jobs
6. Delete jobs from BorisNew SQL 2K -> Management -> SQL Server Agent -> Jobs
7. Delete publications from BorisNew SQL 2K -> Replication -> Publications
8. Import data from Boris SQL 7 to BorisNew SQL 2K
9. Rename Boris SQL 7 to BorisOld SQL 7 (May disconnect from network.)
10. Rename your SQL Server from BorisNew SQL 2K to Boris SQL 2K
11. Reboot Boris SQL 2K
12. On Boris SQL 2K login as SA in Query Analyzer and run the following MS KB 818334:
-- Use the Master database
USE master
GO
-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)
-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))
-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)
-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername
-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'
12.1. Stop and restart SQL Server and SQL server agent
12.2. Verify that the SQL Server name and the network name of the computer are the same In Query Analyzer run
SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')
13. In Query Analyzer connect to Boris SQL 2K and run the following against the System databases that are used with Solomon:
13.1. Update Domain set servername = ‘Boris’
14. After this is complete log into Database Maintenance on Ananew
14.1. (Start | Programs | Microsoft Dynamics SL (Solomon) | Database Maintenance
14.2. Select Boris SQL 2K and enter the SA user and password
14.3. Go to the Update Database tab.
14.4. Select your system database
14.5. Highlight the application databases and click update views in the bottom lower left corner
14.6. Enter Dynamics SL username and password
14.7. Repeat 15.4 – 15.5 for the test and prototype databases
15. Once this is done, you will need to update the Solomon.ini with the new server name so the users will not see the difference. I would recommend editing one Solomon.ini and copying it to all workstations folder. The Solomon.ini is located in the Windows folder on the local machines. There is a line in the file that says server= and you will want to remove BorisNew and replace it with Boris.
15.1. PC’s to update...
16. All users that have FRx will need additional work done.
17. Run scripts in Query Analyzer connect to Boris SQL 2K as SA for Management -> SQL Server Agent -> Jobs from step 5
18. Run scripts in Query Analyzer connect to Boris SQL 2K as SA for Replication -> Publications from step (from script that Shiva had previously ran on Boris SQL 7)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply