Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 create another instance but will be the same DB

Author  Topic 

poratips
Posting Yak Master

105 Posts

Posted - 2013-07-25 : 13:35:14
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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-25 : 15:24:05
Yes you can have the same database name used on the same server, provided they are all on different instances. The name must be unique on the instance only.

You just would provide a different path for the installation and recommended to use a different path for the databases (different than the installation path).


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-07-25 : 17:45:28
Thanks Tara.
Acyually by mistake, i forgot to change the path and it's created on C:\ drive instead of D:\ drive so currently my system db files on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.
Should i leave that it will be ok on C and during user DB Creation/Migration I should use D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL like new path or better to move on D:\drive system DB too?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-25 : 22:38:15
Yes that is fine to have the installation on the C drive, assuming you have space. You can change the default path for new databases in the settings, or just provide the path when you create them.

We do not put any database files on system drives. We split tempdb onto its own disks, other system databases on their own disks, user database data files on more disks and then transaction logs on even more disks. So we've got tons of disks for our setup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-07-26 : 00:17:23
Thanks Tara once again for your kind help!
This will be my STG instance so I will just leave system on C and during migration, I will put the user DB on D drive where I have already created the Folder.
I am completely agreed with you that for production DB, when I will be doing conversion from 2005 to 2008R2 then I will be planning Master, Model and MSDB DB on C, Temp DB on D drive , Data file for User DB on E and Log File on F Drive.
Do you think that Master, Model and MSDB DB will be ok on C or I leave C alone and install on D and then move forward for others files on each separate drive?
Currently we have virtual m/c and thinking get Sql 2008R2 enterprise edition with 8 processor and 32 GB memory should be fine as we have Replication DB is almost 300 GB and other 15 DB is ranging from 5 - 50 GB each?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-26 : 00:41:53
It's recommended to not have database files on system drives. It's somewhat of a pain to move them. I would move them if it were prod environment but probably not stage.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-07-29 : 11:26:10
Thanks Tara.
When I do the Production upgrade, what you suggest? and allocated initially 100GB each drive and TEMP DB - 1 TB.
We have currently Windows Server 2008R2

1) Data File - D drive
2) Log File - L Drive
3) Temp DB - T Drive
4) MAster, Model, MSDS - Which drive, I it ok to put on C drive?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 13:04:37
We split up tempdb into two drives, well actually we use mount points. I wouldn't recommend putting the system databases on the C drive.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -