Days ago, I worked on a service request that our customer is using Microsoft.Azure.Management.Sql ( NuGet Microsoft.Azure.Management.Sql 3.0.0-preview) component that automatize, using REST-API, operations like creation of Azure SQL Servers and Azure SQL Database, etc..
I worked on regarding two issues:
The following code will be to call of the methods:
#region "Azure Operations"
int i;
int iRetries = 0;
ClsOpsDBUsingMicrosoftAzureManagementSql oClsOps = new ClsOpsDBUsingMicrosoftAzureManagementSql();
ClsWriteLog oClsWriteLog = new ClsWriteLog();
Boolean bOperation = false;
String sMsg = "";
Boolean bCheck = false;
oClsWriteLog.WriteSomethingInConsole("bCreateServer - Creating the server", ConsoleColor.White);
while (!bOperation)
{
iRetries++;
bOperation = oClsOps.bCreateServer(ref sMsg, "jmjuradoyuyo", "Default-SQL-NorthEurope", bValueToReturnIfServerExists:true);
if(bCheck)
{
System.Threading.Thread.Sleep(5000);
}
if (iRetries > 3)
{
oClsWriteLog.WriteSomethingInConsole("Operation Server Creation executed with error process.Error: " + sMsg, ConsoleColor.Red);
}
}
oClsWriteLog.WriteSomethingInConsole("bCreateServer - Executed with success", ConsoleColor.White);
for (i=1;i<=10; i++)
{
sMsg = "";
iRetries = 0;
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " is executing.", ConsoleColor.Blue);
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " executing the bCreateDatabase process.", ConsoleColor.Blue);
bOperation = false;
while (!bOperation)
{
iRetries++;
bOperation = oClsOps.bCreateDatabase(ref sMsg, "jmjuradoyuyo", "Default-SQL-NorthEurope", "jmjurado2", bCheck, bValueToReturnIfDatabaseExists:true);
if (bCheck)
{
System.Threading.Thread.Sleep(5000);
}
if (iRetries > 3)
{
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " executed with error process.", ConsoleColor.Blue);
}
}
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " executed the bCreateDatabase process.", ConsoleColor.Blue);
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " executing the bDeleteDatabase process.", ConsoleColor.Blue);
bOperation = false;
iRetries = 0;
while (!bOperation)
{
iRetries++;
bOperation = oClsOps.bDeleteDatabase(ref sMsg, "jmjuradoyuyo", "Default-SQL-NorthEurope", "jmjurado2", bCheck, bValueToReturnIfDatabaseExists:true);
if (bCheck)
{
System.Threading.Thread.Sleep(5000);
}
if (iRetries > 3)
{
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " executed with error process.", ConsoleColor.Blue);
}
}
oClsWriteLog.WriteSomethingInConsole("Operation " + i.ToString() + " executed the bDeleteDatabase process.", ConsoleColor.Blue);
}
#endregion
In other to test my POC, I'm going to use the boolean variable and parameter called bCheck, if the value is true I'm going to perform additional checks if the value if false I'm not going the additional validations.
Check if the server already exists
Check if the database is not already create.
If both exists, try to create the database:
In case of success
Check if the database_operation_status reported value 2, if not, wait 5 seconds for giving time the workflow to complete the process.
Check if after creating the database the status is 0 using sys.databases, if not, wait 5 seconds for giving time the workflow to complete the process.
What are the additional validations for deleting process?
Check if the server already exists
Check if the database exists.
Check if after deleting the database this doesn't exists anymore using sys.databases. if not, wait 5 seconds for giving time the workflow to complete the process.
Retry the operation 3 times at least
Following you could find the C# code with all operations implemented:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure;
using Microsoft.Azure.Management.Sql;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Security.Cryptography.X509Certificates;
using C = System.Data.SqlClient;
using System.Data;
namespace DotNetExample
{
class ClsOpsDBUsingMicrosoftAzureManagementSql
{
public Boolean bCreateServer(ref String sMsg, string sServerName = "", string sRGName = "", Boolean bValueToReturnIfServerExists=false)
{
ClsWriteLog oClsWriteLog = new ClsWriteLog();
try
{
SqlManagementClient SqlClient = GetAccessClient("bCreateServer");
Boolean bReturn = false;
if (!bCheckIfServerExist(ref sMsg, sServerName))
{
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateServer - Server doesn't exist " + sServerName.ToString() + " in resource group:" + sRGName.ToString() + " creating it...", ConsoleColor.Green);
SqlClient.Servers.CreateOrUpdate(sRGName, sServerName, new Microsoft.Azure.Management.Sql.Models.Server
{
AdministratorLogin = "adminusername",
AdministratorLoginPassword = "Password",
Location = "WestEurope"
});
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateServer - Server created " + sServerName.ToString() + " in resource grop:" + sRGName.ToString(), ConsoleColor.Green);
bReturn = true;
}
else
{
bReturn = bValueToReturnIfServerExists;
}
return bReturn;
}
catch (Exception e)
{
sMsg = "Error: " + e.Message;
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateServer - Error Creating Server " + sServerName.ToString() + " in resource group:" + sRGName.ToString() + " Error:" + sMsg.ToString(), ConsoleColor.Red);
return false;
}
}
public Boolean bCreateDatabase(ref String sMsg, string sServerName = "", string sRGName = "", string sDBName = "", Boolean bCheck = true, Boolean bValueToReturnIfDatabaseExists= false)
{
ClsWriteLog oClsWriteLog = new ClsWriteLog();
try
{
SqlManagementClient SqlClient = GetAccessClient("bCreateDatabase");
Boolean bReturn = false;
Microsoft.Azure.Management.Sql.Models.Sku Skus = new Microsoft.Azure.Management.Sql.Models.Sku("S0", "Standard");
if (bCheckIfServerExist(ref sMsg, sServerName, bCheck))
{
if (!bCheckIfDatabaseExist(ref sMsg, sServerName, sRGName, sDBName,bCheck, false))
{
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateDatabase - Creating the database " + sDBName.ToString() + " in server: " + sServerName.ToString(), ConsoleColor.Magenta);
SqlClient.Databases.CreateOrUpdate(sRGName, sServerName, sDBName, new Microsoft.Azure.Management.Sql.Models.Database
{
Collation = "SQL_Latin1_General_CP1_CI_AS",
Sku = Skus,
Location = "WestEurope"
});
if (bCheck)
{
while (!bOperationFinished(ref sMsg, sServerName, sDBName, "C"))
{
System.Threading.Thread.Sleep(5000);
}
}
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateDatabase - Created the database " + sDBName.ToString() + " in server " + sServerName.ToString(), ConsoleColor.Green);
bReturn = true;
}
else
{
sMsg = "Database exists";
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateDatabase - Creating DB " + sDBName.ToString() + " exists in server " + sServerName.ToString(), ConsoleColor.Yellow);
bReturn = bValueToReturnIfDatabaseExists;
}
}
return bReturn;
}
catch (Exception e)
{ sMsg = "Error: " + e.Message;
oClsWriteLog.WriteSomethingInConsole("-----------> bCreateDatabase - Error creating DB " + sDBName.ToString() + " in server " + sServerName.ToString() + " Error:" + sMsg.ToString(), ConsoleColor.Red);
return false;
}
}
public Boolean bDeleteDatabase(ref String sMsg, string sServerName = "", string sRGName = "", string sDBName = "", Boolean bCheck = true, Boolean bValueToReturnIfDatabaseExists = false)
{
ClsWriteLog oClsWriteLog = new ClsWriteLog();
try
{
SqlManagementClient SqlClient = GetAccessClient("bDeleteDatabase");
Boolean bReturn = false;
if (bCheckIfServerExist(ref sMsg, sServerName,bCheck))
{
if (bCheckIfDatabaseExist(ref sMsg, sServerName, sRGName, sDBName, bCheck))
{
oClsWriteLog.WriteSomethingInConsole("-----------> bDeleteDatabase - Deleting the database " + sDBName.ToString() + " in Server:" + sServerName.ToString(), ConsoleColor.Magenta);
SqlClient.Databases.Delete(sRGName, sServerName, sDBName);
if (bCheck)
{
while (!bOperationFinished(ref sMsg, sServerName, sDBName, "D"))
{
System.Threading.Thread.Sleep(5000);
}
}
bReturn = true;
oClsWriteLog.WriteSomethingInConsole("-----------> bDeleteDatabase - Deleted the database " + sDBName.ToString() + " in Server: " + sServerName.ToString(), ConsoleColor.Green);
}
else
{
bReturn = bValueToReturnIfDatabaseExists;
oClsWriteLog.WriteSomethingInConsole("-----------> bDeleteDatabase - Database doesn't exists " + sDBName.ToString() + " in Server: " + sServerName.ToString(), ConsoleColor.Green);
}
}
return bReturn;
}
catch (Exception e)
{
sMsg = "Error: " + e.Message;
oClsWriteLog.WriteSomethingInConsole("-----------> bDeleteDatabase - Error deleting the database: " + sDBName.ToString() + " in Server: " + sServerName.ToString(), ConsoleColor.Red);
return false;
}
}
public Boolean bCheckIfServerExist( ref string sMsg, string sServerName = "",Boolean bCheck = true)
{
ClsWriteLog oClsWriteLog = new ClsWriteLog();
try
{
if(!bCheck)
{
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfServerExist - Skipping if the Server " + sServerName.ToString() + " exists.", ConsoleColor.White);
return true;
}
SqlManagementClient SqlClient = GetAccessClient("bCheckIfServerExist");
Boolean bReturn = false;
var List = SqlClient.Servers.List();
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfServerExist - Checking if the Server " + sServerName.ToString() + " exists.", ConsoleColor.White);
foreach (Microsoft.Azure.Management.Sql.Models.Server ServerItem in List)
{
if( ServerItem.Name.ToLower().Trim() == sServerName.ToLower().Trim())
{
sMsg = "Server already exist";
bReturn = true;
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfServerExist - Yeah! the Server " + sServerName.ToString() + " exists.", ConsoleColor.Green);
break;
}
}
if(!bReturn)
{
sMsg = "Server doesn't exists";
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfServerExist - The Server " + sServerName.ToString() + " doesn't exists.", ConsoleColor.Green);
}
return bReturn;
}
catch (Exception e)
{
sMsg = "Error: " + e.Message;
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfServerExist - Error Checking if the Server " + sServerName.ToString() + " exists. Error: " + sMsg.ToString(), ConsoleColor.Red);
return false;
}
}
public Boolean bCheckIfDatabaseExist(ref string sMsg, string sServerName = "", string sRGName = "", string sDatabaseName = "", Boolean bCheck = true, Boolean bReturnValue = true)
{
ClsWriteLog oClsWriteLog = new ClsWriteLog();
if (!bCheck)
{
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfDatabaseExist - Skipping if database: " + sDatabaseName.ToString() + " exists in server " + sServerName.ToString(), ConsoleColor.White);
return bReturnValue;
}
try
{
SqlManagementClient SqlClient = GetAccessClient("bCheckIfDatabaseExist");
Boolean bReturn = false;
var List = SqlClient.Databases.ListByServer(sRGName, sServerName);
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfDatabaseExist - Checking if database: " + sDatabaseName.ToString() + " exists in server " + sServerName.ToString(), ConsoleColor.White);
foreach (Microsoft.Azure.Management.Sql.Models.Database DatabaseName in List)
{
if (DatabaseName.Name.ToLower().Trim() == sDatabaseName.ToLower().Trim())
{
sMsg = "Database already exists";
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfDatabaseExist - The database: " + sDatabaseName.ToString() + " exists in server " + sServerName.ToString(), ConsoleColor.Green);
bReturn = true;
break;
}
}
return bReturn;
}
catch (Exception e)
{
sMsg = "Error: " + e.Message;
oClsWriteLog.WriteSomethingInConsole("-----------> bCheckIfDatabaseExist - Error checking if database: " + sDatabaseName.ToString() + " exists in server " + sServerName.ToString() + " Error: " + sMsg, ConsoleColor.Red);
return false;
}
}
public Boolean bOperationFinished(ref String sMsg, string sServerName = "", string sDBName = "", String sOperation = "I")
{
ClsWriteLog oClsWriteLog = new ClsWriteLog();
try
{
Boolean bReturn = false;
ClsSecurity oClsSecurity = new ClsSecurity();
oClsSecurity.ServerName = "tcp:" + sServerName + ".database.windows.net,1433";
oClsSecurity.DBName = "master";
oClsSecurity.TypeExecution = "Management Operations";
C.SqlConnection oConnSource = new C.SqlConnection();
ClsRetryLogic oClsRetrySource = new ClsRetryLogic();
if (oClsRetrySource.HazUnaConexionConReintentos(oClsSecurity.GetConnectionString(), oConnSource, false,false))
{
if (sOperation == "D")
{
String sSyntaxOperation = "SELECT TOP 1 State FROM sys.dm_operation_status WHERE UPPER(convert(nvarchar(128),major_resource_id)) = '" + sDBName + "' AND operation = 'DROP DATABASE' ORDER BY start_time DESC";
String sSyntaxCheckSysDatabase = "SELECT TOP 1 name from sys.databases WHERE UPPER(name) = '" + sDBName + "'";
C.SqlCommand oCmdOperation = new C.SqlCommand(sSyntaxOperation, oConnSource);
oCmdOperation.CommandType = CommandType.Text;
C.SqlCommand oCmdSYSDB = new C.SqlCommand(sSyntaxCheckSysDatabase, oConnSource);
oCmdSYSDB.CommandType = CommandType.Text;
C.SqlDataReader oReaderOperation = oCmdOperation.ExecuteReader();
if (oReaderOperation.HasRows)
{
oReaderOperation.Read();
if (oReaderOperation.GetValue(0).ToString() == "2")
{
oReaderOperation.Close();
C.SqlDataReader oReaderSYSDB = oCmdSYSDB.ExecuteReader();
bReturn = !oReaderSYSDB.HasRows;
oReaderSYSDB.Close();
}
else
{
oReaderOperation.Close();
}
}
}
if (sOperation == "C")
{
String sSyntaxOperation = "SELECT TOP 1 State FROM sys.dm_operation_status WHERE UPPER(convert(nvarchar(128),major_resource_id)) = '" + sDBName + "' AND operation = 'CREATE DATABASE' ORDER BY start_time DESC";
String sSyntaxCheckSysDatabase = "SELECT TOP 1 name from sys.databases WHERE UPPER(name) = '" + sDBName + "' AND state=0";
C.SqlCommand oCmdOperation = new C.SqlCommand(sSyntaxOperation, oConnSource);
oCmdOperation.CommandType = CommandType.Text;
C.SqlCommand oCmdSYSDB = new C.SqlCommand(sSyntaxCheckSysDatabase, oConnSource);
oCmdSYSDB.CommandType = CommandType.Text;
C.SqlDataReader oReaderOperation = oCmdOperation.ExecuteReader();
if (oReaderOperation.HasRows)
{
oReaderOperation.Read();
if (oReaderOperation.GetValue(0).ToString() == "2")
{
oReaderOperation.Close();
C.SqlDataReader oReaderSYSDB = oCmdSYSDB.ExecuteReader();
bReturn = oReaderSYSDB.HasRows;
oReaderSYSDB.Close();
}
else
{
oReaderOperation.Close();
}
}
}
oConnSource.Close();
}
return bReturn;
}
catch (Exception e)
{
sMsg = "Error: " + e.Message;
oClsWriteLog.WriteSomethingInConsole("-----------> Checking the DB Error: " + sMsg, ConsoleColor.Red);
return false;
}
}
public SqlManagementClient GetAccessClient(String sOperation = "")
{
//WriteSomethingInConsole("-------------> GetAccessClient - Getting the client and Token for " + sOperation.ToString(), ConsoleColor.White);
var authContextUrl = "https://login.microsoftonline.com/tenantid";
var authenticationContext = new AuthenticationContext(authContextUrl);
var credential = new ClientCredential("principalid", "secret");
var result = authenticationContext.AcquireTokenAsync(resource: "https://management.core.windows.net/", clientCredential: credential).Result;
//var credentials = new Microsoft.Rest.TokenCredentials(result.AccessToken);
Microsoft.Rest.ServiceClientCredentials credentials = new Microsoft.Rest.TokenCredentials(result.AccessToken);
if (result == null)
{
throw new InvalidOperationException("-------------> GetAccessClient - Failed to obtain the JWT token for " + sOperation.ToString());
}
var client = new SqlManagementClient(credentials);
client.SubscriptionId = "subcriptionId";
//WriteSomethingInConsole("-------------> GetAccessClient - Token Obtained..for " + sOperation.ToString(), ConsoleColor.White);
return client;
}
}
}
Additionally, I developed this small C# code to capture all the messages.
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DotNetExample
{
class ClsWriteLog
{
public void WriteSomethingInConsole(String sText, System.ConsoleColor iColor)
{
System.ConsoleColor iColorTmp;
iColorTmp = Console.ForegroundColor;
Console.ForegroundColor = iColor;
Console.WriteLine(DateTime.Now.ToString() + " " + sText);
Console.ForegroundColor = iColorTmp;
}
}
}
Screenshot about the results of the execution
Additionaly I used the following TSQL command to monitor all the operations an current status using SQL Server Management Studio. Everytime that any operation is executing you could see the results in this DMVs.
SELECT * FROM sys.dm_operation_status ORDER BY start_time DESC
Screenshot of the results.
In summary, adding these checks, I didn't receive any error message.
Enjoy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.