TechNet on Tour – Increasing efficiency and scalability with Windows Server 2016 and Microsoft cloud
Want to learn how to evolve your datacenter into a true hybrid cloud model to achieve greater efficiency, flexibility and scalability? If you are around Redmond, WA – sign up for free one-day training event – https://www.microsoftevents.com/profile/form/index.cfm?PKformID=0x111759537c
This training will cover:
- Confidently virtualize your mission critical workloads on premises with Hyper-V, and in the cloud with Microsoft Azure
- Implement new software-defined storage and network architectures to increase storage performance, flexibility and redundancy
- Get the smallest footprint, reduced patching, and most cloud-optimized OS performance available, with Nano Server
- Extend datacenter resources by using virtual networking to bridge the gap between your on-premises infrastructure and Microsoft Azure
- Explore single-click deployment of complex, multi-tier apps across VMs, storage, and networking with the new Azure Resource Manager
I will be delivering one of the sessions. See you there at the venue. J
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
Last two weeks, I have travelled to Sydney, Australia & Kuala Lumpur, Malaysia to provide training to service providers and system integrators in the area, to deliver in-depth hands on training to help them with architecting, designing and implementing SQL cloud solutions in a private and hybrid cloud environment. Training covered the following topics:
- Introduction to SQL 2014 features & capabilities
- Contained databases for multi-tenant database offerings
- Resource Governor for controlling CPU, Memory and Disk IO in a multi-tenant environment
- Advanced Backup & Restore with SQL 2014 (on-premise and cloud)
- Introduction to High Availability & Disaster Recovery
- SQL High Availability with Failover Cluster Instance
- SQL High Availability with AlwaysOn Availability Groups
- Disaster Recovery with Azure as the DR environment
- Establishing hybrid connectivity (VPN) to Azure
- SQL Always On Availability Groups DR setup with Azure
- Enabling SQL service offerings to customers using Windows Azure Pack
- Dev/Test environment setup on Azure
- System Center Operations Manager
Feel free to reach out to me, if you have any follow up questions or need some help. J
WAP administrators can use the functionality of SQL Server 2014 Resource Governor with UR5 of Windows Azure Pack. You can read about ‘Manage tenant database workloads with resource governor in WAPack’ here. In this blog post I am detailing the last section ‘Resource Governor at Work’ of the above blog post.
Tools available to generate load on SQL server
Though we can write custom tools (C#, PowerShell) etc. to create test databases and generate load, sometimes publically available tools will come handy. Providing two tools that I have come across.
- Hammer DB (I have used this for various load generation, benchmark verification before/after changing server configuration etc.)
- SQL Load Generator
View CPU Usage for Resource Pools
In this post, I assume that you have followed the instructions provided in the above blog post. In my configuration, I have created the suggested resource pools and created a database named ‘contoso’. I am providing the steps to identify the resource pool name and how to view the performance counters for the resource pool.
Step 1: Identify the resource pool associated with the database
As a service provider administrator, navigate to ‘servers‘ tab in ‘SQL Servers‘ resource provider and navigate to the details of specific server.
Navigate to the ‘databases‘ tab and find out the ‘resource pool name’.
Step 2: Open performance monitor
You can do this by running ‘perfmon’ on windows run prompt.
- Navigate to the performance monitor view
- Select any existing counters
- Click on ‘Delete’ to remove the existing counters
- Click on ‘+’ button next to add new counters (in the following step)
Step 3: Add SQL Resource Governor Performance Counter
Choose counter ‘CPU usage %‘ in ‘SQL Server: Resource Pool Stats‘
Note: You can see that all the resource pools you saw along with databases tab in windows azure pack + two system resource pool instances (default & internal) are listed in the instances section
- Now you can select all instances and click on ‘Add‘ button.
Step 4: Generate load against the databases and watch usage in performance monitor
Part 1: In the part one of this series, we have gone through the step by step instructions of installing and configuring Windows Azure Pack portal and api express on an Azure virtual machine. You can read through the part one of this series here.
Part 2: In the part two of this series, we have gone through the step by step instruction of creating group and server in SQL server resource provider, followed by creating plan and add-on based for providing a dedicated offer. Also, we created a tenant account. You can read through the part two here.
In this post, I am providing the PowerShell script that can be used to assign the private plan and add-on to the tenant. It is possible to assign private plan to tenant from admin portal. However assigning a private add-on to tenant is not enabled from admin portal. So we need to leverage PowerShell script to assign the plan and add-on to tenant.
Step 7: Assign private plan and add-on to tenant by service provider admin
# Assign all variable values specific to your environment $windowsAuthSiteUri = "https://localhost:30072" $adminUri = "https://localhost:30004" $planName = "Dedicated Plan 001" $addonName = "AddOn for Dedicated Plan 001" $userName = "firstname.lastname@example.org" # # Get Token $token = Get-MgmtSvcToken ` -Type Windows ` -AuthenticationSite $windowsAuthSiteUri ` -DisableCertificateValidation ` -ClientRealm "http://azureservices/AdminSite" # # Get plan, add-on and user objects $plan = Get-MgmtSvcPlan ` -AdminUri $adminUri ` -Token $Token ` -DisableCertificateValidation ` -DisplayName $planName $addon = Get-MgmtSvcAddOn ` -AdminUri $adminUri ` -Token $Token ` -DisableCertificateValidation ` -DisplayName $addonName # Create a new subscription for the user against the dedicated plan $subscription = Add-MgmtSvcSubscription ` -AdminUri $adminUri ` -Token $token ` -AccountAdminLiveEmailId $userName ` -AccountAdminLivePuid $userName ` -PlanId $plan.Id ` -FriendlyName $planName ` -DisableCertificateValidation
In the last part of this series, I will walk through the tenant experience of creating databases leveraging the dedicated plan/add-on.
In the part one of this series, we have gone through the step by step instructions of installing and configuring Windows Azure Pack portal and api express on an Azure virtual machine. You can read through the part one of this series here.
In this post, we will go through creating plan and add-on that will enable us to assign a single server to a single subscription. This is the high level approach.
- Add a new group for the dedicated server. Only this dedicated server will be available in this group.
- Add a new SQL server and assign it to the group we created above. Let us assume that this server can support up-to 10GB of data files. And we want to provide 1GB as the minimum database size. With this configuration, tenant can create one 10GB database or ten 1GB databases. Tenant can create a database and can increase the size of database, based on remaining space available on the server (made available through add-on).
- Now create a new plan. During plan quota configuration, add the group created about. Also, keep the base size as 1GB and allow add-on size of up-to 9GB.
- Create a new add-on and allow tenants to extend capacity by up-to 9GB. Link this plan & add-on.
- Remember, plan and add-on are kept private, so that users can’t sign up for this plan. In the next blog post, I will walk through how to assign the plan & add-on to the user (even though it is private).
Step 5: Create Group, Server, Plan and Add-On
Step 6: Create a new tenant (for test purpose)
You can read through the third part in this series here. This part has the PowerShell script that can be used to assign the private plan and add-on to the tenant.
Windows Azure Pack comes with SQL resource provider, which enables service providers to offer shared database services for both standalone (no HA) and highly available databases. This blog is the first in the series where I explain step-by step on how a dedicated SQL offer (this means that a single database server is reserved for a single subscription) leveraging the plans / add-on features. Each instruction / step is shown with the corresponding screen capture. Texts are provided with the screen capture, only if something needs to be called out specifically.
Like always, while testing this out, I have used a virtual machine from Azure. If you are trying this on an on-premise machine, skip the steps accordingly.
This blog covers the first four steps
- Create and remote into SQL 2014 virtual machine on Azure
- Install Windows Server Roles & Features
- Enable SQL Authentication and Reset ‘sa’ password
- Install and Configure Windows Azure Pack Portal Express
You can continue reading the second part of this series here.
Step 1: Create and remote into SQL 2014 virtual machine on Azure
Step 2: Install Windows Server Roles & Features
Enable the required roles and features for Windows Azure Pack. I have created another blog on how to easily do this using PowerShell. You can follow the steps from here. Note that this step is optional. If you do not do this, Windows Azure Pack Portal Express deployment will do it for you.
Step 3: Enable SQL Authentication and Reset ‘sa’ password
By default, SQL virtual machines on Azure come with Windows Authentication. You need to explicitly enable SQL authentication. Note that you need to restart SQL Server service, after enabling SQL authentication.
Step 4: Install and Configure Windows Azure Pack Portal Express
You can continue reading the second part of this series here.
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