Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirror on three instances on one PC


Mirror on three instances on one PC

Author
Message
n.heyen
n.heyen
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 456
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
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3563 Visits: 3104
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.



Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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" ;-)
n.heyen
n.heyen
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 456
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search