locked
sql server with multiple instance RRS feed

  • Question

  • hi  All,

    i want  to  install sql server with two instance   and i want  to assign two  separate is  ips   for  both   default instance  and named  instance   and  both   should  work like two different server

    note:  If i  logged  with  one  ip(default instance)   it should  show  its  own  databases  not both or  named   instance  databases

    thanks 

     chintala

    Thursday, March 6, 2014 7:50 AM

Answers

  • Hi chintala,

    According to your description, if you want to assign the two different IP to stand for default instance  and named  instance, I recommend you set up aliases on a SQL Server with multiple instances. For example, a physical server is named WinServer and 2 instances of SQL Server named WinServer and WinServer\instance1.

    1. We need to add 1 IP addresse to the WinServer, there is one IP address already assigned to WinServer, 192.168.1.1, by adding one new IP's, 192.168.1.2 .
    2. We need to move into DNS to configure our host names and aliases.  The physical machine must be steup as a static IP in DNS. SQLAlias1, which point to the default instance on WinServer, will be setup as a DNS with an address of 192.168.1.1. SQLAlias2, which will point to WinServer\Instance1, will be setup as a new Host (A) record in DNS with an address of 192.168.1.2.
    3. When everything has been completed on the DNS side, you need to open up SQL Server Configuration Manager on WinServer and configure the protocols for each instance one at a time, restart the 3 SQL services.

    For more information about how to set up aliases on a SQL Server with multiple instances, you can review the similar article.
    http://kevine323.blogspot.com/2013/08/setting-up-aliases-on-sql-server-with.html

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Praveen Rayan D'sa Friday, March 7, 2014 4:05 AM
    • Marked as answer by Sofiya Li Thursday, March 13, 2014 2:59 AM
    Friday, March 7, 2014 3:42 AM

All replies

  • Why not having a different login to access the instances... You can also define different ports for the instances..

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Friday, March 7, 2014 2:57 AM
    Thursday, March 6, 2014 8:17 AM
  • hi  All,

    i want  to  install sql server with two instance   and i want  to assign two  separate is  ips   for  both   default instance  and named  instance   and  both   should  work like two different server

    Adding to what Uri suggested what you are asking can be done with Multi instance cluster( if you are ready to go with it) .Cretae windows cluster then on top of it create SQL Server cluster with multiple instances.Provide each instance with different Virtual IP.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Thursday, March 6, 2014 3:35 PM
    • Proposed as answer by Sofiya Li Friday, March 7, 2014 2:57 AM
    Thursday, March 6, 2014 9:48 AM
  • Hi chintala,

    According to your description, if you want to assign the two different IP to stand for default instance  and named  instance, I recommend you set up aliases on a SQL Server with multiple instances. For example, a physical server is named WinServer and 2 instances of SQL Server named WinServer and WinServer\instance1.

    1. We need to add 1 IP addresse to the WinServer, there is one IP address already assigned to WinServer, 192.168.1.1, by adding one new IP's, 192.168.1.2 .
    2. We need to move into DNS to configure our host names and aliases.  The physical machine must be steup as a static IP in DNS. SQLAlias1, which point to the default instance on WinServer, will be setup as a DNS with an address of 192.168.1.1. SQLAlias2, which will point to WinServer\Instance1, will be setup as a new Host (A) record in DNS with an address of 192.168.1.2.
    3. When everything has been completed on the DNS side, you need to open up SQL Server Configuration Manager on WinServer and configure the protocols for each instance one at a time, restart the 3 SQL services.

    For more information about how to set up aliases on a SQL Server with multiple instances, you can review the similar article.
    http://kevine323.blogspot.com/2013/08/setting-up-aliases-on-sql-server-with.html

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Praveen Rayan D'sa Friday, March 7, 2014 4:05 AM
    • Marked as answer by Sofiya Li Thursday, March 13, 2014 2:59 AM
    Friday, March 7, 2014 3:42 AM