locked
SQL Server 2012 Standard Edition RRS feed

  • Question

  • Hi Experts,

    I've recently purchase 2 copies or SQL Server 2012 Standard edition thinking that I would be able to run database mirroring between 2 instances.

    I've setup 2 instances of SQL 2012 Standard (principle and mirror) and 1 instance of SQL Express (witness) and have backed up and restored all my databases to the 2nd instance in preparation of mirroring.

    Now when I go to 'Task' --> 'Mirror', I don't seem to get the option to 'Configure Security' and hence cannot move forward with the mirroring.

    Has this option been removed by Microsoft or am I just missing something?

    Please advise.

    Kind Regards

    GMSS 




    • Edited by GMSS Saturday, February 11, 2017 2:15 PM
    Saturday, February 11, 2017 2:15 PM

Answers

  • Hi GMSS,

    When you upgrade it to SQL Server Standard, it should be recognized as Standard edition instead of latching on to SQL Express.

    Based on the error message, could you please run the following command?

    SELECT DATABASEPROPERTYEX('DBNAME','IsAutoClose')

    If it returns 1, it is an auto close database, please run the following command:

    ALTER DATABASE [DBNAME] SET AUTO_CLOSE OFF

    If it returns 0, you can take a full backup of the database and then restore it on the instance and create database mirroring again.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by GMSS Thursday, March 2, 2017 7:17 PM
    Thursday, February 16, 2017 7:55 AM
  • There are several possibilities here.

    First think to check is to ensure that if you are mirroring on the same machine that each instance uses a different port. For some reason you are using port 5023 as opposed to the default of 5022. Is 5022 is use?

    Second thing to check is that the ports you are using are open for outbound communication on your principal and inbound communication on your mirror. You need to adjust both firewalls.

    Third thing to check is that the end points have the correct permission on them.

    It would be like this:


    GRANT CONNECT on ENDPOINT::Mirroring_Endpoint TO [domain\Account]; 
    GO 

    • Proposed as answer by Teige Gao Monday, February 20, 2017 9:09 AM
    • Marked as answer by GMSS Thursday, March 2, 2017 7:17 PM
    Monday, February 20, 2017 2:43 AM

All replies

  • Did you restore database 'WITH NO RECOVERY' option and also restore one transaction log backup 'WITH NO RECOVERY' option?

    BTW, database mirroring feature will be deprecated in future version of SQL Server and is not recommended to be used.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Saturday, February 11, 2017 3:57 PM
    Saturday, February 11, 2017 3:54 PM
  • Hi,

    Synchronous mirroring runs perfectly well on SQL 2012 Standard, asynchronous requires SQL 2012 Enterprise.

    Be that you should take a full backup + a log backup of the principal database, and restore them both with

    no-recovery on the mirror side.

    Once that's done, you can start mirroring.

    Saturday, February 11, 2017 3:59 PM
  • I am not sure what you mean about configure security, Right click on the database, select properties and mirroring and you should see the configure security dialog.

    Saturday, February 11, 2017 4:01 PM
  • Hi Guys,

    Thanks for your response. 

    I have done the restore 'WITH NO RECOVERY' option and have also restored one transaction log backup 'WITH NO RECOVERY' option too. 

    I have 4 databases that I've done a backup and restore 'WITH NO RECOVERY' option. All 4 databases on the 2nd SQL instance is still saying 'Restoring'. Here is a screen shot of it:

    

    When I right click on a database in principal instance, go to 'Task' --> 'Mirror' option is there:

     

    However when I click on the 'Mirror' option, the 'Configure Security' button which should be at the top right as illustrated by Mr Hilary Cotter is not there. Nor do I see the Mirroring option on the left hand pane under 'Select a Page'. Here is a snapshot:

    Could Microsoft have deprecated the Mirroring option already...? if so, than why is mirroring one of the selections in the 'Tasks' option....?

    Anymore ideas on what I could try in order to get this mirroring going...?

    Kind Regards

    GMSS


    • Edited by GMSS Saturday, February 11, 2017 5:09 PM
    Saturday, February 11, 2017 4:51 PM
  • Your reporting services databases seem to indicate that you are running SQL Server express and not SQL Server Standard edition.

    Please run this and post the results back here.

    select @@Version

    Saturday, February 11, 2017 4:59 PM
  • Hi Hilary,

    It was SQL Express and the previous admin left the name as default. I have since upgrade it to SQL 2012 Standard. Here is the result of the command ran:

     

    Kind Regards

    GMSS


    • Edited by GMSS Saturday, February 11, 2017 5:11 PM
    Saturday, February 11, 2017 5:09 PM
  • Hi Hilary,

    Hope you can see it. but if not here is a zoomed in version:

    Kind Regards

    GMSS

    Saturday, February 11, 2017 5:14 PM
  • The Dialog is for SQL Server Express, and your SQL Server Agent is not started.

    I am wondering if you are using the correct version of Management studio.

    Can you try to use tsql to configure your mirroring? Use this link. Ensure that the accounts that SQL server Data Engine runs under is running under the same domain account which is a sysadmin on both servers. This is not absolutely necessary, but makes debugging simpler.

    https://msdn.microsoft.com/en-us/library/ms175883.aspx

    Saturday, February 11, 2017 6:35 PM
  • Hi Hilary,

    I've decided to run the mirroring process from the 2nd instance of SQL (mirror server)  as oppose to the 1st instance (principle server, upgraded to SQL Standard Edition from SQL Express) and in there I was able to go through the process of setting up the mirroring and the option for "Configure Security" is showing.

    However, going through the mirroring wizard, at the very end, this is the error that comes up:

    Could this be down to the principle server (for whatever reason) is still latching on to SQL Express, even though it reports back as SQL Standard when running the "select @@VERSION" command?

    If this is true, can I remove the installation of SQL Express now that it has been upgraded from SQL Express to SQL Standard?

    What will be the repercussions of this and will SQL Standard still remain on the principle server?

    Kind Regards

    GMSS 

    Monday, February 13, 2017 12:00 AM
  • Hi GMSS,

    When you upgrade it to SQL Server Standard, it should be recognized as Standard edition instead of latching on to SQL Express.

    Based on the error message, could you please run the following command?

    SELECT DATABASEPROPERTYEX('DBNAME','IsAutoClose')

    If it returns 1, it is an auto close database, please run the following command:

    ALTER DATABASE [DBNAME] SET AUTO_CLOSE OFF

    If it returns 0, you can take a full backup of the database and then restore it on the instance and create database mirroring again.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by GMSS Thursday, March 2, 2017 7:17 PM
    Thursday, February 16, 2017 7:55 AM
  • Hi Teige,

    Many thanks for your response.

    >>When you upgrade it to SQL Server Standard, it should be recognized as Standard edition instead of >>latching on to SQL Express.

    This is what I thought too, but bizarrely enough I'm still unable to run the mirroring process from the principle server when selecting the 'Mirroring' option, but the 'Configure Security Setting' is not showing on this principle server:

    However, I can see and run the Mirroring process with the 'Configure Security Setting' from the mirror server....

    I have now ran:

    SELECT DATABASEPROPERTYEX('DBNAME','IsAutoClose')

    And it did return a 1.

    As the database is currently in use, I will be conducting this procedure on Friday night which is when it is quieter for my clients. 

    I will keep you informed on how it goes on Friday.

    Many thanks once again.

    Kind Regards

    GMSS

    Thursday, February 16, 2017 12:28 PM
  • Hi Teige,

    I've now ran both these commands:

    SELECT DATABASEPROPERTYEX('DBNAME','IsAutoClose')

    and

    ALTER DATABASE [DBNAME] SET AUTO_CLOSE OFF

    Now when I try to run mirroring,  this is the error it returns:

    I know that SQL02.domain.local:5023 (mirror) is on the network because I'm able to telnet to it from the principle server and I'm also able to ping the FQDN SQL02.domain.local (mirrored) server from the principle server.

    Any ideas how I can get this resolved?

    Kind Regards

    GMSS

    Sunday, February 19, 2017 2:43 AM
  • Hi Experts,

    Any ideas how I might solve this?

    Kind Regards

    GMSS

    Sunday, February 19, 2017 11:35 PM
  • Hi, are your mirroring endpoints correctly configured https://msdn.microsoft.com/en-us/library/ms189127.aspx#Endpoints i.e. started, appropriate connect permissions etc
    Monday, February 20, 2017 12:02 AM
  • There are several possibilities here.

    First think to check is to ensure that if you are mirroring on the same machine that each instance uses a different port. For some reason you are using port 5023 as opposed to the default of 5022. Is 5022 is use?

    Second thing to check is that the ports you are using are open for outbound communication on your principal and inbound communication on your mirror. You need to adjust both firewalls.

    Third thing to check is that the end points have the correct permission on them.

    It would be like this:


    GRANT CONNECT on ENDPOINT::Mirroring_Endpoint TO [domain\Account]; 
    GO 

    • Proposed as answer by Teige Gao Monday, February 20, 2017 9:09 AM
    • Marked as answer by GMSS Thursday, March 2, 2017 7:17 PM
    Monday, February 20, 2017 2:43 AM
  • Hi Hilary,

    I'm using different ports for each instance.

    Principle Server - 5022

    Mirror Server - 5023

    Witness Server - 5024

    I've ran a 'telnet' and a 'netstat -an' on all 3 servers and on each one the ports are listening on. At present windows firewalls are disabled.

    I'm going to be removing the endpoints and try the mirror again tomorrow when the database is less used and get back to you on the result.

    Thanks for the responses thus far.

    Kind Regards

    GMSS 

    Tuesday, February 21, 2017 9:13 AM
  • Hi Guys,

    Sorry it has taken me this long to reply as the databases were heavily used during this time.

    The good news is that I have success now. Here is an article I found and followed.

    http://dba.stackexchange.com/questions/23878/mirroring-server-network-address-cannot-be-reached

    I needed to find out the Display Permissions to Endpoint by executing this:

    SELECT EP.name, SP.STATE, 
       CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
          AS GRANTOR, 
       SP.TYPE AS PERMISSION,
       CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
          AS GRANTEE 
       FROM sys.server_permissions SP , sys.endpoints EP
       WHERE SP.major_id = EP.endpoint_id
       ORDER BY Permission,grantor, grantee; 
    GO

    Once I found out who had permissions, I then a ran:

     GRANT CONNECT on ENDPOINT::Mirroring TO [domain\Account];  

    GO  

    I also had to change the 'Log On As' for services 'SQL Server' and 'SQL Server Agent' on both the principle and mirror SQL server to 'domain\administrator'. The best place to do this is in ' SQL Server Configuration Manager'.

    Once changed, I restarted the services and went through the mirror process.

    Many Thanks again for all your support.

    Kind Regards

    GMSS


    • Edited by GMSS Thursday, March 2, 2017 7:18 PM
    Thursday, March 2, 2017 7:16 PM