search

microsoft / mssql-docker

Official Microsoft repository for SQL Server in Docker resources
MIT License
1.74k stars 759 forks source link

SQL Server doesn't start anymore after installing a cumulative update #466

Closed DerDreschner closed 5 years ago

DerDreschner commented 5 years ago

I try to update the RTM windows base image of sql server 2017 developer. To achive that, I modified the dockerfile as following:

# escape=`
FROM mcr.microsoft.com/windows/servercore:ltsc2019 as builder

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

ENV sa_password='_' `
    sa_password_path='C:\ProgramData\Docker\secrets\sa-password' `
    attach_dbs='[]' `
    ACCEPT_EULA='_'

COPY . /

RUN [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12; `
    Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=840945' -UseBasicParsing -OutFile 'C:\\SQL.exe'; `
    Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=840944' -UseBasicParsing -OutFile 'C:\\SQL.box'; `    
    Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup; `
    .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=1 /UpdateSource='MU' /SQLSVCACCOUNT='NT AUTHORITY\System' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS; `
    Stop-Service MSSQLSERVER; `
    Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpdynamicports -Value ''; `
    Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpport -Value 1433; `
    Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver' -Name LoginMode -Value 2; `
    Remove-Item -Recurse -Force SQL.exe, SQL.box, setup; `
    Invoke-WebRequest -Uri 'https://dist.nuget.org/win-x86-commandline/v4.1.0/nuget.exe' -UseBasicParsing -OutFile 'C:\\nuget.exe'; `
    New-Item -Path 'C:\\tools' -ItemType Directory | Out-Null; `
    & 'C:\\nuget.exe' install Microsoft.Data.Tools.Msbuild -Version 10.0.61804.210 -OutputDirectory 'C:\\tools'; `
    Remove-Item -Path 'C:\\nuget.exe';

# Install latest CU
RUN Start-Process -Wait -FilePath .\Update.exe -ArgumentList '/action=patch /allinstances /quiet /IAcceptSQLServerLicenseTerms' -RedirectStandardOutput output.txt -RedirectStandardError err.txt; `
    $LastExitCode; `
    Get-Content -Path output.txt; `
    Get-Content -Path err.txt;

HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 1" ]

CMD .\Start.ps1 -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

Unfortunately, the SQL Server doesn't start anymore after installing the CU15 as it crashed while applying the updates to the databases. The last entries in the errorlog are the following:

2019-06-24 01:18:59.11 spid6s      Installing out of the box Collector objects
2019-06-24 01:18:59.11 spid6s       
2019-06-24 01:18:59.14 spid6s      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2019-06-24 01:18:59.14 spid6s      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2019-06-24 01:18:59.14 spid6s      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2019-06-24 01:18:59.14 spid6s      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2019-06-24 01:18:59.15 spid6s      Creating SSIS folders...
2019-06-24 01:18:59.15 spid6s      Dropping procedure [dbo].[sp_syscollector_get_instmdw]
2019-06-24 01:18:59.16 spid6s      Creating procedure [dbo].[sp_syscollector_get_instmdw]...
2019-06-24 01:18:59.16 spid6s      Dropping procedure [dbo].[sp_syscollector_upload_instmdw]
2019-06-24 01:18:59.16 spid6s      Creating procedure [dbo].[sp_syscollector_upload_instmdw]...
2019-06-24 01:18:59.16 spid6s      Uploading data collector package from disk: c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx
2019-06-24 01:18:59.16 spid6s      Uploading data collector package from disk: c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx
2019-06-24 01:18:59.17 spid6s      Error: 2775, Severity: 17, State: 12.
2019-06-24 01:18:59.17 spid6s      The code page 65001 is not supported by the server.
2019-06-24 01:18:59.17 spid6s      The failed batch of t-sql statements :

--
-- Data Collector placeholder comment, dont move or remove.
-- 875f7de1-9e83-4be1-8b96-2df4a8533e88
--

-- Load SSIS packages needed by collector types

-- Temporarily enable the 'Agent XPs' config option so that sp_ssis_putpackage can 
-- succeed when SQLAgent is stopped. 

EXECUTE #syscollector_upload_package 
        @packagename='SqlTraceCollect'
    ,    @packageid='0E149FC9-1046-4DE6-98BF-4B22ED6F6C42'
    ,    @versionid='244F0904-5CC6-49B8-AE90-905AEEA8BAF3';

EXECUTE #syscollector_upload_package 
        @packagename='SqlTraceUpload'
    ,    @packageid='F389A8E6-5A17-4056-ABFD-C8B823F2092E'
    ,    @versionid='2D32AB4C-9929-4A85-A94E-7A01D8F40016';

EXECUTE #syscollector_upload_package 
        @packagename='TSQLQueryCollect'
    ,    @packageid='292B1476-0F46-4490-A9B7-6DB724DE3C0B'
    ,    @versionid='E24C6D00-94C6-457B-BED4-1F9F018F3273';

EXECUTE #syscollector_upload_package 
        @packagename='TSQLQueryUpload'
    ,    @packageid='6EB73801-39CF-489C-B682-497350C939F0'
    ,    @versionid='DA1210BC-C31B-43C6-B255-D8DDEB288CA1';

EXECUTE #syscollector_upload_package 
        @packagename='PerfCountersCollect'
    ,    @packageid='C2EAABC1-5BF3-4127-BEB3-26E94D026E7D'
    ,    @versionid='09A5B959-21B3-44E1-A37F-4A62BE5D6244';

EXECUTE #syscollector_upload_package 
        @packagename='PerfCountersUpload'
    ,    @packageid='08D854CB-0D45-4E96-92C6-227A5DCD7066'
    ,    @versionid='22A676DD-2025-493A-AD6B-C0186ABD556F';

EXECUTE #syscollector_upload_package 
        @packagename='QueryActivityCollect'
    ,    @packageid='0B68FC9D-23DC-48F3-A937-90A0A8943D0E'
    ,    @versionid='75A3A143-2059-433B-A11C-C8E0C80A83CF';

EXECUTE #syscollector_upload_package 
        @packagename='QueryActivityUpload'
    ,    @packageid='833DB628-8E19-47A3-92C5-FB1779B52E76'
    ,    @versionid='B1D79132-C6E6-46AA-8B14-E0AE4C4BA7BB';

2019-06-24 01:18:59.17 spid6s      Error: 912, Severity: 21, State: 2.
2019-06-24 01:18:59.17 spid6s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2019-06-24 01:18:59.17 spid6s      Error: 3417, Severity: 21, State: 3.
2019-06-24 01:18:59.17 spid6s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2019-06-24 01:18:59.17 spid6s      SQL Server shutdown has been initiated
2019-06-24 01:18:59.17 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

After some research, I found some threads suggesting turning off the experimental utf-8 support fix the problem (e.g. https://social.msdn.microsoft.com/Forums/en-US/6282dc39-0e19-476a-8fbc-e1ce26cb94e5/cannot-start-sql-server-express-2017?forum=sqlexpress ). To achive that, I inserted the following step in my dockerfile:

RUN Set-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\Nls\CodePage' -Name ACP -Value 1252; `
    Set-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\Nls\CodePage' -Name MACCP -Value 10000; `
    Set-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\Nls\CodePage' -Name OEMCP -Value 437; `
    Set-ItemProperty -Path 'HKLM:\Software\Microsoft\Command Processor' -Name Autorun -Value '@chcp 437>nul';

But that doesn't help, sql server doesn't start at all and still crashes with the same error message. Looks like the charset can't be changed inside a docker container. Trying that in a powershell session in docker returns the following error message:

PS C:\> chcp 437
chcp 437
Invalid code page
PS C:\>

For further investigations, I include the whole log folder as zip to this issue: Logs.zip. As I don't know what I could do anymore, I think opening a new issue is the only option for me as I want to use SQL Server with the latest CU even in development.

Thank you very much for any help!

banerjeeamit commented 5 years ago

We don't support UTF-8 for SQL Server 2017. This is something that we are working on for SQL Server 2019. So if you have a system that has the Windows Beta feature for UTF-8, we recommend that you don't turn it on if you are running SQL Server 2017 and below.

NKnusperer commented 5 years ago

@dreschner I had the same problem and as it turned out the root issue was that when using Docker Desktop the default container isolation mode is hyperv instead of process (which is the default on Windows Server). In Hyper-V the system code-page is 65001 (UTF-8) which prevents cumulative updates to be applied as documented here: https://feedback.azure.com/forums/908035-sql-server/suggestions/35221357-installing-service-pack-cumulative-update-on-sql

The solution is to simply build the image using process isolation like this: docker build --memory 4g --isolation process . This will now use your local code-page instead of UTF-8. After the image was build it can be used with Hyper-V isolation. Tested on Win 10 1903 using Docker 19.03.4