SQL server Uninstall Fails

If the removal process fails, review the SQL Server SetupLog Files to determine the root cause.

SQL Server Setup creates log files in a dated and time-stamped folder within %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log by default, where nnn are numbers that correspond to the version of SQL that’s being installed. The time-stamped log folder name format is YYYYMMDD_hhmmss. When Setup is executed in unattended mode, the logs are created within %temp%\sqlsetup*.log. All files in the log folder are archived into the Log*.cab file in their respective log folder.

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

The numbers in the path nnn correspond to the version of SQL being installed. In the above picture, SQL 2017 was installed, so the folder is 140. For SQL 2016, the folder would be 130, and for SQL 2014 the folder would be 120.

SQL server setup completes three basic phases:

  1. Global Rules verification: validates basic system requirements
  2. Component update: checks to see if there are any updates available for the media being installed
  3. User-requested action: allows the user to select and customize features

This workflow produces a single summary log, and either a single detail log for a base SQL Server installation, or two detail logs for when update, such as a service pack, is installed along with the base installation.

Additionally, there are datastore files that contain a snapshot of the state of all the configuration objects that are being tracked by the setup process, and are useful for troubleshooting configuration errors. XML dump files are created for each execution phase and are saved in the Datastore log subfolder under the time-stamped log folder.

The following sections describe SQL Server Setup log files.

Summary.txt file

Overview

This file shows the SQL Server components that were detected during Setup, the operating system environment, command-line parameter values if they are specified, and the overall status of each MSI/MSP that was executed.

The log is organized into the following sections:

  • An overall summary of the execution
  • Properties and the configuration of the computer where SQL Server Setup was run
  • SQL Server product features previously installed on the computer
  • Description of the installation version and installation package properties
  • Runtime input settings that are provided during install
  • Location of the configuration file
  • Details of the execution results
  • Global rules
  • Rules specific to the installation scenario
  • Failed rules
  • Location of the rules report file

Location

The summary.txt is located within %programfiles%\MicrosoftSQL Server\nnn\Setup Bootstrap\Log\.

To find errors in the summary text file, search the file by using the “error” or “failed” keywords.

Summary_<MachineName>_YYYYMMDD_HHMMss.txt file

Overview

The summary_engine base file is similar to the summary file and is generated during the main workflow.

Location

The Summary_<MachineName>_YYYYMMDD_HHMMss.txt file is located at %programfiles%\MicrosoftSQL Server\nnn\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\.

Detail.txt file

Overview

Detail.txt is generated for the main workflow such as install or upgrade, and provides the details of the execution. The logs in the file are generated based on the time when each action for the installation was invoked. The text file shows the order in which the actions were executed, as well as their dependencies.

Location

The detail.txt file is located within %programfiles%\MicrosoftSQL Server\nnn\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Detail.txt.

If an error occurs during the Setup process, the exception or error is logged at the end of this file. To find the errors in this file, first examine the end of the file followed by a search of the file for the “error” or “exception” keywords

MSI log files

Overview

The MSI log files provide details of the installation package process. They are generated by the MSIEXEC during the installation of the specified package.

Types of MSI log files:

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

Location

The MSI log files are located at %programfiles%\MicrosoftSQL Server\nnn\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\<Name>.log.

At the end of the file is a summary of the execution, which includes the success or failure status and properties. To find the error in the MSI file, search for “value 3” and review the text before and after.

ConfigurationFile.ini file

Overview

The configuration file contains the input settings that are provided during installation. It can be used to restart the installation without having to enter the settings manually. However, passwords for the accounts, PID, and some parameters are not saved in the configuration file. The settings can be either added to the file or provided by using the command line or the Setup user interface. For more information, see Install SQL Server 2016 Using a Configuration File.

Location

The ConfigurationFile.ini is located at %programfiles%\MicrosoftSQL Server\nnn\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\.

SystemConfigurationCheck_Report.htm file

Overview

The system configuration check report contains a short description for each executed rule, and the execution status.

Location

The SystemConfigurationCheck_Report.htm is located at %programfiles%\MicrosoftSQL Server\nnn\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\.

SQL Server Uninstallation

Today we are discussing about uninstall a stand-alone instance of SQL Server. By following the steps, you also prepare the system so that you can reinstall SQL Server.

Considerations :

  • For Successful uninstall of SQL Server, you must have a local administrator with permissions to log on as a service.
  • If your computer has the minimum required amount of physical memory, increase the size of the page file to two times the amount of physical memory. Insufficient virtual memory can result in an incomplete removal of SQL Server.
  • On a system with multiple instances of SQL Server, the SQL Server browser service is uninstalled only once the last instance of SQL Server is removed. The SQL Server Browser service can be removed manually from Programs and Features in the Control Panel.
  • Uninstalling SQL Server deletes tempdb data files that were added during the install process. Files with tempdb_mssql_*.ndf name pattern are deleted if they exist in the system database directory.

