SQL Server - 1 hostname - many sql servers?

Is there a way for me to have one sql server host name but have many sql servers using the same hostname.  I hope there is something for this.
example.

2012 sql server - hostname - server1  - has databases,  apple and orange on it
2014 sql server - hostname - server2 - has databases, one and two on it
2016 sql server - hostname - server3 - has databases , car and truck on it

i would like all the connection strings to be BigSqlServer and then the database name

ie   BigSqlServer\apple - actually forwards it to Server1\apple
ie.  BigSqlServer\truck     - actually forwards it to Server3\truck


the other thing i would hope to do is to move a database from one server to another and leave not have to worry about client connections
jamesmetcalf74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPRetiredCommented:
actually it would  be  computername\instancename\database
computername is looked up and an ip address is returned. You can't have more than 1 computer with the same ip address
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Is there a way for me to have one sql server host name but have many sql servers using the same hostname

Yes, provided if the Server specs are good enough to support multiple instances of SQL Server.
And then install SQL Server as mentioned below:

* 2012 sql server instance as - SQL2012 - has databases,  apple and orange on it
* 2014 sql server instance as - SQL2014 - has databases, one and two on it
* 2016 sql server instance as - SQL2016 - has databases , car and truck on it

If configured like above, then you can connect to the SQL Server in your connection string like:
* 2012 sql server instance as SERVERNAME\SQL2012 to access databases apple and orange
* 2014 sql server instance as SERVERNAME\SQL2014 to access databases one and two
* 2016 sql server instance as SERVERNAME\SQL2016 to access databases car and truck

>> the other thing i would hope to do is to move a database from one server to another and leave not have to worry about client connections

If you are having multiple versions of SQL Server installed, then you can move database from 2012 to 2014/2016 and 2014 to 2016 only and not vice versa..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
No. Of course you can run all instances on the same machine (what Raja is talking about), but having instances on different machines and still use a single hostname is not possible.

You could use (local) connection aliases like SQL2012 pointing to the correct server and instance, and zhen just using those aliases for the server part. But that requires to modify local settings on each client (e.g. per registry edit).

Or switch to using port numbers instead of instance names in connection strings, then implement port forwarding on one machine for all those ports to the real destination, like I described  in https://www.experts-exchange.com/questions/29133029/Redirecting-SQL-traffic-for-one-specific-SQL-instance.html?headerLink=workspace_participated_questions.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Gustav BrockCIOCommented:
Or you can simply assign a different port number to two or all instances, like:

Alternative port
Then append the port number to the hostname in the connection string as hostname,portnumber like:

BigSqlServer,10433
SteveCommented:
What you're asking for may be achieved with Always On Availability Groups. This allows a SQL DB to exist on more than one server to improve availability & resilience, but uses a single 'hostname' regardless of which server the DB is 'live' on.

The Microsoft documentation is on the link below but it may be a tough read if you've not had experience of them before but they're not too complicated once you've taken a look at a few guides.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-2017

Functionality depends which SQL version you are using, but here is a quick FAQ on the basic functionality possible without needing to buy Enterprise.
https://blogs.technet.microsoft.com/msftpietervanhove/2017/03/14/top-5-questions-about-basic-availability-groups/
jamesmetcalf74Author Commented:
Thanks Gentlemen
looks like for me to achieve what i want, i'm going to have to implement something bigger than expected.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.