The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, October 7, 2024 Login
Public

Blog posts for the month of May,2018.
Disable your Floppy Disk Drive5/17/2018 9:10:34 PM

Incredible, but true.

SQL Server 2017, specific build = 14.0.3015.40, on an Azure Cloud server, IAAS.

While restoring a database it just sits there forever what felt like forever (it was 80gb), and after awhile saw this in the error log:

The operating system returned the error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace' on 'A:\'.

Guess what, you fix this by disabling your floppy drive in device manager. Absolutely amazing.

 http://www.sqlservercentral.com/blogs/sql-geek/2017/05/15/sql-server-2017-the-operating-system-returned-the-error-21/

I actually still have some 5 1/2 and 3 1/4 floppy disks and a USB Floppy drive, though I doubt there would be a use for a "floppy drive" controller on a cloud machine, but it's good to see the floppy's in the cloud.

SQL 2017 Always-on no cluster5/17/2018 12:57:24 PM

Setting up always-on availability group with no cluster.

So far works good, though some of our backup and monitoring queries began failing, specifically the function, sys.fn_hadr_backup_is_preferred_replica

Msg 35222, Level 16, State 0, Line 1 Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

We ended up working around this by checking the sys.availability_groups which has a column for cluster type, this way we can check if the always-on group has a cluster or not.

-- This leads to error
select [master].sys.fn_hadr_backup_is_preferred_replica('ao_test')
go
select
CASE WHEN dbrs.is_primary_replica = 1 and ag.cluster_type = 1 then 1
when dbrs.is_primary_replica = 0 and ag.cluster_type = 1 then 0
WHEN ag.cluster_type is null then 0
else Convert(int,[master].sys.fn_hadr_backup_is_preferred_replica(sd.[name])) end
as preferred_replica_backup
From sys.databases sd
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON sd.group_database_id = dbrs.group_database_id and dbrs.is_local = 1
left outer join master.sys.availability_groups ag
on ag.group_id = dbrs.group_id where sd.[name] = 'ao_test'

 

Spring Dexter Heifer Calf 20185/17/2018 12:26:42 PM

Horkay Family Farms first heifer calf, 3 bull calves in row up until now !

5/17/2018, Not yet named

 


Blog Home