0

Scenario:

  • Single SQL Server with 2 instances -> .\test & .\live
  • Single IIS server with 2 websites -> test & live

test website's web.config points to the .\test SQL server instance, live website points to .\live. The SQL Server instances are identical in every way, i.e. DB names, SQL username / password, etc. The only way they differ is by the ports they listen on.

We have the following code deployed to the test website:

_logger.Debug("connection string: " + _context.Database.Connection.ConnectionString);

_context.Database.ExecuteSqlCommand("stored_procedure @param");

When we check the logfile after this code has executed, it shows that the connection string is pointing to the .\test instance, as expected. But a SQL Profiler trace shows that stored_procedure is being executed against the .\live instance.

We can't figure out why or how this is happening, any help?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ian Kemp
  • 28,293
  • 19
  • 112
  • 138

1 Answers1

1

The missing piece of the puzzle was the ports. As per the comments on this answer, MSSQL ports and instances are the same thing... and port overrides instance name if specified.

Our connection strings looked like the following:

test: Data Source=server\test,7777;Initial Catalog=ourdb;Persist Security Info=True;User ID=user;Password=pass
live: Data Source=server\live,7777;Initial Catalog=ourdb;Persist Security Info=True;User ID=user;Password=pass

So although they were pointing to different instances, the same port overrode that and forced them both to effectively point at whatever was running on port 7777 - i.e. the .\live instance, hence the anomalous behavior we experienced.

Community
  • 1
  • 1
Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
  • 1
    http://technet.microsoft.com/en-us/library/hh510203.aspx gives a pretty reasonable overview of how instance names and ports correlate. – ta.speot.is Feb 12 '14 at 09:38