static void Main(string[] args)
{
var parameters = new SqlManagementControllerParameters
{
PublishSettingsFilePath = @"C:\Your.publishsettings", // UPDATE
ServerRegion = "West US",
ServerAdminUsername = "******", // UPDATE
ServerAdminPassword = "******", // UPDATE
FirewallRuleAllowAzureServices = true,
FirewallRuleName = "Sample Firewall Rule",
FirewallRuleStartIP = "0.0.0.0",
FirewallRuleEndIP = "255.255.255.254", // Example Firewall Rule only. Do Not Use in Production.
DatabaseName = "Demo",
DatabaseEdition = "Basic",
DatabaseMaxSizeInGB = 1,
DatabaseCollation = "SQL_Latin1_General_CP1_CI_AS"
};
if (!VerifyConfiguration(parameters))
{
Console.ReadLine();
return;
}
Task.WaitAll(SetupAndTearDownLogicalSQLServer(parameters));
Console.WriteLine("Done");
Console.ReadLine();
}
private static bool VerifyConfiguration(SqlManagementControllerParameters serviceParameters)
{
bool configOK = true;
if (!File.Exists(serviceParameters.PublishSettingsFilePath))
{
configOK = false;
Console.WriteLine("Please download your .publishsettings file and specify the location in the Main method.");
}
if (serviceParameters.ServerAdminUsername.StartsWith("[") || serviceParameters.ServerAdminPassword.StartsWith("["))
{
configOK = false;
Console.WriteLine("Please specify a server admin username and password in the Main method.");
}
return configOK;
}
private static async Task SetupServerAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{1}. Create logical Server in Region {0}", parameters.ServerRegion, step);
ConsoleContinuePrompt("Create");
var t = Task.Run<ServerCreateResponse>(() =>
{
return controller.CreateServerAsync(parameters.ServerRegion, parameters.ServerAdminUsername,
parameters.ServerAdminPassword);
});
WaitForStatus(t);
// now that the task is done, save the returned Server Name in to a global variable
// so that we can use it again later when creating the Firewall Rules and Database etc.
_serverName = t.Result.ServerName;
if (parameters.FirewallRuleAllowAzureServices)
{
Console.WriteLine("\n{1}. Adding Firewall rules for Azure Services on server {0}", _serverName, step);
Task p = Task.Run(() => controller.ConfigureFirewallAsync(_serverName, "Azure Services", parameters.FirewallRuleStartIP, parameters.FirewallRuleEndIP));
WaitForStatus(p);
}
Console.WriteLine("\n...Complete");
}
}
private static async Task CreateDatabaseAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{2}. Creating Database {1} on Server {0}", _serverName, parameters.DatabaseName, step);
ConsoleContinuePrompt("Create");
Task t = Task.Run(() => controller.CreateDatabaseAsync(_serverName, parameters.DatabaseName, parameters.DatabaseCollation, parameters.DatabaseEdition, parameters.DatabaseMaxSizeInGB));
WaitForStatus(t);
Console.WriteLine("\n...Complete");
}
}
private static async Task ConfigureFirewallAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{1}. Adding Firewall rules for server {0}", _serverName, step);
ConsoleContinuePrompt("Create");
Task t = Task.Run(() => controller.ConfigureFirewallAsync(_serverName, parameters.FirewallRuleName, parameters.FirewallRuleStartIP, parameters.FirewallRuleEndIP));
WaitForStatus(t);
Console.WriteLine("\n...Complete");
}
}
private static async Task TearDownDatabaseAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{2}. Dropping Database {1} on Server {0}", _serverName, parameters.DatabaseName, step);
ConsoleContinuePrompt("Drop");
Task t = Task.Run(() => controller.DropDatabaseAsync(_serverName, parameters.DatabaseName));
WaitForStatus(t);
Console.WriteLine("\n...Complete");
}
}
private static async Task ListServersAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{1}. Listing Servers", _serverName, step);
ConsoleContinuePrompt("List");
var t = Task<ServerListResponse>.Run(() => { return controller.ListServersAsync(); });
WaitForStatus(t);
var servers = from s
in t.Result.Servers
select s.Name;
Console.Write("\n");
foreach (string server in servers)
{
Console.WriteLine(" Server - {0}", server);
}
Console.WriteLine("...Complete");
}
}
private static async Task ListFirewallRulesAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{1}. Listing Firewall Rules for Server {0}", _serverName, step);
ConsoleContinuePrompt("List");
var t = Task<FirewallRuleListResponse>.Run(() => { return controller.ListFirewallRulesAsync(_serverName); });
WaitForStatus(t);
var rules = from r
in t.Result.FirewallRules
select new
{
Name = r.Name,
StartIP = r.StartIPAddress,
EndIP = r.EndIPAddress
};
Console.Write("\n");
foreach (var rule in rules)
{
Console.WriteLine(" Rule - {0}\tStart IP - {1}\tEnd IP - {2}", rule.Name, rule.StartIP, rule.EndIP);
}
Console.WriteLine("...Complete");
}
}
private static async Task ListDatabasesAsync(SqlManagementControllerParameters parameters, int step)
{
using (var controller = new SqlManagementController(parameters.PublishSettingsFilePath))
{
Console.WriteLine("\n{1}. Listing Databases on Server {0}", _serverName, step);
ConsoleContinuePrompt("List");
var t = Task<DatabaseListResponse>.Run(() => { return controller.ListDatabasesAsync(_serverName); });
WaitForStatus(t);
var databases = from s
in t.Result.Databases
select s.Name;
Console.Write("\n");
foreach (string database in databases)
{
Console.WriteLine(" Database - {0}", database);
}
Console.WriteLine("...Complete");
}
}
private static async Task SetupAndTearDownLogicalSQLServer(SqlManagementControllerParameters parameters)
{
int step = 1;
// Create a new logical server
await SetupServerAsync(parameters, step++);
// List servers in subscription
await ListServersAsync(parameters, step++);
// Add new Firewall rules on the logical server created
await ConfigureFirewallAsync(parameters, step++);
// List Firewall Rules on server
await ListFirewallRulesAsync(parameters, step++);
// Create a new database on the server
await CreateDatabaseAsync(parameters, step++);
// List Firewall Rules on server
await ListDatabasesAsync(parameters, step++);
// Cleanup
await TearDownDatabaseAsync(parameters, step++);
await TearDownServerAsync(parameters, step++);
}
