New answers tagged

0

I have experienced this behavior before, where the AGs "blip:" from PRIMARY to RESOLVING and back to PRIMARY from SECONDARY to SECONDARY It's my understanding that, on a system with a small number of logical processors (1-4), and with multiple AGs (or AGs with many databases), some of these "blips" are unavoidable. Especially if the server is a VM with ...


3

Simply run RESTORE DATABASE TestDB WITH RECOVERY on the secondary.


0

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 JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address WHERE s.name = N'AlwaysOn_health' ) ft SELECT XEData.value('(event/@timestamp)[1]','datetime2(3)') AS event_timestamp, ...


0

Always ON is nothing but a special type of mirroring. You will have to schedule log backups the same way you do for any sql server database with full recovery model. You do have option to offload backups to secondary node (very helpful). Copy_only full backups and normal log backups are the only possible ones on secondaries though. However, Differential ...


0

Since the databases in an AG always have to have Full Recovery Model, yes, the databases definitely need log backups scheduled frequently to avoid excessive log file growth.


0

We use ola hallengren backup scripts/jobs and that does check the cluster settings for each database. For each database it will check, can I backup this database?, using the cluster settings and current cluster status. So if your AOAG is set to only allow backups from the Primary it wont backup a database that is currently a secondary. Works great for us, ...


1

What happens when the old primary is available and back online? Synchronous-commit mode and Automatic Failover: When the former primary replica comes back online, it takes on the secondary role, and the former primary database becomes the secondary database. The new secondary replica quickly resynchronizes the new secondary databases with the corresponding ...


1

This is not possible since database is added to AOAG You cannot take the db offline unless you remove it from AG You can see and verify this yourself simply by adding some "TestDB" to your AG and try to take if offline Do I have to wait for the sync to finish before manually failing back over to Database A from Database B? Yes you have to wait for sync ...


1

The problem is that procedure only works if read access is enabled for the secondaries and, as per the documentation, Basic Availability Groups do not allow readable secondaries. You need to manually perform the checks that this procedure normally handles as per the doco: This is expected behavior. You must verify the presence of the subscriber server ...


0

My instinct is to (side by side) migrate as is. Then once live and stable, separately, add always-on ag's. That is perfectly reasonable and supported. However. Are you sure you really want to perform the cluster creation, configuration and AG provisioning and testing on a live (post-migration) system? If you do the AGs first, you can perform all that ...


1

why would that be the case only with SSRS? As addition note to the Handy's answer, one of the main reason, SSRS dependencies are not only in ReportServer and ReportServerTempDB databases, but also on SQL Server Agent which host all the subscription configuration of the SSRS and which would not be a scope of AG replication. So, you got covered ReportServer ...


2

The reason the documentation states that automatic failover is not supported is that there are post-failover tasks that must be carried out to ensure the Report Server is completely online. See this section for information on exactly what tasks need to be completed. Essentially it boils down to needing to stop the SQL Agent on the old primary and restart the ...


2

You cannot add a Linux system to a Windows cluster, so you won't be able to add any Linux systems into a SQL Server 2014 availability group. You also cannot do the migration with distributed availability groups because SQL Server 2014 did not have support for distributed availability groups. From Distributed availability groups: Because the distributed ...


1

I had lots of confusion around the topic - In AG cluster restoring Log backup following with Copy - only full backup. It is now working fine. We can use copy only backup from secondary replica: USE [master] RESTORE DATABASE [xxxxx_testDB] FROM DISK = N'D:\Backups\FULL_COPY_ONLY\xxxxx_testDB_FULL_COPY_ONLY.bak' WITH FILE = 1, MOVE N'xxxxx_testDB' TO N'...


Top 50 recent answers are included