SQL Server, convert a named instance to default instance?
As far as I know, no. One reason is the folder structure on the hard drive; they will have a name like MSSQL10.[instancename]
This is why a lot of companies store their applications' connection strings at the machine level instead of the application level.
Just take the connection string out of the source code entirely. Then have everyone put their connection string in their machine.config.
This has the added benefit of avoiding unnecessary app-specific environment logic, i.e. when you copy your application to the staging server, the staging server already "knows" what database to use.
I also wanted to convert a named instance to default - my reason was to access it with just the machine name from various applications.
If you want to access a named instance from any connection string without using the instance name, and using only the server name and/or IP address, then you can do the following:
- Open
SQL Server Configuration Manager
- Click
SQL Server Network Configuration
- Click
Protocols for INSTANCENAME
you want to make available (i.e. SQLExpress) - Right-click
TCP/IP
and clickEnabled
- Right-click
TCP/IP
and go toProperties
- Go to the
IP Addresses
tab - Scroll down to the
IPAll
section - Clear the field
TCP Dynamic Ports
(i.e. empty/blank) - Set
TCP Port
to1433
- Click
Ok
- Go to the
- Go to
SQL Server Services
- Right-click your
SQL Server (INSTANCENAME)
and clickRestart
This will make the named instance listen on the default port. Note : You can have only one instance configured like this - no two instances can have same port on the IP All section unless the instance is a failover cluster.