Sql-server – Is it Possible to have Multiple Named Instances with No Default Instance

There is an advantage to having separate SQL Server instances however do those advantages outweigh the cons? You'll have to look at how you would use those instances and decide for yourself.

Advantages

1. Allocate/limit resources that each instance has access to, specifically you can limit memory and CPU on the Dev instance so that the Live instance will not be 'overly' affected while you make mistakes in Dev...

2. Data separation: by having separate instances if you want to share data between Live and Dev you would need to explicitly set up a Linked Server, if they aren't separated you can just refer to the live db (assuming you've given the same user permission)

3. Ease of development: you can have db's named the same thing on difference instances, ie: dev can be a structural replica of Live (which seriously reduces the development complexities)

Disadvantages

1. Heavy resource usage: As others (and yourself) have pointed out, having two instances of the same services will consume more of the resources, which will reduce the amount of resources that can be used by SQL Server to actually do the work the DB is for....

Note: The benefits of a separate instance would probably be better if you instead created a separate VM for your Dev DB environment. This would have the added benefit that when/if you can get another physical server you can move your VM without having to change any connection settings...

I don't think it is possible to upgrade SQL Server Express to another edition. You should be able to use all the features of SQL Server you mentioned (agent and profiler) with the new, named instance (DEVEDITION) along side of Express Edition.

I don't think its possible to rename a SQL Server instance name, at least not with a bunch of headache.

What I would do is:

• Detach the dbs connected to your SQL Server Express instance and uninstall SQL Server Express. Then re-install the dev edition to the default instance and reattach the dbs.