Category: Azure

Azure SQL(PaaS): Geo-Replication (DR)

I have seen couple of scenario where most of the clients and customer really wants to go with SQL (PaaS) services but they are not moving because if an case SQL PaaS services got crashed due to some technical issue there was option to recover the services or fail back the services with limited time frame. […]

via Azure SQL(PaaS): Geo-Replication (DR) — Azure4you By Lalit Rawat

Deploy VM via ARM template: Purchase eligibility failed

I recently tried to deploy a VM using an ARM template executed via PowerShell and I encountered the purchase eligibility failed error as seen below. As I have encountered this before I ensured I accepted marketplace terms for the VM image in question using the PowerShell commands: Get-AzureRmMarketplaceTerms -Publisher PublisherName -Product ProductName -Name Name | […]

via Deploy VM via ARM template: Purchase eligibility failed — Kloud Blog

Migrating SQL Server Database on Azure SQL PaaS

Posted by: prabthind in AZURE, Azure SQL January 7, 2017 0 135 Views

I have recently been working testing the migration of the on-premise SQL Server databases on Azure SQL. This blog is about the guidance on how this can be achieved.
Azure SQL is Microsoft’s PaaS offering where the IT teams in organizations don’t have to worry about the infrastructure and leverage the functionality of the SQL database almost as identical as what they are used to with on-premise SQL servers. This way new databases can be spun in a matter of minutes. More information about Azure SQL can be found here.
We will use a SQL 2012 for the purposes of this blog since most customers environments I have worked with are still on SQL Server 2012. We will be using SSDT(SQL Server Data Tools) 2015 and SSMS(SQL Server Management Studio) 2016, both of which are available to download from Microsoft for free(here and here). It is advisable to use the latest binaries for these because they have a complete feature set needed to test and migrate your databases to Azure SQL.
The migration of the SQL databases can be done in three steps.

  1. Test SQL database compatibility
  2. Fix compatibility issues
  3. Migrate to Azure SQL

 

Test SQL Compatibility and Fix Compatibility Issues

In order to test the compatibility of the SQL Server database with Azure SQL, Microsoft has provided the following options:

  1. SQL Server 2016 Upgrade advisor: This is the default SQL 2016 advisor which can be used to test the schema compatibility with Azure SQL. Although what i found was that this tool does not pick all the issues with the compatibility and you might be left with surprise after migration.
  2. SSDT 2015: This would be my go-to tool for looking into the issues with Compatibility and also helps with resolving them so that your database is compatible with Azure SQL. This provides testing for both Azure SQL V11 and V12. Although, always use V12 since there is a better parity for the features in Azure SQL V12 than V11 so you are likely to run into lesser errors during testing.
  3. SQL Package and SSMS: You can use these tools as well for finding the issues with the schema changes but they don’t provide much help with fixing the issues.

We will choose SSDT for this article. We have a created a Windows 2012 R2 VM on Azure with SQL Server 2012 SP3. I have downloaded and imported the AdventureWorks database from Microsoft’s website for SQL 2012. As you can see below, the database contains the data in the tables in SSMS

http://cloudsunboxed.com/2017/01/07/migrating-sql-server-database-on-azure-sql-paas/

In the next blog, I will outline the best practices for the Azure SQL migration. Till next time…

 

Reference Link – http://cloudsunboxed.com/2017/01/07/migrating-sql-server-database-on-azure-sql-paas/

Posted by Sheikvara

+919840688822, +919003270444

PowerShell script to update Azure firewall rule

July 28, 2016

I’ve recently moved house, and as a result had to change my broadband plan from cable to ADSL (sad face). This also means I’ve gone from having a fixed IP address to a dynamically assigned one. Usually this wouldn’t be a problem, except when it comes to connecting to the several Azure servers that I manage on a daily basis. Now I need to use the Azure Portal to manually change each server’s firewall settings at least once or twice a week. Painfull…

So I quickly threw together this PS script to do the job for me, and thought others out there might find it useful too.

How’s it work?

The script accepts an array of Azure SQL Server names, finds your external IP address using myexternalip.com, and then loops through the list of servers. You’ll need to provide a default rule name, or modify the function call to pass it in (maybe include it in the array if it’s different for each server?).

It then checks the current IP address of the specified rule and, if it’s different to your external IP address, updates the firewall rule for you. #Magic

 

Import-Module SQLPS -DisableNameChecking
Import-Module Azure
cls
[array]$AzureServers = @(‘ServerName1’,‘ServerName2’,‘ServerName3’<# etc, etc as needed#>);
function Get-MyIpAddress
{
    $wc = new-object System.Net.WebClient
    $ip = $wc.DownloadString(“http://myexternalip.com/raw&#8221;).ToString().Trim();
    return $ip;
}
function Update-MyAzureFirewallRule
{
    Param (
        [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)]
        [string]$ServerName,
        [string]$RuleName = ‘MyDefaultRuleName’,
        [string]$IpAddress
        )
    $CurrentRule = Get-AzureSqlDatabaseServerFirewallRule -RuleName $RuleName -ServerName $ServerName;
    $CurrentIp = $CurrentRule.StartIpAddress
    if ($CurrentIp -ne $IpAddress)
    {
        Write-Host “Setting firewall rule ‘$RuleName’ on server ‘$ServerName’ to IP address ‘$IpAddress’ (was ‘$CurrentIp’)…”
        Set-AzureSqlDatabaseServerFirewallRule -StartIPAddress $IpAddress -EndIPAddress $IpAddress -RuleName $RuleName -ServerName $ServerName;
    }
    
}
if ($IpAddress = Get-MyIpAddress)
{
    Write-Host “My IP address is $IpAddress”
    foreach ($s in $AzureServers)
    {
        Update-MyAzureFirewallRule -ServerName $s -IpAddress $IpAddress;
    }
}

This post provided the inspiration, which I then tweaked it to suit my needs. Like I said; it’s quick-and-dirty, so use at your own risk. 😉  I’m no PowerShell guru either, so feel free to let me know if you improve on it.

Cheers,
Dave

Author: DB Dave

Currently the database & infrastructure guy at Timely Ltd. Between 2007 and mid-2014 I was the SQL Server Database Team manager at Trade Me. And before that I worked for a government department, a large private supermarket cooperative, and an IT training company. I’m originally from the sunny southern coast of South Africa, but since 2003 I’ve called New Zealand home. I do stuff with SQL Server, Azure, Business Intelligence, PowerShell, etc.

Sheikvara
+919840688822, +919003270444

HOW TO INSTALL THE AZURE POWERSHELL MODULE

Well if you are working with Microsoft Azure you may need the PowerShell Modules for automation and some settings which are only available in PowerShell. With the latest releases you can install the Azure PowerShell Module in several different ways.

Install Azure PowerShell

For me using the PowerShell Package Management and the PowerShell Gallery is may the easiest and fastest way to install it. In Windows 10 or a computer with the Windows Management Framework 5 installed, you can use the following PowerShell cmdlets to install it:

Microsoft Azure Resource Manager (new Portal):

And you can use the following command to login:

If you are using it against the classic Azure Portal you can use the following:

And you can use the following command to login:

 

You can also see the Azure PowerShell Modules and versions using the PowerShell Package Management:

Azure PowerShell Module

 

Posted by Sheikvara

+919840688822, +919003270444