none
unable to find Mirroring option in sql server 2008 R2 (SP1)

    Question

  • I have recently install 3 instances of SQL SERVER 2008 R2 and try to setup Mirroring but there is no mirroring option available on database property. I have then install SP1 as well. still no luck. 

    have some idea?

    I have Windows 7 , SQL SERVER 2008 R2, SP1 Build 7600

    This is the Version Information

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 6:20 AM

Answers

  • Thanks bro... 

    I got the problem just before you post and problem is same. I did following steps for installation on the machine

    - visual Studio 2010 with SQL SERVER Express and it installs Management Studio

    - Install all 3 instances of SQL SERVER but did not check Management Tools -  Complete

    Now i have tried to connect this server to another machine's Management Studio and i am able to view Mirror option.

    thanks for your information and help..


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    • Marked as answer by Shamas Saeed Wednesday, March 28, 2012 8:38 AM
    Wednesday, March 28, 2012 8:38 AM
  • Here is a link to setup Database Mirroring using SQLCMD Scripts:

    http://sudeeptaganguly.wordpress.com/2012/03/06/database-mirroring-with-sqlcmd/

    If you installed all the instances on a single machine, then you need to change the Database Mirroring endpoint ports; however if you installed it on separate machines, then the above script will configure the database Mirroring for you.

    If you want to use Database Mirroring with Automatic Failover, check the following post:

    http://sudeeptaganguly.wordpress.com/2010/07/25/how-to-configure-database-mirroring-in-an-active-directory-environment-using-sql-server-2008/

    Let me know, if this helps :)


    SKG: Please Marked as Answered, if it resolves your issue.

    • Marked as answer by Shamas Saeed Wednesday, March 28, 2012 8:41 AM
    Wednesday, March 28, 2012 7:23 AM
  • Last thought, do you connect to that database with the same login/user as you do to the database where you do see Mirroring? Is that possible permissions? Moreover , did you install e "Management Tools - Complete" component  I think it should fix the problem?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Shamas Saeed Wednesday, March 28, 2012 8:38 AM
    Wednesday, March 28, 2012 8:35 AM

