Quick access to SQL Server AlwaysOn Availability cheatsheet

These are queries to manage SQL Server AlwaysOn Availability Groups Using sys.fn_hadr_is_primary_replica to identify if the database is primary replica SELECT sys.fn_hadr_is_primary_replica ('my_ag_db'); Display All availability groups visible to this server when the Server is the Primary replica SELECT Groups.[Name] AS AGname FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica = @@Servername; Display All availability groups visible to this server when this Server is a Secondary replica SELECT Groups.[Name] AS AGname FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica != @@Servername; Display All Databases Across all Availability groups within the... Read more →

List the differences between Kerberos and NTLM

There is a message found in SQL Server Error Logs similar to The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. The line I'm interested in reviewing is Failure to register a SPN might cause integrated authentication to use... Read more →

How to find Active Directory users with Get-ADUser search filter

Question: How can I find a list of Active Directory users with a specific search filter on the name column using Powershell? In this case I want to find all Active Directory users in the default OU group with a search string. Answer: Assuming you are in the current Domain group \ OU group , find below an example of the Powershell cmdlet Get-ADUser which will filter on the search string "dba". In this example seaech - the GetADUser cmdlet iterates through the user list looking for any user with the string "dba" in any part of the name. Get-ADUser... Read more →

How to use STRING_AGG to concatenate strings with grouping

Question: Below is table and expected result. What is the query to achieve this result Table : Test ID LOT 7065161 4 7065212 1 7065212 4 7065203 1 7065203 2 7065203 3 Expected Result of query ID LOT 7065161 4 7065212 1_4 7065203 1_2_3 Answer: Previous to SQL Server 2017 - I use to use the COALESCE function , but since SQL Server 2017 - a new function was introduced : STRING_AGG . According to MS documentation "Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string" There... Read more →

How to bcp export file with encoding ascii

Question: I have a bcp command exporting a sql query which outputs to a file . The problem is the file is being saved as Unicode type - causing a subsequent ETL process to fail. How can I fix? I want the file to export as ascii encoding bcp "select * FROM myTable" queryout E:\mytable.csv -w -t, -T -S SERVER\MYINST1 Answer: The quick answer is to ask you to add the -c and -C switches. From SQL Server Books Online "-c Performs the operation using a character data type. This option does not prompt for each field; it uses char... Read more →

How to fix Msg 7390 - The requested operation could be performed

Question: I have a SQL Server Linked Server configured , pointing to an ODBC - accessing a MongoDB driver . The test connections all work OK - and no problems running an OPENQUERY select statement using the Linked Server. But when I attempt to run an EXECUTE AT , and attempt to INSERT the data into a #temp table - I get an error message: declare @myStmt varchar(max) set @myStmt = 'select _id from mytable' CREATE TABLE #temp2 (_id varchar(50) ) INSERT INTO #temp2 EXECUTE (@myStmt) AT my_linked_server DROP TABLE #temp2 Msg 7390, Level 16, State 2, Line 6 The... Read more →

How to find physical path for SQL Server backup on Container

Question: I'm migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defaultbackup directory. I don't have access to the underlying files via command line and can only use command line. How can I get the physical disk device , which will then allow me to create a RESTORE DATABASE statement pointing to the device? Answer: Assuming you've taken the BACKUP DATABASE with at at least one backup device , such as this example , which backups to the default backup directory... Read more →