By: Jugal Shah | Comments (2) | Related: More > Database Administration
Problem
Many times there is the need to restart the SQL Server services, this could be a reboot of the server after patching, service pack installation or due to some other reason. Sometimes we may face issues after the restart for example the SPN is not registered, a database is offline etc... For the solution you can use the below standard SQL Server validation checklist after you restart your SQL services.
Solution
The following is a list of steps I use after restarting one of my SQL Servers.
Pre Validation Checklist
1. Keep an inventory of all versions, editions, languages and list of running SQL Services for your SQL Servers
2. Maintain test systems that match the configuration of you production systems and are readily available for testing new patches.
3. Backup all the System and User databases
4. Keep the Service Account password handy, so if the service restart failed due to a bad password you can correct it.
5. Check the server for CLR initialization status. You can check the status and enable CLR using the below SQL commands
select * from sys.dm_clr_properties -- Many times CLR initialization is failed due to memory --pressues or leakage. You can configure the CLR using below command. EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO --To initialize CLR you have to create an assembly and call it using T-SQL
6. Suppress Alerts from your monitoring tools
7. Stop Replication, Log Shipping
Post Validation Checklist
Step 1
Check the status of the SQL Server services. You can execute the below commands in SSMS to check the status of the SQL Server services.
EXEC xp_servicecontrol N'querystate',N'MSSQLServer' EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT' EXEC xp_servicecontrol N'querystate',N'msdtc' EXEC xp_servicecontrol N'querystate',N'sqlbrowser' EXEC xp_servicecontrol N'querystate',N'MSSQLServerOLAPService' EXEC xp_servicecontrol N'querystate',N'ReportServer'
You can also use the SQL Server Configuration Manager to check the services status.
Step 2
Check the status and configuration of the MSDTC service. There is a chance of an MS-DTC security configuration change.
Go to Control Panel -> Administrative Tools -> Component Services -> Expand the Computer Node -> Right click on My Computer (figure A) -> Click on Properties -> MS DTC Tab -> Security Configuration (figure B) -> Adjust security settings if needed (figure C)
figure A
figure B
figure C
Step 3
Validate all databases are online (they should not be in a restoring or suspect state). You can do this by executing the below query or using SSMS.
select name,databasepropertyex(name, 'status') [status] from sysdatabasesOR select name from sys.databases where state_desc <> 'online'
Using SSMS
Step 4
Check the SQL Server Error Logs for any errors by executing below procedure
EXEC xp_readerrorlog 0,1,"Error",Null
You can also check the error log using SSMS object explorer
Step 5
If your server is clustered, check the Cluster Group and verify the instances are on the right node.
You can check the status of the cluster resources using this DOS command:
cluster clusterName resource >> clusterResourcestatus.txt
If the instances are not running on the correct node, failover using the below DOS command:
-- Below command will move the Node 1/SQL Server resources --(ex. Services, Drives, Network Name) to cluster SQL Server Group cluster group "SQL Server Group" /move: Physical_Name_Of_Node1 -- Below command will move the Node 2/SQL Server resources --(ex. Services, Drives, Network Name) to cluster SQL Server Group INST2 cluster group "SQL Server Group INST" /move: Physical_Name_Of_Node2
OR
Go to Control Panel ->Administrative Tools -> Cluster Administrator -> Connect to Cluster Node click on Move Group to failover the node
Step 6
Check the Service Principal Name for the SQL Server instance using the setSPN utility. The SPN is a mapping between a principal name and the Windows account that started the server instance service. This is needed because the client will use the server's hostname and the TCP/IP port to which it connects to compose an SPN. If the SPN mapping has not been performed, then the Windows security layer will be unable to determine the account associated with the SPN and Kerberos authentication will not be used. In an attempt to facilitate this, the SQL Server 2005/2008 instance will automatically try to register the SPN with the AD at startup if TCP/IP is enabled. The problem is, however, that only a domain administrator or a Local System account has the authority to register an SPN.
If the SPN is not registered, you can register the SPN using the below DOS command
Setspn -L Service Account/Computer Name
Step 7
Check the authentication scheme by executing the below query in SSMS. The query will return the authentications scheme (KERBEROS or NTLM).
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
If the output of the below query is NTLM and your organization wants to use KERBEROS, follow these steps.
1. Both the server and the client computers must be members of the same Windows domain or members of trusted domains. See Step 6 to add the SPN.
2. The server's service principal name (SPN) must be registered in Active Directory.
3. The instance of SQL Server 2005/2008 must have the TCP/IP protocol enabled.
4. The client must connect to the instance of SQL Server 2005/2008 using the TCP/IP protocol.
Next Steps
- Add additional steps needed for your environment
- Document issues you come across after patching or rebooting
- Create a batch file for the post validation checklist if you are applying the patch/reboot to multiple servers
About the author
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
