If you can convince the reporting users that they don't really need real-time reports, you can take advantage of the ability to create a snapshot of the mirror database. See Database Mirroring and Database Snapshots on Technet.
Unlike the mirror database itself, a database snapshot is accessible
to clients. As long as the mirror server is communicating with the
principal server, you can direct reporting clients to connect to a
snapshot. Note that because a database snapshot is static, new data is
not available. To make relatively recent data available to your users,
you must create a new database snapshot periodically and have
applications direct incoming client connections to the newest
snapshot.
Mirror/snapshot combinations can also be beneficial if you want to run ETL over an OLTP database to load into a data warehouse. Example architecture in this article on SSC.
The advantage in both cases is removing the overhead of running a replication feed, if you can convince your users that an hourly lag on reporting data is acceptable. In the vast majority of cases it is perfectly reasonable but the perception can be tough to change.
First make sure you have both logins and jobs fully synchronised between your instances.
there are different ways to achieve these.
index maintenance jobs and backups
I had good results by using ola hallegren maintenance jobs for these.
replication and service broker
Always point to the listener
instead of individual IPs or server names.
inside the jobs
inside every step of every job I test if I am in the primary server, in my current availability group called sqlprodag
.
Something like this:
if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = 'SQLPRODAG'
and ars.is_local = 1) = 'PRIMARY'
begin
-- this server is the primary replica, do something here
print 'yes, primary'
EXEC MYDATABASE.[dbo].[usp_ins_APfactAPApplicationBreakdown]
end
else
begin
-- this server is not the primary replica, (optional) do something here
print 'Not a primary server'
end
and the job will only run on the primary server.
best practices before performing a failover
planned manual failover of an availability group
--======================================================================================
-- set the availability group to synchronous mode
-- wait until all is synchronized
-- do the failover
--SQLPRODAG is the availability group name
--servers are SQLPROD1 and SQLPROD2
--======================================================================================
--ON THE PRIMARY
ALTER AVAILABILITY GROUP SQLPRODAG
MODIFY REPLICA ON N'SQLPROD2'
WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
--GO TO THE SECONDARY
ALTER AVAILABILITY GROUP SQLPRODAG FAILOVER;
SELECT ag.name,
drs.database_id,
drs.group_id,
drs.replica_id,
drs.synchronization_state_desc
--ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;
SELECT ag.name,
*
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;
Best Answer
As with physical machines IO is king. There will be a noticeable IO hit from virtualisation (how much of a hit depends on the technology you choose), so make sure that you do nothing that can exacerbate this and harm IO performance further.
Of course if your entire DB (and everything else the VM is running) fits into the RAM allocated to the VM and it sees very little write activity, IO performance can be much less of an issue.
Edit: a few more points: