search

avivasolutionsnl / sitecore-docker

Dockerized Sitecore 9 XP0 & XC
33 stars 24 forks source link

Mssql container crash at startup #120

Closed Barsonax closed 5 years ago

Barsonax commented 5 years ago

Could possibly be a race condition in the container?

mssql_1     | ### Existing Sitecore databases found in 'c:/data/'...
mssql_1     | ### Attaching 'sitecorecommerce9_global'...
mssql_1     | The following error occurred while attaching sitecorecommerce9_global : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
mssql_1     | ### Repairing 'sitecorecommerce9_global'...
mssql_1     | Invoke-Sqlcmd : A network-related or instance-specific error occurred while
mssql_1     | establishing a connection to SQL Server. The server was not found or was not
mssql_1     | accessible. Verify that the instance name is correct and that SQL Server is
mssql_1     | configured to allow remote connections. (provider: Named Pipes Provider,
mssql_1     | error: 40 - Could not open a connection to SQL Server)
mssql_1     | At C:\Boot.ps1:45 char:9
mssql_1     | +         Invoke-Sqlcmd -Query $repairCmd
mssql_1     | +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mssql_1     |     + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlExcept
mssql_1     |    ion
mssql_1     |     + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.
mssql_1     |    PowerShell.GetScriptCommand
mssql_1     |

However there seems to be more flavors of this boot error:

mssql_1     | ### Existing Sitecore databases found in 'c:/data/'...
mssql_1     | ### Attaching 'SitecoreCommerce9_Global'...
mssql_1     | The following error occurred while attaching SitecoreCommerce9_Global : Error while closing database 'SitecoreCommerce9_Global'. Check for previous additional errors and retry the operation.
mssql_1     | ### Repairing 'SitecoreCommerce9_Global'...
mssql_1     | Invoke-Sqlcmd : Repair statement not processed. Database needs to be in single
mssql_1     | user mode.
mssql_1     | At C:\Boot.ps1:45 char:9
mssql_1     | +         Invoke-Sqlcmd -Query $repairCmd
mssql_1     | +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mssql_1     |     + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
mssql_1     |    hellSqlExecutionException
mssql_1     |     + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
mssql_1     |    ll.GetScriptCommand
mssql_1     |

Another error that can occur even if the sql server is already started:

mssql_1     | ### Existing Sitecore databases found in 'c:/data/'...
mssql_1     | ### Attaching 'sitecorecommerce9_global'...
mssql_1     | The following error occurred while attaching sitecorecommerce9_global : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
mssql_1     | ### Repairing 'sitecorecommerce9_global'...
mssql_1     | Invoke-Sqlcmd : User does not have permission to alter database
mssql_1     | 'sitecorecommerce9_global', the database does not exist, or the database is
mssql_1     | not in a state that allows access checks.
mssql_1     | ALTER DATABASE statement failed.
mssql_1     | Could not find database 'sitecorecommerce9_global'. The database either does
mssql_1     | not exist, or was dropped before a statement tried to use it. Verify if the
mssql_1     | database exists by querying the sys.databases catalog view.
mssql_1     | At C:\Boot.ps1:47 char:9
mssql_1     | +         Invoke-Sqlcmd -Query $repairCmd
mssql_1     | +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mssql_1     |     + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
mssql_1     |    hellSqlExecutionException
mssql_1     |     + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
mssql_1     |    ll.GetScriptCommand

Another one in the repair query:

mssql_1     | ### Reattaching 'sitecore_xdb.collection.shard1'...
mssql_1     | ### Setting single user for [sitecore_xdb.collection.shardmapmanager]...
mssql_1     | ### Reattaching 'sitecore_xdb.collection.shardmapmanager'...
mssql_1     | The following error occurred while attaching sitecore_xdb.collection.shardmapmanager : Cannot detach the database 'sitecore_xdb.collection.shardmapmanager' because it is currently in use.
mssql_1     | ### Repairing 'sitecore_xdb.collection.shardmapmanager'...
mssql_1     | Invoke-Sqlcmd : Incorrect syntax near '.'.
mssql_1     | Incorrect syntax near '.'.
mssql_1     | Incorrect syntax near the keyword 'with'. If this statement is a common table
mssql_1     | expression, an xmlnamespaces clause or a change tracking context clause, the
mssql_1     | previous statement must be terminated with a semicolon.
mssql_1     | At C:\Boot.ps1:52 char:9
mssql_1     | +         Invoke-Sqlcmd -Query $repairCmd -Querytimeout 65535 -Connecti ...
mssql_1     | + 
mssql_1     |     + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Goal is to fix all possible startup errors to make the mssql container more robust.

Barsonax commented 5 years ago

Copied from @joostmeijles post (might fix the second error?):

Probably need to add to xp/mssql/Boot.ps1, something like:

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

See https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode?view=sql-server-2017

Barsonax commented 5 years ago

First error could be fixed by first checking if the mssql service is running

(GET-SERVICE MSSQLSERVER).Status -eq 'Running'

If its not then sleep for a bit and check again before running the queries.

Alternatively we could start the service ourselves instead of relying on the automatic service.

joostmeijles commented 5 years ago 
# Make sure SQL server is running
Start-Service MSSQLSERVER;
(Get-Service MSSQLSERVER).WaitForStatus('Running');
Barsonax commented 5 years ago

121 will fix error 1 and 3

Barsonax commented 5 years ago

121 should now also fix error 2

Barsonax commented 5 years ago

121 will fix error 4 as well