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!
Updated Apr 23, 2019
Version 1.0Jose_Manuel_Jurado
Microsoft
Joined November 29, 2018
Azure Database Support Blog
Follow this blog board to get notified when there's new activity