I have a situation that I encountered with one of my clients, that I have never seen before. They are running SQL Server Express 2008 R2. There are two instances of SQL Server installed - a default instance on DATA-SVR, and a named instance on DATA-SVR\Product.
When trying to investigate a problem on the system, I checked the data link files on all workstations, and saw that some users were connecting to DATA-SVR, and others were connecting to DATA-SVR\Product. They were able to do this, because both instances have exactly the same databases. Not good, to say the least.
However, when I checked the database paths between the two instances, they are both pointing at the same data files. I checked that this was the case by creating a table in DATA-SVR, and then accessing the same table from DATA-SVR\Product. They're definitely pointing at the same data, so catastrophic failure was averted. However, to clean things up, I tried detaching the databases from the DATA-SVR\Product instance so that there was only one instance to connect to, but this also detached the database from the default instance.
We have a service running on the server, which for some reason is only able to access the named instance, and not the default instance, so I've now changed all users to use the named instance. Everything is working now. However, I'm still uneasy about how this situation could have come about.
Can anyone shed some light on what is happening here, and how to fix it (preferably without reinstalling the database server)?