Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@dennisdoomen
Last active March 3, 2024 07:40
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save dennisdoomen/9a97e07a4c4a8f2eef3af5ac293d6759 to your computer and use it in GitHub Desktop.
Save dennisdoomen/9a97e07a4c4a8f2eef3af5ac293d6759 to your computer and use it in GitHub Desktop.
internal class DockerMsSqlServerDatabase : IAsyncDisposable
{
private const string Password = "!Passw0rd";
private const string Image = "mcr.microsoft.com/mssql/server";
private const string Tag = "2019-GA-ubuntu-16.04";
private static IContainer _sqlServerContainer;
private SemaphoreSlim semaphore = new(1, 1);
private readonly string DatabaseName;
private bool _deleted;
private static readonly int ContainerPort = 1433;
public static async Task<DockerMsSqlServerDatabase> Create(CancellationToken cancellationToken = default)
{
string dbName = "ovs-" + Guid.NewGuid();
var db = new DockerMsSqlServerDatabase(dbName);
await db.CreateAndStartContainer();
await db.CreateDatabase(cancellationToken);
return db;
}
private DockerMsSqlServerDatabase(string databaseName = null)
{
DatabaseName = databaseName;
}
private async Task CreateAndStartContainer()
{
if (_sqlServerContainer == null)
{
try
{
await semaphore.WaitAsync();
if (_sqlServerContainer == null)
{
_sqlServerContainer = new ContainerBuilder()
.WithImage($"{Image}:{Tag}")
.WithPortBinding(ContainerPort, assignRandomHostPort: true)
.WithEnvironment("ACCEPT_EULA", "Y")
.WithEnvironment("SA_PASSWORD", Password)
.WithCleanUp(cleanUp: true)
.WithWaitStrategy(Wait.ForUnixContainer()
.UntilOperationIsSucceeded(
() => HealthCheck(CancellationToken.None).GetAwaiter().GetResult(),
10))
.Build();
_sqlServerContainer.Stopping += OnStopping;
await _sqlServerContainer.StartAsync();
}
}
catch (DockerImageNotFoundException)
{
throw new InvalidOperationException(
"SQL Server docker image not found. Did you run \"build.ps1 BuildSqlServerWithFtsImage\"");
}
finally
{
semaphore.Release();
}
}
}
private void OnStopping(object sender, EventArgs e)
{
try
{
semaphore.Wait();
if (_sqlServerContainer != null)
{
_sqlServerContainer.Stopping -= OnStopping;
_sqlServerContainer = null;
}
}
finally
{
semaphore.Release();
}
}
private static async Task<bool> HealthCheck(CancellationToken cancellationToken)
{
try
{
SqlConnection.ClearAllPools();
await using var connection = CreateConnection();
await connection.OpenAsync(cancellationToken);
return true;
}
catch (Exception)
{
await Task.Delay(1.Seconds(), cancellationToken);
}
return false;
}
public string ConnectionString =>
$"server=localhost,{PublicPort};database={DatabaseName};User Id=sa;Password={Password};Encrypt=false";
private async Task CreateDatabase(CancellationToken cancellationToken = default)
{
SqlConnection.ClearAllPools();
await using var connection = CreateConnection();
await connection.OpenAsync(cancellationToken);
var createCommand = $@"
CREATE DATABASE [{DatabaseName}]
ALTER DATABASE [{DatabaseName}] SET SINGLE_USER
ALTER DATABASE [{DatabaseName}] SET COMPATIBILITY_LEVEL=110
ALTER DATABASE [{DatabaseName}] SET MULTI_USER";
await using var command = new SqlCommand(createCommand, connection);
// HACK: should mitigate (slightly) the bug in MSSQL that prevents us from creating
// new databases.
// See https://github.com/Microsoft/mssql-docker/issues/344 for tracking issue.
var CreatePolicy = Policy
.Handle<SqlException>(e => e.Number == 5177)
.WaitAndRetryAsync(new[]
{
TimeSpan.FromSeconds(1),
TimeSpan.FromSeconds(4),
TimeSpan.FromSeconds(6)
});
await CreatePolicy.ExecuteAsync(async () => { await command.ExecuteNonQueryAsync(cancellationToken); });
}
public ValueTask DisposeAsync()
{
if (_deleted)
{
return new ValueTask();
}
DeleteDatabase();
return new ValueTask();
}
private void DeleteDatabase()
{
using (var connection = CreateConnection())
{
connection.Open();
using (var command =
new SqlCommand($"ALTER DATABASE [{DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
connection))
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand($"DROP DATABASE [{DatabaseName}]", connection))
{
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
}
}
_deleted = true;
}
private static SqlConnection CreateConnection()
{
var masterConnectionString =
$"server=localhost,{PublicPort};User Id=sa;Password={Password};Initial Catalog=master;Encrypt=false";
var connectionStringBuilder = new SqlConnectionStringBuilder(masterConnectionString);
return new SqlConnection(connectionStringBuilder.ConnectionString);
}
private static int PublicPort => _sqlServerContainer.GetMappedPublicPort(ContainerPort);
}
@jeboal
Copy link

jeboal commented Feb 6, 2024

Hi - am not sure what the "Policy" refers to here... not seeing a way to resolve this that has the methods referenced:

ln 125: var CreatePolicy = Policy
.Handle(e => e.Number == 5177)

Can you tell me what to reference for this please?

Thanks!

@dennisdoomen
Copy link
Author

@markdehaas
Copy link

First of all: Thanks, this works great.

However, I ran into an issue testing a query that uses .Contains() in EF 8: "Incorrect syntax near '$'". After some doublechecking and searching I came across this answer https://stackoverflow.com/a/77374827 about a breaking change in EF 8 for older version of SQL Server. Bumping the COMPATIBILITY_LEVEL on line 117 up to 150 seems to fix the issue.

Just thought I'd mention it here in case anybody else runs into the same issue. 😇

@dennisdoomen
Copy link
Author

Cool. Thank you for reporting that.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment