/// <summary>
/// Creates a new database on the server
/// </summary>
/// <param name="DatabaseName">The name of the database</param>
/// <returns>The newly created database</returns>
public Database CreateDatabase(string DatabaseName)
{
Microsoft.SqlServer.Management.Smo.Database db;
db = new Microsoft.SqlServer.Management.Smo.Database(_server, DatabaseName);
db.Create();
return new Database(db);
}
/// <summary>
/// Enable File Streaming on the MediaDbContext
/// </summary>
public static void Enable( )
{
Log.Info("Enabling Sql File Streaming");
// TODO: Configurable?
const string fileGroupName = "fg";
const string dataFileName = "file";
// Get the Connection String to the Media DbContext
var mediaDbConnectionString = new SqlConnectionStringBuilder(ContextConnectionStringManager.Get(ContextNames.Media));
// Get the server name from the connection string.
var mediaDbDatasource = mediaDbConnectionString.DataSource;
// Get the catalog name of the media database.
var mediaDbDatabaseName = mediaDbConnectionString.InitialCatalog;
// Connect with SMO.
var server = new Microsoft.SqlServer.Management.Smo.Server(mediaDbDatasource);
server.ConnectionContext.AutoDisconnectMode = Microsoft.SqlServer.Management.Common.AutoDisconnectMode.NoAutoDisconnect;
// Determine the Filestream directory.
var serverName = server.ComputerNamePhysicalNetBIOS;
const string filestreamPathLocal = @"C:\TriTech\InformRMS\Data\FileStream"; // TODO: Derive from input options.
var isLocalDeploy = IsHostLocal(serverName);
var filestreamPathUnc = isLocalDeploy ? "" : String.Format(@"\\{0}\{1}", serverName, filestreamPathLocal.Replace(':', '$'));
// Create the Filestream directory, if necessary.
var filestreamDirectory = isLocalDeploy
? Path.GetDirectoryName(filestreamPathLocal)
: Path.GetDirectoryName(filestreamPathUnc);
if (!Directory.Exists(filestreamDirectory))
{
Directory.CreateDirectory(filestreamDirectory);
}
// Update the Filestream access level, if necessary. This is required before Filestream is enabled on a database.
const int target = (int)Microsoft.SqlServer.Management.Smo.FilestreamAccessLevelType.FullAccess;
if (server.Configuration.FilestreamAccessLevel.ConfigValue != target)
{
Log.Info("Changing Filestream access level to: {0}", target);
server.Configuration.FilestreamAccessLevel.ConfigValue = target;
server.Configuration.Alter();
}
// Manually create the database from the context.
// We need to do this because Filestream has to be enabled on the database before we can create Filestream columns.
var database = server.Databases
.Cast <Microsoft.SqlServer.Management.Smo.Database>()
.FirstOrDefault(x => x.Name.Equals(mediaDbDatabaseName, StringComparison.OrdinalIgnoreCase));
if (database == null)
{
Log.Info("Creating database {0}.", mediaDbDatabaseName);
database = new Microsoft.SqlServer.Management.Smo.Database(server, mediaDbDatabaseName);
database.Create();
}
// Create a FileGroup for Filestream data, if necessary.
if (String.IsNullOrWhiteSpace(database.FilestreamDirectoryName))
{
var fg = new Microsoft.SqlServer.Management.Smo.FileGroup(database, fileGroupName, true);
fg.Create();
var file = new Microsoft.SqlServer.Management.Smo.DataFile(fg, dataFileName, filestreamPathLocal);
file.Create();
}
// Disconnect.
server.ConnectionContext.Disconnect();
// Attempt to determine if FileStreaming is already setup
var result = SqlScripting.ExecuteNonQuery(mediaDbConnectionString.ToString(), @"
SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName
FROM sys.columns
WHERE is_filestream = 1 AND OBJECT_NAME(object_id) = 'MEDIASTORAGES' AND name = 'MEDIA'");
// The Drop Column will fail if the table is already setup with File Streaming.
// Make sure we only execute the script if the FileStream is not already setup.
if (result < 1)
{
// Enable Filestream on the MediaStorages table.
SqlScripting.ExecuteScript(mediaDbConnectionString.ToString(), @"
-- Filestream-enabled tables must have a RowGuidCol column. This cannot be added through fluent mapping.
alter table [dbo].[MediaStorages] drop column [MediaGuid]
alter table [dbo].[MediaStorages] add [MediaGuid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID()
-- Manually create the filestream column. NOTE: SQL will not allow the FILESTREAM attribute to be added to an existing column, and the column must be created as part of the model (so it can be accessed via code), so drop and recreate it here.
alter table [dbo].[MediaStorages] drop column [Media]
alter table [dbo].[MediaStorages] add [Media] varbinary(max) FILESTREAM null");
}
}
