locked
Can we automate database mirroring RRS feed

  • Question

  • Hi All ,

    Is there any possible way where we can automate database mirroring ? My scenario is i have appliaction where in it creates database for every 1 week and as per the client requirment he would like to setup database mirroring for the newly created database .

    The database name convention will be in the following format for the first time the database name would be xyz and from the very next suffix will be added stating xyz1,xyz2 E.T.C

    Kindly let me know if you need any further information from my end .

    Sql server version : 2005

    ----------------
    Thanks,Rajasekhar Reddy

    Thursday, October 7, 2010 6:06 AM

Answers

  • I don't think that the code has changed for SQL 2008. Have you tried it?

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Thursday, October 7, 2010 8:06 AM
  • Below script should be run via SSMS on principal server in SQLCMD mode. (Query Menu > SQLCMD mode)
    Replace Principal and Mirror Server Name

    -- if database exists with mirrororing, Remove Mirroring and drop it. 
    -- We are reusing the endpoints
    
    :setvar PRINCIPALSERVER "ATOM\DAD"
    :setvar MIRRORSERVER "ATOM\MOM"
    
    :connect $(PRINCIPALSERVER)
    use master
    go
    if db_id('MirroringDemo') is not null
    begin
    drop database MirroringDemo
    end
    go
    Create database MirroringDemo
    go
    use MirroringDemo
    go
    create table CorruptMe (id int, name varchar(100))
    go
    insert into CorruptMe values (1, 'My Name is Balmukund')
    go
    Select * from CorruptMe
    go
    !!del D:\MirroringDemo_Full.bak
    !!del D:\MirroringDemo_Tlog.trn
    backup database MirroringDemo to disk = 'D:\MirroringDemo_Full.bak'
    go
    backup Log MirroringDemo to disk = 'D:\MirroringDemo_Tlog.trn'
    go
    
    :connect $(MIRRORSERVER)
    if db_id('MirroringDemo') is not null
    begin
    drop database MirroringDemo
    end
    go
    RESTORE DATABASE [MirroringDemo] FROM 
    DISK = N'D:\MirroringDemo_Full.bak' WITH FILE = 1, 
    MOVE N'MirroringDemo' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MOM\MSSQL\DATA\MirroringDemo.mdf', 
    MOVE N'MirroringDemo_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MOM\MSSQL\DATA\MirroringDemo_1.LDF', 
    NORECOVERY, NOUNLOAD, STATS = 10
    GO
    RESTORE LOG [MirroringDemo] FROM DISK = N'D:\MirroringDemo_Tlog.trn' WITH FILE = 1, 
    NORECOVERY, NOUNLOAD, STATS = 10
    GO
    :connect $(PRINCIPALSERVER)
    ALTER DATABASE MirroringDemo SET PARTNER = 'TCP://HOSTNAME.Domain.com:5022'
    go
    
    :connect $(MIRRORSERVER)
    ALTER DATABASE MirroringDemo SET PARTNER = 'TCP://HOSTNAME.Domain.com:5023'
    go
    ALTER DATABASE MirroringDemo  SET WITNESS =  'TCP://HOSTNAME.Domain.com:5024'
    GO
    
    -- Create snaphot
    

    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Thursday, October 7, 2010 8:44 AM

All replies

  • You could write a script to automate it. Please look at the BOL, ALTER DATABASE Database Mirroring pages: http://msdn.microsoft.com/en-us/library/bb522476.aspx

    Tom

    Tom Van Zele | Blog | Twitter | LinkedIn
    Thursday, October 7, 2010 7:42 AM
  • Thanks Tom,

    But i have already looked over this link prior to posting this question ... Morever the link provided in the above  post is for 2008 R2 .

    ----------------
    Thanks,Rajasekhar Reddy

    Thursday, October 7, 2010 7:59 AM
  • I don't think that the code has changed for SQL 2008. Have you tried it?

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Thursday, October 7, 2010 8:06 AM
  • Below script should be run via SSMS on principal server in SQLCMD mode. (Query Menu > SQLCMD mode)
    Replace Principal and Mirror Server Name

    -- if database exists with mirrororing, Remove Mirroring and drop it. 
    -- We are reusing the endpoints
    
    :setvar PRINCIPALSERVER "ATOM\DAD"
    :setvar MIRRORSERVER "ATOM\MOM"
    
    :connect $(PRINCIPALSERVER)
    use master
    go
    if db_id('MirroringDemo') is not null
    begin
    drop database MirroringDemo
    end
    go
    Create database MirroringDemo
    go
    use MirroringDemo
    go
    create table CorruptMe (id int, name varchar(100))
    go
    insert into CorruptMe values (1, 'My Name is Balmukund')
    go
    Select * from CorruptMe
    go
    !!del D:\MirroringDemo_Full.bak
    !!del D:\MirroringDemo_Tlog.trn
    backup database MirroringDemo to disk = 'D:\MirroringDemo_Full.bak'
    go
    backup Log MirroringDemo to disk = 'D:\MirroringDemo_Tlog.trn'
    go
    
    :connect $(MIRRORSERVER)
    if db_id('MirroringDemo') is not null
    begin
    drop database MirroringDemo
    end
    go
    RESTORE DATABASE [MirroringDemo] FROM 
    DISK = N'D:\MirroringDemo_Full.bak' WITH FILE = 1, 
    MOVE N'MirroringDemo' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MOM\MSSQL\DATA\MirroringDemo.mdf', 
    MOVE N'MirroringDemo_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MOM\MSSQL\DATA\MirroringDemo_1.LDF', 
    NORECOVERY, NOUNLOAD, STATS = 10
    GO
    RESTORE LOG [MirroringDemo] FROM DISK = N'D:\MirroringDemo_Tlog.trn' WITH FILE = 1, 
    NORECOVERY, NOUNLOAD, STATS = 10
    GO
    :connect $(PRINCIPALSERVER)
    ALTER DATABASE MirroringDemo SET PARTNER = 'TCP://HOSTNAME.Domain.com:5022'
    go
    
    :connect $(MIRRORSERVER)
    ALTER DATABASE MirroringDemo SET PARTNER = 'TCP://HOSTNAME.Domain.com:5023'
    go
    ALTER DATABASE MirroringDemo  SET WITNESS =  'TCP://HOSTNAME.Domain.com:5024'
    GO
    
    -- Create snaphot
    

    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Thursday, October 7, 2010 8:44 AM
  • Tom & Mukund thanks much for the very quick response ...

    Was just looking after the follwoing syntax "ALTER DATABASE MirroringDemo SET PARTNER = 'TCP://HOSTNAME.Domain.com:5023'
    " ... Rest i will make changes as per my requirments ..

    Thursday, October 7, 2010 9:14 AM