Monitor SQL Server enabled by Azure Arc 

Simplifying deployment and cost management in multi-cloud environments.

Learn more

The traditional method of paying for SQL Server is buying license and software assurance. Now, you can connect your SQL Server to Azure Arc and pay only for the hours when your VM with SQL Server is online and your SQL instance is active. For the pricing information, see SQL Server 2022—Pricing | Microsoft.

While SQL Server 2022 integrates a pay-as-you-go billing option in the setup, the older versions require a product key to install any production edition. This blog provides instructions on how to install a pay-as-you-go image of any SQL Server version starting from SQL Server 2012 without purchasing a license and providing a product key.

NOTE: if you have access to a SQL Server image that does not require a product key, for example from a hosting provider, you should install it, follow the documented Azure Arc onboarding process for SQL Server and then set the License type to pay-as-you-go in SQL Server configuration.

The process in a nutshell

The deployment of a pay-as-you-go image on a virtual or physical machine running in any cloud, edge, or on-premises datacenters consists of three steps:

1. Installing the prerequisites on the target machines
2. Creating a support ticket with Microsoft Azure to request and download a pay-as-you-go image
3. Running the installation script

Prerequisites

Make sure your target machine meets the following requirements.

On target VMs:

• Windows Server instance is running, and you have a local administrator account.
• Azure PowerShell is installed and updated.
• For SQL Server version 2014, you will need to install .NET. For more information on this prerequisite, visit here.
• You have a remote desktop connection to connect to Windows Server as a local administrator.
• You copied the script from here to a local folder. Follow the download instructions in the readme file.
• Important: If your target VM runs Windows Server 2016, you must make sure that your Transport Layer Security (TLS) configuration is compatible with Azure. Follow the instructions in the readme file on how to mitigate the TLS version issue on Windows Server 2016.

On Microsoft Azure:

• You have an Azure account, subscription, and a target resource group.

Opening a support ticket

You can open the support ticket using a Support + Troubleshooting entry from the subscription which the newly installed SQL Server instance will be connected to. The following screenshots illustrate the flow. Make sure to use the answers as highlighted in the screenshots.

After you complete the support ticket creation process, Azure support will email you a private link to a workspace with the zip file you need to download.

Download the file to the VM where you want to install SQL Server, and unzip it to the same folder that contains the installation script.

Note: For SQL Server 2012 and SQL Server 2022 images, the keys are not required, and the .zip file will contain only the ISO file. For other SQL Server versions, the .zip file includes a product key and the ISO file. The script will recognize these differences.

Running the script

Follow the instructions in the readme file on how to launch the script. An Azure sign-in screen will prompt you to enter your Azure credentials or identify the account you’re already signed into. After this, the script proceeds unattended to carry out the following steps:

• Installing Azure PowerShell modules if they’re not already installed
• Logging into Azure with your assistance
• Onboarding the VM to Azure Arc
• Installing SQL Server on the Windows Server from the file you identified in the previous step
• Mounting the ISO file as a volume
• Installing SQL Arc Extension with a pay-as-you-go license type
• Displaying the status of the Azure resource for the connected SQL Server Instance

Disclaimer: The script has been tested on several combinations of VM products available on AWS and Google Cloud Platform with different versions of Windows Server and SQL Server, but it is not specific to these platforms, and you are welcome to try it on other clouds.

Supercharge SQL Server with Azure Arc

You can use this process to streamline the installation of pay-as-you-go images of SQL Server versions and editions of your choice to machines across different clouds, on-premises datacenters, and edge. By doing so, you can maintain control over the SQL Server compliance and optimize the cost based on the resource’s utilization.

The post Save money on Microsoft SQL Server licensing with Microsoft Azure Arc appeared first on Microsoft SQL Server Blog.

Stay secure and supported on-premises or in Azure

There are four options to keep your SQL Server 2012 secure and supported:

• Migrate to Azure Virtual Machines and run securely with up to three years of free Extended Security Updates and continued access to product support through your existing support contract. Moving SQL Server 2012 to Azure can have the lowest total cost of ownership—as much as 69 percent less than purchasing Extended Security Updates on-premises. It’s also easy to migrate because Azure Virtual Machines run the exact same SQL Server you have in your on-premises environment. In just three weeks, Allscripts lifted and shifted dozens of acquired applications running on 1,000 virtual machines to Azure. The free Extended Security Updates offer extends to other destinations in Azure including Azure Dedicated Host, Azure VMWare Solution, and hybrid destinations such as Azure Stack HCI. Microsoft is also providing one additional year of extended security updates for Windows Server and SQL Server 2008 and 2008 R2, only in Azure.
• Modernize your SQL Server in the cloud with Azure SQL Managed Instance, which combines the broadest SQL Server engine compatibility with all the benefits of a fully managed and always up-to-date database service. SQL Managed Instance helps you quickly and confidently modernize your custom and vendor-provided apps to Azure so you can further unlock the benefits of Azure’s integrated service platform. Customers like H&R Block find that SQL Managed Instance helps them scale up or down to meet changing demands in a cost-optimized way.
• Upgrade to SQL Server 2019, which allows you to benefit from industry-leading security and performance, the ability to break down data silos, added business continuity scenarios, and deployment flexibility.
• Sign up for Extended Security Updates for your on-premises SQL Server via Azure Arc. Just like moving to Azure, purchasing Extended Security Updates for use in your on-premises environment gives you continued access to product support through your existing support contract. There are two ways to use Azure Arc to receive Extended Security Updates:
  • Connected: Install the Azure Connected Machine agent with direct connectivity to Azure. You’ll benefit from the features that Azure Arc-enabled servers provide, including ease of enrollment and deployment of Extended Security Updates.
  • Registered: Manually add your instance in a disconnected state and manage all your SQL Server from a single pane of glass. The management experience is consistent with how you manage native Azure Virtual Machines. Learn more about Extended Security Updates for SQL Server.

Plan for upcoming Windows Server 2012/R2 end of support

As you plan to protect SQL Server 2012 workloads, you should also plan to do the same for Windows Server 2012 and 2012 R2 as they reach end of support next year on October 10, 2023. You can continue to protect these workloads by upgrading to Windows Server 2022 or migrating to the cloud.

With Azure, you can focus on modernization, knowing your Windows Server 2012/R2 legacy workloads are seamlessly secured with free Extended Security Updates. Take advantage of unique capabilities for Windows Server in Azure Virtual Machines or hybrid management through Azure Arc. Solutions such as Azure Automanage simplify management of your Windows Server Virtual Machines, while you can accelerate application modernization with PaaS solutions such as Azure App Service and Azure Kubernetes Service.

For customers that need to run business-critical applications on-premises, you can continue to protect your workloads by upgrading to Windows Server 2022 with the latest innovation in security, hybrid capabilities, and application development.

Start your journey with help from Microsoft

Microsoft is here to help you get started. We recently announced an expansion to the Azure Migration and Modernization Program, which has helped thousands of customers unlock the value of the cloud. We’re now investing significantly more to support your largest Windows Server and SQL Server migration and modernization projects, up to 2.5 times more than previous investments, based on project eligibility. This investment will help with your migration in two ways: partner assistance with planning and moving your workloads, and Azure credits that offset transition costs during your move to Azure Virtual Machines, Azure SQL Managed Instance, and Azure SQL Database.

Learn more about end of support and migration resources:

• See your options for end of support at the SQL Server 2012 end of support page.
• Learn more about Extended Security Updates for SQL Server from our documentation.
• Sign up for the Azure Migration and Modernization Program to get assistance from Microsoft to accelerate your cloud migration.
• Use the Azure Migrate tool to migrate your SQL Server workloads to Azure.

The post SQL Server 2012 reaches end of support and Microsoft is here to help appeared first on Microsoft SQL Server Blog.

Stay protected on-premises or in Azure 

If you need more time to upgrade or modernize, you can still run your workloads in the existing infrastructure. However, to protect databases after the end of support deadline, you will have to purchase three more years of Extended Security Updates. The cost of on-premises Extended Security Updates is 75 percent of the price of a SQL Server license in year one, 100 percent of the price of a SQL Server license in year two, and 125 percent of the price of a SQL Server license in year three.

If you choose to modernize on-premises, upgrading to SQL Server 2019 allows you to benefit from industry-leading security and performance, the ability to break down data silos, added business continuity scenarios, and deployment flexibility. You can get access to the latest version of SQL Server through the New Version Rights benefit of Software Assurance, or by purchasing new SQL Server licenses.

If you decide to move SQL Server to Azure Virtual Machines, you will gain access to free Extended Security Updates for up to three years after SQL Server 2012 end of support. We continue to innovate to make Azure Virtual Machines the best destination for your SQL Server by adding to our hardware options and suite of free manageability capabilities enabled by SQL Server IaaS Agent extension. Registering with SQL Server IaaS Agent extension also allows you to configure a maintenance window and turn on optional automated patching.

The free Extended Security Updates offer also extends to other destinations in Azure including Azure Dedicated Host, Azure VMWare Solution, and hybrid destinations such as Azure Stack HCI.

If you’re ready to not only move to Azure but to modernize to a managed database, Azure SQL Managed Instance is a fully managed service with evergreen SQL that never needs to be patched or upgraded. With SQL Managed Instance, you can confidently modernize your existing apps at scale by combining your experience with familiar tools, skills, and resources and do more with what you already have.

Customers like Forever 21 made the decision to migrate end-of-life SQL Server workloads to Azure, moving from on-premises to Windows Server, and SQL Server to Azure Virtual Machines. This allowed Forever 21 the flexibility to scale resources at will. To make the deal even better, Forever 21 used Azure Hybrid Benefit which lowered their total cost of ownership by allowing use of existing on-premises Windows Server and SQL Server licenses in Azure. This resulted in a 20 percent annual saving on software licenses, and server and network infrastructure, amounting to $250,000.

“With Azure, we were able to cope with more sessions and more online customers than we’d ever seen—by almost 50 percent. Ultimately, by scaling our presence in Azure, we got through our biggest holiday season ever without a hitch.”—Kevin Diamond, Head of Global E-Commerce, Forever 21

Reaping the benefits of moving to Azure

You may feel that your current SQL Server infrastructure is fully depreciated and staying on it is low- or no-cost. Or you may be concerned about migration costs. By moving to Azure, not only do you save with free Extended Security Updates, but you can also save with Azure Hybrid Benefit for Windows Server and SQL Server, allowing you to reuse existing licenses. Migrating your application to Azure enables you to strengthen your information security, while consistently meeting compliance regulations.  

Example one: Purchasing Extended Security Updates versus free updates in Azure  

When it comes to deciding to upgrade, migrate, or stay in place, the numbers year after year tell the entire story. Running your end-of-life SQL Server on Azure Virtual Machines allows you to both strengthen your security and save. Here’s how:   

• Receive SQL Server 2012 Extended Security Updates for up to three additional years.   
• Receive critical security patches for an additional year on SQL Server 2008 and 2008 R2 (in Azure only).
• Gain access to continued product support through existing technical support contracts.

In the comparison below, we looked at the price to migrate sixteen cores of SQL Server 2012 to Azure virtual machines including eight cores of Standard edition and eight cores of Enterprise edition. A customer who did this would save $30,673 in the first year by purchasing Extended Security Updates on-premises.¹ This represents a 69 percent savings over staying in your datacenter.

Example two: Free Extended Security Updates in Azure versus moving to other clouds  

In Azure, customers running SQL Server 2012 save significantly compared to AWS. On top of three years of free Extended Security Update patching, customers can save money by re-using SQL Server and Windows Server licenses in the cloud through Azure Hybrid Benefit

In the example below, we evaluated moving sixteen cores of SQL Server to Azure VMs using Azure Hybrid Benefit for Windows Server and SQL Server against moving sixteen cores of Azure to AWS EC2 using SQL Server License Mobility. Compared to staying on-premises or moving to AWS, the Azure customer also benefitted from free Extended Security Updates. 

In this example, the customer can obtain the best total cost of ownership by moving to Azure VMs–saving $46,910 over purchasing Extended Security Updates and running on AWS.¹

Get ready 

Make moving SQL Server 2012 to Azure before the July 12, 2022 end of support deadline part of your plan. Windows Server 2012/2012 R2 end of support is coming up on October 10, 2023, so planning for your operating system is also critical. When you migrate SQL Server 2012 and Windows Server 2012/2012 R2 to Azure Virtual Machines you will get three more years of Extended Security Updates for free! This not only buys you more time to work on upgrading or modernizing your applications in the cloud, but it also lowers your costs.

Visit the following pages to get started on your journey today: 

• Azure migration and modernization center resources for every stage of your cloud migration.
• SQL Server and Windows Server End of Support resource page. 
• Product lifecycle FAQ—Extended Security Updates documentation.

References

¹Azure pricing is based on the East US 2 region, AWS pricing on the US East (Ohio) region. Actual savings may vary based on region, instance size, and compute family. Prices as of January 20, 2022, are subject to change. The below example also uses Azure Hybrid benefit for Windows Server and SQL Server in Azure and license mobility for SQL Server in AWS.

The post Move end-of-support SQL Server 2012 to Azure Virtual Machines and save appeared first on Microsoft SQL Server Blog.

Azure offers unique benefits that no other cloud provider can match including dual-use rights, Azure Hybrid Benefit, and unlimited virtualization, which lets you license the physical cores you have on-prem and those licenses will cover all the vCPUs on the host until the host runs out of resources. All of these benefits help you migrate to the cloud as cost-effectively as possible. We’ll go into more detail about each Azure-only benefit in this blog.

This blog will take you on a deep dive into moving SQL Server licenses to Azure Dedicated Host, its infrastructure benefits, and why it is the easiest way to bring your existing on-prem licenses to the cloud.

What is Azure Dedicated Host?

Azure Dedicated Host is an Azure service that provides you with a physical server, which you can host one or more virtual machines (VMs) on. Through physical, host-level isolation, you are the only tenant on the host; the server is dedicated to your organization and your workloads. With Azure Dedicated Host, you gain direct visibility to and control over all the Azure resources on the host. Essentially, Azure Dedicated Host allows you to create your own private cloud in Azure.

What are the benefits of Azure Dedicated Host?

Infrastructure benefits

With hardware isolation at the physical server level, Azure Dedicated Host provides servers solely for your subscription. You are the only one using the capacity and resources on the host. Physical isolation may be required by your company or industry, or integral to ensuring your workloads are able to perform as quickly as they should.

In addition to the physical isolation of the host and awareness of all Azure resources on the host, Azure Dedicated Host allows you to choose the combination of the number of processors, VM series, VM sizes, and the type of processor you use. Azure has a wide offering of various combinations of processor type and VM series. A full list of the available Dedicated Host SKUs, the combination of a VM series and processor type in a given region can be found on the Dedicated Host pricing page.

Azure Dedicated Host also gives you direct control over the maintenance on the host. Instead of adhering to Azure’s maintenance schedule, Maintenance Control allows you to delay platform updates and apply them during a 35-day window.

Licensing benefits

In addition to the infrastructure benefits, Azure Dedicated Host provides substantial financial benefits for those who already have on-premises SQL Server enterprise edition licenses, whether the license was purchased with or without Software Assurance. Software Assurance allows you to maximize your discounts and can unlock more benefits like Azure Hybrid Benefit, unlimited virtualization, and dual-use rights, which are not available for the license only. Microsoft highly recommends purchasing Software Assurance for your migration to Azure.

*Azure Hybrid Benefit allows you to bring your on-premises SQL Server licenses with Software Assurance to Azure at no additional cost.

Software application licenses can be applied at one of two places: the host or the VM. By applying the licenses at the host level, rather than the VM level, your license will cover all the physical cores rather than the virtual cores. With more virtual cores than physical cores on a host, this allows you to stretch your existing licenses further. For example, instead of licensing all 80 virtual cores on the Esv4-Type1 Dedicated Host, you can instead save 28 core licenses by licensing the 52 physical cores on the host. In doing so, you can license up to 80 virtual cores on the host while only applying the licenses on 52 virtual cores. This is only available on Azure.

The practice of licensing the physical cores to minimize your overall licensing cost is called unlimited virtualization. With SQL Enterprise Edition, Software Assurance, and unlimited virtualization, customers can apply the license to the physical cores, at the host level, and create as many VMs as the host allows. All VMs created on that host are then covered by unlimited virtualization and are covered by the host-level licenses. Unlimited virtualization is unique to Azure.

How you go about achieving unlimited virtualization depends on the license type. For SQL Server 2008, you can achieve unlimited virtualization at no additional cost by applying the licenses at the host level. For those with SQL Server 2008 R2 and later, by purchasing Software Assurance, you can unlock unlimited virtualization on your Dedicated Host.

Azure allows for dual-use rights while migrating your on-prem workloads to the cloud through Azure Hybrid Benefit. You can migrate to the cloud and simultaneously use your existing on-prem SQL Server licenses in Azure for up to 180 days. This allows you to avoid buying more licenses to cover the migration period and furthers the use of your existing licenses. You must have Software Assurance in order to use dual-use rights while migrating.

To better understand how much you can save through Azure Dedicated Host, Software Assurance, and unlimited virtualization, please check out the Azure pricing calculator.

Extended Security Update benefits

Customers who migrate their SQL Server 2012 licenses to Azure will receive free Extended Security Updates, only in Azure. Extended Security Updates are critical security patches for legacy services that need to be run past the end of support date. After the July 12, 2022 end of SQL Server 2008 and 2008 R2 Extended Security Updates on-premises, you can migrate to Azure and take advantage of one year of additional SQL Server 2008 extended security updates, only in Azure, for free. Extended security updates for both SQL Server 2008 and SQL Server 2012 are free only in Azure, making Azure the prime choice for SQL workloads running on either version.

Moving to the cloud doesn’t have to be challenging. There are proven ways to get to Azure that drive business impact. Through Azure Dedicated Host, you can bring your existing licenses to Azure. By purchasing Software Assurance, you maximize your licensing discounts and unlock more benefits, such as Azure Hybrid Benefit, unlimited virtualization, and dual-use rights.

Learn more about our product and pricing offers for SQL Server migration:

• Azure Dedicated Host
• Azure Hybrid Benefit
• Extended Security Updates

Get started with your migration to Azure using Azure Migrate.

The post Move SQL Server licenses without Software Assurance to Azure appeared first on Microsoft SQL Server Blog.

SQL Server 2012 Service SP4 Express contains a roll-up of released hotfixes as well as more than twenty improvements centered around performance, scalability, and diagnostics based on the feedback from customers and SQL community. These improvements enable SQL Server 2012 to perform faster and scale out of the box on modern hardware design. It also showcases the SQL Server product team’s commitment to providing continued value to in-market releases.

SQL Server 2012 Service Pack 4 will include:

• All fixes and Cumulative Updates (CUs) for SQL Server 2012 up to and including SQL Server 2012 SP3 CU10.
• Scalability and performance improvements for SQL Server.
• Additional monitoring capabilities through enhancements in DMV, Extended Events and Query Plans and the ability to clone the database including statistics with DBCC CLONEDATABASE.
• New improvements based on Connect feedback items filed by the SQL Server Community.
• Some of the improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1.

Find more information on SQL Server 2012 SP4 by reading the detailed Engineering team blog post and the release notes.

Customers running SQL Server 2012 can extend their product support lifecycle by six years with Premium Assurance. Learn more about this option to explore this opportunity to stay compliant with minimal disruption.

The post SQL Server 2012 Service Pack 4 is now available appeared first on Microsoft SQL Server Blog.

SQL Server 2012 SP2 contains fixes to issues that have been reported through our customer feedback platforms and Hotfix solutions provided in SQL Server 2012 SP1 cumulative updates up to and including Cumulative Update 9.  Service Pack 2 also includes a handful of design change requests and fixes for issues that have been reported through Windows Error Reporting system.

A few customer-requested updates in Microsoft SQL Server 2012 SP2 are:

• Enhanced informational messages about AlwaysOn availability groups.  Support for COPY_ONLY backups of an AlwaysOn secondary through DPM.
• Support for local cube creation.  More Analysis Services and Reporting Services logging to improve troubleshooting.
• Performance improvements for SQL Server Integration Services around SSIDB deadlock/cleanup.
• Query planning enhancements and improved troubleshooting diagnostics for hash join/aggregate operator spills of data in tempdb and full text indexes.
• Replication supportability and functionality enhancements.
• Storage engine performance enhancements.

For more highlights of SQL Server 2012 SP2, please read here.

For information about upgrading to SQL Server 2012, see setup information here. To obtain SQL Server 2012 SP2 with its improved supportability please visit the links below:

SQL Server SQL Server 2012 SP2

SQL Server SQL Server 2012 SP2 Express

SQL Server SQL Server 2012 SP2 Feature Packs 

The post SQL Server 2012 SP2 is now available! appeared first on Microsoft SQL Server Blog.

Given that it’s such an exciting time to be in the industry, we thought this would be an ideal opportunity to ask some of the SQL Server community members attending PASS about what issues from the past they are glad are behind them, and about what industry and technology trends they are looking forward to in the future.

The answers from community members on what future trends they are most interested in were extremely diverse, including topics such as big data, new data visualizations, in-memory technologies and cloud-based & hybrid architectures. Watch the full video below to hear what the SQL Server community had to say.

Incidentally, many of the people featured in the video have already worked on published SQL Server 2012 customer stories.  You can find a complete list of these case studies at www.microsoft.com/sqlcustomers.

The post The SQL Server Community Looks to Emerging Trends in BI and Database Technologies appeared first on Microsoft SQL Server Blog.

• • Project codename “Hekaton,” a new in-memory technology that will be built directly into the data platform, will ship in the next major version of SQL Server.  Currently in private technology preview with a small set of customers, Hekaton completes the company’s portfolio of in-memory technologies across analytics, transactions, streaming and caching workloads, enabling business acceleration by shrinking the time from raw data to insights.

• • SQL Server 2012 Parallel Data Warehouse (PDW), the next version of Microsoft’s enterprise-class appliance, will be available during the first half of 2013.  SQL Server 2012 PDW includes PolyBase, a fundamental breakthrough in data processing that will enable queries across relational data and non-relational Hadoop data.

• • SQL Server 2012 SP1, which supports Office 2013 by offering business users enhanced, new capabilities for self-service business intelligence using familiar tools such as Excel and Sharepoint, is now available for download. 

What’s more, on the final day of PASS Summit 2012, attendees were treated to the presentation, “Big Data Meets SQL Server 2012” by Microsoft Technical Fellow David DeWitt. 

All the while, conference participants attended a wide variety of technical sessions presented by industry experts in addition to a host of other programs. From on-site certification testing, to hands-on-labs, attendees were able to boost their technical skills using these resources, as well as work through technical issues with top Microsoft Customer Service and Support (CSS) engineers and get architectural guidance from the SQL Server, Business Intelligence and Azure Customer Advisory Teams (CAT). Of course, the learning didn’t stop there; attendees were invited to new, “I Made That!” Developer Chalk Talks, which featured 30 minute casual talks with the Microsoft developers who worked on specific features and functionalities of SQL Server 2012. The topics appealed to many, ranging from AlwaysOn to Hekaton. You can see more great photos from PASS Summit 2012 on the SQL Server Facebook page or access the video interviews with Dave Campbell, Quentin Clark, and David DeWitt available at the SQL Server virtual press room.

And so, as we close on another year of PASS Summit, it’s the perfect time to look back and see how far we’ve come since the launch of SQL Server 2012.  Join us below, as we take a celebratory look at the milestones we’ve hit along the way, and let’s look together toward the bright future ahead!

The post PASS Summit 2012 Recap & the Milestones of SQL Server 2012 appeared first on Microsoft SQL Server Blog.

