Tag Info

New answers tagged

0

In addition to DBAFromTheCold's response, another approach would be to use replication and replicate only the tables that you need to use for reporting to the reporting server. This may be a better option for you for a few reasons: 1) you will only get the data that you need 2) there will be no downtime for synchronization and 3) you can index the reporting ...


0

It sounds to me like a 'redundant rack' style of reporting server would support your needs. Implemented by doing a nightly restore to the reporting server from the production backups. Your reporting data will be one day behind, but this is usually up-to-date enough to meet client needs.


1

If you have SQL Server 2012 Enterprise Edition you could implement Always On Availability groups. This is an extension of mirroring (built on-top of windows clustering) which allows the mirrored database to be a readable secondary. The benefit of this is that the secondary is updated as the primary database is updated. However it is expensive as the ...


1

While not real-time, Log Shipping can be performed at pretty frequent intervals. You can configure how often your Primary performs log backups and how far behind your Secondary should be (0 minutes, 30 minutes, 2 hours...). Brent Ozar makes the case you could even execute Log Shipping every minute if you wanted. It's certainly not for everyone, but helps ...


3

Well, you can keep using mirroring. "Deprecated" does not mean "doesn't work any more" - it just means that, at some point in the future, it won't be supported when you move to some version > 2012 (probably at least 3 versions later, maybe more given the backlash this announcement has caused). By then, I am sure they will have a replacement for mirroring ...


0

I used the following query that I got from EXCHANGE SPILL, but slightly modified it to convert time from UTC to local. DECLARE @FileName NVARCHAR(4000) SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)') FROM ( SELECT CAST(target_data AS XML) target_data FROM sys.dm_xe_sessions s ...


4

Here's my go-to query to pull the events that Aaron mentioned (availability_replica_state_change). This script also takes the liberty of converting the UTC time to your local time: declare @xel_path varchar(1024); declare @utc_adjustment int = datediff(hour, getutcdate(), getdate()); ...


2

Look for availability_replica_state_change events in the AlwaysOn_health extended events session. If this session is not running: ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE = ON); ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE = START; More info: ...



Top 50 recent answers are included