Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create another instance but will be the same DB name on same server Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 11:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:28 PM
Points: 182, Visits: 648
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?

Post #1477647
Posted Thursday, July 25, 2013 11:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1477650
Posted Thursday, July 25, 2013 11:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:28 PM
Points: 182, Visits: 648
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
Post #1477657
Posted Thursday, July 25, 2013 12:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1477684
Posted Thursday, July 25, 2013 3:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:28 PM
Points: 182, Visits: 648
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?
Post #1477779
Posted Thursday, July 25, 2013 3:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #1477784
Posted Thursday, July 25, 2013 10:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:28 PM
Points: 182, Visits: 648
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!
Post #1477842
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse