SQL Server Patching Steps for Standalone Instance


Before patching change window starts we have to perform below steps :

1.     Log Onto the Standalone SQL Windows box.

2.    Check for Missing msi and msp files . If there are any missing of msi\msp files found then please go through below links and find how to fix

https://support.microsoft.com/en-in/help/969052/how-to-restore-the-missing-windows-installer-cache-files-and-resolve-p                                                                                  OR

You can use MSI-Moksha.exe  tool  to find out missing msi and msp files ,also you can repair the missing files by using this tool itself .

3.     If there are any – repair the missing files.

4.     And Make sure that above steps are completed successfully.

5.     During maintenance Window perform below steps :·       

Keep the SQL Database servers into maintenance mode·       

 Run the patch executable·        

Make sure all rules have passed. If not take necessary actions to pass the rules·       

 In the next wizard ,Accept the license terms and click proceed·       

 In next wizard, Select all the SQL server features which needs to be Upgraded·       

 In next wizard, Files in use will be checked – once checking completes click next.

In next wizard, just go through the list of features and click Upgrade·     

We need to wait until it completes and make sure the patch is successfully applied. See the Summary. txt error log located at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log and check the detail logs for troubleshooting if failure   happens

6.       Reboot SQL window box if required

7.       Perform SQL Health check

8.     Once above steps are completed successfully you can remove the SQL Box from MM(Maintenance Mode).

9.   Validate in the Monitoring Tool ( if you have any ) to make sure the corresponding Patched Instances are being correctly monitored.
Once the above all steps completed successfully Connect to SQL Instances which are patched and run below script to see SQL Version is at correct patch level: 

SERVERPROPERTY(‘Edition’) AS ‘Edition’,
SERVERPROPERTY(‘ProductVersion’) AS ‘ProductVersion’,
SERVERPROPERTY(‘ProductLevel’) AS ‘ProductLevel’,
SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS ‘ResourceLastUpdateDateTime’,
SERVERPROPERTY(‘ResourceVersion’) AS ‘ResourceVersion’

Back-Out Plan :
If you encountered with any issues while applying Patch perform below steps
Log onto Standalone SQL windows box

  • Open Add and Remove Programs from control panel
  • Check the Show Updates\view installed updates box on add remove programs window 
  • on the right pane, Go through the page and locate (Sp info), click the box to mark it and hit remove
  • Follow the wizard until the feature is completely removed
  • Reboot if required
  • Perform SQL Health check.

Best Practice Steps After SQL Server Installation

