How to add another instance of SQL SErver 2005 in a Server

Question

How to add another instance of SQL SErver 2005 in a Server

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
September 26, 2009 at 1:41 am

#135438

Hi,
I would like to know How to add another instance of SQL SErver 2005 in a Server. I have SQL Server 2005 installed in my Win OS server2003 with a named instance myname\sql2005, now i would like to add one more instance in the same server for the same sql server so that, there will be different instances for different DB.
In other words, i would like to virtualize SQL Server 2005 for different diferent databases in my production server having a single SQL server 2005 in single server machine.
I need to achive, one single server 2005 installed in a Server Machine, need to have 2 different instances having different UserDatabases for each instance.
So please let me know how to achive this task.
With Regards
Dakshina Murthy

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You'd install another instance the same way you installed the existing one. Enter a unique instance name when prompted by setup. Database components will be designated with a new instance ID. I recommend putting database data files and log files on different drives than the existing instance's files if you have enough drives on the server.

Greg

Steve Jones - SSC Editor SSC Guru Points: 735932 More actions · Answer 2

I think you're confused about SQL Server and instances.

EVERY installation of SQL Server is an instance. You can have one default instance, which responds to the Windows server name. All other instances are named instances, which have the Windows name\instance name.

If you install multiple instances, they are separate. They are not virtualized, but they are separate server services running on your machine. not all SQL Server components are multi-instance, like SSIS. You only get one per Windows host.

Virtualization is a separate piece of software that runs on a host and appears as a separate Windows machine. Each separate virtual machine appears as another complete server. If you install SQL Server in a virtual machine, it is separate from other machines, and the host. Just as if you have a separate physical server.

You can have multiple databases on one instance for separate applications. You can assign security separately for each one.

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 3

Hi,

Thanks a lot i tried and it has happend.

With Regards

Dakshina Murthy

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 4

Hi,

Thanks a lot, I will keep the Data Files of the different Instances in the Separate Drive, I would like to know by having multiple instances and using separate instance for the separate DBs will it improve the performance of the DB, as one application is using DB1 in instance1 and other Application using DB2 instance2.

I would like to know is there any difference or improvement of performance in having separate DBs in separate instances.

Also i would like to know will it be any improvment in the performance if i have the virtualization of the SQL SErver like having different dbs in the different Virtual Machines where in the SQL Server will be of different instance.

With Regards

Dakshina Murthy

Steve Jones - SSC Editor SSC Guru Points: 735932 More actions · Answer 5

there isn't necessarily any improvement from having the dbs in separate instances. You are now sharing memory, which is an issue on 32 bit systems. You have to set the memory that each will use so you aren't starving the instance. On 64bit systems, with more RAM, this is less of an issue.

Two instances do not share tempdb, so if you make heavy use of that, then it may help.

Virtualization means you are sharing all resources among two VMs. This doesn't provide a performance benefit. It's a performance hindrance. It is usually used to consolidate machines, not improve performance.

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 6

Thanks a lot for quick reply sir, So how to allote the memory for each SQL Server instance in either 32 bit or 64 bit systems, if i do so, will this be avoiding the slowness of one DB which will not be affected to the other DB in other instance. Please help me out. Whats your advice regarding this, is there any way to go with the VIrutualization of SQL server itself.

With Regards

Dakshina Murthy

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

Read section 'Running Multiple Instances of SQL Server' at

http://msdn.microsoft.com/en-us/library/ms178067.aspx

MJ