With first release candidate for SQL Server 2016 is available – https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/07/first-release-candidate-of-sql-server-2016-now-available/ – , how best and fast can we try it out? With the image for SQL Server 2016 RC0 available in Azure Gallery, it is easier to get started with it on an Azure Virtual Machine. You can search for SQL 2016 images in the gallery and create virtual machine from there.
Today, we get multiple versions and editions – including Express – of SQL Server from Azure Image Gallery.
- SQL Server 2016 RC0 Evaluation on Windows Server 2012 R2
- SQL Server 2016 CTP3.3 Evaluation on Windows Server 2012 R2
- SQL Server 2016 CTP3 Evaluation on Windows Server 2012 R2
- SQL Server 2014 Web on Windows Server 2012 R2
- SQL Server 2014 Standard on Windows Server 2012 R2
- SQL Server 2014 SP1 Web on Windows Server 2012 R2
- SQL Server 2014 SP1 Standard on Windows Server 2012 R2
- SQL Server 2014 SP1 Express on Windows Server 2012 R2
- SQL Server 2014 SP1 Enterprise on Windows Server 2012 R2
- SQL Server 2014 Enterprise on Windows Server 2012 R2
- SQL Server 2012 SP2 Web on Windows Server 2012 R2
- SQL Server 2012 SP2 Web on Windows Server 2012
- SQL Server 2012 SP2 Standard on Windows Server 2012 R2
- SQL Server 2012 SP2 Standard on Windows Server 2012
- SQL Server 2012 SP2 Express on Windows Server 2012
- SQL Server 2012 SP2 Enterprise on Windows Server 2012 R2
- SQL Server 2012 SP2 Enterprise on Windows Server 2012
- SQL Server 2008 R2 SP3 Web on Windows Server 2008 R2
- SQL Server 2008 R2 SP3 Standard on Windows Server 2008 R2
- SQL Server 2008 R2 SP3 Express on Windows Server 2008 R2
- SQL Server 2008 R2 SP3 Enterprise on Windows Server 2008 R2
I found this article good – https://www.linkedin.com/pulse/ten-ways-sql-server-2016-change-way-we-do-things-kevin-chant?trk=hp-feed-article-title-like. Get started with SQL Server 2016 on Azure Virtual Machine, and also do follow various learning material made available at https://www.microsoft.com/en-us/server-cloud/data-driven.aspx
I was doing a webinar last week about running SQL workloads on Azure Virtual Machine. An interesting topic of discussion was yes, when we think of virtual machine, it is purely Infrastructure as a Service (IaaS). However, when we think of running SQL Server on Azure Virtual Machine, we get a lot more capabilities / features that we would typically do not expect on IaaS offering.
We can enable automated patching, both Windows & SQL Server patches, to be applied on the virtual machine once every day or once every week. We can also provide the patching time window for Azure to apply the patches.
We can configure automated backup of SQL Server 2014 Enterprise and Standard virtual machines, to retain the backups for up to 30 days. We can optionally configure the backups to be encrypted.
Last two weeks, I have travelled to Bangalore, India to provide trainings to Architects from various global system integrators on Microsoft Azure technology. During this travel, I have delivered sessions / workshops on the following:
Architecting Big Data & Analytics Solutions: The proliferation of customer data and the desire for insights not previously attainable has created a new industry focused on analyzing massive amounts of data. The cost of processing huge amounts of data does not need to be prohibitive when you can take advantage of a scalable cloud platform. We will discuss the Lambda architecture to take advantage of both batch and stream processing of data and will show various Azure services such as Azure Data Lake, Azure Data Factory, Azure Stream Analytics, Azure Machine Learning, and Power BI to provide a solution to unlock insights into data.
Architecting Modern Cloud Applications: Modern cloud applications offer end-user experiences and features that transcend traditional on-premises applications. For example, there are often multiple database technologies supporting today’s modern app. Embrace the notion of polyglot persistence and see how RDBMS’s, NoSQL databases, and caches can be combined to deliver robust end-user experiences. Modern cloud apps today are also expected to be reachable regardless of the platform or device. See how Azure’s App Service can be used to expand the reach of your solutions. Modern cloud apps are also not always just platform-as-a-service solutions. Learn about modern architecture patterns enabled by Azure that span infrastructure-as-a-service and platform-as-a-service deployments.
Architecting Global Scale Web and Mobile Solutions: One of the main benefits of the Microsoft Azure platform is its massive scale. Microsoft Azure runs on a massive network of over 19 regions worldwide. This global reach makes it possible to create solutions on a global scale that meet your demanding performance requirements. We will discuss architectural patterns that lend themselves to global geo-located solutions and the specific features of Microsoft Azure that enable geo-replicated data.
Dev Ops: Enterprise IT organizations are increasingly driven by business demands for faster, better solutions delivered more quickly than ever before. DevOps has emerged as a trend to help organizations evolve to better collaborate between development and operations teams for addressing these solution delivery challenges. While a DevOps transformation can take time to fully realize within an organization’s people and processes, there’s opportunities to accelerate the path towards DevOps by smartly leveraging cloud capabilities that can reduce infrastructure complexity, simplify release management, and support end-to-end application visibility through all stages of a solution’s lifecycle. This session will discuss the features in Microsoft Azure that help to realize this transformation and how the platform enables integration with existing assets.
- Case Study – Internet of Things (IoT)
- Case Study – Refactoring Multi-Tier Solutions
- Case Study – Lift and Shift
- Case Study – Identity and Access Management
- Case Study – Networking
If you would like to use SSH with Linux virtual machines on Azure, there is a great documentation here. One question I have seen in the forum, which is not clearly explained in the documentation is, how do we add a new SSH authorization key, if we missed adding the SSH file, during the creation of virtual machine.
Giving the steps I have followed to setup a new SSH authorization key below:
Step 1: I created a Linux VM (Ubuntu 14) on Azure using quick create VM (linuxvmforssh.cloudapp.net).
Step 2: Executed following commands from Git shell on my Windows 8.1 laptop
GIT> ssh-keygen -t rsa -b 2048 -v -f azurecert.key GIT> scp azurecert.key.pub email@example.com:/home/azureuser GIT> ssh firstname.lastname@example.org azureuser@linuxvmforssh:~$ cat azurecert.key.pub >> .ssh/authorized_keys azureuser@linuxvmforssh:~$ sudo service ssh restart azureuser@linuxvmforssh:~$ exit GIT> ssh -i azurecert.key email@example.com
Hope this helps you to setup new SSH authorized keys for your Linux virtual machines.
Though Microsoft Azure provides virtual machine images with SQL server installed, during development and testing, I may not need those optimizations. All I might need is a virtual machine with database engine installed. Also, from a cost standpoint, using an evaluation version of SQL server on a virtual machine might be preferred over a SQL VM image, for development and testing purposes. Providing how I prepare my SQL experiment virtual machine (most of the time, I use the machine for a week or two and once the experiment is complete, I delete the virtual machine).
Azure Virtual Machine Images with SQL
Build a Virtual Machine with SQL Database Engine
Step 1: Create a new ‘Windows Server 2012 R2 Datacenter’ virtual machine with required configuration. Once virtual machine is up and running continue with next steps.
Step 2: SQL Server requires .NET 3.5 to be installed on server. This is not installed by default on Azure virtual machines.
Install-WindowsFeature -Name NET-Framework-Core Install-WindowsFeature -Name PowerShell-V2
Step 3: Download SQL server installer from an Azure storage account. You should run the following commands from Microsoft Azure PowerShell window.
CD C:\ $context = New-AzureStorageContext ` -StorageAccountName '<todo>' ` -StorageAccountKey '<todo>' Get-AzureStorageBlobContent -Container '<todo>' ` -Context $context ` -Blob 'SQLServer2014-x64-ENU.iso'
Step 4: Mount downloaded ISO image to a drive
$sqlIso = "C:\SQLServer2014-x64-ENU.iso" $sqlDrive = (Mount-DiskImage -ImagePath $sqlIso ` -PassThru ` -WarningAction Ignore | Get-Volume).DriveLetter + ":"
Step 5: Silently install SQL Server from command line. For more details around command line based install check product documentation.
$domainName = Get-Content env:USERDOMAIN $userName = Get-Content env:USERNAME $adminUser = $domainName + "\" + $userName $saPassword = "Secret@2015" $installCommand = "$sqlDrive\Setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /UpdateEnabled=0 /FEATURES=SQLENGINE,CONN,SSMS,ADV_SSMS /INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SAPWD=$saPassword /SQLSVCACCOUNT='NT Service\MSSQLSERVER' /SQLSYSADMINACCOUNTS=$adminUser" Invoke-Expression $installCommand
Step 6: Once installation is complete, open SQL Management Studio and test with both Windows as well as SQL authentication.
Upload SQL installer to Storage
You can use the following script to upload SQL installer to blob storage for future use.
$context = New-AzureStorageContext ` -StorageAccountName '<todo>' ` -StorageAccountKey '<todo>' Set-AzureStorageBlobContent ` -Blob "SQLServer2014-x64-ENU.iso" ` -Container '<todo>' ` -File "SQLServer2014-x64-ENU.iso" ` -Context $context ` -Force
I will be writing a series of blogs around Microsoft Azure & Hybrid Cloud. As a quick start, wanted to write up on how to setup our machines with PowerShell to work with Microsoft Azure.
Setup machine with Microsoft Azure PowerShell
- If you haven’t installed Web Platform Installer, you can download and install it from http://www.microsoft.com/web/downloads/platform.aspx
- Download and install ‘Microsoft Azure PowerShell’ from Web Platform Installer. If you do not find that in ‘Spotlight’ you can perform a search for ‘Microsoft Azure PowerShell’
Set your default subscription and storage account
Download Azure Publish Settings file , configure default subscription and configure default storage account. There is a very good blog article from Michael Walsham explaining this step-by-step. I strongly recommend you read through that post. There is one minor change in Azure PowerShell cmdlet usage (based on a cmdlet update from Microsoft), from the post you see in the blog. I have written a PowerShell script with minor updates, which you can download from here.