June 18, 2009 at 7:55 am
I've got a development box that's running three instances of SQL2005, named TEST1, TEST2, and TEST3. Each is a copy of my live environment.
It's possible to modify the connection strings our various applications use in order to select the correct test instance, but I would rather do the switching at the network level. It would make sandboxing my infrastructure easier to simply connect to a different IP address for each instance, just as if they were each a default instance.
I attempted to set up each instance to listen on it's own IP address, but I was still not able to connect to the server without explicitly specifying the instance.
Has anyone else attempted this? What did you end up doing to get around the requirement to pass the instance name as part of the Data Source token in the connection string?
Thanks!
June 18, 2009 at 8:17 am
I have heard of using diff TCP Port for SQL server instances.. but not using diff IP's. Maybe i should learn more. Let us know what you find out for sure.
http://support.microsoft.com/kb/823938
http://www.sqlservercentral.com/Forums/Topic661028-149-1.aspx
Maninder
www.dbanation.com
June 18, 2009 at 10:34 am
This type of requirement is becoming more frequent with virtualization and consolidation and is easy to solve.
Assume you have
1) TestServer at IP 192.168.2.2
2) TestServer hosts SQL Servers TestServer\Test1, TestServer\Test2 and TestServer\Test3
Actions:
To test, on another computer, add entries to C:\window_directory\system32\drivers\etc\hosts such as
192.168.2.2 Test1
192.168.2.2 Test2
192.168.2.2 Test3
Logon to the TestServer and run the SQL Server Configuration Manager, pick "SQL Native Client Configuration", and then pick "Alias". Add an alias for each name such as an alias of "Test1" and the "ServerName" would be "TestServer\Test1". The alias name must match what was entered in the host file.
Test from the other computer, confirm you can connect to SQL Server specifying "Test1" as the SQL Server name.
After confirming, then add the fully qualified alias names to DNS and remove from the host file.
SQL = Scarcely Qualifies as a Language
June 18, 2009 at 2:27 pm
Thanks for your help... I think I've found a solution!
Next question: I was able to restore one instance of my live environment to my test box with no issues whatsoever. Everything works great!
No such luck with the second and third instances. The first one worked because the data and log files for all the system databases were at the same path as on the live server. After restoring master to my second instance, TEST2 (MSSQL.2), the second instance won't start. Turns out it's looking for tempdb, model, and msdb in the same path as my first instance (MSSQL.1). Is there any way to modify a master database when the instance won't start? How about a way to ALTER the database after the master restore but before SQL server auto stops?
June 18, 2009 at 2:34 pm
Andy McKenzie (6/18/2009)
Thanks for your help... I think I've found a solution!Next question: I was able to restore one instance of my live environment to my test box with no issues whatsoever. Everything works great!
No such luck with the second and third instances. The first one worked because the data and log files for all the system databases were at the same path as on the live server. After restoring master to my second instance, TEST2 (MSSQL.2), the second instance won't start. Turns out it's looking for tempdb, model, and msdb in the same path as my first instance (MSSQL.1). Is there any way to modify a master database when the instance won't start? How about a way to ALTER the database after the master restore but before SQL server auto stops?
Here we go
Moving System databases
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
June 18, 2009 at 2:36 pm
Thank you! That is very slick.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply