SQL Server – Installation – v2019

Background

Needing to install MS SQL Server v2019 so that I can start evaluating it.

Artifacts

Please go here to download SQL Server 2019 :-

  1. Community Technology Preview ( CTP )
    • SQL Server 2019 – CTP
      • SQL Server 2019 CTP
        CTP

 

Installation

Outline

Install Steps

  1. Product Key
    • Choices are
      • Specify a Free Edition
      • Enter the Product Key
    • Accept default of Free Edition ( Evaluation )
  2. License Terms
    • Accept the License Terms
  3. Install Setup Files
    • The files that will be used for the setup are themselves installed
  4. Feature Selection
    • Please review features
    • And, select the features you want
      • Database Engine
        • We want the Database Engine
          • Please specify the drive and folder where the database engines files will be installed
          • By default the system drive ( C: ) will be targeted
  5. Instance Configuration
    • Choices
      • Choose Instance
        • Default
        • Named Instance
      • If a default instance is not already installed, consider accepting the default choice
      • Else, if another instance already exists, please specify a name to assign the instance you are installing
    • Our choice
      • We have a default instance installed on same box
      • We entered a new name
  6. Server Configuration
    • Choices
      • Review Services
        • Service Account Name
        • Startup Type
    • Our choice
      • We chose go with the default Service Account Names
  7. Database Configuration
    • TempDB
      • Data files
        • Drive and Folder to locate TempDB Data Files
        • Initial Size
        • File Growth
        • Based on number of CPU Cores
          • Choose # of TempDB files
      • Log Files
        • Drive and Folder to locate TempDB Log Files
  8. Analysis Services Configuration
    • Tab :- Server Configuration
      • Please add the accounts ( Active Directory and Local Accounts that should have access )
  9. Ready To Install
  10. Installation Progress
  11. Complete
    • Restart Computer
      • As there are pending file operations, please advance towards completing the install, gracefully exiting the Install application,  and restart your computer

Server Network Configuration

Post Installation and Computer Reboot Steps:

  1. Restarted Computer
  2. Launch Server Network Configuration
  3. Adjust Instance Network Configuration

Screenshots

Install Steps

Product Key

productKey.01.20190411.1126AM.PNG

License Terms

licenseTerms.01.20190411.1127AM.PNG

Install Setup Files

installSetupFiles.01.20190411.1127AM.PNG

Feature Selection

Initial

FeatureSelection.01.20190411.1128AM.PNG

Customize

FeatureSelection.02.20190411.1130AM.PNG

Instance Configuration

Initial

instanceConfiguration.01.20190411.1131AM.PNG

Revise

instanceConfiguration.02.20190411.1132AM.PNG

 

Server Configuration

Initial

serverConfiguration.01.20190411.1134AM.PNG

Revise

serverConfiguration.02.20190411.1136AM.PNG

Database Configuration

Initial

DatabaseEngineConfiguration.01.20190411.1137AM.PNG

Revise
Revise – Tab – TempDB

DatabaseEngineConfiguration.Tab.TempDBFiles.01.20190411.1138AM.PNG

Analysis Services Configuration

Revise

AnalysisServicesConfiguration.01.20190411.1140AM.PNG

Ready to Install

Installation Progress

installProgress.01.20190411.1146AM.PNG

Complete

Restart Computer

ComputerRestartRequired.01.20190411.1205PM.PNG

Complete
Complete.01.20190411.1206PM.PNG

Sql Server Configuration Manager

SQL Server Network Configuration

Protocols
Protocol – TCP/IP Properties

SQLServerConfigurationManager.01.20190412.0117PM.PNG

 

Summary

Considerations

  1. Upgrade Path
    • Edition – Evaluation
      • You can advance a previous version to v2019 only if the current version is Evaluation
  2. Post Upgrade
    • Review Services
      • Please stop and disable previous services
    • Amend Network Ports
      • Only one service can use a network port at a time
      • Review each serview and amend the Network Port it is using

 

SQL Server 2005 – Express Edition – Manual Updates

Background

As promised a couple of posts back, over this last weekend we successfully patched an old SQL Server Express v2005 RTM Edition  to v2005 SP4.

In our initial post, we spoke of the difficulty we experienced trying to patch v2005 using Windows Update.

In this post we skipped having Automatic Updates apply the patch and downloaded the patch and manually applied it.

We stumbled badly but thankfully discovered workarounds through Google.

 

TroubleShooting

Event Viewer

Error 1260 – Windows cannot open this program because it has been prevented by a software restriction policy

Error Image

EventID_11260_MicrosoftSQLServerExpressEdition_Error_1260_0237PM (Brushed Up)

 

Textual

  1. Source :- MsiInstaller
  2. Type :- Error
  3. Event ID :- 11260
  4. Product :- Microsoft SQL Server Express Edition – Error 1260.  Windows cannot open this program because it has been prevented by a software restriction policy.

 

Remediation

Outline
  1. Get Application’s GUID
  2. Add it to the exception list
ScreenShot

ScreenShot – Before Adding Application GUID

WLRegistry_20170319_0243PM

ScreenShot – Before Adding Application GUID { 2AFFDD7* }

WLRegistry_20170319_0244PM

SQL Server – Error Log

Error: 17207, Severity: 16, State: 1 – FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file

Error Text

2017-03-19 14:02:52.13 spid5s      Starting up database ‘master’.
2017-03-19 14:02:52.18 spid5s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:52.18 spid5s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\master.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:52.59 spid5s      Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.

2017-03-19 14:02:52.82 spid5s      Starting up database ‘mssqlsystemresource’.
2017-03-19 14:02:53.13 spid7s      Starting up database ‘model’.

2017-03-19 14:02:53.18 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:53.18 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\model.mdf’. Diagnose and correct the operating system error, and retry the operation.

2017-03-19 14:02:54.16 spid7s      Clearing tempdb database.
2017-03-19 14:02:54.99 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:54.99 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\tempdb.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:55.97 spid7s      Starting up database ‘tempdb’.
2017-03-19 14:02:55.99 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:55.99 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\tempdb.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:56.18 spid5s      Recovery is complete. This is an informational message only. No user action is required.

Explanation

It is likely another application is interfering with the database’s engine access to the database files.

It could be Anti-Virus, OS File System drivers and filters or diagnostic tools.

In our case it could have been the fact that we were running SysInternal’s process monitor and chosen to focus in on the folder where how SQL Server data files are kept.

 

SQL Server Install – Summary.txt

Exit Code Returned: 1260

Error Text

**********************************************************************************
Product Installation Status
Product                   : Express Database Services (V2005SQLEXPRESS)
Product Version (Previous): 1399
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9Express_Hotfix_KB2463332_SQLEXPR.EXE
SQL Express Features      : SQL_Data_Files,SQL_Engine,SQL_SharedTools
Error Number              : 1260
Error Description         : Unable to install Windows Installer MSI file
———————————————————————————-

**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 1260

 

Remediation

Remediation – Change SQL Server Service Account to Domain Account

Review Account SQL Server is running under.  If it is running under “NT Authority\Network Services”, then change it to use to use a “Domain Account”.

Steps Outline

  1. Launch SQL Server Configuration Manager
  2. On the left panel, access the SQL Server Services Node
  3. On the right panel, review the list of services
  4. In our case, our targeted instance is v2005 SQLExpress
    • We reviewed all of the corresponding services for that instance
    • Currently, they are set to NT AUTHORITY\NETWORK SERVICES
    • We changed them to run under a least privileged domain account

ScreenShot

Sql Server Configuration Manager – List of Services

SQLServerConfigurationManager-NetworkService-20170319-0119PM (Brushed Up)

 

Sql Server Configuration Manager – Changing Service Account from Network Service to ….

changeServiceAccount_20170319_0110PM (Brushed Up)

 

Sql Server Configuration Manager – Changed Service Account from Network Service to Domain Account

SQLServerConfigurationManager-ChangedToDomainAccount-Services-20170319-0113PM (Brushed Up)

 

 

Summary

As always took the long way home.

Here are all the things we did:

  1. Whitelisted SQL Server v2005 SP4
    • Captured App GUID through Event Viewer or summary.txt ( SQL Server Log File )
  2. Experienced “Error: 17207, Severity: 16, State: 1 – FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file”
    • Stopped SysInternal’s Process Monitor
  3. Ran into Error Code 1260
    • Changed Service Account from “NT Authority\Network Services” to Domain Account
    • Ensured that the Service Account has full permissions on all folders that SQL Server has data and log files