Tools to manage sql 2008 database mirroring?

We are going to be moving about 20 databases that live on a single instance of sql 2000 to a sql 2008 r2 environment with database mirroring.

What I'm looking for is a tool or scripts that will help me manage the conversion and management of those 20db's onto this new mirrored environment easily. There are many steps in setting each DB up and I want to automate as much as possible.

Edit: Here are the steps I've been doing manually:

Create the same username/passwords from the old sql 2000 server onto new sql 2008 server. Then sync those users/passwords onto the other sql 2008 server with the same SSID's so when we do the db backup and restore they match up.
Take a backup of each sql 2000 db's.
Copy them to server A.
Restore the backup to server A.
Backup from server a, copy to server b, restore there.
Run the mirror "configure security" wizard.
Start mirroring.

I've love to be able to script this out or have a tool that does it for me. Thanks! Paul

I created a series of batch files and SQL scripts to do the steps below. I only included the scripts for the mirroring stuff but the other referenced SQL scripts are pretty basic SQL commands (except the restore database script, that one is kind of tricky due to how I've implemented it) and creating them will give you an opportunity to change the SQLCMD mode variables. If you want to Direct Message me on twitter (@jcumberland) with your email address I can send you the full set of scripts. I have removed the full paths on the file names as well.

sets recovery mode for database
removes mirroring
remove mirror database
backup database
backup log
copies bak file
copies trn file
restores database backup
restores log backup
deletes bak file
deletes trn file
set up mirroring between the principal and mirror

/*------------------------------------------------------*/
--  REPLACE THESE VALUES
/*------------------------------------------------------*/











/*------------------------------------------------------*/
--  BATCH FILE 1, CALLS UTILITY BATCH FILE
/*------------------------------------------------------*/
@echo off

set PRIMARYHOST=
set MIRRORHOST=
set INST=
set DRIVE=
set DRTARGETDIR="%DRIVE%:\MSSQL.1\MSSQL\DATA\"
set LOGDIR="%DRIVE%:\MSSQL.1\MSSQL\DATA\"
set PORT=
set PRIMARYINST=%PRIMARYHOST%\%INST%
set MIRRORINST=%MIRRORHOST%\%INST%

@echo on


call drdbsetup.bat %PRIMARYHOST% %MIRRORHOST% %PRIMARYINST% %MIRRORINST% %DRIVE% %DRTARGETDIR% %PORT%  %LOGDIR%

pause







/*------------------------------------------------------*/
--  BATCH FILE 2 - CALLS THE SQL SCRIPTS
/*------------------------------------------------------*/
rem -- set recovery mode to full
sqlcmd /E /S%3 -i SetRecoveryMode.sql -v DATABASE="%8"

rem -- remove mirroring
sqlcmd /E /S%3 -i MirroringRemove.sql -v DATABASE="%8"

sqlcmd /E /S%4 -i MirroringRemoveMirror.sql -v DATABASE="%8"

rem -- full backup
sqlcmd /E /S%3 -i BackupDatabaseFull.sql -v BACKUPPATH="%5:\" -v DATABASE=%8

rem -- log backup
sqlcmd /E /S%3 -i BackupDatabaseLog.sql -v BACKUPPATH="%5:\" -v DATABASE=%8

rem -- copy backup files to mirror
copy \\%1\%5$\*.bak %6 /y
copy \\%1\%5$\*.trn %6 /y

rem -- remove backup files from primary
del \\%1\%5$\*.bak
del \\%1\%5$\*.trn

rem -- restore database from backup directory
sqlcmd /E /S%4 -i RestoreDatabase.sql -v BKDIR=%6 -v DATADIR=%6 -v LOGDIR=%9

rem -- restore database log
sqlcmd /E /S%4 -i RestoreLog.sql -v BACKUPPATH=%6 -v DATABASE=%8

rem -- remove backup files from mirror
del %6\*.bak
del %6\*.trn

rem -- set up mirroring
sqlcmd /E /S%4 -i MirroringSetup.sql -v PRINCIPAL="%3" -v MIRROR="%4" -v PRINCIPAL_DNS="%1." -v MIRROR_DNS="%2.yourdomain.local" -v DATABASE_NAME="%8" -v PORT=%7







/*------------------------------------------------------*/
--  MirroringSetup.sql
/*------------------------------------------------------*/


/*-----------------------------------------------------*/
--  run on principal
/*-----------------------------------------------------*/
:connect $(PRINCIPAL)
GO

--  creates endpoint
if not exists (select * from sys.endpoints where name = 'Mirroring')
begin
    CREATE ENDPOINT Mirroring
    AUTHORIZATION []
    STATE = STARTED AS TCP (
        LISTENER_PORT = $(PORT)
        ,LISTENER_IP = ALL
    )
    FOR DATA_MIRRORING (
        ROLE = PARTNER
        ,AUTHENTICATION = WINDOWS NEGOTIATE
        ,ENCRYPTION = REQUIRED ALGORITHM RC4
    )
end
go

/*-----------------------------------------------------*/
--  run on mirror
/*-----------------------------------------------------*/
:connect $(MIRROR)
GO

--  creates endpoint
if not exists (select * from sys.endpoints where name = 'Mirroring')
begin
    CREATE ENDPOINT Mirroring
    AUTHORIZATION []
    STATE = STARTED AS TCP (
        LISTENER_PORT = $(PORT)
        ,LISTENER_IP = ALL
    )
    FOR DATA_MIRRORING (
        ROLE = PARTNER
        ,AUTHENTICATION = WINDOWS NEGOTIATE
        ,ENCRYPTION = REQUIRED ALGORITHM RC4
    )
end
go

-- Sets up mirror partnership
ALTER DATABASE [$(DATABASE_NAME)] SET PARTNER = N'TCP://$(PRINCIPAL_DNS):$(PORT)'
go

-- Removes mirror monitoring
if exists (select * from msdb..sysjobs where [name] ='Database Mirroring Monitor Job')
begin
    EXEC sp_dbmmonitordropmonitoring
end
go

-- Sets up mirror monitoring
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
go


/*-----------------------------------------------------*/
--  run on principal
/*-----------------------------------------------------*/
:connect $(PRINCIPAL)
GO

-- Sets up mirror partnership
ALTER DATABASE [$(DATABASE_NAME)] SET PARTNER = N'TCP://$(MIRROR_DNS):$(PORT)'
go

-- Removes mirror monitoring
if exists (select * from msdb..sysjobs where [name] ='Database Mirroring Monitor Job')
begin
    EXEC sp_dbmmonitordropmonitoring
end
go

-- Sets up mirror monitoring
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute 
go






/*------------------------------------------------------*/
--  DRMirroringRemove.sql
/*------------------------------------------------------*/
if exists ( select * from sys.database_mirroring where db_name(database_id) = '$(DATABASE)' and mirroring_guid is not null )
begin
    ALTER DATABASE [$(DATABASE)] SET PARTNER OFF
end
go

WAITFOR DELAY '00:00:10'
go







/*------------------------------------------------------*/
--  DRMirroringRemoveMirror.sql
/*------------------------------------------------------*/
if exists ( select * from sys.database_mirroring where db_name(database_id) = '$(DATABASE)' and mirroring_guid is not null )
begin
    ALTER DATABASE [$(DATABASE)] SET PARTNER OFF
end
go

WAITFOR DELAY '00:00:10'
go

DROP DATABASE [$(DATABASE)]
go

Tools to manage sql 2008 database mirroring?

Related

Recent Posts