Lesson Learned #84: How to synchronize views definitions between databases

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a case that our customer needs to synchronize the definitions of all the views that they have between two databases.

To archive this, I suggested to run an Azure RunBook using the two following PowerShell scripts:

• To synchronize the views that the serverA/databaseA has with server/database. In every execution of this script you are going to create or alter the views from Database A to Database B.

#Connectivity details User DB and master DB
$server= "tcp:serverA.database.windows.net,1433"
$user="user"
$password="password"
$Db="databaseA"

#Connectivity details User DB and master DB
$serverTarget= "tcp:serverB.database.windows.net,1433"
$userTarget="user"
$passwordTarget="password"
$DbTarget="DatabaseB"



#Function to connect to the database
Function GiveMeConnectionSource{ 
  for ($i=0; $i -lt 10; $i++)
  {
   try
    {
      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$server+";Database="+$Db+";User ID="+$user+";Password="+$password+";Connection Timeout=60" 
      $SQLConnection.Open()
      break;
    }
  catch
   {
    Start-Sleep -s 5
   }
  }
   Write-output $SQLConnection
}

Function GiveMeConnectionTarget{ 
  for ($i=0; $i -lt 10; $i++)
  {
   try
    {
      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$serverTarget+";Database="+$DbTarget+";User ID="+$userTarget+";Password="+$passwordTarget+";Connection Timeout=60" 
      $SQLConnection.Open()
      break;
    }
  catch
   {
    Start-Sleep -s 5
   }
  }
   Write-output $SQLConnection
}

try
{
Clear-Host

$SQLConnectionSource = GiveMeConnectionSource
$SQLConnectionTarget = GiveMeConnectionTarget

$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand("Select Name,REPLACE(object_definition(object_id),'CREATE VIEW','CREATE OR ALTER VIEW')from sys.views Order by Name", $SQLConnectionSource) 
 $Reader = $SQLCommandExiste.ExecuteReader(); 
 while($Reader.Read())
   {
    $ComandCreate=$Reader.GetSqlString(1).ToString()
    Write-Host $ComandCreate
    $SQLCommandExec = New-Object System.Data.SqlClient.SqlCommand($ComandCreate, $SQLConnectionTarget) 
    $SQLCommandExec.CommandTimeout=1200;
    $SQLCommandExec.ExecuteNonQuery(); 
  }
   $Reader.Close();

$SQLConnectionSource.Close() 
}
catch
  {
    Write-Host -ForegroundColor DarkYellow "You're WRONG"
    Write-Host -ForegroundColor Magenta $Error[0].Exception
  }
finally
{
   Write-Host -ForegroundColor Cyan "It's finally over..."
} 

• In you want to drop the views that don’t exist in Server A/Database A that could be created or still existing in ServerB/DatabaseB you could follow up the instructions below.

#Connectivity details User DB and master DB
$server= " tcp:serverB.database.windows.net,1433"
$user="user"
$password="password"
$Db="databaseB"

#Connectivity details User DB and master DB
$serverTarget= "tcp:serverA.database.windows.net,1433"
$userTarget="user"
$passwordTarget="password"
$DbTarget="databaseA"


#Function to connect to the database
Function GiveMeConnectionSourceRead{ 
  for ($i=0; $i -lt 10; $i++)
  {
   try
    {
      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$server+";Database="+$Db+";User ID="+$user+";Password="+$password+";Connection Timeout=60" 
      $SQLConnection.Open()
      break;
    }
  catch
   {
    Start-Sleep -s 5
   }
  }
   Write-output $SQLConnection
}

#Function to connect to the database
Function GiveMeConnectionSourceWrite{ 
  for ($i=0; $i -lt 10; $i++)
  {
   try
    {
      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$server+";Database="+$Db+";User ID="+$user+";Password="+$password+";Connection Timeout=60" 
      $SQLConnection.Open()
      break;
    }
  catch
   {
    Start-Sleep -s 5
   }
  }
   Write-output $SQLConnection
}

Function GiveMeConnectionTarget{ 
  for ($i=0; $i -lt 10; $i++)
  {
   try
    {
      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$serverTarget+";Database="+$DbTarget+";User ID="+$userTarget+";Password="+$passwordTarget+";Connection Timeout=60" 
      $SQLConnection.Open()
      break;
    }
  catch
   {
    Start-Sleep -s 5
   }
  }
   Write-output $SQLConnection
}


try
{
Clear-Host

$SQLConnectionSourceRead = GiveMeConnectionSourceRead
$SQLConnectionSourceWrite = GiveMeConnectionSourceWrite
$SQLConnectionTarget = GiveMeConnectionTarget

$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand("Select Name,'drop view [' + Name +']' FROM SYS.VIEWS Order by Name", $SQLConnectionSourceRead) 
 $Reader = $SQLCommandExiste.ExecuteReader(); 
 while($Reader.Read())
   {
    $ComandSearch=“SELECT Name FROM sys.views where name='"+$Reader.GetSqlString(0).ToString() + "'"
    $ComandCreate=$Reader.GetSqlString(1).ToString()
    Write-Host $ComandSearch
    Write-Host $ComandCreate
    $SQLCommandVerify = New-Object System.Data.SqlClient.SqlCommand($ComandSearch, $SQLConnectionTarget) 

    $ReaderSearch = $SQLCommandVerify.ExecuteReader(); 
    if($ReaderSearch.HasRows -eq 0)
     {
      $ReaderSearch.Close();
      $SQLCommandExec = New-Object System.Data.SqlClient.SqlCommand($ComandCreate, $SQLConnectionSourceWrite) 
      $SQLCommandExec.CommandTimeout=1200;
      $SQLCommandExec.ExecuteNonQuery(); 
     }
    
    $ReaderSearch.Close();
  }
   $Reader.Close();

$SQLConnectionSource.Close() 
}
catch
  {
    Write-Host -ForegroundColor DarkYellow "You're WRONG"
    Write-Host -ForegroundColor Magenta $Error[0].Exception
  }
finally
{
   Write-Host -ForegroundColor Cyan "It's finally over..."

Enjoy!