For purposes of ilustrate this method:
PRINCIPAL will be the PRINCIPAL database serverMIRROR will be the MIRROR database servermyDB will be the database
MIRRORBackup the folder created in PRINCIPAL to store the backups
EP5022 will be the endpoint name and I will use the port 5022 to configure it
I will create an anonymous login and grant it access to connect using the endpoint, this is not very secure, so you may wan to change it
No witness is configured, if you need one, you’ll have to configure it as well
For each Database do:
Set Recovery mode, execute in PRINCIPAL:
ALTER DATABASE MyDB SET RECOVERY FULL
Create Backup Folder in PRINCIPAL(i.e. c:\backup)Share the Backup Folder (make sure other users can modify the folder)Create Endpoints:
Execute in PRINCIPAL
if Not Exists (SELECT name, role_desc, state_desc FROM sys.database_MIRRORing_endpoints where name = 'EP5022') Begin CREATE ENDPOINT EP5022
STATE=STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING(ROLE = PARTNER) End if not exists( select * from sys.syslogins where loginname = 'NT AUTHORITY\ANONYMOUS LOGON') Begin Create Login [NT AUTHORITY\ANONYMOUS LOGON] from windows End GRANT CONNECT ON ENDPOINT::EP5022 TO [NT AUTHORITY\ANONYMOUS LOGON]
Execute in MIRROR
STATE=STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING(ROLE = ALL) End if not exists( select * from sys.syslogins where loginname = 'NT AUTHORITY\ANONYMOUS LOGON') Begin Create Login [NT AUTHORITY\ANONYMOUS LOGON] from windows End GRANT CONNECT ON ENDPOINT::EP5022 TO [NT AUTHORITY\ANONYMOUS LOGON]
Backup Database, Execute in PRINCIPAL
BACKUP DATABASE MyDB TO DISK = '\\PRINCIPAL\MIRRORBackup\MyDB.bak' WITH FORMAT
Kill Connections, Execute in PRINCIPAL
declare @sql nvarchar(1000) declare @spid as int declare processcursor cursor fast_forward for select SPID from SYS.SYSPROCESSES p inner join sys.sysdatabases d on p.dbid= d.dbid and d.name = 'MyDB' where SPID > 50 AND spid <> @@spid
open processcursor fetch next from processcursor into @spid
while @@fetch_status = 0 begin set @sql = ' kill ' + convert(varchar, @spid) exec (@sql) fetch next from processcursor into @spid end
close processcursor deallocate processcursorRestore Database, Execute in MIRROR if not exists(select * from sys.databases where name='MyDB')
Begin Create Database MyDB End RESTORE DATABASE MyDB FROM DISK = '\\PRINCIPAL\MIRRORBackup\MyDB.bak' WITH REPLACE, NORECOVERY'
Backup Log, Execute in PRINCIPAL
BACKUP Log MyDB TO DISK = '\\PRINCIPAL\MIRRORBackup\MyDB_log.bak'
Kill Connections, Execute in MIRROR
close processcursor deallocate processcursorRestore Log, Execute in MIRROR,
RESTORE Log MyDB FROM DISK = '\\PRINCIPAL\MIRRORBackup\MyDB_log.bak' WITH NORECOVERY'
Enable MIRRORing:
ALTER DATABASE MyDB SET PARTNER = 'TCP://PRINCIPAL.mydomain.com:5022'
ALTER DATABASE MyDB SET PARTNER = 'TCP://MIRROR.mydomain.com:5022'
Now everything should be ok now, and the Database is already configured.