Mirror Commands

gistfile1.sql

Mirror Commands

Create Mirror

Configure Connections between the dbs using certificates/endpoints - https://technet.microsoft.com/en-us/library/ms191140.aspx

-- Disable automatic backup jobs before starting --

1. Backup database
2. Ship backup to the mirror server
3. Restore the backup WITH RECOVERY

RESTORE DATABASE AdventureWorks 
    FROM DISK = 'C:\AdventureWorks.bak' 
    WITH NORECOVERY
GO

4. Backup Transaction log

BACKUP LOG AdventureWorks 
    TO DISK = 'C:\AdventureWorks.bak' 
GO

5. Restore Transaction log backup on mirror (If any additional log backups occur before you start mirroring, you must also restore all of those log backups, in sequence, to the mirror server using WITH NORECOVERY.)

RESTORE LOG AdventureWorks 
    FROM DISK = 'C:\AdventureWorks.bak' 
    WITH FILE=1, NORECOVERY
GO

6. Set PARTNERS (Must be at least SQL Standard licence) - Always run failover one first

On Failover box:
--At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE AdventureWorks 
    SET PARTNER = 'TCP://HOST_A.Mydomain.Corp.Adventure-Works.com:7024';
GO

On Master box:
--At HOST_A, set server instance on HOST_B as partner (mirror server).
ALTER DATABASE AdventureWorks 
    SET PARTNER = 'TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024';
GO

7. Add WITNESS if needed (Needs at least a Web SQL licence on witness box)

On Master box:
ALTER DATABASE AdventureWorks 
    SET WITNESS =  'TCP://WITNESSHOST4.COM:5022'
GO

** Ensure you use hostnames (set up using a domain or the hosts files), the witness will never work using an IP only

-- Re-Enable automatic backup jobs before exiting --



Actual Scripts 

On Master Box

BACKUP DATABASE [ProductionTest]   
    TO DISK = 'Z:\ProductionTest-mirror.bak' 
GO

BACKUP LOG [ProductionTest]   
    TO DISK = 'Z:\ProductionTest-mirrortrans.bak' 
GO

/* Do other steps */


ALTER DATABASE ProductionTest
SET RECOVERY FULL;

ALTER DATABASE ProductionTest
    SET PARTNER = 'TCP://au-sql-failover:5022';
GO

ALTER DATABASE ProductionTest 
    SET WITNESS =  'TCP://au-sql-witness:5022'
GO


On Failover Box:

RESTORE DATABASE [ProductionTest] 
    FROM DISK = 'Z:\sqlshare\ProductionTest-mirror.bak' 
    WITH NORECOVERY
GO

RESTORE LOG [ProductionTest]  
    FROM DISK = 'Z:\sqlshare\ProductionTest-mirrortrans.bak' 
    WITH FILE=1, NORECOVERY
GO

--At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE [ProductionTest]   
    SET PARTNER = 'TCP://au-sql-primary:5022';
GO




Pause Mirror
ALTER DATABASE Production SET PARTNER SUSPEND;
Resume Mirror
ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;
FailOver/FailBack
Alter Database [<database>] Set Partner Failover; -- Run on the active principle master db, <database> should be the db you want to fail over.



Possible auto failover script (untested):

/* http://www.sqlservercentral.com/blogs/robert_davis/2009/02/22/Will-the-Mirror-automatically-fail-back-to-the-original-Principal-when-it-comes-back-online/ */

Create Procedure dbo.dbm_FailoverMirrorToOriginalPrincipal
     @DBName sysname
As
Declare @SQL nvarchar(200)

Set NoCount On;

/*
     If database is in the principal role and is in a synchronized state
     then fail database back to original principal
*/
If Exists (Select 1 From sys.database_mirroring
          Where database_id = db_id(@DBName)
          And mirroring_role = 1 -- Principal partner
          And mirroring_state = 4) -- Synchronized
  Begin
     Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'

     Exec sp_executesql @SQL;
  End

Set NoCount Off;


Data Source=myServerAddress;Failover Partner=myMirrorServerAddress; Initial Catalog=myDataBase;Integrated Security=True;

Server=y2uepbs4lk.database.windows.net,1433;Database=au-prod;User ID=ibsa;Password=410dAeae89005aac35da2b8f31ff7533;Trusted_Connection=False;Connection Timeout=120;MultipleActiveResultSets=True;
Server=10.32.0.24,1433; Failover Partner=10.32.0.13,1433; Database=ProductionTest; User ID=ibsa; Password=410dAeae89005aac35da2b8f31ff7533; Trusted_Connection=False; Connection Timeout=120; MultipleActiveResultSets=True;

Server=au-sql-master.cloudapp.net,53856; Failover Partner=au-sql-master.cloudapp.net,53859; Database=ProductionTest; User ID=ibsa; Password=410dAeae89005aac35da2b8f31ff7533; Trusted_Connection=False; Connection Timeout=120; MultipleActiveResultSets=True;