Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirror on three instances on one PC Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 10:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:09 PM
Points: 86, Visits: 366
Trying to get developer edition 2008R2 to mirror AdventureWorks with a monitor as an experiment on my PC. Is it possible to do this on one PC or do I need to setup VM's?

I have three instances installed. AdventureWorks on 'MSSQLSERVER', a copy of AdventureWorks in recovery state on 'TWO' and 'THREE' setup as the monitor. The listener ports default to 5022, 5023 and 5024. SQL Server Browser is running.

Been playing with this for a couple of days and just not getting it to work. I've tried to follow using the wizard and I always get to the point of starting to synchronize, then an error saying

"TCP://desktop:5023" can not be reached or does not exist.

TCP is enabled on all three instances and services restarted.

I just have the home version of Windows 7 so no domain.

Is this possible? If so, any hints to what I need to do to make this work?

I'm thinking I need to do something with the listener ports but not sure what. Instance 'MSSQLSERVER' (the default) is set to port 1433 and 'TWO' and 'THREE' are dynamic.

Thanks!
Norman
Post #1456600
Posted Sunday, May 26, 2013 9:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 2,993, Visits: 2,590
Yes - this is possible on a single PC. The trick is that you need to set the primary DNS suffix for your PC for mirroring to work.

Normally, your pc will have a name like "MyName" instead of something like what you would see at work which would be something like "MyName.MyCompany.com".

If you set the primary DNS suffix to something like "local" so that your PC full name becomes "MyName.local", mirroring will be happy.

Now, For Windows 7 (which is what I am running) to do this,
- Open Windows Explorer
- Right click "Properties" on Computer
- Select "advanced system settings"
- You should see a button called "Change" (next to "To rename this computer or change its domain or workgroup)
- Click "Change"
- in the "Computer Name/Domain Name Changes" screen, click "More"
- you should see an option to set the Primary DNS suffix.
- Enter "local" and click OK

I am not sure but you may need to restart after this. You will be prompted if this is required.

For other versions of Windows, the equivalent of this is what you need to do.




Post #1456960
Posted Tuesday, May 28, 2013 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,358, Visits: 13,688
To mirror in a workgroup can be acheived with minimal effort using this post.
However, it's a little more complex, but the best way is to use certificates as detailed here.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1457365
Posted Friday, May 31, 2013 12:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:09 PM
Points: 86, Visits: 366
Thank you Perry for the solution - works great. It has been one of those weeks that makes a DBA know they are not paid enough...

I am now happily playing with Adventure Works in a mirror configuration on my home desktop machine. Yes, it works as advertised without the certificates. And I admit getting spoiled working in an environment with AD running reasonably well... Something I'm not ready to set up at home for various reasons.

I set all the SQL instances to run under my Windows 7 administrator account and all is well. Just didn't make the connection that a single computer is the same as a workgroup - it has been a long time since I've really needed to even consider that.

Norman
Post #1458837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse