Category: Azure

Migrating SQL Server Database on Azure SQL PaaS

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

 

Secure your VSTS Release Management Azure VM deployments with NSGs and PowerShell — siliconvalve

One of the neat features of VSTS’ Release Management capability is the ability to deploy to Virtual Machine hosted in Azure (amongst other environments) which I previously walked through setting up. One thing that you need to configure when you use this deployment approach is an open TCP port to the Virtual Machines to allow […]

via Secure your VSTS Release Management Azure VM deployments with NSGs and PowerShell — siliconvalve

 

Sheikvara

+919840688822, +919003270444

Resize a Windows VM in Azure

10/19/2016 2 min to read Contributors

This article shows you how to resize a Windows VM, created in the Resource Manager deployment model using Azure Powershell.

After you create a virtual machine (VM), you can scale the VM up or down by changing the VM size. In some cases, you must deallocate the VM first. This can happen if the new size is not available on the hardware cluster that is currently hosting the VM.

Resize a Windows VM not in an availability set

  1. List the VM sizes that are available on the hardware cluster where the VM is hosted.
    Copy
    PowerShell
     Get-AzureRmVMSize -ResourceGroupName <resourceGroupName> -VMName <vmName>
    
  2. If the desired size is listed, run the following commands to resize the VM. If the desired size is not listed, go on to step 3.
    Copy
    PowerShell
     $vm = Get-AzureRmVM -ResourceGroupName <resourceGroupName> -VMName <vmName>
     $vm.HardwareProfile.VmSize = "<newVMsize>"
     Update-AzureRmVM -VM $vm -ResourceGroupName <resourceGroupName>
    
  3. If the desired size is not listed, run the following commands to deallocate the VM, resize it, and restart the VM.
    Copy
    PowerShell
     $rgname = "<resourceGroupName>"
     $vmname = "<vmName>"
     Stop-AzureRmVM -ResourceGroupName $rgname -VMName $vmname -Force
     $vm = Get-AzureRmVM -ResourceGroupName $rgname -VMName $vmname
     $vm.HardwareProfile.VmSize = "<newVMSize>"
     Update-AzureRmVM -VM $vm -ResourceGroupName $rgname
     Start-AzureRmVM -ResourceGroupName $rgname -Name $vmname
    
Warning

Deallocating the VM releases any dynamic IP addresses assigned to the VM. The OS and data disks are not affected.

Resize a Windows VM in an availability set

If the new size for a VM in an availability set is not available on the hardware cluster currently hosting the VM, then all VMs in the availability set will need to be deallocated to resize the VM. You also might need to update the size of other VMs in the availability set after one VM has been resized. To resize a VM in an availability set, perform the following steps.

  1. List the VM sizes that are available on the hardware cluster where the VM is hosted.
    Copy
    PowerShell
     Get-AzureRmVMSize -ResourceGroupName <resourceGroupName> -VMName <vmName>
    
  2. If the desired size is listed, run the following commands to resize the VM. If it is not listed, go to step 3.
    Copy
    PowerShell
     $vm = Get-AzureRmVM -ResourceGroupName <resourceGroupName> -VMName <vmName>
     $vm.HardwareProfile.VmSize = "<newVmSize>"
     Update-AzureRmVM -VM $vm -ResourceGroupName <resourceGroupName>
    
  3. If the desired size is not listed, continue with the following steps to deallocate all VMs in the availability set, resize VMs, and restart them.
  4. Stop all VMs in the availability set.
    Copy
    PowerShell
    $rg = "<resourceGroupName>"
    $as = Get-AzureRmAvailabilitySet -ResourceGroupName $rg
    $vmIds = $as.VirtualMachinesReferences
    foreach ($vmId in $vmIDs){
      $string = $vmID.Id.Split("/")
      $vmName = $string[8]
      Stop-AzureRmVM -ResourceGroupName $rg -Name $vmName -Force
    }
    
  5. Resize and restart the VMs in the availability set.
    Copy
    PowerShell
    $rg = "<resourceGroupName>"
    $newSize = "<newVmSize>"
    $as = Get-AzureRmAvailabilitySet -ResourceGroupName $rg
    $vmIds = $as.VirtualMachinesReferences
    foreach ($vmId in $vmIDs){
      $string = $vmID.Id.Split("/")
      $vmName = $string[8]
      $vm = Get-AzureRmVM -ResourceGroupName $rg -Name $vmName
      $vm.HardwareProfile.VmSize = $newSize
      Update-AzureRmVM -ResourceGroupName $rg -VM $vm
      Start-AzureRmVM -ResourceGroupName $rg -Name $vmName
    }

     

Please find the reference article – https://docs.microsoft.com/en-us/azure/virtual-machines/virtual-machines-windows-resize-vm

Posted by Sheikvara

+919840688822, +919003270444

Azure – Configure a Point-To-Site Connection to a Vnet using Azure Portal

Awesome

Explore Azure and Powershell

A Point-To-Site(P2S) configuration lets you create a secure connection from an individual client computer to a virtual network. A P2S connection is useful when you want to connect to your VNET from a remote location, such as from home or a conference, or when you only have few clients that need to connect to virtual network.

P2S connections do not require a VPN device or a public-facing IP address to work. A VPN connection is established by starting the connection from client computer.

Below are the operating systems that we can use with Point-To-Site:

  • Windows 7 (32-bit and 64-bit)
  • Windows Server 2008 R2 (64-bit only)

  • Windows 8 (32-bit and 64-bit)

  • Windows 8.1 (32-bit and 64-bit)

  • Windows Server 2012 (64-bit only)

  • Windows Server 2012 R2 (64-bit only)

  • Windows 10

This blog walks you through creating a Vnet with Point-To-Site connection in the Resource Manager deployment model using the Azure Portal.

This…

View original post 2,354 more words

How to Build your Citrix Disaster Recovery environment in Microsoft Azure

March 7, 2017

One of the great Platform Services of Microsoft Azure is DRaaS, what stands for Disaster-Recovery-As-A-Service or also named as Recovery Services in the Azure ARM Portal. A simple, cost-effective and fast solution to build up a complete Recovery Site in the Azure Cloud, with no need to buy own hardware that needs to be available for stand-by! What makes old solutions very expensive, in operational costs. It also gives you the flexibility to simply start individual servers, in case of a single server or hypervisor outage. At this moment, there is no official Citrix solution to provide any out-of-the-box disaster recovery capabilities to the cloud, and because of the increased collaboration between Citrix and Microsoft, this is the solution that Citrix advise you to use these days, what makes this solution definitely one to remember!

Also one text I like to mention, last week Prasanna Padmanabhan from the Citrix Azure Engineering team, tweeted this following message, what exactly symbols the way of thinking about the Azure DRaaS solution;

With the ASR (Azure Site Recovery) service Microsoft offers a new way to secure replicate your on-premises VMware, Hyper-V and physical servers to Azure and back to your on-premises site over an SSL encrypted channel, with VMware support, without the need to uninstall the VMware tools first! The on-premises site needs to be prepared with a Process server installation, also called the Management server. This server will be proceeding the server synchronization to the Azure site. When you failover to azure, the Recovery Services – Recovery plan creates all the protected Virtual Machines groupwise in Azure for you.

Read the entire article here, How to Build your Citrix Disaster Recovery environment in Microsoft Azure | InfraShare.net

Thanks,

Sheikvara

+919840688822, +919003270444

SharePoint Server 2016 in Azure infrastructure services

Posted on March 6, 2017

Senior Content Developer, Microsoft Cloud Adoption

To take advantage of SharePoint’s collaboration features, Microsoft recommends SharePoint Online in Office 365. If that is not the best option for you right now, you should use SharePoint Server 2016. However, building a SharePoint Server 2016 farm in Microsoft Azure infrastructure services requires additional planning considerations and deployment steps.

For a defined path from evaluation to successful deployment, see SharePoint Server 2016 in Microsoft Azure. This new content set reduces the time it takes for you to design and deploy dev/test, staging, production, or disaster recovery SharePoint Server 2016 farms in Azure.

There are step-by-step instructions for two prescriptive dev/test environments:

1. A single-server farm running in Azure for demonstration, evaluation, or application testing.

2. An intranet farm running in Azure to experiment with client access and administration in a simulated Azure IaaS hybrid configuration.

When you are ready to begin planning the Azure environment for your SharePoint Server 2016 farm, see Designing a SharePoint Server 2016 farm in Azure. A table-based, step-by-step approach assures that you are collecting the right set of interrelated settings for the networking, storage, and compute elements of Azure infrastructure services.

When you are ready to deploy, see Deploying SharePoint Server 2016 with SQL Server AlwaysOn Availability Groups in Azure to build out this high availability configuration:

A table-based, phased approach assures that you are creating the Azure infrastructure with the correct settings, which you can adapt or expand for your business needs.

To assist you in creating the Azure infrastructure and configuring the servers of the high availability SharePoint Server 2016 farm, use theSharePoint Server 2016 High Availability Farm in Azure Deployment Kit, a ZIP file in the TechNet Gallery that contains:

  • Microsoft Visio and Microsoft PowerPoint files with the figures for the two dev/test environments and the high-availability deployment

  • All the PowerShell command blocks to create and configure the high availability SharePoint Server 2016 farm in Azure

  • A Microsoft Excel configuration workbook that generates the PowerShell commands to create the SharePoint Server 2016 high availability farm in Azure, based on your custom settings

Thanks,

Sheikvara

+919840688822,+919003270444