We use database mirroring for replicating data from physical sql servers to the disaster recovery location; SRM is used for virtual machines. I don’t like the point and click interface though. So many screens for a pretty simple series of tasks. If you take the script from here and modify it to also create the following ALTER statements then mirroring essentially sets up itself. As you can tell, we don’t use a witness server, preferring to manually fail over. When there are a lot of databases to configure this will save you from carpel tunnel let me tell you.
— execute on primary
if not exists (select * from sys.tcp_endpoints where name=’mirroring’)
CREATE ENDPOINT mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
— execute on mirror
if not exists (select * from sys.tcp_endpoints where name=’mirroring’)
CREATE ENDPOINT mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
— execute on mirror
ALTER DATABASE <<db name>>
SET PARTNER = ‘TCP://<<primary server name>>:5022’
— execute on primary
ALTER DATABASE <<db name>>
SET PARTNER = ‘TCP://<<mirror server name>>:5022’
— we use asynchronous / high performance
ALTER DATABASE <<db name>> SET SAFETY OFF
Here are some other commands which are helpful when you have to work with many databases at once:
— to stop mirroring, run on principal or mirror
alter database [<<db name>>] set partner off
select ‘alter database [‘+db_name(database_id)+’] set partner off’ from sys.database_mirroring where mirroring_guid is not null order by 1
— restore the mirrored database after mirroring is stopped
restore database [<<db name>>] with recovery
select ‘restore database [‘+db_name(database_id)+’] with recovery’ from sys.database_mirroring where mirroring_guid is not null order by 1
— make mirroring asynchronous
alter database [<<db name>>] set SAFETY OFF
select ‘alter database [‘+db_name(database_id)+’] set safety off’ from sys.database_mirroring where mirroring_guid is not null order by 1
— make mirroring synchronous (required to fail over)
alter database [<<db name>>] set SAFETY On
select ‘alter database [‘+db_name(database_id)+’] set safety full’ from sys.database_mirroring where mirroring_guid is not null order by 1
— manuall failover (must be run on principal)
alter database [<<db name>>] set partner failover
select ‘alter database [‘+db_name(database_id)+’] set partner failover’ from sys.database_mirroring where mirroring_guid is not null order by 1