Preparation :

  1. Back up your data. Either create FULL Backup of all databases, including system databases, or manually copy the .mdf and .ldf files to a separate location. The master database contains all system level information for the server, such as logins, and schemas. The msdb database contains job information such as SQL Server agent jobs, backup history, and maintenance plans. The files that you must save include the following database files:
    • 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. Stop all SQL Server services. We recommend that you stop all SQL Server services before you uninstall SQL Server components. Active connections can prevent successful uninstallation.

3. Use an account that has the appropriate permissions. Log on to the server by using the SQL Server service account or by using an account that has equivalent permissions. For example, you can log on to the server by using an account that is a member of the local Administrators group.

Uninstallation ( Win 2008 – Win 2012 R2 ):

To uninstall SQL Server from Windows Server 2008, Windows Server 2012 and Windows 2012 R2, follow these steps:

  1. To begin the removal process, navigate to the Control Panel and then select Programs and Features.
  2. Right-click Microsoft SQL Server (Version) (Bit) and select Uninstall. For example, Microsoft SQL Server 2012 (64-bit).

3. Select Remove on the SQL Server dialog pop-up to launch the Microsoft SQL Server installation wizard.

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

5. On the Select Features page, specify the features to remove from the specified instance of SQL Server.

6. On the Ready to Remove page, review the list of components and features that will be uninstalled. Click Remove to begin uninstalling

7. Refresh the Programs and Features window to verify the SQL Server instance has been removed successfully, and determine which, if any, SQL Server components still exist. Remove these components from this window as well, if you so choose.

Uninstallation ( Win 2016 – Win 2019) :

To uninstall SQL Server from Windows 10, Windows Server 2016, Windows Server 2019, and greater, follow these steps:

  1. To begin the removal process navigate to Settings from the Start menu and then choose Apps.
  2. Search for sql in the search box.
  3. Select Microsoft SQL Server (Version) (Bit). For example, Microsoft SQL Server 2017 (64-bit).
  4. Select Uninstall.

5. Select Remove on the SQL Server dialog pop-up to launch the Microsoft SQL Server installation wizard.

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

7. On the Select Features page, specify the features to remove from the specified instance of SQL Server.

8. On the Ready to Remove page, review the list of components and features that will be uninstalled. Click Remove to begin uninstalling

9. Refresh the Apps and Features window to verify the SQL Server instance has been removed successfully, and determine which, if any, SQL Server components still exist. Remove these components from this window as well, if you so choose.

SQL Server Patching Steps for Standalone Instance

Implementation

Before patching change starts you need to perform below steps :

1.     Log Onto the Standalone SQL Windows box.

2.    Check for Missing msi and msp files . Please check below link how to find out missing files and fix it


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 :·       

 Put SQL windows box into maintenance mode·       

 Run the patch executable·        

Make sure all rules have passed. If not take corrections·       

 Accept the license term on next page and proceed·       

 Select all SQL server features that needs to be Upgraded·       

 Files in use will be checked on next page – hit next when checking complete·        

Verify the list of features that will be upgraded and hit Upgrade·     

Wait until it completes and make sure it’s successful. 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 above all steps are completed successfully Connect to SQL Instances that were patched and run below SQL to see SQL Version is at correct 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 any issues with Patching perform below steps
Log onto Standalone SQL windows box

  • Open Add and Remove Programs from control panel
  • Check the Show Updates box on add remove programs window 
  • Go down the page and locate (Sp info), check 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 SQLServerMSSQLUser$ServerName$InstanceName group to the Root path with Full Control of D, and List Folder Contents Permissions for Data, Log and Tempdb Drives.
  2. Add the SQLServerMSSQLUser$ServerName$InstanceName group with Full Control of SQLData folder on Data and Tempdb Drives.
  3. Add the SQLServerMSSQLUser$ServerName$InstanceName group with Full Control of SQLLogs folder on Log Drive.
  4. Remove the AD Service User Account from the Root Path. (This decouples the Service Account explicitly and relys on the group)
  5. Add the SQLServerMSSQLUser$ServerName$InstanceName, SQLServerSQLAgentUser$ServerName$InstanceName, or other group accounts to any Backup, or processing folders as needed.
  6. In the Local Security Policy, add the SQLServerMSSQLUser$ServerName$InstanceName group to the Perform Volume Maintenance Tasks and Lock Pages in Memory objects.
  7. Verify the Antivirus( Macaffe) team excluded Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.
  8. Verify backup is excluding mdf, ndf, and ldf files.
  9. Remove Builtin\Administrators and Builtin\user logins.
  10. Enable All Login Auditing in the SQL Server Security Settings
  11. Enable TCP/IP and change default port from 1433.
  12. Enable remote DAC connections.
  13. Enable as required xp_cmdshell, SQLCLR, and OLE Automation for the SQL Server Instance.
    a. Configure xp_cmdshell proxy account as required.
  14. Enable DatabaseMail and configure default public and private accounts.
  15. Configure SQL Error Log retention for 99 log files
  16. Configure SQL Agent job to perform nightly log rollover.
  17. Configure SQL Server Maintenance Plans for system and user database backups, CHECKDB, index maintenance, statistics updates, backup cleanup, and history cleanup.
  18. –Move MSDB Database files to SQLData and SQLLogs respectively.
  19. Reconfigure Tempdb with data files equal to 1/2-1/4 the physical CPU’s on the server based on load characteristics. Set data files to the same 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 2000/2005/2008/2008R2/2012/2014 and SQL Server 2016 for Tempdb.
  21. Set Model database to SIMPLE recovery, 2048MB default datafile size and 1024MB default logfile size. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  22. Set Max Server Memory based on installed RAM and installation type (Newer Servers are all 64bit, but enable 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. These are base values that will later be adjusted based on the Memory\Available MBytes counter being > 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, you should 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, generally set to 1/2 or 1/4 of the physical cores available on the server.
    b. Adjusted 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

A side-by-side upgrade/Migration consists of installing SQL Server Latest Version and moving the databases from the old instance to the new instance. The side-by-side method gives us a chance to test the effects SQL Server 2Latest Version will have on the application before moving from the older version. The new instance can be installed on a second server, or we can use the same server provided it meets the installation requirements. Migration provides access to two instances of the system, letting us verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

Once we have installed the new instance of SQL Server Latest Version, there are three methods for moving the databases to the new instance:

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

Installing SQL Server Latest Version in this side-by-side approach is no different from doing a fresh install. However, with a side-by-side upgrade, we need to worry is about how to migrate the database to the new instance.

Copy database Wizard: The Copy Database Wizard lets us move or copy databases and their objects easily from one instance to another instance, with no server downtime. We can use the Copy Database Wizard to copy our database to an upgraded instance of SQL Server Latest Version. We are given the option to make a copy of the database or completely move the database. We may also choose to copy the database by using the detach and attach method, or by using SQL Management Objects (SMO). If we use the detach and attach method from the wizard, we need to make sure that there are no users trying to access the database before running the wizard. If we use SMO, the database will remain online during the entire process. We can also choose to move any database-related objects, such as logins and jobs. We should point out that while we can copy logins using the Copy Database Wizard, for security reasons, the wizard creates the login on the destination server with a random password and then disables the login.

Using this wizard, we can do the following.
1) Pick a source and destination server.
2) Select databases to move or copy.
3) Specify the file location for the databases.
4) Create logins on the destination server.
5) Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.
6) Schedule when to move or copy the databases.

NOTE: The Copy Database Wizard creates a SQL Server Agent job that executes an SSIS package. We need to make sure that we have Integration Services installed and the SQL Server Agent running on the destination server prior to executing the Copy Database Wizard.

Database Backup and Restore: Using the backup and restore method is a good way to copy the database to the new instance without impacting the availability of the current database. All we need to do is take a full backup of the current database, copy that backup to the new location, and restore it. SQL Server will upgrade the database during the restore process. The result will be a SQL Server Latest Version database that we will not be able to move back to an earlier release.
We need to copy any objects outside the database, such as logins and jobs, since we cannot restore system databases to a newer version of SQL Server.

Detaching and Attaching: We can prefer the detach and attach method when permanently moving databases to a new instance of SQL Server. By moving each file itself instead of a copy of the file at any given point in time, we can be sure that we have captured the exact state of the database as it existed on the previous instance. Since the database is detached, it will be inaccessible for any transactions, ensuring that no data will be committed on the old system during the upgrade. Detaching the database also helps to validate that all the connections have been reconfigured to point to the new system.
We can use the sp_detach_db stored procedure to detach a database. We should also set the database to single-user mode and immediately roll back any transactions before trying to detach the database.

T-SQL Script to Detach a Database:

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

To attach a database in SQL Server Latest Version, we should use the CREATE DATABASE statement with the FOR ATTACH clause. This statement and clause replace the sp_attach_db stored procedure that was previously used to attach a database. The sp_attach_db stored procedure has been deprecated and will be removed in a future release. We will also need to specify the locations of the data files that are going to be attached.

T-SQL Script to Attach a Database:

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

We can detach and attach databases using the GUI in SQL Server Management Studio.
To detach a database using the GUI, we can right-click on the database we want to detach, select Tasks, and then select Detach from the context menu. This will bring up the Detach Databases screen. Select OK to detach the database.
To attach a database using the GUI, we can right-click on the Databases folder and select Attach from the context menu. This will bring us to the Attach Databases screen. Selecting Add will bring up the Locate Database Files screen, which will allow us to navigate to the data file we would like to attach. Once we have selected the data file, select OK to close the Locate Database Files screen and OK once again on the Attach Databases screen to close the screen and attach the database.

Note : Above are not only the methods to migrate database\Instance, its all based on Databases Size\Client Requirement\Downtime …etc.,

There so many other Methods to migrate the Databases like we can configure High Availability(Logshipping,Mirroring,Replication,AlwaysON) ,backpac, Import and Export, SSMA tool ..etc.,

As I said it all depends on Environment and discussion having with Client.

And the Latest one which I come to know when searching for Migration on Internet is DBATOOLS , its a Awesome way to migrate the Databases or Entire Instance very quick and easy method using Powershell.