December 15, 2014 at 2:19 pm
We are running Sql Server 2005. We have about 100 SSI packages which runs based on the old Sql server name.
I have moved the msdb databases from the old machine and I am able to see all the packages. However I cant run any since they are based on old sql server name. But they work if I export them and re-configure them.
So I need to rename the sql server and the database so that I can run all the sql integration packages without any changes. What is the best procedure the rename the machine and the server. The server I am moving to is Windows server 2007
Can I rename the machine first and then drop and change the database instance name?
Thank you
December 16, 2014 at 12:21 am
Dear Vijay,
There is no need to rename the machine as your SSIS packages would be connecting to the instance name of your SQL Server.
As you have not mentioned if you have a default instance or a named instance, I would suggest that you look for the syntax for using
Sp_dropserver and sp_addserver.
Here is a gud link
# http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/
Hope it helps..!!!
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 16, 2014 at 2:31 pm
thank you for your reply. We don't have instance running. It is just the default. I have not yet renamed the server or the mssql SQL. I don't want to end up in 2 names.
1. Can we do it from remote desktop? I have admin access.
2. SELECT SERVERPROPERTY ('InstanceName') returns Null We haven't configured reporting server either.
So is there anything else I need to do apart from renaming the machine and running the following sql. Is the syntax correct?
sp_dropserver 'sqlprod_1'
--Add the current server.
sp_addserver 'sqlprod_2', local
Thank you
December 16, 2014 at 9:50 pm
Dear Vijay,
If you only have a default instance then the name of the SQL Server Instance would be your machine name. You can check with the listed query.
select @@servername
Copy the name of the instance from the output of the above query and drop replace it with the desired name.
sp_dropserver 'CURRENT_NAME'
sp_addserver 'DESIRED_NAME', local
Run the query again to check if the new name is updated or not.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 19, 2014 at 10:25 am
FYI: This works... however, you must stop/start SQL Server for the servername within SQL server to take effect.
December 19, 2014 at 10:32 am
Thank you everyone. I am also changing the name of the server it self. Do I need to do this after rebooting the machine with the DNS name change or can I do this at the same time (both DNS name and SQL instance name) and reboot the machine later?
Thanks again
December 20, 2014 at 8:55 am
Dear Vijay,
You can change it in one go. No need to wait for the restart.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply