Timeout when trying to dockerize SqlServer+restored database

Docker Desktop
1

Hi, I’m starting with Docker and trying to dockerize a Windows SqlServer and a database restored from a backup. My host is Windows 10 Pro 1709 (OS Build 16299.64), and I’m following the advice found at https://anchorloop.com/2017/05/06/building-a-windows-docker-container-for-a-sql-server-database/.

My dockerfile is very simple:

# escape=`
 
FROM microsoft/mssql-server-windows-express

ENV user_name _
ENV user_password _

WORKDIR C:\
 
ADD .\py-liz.bak C:\
ADD .\Invoke-BuildActions.ps1 C:\
ADD .\Invoke-RunActions.ps1 C:\
 
SHELL ["powershell", "-File"]

RUN .\Invoke-BuildActions.ps1

EXPOSE 1433

CMD .\Invoke-RunActions.ps1 -Username $env:user_name -Password $env:user_password -Verbose

The scripts listed in the docker file are reported below, but essentially they follow the model found at the page cited above. Everything seems to work fine, until I try to restore the database, which is about 3.5 Gb. At that point, I keep getting a timeout from SqlCmd. I tried removing the timeout altogether (see the script below), but this does not change the result; I have also supposed that the SqlServer might not yet be available when launching SqlCmd, but this should not be the case. Could anyone suggest available documentation or possible solutions about this? Here is the error:

Step 9/11 : RUN .\Invoke-BuildActions.ps1
 ---> Running in 93f77cc0197d

Name                           Version          Source           Summary
----                           -------          ------           -------
nuget                          2.8.5.208        https://onege... NuGet provi...

PSPath            : Microsoft.PowerShell.Core\FileSystem::C:\SqlData
PSParentPath      : Microsoft.PowerShell.Core\FileSystem::C:\
PSChildName       : SqlData
PSDrive           : C
PSProvider        : Microsoft.PowerShell.Core\FileSystem
PSIsContainer     : True
Name              : SqlData
FullName          : C:\SqlData
Parent            :
Exists            : True
Root              : C:\
Extension         :
CreationTime      : 12/6/2017 9:19:00 AM
CreationTimeUtc   : 12/6/2017 8:19:00 AM
LastAccessTime    : 12/6/2017 9:19:00 AM
LastAccessTimeUtc : 12/6/2017 8:19:00 AM
LastWriteTime     : 12/6/2017 9:19:00 AM
LastWriteTimeUtc  : 12/6/2017 8:19:00 AM
Attributes        : Directory
Mode              : d-----
BaseName          : SqlData
Target            : {}
LinkType          :

Invoke-Sqlcmd : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
At C:\Invoke-BuildActions.ps1:18 char:1
+ Invoke-Sqlcmd -ServerInstance localhost -Query `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
   hellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
   ll.GetScriptCommand

Here are the scripts: Invoke-BuildActions.ps1:

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
Install-Module SqlServer -Force -AllowClobber
 
Import-Module SqlServer
 
New-Item -Type Directory -Path C:\SqlData
 
Invoke-Sqlcmd -ServerInstance localhost -Query `
"EXEC sp_configure 'remote query timeout', 0; `
GO `
RECONFIGURE ;  `
GO"

Invoke-Sqlcmd -ServerInstance localhost -Query `
"USE [master] RESTORE DATABASE [py-liz] `
FROM DISK = N'C:\py-liz.bak' `
WITH
MOVE 'py-liz' TO 'C:\SqlData\py-liz.mdf',   
MOVE 'py-liz_log' TO 'C:\SqlData\py-liz_log.ldf';"

and Invoke-RunActions.ps1:

Param (
    [Parameter(Mandatory=$true)][string] $Username,
    [Parameter(Mandatory=$true)][string] $Password
)
 
# If $Username or $Password is a variable, resolve them.
if($Username -like "$*") {
    $Username = Invoke-Expression -Command $Username
}
if($Password -like "$*") {
    $Password = Invoke-Expression -Command $Password
}
 
# Validate $Username and $Password are not defaults
if($Username -eq "_") {
    Write-Host "ERROR: A name for the new user account is required. Please supply a '--env user_name' variable with the 'docker run' command."
    Exit(1)
}
if($Password -eq "_") {
    Write-Host "ERROR: A password for the new user account is required. Please supply a '--env user_password' variable with the 'docker run' command."
    Exit(1)
}
 
# Create login for user.
$SecurePassword = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username, $SecurePassword
Add-SqlLogin -ServerInstance localhost -LoginName $Username -LoginType SqlLogin -DefaultDatabase WideWorldImporters -Enable -GrantConnectSql -LoginPSCredential $Credential | Out-Null
Write-Verbose "Created SQL login for user $Username."
 
# Create user for database
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList localhost
$Database = $Server.Databases['py-liz']
 
$User = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $Database, $Username
$User.Login = $Username
$User.Create()
Write-Verbose "Created user $Username."
 
# Assign user to roles with read and write privileges.
Invoke-Sqlcmd -ServerInstance localhost -Database py-liz -Query "ALTER ROLE db_datareader ADD MEMBER $Username"
Invoke-Sqlcmd -ServerInstance localhost -Database py-liz -Query "ALTER ROLE db_datawriter ADD MEMBER $Username"
Write-Verbose "Granted user $Username read and write access to Database: py-liz."
 
# This is the CMD script from the parent Docker image (microsoft/mssql-server-windows-express). By running it we inherit its functionality.
.\start.ps1 -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs "$env:attach_dbs" -Verbose
2

Hi,

I am the author of the blog post you have linked to above - I’m glad you have found most of the code useful even though you have encountered this timeout error with your own database.

I don’t know the underlying cause of the timeout, but I can see that you are disabling the “remote query timeout” on the server. The next thing I would try is look at the usage of the Invoke-SqlCmd cmdlet, so the client side of the problem. A quick Google around suggests that Invoke-SqlCmd may have a default timeout of 600 seconds (10 minutes). It might or it might not, I don’t know without trying, but this is what I would test for next. Consider outputting some timestamps before and after the RESTORE operation to see just how long Invoke-Sqlcmd/the server is giving you, or generating a PowerShell transcript or generating PSDebug trace output.

I hope that was helpful and that you come to a solution.

Best regards,

Kirk MacPhee

3

Can you try adding the -QueryTimeout parameter to the Invoke-Sqlcmd command itself ? I wonder if the disabling timeout in your script is working or not. Also can you specify the Database name with the -Database parameter ? (to ensure you are indeed connecting to the database you just created) ?

Thanks