Ulock read only database

  • Hi list,

    I restored a database with norecovery mode for the purpose database mirroring. How can I change the satae of this database so I can read again?

    Thanks indeed

    Niyala

  • restore database dbname with recovery

    brings a database online again, but did you actually want to know how to failover a mirrored database?

    ---------------------------------------------------------------------

  • Thanks indeed. How to failover was my next issue that I wanted to test. Unfortunately my mirroring attempt fails when I use the following script.

    -- Change the recovary model to full.

    USE [master]

    GO

    ALTER DATABASE [myDB] SET RECOVERY FULL WITH NO_WAIT

    GO

    -- Create a full backup of the database

    USE myDB

    GO

    BACKUP DATABASE myDB

    TO DISK = N'E:\backup\myDBDATA\myDB.bak' WITH NOFORMAT, INIT

    GO

    -- Create a transaction log backup

    BACKUP LOG [myDB] TO DISK = N'E:\backup\myDBDATA\myDB.trn' WITH NOFORMAT, INIT

    GO

    On the Mirror server

    RESTORE DATABASE [myDB]

    from disk = '\\Server_1\E$\Backup\myDBDATA\myDB.bak'

    WITH NORECOVERY, REPLACE

    RESTORE LOG [myDB]

    FROM DISK = '\\Server_1\E$\Backup\myDBDATA\myDB.trn'

    WITH NORECOVERY

    On the principal

    CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP (LISTENER_PORT = 5022)

    FOR DATABASE_MIRRORING (ROLE = PARTNER)

    GO

    On the mirror

    CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP (LISTENER_PORT = 5022)

    FOR DATABASE_MIRRORING (ROLE = ALL)

    GO

    ALTER DATABASE myDB

    SET PARTNER = 'TCP://Server_1.xxx-ge.net:5022'

    GO

    The following step fails with the following error

    On the principal

    ALTER DATABASE myDB

    SET PARTNER = 'TCP://Server_2.xxx-ge.net:5022'

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://PDIESS01SQ0012.POLIZEI-BW.DE:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    Please I appreciate if you could give me a hint on how to solve this problem.

  • have you tried setting up endpoints via the GUI?

    check ports are not in use by any other process

    ---------------------------------------------------------------------

  • Thanks. I used the gui. This ports are not used by other applications. I managed to do the mirroring using the same port using the wizard. I then deleted the endpoints (DROP endpoints mirroring).

    Then I repeated the procedure using the script. Now neither the script nor the wizard can do th mirroring.

    Need hint please

    Regards

    Niyala

  • undoing it when you had it working might have been shooting yourself in the foot. run

    alter database dbname set partner off

    to remove all traves of mirroring and try through the GUI again.

    ---------------------------------------------------------------------

  • Thanks, but I am more interested in getting the script running, as I have to mirror 100 +- servers.

    Grateful for any hint

    Regards

    Niays

  • I think you are missing the grant connect statement to the endpoints.

    ---------------------------------------------------------------------

  • else, set up just one via the GUI and then use the script option on the mirroring tab of properties to create the base script for you.

    ---------------------------------------------------------------------

  • Hi,

    Thanks for your message. I tried to script the action, unfortunately this action can not be scripted using the scripting button. Is there another means to script?

    Regards

    Niyala

  • Hi,

    Thanks again. I was wondering if you could give me information or guidelines about how to failover. I have not witness. I have only the principal and the mirror. I can manually failover the databases, but how about if the principal crushes?

    Thanks a lot.

    Regards

    Niyala

  • sorry no. have you tried BOL - search on 'Setting Up Database Mirroring (Transact-SQL)'

    Otherwise it looks like there is a problem with the server name or port.

    ---------------------------------------------------------------------

  • Hi,

    Thanks a lot, I solved this problem. I do not know what was going wrong, all I did was to reinstall sql server on both machines.

    My open question is how to use the mirror database in case the principal fails. Ok I can do restore. Is there additional step to do?

    Thanks indeed.

    Niyala

  • Niyala (7/2/2009)


    Hi,

    Thanks again. I was wondering if you could give me information or guidelines about how to failover. I have not witness. I have only the principal and the mirror. I can manually failover the databases, but how about if the principal crushes?

    Thanks a lot.

    Regards

    Niyala

    You have two options if the principal is totally unavailable. Alter database set partner off followed by bring database online

    or

    alter database set partner force_service_allow_data_loss

    which you use depends on how long you think the principal will be down and whether you want the mirror to become the principal moving forwards. there is a risk of data loss if you are using high performance mode.

    you will also need to point the client to the new principal server

    ---------------------------------------------------------------------

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply