create another instance but will be the same DB name on same server

Question

create another instance but will be the same DB name on same server

poratips

Hall of Fame

Points: 3994
More actions
July 25, 2013 at 11:32 am

#280567

Hi,
I need to create another instance but will be the same DB name on same server, is it possible?
1) i have server called Server1 and it has instance called (DBDEV) and it has different DB like DB1, DB2
2) My Data File and Log file is on D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
3) Now i need to create another Instance so they can use using as IP address
4) they wants same DB name as in my exising Instance
5) We have Sql 2005 on Windows m/c
Could you please guide me as is it possible or not and how can i do this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

poratips (7/25/2013)
Hi,
I need to create another instance but will be the same DB name on same server, is it possible?
1) i have server called Server1 and it has instance called (DBDEV) and it has different DB like DB1, DB2
2) My Data File and Log file is on D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
3) Now i need to create another Instance so they can use using as IP address
4) they wants same DB name as in my exising Instance
5) We have Sql 2005 on Windows m/c
Could you please guide me as is it possible or not and how can i do this?

You can have 2 instances on the same IP with the same databases BUT the instance name will have to be different, your current setup is DBDEV, you can create an instance with any other valid name.

ServerName\DBDEV

ServerName\DBDEV2

They are two entirely separate SQL servers so having the same Database name on each is just fine. They will be at different places on disk as well. Does this make sense?

CEWII

poratips Hall of Fame Points: 3994 More actions · Answer 2

Thanks Elliott for quick response as i need to do it today.

1) When i install sql 2005 on same server, i can leave the default instalaltion, right?

2) We have only two drive c and D and on D drive we have data file and log file

Is it my data file name having an issue if i specify the directory D:\?

You can have 2 instances on the same IP with the same databases BUT the instance name will have to be different, your current setup is DBDEV, you can create an instance with any other valid name.

ServerName\DBDEV

ServerName\DBDEV2

They are two entirely separate SQL servers so having the same Database name on each is just fine. They will be at different places on disk as well. Does this make sense

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

poratips (7/25/2013)
Thanks Elliott for quick response as i need to do it today.
1) When i install sql 2005 on same server, i can leave the default instalaltion, right?
2) We have only two drive c and D and on D drive we have data file and log file
Is it my data file name having an issue if i specify the directory D:\?

I know that SQL 2008 and above knows about other installs on the server, I THINK SQL 2005 does as well, it should make you chose a different instance name which will cause it to install in a different path. This is all good. Under NO circumstances use the same path for user databases between the two instances. Each instance should use different paths. The instances can share the same drive, but keep in mind they will fight each other for disk resources.

CEWII

poratips Hall of Fame Points: 3994 More actions · Answer 4

Thanks Elliott once again.

I have already default instance and we called by ip or DEVDB then iinstalled sql 2005 and created another instance as named instance - STG so it's called DEVDB\STG becuase sql server can have only one default instance.

By mistake, i forgot to change the default location for files so all system DB created and using C drive.

How can I change the location? Should i leave the system DB there and create another MSSQl directory on D:\ drive like files - D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL and when i create/Migrate new DB then change the .mdf and .ldf location to new path?

If i migrate DB then it will go to old location where my system DB files are, right?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

You can set the default location for user databases in management studio. There are good articles online for movie the system databases. It would be better to use one of those than for me to try and explain it. The system databases do not need to be in the same place as the user databases, there is no requirement for that.

CEWII

poratips Hall of Fame Points: 3994 More actions · Answer 6

Ok, Then I will just leave the System DB on C and will change it to D drive for USer DBs when I will do the Migration/creation of new DB.

Thank you!