private static void CreateLoginIfNotExists(Server server, Database database)
{
Login login = null;
if (!server.LoginExists(Configuration.LoginName))
{
Console.WriteLine("Creating Login {0}", Configuration.LoginName);
login = server.CreateSqlLogin(Configuration.LoginName, Configuration.LoginPassword, Configuration.DatabaseName);
}
else
{
login = server.Logins[Configuration.LoginName];
}
if (!database.UserExists(Configuration.LoginName))
{
Console.WriteLine("Creating User {0} for Database {1}", login.Name, database.Name);
var user = new User(database, Configuration.LoginName);
user.Login = login.Name;
user.Create();
user.AddToRole("db_owner");
}
else
{
ConsoleHelper.WriteLine(ConsoleColor.Yellow, string.Format("User {0} already exists in database {1}, creation skiped", Configuration.LoginName, Configuration.DatabaseName));
}
}
public static void UpdateDataSet(this MSMO.User user, Data.ApplicationDataSet.DBUsersRow dataRow)
{
try
{
//dataRow.X = user.X;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
public static void UpdateDataSet(this MSMO.User user, Data.ApplicationDataSet.DBUsersRow userRow)
{
try
{
//instanceRow.ServiceName = server.ServiceName;
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
private static void Update(MSMO.User user, SQLInformation.Data.ApplicationDataSet.DBUsersRow dataRow)
{
try
{
user.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
private static SQLInformation.Data.ApplicationDataSet.DBUsersRow Add(MSMO.User user, Guid databaseID, string databaseName)
{
SQLInformation.Data.ApplicationDataSet.DBUsersRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.DBUsers.NewDBUsersRow();
dataRow.ID = Guid.NewGuid();
dataRow.Database_ID = databaseID;
dataRow.Name_Database = databaseName;
dataRow.IsSystemObject = user.IsSystemObject;
dataRow.Name_User = user.Name;
dataRow.CreateDate = user.CreateDate;
dataRow.DateLastModified = user.DateLastModified;
dataRow.Login = user.Login;
dataRow.LoginType = user.LoginType.ToString();
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.DBUsers.AddDBUsersRow(dataRow);
Common.ApplicationDataSet.DBUsersTA.Update(Common.ApplicationDataSet.DBUsers);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
public User(Smo.User user, Server server)
{
_user = user;
_server = server;
}
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose)
{
// Store extracted scripts. Each extract may include multiple scripts.
StringCollection OutputScripts = new StringCollection();
string FinalScript = String.Empty;
switch (oname.ObjectType)
{
case SQLObjectType.Table:
Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema];
if (scriptTable != null)
{
StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked
String TableScript = String.Empty; // Stores individual script for output collection.
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.Statistics = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.WithDependencies = false;
// Get table and related scripts
CheckScripts = scriptTable.Script(scriptOptions);
// Check scripts so we can remove invalide SQL 2012 column store options from the script.
// (Why doesn't the target server version remove this?
// This is a crappy place to do this, and it's version specific.
// Need to implement the new versioning code to check target model.
foreach (string CheckCCI in CheckScripts)
{
if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE"))
{
TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", "");
}
else
{
TableScript = CheckCCI;
}
// Add the script into the OutputScripts collection.
OutputScripts.Add(TableScript);
}
}
break;
case SQLObjectType.View:
Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema];
if (scriptView != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.WithDependencies = false;
// Must specify tables seperatly, but safer to do so
// to avoid having duplicate table names in the model.
OutputScripts = scriptView.Script(scriptOptions);
}
break;
case SQLObjectType.StoredProcedure:
Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema];
if (scriptStoredProcedure != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptStoredProcedure.Script(scriptOptions);
}
break;
case SQLObjectType.PartitionScheme:
{
Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name];
if (scriptPScheme != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPScheme.Script(scriptOptions);
}
}
break;
case SQLObjectType.PartitionFunction:
{
Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name];
if (scriptPFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPFunction.Script(scriptOptions);
}
}
break;
case SQLObjectType.Schema:
{
Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name];
if (scriptSchema != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
scriptOptions.ScriptOwner = true; // This includes the "with authorize" part.
OutputScripts = scriptSchema.Script(scriptOptions);
}
}
break;
case SQLObjectType.FileGroup:
{
Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name];
if (scriptFG != null)
{
// Create manual script for FileGroups
OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name);
}
}
break;
case SQLObjectType.User:
{
Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name];
if (scriptUser != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptUser.Script(scriptOptions);
}
}
break;
case SQLObjectType.Function:
Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema];
if (userDefinedFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = userDefinedFunction.Script(scriptOptions);
}
break;
}
if (OutputScripts.Count > 0)
{
Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
foreach (string script in OutputScripts)
{
// Add the script to the script collection.
FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine;
}
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
FinalScript = String.Empty;
}
if (FinalScript != String.Empty)
{
SQLScriptsCollection.Scripts.Add(FinalScript);
}
else
{
SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
// Print script(s) if verbose is on.
if (Verbose)
{
Console.WriteLine(FinalScript);
}
}
protected override void DoWork(object state)
{
var mainCancellationTokenSource = new CancellationToken();
try
{
var tasks = new List <Task>();
var deploymentJobs = _deploymentJobService.Get(p => p.Server.Code == ServerId &&
p.JobType == (int)JobType.Database &&
(p.IsCopySourceDone ?? false) &&
(p.IsStart ?? false) &&
!(p.IsDone ?? false))
.OrderBy(p => p.FailCount)
.Take(2).ToList();
var listJobUpdated = new List <DeploymentJob>();
foreach (var job in deploymentJobs)
{
var localJob = job;
var t = Task.Factory.StartNew(() =>
{
var dbName = localJob.Configuration.DatabaseName;
var loginName = localJob.Configuration.DatabaseUsername;
var loginPassword = EncryptHelper.Decrypt(localJob.Configuration.DatabasePassword);
var pass = PasswordHelper.HashString("123456", "camino");
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString);
var file = new FileInfo(localJob.Configuration.SqlScriptPath);
var script = file.OpenText().ReadToEnd();
script = script.Replace("{{DATABASE_NAME}}", dbName)
.Replace("{{FRANCHISEE_ADMIN_NAME}}", "camino")
.Replace("{{FRANCHISEE_ADMIN_PASSWORD}}", pass)
.Replace("{{FRANCHISEE_ADMIN_EMAIL}}", "*****@*****.**");
var server = new Server(new ServerConnection(conn));
try
{
server.ConnectionContext.ExecuteNonQuery(script);
using (var scope = new TransactionScope())
{
var db = server.Databases[dbName];
var login = server.Logins[loginName];
if (login == null)
{
// Creating Logins
login = new Login(server, loginName)
{
LoginType = LoginType.SqlLogin, PasswordPolicyEnforced = false
};
login.Create(loginPassword);
login.DefaultDatabase = dbName;
login.Alter();
}
// Creating Users in the database for the logins created
if (db.Users[loginName] != null)
{
return;
}
var dbUser = new User(db, loginName)
{
UserType = UserType.SqlLogin,
Login = login.Name,
};
dbUser.Create();
dbUser.AddToRole("db_owner");
localJob.IsDone = true;
scope.Complete();
}
}
catch (Exception exception)
{
server = new Server(new ServerConnection(conn));
server.KillAllProcesses(dbName);
server.KillDatabase(dbName);
var db = server.Databases[dbName];
db.Drop();
localJob.IsDone = false;
localJob.FailCount = (localJob.FailCount ?? 0) + 1;
_diagnosticService.Error(exception);
}
listJobUpdated.Add(localJob);
}, mainCancellationTokenSource);
tasks.Add(t);
}
Task.WaitAll(tasks.ToArray(), mainCancellationTokenSource);
_deploymentJobService.UpdateListJobs(listJobUpdated);
}
catch (Exception ex)
{
_diagnosticService.Error(ex);
}
}
private static SQLInformation.Data.ApplicationDataSet.DBUsersRow GetInfoFromSMO(MSMO.User user, Guid databaseID, string databaseName)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.DBUsersRow dataRow = null;
try
{
var dbs = from tb in Common.ApplicationDataSet.DBUsers
where tb.Database_ID == databaseID
select tb;
var dbs2 = from db2 in dbs
where db2.Name_User == user.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(user, dataRow);
}
else
{
dataRow = Add(user, databaseID, databaseName);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}
