Hi Experts,
I have old server ( which is in 2-node cluster) and has 2 instances runnning. But i want to migrate everything from one instances to New standalone server.
1) How do I rename the alias on new server so all the applications can point to new server.?
2) Both old and new servers are in Same SAN.
Appreciate for your experts advice.
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Thanks
Sean
I installed sql server on one of my test servers, and I am eventually going to rename this server and put it into production. This server will replace one of my old servers with the same name. I was wondering if there was a way to rename the SQL Server instance after you rename your server without having to reinstall SQL Server. Thanks in advance for all the help.
View 2 Replies View Relatedhow to add a alias to a server?
I have gone through BOL where it says it can be done thru right click agent -->> connections and configure alias.
i want to know if i configure a alias to a server from a client is it valid for other connections also?
meaning there are two database servers A and B
i add alias to server A by using client network utilities on server B,
then is this alias available if i try to connect to the server A thro some other server c?
hope this is not that confusing as it sounds. ;)
Is there any way to refer to an Alias in a select statement? For example, if I create an expression that sums to fields together and give it the alias TOTAL, and then in another field I want to create and other expresssion that multiplies a field by TOTAL, what is the syntax?
In MS Access the Alias name is surrounded by brackets, but I can't seem to find a way to do this with SQL Server.
Thanks
We have a SQL 2000 installation on a server called SATURN that has been around for a while. We've installed a new SQL 2005 installation on a server called DEIMOS. Our intention is to move all the databases that are currently on SATURN, to DEIMOS.
I've just been looking at the config settings, and it's suddenly occurred to me that the ability to set Server Aliases might save me quite a bit of work. What I'm thinking is setting a new alias on DEIMOS called SATURN. Then I won't need to go round the various applications changing the connection properties.
So:
Does this sound reasonable?
I intend uninstalling SQL 2000 from SATURN, but the server itself will stay - is that ok?
What will happen if SQL comes alive on the server SATURN while the alias is in place - effectively there will be 2 servers responding to SATURN. I ask this because my intention is to shut down all the SQL services on SATURN, set up the alias, and once I'm happy with the testing, then uninstall. Just wondering what will happen if for some reason the services were restarted before I finish.
Is there anything else I need to consider?Regards,
Tim
Today I found a problem with alias name during retrieving values from database table with alias name. My query was like as following:
========================================================SelectEmpID,Case EmpNameOrEmpFirstLastNameWhen βENβ ThenEmpNameElse--EmpLast + β, β? + EmpFirst
EmpLast + ', ' + EmpFirstEnd As EmpName,CityFrom tblEmployeeWhere EmpName LIKE βN%β=======================================================
When I execute this query I get all the record which contains the N% in EmpName as well as from FirstName. As you know that should not because I give alias name EmpName to EmpLast + ', ' + EmpFirst. But here the problem is we cannot use the alias name in Where condition.
I resolve this problem by using #temp table like the following:=======================================================SelectEmpID,Case EmpNameOrEmpFirstLastNameWhen βENβ ThenEmpNameElse--EmpLast + β, β? + EmpFirst
EmpLast + ', ' + EmpFirstEnd As EmpName,CityInto #tmpEmployeeFrom tblEmployee
Select * from #tmpEmployee where EmpName LIKE βN%β=======================================================
It really very simple, isnβt it? You can use the reference link:Reference: http://www.thescripts.com/forum/thread82710.html
So friends enjoy, we are the Sql Master:)Regards,Vijay Modi
Reference: http://vijaymodi.wordpress.com/2007/05/23/sql-server-problem-using-alias-name-in-where-condition/
I have a SQL Server 6.5 db on a server, we need to migrate SQL Server to another server.
We have about 400 users connected to an OBCD driver pointing to the original server and
would like to connect them to the new server.
How can I do this without changing all the users OBCD settings?
Is there a function to alias the new server?
I understand there is a way for a user to connect to a SQL Server
using an ALIAS ( i.e. I am connected with one instance using the
name DUBAY and now I want to make another connection with my
ALias - George). I'd appreciate anyone telling me how to do this.
Thanks in advance.
I am moving my Sql Server installation to a larger machine. In doing so, I don't want to change everyone's connection settings. Does anyone see any issues with using an DNS alias to connect to the new server?
Using the same name is not an option per the windows admin group that is provisioning the machine.
Thanks,
Andy
Hi,
In DTS 2000 when you created a connection you could use a server alias (as created in the network client utility) as the servername. When I try to use a server alias in connection mgr(I am using Native Client) it fails to connect with the standard error: "Test connection failed because of an error in initializing provider. Login timeout expired. etc etc...
Has anyone had success using a server alias? (Yes i set up the alias using sql configuration mgr)
The problem I am trying to solve is I have dev/test/production environments that I want to move this package to and I don't want to have to change any server name properties within the package when moving to the different environments.
Thanks
Can anybody please tell me how to rename an sql server express.
I have an SQL server instance named 'PC075/Sqlexpress'. I have to change it to 'PC075'.
Thanks,
if my computer name is x and sql server was installed and therefore took on the default name of x, can I change the sql server name to y at a later date while the computer name remains as x? If so how do I do it?
The sp_addserver is internal recognition. But I want to connect to sql server using the new name y.
I am assuming setup needs to be run only if computer name also changes to y and this is not the case.
Any help is appreciated.
To all,
I would like to rename my SQL server. Someone suggested SQLServer and we went with it, but after some discussions with other developers we thought that this name would be a hackers delight. So now I need to rename it to something that wouldn't put a "bulls eye" on its back. Any suggestions? :-)
This machine is not in production just yet, so if I had to reinstall SQL it wouldnt' be that bad of an option.
Thnx.
Michael Fanara
Hello, I want to rename a server NT4 on whom is bases sql server sql 7 sp 2, who is the precautions to set and can you indicate me the procedure. Thank you in advance.
pascal
How can I change the name of an SQLServer to have a name different than
the machine it is runnning on?
I inherited a sql server that I have always known as cmsphdb5 setup by a former dba. newer Junior dba tries to install LiteSpeed and he gets and error about server name mismatch. I go to QA and run select @@servername and I get
cmdphdb5. Can I sp_dropserver cmdphdb5 and then
sp_addserver cmsphdb5?
Hi
I am going rename my NT Box SQL Server 6.5 / 7.0 installed. Is there any procedure or steps to follow. Or is there any links already available?
Any guidelines are appreciated.
Thanks in advance
Jaya
I have an SQL Server 2000. I need to rename the box that it is installed on
and then rename the SQL Server to have the same name as the what the server
was renamed to. How do I go about this? What are the steps?
Thanx
I am using SQL Server 2005 in a test environment and I want to rename the server to match my production environment. Currently the name is the same as my computer name. Can it be changed? Do I have to change my computer name? (My computer is not on the same network so it is possible).
View 3 Replies View RelatedWe want to migrate a SQL 7 to SQL2000 (there will be a new box) and then rename the old SQL 7 box and keep it on the network.
Seems like I remember there is a problem when your rename a SQL server.
Anyone have experience with this?
Thanks
Chuck
I have renamed one of my sql7 server boxes; then the sql server won't start and get an error message: something related to server license. Can anybody help me on that??
Jim
I had two servers, in which SQL Servers are installed. I created a alias for server
Server 1 : test1.sqlcloud.com (Alias name with port no other than 1433)
Server 2 : test2.sqlcloud.com (Alias name with port no other than 1433)
I can connect to the servers using the port. for ex server1 to server2 and viceversa using "test2.sqlcloud.com,port". But I could not connect to the server by only specifying the alias name ("test2.sqlcloud.com"). When I am connecting I am getting the following error.
"TITLE: Connect to Server
------------------------------
Cannot connect to test2.sqlcloud.com.
------------------------------
ADDITIONAL INFORMATION:
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21020; handshake=0; (Microsoft SQL Server, Error: -2)
so you have two place to configure alias, in SQL server configuration manager under aliases tab on and on the cliconfg.exe tool on a different machine. If I want another server to see this sql server by the alias name I will need to configure the alias on the cliconfg tool on the client machine. It looks like as long as I have the alias configured in the cliconfg on the client machine, I don't need the alias to exist on the config manager, the client can still see SQLserver using just the alias on cliconfg...if so, I wondering under what situation I would really need the alias on the configuration manager?
View 7 Replies View RelatedHello,
I would like create/modify/update the alias SQL server name on the SQL 2005 version.
I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.
I tried with
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
But this way doesn't work...
Anyone got an idea to change alias SQL server in transact?
Thanks for your help!!!
Hi,
I was looking to a server which was renamed from A_old to A_new.
but the problem is the system table still shows an entry with A_old and is taking this as a local server name.
Any ideas how to do this?Do I have to stop the services for this?run setup again?and then sp_dropserver and sp_addserver is the answer to this?or there is any other solution to it?
Note:this is a productionBox.
Any thoughts appreciated?
Hi,
We have server 'A' as production 6.5 db server. We plan to copy over & restore sytem/user db backups to server 'B'.
and then we plan to rename Serer B to Server A. Could anyone please advice what needs to be configured on SQL 6.5 to handle the Rename.
Thanks,
I have change the name of my host by "network neighborhood"
and user sp_dropserver and sp_addserver.
But when rebooting the host, I have this following message.
"Your SQL Server installation is either corrupt or has been
tampered with (unknown package id). Please run setup."
But I change the name of the hostanme to the former name, there
is no error.
Please, can you advise me something ?
We have an NT 4.0 member server in a production domain which has SQL 6.5 loaded with a number of databases. There is a requirement to rename the NT server to another name. Will we have to reinstall SQL and reload any databases after the server is renamed? If not - are there any other considerations/registry changes necessary to ensure SQL will run correctly?
Thank you for assistance.
I am installing SQL Server 2005 on a couple of servers where the windows server will later be renamed. One is a default instance, and one is a named instance.
Does anyone have a procedure for this, or know any gotchas for 2005? I have done this for SQL 2000 many times, but never of 2005.
Books Online seems to say you just rename the server, and everything will be OK.
I know how to rename the server internally with sp_dropserver and sp_dropserver. It looks like you no longer have to update originating_server in msdb.dbo.sysjobs, because it was replaced by originating_server_id.
Got links?
No nubeez! Xpurts ownli pleez!!
CODO ERGO SUM
We have a new SQL Server 2005 box in place and we named this server - Server2. Restored all the production databases from original Server1. Server1 is running SQL Server 2000 Enterprise Edition. Server1 also has three subscribers ServerA, ServerB, and ServerC.
After Server2 was renamed to Server1 and Server1 decommissioned, replication is not working on the new Server1 due to the renaming issue. I can afford to re do replication from ground up on Server1 but cannot reinstall SQL Server 2005 and start from scratch since the server is already in production.
How can I resolve this issue? What is the right approach to take for the scenario described above?
I have an app that has some stored procedures running queries across a linked server. In our development environment, the linked database is on a box called 10.xxx.xxx.xx. In the production environment, the linked database will be on a server called something else. What I want to do is have the linked server in the development environment named the same thing as the linked server in the production environment, so that I won't have to change the stored procedures when migrating the project into production.
SQL Server 2005 isn't giving me the option to rename or alias the linked server, though. Is there anyway to do this so that I can have the linked server named the same thing on the two different boxes and not have to change the sp's?
I'm trying to write a script to run after changing the computer name of a machine running SQL Server 2005. I know about the article on the SQL Developer center that talks about sp_dropserver and sp_addserver. My question is about the Windows groups that are created with the machine name embedded inside (e.g. MACHINENAMESQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER).
Do I need to worry about these? If the answer is no because the name of the group is irrelevant, how do I go about changing the machine name for the login (i.e. the MACHINENAME in front of the slash)? I don't want to hardcode these group names into my script.
Thanks for any help.