Post SQL Server Installation Steps :

  1. Add the group SQLServerMSSQLUser$ServerName$InstanceName to the Root path of drive(ex: D) with Full Control, and List Folder Contents Permissions for Data, Log and Tempdb Drives.
  2. Add the group SQLServerMSSQLUser$ServerName$InstanceName to SQLData folder on Data and Tempdb Drives with Full Control.
  3. Add the group SQLServerMSSQLUser$ServerName$InstanceName to SQLLogs folder on Log Drive with Full Control.
  4. Remove the Active Directory (AD) Service User Account from the Root Path. (This will decouples the Service Account explicitly and rely on the group) .
  5. Add the groups SQLServerMSSQLUser$ServerName$InstanceName, SQLServerSQLAgentUser$ServerName$InstanceName, or any other group accounts to Backup or sharing folders as needed.
  6. In Local Security Policy, add the group SQLServerMSSQLUser$ServerName$InstanceName to policies Lock Pages in Memory objects and Perform Volume Maintenance Tasks.
  7. Verify with Antivirus(Ex: Macaffe) team if they have excluded the folders Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.
  8. Check\Verify backup is excluding mdf, ndf, and ldf files.
  9. Remove the Accounts Builtin\Administrators and Builtin\user logins.
  10. Enable the option All Login Auditing in the SQL Server Security Settings
  11. Enable the protocol TCP/IP and also change default port 1433.
  12. Enable the option remote DAC connections.
  13. Enable xp_cmdshell, OLE Automation and SQLCLR for the SQL Server Instance as required.
    a. Configure xp_cmdshell proxy account as needed.
  14. Enable the option Database Mail and configure default private and public accounts.
  15. Configure SQL Error Log retention with 99 log files
  16. Configure SQL Agent job to perform nightly .
  17. Configure SQL Server Maintenance Plan jobs for system and user database backups, index maintenance, CHECKDB, statistics updates, history cleanup and backup cleanup.
  18. Move the MSDB Database files to SQLData and SQLLogs .
  19. Reconfigure Tempdb Database with data files equal to 1/2-1/4 the physical CPU’s on the server based on load characteristics. Set all data files to equal size based on load characteristics in 4096MB increments for Datafiles, and 1024MB increments for Log files. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  20. Enable Trace Flag 1118 on SQL Server 2005/2008/2008R2/2012/2014 and 2016 for Tempdb.
  21. Change the recovery model for Model database to SIMPLE recovery, 2048MB as default datafile size and 1024MB as default logfile size. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  22. Set Max Server Memory based on RAM installed on Server and installation type (Latest\New Servers are all 64bit, but enable the option AWE as needed on 32 bit servers).
    a. 8GB RAM = 6144 Max Server Memory
    b. 16GB RAM = 12228 Max Server Memory
    c. 32GB RAM = 28672 Max Server Memory
    d. The above values are base values that can later be adjusted based on the Memory\Available MBytes counter value > 150 on the Server( based on your environment ).
    e. Memory configuration on clustered Instances should account for multiple instances on a one physical node in case of fail over.
    f. In general, we calculate Max. Server Memory using the formula: Max. Server Memory for a SQL Server Instance = (Total RAM available to the OS) – {(OS: memory pool, filesystem cache, PTE, desktop heap, Driver Images etc…) + (Non-buffer Pool region of SQL Server allocated to Multi Page Allocators, Worker Threads, COM, Extended SPs, Backup Buffers, CLR, Linked Server…) + (SQL Server Agent, Replication Agents, Bulk Copy, SSRS, SSAS, SSIS, and Full Text) + (Log shipping file copy depending on the size of log backups) + (Other SQL Server instances running in the box) + (Other applications running in the box (Antivirus, Monitoring Softwares, Compression softwares etc…) }
  23. Set max degree of parallelism sp_configure option based on the number of physical CPU cores installed and anticipated workload
    a. For OLTP servers, generally we set to 1/2 or 1/4 of the physical cores available on the server.
    b. we can Adjust up or down based on wait stats and load.
  24. Set cost threshold of parallelism sp_configure option based on the anticipated load.
    a. General default value of 5 is low for most OLTP workloads and should be increased.
    b. Base value of 20-25 used for most server installs.
  25. Add AD login (standard for environment and locked out in AD by default) for patching and emergency server access to Local Administrators Group.
  26. Set SA user password to standardized password that is changed quarterly on all servers and maintained in password safe.
  27. Install the SQL Server Performance Dashboard Reports
  28. Create default alerts for severities 16 through 25.
    SQL Server’s alerting system has the ability to notify operators whenever major things break inside the database. These include running out of space in log files, backup failures, failed logins and other things DBAs just need to be aware of. Don’t rely on this as your only SQL Server monitoring system, because it only sends alerts when it’s too late to take proactive action, but still, it’s better than nothing.

The below script will set up an alert for severity 16. Copy this and repeat the same thing for 17-25, but change ‘Database Team’ to be the name of your default operator. Notice that @delay_between_responses is set to 60 – that means if it sends out an alert, it won’t repeat that same alert for 60 seconds. This is useful because when a database runs out of drive space, for example, all hell will break loose for a minute or two, and you don’t want hundreds of emails and pages per minute.

USE [msdb]
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7