All replies

  • Here is a link to setup Database Mirroring using SQLCMD Scripts:

    http://sudeeptaganguly.wordpress.com/2012/03/06/database-mirroring-with-sqlcmd/

    If you installed all the instances on a single machine, then you need to change the Database Mirroring endpoint ports; however if you installed it on separate machines, then the above script will configure the database Mirroring for you.

    If you want to use Database Mirroring with Automatic Failover, check the following post:

    http://sudeeptaganguly.wordpress.com/2010/07/25/how-to-configure-database-mirroring-in-an-active-directory-environment-using-sql-server-2008/

    Let me know, if this helps :)


    SKG: Please Marked as Answered, if it resolves your issue.

    • Marked as answer by Shamas Saeed Wednesday, March 28, 2012 8:41 AM
    Wednesday, March 28, 2012 7:23 AM
  • Database mirroring is supported in SQL Server Standard Edition and Enterprise Edition... Can  you post a print screen?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 28, 2012 7:29 AM
  • thanks i am able to see Mirror link in database task but there is no mirror option in database properties. see attached image.

    i have another server with same sql server but its version is 1627 and it shows mirror option in database properties. is there any difference.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 7:35 AM
  • Yes i have all 3 instances on same server. thanks for endpoint information. Let me install mirror setup and i will post my answer. 

    thanks


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 7:39 AM
  • Shamas,

    Did you check the recovery model of the database? Only Full recovery model is supported.

    And Check the screenshot from Uri's reply, you can find it within Task option.


    SKG: Please Marked as Answered, if it resolves your issue.

    Wednesday, March 28, 2012 7:47 AM
  • Yes i have tried but Task option returns same screen i have attached above. 

    Option is Full and AutoShrink is true


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 7:48 AM
  • Is that possible you are seeing an EXPRESS Edition??

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 28, 2012 7:55 AM
  • select @@Version

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

    is this helpfull


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 7:58 AM
  • On another machine i am able to see both options and its version is

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor) 

    Is this helpfull


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 8:08 AM
  • You have a mismatch in SQL Server Patch version, the above screenshot says Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) and the first one says Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    One more thing, are you trying the same database? The size of the database on both the screen are different too.

    Could you please mention, the steps you have followed till now?


    SKG: Please Marked as Answered, if it resolves your issue.

    Wednesday, March 28, 2012 8:25 AM
  • I am trying to setup mirror on a machine which shows version 

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    and there is another machine which have already have mirror setup and that shows 

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)

    that is why i have uploaded both screen shots. I am also confused why i am not able to setup on first machine.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 8:28 AM
  • Last thought, do you connect to that database with the same login/user as you do to the database where you do see Mirroring? Is that possible permissions? Moreover , did you install e "Management Tools - Complete" component  I think it should fix the problem?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Shamas Saeed Wednesday, March 28, 2012 8:38 AM
    Wednesday, March 28, 2012 8:35 AM
  • Thanks bro... 

    I got the problem just before you post and problem is same. I did following steps for installation on the machine

    - visual Studio 2010 with SQL SERVER Express and it installs Management Studio

    - Install all 3 instances of SQL SERVER but did not check Management Tools -  Complete

    Now i have tried to connect this server to another machine's Management Studio and i am able to view Mirror option.

    thanks for your information and help..


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    • Marked as answer by Shamas Saeed Wednesday, March 28, 2012 8:38 AM
    Wednesday, March 28, 2012 8:38 AM
  • All right, here is what you should do:

    Consider your First Machine as Machine A : Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    Second Machine as Machine B:                    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)

    First thing, you should do is, install SQL Server 2008 R2 SP1 on Machine B. Then create a database in Full Recovery Model, on any of the server, take a full backup and folloowed by a log backup. Restore it on the other machine, with option NORECOVERY. After that follow the steps mentioned in the above blog post.

    You may also try to configure it using SQLCMD. I have attached a link to that in my first reply, do change the server name, dbname, service accounts, endpoint name, and port number as per your requirement.

    Let me know, if you able to configure it, Hope, this may help :)


    SKG: Please Marked as Answered, if it resolves your issue.

    Wednesday, March 28, 2012 8:39 AM
  • thanks for your help but i have printed Machine B just for an thought that i am able to setup mirror on this machine. But issue is about SSMS and its new for me...

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, March 28, 2012 8:41 AM
  • Shamas Saeed,

    I have the same exact problem. Everyone involved in this post chain are helpful but they are not listening to you sadly. I wish if someone would read your comment you will find answer fast. 

    Your problem is not that you don't know how to setup mirror ( NOTE: no mirror setup help please )
    In your windows server when you install SQL Server express first, it installs the management studio and as you know you cant do mirror with this version. NOW you go ahead and installed new instances of SQL server that support mirroring in the same windows server but unfortunately management studio thinks they are all express version and thus you dont see the MIRROR option in the properties window. People are saying check endpoint, check if database is in full recovery model etc but that's not the issue. If you go to another windows server and use "non express management studio" to log into to the SQL server then you can see mirror option. Im having same exact issue lol.

    I think I will just uninstall express SQL and all other instance and just reinstall from scratch since my issue is on a staging.

    I hope someone carefully ready this post and helps us.

    IF someone wants to simulate this issue then just install sql express on a windows server and then install sql non express on same windows server and you will see when you go on management studio you wont get mirror option for the regular version (non express). Only solution is to go to another windows server and then from that server SSMS into the problem instance and only then you will see the problem. 

    Tuesday, March 18, 2014 8:15 PM
  • I didn't notice the post is from March 2012 so the issue is long resolved by now.

    I have same issue and I resolved it. Management Studio BASIC was installed and because of that you dont see Mirroring option in properties.

    To resolve the issue just run the installer and adding features to existing, make sure to put check mark on "Management Studio Complete"

    Depending on if you have older version or if the installed is different then the parent the Management Studio Complete option might be grayed out and it will instruct you to use the upgrade wizard. You can do that or just use SQL 2012 installer. 


     
    Tuesday, March 18, 2014 11:41 PM