SQL server Uninstall Fails

If the uninstallation fails, look for the root problem in the SQL Server SetupLog Files.

If the uninstallation fails, look for the root problem in the SQL Server SetupLog Files.
The log files are created by SQL Server Setup in a dated-time-stamped folder within %programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log
Version number is the SQL version number that is being installed by default. YYYYMMDD hhmmss is the format of the log folder name. The logs are created in the Temporary directory ‘ %temp%\sqlsetup*.log ‘, when Setup is run in unattended mode. All files in the log folder are archived into the Log.cab file in the log folder where they belong.

FilePath
Summary.txt%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log
Summary_<MachineName>_Date.txt%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log\YYYYMMDD_hhmmss
Detail.txt%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log\YYYYMMDD_hhmmss
Datastore%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log\YYYYMMDD_hhmmss\Datastore
MSI Log Files%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log\YYYYMMDD_hhmmss\<Name>.log
ConfigurationFile.ini%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log\YYYYMMDD_hhmmss
SystemConfigurationCheck_Report.htm%programfiles%\Microsoft SQL Server\Version number\Setup Bootstrap\Log\YYYYMMDD_hhmmss
For unattended installations%temp%\sqlsetup*.log

The figures on the path The SQL version number that is being installed is the version number. As you can see in the above photo, we have a SQL 2017 setup installed, thus we proceeded to folder 140. If you’re using SQL 2016, go to folder 130, and if you’re using SQL 2014, go to folder 120.

Three basic phases are completed by SQL server setup :

  1. Verification of Global Rules: Ensures that the basic system requirements are met.
  2. Component update: Checks to determine if there are any updates available for the media being installed
  3. Action requested by the user: allows the user to choose and personalize features.

For a base SQL Server installation, this workflow produces a single summary log and either a single detail log or two detail logs when any upgrade, such as a service pack (SP), is installed alongside the base installation.

Aside from the above files, there are datastore files, which include a snapshot of the state for all of the configuration objects that the setup process is tracking, and are important for diagnosing configuration issues. For each execution phase, XML dump files were created and saved in the Datastore log subfolder of the time-stamped log folder.

The sections below discuss SQL Server Setup log files.

Summary.txt file

Overview

This summary file lists the SQL Server components discovered during Setup, as well as the operating system environment, command-line parameter settings, and the status of each MSI/MSP that was run.

The following sections make up the log:

  • a general overview of the execution
  • The properties and settings of the PC on which SQL Server Setup was executed
  • Features of SQL Server that were previously installed on the PC
  • Properties of the installation package and description of the installation version
  • During installation, runtime input parameters are presented.
  • The configuration file’s location
  • Results of the execution in detail
  • Global guidelines
  • The installation scenario has its own set of rules.
  • Rules that didn’t work
  • The file containing the rules report Location.

Location

The summary.txt file can be found on location path %programfiles%\MicrosoftSQL Server\Version number\Setup Bootstrap\Log\.

You can search the summary text file for errors by using the terms “error” or “failed” in the search box.

Summary_<MachineName>_YYYYMMDD_HHMMss.txt file

Overview

The summary engine file looks like the summary file generated during the main workflow.

Location

The Summary_<MachineName>_YYYYMMDD_HHMMss.txt file is found at location path %programfiles%\MicrosoftSQL Server\Version number\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\.

Detail.txt file

Overview

Detail.txt is a text file that is created for the primary workflows, such as install/upgrade, and contains detailed information on the execution. The time when each step for the installation was initiated is used to construct the logs in the file. The text file explains the sequence in which the tasks were performed as well as their interdependencies.

Location

The detail.txt file is found at location path %programfiles%\MicrosoftSQL Server\Version number\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Detail.txt.

The exception or error is recorded at the conclusion of this file if an error occurs during the Setup procedure. To locate the errors in this file, open it and look for the words “error” or “exception” at the conclusion of the file.

MSI log files

Overview

The MSI log files contain detailed information about the package installation process. They were formed during the installation of the specified package by MSIEXEC.

Types of format for MSI log files :

  • <Feature>_<Architecture>_<Interaction>.log
  • <Feature>_<Architecture>_<Language>_<Interaction>.log
  • <Feature>_<Architecture>_<Interaction>_<workflow>.log

Location

The MSI log files are found at location path %programfiles%\MicrosoftSQL Server\Version number\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\<Name>.log.

Finally, there is a summary of the execution at the conclusion of the file, which includes the success or failure status as well as properties. Search for “value 3” in the MSI file and compare the text before and after to discover the issue.

ConfigurationFile.ini file

Overview

The input settings specified during installation are stored in the configuration file. It allows you to restart the installation without having to manually enter the parameters. However, the configuration file does not save the account password, PID, or some settings. The required settings can be added to the file or provided via the Setup user interface or the command line. See Install SQL Server 2016 Using a Configuration File for further details.

Location

The ConfigurationFile.ini is found at location path %programfiles%\MicrosoftSQL Server\Version number\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\.

SystemConfigurationCheck_Report.htm file

Overview

The system configuration check report includes a brief summary of each rule that was executed as well as the status of the execution.

Location

The SystemConfigurationCheck_Report.htm is found at location path %programfiles%\MicrosoftSQL Server\Version number\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\.

SQL Server Uninstallation

Today, we’ll talk about how to uninstall a standalone SQL Server instance.

Considerations :

  • To uninstall SQL Server Successfully, you must have a local administrator permissions to log on as a service.
  • Increase the size of the page file to two times the amount of physical memory if the computer has a minimum required amount of physical memory. An incomplete SQL Server uninstall can occur if there is insufficient virtual memory or if there is no memory available.
  • If a system has numerous instances of SQL Server, the SQL Server browser service is only uninstalled for the last instance of SQL Server. The SQL Server Browser service can be manually uninstalled from the Control Panel’s Programs and Features.
  • The tempdb data files that were added during the installation procedure are deleted when SQL Server is uninstalled. If files with the name pattern tempdb mssql *.ndf exist in the system database directory, they are destroyed.

Preparation :

  1. Back up your data. Create a FULL Backup of all databases, including system databases, or manually copy the .mdf and .ldf files to a separate location. The master database which contains all system level information of the server, which are logins, and schemas. The msdb database contains jobs information which are like SQL Server agent jobs, backup history, and maintenance plans. The following database files need to save:
    • master.mdf
    • msdbdata.mdf
    • Tempdb.mdf
    • mastlog.ldf
    • msdblog.ldf
    • Templog.ldf
    • model.mdf
    • Mssqlsystemresource.mdf
    • ReportServer[$InstanceName]
    • modellog.ldf
    • Mssqlsustemresource.ldf
    • ReportServer[$InstanceName]TempDB

2. All SQL Server services should be stopped. Uninstall SQL Server components before you begin. Active connections can prevent us from successfully uninstalling SQL Server. It is always advisable to disable SQL Server services.

3.Make use of a Login account with the appropriate permissions. Use the SQL Server service account or another account with equivalent access to RDP to the server. You can RDP to the server using a Login account that belongs to the local Administrators group, for example.

Uninstall (Windows 2008 – Windows 2012 R2):

Follow these procedures to uninstall SQL Server on Windows Server 2008, Windows Server 2012, and Windows 2012 R2:

  1. To begin the uninstall process, navigate to the Control Panel and then click on option Programs and Features.
  2. here we will be listing all services which are installed on the server , Select the SQL Server Right-click on Microsoft SQL Server (Version) (Bit) and click Uninstall. Example, MicrosoftSQLServer2012(64-bit)

3. Click on the option Remove for the SQL Server uninstallation, a dialog pop-up to launch the Microsoft SQL Server installation wizard opens.

4. On the Select Instance page wizard, use the drop-down box to select an instance of SQL Server to uninstall, or select the option to remove only the SQL Server shared features and management tools. then click Next to continue.

5. On the Select Features page wizard, Select or enable the check box of the features to remove from the selected instance of SQL Server.

6. On the Ready to Remove page wizard, cross check the list of components and features which are going to be uninstalled. Click Remove button to begin uninstalling.

7. Refresh the Programs and Features window to verify that the SQL Server instance was successfully removed and that no SQL Server components remain. If these components are no longer required, uninstall them as well.

Uninstall (Windows 2008 – Windows 2012 R2):

Follow these procedures to successfully uninstall SQL Server from Windows 10, Windows Server 2016, Windows Server 2019, and higher operating systems:

  1. To start uninstall process go to Settings from the Start menu and then choose Apps & features
  2. In the Search bar type sql and click search.
  3. Select the Microsoft SQL Server (Version) (Bit). For example, Example, MicrosoftSQLServer2017(64-bit).
  4. click on Uninstall.

5. Now click Remove option on the SQL Server dialog pop-up to get the Microsoft SQL Server installation wizard.

6. On the Select Instance page wizard, use the drop-down box to select an instance of SQL Server to uninstall, or select the option to remove only the SQL Server shared features and management tools then click Next to continue.

7. On the Select Features page wizard, Select or enable the check box of the features to remove from the selected instance of SQL Server

8. On the Ready to Remove page wizard, cross check the list of components and features which are going to be uninstalled. Click Remove button to begin uninstalling

9. Refresh the page Apps and Features window to verify the SQL Server instance has been successfully uninstalled, and also check if any of the SQL Server components still exist. uninstall these components as well if not needed.

SQL Server Patching Steps for Standalone Instance

Implementation

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&nbsp;                                                                                 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: 

SELECT
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]
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7
GO

Sql Server Migration

Side-by-side migration

