locked
SQL Express 2008 R2 post Visual Studio 2010 Installation RRS feed

  • Question

  • Hello SQL Express Forum,

    I had a proper installation of SQL Express 2008 R2 (x64) with Management Studio (x86). I installed it with the default instance name of MSSQLSERVER, all was going well and how please I was with this set up. In the Sql Server Configuration Manager I had the following SQL Server Services;

    SQL Server (MSSQLSERVER)

    SQL Server Browser (MSSQLSERVER)

    SQL Server Full-text Filter Daemon Launcher (MSSQLSERVER)

    SQL Server Agent (MSSQLSERVER)

     

    A few days ago I installed Visual Studio 2010 Professional with Service Pack 1, amazed by the installer not allowing to deselect SQL Express with this version, I continued with the installation. All still works but I now have some anomalies;

    1) I now have 2 SQL instances, MSSQLSERVER which is the one I still use, and another one named, SQLEXPRESS which appeared after the VS 2010 SP 1 installation.

    2)  In the Sql Server Configuration Manager, SQL Server Browser (MSSQLSERVER) is not in the SQL Server Services list anymore, but is still in the Services.msc console and I can still Start, Stop, Disable, set to Automatic, Manual, etc from Services.msc.

    The new SQLEXPRESS named instance does not appear in the Sql Server Configuration Manager-SQL Server Services list, but does in the, SQL Server Network Configuration called, Protocols for SQLEXPRESS. It doesn't matter I can't do anything with this instance anyway.

    Running the SQL Server Installation Center (x64bit), and performing repairs, or add remove features for an instance, doesn't do anything now (maybe nothing is wrong), and I cannot access the instance SQLEXPRESS to change it, it only allows me to make changes and view  the MSSQLSERVER named instance. When prompted or asked which instance would I like to configure the list displayes both, but the dropdown list to select an instance only has the MSSQLSERVER instance.

    My questions being;

    1) Is it possible to remove the named instance SQLEXPRESS by other means like registry alterations, or access this instance somehow to alter its' attributes?

    2) Is there someway of getting SQL Server Browser (MSSQLSERVER) back into the Sql Server Configuration Manager-SQL Server Services list?

    I'm using Windows 7 Ultimate x64 SP1 with most current updates.

    Thanks and Regards,

     - BotRot

     

     


    BotRot
    • Edited by nj-c Saturday, May 14, 2011 10:57 PM Forgot to mention something.
    Saturday, May 14, 2011 10:54 PM

Answers

  • hi,

    >1) Is it possible to remove the named instance SQLEXPRESS by other means like registry alterations, or access this instance somehow to alter its' attributes?

    you should be able to uninstall the SQL Server (SQLExpress) instance accessing the standard "add remove program" OS's applet, where you should select "SQL Server 200x" entry.. the standard SQL Server installation center applet will guide you through the uninstall.. select "remove",  and proceed.. you will be then prompted with the "SELECT INSTANCE" dialog where you have to specify the instance you like to remove.. you can follow a simple "how to" in my own experiences described at http://www.asql.biz/Articoli/SQLX08/Art1_7.aspx .. in the "feature selection", only set to remove the relational database engine service, full text search and reporting service (if installed) and not the shared tools

    >... and I cannot access the instance SQLEXPRESS to change it, it only allows me to make changes and view the MSSQLSERVER named instance. When prompted or asked which instance would I like to configure the list displayes both, but the dropdown list to select an instance only has the MSSQLSERVER instance..

    where and how do you do it?... if you are trying to connect to that named instance, say, via SQL Server Management Studio, you have to provide the full instance name, which is composed by "ComputerName\InstanceName", resulting in "(Local)\SQLExpress" for a local connection to a SQLExpress named instance..

    on the other side, the "MSSQLSERVER" instance you see in your SQL Server Configuration Manager tool, identifies a "default instance" and not a "named" one, and this is available via the "ComputerName" (or "(Local)", or ".") identifier for all connecting clients...

     

    >2) Is there someway of getting SQL Server Browser (MSSQLSERVER) back into the Sql Server Configuration Manager-SQL Server Services list?

    actually, SQL Browser should not have an associated instance setting, as it is a "shared component" among all installed instances.. so you should have a "SQL Server Browser" entry and not "SQL Server Brower (MSSQLSERVER)" one... by default, "default instances" (like the one you seem to have) are set to use TCP/IP 1433 port, the traditional and "well known" port associated by IANA to Microsoft for SQL Server service.. on the other side, "named instances" are (by default) set to use a dynamically associated port.. when the instace starts up, it checks and find an available free port and registers it in the local registry... at next startup it checks if that port still is free and available, or it finds and register a new one.. this solution is a simple method to bypass server configuration and to allow "simple" remote connections... but... for this to work, something must resolve the port redirection... loosely speaking, SQL Server Browser is a "service resolution" tool that is in charge to intercept incoming connection (on UDP 1434 port), check the appropriate assigned port and redirect the incoming trafic to the port currently used by the SQL Server instance.. you can set up the instance (even the default instance) to listen on a "static associated port", where you specify it accordingly to your needs.. in this case, you can (and should) disable SQL Browser but you have to "instruct" your connections strings about the port to be used, or define and set appropriate "ALIAS" in each and every remote client machine..

    so, try removing the unrequired SQLExpress instance and perhaps repair the formerly installed default instance...

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    • Marked as answer by nj-c Sunday, May 15, 2011 11:42 AM
    Sunday, May 15, 2011 12:16 AM

All replies

  • hi,

    >1) Is it possible to remove the named instance SQLEXPRESS by other means like registry alterations, or access this instance somehow to alter its' attributes?

    you should be able to uninstall the SQL Server (SQLExpress) instance accessing the standard "add remove program" OS's applet, where you should select "SQL Server 200x" entry.. the standard SQL Server installation center applet will guide you through the uninstall.. select "remove",  and proceed.. you will be then prompted with the "SELECT INSTANCE" dialog where you have to specify the instance you like to remove.. you can follow a simple "how to" in my own experiences described at http://www.asql.biz/Articoli/SQLX08/Art1_7.aspx .. in the "feature selection", only set to remove the relational database engine service, full text search and reporting service (if installed) and not the shared tools

    >... and I cannot access the instance SQLEXPRESS to change it, it only allows me to make changes and view the MSSQLSERVER named instance. When prompted or asked which instance would I like to configure the list displayes both, but the dropdown list to select an instance only has the MSSQLSERVER instance..

    where and how do you do it?... if you are trying to connect to that named instance, say, via SQL Server Management Studio, you have to provide the full instance name, which is composed by "ComputerName\InstanceName", resulting in "(Local)\SQLExpress" for a local connection to a SQLExpress named instance..

    on the other side, the "MSSQLSERVER" instance you see in your SQL Server Configuration Manager tool, identifies a "default instance" and not a "named" one, and this is available via the "ComputerName" (or "(Local)", or ".") identifier for all connecting clients...

     

    >2) Is there someway of getting SQL Server Browser (MSSQLSERVER) back into the Sql Server Configuration Manager-SQL Server Services list?

    actually, SQL Browser should not have an associated instance setting, as it is a "shared component" among all installed instances.. so you should have a "SQL Server Browser" entry and not "SQL Server Brower (MSSQLSERVER)" one... by default, "default instances" (like the one you seem to have) are set to use TCP/IP 1433 port, the traditional and "well known" port associated by IANA to Microsoft for SQL Server service.. on the other side, "named instances" are (by default) set to use a dynamically associated port.. when the instace starts up, it checks and find an available free port and registers it in the local registry... at next startup it checks if that port still is free and available, or it finds and register a new one.. this solution is a simple method to bypass server configuration and to allow "simple" remote connections... but... for this to work, something must resolve the port redirection... loosely speaking, SQL Server Browser is a "service resolution" tool that is in charge to intercept incoming connection (on UDP 1434 port), check the appropriate assigned port and redirect the incoming trafic to the port currently used by the SQL Server instance.. you can set up the instance (even the default instance) to listen on a "static associated port", where you specify it accordingly to your needs.. in this case, you can (and should) disable SQL Browser but you have to "instruct" your connections strings about the port to be used, or define and set appropriate "ALIAS" in each and every remote client machine..

    so, try removing the unrequired SQLExpress instance and perhaps repair the formerly installed default instance...

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    • Marked as answer by nj-c Sunday, May 15, 2011 11:42 AM
    Sunday, May 15, 2011 12:16 AM
  • Hello Andrea,

    Thank you very much for your response, one instance was somehow corrupt and removing it ruined my original instance and installation. I was (eventually) able to remove SQL Express 2008 R2 clean out many registry keys and install it all over again. To do so I had to also uninstall Visual Studio 2010 SP 1. Painful but all worked out well and I have a properly working SQL Express with Management Console, Configuration Manager, Reporting Services,...

    Thanks for the cool link, http://www.asql.biz/en/ its been very useful.

    A bit off topic, I can't understand why Microsoft insists on installing SQL Express 2008 R2 without option not to when installing Visual Studio 2010, it also has the effect of ruining IntelliSense for T-SQL in Management Studio. Anyway that another problem for somewhere else.

     

    Thanks again Andrea.

     

    - BotRot


    BotRot
    Sunday, May 15, 2011 11:41 AM