On November 7, we unveiled details for SQL Server 2012 Parallel Data Warehouse (PDW), our scale-out Massively Parallel Processing (MPP) data warehouse appliance, which has evolved to fully embrace this new world. SQL Server 2012 PDW is built for big data and will provide a fundamental breakthrough in data processing using familiar tools to do seamless analysis on relational and Hadoop data at the lowest total cost of ownership.

• Built for Big Data: SQL Server 2012 PDW is powered by PolyBase, a breakthrough in data processing, thatenables integrated queries across Hadoop and relational data. Without manual intervention, PolyBase Query Processor can accept a standard SQL query and join tables from a relational source with data from a Hadoop source to return a combined result seamlessly to the user. Going a step further, integration with Microsoft’s business intelligence tools allows users to join structured and unstructured data together in familiar tools like Excel to answer questions and make key business decisions quickly.   

• Next-generation Performance at Scale: By upgrading the primary storage engine to a new updateable version of xVelocity columnstore, users can gain in-memory performance (up to 50x faster) on datasets that linearly scale out from small all the way up to 5 Petabytes of structured data.     

• Engineered For Optimal Value: In SQL Server 2012 PDW, we optimized the hardware specifications required of an appliance through software innovations to deliver significantly greater cost savings, roughly 2.5x lower cost per TB and value. Through features delivered in Windows Server 2012, SQL Server 2012 PDW has built-in performance, reliability, and scale for storage using economical high density disks. Further, Windows Server 2012 Hyper-V virtualizes and streamlines an entire server rack of control functions down to a few nodes. Finally, xVelocity columnstore provides both compression and the potential to eliminate the rowstore copy to reduce storage usage up to 70%. As a result of these innovations, SQL Server 2012 PDW has a price per terabyte that is significantly lower than all offers in the market today.

With SQL Server 2008 R2 Parallel Data Warehouse, Microsoft already demonstrated high performance at scale when customers like HyVee improved their performance 100 times by moving from SQL Server 2008 R2 to SQL Server 2008 R2 Parallel Data Warehouse. SQL Server 2012 Parallel Data Warehouse takes a big leap forward in performance, scale, and the ability to do big data analysis while lowering costs. For the first time, customers of all shapes, sizes and data requirements from the low end to the highest data capacity requirements can get a data warehouse appliance within their reach.

We are very excited about SQL Server 2012 PDW which will be released broadly in the first half of 2013 and invite you to learn more through the following resources:

• Watch the latest PASS Summit 2012 Keynote or sessions here
• Microsoft Official Blog Post on PASS Summit 2012, authored by Ted Kummert here
• Read customer examples of SQL Server 2008 R2 PDW (HyVee)
• Visit HP’s Enterprise Data Warehouse for SQL Server 2008 R2 Parallel Data Warehouse site
• Find out more about Dell’s SQL Server 2008 R2 Parallel Data Warehouse here

The post Seamless insights on structured and unstructured data with SQL Server 2012 Parallel Data Warehouse appeared first on Microsoft SQL Server Blog.

As I mentioned in the “tipping point” post, much of the energy around in-memory data management systems thus far has been around columnar storage and analytical workloads. As the previous blog post mentions, Microsoft already ships this form of technology in our xVelocity analytics engine and xVelocity columnstore index. xVelocity columnstore index will be updated in SQL Server 2012 Parallel Data Warehouse (PDW v2) to support updatable clustered columnar indexes. Hekaton, in contrast, is a row-based technology squarely focused on transaction processing (TP) workloads. Note that these two approaches are not mutually exclusive. The combination of Hekaton and SQL Server’s existing xVelocity columnstore index and xVelocity analytics engine, will result in a great combination.

The fact that Hekaton and xVelocity columnstore index are built-in to SQL Server, rather than a separate data engine, is a conscious design choice. Other vendors are either introducing separate in-memory optimized caches or building a unification layer over a set of technologies and introducing it as a completely new product. This adds complexity forcing customers to deploy and manage a completely new product or, worse yet, manage both a “memory-optimized” product for the hot data and a “storage-optimized” product for the application data that is not cost-effective to reside primarily in memory.

Hekaton is designed around four architectural principles:

1) Optimize for main memory data access: Storage-optimized engines (such as the current OLTP engine in SQL Server today) will retain hot data in a main memory buffer pool based upon access frequency. The data access and modification capabilities, however, are built around the viewpoint that data may be paged in or paged out to disk at any point. This perspective necessitates layers of indirection in buffer pools, extra code for sophisticated storage allocation and defragmentation, and logging of every minute operation that could affect storage. With Hekaton you place tables used in the extreme TP portion of an application in memory-optimized main memory structures. The remaining application tables, such as reference data details or historical data, are left in traditional storage optimized structures. This approach lets you memory-optimize hotspots without having to manage multiple data engines.

Hekaton’s main memory structures do away with the overhead and indirection of the storage optimized view while still providing the full ACID properties expected of a database system. For example, durability in Hekaton is achieved by streamlined logging and checkpointing that uses only efficient sequential IO.

2) Accelerate business logic processing: Given that the free ride on CPU clock rate is over, Hekaton must be more efficient in how it utilizes each core. Today SQL Server’s query processor compiles queries and stored procedures into a set of data structures which are evaluated by an interpreter in SQL Server’s query processor. With Hekaton, queries and procedural logic in T-SQL stored procedures are compiled directly into machine code with aggressive optimizations applied at compilation time. This allows the stored procedure to be executed at the speed of native code.

3) Provide frictionless scale-up: It’s common to find 16 to 32 logical cores even on a 2-socket server nowadays. Storage-optimized engines rely on a variety of mechanisms such as locks and latches to provide concurrency control. These mechanisms often have significant contention issues when scaling up with more cores. Hekaton implements a highly scalable concurrency control mechanism and uses a series of lock-free data structures to eliminate traditional locks and latches while guaranteeing the correct transactional semantics that ensure data consistency.

4) Built-in to SQL Server: As I mentioned earlier – Hekaton is a new capability of SQL Server. This lays the foundation for a powerful customer scenario which has been proven out by our customer testing. Many existing TP systems have certain transactions or algorithms which benefit from Hekaton’s extreme TP capabilities. For example, the matching algorithm in financial trading, resource assignment or scheduling in manufacturing, or matchmaking in gaming scenarios. Hekaton enables optimizing these aspects of a TP system for in-memory processing while the cooler data and processing continue to be handled by the rest of SQL Server.

To make it easy to get started, we’ve built an analysis tool that you can run so you can identify the hot tables and stored procedures in an existing transactional database application. As a first step you can migrate hot tables to Hekaton as in-memory tables. Doing this simply requires the following T-SQL statements²:

While Hekaton’s memory optimized tables must fully fit into main memory, the database as a whole need not. These in-memory tables can be used in queries just as any regular table, however providing optimized and contention-free data operation at this stage.

After migrating to optimized in-memory storage, stored procedures operating on these tables can be transformed into natively compiled stored procedures, dramatically increasing the processing speed of in-database logic. Recompiling these stored procedures is, again, done through T-SQL, as shown below:

What can you expect for a performance gain from Hekaton? Customer testing has demonstrated up to 5X to 50X throughput gains on the same hardware, delivering extreme TP performance on mid-range servers. The actual speedup depends on multiple factors, such as how much data processing can be migrated into Hekaton and directly sped up; and, how much cross transaction contention is removed as a result of the speed up and other Hekaton optimizations such a lock free data structures.

Hekaton is now in private technology preview with a small set of customers. Keep following our product blogs for updates and a future public technology preview.

Dave Campbell
Technical Fellow
Microsoft SQL Server

[1] Hekaton is from the Greek word ἑκατόν for “hundred”. Our design goal for the Hekaton original proof of concept prototype was to achieve 100x speedup for certain TP operations.

[2] The syntax for these operations will likely change. The examples demonstrate how easy it will be to take advantage of Hekaton’s capabilities.

The post Breakthrough performance with in-memory technologies appeared first on Microsoft SQL Server Blog.