Second database with same name
Question
Tuesday, January 19, 2010 4:52 PM
Hi forum,
I d'like to host multiple databases with same name on one server.
How can i realize this?
I tried to use different Safe-Pathes for DBs, but that didn't work.
I know that it's not the idea of MSSQL to use more then one database with same name, but it's
not resolveable on different way.
I've installed MS SQL 2005.
Best regards
Marco
All replies (11)
Friday, January 22, 2010 11:06 AM ✅Answered
Also, check what port number is used for this second instance for the TCP/IP netlib (SQL Server Configuration Manager) and try to conncet using that port number instead of the instance name:
machinename,portnumber
Note: it is a commn, not semi-colon.Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
Friday, January 22, 2010 10:06 PM ✅Answered
You have to use SERVERNAME\INSTANCENAME convention to connect to an instance. SERVERNAME only for default instance. Don't use localhost and similar because just creates confusion.
Go to Registered Servers in SSMS and register the instances (Gold Cylinder).
After registration you should be able to connect to an instance in Object Explorer.
You can also open a New Query window. However, if you are connected to more than one instance, it may become confusing where the Query Window is attaching. If you glide over the frame header with the mouse, you will be able to tell which instance the Query Editor pointing to.Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
Saturday, January 23, 2010 4:52 PM ✅Answered
Also, verify that the instance-specific port is avaiable for remote connections (i.e. not blocked by firewall). An easy way to test port connectivity is with the command-line TELNET utility. For example, if the instance port is 12345:
TELNET sqlservername 12345Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Tuesday, January 19, 2010 5:42 PM
I don't think it's possible.Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Tuesday, January 19, 2010 6:18 PM
Since the way to address a database is through its name, how would you differentiate between the two if they had the same name? It would be like having two files in the same folder and exactly the same filename...
Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
Tuesday, January 19, 2010 7:26 PM | 1 vote
You would have to use two different instances of SQL Server in order to use two databases with the exact same name on the same server with different data and log paths.
Tuesday, January 19, 2010 8:38 PM
Since the way to address a database is through its name, how would you differentiate between the two if they had the same name? It would be like having two files in the same folder and exactly the same filename...
Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
Hi TiborK,
I thought that it could be differentiate by logon. So user1 would be able to have same database as user2 if they have seperate storage-path.
You would have to use two different instances of SQL Server in order to use two databases with the exact same name on the same server with different data and log paths.
Hi Sean Gallardy,
I now installed a second instance of SQL Server named sandbox01. I'm able to login from localhost to this new instace.
In Connection-Windows i can see servername as follows: WWW02\SANDBOX01
Unfortunately i'm not able to log on this instace from remote. i tried to use MyHost\WWW02\SANDBOX01 and MyHost\SANDBOX01
but i doesn't work. the default-instance is available by connecting to MyHost.
I also checked that Windows- AND SQL-Authentication is enabled in properties.
How do I correctly connect to my new instance?
Best regards
Marco
Wednesday, January 20, 2010 11:00 AM
Marco,
First thing to do is check to make sure that the SQL Browser service is running and that the new instance is setup to allow remote connections. Once those are verified, you should be able to connect to the instance by Servername\Instancename which in your case looks to be "WWW02\SANDBOX01" without the quotes. Try this and get back to us if it does not work.
Hope this helps,
Sean
Friday, January 22, 2010 10:21 AM
Hi Sean,
I can confirm that SQL Server Browser Services is started, I checked it in services.msc.
I can confirm that TCP/IP and Named Pipes are enabled for Default and Seconde Instance.
I checked this in SQL Server Configuration Manager.
It's strange but First (Default) Instance is able to accept remote connections.
Best regards
Marco
Friday, January 22, 2010 10:55 AM
Marco,
You also confirmed that allow remote connections is enabled for the second instance? Can you connect to it locally?
-Sean
Sunday, January 24, 2010 10:12 AM
How many users do you have?
Assigning separate instance to each user does not scale very well.
Assigning separate database (one instance) to each user is somewhat better scaling-wise, but there is substantial maintenance overhead if you have hundreds of databases.
For large number of users, you add UserID to each table where it matters. This of course assumes that the database is accessed through an application software which controls security.
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com