locked
Limitations of Names Instance RRS feed

  • Question

  • Hi Team,

    I wonder if there are any limitation on functionality or in any terms in named instance of SQL server compare to Default instance.

    Thanks


    Swapna

    Tuesday, September 16, 2014 10:23 PM

Answers

  • Yes true, but what you can do is, just create one new instance in on spare server and then restore all the DB's then you can uninstall this current default instance and then you can move all the objects and database from that Temp sql instance..

    Yes its bit risky, because sometimes we dont know what is working and what is not working if we remove the current instance completely without any control..

    Creating temp sql instnace may take bit hardware and other resoruces but we have to deal it carefully, there should be some control always..

    Otherwise verify all the backups are vaild and take backup of all the logins, jobs , configurations, linked serevr details, keep a note of Maintenance plans details, just restore the backups in either test or Dev servers and check you can able to restore the database without any issues and valid, this is to double check everything that you are in safe soon, then you can directly go ahead with your plan, but you have to plan it well...


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by SQL DBA1 Tuesday, September 16, 2014 11:35 PM
    Tuesday, September 16, 2014 11:26 PM

All replies

  • Hello,

    In terms of the product, no there aren't any 'limitations' by using a named instance.

    The only drawback I have found is that some 3rd party software doesn't take this into account and I have had to use some workarounds to get past it.


    Sean Gallardy | Blog | Microsoft Certified Master

    Tuesday, September 16, 2014 10:38 PM
  • Thank you Sean,

    I have SQL 2008 R2 environment and need to be upgraded to 2012 soon. I need to get Default instance of new version as well. Is there any best way other than Inplace upgrade?

    Thanks


    Swapna

    Tuesday, September 16, 2014 10:51 PM
  • Named instances server name is long, And named instance is identified by a name with the format computer_name\instance_name, and you cant use 1433 port, and you have to user another port incase 1433 is used by default instance..

    Most of the time you may ended up having problems with third-party software not connecting.  It's hard to get them to change their ways.  Developers can be told how to connect, and they connect.  Now, getting them to change existing code is another matter.

    If the application can handle it, then there is really no difference between a default instance and a named instance of SQL Server on a server providing there is only one instance on it and you are using the default port number.

    There is a work around to get a shorter name.Its creating an 'alias' on the client machine. But lots of clients mean lots of manual intervention unless your network admins are adept at SMS rollouts. Also, it's another 'moving part' in the application that can be broken.

    The only time I can see named instances as a benefit is when one has multiple SQL Server instances on a large and powerful server.

    In old versions Microsoft CRM 3.0 until just recently only supported default instances and not with Named instances.

    By default, the instance name is used as the Instance ID. Instance ID is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances...

    http://msdn.microsoft.com/en-us/library/ms143531%28v=sql.120%29.aspx


    Raju Rasagounder Sr MSSQL DBA

    Tuesday, September 16, 2014 10:51 PM
  • Thank you Raju,

    Because of application brokage issue we are planning to do the inplace update, but also thinking the alternate option by taking backup of Databases and other objects. But uninstall the default instance by relying on backups is not best practice I feel. What you suggest.

    Thanks


    Swapna

    Tuesday, September 16, 2014 11:11 PM
  • Yes true, but what you can do is, just create one new instance in on spare server and then restore all the DB's then you can uninstall this current default instance and then you can move all the objects and database from that Temp sql instance..

    Yes its bit risky, because sometimes we dont know what is working and what is not working if we remove the current instance completely without any control..

    Creating temp sql instnace may take bit hardware and other resoruces but we have to deal it carefully, there should be some control always..

    Otherwise verify all the backups are vaild and take backup of all the logins, jobs , configurations, linked serevr details, keep a note of Maintenance plans details, just restore the backups in either test or Dev servers and check you can able to restore the database without any issues and valid, this is to double check everything that you are in safe soon, then you can directly go ahead with your plan, but you have to plan it well...


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by SQL DBA1 Tuesday, September 16, 2014 11:35 PM
    Tuesday, September 16, 2014 11:26 PM