If I create a database and then try to connect to it with MS Sync Framework within 2 seconds it throw Exception "Cannot open database ..."
Adding
Thread.Sleep(4000);
Will cause code to work properly, however I don't want a hard time set as this code will be running on tablets and other weak hardware, so the time to generate the database could differ drastically.
I can check if database exists after creation and it always does right away, so conditional waiting(spinning) isn't an option.
using (SqlConnection masterConnection = new SqlConnection(masterConnectionString)) {
using (SqlCommand sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
masterConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
bool databaseExist = false;
while (!databaseExist) {
using (SqlConnection masterConnection = new SqlConnection(masterConnectionString)) {
using (SqlCommand verifySqlCommand = new SqlCommand(string.Format("SELECT database_id FROM sys.databases WHERE name = '{0}'", databaseName), masterConnection)) {
masterConnection.Open();
databaseExist = (int)verifySqlCommand.ExecuteScalar() > 0; // Always true
}
}
Thread.Sleep(1000);
}
...
using (SqlConnection sqlConnection = new SqlConnection(CONNECTION_STRING)) {
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
Issue seems to be with the SqlConnection, the first SqlConnection can see the database exists, but the second(new one) can't see it exists for those first few seconds (fails on SqlConnection.Open()).
Stack Trace
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Synchronization.Data.SyncUtil.TryOpenConnection(IDbConnection connection)
at Microsoft.Synchronization.Data.SyncUtil.OpenConnection(IDbConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlEditionHelper.GetEdition(SqlConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.set_Connection(SqlConnection value)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning..ctor(SqlConnection connection, DbSyncScopeDescription scopeDescription, SqlSyncScopeProvisioningType provisioningType, Boolean expectConnection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning..ctor(SqlConnection connection)
Mark II based on @CharlieBrown's answer
The code @CharlieBrown posted does work by itself, but not in the setting I have, the following code demonstrates the crash. If you uncomment the manual throw then the code will work properly, otherwise not.
try {
//If I manually throw this exception then code in catch will run fine, otherwise not.
//throw new Exception("Cannot open database testDB");
using (var sqlConnection = new SqlConnection(CONNECTION_STRING)) {
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
} catch (Exception exception) {
if (exception.Message.StartsWith("Cannot open database")) { // Database does not exist, try to create
try {
using (var masterConnection = new SqlConnection(masterConnectionString)) {
using (var sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
masterConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
using (var sqlConnection = new SqlConnection(CONNECTION_STRING)) {
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
} catch (Exception ex) { // Always end up here
// ex = System.Data.SqlClient.SqlException (0x80131904): Cannot open database "testDB" requested by the login. The login failed. Login failed for user ...
}
} else { // Other exception
// Never hit
}
}
Edits
Updated code per using suggestions, added stack trace. Added another code example to demonstrate issue.
Solution
After talking to @CharlieBrown there doesn't seem to be a solution to this issue, so I will work around it by always running the SqlCommand to create database if not exist, rather than trying to use database and then creating in catch() if exception. This code is only run on app start.
Your call toSqlSyncScopeProvisioning
is being called before SQL Server has a moment to get the database ready for Synchronization. I can't find a reference for the amount of time offhand, but after the database is created, several triggers occur to ready the database.Working code: Tested against LocalDb, Sql Server 2008r2, Sql Server 2012