Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server restart validation checklist


By:   |   Last Updated: 2010-07-30   |   Comments (2)   |   Related Tips: 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.

SQL Server Configuration Manager

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)

Check the status and configuration of the MSDTC service
figure A
There is a chance of an MS-DTC security configuration change
figure B
Go to Control Panel
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

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

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

check the status of the cluster resources using this DOS command

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

register the SPN using the below DOS command

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 [email protected]@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


Last Updated: 2010-07-30


next webcast button


next tip button



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, October 07, 2015 - 3:10:23 PM - vish Back To Top

HI Jugal,

 

As ussual excellent.   Thanks for nice posts. I  always like your posts and found them useful.

 

regards

Vish


Friday, August 27, 2010 - 10:28:03 PM - Ameer Back To Top
Very well Explained


Learn more about SQL Server tools