Installing the most recent SQL Server version and migrating databases from the source(old) instance to the destination(new) instance constitutes a side-by-side upgrade/migration. Before transferring from the older version, we can use the side-by-side method to evaluate the application connection of the latest SQL Server version. The latest(new) SQL instance can be deployed on a second server that will function as the destination server, or it can be put on the same server as long as it fits the installation requirements. This side-by-side Migration allows us to test two different instances of the system, allowing us to compare and contrast the two. Both the source(old) and destination(new) systems remain operational during migration until the migration to the destination(new) instance is completed. All applications are redirected to the new instance at the conclusion of the migration, and the old instance is manually taken down or decommissioned.

There are three techniques for transferring databases to the newest version instance once we have installed the new instance of Latest SQL Server Version:

1) Copy DB Wizard
2) Database Backup and Restore
3) Detaching and Attaching

Installing the latest SQL Server version in this side-by-side approach is the same as installing it from scratch. However, when performing a side-by-side upgrade, we must consider how to move the databases to the most recent SQL Server instance.

Copy DB Wizard: The Copy DB Wizard allows us to simply relocate or copy databases and their objects from one instance to another without having to shut down the server. We can copy our database to an upgraded instance of Latest SQL Server Version using the Copy DB Wizard. This gives you the option of making a database clone or moving the database entirely. We can also select to copy the database using the detach and attach technique or SQL Management Objects in the wizard (SMO). According to the wizard Before running the wizard, make sure there are no user connections to the database if we utilise the detach and attach approach. According to the wizard If we pick the SMO option, the database(s) will remain online while the migration is in progress, which means the database will remain online while the migration is in progress. There are options for relocating database-related objects, such as jobs and logins, as well. We must point you that when using the Copy DB Wizard, the wizard establishes a login on the destination server with a random password and disables it for security reasons.

We can accomplish the following with the help of this wizard.

1) Choose a source and a destination server.
2) Choose the databases that need to be moved or copied.
3) Indicate where the databases’ files should be saved.
4) On the target server, create the logins.
5) Make copies of supporting objects, jobs, user-defined stored procedures, and error messages.
6) Make a plan for moving or copying the databases.

NOTE: The Copy Database Wizard generates a SQL Server Agent task that runs an SSIS package. Before launching the Copy Database Wizard, check sure SSIS (Integration Services) is installed and the SQL Server Agent service is functioning on the destination server.

Database Backup and Restore: The backup and restore approach is the best way to copy databases to a new instance without affecting the source database’s availability. All that is required is a thorough backup of the source database, its copy to the target location, and its restoration. Internally, SQL Server will upgrade the database while the restore operation is in progress. The database with the most recent SQL Server version, which cannot be downgraded to a previous version.
Because we can’t restore system databases to the most recent version of SQL Server, we’ll have to manually copy logins, jobs, and other data.

Detaching and Attaching: When permanently migrating databases to a new instance of SQL Server, the detach and attach technique may be preferred. The files from the source server will be transferred to the new instance. Because the database is disconnected, it will be inaccessible for any transactions, ensuring that no data is committed on the old system while the upgrade is in progress. Detaching the database allows you to double-check that all of your connections have been routed to the new instance.
The sp detach db stored procedure can be used to detach the database. Before attempting to remove the database, we must also put it to single-user mode and quickly roll back any transactions.

T-SQL Script to Detach a Database:

USE [master]
GO
ALTER DATABASE [XYA] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db ‘XYA’
GO

In the latest SQL Server version, we must use the CREATE DATABASE statement with the FOR ATTACH clause to attach a database. sp attach db is a deprecated stored procedure that will be removed in a future release. We must indicate the locations of the data files that will be attached.

T-SQL Script to Attach a Database:

USE [master]
GO
CREATE DATABASE [XYA] ON
( FILENAME = N’C:\MSSQL\DATA\XYA.mdf’ ),
( FILENAME = N’C:\MSSQL\DATA\XYA_log.ldf’ )
FOR ATTACH
GO

We can also use SQL Server Management Studio’s GUI to disconnect and attach databases.
To detach a database in the GUI, right-click on the database you wish to detach—>select Tasks—>select Detach from the menu. The wizard to Detach Databases will be launched. To detach the database, click OK.
Right-click on the folder Databases and select Attach from the menu to attach a database using the GUI. The wizard for attaching databases is launched. The Locate Database Files screen will appear once we select the Add option, allowing us to go to the data file we want to attach. Select OK to exit the Locate Database Files screen and OK again on the Data Files screen once we’ve selected the data file.

Note : Not only are the methods to migrate database instances listed above, but they are all depending on database size, client requirements, downtime, and other factors.

There are also additional methods for migrating databases, such as configuring High Availability (Logshipping, Mirroring, Replication, AlwaysON), backpac, Import and Export, the SSMA tool, and so on.

As I previously stated, everything is dependent on the environment and the client’s input.

And the most recent one that I discovered when searching for migration on the internet is DBATOOLS, which is an awesome way to migrate databases or entire instances using PowerShell in a very rapid and straightforward manner.