There are several ways to configure AlwaysOn, and even more ways to configure networking behind the cluster. A very common configuration I’ve setup and seen in production environment is a Active site and Passive disaster recovery site. In this scenario the cluster configuration needs the ability to bring the AlwaysOn resource online, even if it will never have the need to actually do that. Because of this you will need to assign an IP address from all subnets to the AlwaysOn group. This can potentially cause problems with DNS if all addresses are registered to the CNAME. There are some steps to overcome this here. In summary this is what action I take on my clusters:
- Open PowerShell with “Run as Administrator”
- Run command to list all clustered resources. We are focused on the AlwaysOn listener name:
- Run the following command to unregister the IP address that are not in use:
Get-ClusterResource “SharePoint 2013” | Set-ClusterParameter RegisterAllProvidersIP 0
- Restart the Availability Group clustered resource.
There have been several times I’ve attempted to install Failover Clustering Tools on a server and received the following error: “Object reference not set to an instance of an object”
Here are the steps I’ve taken in the past to correct this:
- Open PowerShell with “Run as Administrator”
- Run this command:
Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools
Additional instructions can be found here.
I cannot take credit for this script, but I use it all the time:
AS [Percent Complete],CONVERT(VARCHAR(20),
DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CASE WHEN r.statement_end_offset = -1 THEN 1000
ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE','RESTORE LOG','BACKUP LOG')
How to run SQL services using a Group Managed Service Account (gMSA). The process is very similar to using a Managed Service Account, here are the steps to get this up and running:
- Open a PowerShell prompt with “Run as Administrator”.
- Run the following commands:
- Lastly in PowerShell, run this command (replacing ACCOUNTNAME with your Group Managed Service Account):
- Open SQL Server Configuration Manager and select SQL Server Services.
- Open the Properties of the service you wish to edit.
- Under the Log On tab, Browse or type in the gMSA name. The gMSA should end with a $. The password field should remain blank:
- A prompt will appear asking for confirmation. Select Yes.
Recently I received a request to gather run-time information about a particular SQL scheduled job. They wanted to know how long a specific job was running. I started digging through the msdb.sysjobhistory table and found some strange values for the columns. After searching the Internet I wrote this query that fit my needs:
j.[name] AS [Job Name],
[msdb].[dbo].[agent_datetime]([run_date], [run_time]) AS [Run DateTime],
(([run_duration]/10000*3600 + ([run_duration]/100)%100*60
+ [run_duration]%100 + 31 ) / 60) AS [Run Duration (Minutes)]
FROM [msdb].[dbo].[sysjobs] j
INNER JOIN [msdb].[dbo].[sysjobhistory] h
ON j.[job_id] = h.[job_id]
WHERE j.[name] = 'JOB NAME'
AND [step_id] = 0 --Only Job Outcome Results
ORDER BY [Job Name], [Run DateTime] DESC
Getting an error like this after you start SQL in single user mode can be frustrating:
This happens to me sometimes when I am attempting to reset passwords of instances. Here are the steps you can take to get by this error (“Run as Administrator” should be selected for all command below) :
- Stop the SQL Server Service:
- Start the SQL Server Service using the switches below. The service will not fully start and the “service is starting…” message will remain in the command window. Once this message is displayed you can move on to step 3:
net start MSSQLSERVER /c /m /T3608
- Either open Management Studio and connect to the instance, or open a new CMD window to use SQLCMD.
- Once you’ve completed the necessary changes the SQL Server Service will need to be forcibly stopped. Either end the process in task manager or use the following command in the windows CMD window:
taskkill /IM sqlservr.exe /F
- Now SQL can be started normally.
When configuring always on in an a few environments I have come across a this error a few time:
Checking whether the endpoint is encrypted using a compatible algorithm:
The configuration of endpoint data encryption is incompatible between replicas and the endpoint connection will fail. The current configuration is listed below with following order: the name of replica, role of replica, encryption and algorithm
This isn’t as big of an issue as it looks like. The portion in red above is the part you need to pay attention to. The error is stating that the server you are trying to add to the availability group has default Mirroring endpoint that is using a different encryption then the type that the servers in the availability group are using. A simple fix is to modify the new server’s endpoint encryption to match the existing replicas setup. In this case the first two servers were using AES and the new server is using RC4. Here are the steps:
- In SSMS expand Server Objects, then Endpoints, then Database Mirroring.
- There should be an endpoint named Mirroring here, right click on this endpoint and Script Endpoint as->Create to->New Query Editor Window:
- Modify the script from Create to Alter and modify the Required Algorithm to match the existing replicas:
ALTER ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
Most of the time when I use a cursor I find myself using the same template. I am always writing the same code so I figured I’d save myself 3 minutes each time I use a cursor and store the code here:
DECLARE @name varchar(128)
DECLARE _cursor CURSOR LOCAL FAST_FORWARD FOR
--uncomment line below to include Master, MSDB, Model, TEMPDB, and distribution
WHERE [database_id] > 4 AND [name] != 'distribution'
ORDER BY [name];
FETCH NEXT FROM _cursor
WHILE @@FETCH_STATUS = 0
PRINT 'USE [' + @name + ']'
FETCH NEXT FROM _cursor
Recently we had a group of DBA who have limited access on an instance. They wanted to view the SQL logs but were unable. I found it difficult to find a clear solution for this, but after some trial and error here is what I came up with:
-- Create a user on the master database to grant xp_readerrorlog permissions
CREATE USER _USERNAME_or_GROUPNAME FOR LOGIN _USERNAME_or_GROUPNAME
-- Grant execute permission to stored procedure xp_readerrorlog
GRANT EXECUTE ON xp_readerrorlog TO _USERNAME_or_GROUPNAME
Even after this you might get a message: The user does not have permissions to perform this action Error: 297.
In order to correct this you must grant VIEW SERVER STATE to the login:
GRANT VIEW SERVER STATE TO _USERNAME_or_GROUPNAME
Now the user or group should have access to view the SQL error logs.
I needed a way to check for the sysadmin role on an instance. I came up with this small query that does that:
SELECT Name,IS_SRVROLEMEMBER('sysadmin', name) AS SYSADMIN
WHERE (SELECT IS_SRVROLEMEMBER('sysadmin', name)) = 1