locked
remove mirroring between 2 cluster RRS feed

  • Question

  • hello,

    I want to remove the mirroring between 2 sql 2012 cluster (each with 2 node) : I'll configaure always on later.

    Id like to remove the mirronring between the clustres and avoid remove it between databases (I have manu db on each cluster so it will take time to remove mirroring DB per DB

    Thanks

    Wednesday, August 1, 2018 1:19 PM

Answers

  • Thnaks for your reply,

    in this article, we have to remove mirroring between databases and note instance (in each instance I have more than 1000 DB and don't like remove the mirror on each of them)

    There is no direct method, you have to write your own script. 

    1. First get the list of databases configured for mirroring

    2. Run a loop and and on each run

    ALTER DATABASE database_name SET PARTNER OFF  

    3. Now mirroring is removed.

    4. On mirror server o in a loop run

    RESTORE DATABASE database_name WITH RECOVERY;  

    I do not have time to write whole query but below is untested version of the query. Try it on UAT first

    IF OBJECT_ID('tempdb..#DatabaseName') IS NOT NULL DROP TABLE #DatabaseName
    CREATE TABLE #DatabaseName-- Temp table to store Database names on which index operation needs to be performed on the server
    (
    id INT IDENTITY (1,1),
    Databasename SYSNAME,
    Database_id SMALLINT
    )
    
    INSERT INTO #DatabaseName
    SELECT db_name(database_id) as DBName,database_id FROM SYS.database_mirroring
    
    Select 'List of Databases On which Maintenance Activity will be performed'
    SELECT * FROM #DatabaseName
    
    DECLARE 
    @id int,
    @dbname SYSNAME,
    @db_id smallint,
    @cmd1 nvarchar(max)
    
    SET @ID=1
    WHILE(1=1)
    BEGIN
    SELECT @ID=ID,
    @Dbname=Databasename,
    @db_id=Database_id
    FROM #DatabaseName
    WHERE ID=@ID
    IF @@ROWCOUNT=0
    BREAK
    Set @cmd1= ALTER DATABASE @dbname SET PARTNER OFF  
    print(@cmd1)--this will print the command. Make sure it is correct
    --exec sp_executesql @cmd1--if print command is correct comment it and uncomment this line and run the query again
    END


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Xi Jin Thursday, August 2, 2018 5:41 AM
    • Marked as answer by virtugeek Monday, August 6, 2018 9:33 AM
    Wednesday, August 1, 2018 2:03 PM

All replies

  • hello,

    I want to remove the mirroring between 2 sql 2012 cluster (each with 2 node) : I'll configaure always on later.

    Id like to remove the mirronring between the clustres and avoid remove it between databases (I have manu db on each cluster so it will take time to remove mirroring DB per DB

    Thanks

    Mirroring is not setup between clusters it is between 2 instances of SQl Server.  To remove mirroring follow Remove Database Mirroring

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, August 1, 2018 1:28 PM
  • Thnaks for your reply,

    in this article, we have to remove mirroring between databases and note instance (in each instance I have more than 1000 DB and don't like remove the mirror on each of them)

    Wednesday, August 1, 2018 1:44 PM
  • Thnaks for your reply,

    in this article, we have to remove mirroring between databases and note instance (in each instance I have more than 1000 DB and don't like remove the mirror on each of them)

    There is no direct method, you have to write your own script. 

    1. First get the list of databases configured for mirroring

    2. Run a loop and and on each run

    ALTER DATABASE database_name SET PARTNER OFF  

    3. Now mirroring is removed.

    4. On mirror server o in a loop run

    RESTORE DATABASE database_name WITH RECOVERY;  

    I do not have time to write whole query but below is untested version of the query. Try it on UAT first

    IF OBJECT_ID('tempdb..#DatabaseName') IS NOT NULL DROP TABLE #DatabaseName
    CREATE TABLE #DatabaseName-- Temp table to store Database names on which index operation needs to be performed on the server
    (
    id INT IDENTITY (1,1),
    Databasename SYSNAME,
    Database_id SMALLINT
    )
    
    INSERT INTO #DatabaseName
    SELECT db_name(database_id) as DBName,database_id FROM SYS.database_mirroring
    
    Select 'List of Databases On which Maintenance Activity will be performed'
    SELECT * FROM #DatabaseName
    
    DECLARE 
    @id int,
    @dbname SYSNAME,
    @db_id smallint,
    @cmd1 nvarchar(max)
    
    SET @ID=1
    WHILE(1=1)
    BEGIN
    SELECT @ID=ID,
    @Dbname=Databasename,
    @db_id=Database_id
    FROM #DatabaseName
    WHERE ID=@ID
    IF @@ROWCOUNT=0
    BREAK
    Set @cmd1= ALTER DATABASE @dbname SET PARTNER OFF  
    print(@cmd1)--this will print the command. Make sure it is correct
    --exec sp_executesql @cmd1--if print command is correct comment it and uncomment this line and run the query again
    END


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Xi Jin Thursday, August 2, 2018 5:41 AM
    • Marked as answer by virtugeek Monday, August 6, 2018 9:33 AM
    Wednesday, August 1, 2018 2:03 PM
  • ok thanks alot
    Wednesday, August 1, 2018 2:47 PM
  • Hi virtugeek,

    Did you resolve your issue? If so, please kindly mark corresponding reply as answer. It'll benefit others with relevant issue.

    If not, please feel free to ask.

    Thanks,
    Xi Jin.


    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.

    Thursday, August 2, 2018 5:42 AM