SQL Server Performance

database mirroring - mirror to principal

Discussion in 'SQL Server 2005 Database Mirroring' started by priyaram, Jul 30, 2007.

  1. priyaram New Member

    we want to migrate our production server , without taking the database down.so what we did was , we mirrored the databases from production to one of our development servers and we took the prodcution server for uprgrade.
    so today we are moving back (mirroring) all the databases from that development server to our new production server.
    my question is , when i move
    productionserver_old(principal server) - having the script for principal server
    development server(mirror server---->principalserver)) - having the script for mirror server , which is now acting as principal server
    new production server(mirror server) - having the script for mirror server. will become principal server , once i moved all the databases
    now while mirroring between the developement server and the new production server, i have to run the scripts for the mirror server only.
    so both the servers are havign the mirror script now...
    is that a problem, or any other way i can handle this....
  2. satya Moderator

    Seems like a confusion in the process, as the databases will be in production why don't you set the database mirroring once they are set on live server and choose the development as mirror using wizards. If you have scripts then it is better to test them before ensuring all are going well.
    Take a closer look at the sql error log in this case for any issues, before doing any configuration changes ensure you have complete backup.
  3. priyaram New Member

    sathya,
    suppose , we are setting up the mirroring between the 2 servers , which both are production.
    in server 1, we are having 3 user databases , we are going to use server 2 for these failover.
    server 2, we are having 5 user databases , we are going to use server 1 for failover.
    will this be possible? , in this case we have to run both the scripts in both the servers right??
  4. satya Moderator

    Yes it is possible, as the database mirroring is set to databases alone. Also you need to take care about the endpoint creation that should be unique to the databases on 1 server alone.
    During the failover scenario you might have single-point of failure where only 1 server will be availble, so you have to consider the 3 rd server to do additional mirror in this case.
    BTW what type of DB mirroring mode you are chosing?
  5. priyaram New Member

    i am planning to do a high safety mode morroring(since itll be commited at both servers.)
    i didn't get your concept of third server, can you explain a little bit about it
  6. satya Moderator

    Third mirror concept is nothing but having additional ability in case one of the server is totally out of network, where you will be in a single-point-of-failure position for having only 1 server for critical applications. In this scenario you could simply mirror the databases from these 2 servers to the 3rd server as an additional feature in case to be used when it is required.
    Hope this is clear.
    Further on this high-safety mode using without automatic failover due to the reason of having other databases in principal server that are mirrored from other server.
  7. priyaram New Member

    but dring our process of miroring, i am having the script for mirror server(which is now the principal server).and also it's having some databases mirrorored and now the mirroring for those databases has been remioved and it's now the pricipal database.
    so now i am going to start everything as new , shall i drop master key,certificate and endpoint from that server.
    and rerun the script for principal in that server
    will that be work...
  8. satya Moderator

    Good point, it is better to recreate everything afresh on the endpoint too, as there are changes to the db mirroring settings here.
    Otherwise you might get few issues later due to such mismatch of configuration. Don't forget to take complete backups for system and user database before doing any thing here.
  9. priyaram New Member

    sathya,
    since i am having some databases mirrored and failed over to the mirror server using the master key encrption and principal and mirror certificates. will it create any problem while dropping them all , and recreating the endpoints and mirroring with out the certificates for that server and that databases ....
    CREATE ENDPOINT EndPointName
    STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
    FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
    Set partner and setup job on mirror server
    ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:portNumber'
    EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
    Set partner, set asynchronous mode, and setup job on principal server
    ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:portNumber'
    ALTER DATABASE DatabaseName SET SAFETY OFF
    EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute

Share This Page