Home
%3CLINGO-SUB%20id%3D%22lingo-sub-481043%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2384%3A%20How%20to%20synchronize%20views%20definitions%20between%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481043%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20case%20that%20our%20customer%20needs%20to%20synchronize%20the%20definitions%20of%20all%20the%20views%20that%20they%20have%20between%20two%20databases.%3C%2FP%3E%0A%3CP%3ETo%20archive%20this%2C%20I%20suggested%20to%20run%20an%20Azure%20RunBook%20using%20the%20two%20following%20PowerShell%20scripts%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ETo%20synchronize%20the%20views%20that%20the%20serverA%2FdatabaseA%20has%20with%20server%2Fdatabase.%20In%20every%20execution%20of%20this%20script%26nbsp%3Byou%20are%20going%20to%20create%20or%20alter%20the%20views%20from%20Database%26nbsp%3BA%20to%20Database%26nbsp%3BB.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%3E%23Connectivity%20details%20User%20DB%20and%20master%20DB%0A%24server%3D%20%22tcp%3AserverA.database.windows.net%2C1433%22%0A%24user%3D%22user%22%0A%24password%3D%22password%22%0A%24Db%3D%22databaseA%22%0A%0A%23Connectivity%20details%20User%20DB%20and%20master%20DB%0A%24serverTarget%3D%20%22tcp%3AserverB.database.windows.net%2C1433%22%0A%24userTarget%3D%22user%22%0A%24passwordTarget%3D%22password%22%0A%24DbTarget%3D%22DatabaseB%22%0A%0A%0A%0A%23Function%20to%20connect%20to%20the%20database%0AFunction%20GiveMeConnectionSource%7B%20%0A%20%20for%20(%24i%3D0%3B%20%24i%20-lt%2010%3B%20%24i%2B%2B)%0A%20%20%7B%0A%20%20%20try%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%24SQLConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%0A%20%20%20%20%20%20%24SQLConnection.ConnectionString%20%3D%20%22Server%3D%22%2B%24server%2B%22%3BDatabase%3D%22%2B%24Db%2B%22%3BUser%20ID%3D%22%2B%24user%2B%22%3BPassword%3D%22%2B%24password%2B%22%3BConnection%20Timeout%3D60%22%20%0A%20%20%20%20%20%20%24SQLConnection.Open()%0A%20%20%20%20%20%20break%3B%0A%20%20%20%20%7D%0A%20%20catch%0A%20%20%20%7B%0A%20%20%20%20Start-Sleep%20-s%205%0A%20%20%20%7D%0A%20%20%7D%0A%20%20%20Write-output%20%24SQLConnection%0A%7D%0A%0AFunction%20GiveMeConnectionTarget%7B%20%0A%20%20for%20(%24i%3D0%3B%20%24i%20-lt%2010%3B%20%24i%2B%2B)%0A%20%20%7B%0A%20%20%20try%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%24SQLConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%0A%20%20%20%20%20%20%24SQLConnection.ConnectionString%20%3D%20%22Server%3D%22%2B%24serverTarget%2B%22%3BDatabase%3D%22%2B%24DbTarget%2B%22%3BUser%20ID%3D%22%2B%24userTarget%2B%22%3BPassword%3D%22%2B%24passwordTarget%2B%22%3BConnection%20Timeout%3D60%22%20%0A%20%20%20%20%20%20%24SQLConnection.Open()%0A%20%20%20%20%20%20break%3B%0A%20%20%20%20%7D%0A%20%20catch%0A%20%20%20%7B%0A%20%20%20%20Start-Sleep%20-s%205%0A%20%20%20%7D%0A%20%20%7D%0A%20%20%20Write-output%20%24SQLConnection%0A%7D%0A%0Atry%0A%7B%0AClear-Host%0A%0A%24SQLConnectionSource%20%3D%20GiveMeConnectionSource%0A%24SQLConnectionTarget%20%3D%20GiveMeConnectionTarget%0A%0A%24SQLCommandExiste%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand(%22Select%20Name%2CREPLACE(object_definition(object_id)%2C'CREATE%20VIEW'%2C'CREATE%20OR%20ALTER%20VIEW')from%20sys.views%20Order%20by%20Name%22%2C%20%24SQLConnectionSource)%20%0A%20%24Reader%20%3D%20%24SQLCommandExiste.ExecuteReader()%3B%20%0A%20while(%24Reader.Read())%0A%20%20%20%7B%0A%20%20%20%20%24ComandCreate%3D%24Reader.GetSqlString(1).ToString()%0A%20%20%20%20Write-Host%20%24ComandCreate%0A%20%20%20%20%24SQLCommandExec%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand(%24ComandCreate%2C%20%24SQLConnectionTarget)%20%0A%20%20%20%20%24SQLCommandExec.CommandTimeout%3D1200%3B%0A%20%20%20%20%24SQLCommandExec.ExecuteNonQuery()%3B%20%0A%20%20%7D%0A%20%20%20%24Reader.Close()%3B%0A%0A%24SQLConnectionSource.Close()%20%0A%7D%0Acatch%0A%20%20%7B%0A%20%20%20%20Write-Host%20-ForegroundColor%20DarkYellow%20%22You're%20WRONG%22%0A%20%20%20%20Write-Host%20-ForegroundColor%20Magenta%20%24Error%5B0%5D.Exception%0A%20%20%7D%0Afinally%0A%7B%0A%20%20%20Write-Host%20-ForegroundColor%20Cyan%20%22It's%20finally%20over...%22%0A%7D%20%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIn%20you%20want%20to%20drop%20the%20views%20that%20don%E2%80%99t%20exist%20in%20Server%20A%2FDatabase%20A%20that%20could%20be%20created%20or%20still%20existing%20in%20ServerB%2FDatabaseB%20you%20could%20follow%20up%20the%20instructions%20below.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%3E%23Connectivity%20details%20User%20DB%20and%20master%20DB%0A%24server%3D%20%22%20tcp%3AserverB.database.windows.net%2C1433%22%0A%24user%3D%22user%22%0A%24password%3D%22password%22%0A%24Db%3D%22databaseB%22%0A%0A%23Connectivity%20details%20User%20DB%20and%20master%20DB%0A%24serverTarget%3D%20%22tcp%3AserverA.database.windows.net%2C1433%22%0A%24userTarget%3D%22user%22%0A%24passwordTarget%3D%22password%22%0A%24DbTarget%3D%22databaseA%22%0A%0A%0A%23Function%20to%20connect%20to%20the%20database%0AFunction%20GiveMeConnectionSourceRead%7B%20%0A%20%20for%20(%24i%3D0%3B%20%24i%20-lt%2010%3B%20%24i%2B%2B)%0A%20%20%7B%0A%20%20%20try%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%24SQLConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%0A%20%20%20%20%20%20%24SQLConnection.ConnectionString%20%3D%20%22Server%3D%22%2B%24server%2B%22%3BDatabase%3D%22%2B%24Db%2B%22%3BUser%20ID%3D%22%2B%24user%2B%22%3BPassword%3D%22%2B%24password%2B%22%3BConnection%20Timeout%3D60%22%20%0A%20%20%20%20%20%20%24SQLConnection.Open()%0A%20%20%20%20%20%20break%3B%0A%20%20%20%20%7D%0A%20%20catch%0A%20%20%20%7B%0A%20%20%20%20Start-Sleep%20-s%205%0A%20%20%20%7D%0A%20%20%7D%0A%20%20%20Write-output%20%24SQLConnection%0A%7D%0A%0A%23Function%20to%20connect%20to%20the%20database%0AFunction%20GiveMeConnectionSourceWrite%7B%20%0A%20%20for%20(%24i%3D0%3B%20%24i%20-lt%2010%3B%20%24i%2B%2B)%0A%20%20%7B%0A%20%20%20try%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%24SQLConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%0A%20%20%20%20%20%20%24SQLConnection.ConnectionString%20%3D%20%22Server%3D%22%2B%24server%2B%22%3BDatabase%3D%22%2B%24Db%2B%22%3BUser%20ID%3D%22%2B%24user%2B%22%3BPassword%3D%22%2B%24password%2B%22%3BConnection%20Timeout%3D60%22%20%0A%20%20%20%20%20%20%24SQLConnection.Open()%0A%20%20%20%20%20%20break%3B%0A%20%20%20%20%7D%0A%20%20catch%0A%20%20%20%7B%0A%20%20%20%20Start-Sleep%20-s%205%0A%20%20%20%7D%0A%20%20%7D%0A%20%20%20Write-output%20%24SQLConnection%0A%7D%0A%0AFunction%20GiveMeConnectionTarget%7B%20%0A%20%20for%20(%24i%3D0%3B%20%24i%20-lt%2010%3B%20%24i%2B%2B)%0A%20%20%7B%0A%20%20%20try%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%24SQLConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%0A%20%20%20%20%20%20%24SQLConnection.ConnectionString%20%3D%20%22Server%3D%22%2B%24serverTarget%2B%22%3BDatabase%3D%22%2B%24DbTarget%2B%22%3BUser%20ID%3D%22%2B%24userTarget%2B%22%3BPassword%3D%22%2B%24passwordTarget%2B%22%3BConnection%20Timeout%3D60%22%20%0A%20%20%20%20%20%20%24SQLConnection.Open()%0A%20%20%20%20%20%20break%3B%0A%20%20%20%20%7D%0A%20%20catch%0A%20%20%20%7B%0A%20%20%20%20Start-Sleep%20-s%205%0A%20%20%20%7D%0A%20%20%7D%0A%20%20%20Write-output%20%24SQLConnection%0A%7D%0A%0A%0Atry%0A%7B%0AClear-Host%0A%0A%24SQLConnectionSourceRead%20%3D%20GiveMeConnectionSourceRead%0A%24SQLConnectionSourceWrite%20%3D%20GiveMeConnectionSourceWrite%0A%24SQLConnectionTarget%20%3D%20GiveMeConnectionTarget%0A%0A%24SQLCommandExiste%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand(%22Select%20Name%2C'drop%20view%20%5B'%20%2B%20Name%20%2B'%5D'%20FROM%20SYS.VIEWS%20Order%20by%20Name%22%2C%20%24SQLConnectionSourceRead)%20%0A%20%24Reader%20%3D%20%24SQLCommandExiste.ExecuteReader()%3B%20%0A%20while(%24Reader.Read())%0A%20%20%20%7B%0A%20%20%20%20%24ComandSearch%3D%E2%80%9CSELECT%20Name%20FROM%20sys.views%20where%20name%3D'%22%2B%24Reader.GetSqlString(0).ToString()%20%2B%20%22'%22%0A%20%20%20%20%24ComandCreate%3D%24Reader.GetSqlString(1).ToString()%0A%20%20%20%20Write-Host%20%24ComandSearch%0A%20%20%20%20Write-Host%20%24ComandCreate%0A%20%20%20%20%24SQLCommandVerify%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand(%24ComandSearch%2C%20%24SQLConnectionTarget)%20%0A%0A%20%20%20%20%24ReaderSearch%20%3D%20%24SQLCommandVerify.ExecuteReader()%3B%20%0A%20%20%20%20if(%24ReaderSearch.HasRows%20-eq%200)%0A%20%20%20%20%20%7B%0A%20%20%20%20%20%20%24ReaderSearch.Close()%3B%0A%20%20%20%20%20%20%24SQLCommandExec%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand(%24ComandCreate%2C%20%24SQLConnectionSourceWrite)%20%0A%20%20%20%20%20%20%24SQLCommandExec.CommandTimeout%3D1200%3B%0A%20%20%20%20%20%20%24SQLCommandExec.ExecuteNonQuery()%3B%20%0A%20%20%20%20%20%7D%0A%20%20%20%20%0A%20%20%20%20%24ReaderSearch.Close()%3B%0A%20%20%7D%0A%20%20%20%24Reader.Close()%3B%0A%0A%24SQLConnectionSource.Close()%20%0A%7D%0Acatch%0A%20%20%7B%0A%20%20%20%20Write-Host%20-ForegroundColor%20DarkYellow%20%22You're%20WRONG%22%0A%20%20%20%20Write-Host%20-ForegroundColor%20Magenta%20%24Error%5B0%5D.Exception%0A%20%20%7D%0Afinally%0A%7B%0A%20%20%20Write-Host%20-ForegroundColor%20Cyan%20%22It's%20finally%20over...%22%0A%3C%2FPRE%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-481043%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20case%20that%20our%20customer%20needs%20to%20synchronize%20the%20definitions%20of%20all%20the%20views%20that%20they%20have%20between%20two%20databases.%3C%2FP%3E%0A%3CP%3ETo%20archive%20this%2C%20I%20suggested%20to%20run%20an%20Azure%20RunBook%20using%20the%20two%20following%20PowerShell%20scripts.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E

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!