Unanswered: not able to access mssql instance using alias
We created an alias using the sql server configuration manager. We were able to access the instance thru the alias on the server the alias was setup. However, we were not able to access the instance thru the alias on other server.
Is there other configuration that we need to do to make this work?
Depends how you are using the alias, if you are connecting from a different machine then it does not know what "dbserver" is.
You would have to create an entry in your local host file to say that "dbserver" means 192.168.0.1 or something like that
Again, depends how you are using it though
For example, when I was in the transition stages of moving dbs and I was calling stored procedures on another db, I created alias and setup jobs that called the other server which were aptly names "dbserver1" and "dbserver2". I had to create a host entry so that dbserver1 knew where dbserver2 was though
We have installed 5 instances of mssql 2008 on one server. Now, there server is have resource issue. We want to reduce the number of instance, but want to have as little impact on the user as possible. One way that we want to accomplish this is to move databases from 1 or more instance to the other instance and shut down these instance. So, instead of 5 instance, we might have only 3 instance. If we can create aliases for the 2 shut down instance, then user would not have to make any changes to their application and be still able to connect to their database thru the alias.
instance A - db1, db2
instance B - db3, db4
instance B - db1, db2, db3 and db4
using alias A, user can use the same connect string to access db3 and db4
this is on the company's network. so, the sql studio on the other serverB will know the serverA where the alias is.
for example: there is an instance on serverA called serverA\instance1, which I have not problem accessing on serverB. So, if I create an alias call instA on serverA, pointing to instance serverA\instance1.
Should I not be able to access serverA\instance1 using its alias -> serverA\instA? Or, do I need to use the FQN, like serverA.company.com\instA? thanks
Aliases is (as far as I know) only usable on the server on which they are created, so you'll have to create it on all servers which shall use the alias. It is actually a feature of the SQL Server client, not the Server software.
I tend to avoid using aliases, except for testing purposes. For better naming, i could suggest the following:
* ONLY use named instances
* Instance names should be unique across all DB Servers
* Instance names should NOT be a host name
* Create a CName record in DNS matching the instance name, referring to the server hosteing the instance.
In this way. Lets say you have ServerA\InstA. You crate a CNAME record InstA -> ServerA. You can connect to InstA\InstA. If you want to move the instance to a different server, you can create InstA on ServerB, restore, set up database mirroring, whaterver to copy the data. When you're finished moving all data, just take down the old server, and alter the CName record.