May 24, 2013 at 10:06 am
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
May 26, 2013 at 9:42 pm
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.
May 28, 2013 at 8:51 am
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" 😉
May 31, 2013 at 12:36 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply