Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, 17 May 2018

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.

Thursday, 17 May 2018 20:10:34 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#

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'

 

Thursday, 17 May 2018 11:57:24 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#

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

5/17/2018, Not yet named

 

Thursday, 17 May 2018 11:26:42 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL 2008 R2 License / Cost = Open S...
Pied Piper of Grandview II
Buick drops the ball, er...muffler
Recent Posts
Archive
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll