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.

To generate a production-ready schema-only copy of a database, it’s highly recommended to utilize tools such as Microsoft SQL Server Data Tools (SSDT); the Generate and Publish scripts Wizard; or data-tier application extraction, which includes either the Extract Data-tier Application (DAC) Wizard or a PowerShell script. These tools provide a reliable way to create a copy of a database for use in production environments.

Learn more about

• DBCC CLONEDATABASE (Transact-SQL) – SQL Server
• Clone an Existing Database – SQL Server Data Tools (SSDT)
• Generate and Publish Scripts Wizard – SQL Server Management Studio (SSMS)
• Create a statistics-only database using a generated statistics script – SQL Server
• Extract a DAC from a Database – SQL Server

Frequently asked questions

What is SSDT?

SQL Server Data Tools (SSDT) is a modern development tool that integrates with Microsoft Visual Studio and provides design, debugging, and deployment capabilities for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, developers can perform necessary tasks without needing the admin-focused SQL Server Management tool on their developer computer. Essentially, Visual Studio removes unnecessary features like admin tools, and focuses on tools that are useful for developers, like database and schema comparison tools.

What is the Generate and Publish Scripts Wizard?

The Generate and Publish Scripts Wizard is a feature in SQL Server Management Studio (SSMS) that allows you to create scripts for transferring a database between instances of the SQL Server Database Engine or Azure SQL Database. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Database. The generated scripts can be run on another instance of the Database Engine or SQL Database. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services. You can create scripts for an entire database or limit it to specific objects.

What is a data-tier application (DAC)?

A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects—such as tables, views, and instance objects, including logins—associated with a user’s database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. Tooling support for data-tier applications enables developers and database administrators to deploy dacpacs to new or existing databases. Deployments to an existing database update the database model from the existing state to match the contents of the dacpac. Developers build DACs from SQL database projects, a declarative development concept for building SQL objects that enables source control on the database schema.

A .bacpac is a related artifact that, by default, encapsulates the database schema and the data stored in the database. The primary use case for a BACPAC is to move a database from one server to another—or to migrate a database from a local server to the cloud—and archiving an existing database in an open format.

What is DBCC CLONEDATABASE command?

DBCC CLONEDATABASE creates a new database that contains the schema of all the objects and statistics from the specified source database. Cloned databases copy all schema and metadata of the source database without copying any data.

How do I use Schema Compare to compare different database definitions?

SSDT includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot, or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete, you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.

The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.

You can save comparisons either as part of an SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily repeat the same comparison later or use it as the starting point for a new comparison.

Why do I need to generate a schema-only clone of a database with statistics in SQL Server?

You will need to generate a schema-only clone of a database with statistics to investigate performance issues.

The query optimizer in SQL Server uses the following types of information to determine an optimal query plan:

• Database metadata
• Hardware environment
• Database session state

Typically, you must simulate all these same types of information to reproduce the behavior of the query optimizer on a test system.

Microsoft Customer Support Services might ask you to generate a schema script of the database along with statistics to investigate a query optimizer issue.

The post Update on the support of DBCC CLONEDATABASE for production use appeared first on Microsoft SQL Server Blog.

In SQL Server 2016 through 2019, we added R and Python language support, which enable secure execution of R and Python programs in the context of a SQL Server query. This enables a wide range of scenarios such as performing advanced text and data preparation tasks, and reaching out to external APIs to get data and also training machine learning models and model scoring.

Previously, we announced a Java extension. Today, we are sharing that we are open sourcing the R and Python language extensions for SQL Server for both Windows and Linux on GitHub.

These extensions are the latest examples using an evolved programming language extensibility architecture which allows integration with a new type of language extension. This new architecture gives customers the freedom to bring their own runtime and execute programs using that runtime in SQL Server, while leveraging the existing security and governance that the SQL Server programming language extensibility architecture provides.

Choosing which runtime to use does provide the flexibility to choose different distributions of R and Python, and as newer versions of the R and Python runtimes get released, this architecture will make it easier to upgrade the R and Python runtime. Enterprises need to have a support contract in place for their R and Python runtime.

Now that support is not an issue, let’s look at what use cases R and Python can enable inside SQL Server. Bringing R and Python workloads closer to the data opens a variety of possibilities:

• Run R and Python scripts to do data preparation and general purpose data processing.
• Train machine learning models in database.
• Deploy your models and scripts into production in stored procedures.
• Furthermore, this will help avoid unnecessary data movement and latency when data must be retrieved from SQL Server and moved into the app tier to do the business logic processing.
• Data Security Model of database logins and roles extend to external scripts.
• Avoid impersonation attempts.
• Prevent the installation of malware.

Why Open Source?

The R and Python language extension leverages the Extensibility Framework API for SQL Server to communicate and exchange data with SQL Server. This API has been publicly documented. The API in combination with the open source code of the R and Python language extension provides an end to end example implementation of how a programming language extension can be built. This makes it easier for additional programming language extensions to be built for SQL Server by the community. What language extensions would you like to see?

Get started

Whether you are interested in creating your own language extension or just using the R and Python language extension for SQL Server, here is some more information to get you started.

The post Open sourcing the R and Python language extensions for SQL Server appeared first on Microsoft SQL Server Blog.

This year myself and other members of the Microsoft SQL Server Engineering and support teams will be at the PASS Summit again in a major way with keynotes, a pre-conference session, conference sessions, theater sessions, a great booth, and the always popular Data Clinic.

We have already received great interested in our pre-conference session called Modernize Your SQL Server with Bob Ward, the Tiger Team, and CSS Escalation Engineers on Tuesday, November 6th. You can read more about this session at the PASS Summit Session webpage.

Whether you have registered or not, it is not too late to register for this pre-conference seminar. It has been awhile since I have participated in a pre-conference seminar at the PASS Summit. I’m excited what this one is about. We know that many of our customers are still using legacy releases of SQL Server like 2005, 2008, and 2008R2. If you are in this situation and wondering should I make the move to something new, something more modern, then this session is for you.

This session is not just an upgrade session! Sure we will show you how to migrate but our intention is to provide you with everything you need on how to modernize. Now you can learn the capabilities of SQL Server 2016, 2017, 2019, SQL Server on Linux, containers, Azure Virtual Machine, and Azure SQL Database in a way to know what features make a migration compelling. Our day also includes pre and post migration advice to maximize your investments and architectural guidance for key areas like performance, security, and HADR (the “meat and potatoes” of SQL Server).

The team of folks I have lined up to train you this day is nothing short of amazing. Every person that will be there to train you all have extensive customer experience and practical knowledge. This includes members of the Tiger Team such as Amit Banerjee, Pam Lahoud, Pedro Lopes, Argenis Fernandez, Sourabh Agarwal, and Vin Yu. In addition, we want you to be able to diagnose and troubleshoot problems with new technologies in a modern way so I’m bringing in some of the top talent of CSS including Suresh Kandoth and Sean Gallardy. It will be a packed day but also an opportunity when you can talk to us 1:1 about your SQL Server installation base and what and how modernization will help you.

You will walk away from this session with the resources you need including all of our content and demo scripts and examples. We look forward to everyone coming to this session and I would love to meet each and every one of you personally to talk about your modernization plans for SQL Server.

Stay tuned for future blog posts where I’ll show you the other great session and content the Microsoft SQL Server team has available at the PASS 2018 Summit.

The post Modernize your SQL Server at the PASS Summit 2018 appeared first on Microsoft SQL Server Blog.

Customers will get significantly improved query performance and enhanced security features with this release. APS AU7 builds on appliance update 6 (APS 2016) release as a foundation. Upgrading to APS appliance update 6 is a prerequisite to upgrade to appliance update 7.

Faster performance

APS AU7 now provides the ability to automatically create statistics and update of existing outdated statistics for improved query optimization. APS AU7 also adds support for setting multiple variables from a single select statement reducing the number of redundant round trips to the server and improving overall query and ETL performance time. Other T-SQL features include HASH and ORDER GROUP query hints to provide more control over improving query execution plans.

Better security

APS AU7 also includes latest firmware and drivers along with the hardware and software patch to address the Spectre/Meltdown vulnerability from our hardware partners.

Management enhancements

Customers already on APS2016 will experience an enhanced upgrade process to APS AU7 allowing a shorter maintenance window with the ability to uninstall and rollback to a previous version.
AU7 also introduces a section called Feature Switch in configuration manager giving customers the ability to customize the behavior of new features.

Flexibility of choice with Microsoft’s data warehouse portfolio

The latest update is an addition to already existing data warehouse portfolio from Microsoft, covering a range of technology and deployment options that help customers get to insights faster. Customers exploring data warehouse products can also consider SQL Server with Fast Track for Data Warehouse or Azure SQL Data Warehouse, a cloud-based fully managed service.

Next Steps

For more details about these features, please visit our online documentation.

The post Microsoft releases the latest update of Analytics Platform System appeared first on Microsoft SQL Server Blog.

SQL Server 2016 Service Pack 2 (“SP2”) contains a roll-up of released hotfixes as well as multiple improvements centered around performance, scalability, and supportability based on feedback from customers and the SQL community. These improvements enable SQL Server 2016 to perform faster and with expanded supportability and diagnostics. It also showcases the SQL Server product team’s commitment to providing continued value into in-market releases.

SQL Server 2016 Service Pack 2 includes:

• Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
• Supportability and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.
• New improvements based on Connect feedback items filed by the SQL Server Community.
• Some improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2012 SP4.

Find more information on SQL Server 2016 SP2 in our KB article and Release Notes, and download today by visiting the links below:

• SQL Server 2016 SP2
• SQL Server 2016 SP2 Express
• SQL Server 2016 SP2 Feature Packs

If you have a question or would like to make a suggestion, you can let us know through UserVoice. We look forward to hearing from you!

The post SQL Server 2016 Service Pack 2 is now available appeared first on Microsoft SQL Server Blog.

Efficient data management keeps goods flowing smoothly in Denmark. Danske Fragtmaend, the country’s largest national transport and distribution firm, has been moving freight for more than a century. Today, Danske Fragtmaend delivers more than 40,000 consignments each day throughout Denmark, and businesses from small mom-and-pop operations to factories rely on its services.

The firm handles logistics in a central location, where 200 dispatchers keep an eye on the movement of thousands of trucks and their cargo. Both drivers and dispatchers need the latest information to operate efficiently, so they rely on a data platform based on SQL Server 2016. The storage system includes 160 terabytes of flash memory for fast I/O and high uptimes. Throughout the day, drivers continually scan transactions with PDAs and send shipping information including GPS coordinates to the data platform. Fast access to information is essential. Ulf Preisler, chief information officer at Danske Fragtmaend, says, “When it comes to short-term logistics, you’ve got to think like an air traffic controller more than a traditional radio dispatcher.”

Because the data changes rapidly, asynchronous replication between geographically disparate datacenters was inadequate. Instead, Danske Fragtmaend runs SQL Server on Windows 2016. Windows Server 2016 introduces a new disaster recovery and preparedness feature, Storage Replica, which enables storage-agnostic, synchronous replication of data across geographically diverse datacenters. Even if disaster strikes one location, all the data exists elsewhere, so there is no possibility of loss.

Best of all, companies that combine flash storage with the latest versions of SQL Server and Windows Server can achieve a multiplying effect on performance. Danske Fragmaend’s lead software developer, Morten Vinther, ran several tests to compare the old storage stack with the new one. “After combining the new all-flash infrastructure and the features from SQL Server 2016 on Windows Server 2016, one of our BI queries ran 9,521 times faster than on the prior infrastructure. That is much more than we expected.”

To find out more about Danske Fragtmaend’s SQL Server 2016 implementation, read the customer story.

Customer Name: Danske Fragtmaend
Industry: Transportation and logistics
Country or Region: Denmark
Customer Website: www.fragt.dk
Employee Size: 900

The post Keeping freight moving in Denmark with SQL Server 2016 appeared first on Microsoft SQL Server Blog.

Have you ever seen a tree that simultaneously bears completely different species of fruit? It’s a real thing: apples, plums, oranges, lemons, and peaches all growing on the same tree. The growers have the advantage of a consistent environment (the same tree) that allows them to be efficient with resources, pick the type of fruit they need when they need it, and always have the right kind of fruit without having to invest in specialized plants.

Those trees are like the consistent foundation shared by SQL Server 2016, Windows Server 2016, and Microsoft Azure: Common code underlying the Microsoft platform makes it possible to run your data workloads seamlessly on-premises, in a hybrid environment, or strictly in the cloud—and to pick the option you need, while moving easily from one environment to the other.

Common code = Unique value

The common code base creates a write-once-deploy-anywhere SQL Server and Windows Server experience. You have flexibility across physical on-premises machines, private cloud environments, third-party hosted private cloud environments, public cloud, and hybrid deployments. Figure 1 diagrams this unique platform.

Figure 1: Microsoft Data Platform: On-premises, hybrid, and cloud

This means that you can choose a hybrid deployment and take advantage of any of the four basic options for hosting SQL Server:

1. SQL Server in on-premises non-virtualized physical machines
2. SQL Server in on-premises virtualized machines
3. SQL Server on Azure Virtual Machine. This is SQL Server installed and hosted in the cloud on Windows Server virtual machines (VMs) running on Azure. Also known as infrastructure as a service (IaaS), it is optimized to “lift and shift” existing SQL Server applications to the cloud. All versions and editions of SQL Server are available, including free ones for dev/test and lightweight workloads.
4. Azure SQL Database (Microsoft public cloud). This is a SQL Server database native to the cloud and compatible with most SQL Server features. It is also known as a platform as a service (PaaS) database or a database as a service (DBaaS). It delivers all the agility and world-class security features of Azure and is ideal for software as a service (SaaS) app development.

When you run SQL Server on Windows Server, whether on-premises or in an IaaS virtual machine, you get the benefit of:

• Improved database performance and availability with support for up to 24 terabytes of memory and 640 cores on a single server.
• Built-in security at the operating system level. For example, when database admins can use a single Active Directory management pane across Azure and on-premises machines to set policies, enable/disable access, etc., it truly raises the security bar across the organization.
• Simple and seamless upgrades with Rolling Upgrades.
• Ability to make SQL highly available on any cloud with Storage Spaces Direct to create virtual shared storage across VMs.
• Access to new classes of direct-attach storage (such as NVMe) for applications that require redundant storage across machines.
• Reduce costs of hosting additional VMs by leveraging a Cloud Witness.

You benefit from the ability to use familiar server products, development tools, and technical expertise across all environments. No other platform delivers across this spectrum of implementations and builds in hybrid capabilities everywhere. Learn how to choose Azure SQL (PaaS) Database or SQL Server on Azure VMs (IaaS).

Free migration tools

Further easing the way to hybrid and cloud solutions are the SQL Azure Migration Wizard and other free migration tools. These are designed to provide easy migration of Windows Server 2016 servers to virtual machines in the cloud.

When determining how much hardware to allocate for certain applications, downsizing datacenters, or migrating existing workloads to virtual machines (VMs), you can tap into cloud capabilities in several ways:

• Backup to Azure, including, managed backup, backup to Azure Block Blobs, and Azure Storage snapshot backup.
• The Azure Site Recovery tool to migrate workloads on on-premises VMs and physical servers to run on Azure VMs, with full replication and backup, Azure IaaS VMs between Azure regions, and AWS Windows instances to Azure IaaS VMs. Easy addition of an Azure node to an AlwaysOn Availability Group in a hybrid environment.
• Two new limited previews, Azure Database Migration Service and Azure SQL Database – Managed Instance, create a great path for customers looking for a way to easily modernize their existing database environment to a fully managed PaaS service without application redesign.

SQL Server License Mobility and Azure Hybrid Use Benefit for Windows Server

Even licensing is designed to ensure that wherever you deploy, you can cost-effectively take advantage of all the options.

• SQL Server customers with active Software Assurance can use existing licenses on Azure Virtual Machines with no extra charges to SQL Server licensing. Simply assign core licenses equal to the virtual cores in the VM, and pay only for VM compute costs.
• License Mobility ensures you can easily move SQL Server databases to the cloud using your existing licensing agreement with active Software Assurance. No additional licensing is required for SQL Server passive high availability (HA) nodes; you can configure a passive VM with up to the same compute as your active node to deliver uptime.
• Windows Server customers with Software Assurance can save up to 40 percent by leveraging on-premises licenses to move workloads to Azure VMs with this Azure Hybrid Use Benefit.

SQL Server 2016 with Windows Server 2016: Built for hybrid cloud

Microsoft continues to build in innovation so that organizations do not have to purchase expensive add-ins to get the benefits of the cloud with security features, simplicity, and consistency across on-premises and the cloud. Together, SQL Server 2016 and Windows Server 2016 will bear fruit for your organization. Get started on hybrid now.

Learn more about SQL Server in Azure VM in this datasheet.

Try SQL Server in Azure.

Improve security, performance, and flexibility with SQL Server 2016 and Windows Server 2016

By running SQL Server 2016 and Windows Server 2016 together you can unlock the full potential of the Microsoft data platform. This series of blogs on five reasons to run these two new releases together barely scratches the surface. What’s the best way to find out just how powerful this combination is? Try it out! Download your free trial of Windows Server 2016 and SQL Server 2016 today.

Read more

• Five reasons to run SQL Server 2016 on Windows Server 2016, part 1
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 2
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 3
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 4

The post Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 5: Consistent data environment across hybrid cloud environments appeared first on Microsoft SQL Server Blog.

In addition, join us for Microsoft Data Amp on April 19 at 8 AM PT. The online event will showcase how data is the nexus between application innovation and artificial intelligence. You’ll learn how data and analytics powered by the most trusted and intelligent cloud can help companies differentiate and out-innovate their competition. Microsoft Data Amp—where data gets to work.

“Data! Data! Data! … I can’t make bricks without clay!” – Sherlock Holmes (Sir Arthur Conan Doyle)

If he lived today, Sherlock Holmes might be a data scientist, working to solve cases faster by using advanced analytics to augment his legendary deductive powers. And Sherlock would insist on the fastest means possible for reaching insights. So what’s the best way to process massive amounts of data quickly to get faster time to insight?

It’s elementary: Sherlock would deduce that SQL Server 2016 and Windows Server 2016 are an exceptional platform for delivering built-in fast analytics by running queries at the point of creation.

At the OS level, Windows Server 2016 delivers new levels of performance with capabilities such as Persistent Memory (or Storage Class Memory), which improves latency by 3x, and Storage Spaces Direct, which gives you highly available, scalable storage area network functionality on inexpensive industry-standard servers and produces read speeds that can exceed 25 GB per second.

These features are built into the OS, so no additional licenses are required. For full details on Windows Server 2016 price/performance benefits, read the blog post “Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost.”

In addition, at the data platform level, SQL Server 2016 delivers innovative analytics tools such as SQL Server R Services, real-time operational analytics, and new R-models.

Combined, SQL Server 2016 and Windows Server 2016 provide multithreading and massively parallel processing for high-performance data analysis.

SQL Server R Services built into T-SQL

R is a respected data-mining tool for uncovering insights and making predictions. SQL Server R Services is built into T-SQL and brings advanced predictive analytics to the data.

As a SQL Server 2016 data professional, you probably use T-SQL daily. Now, you can take advantage of R through the T-SQL interface. With R Services support for in-database analytics, you can work with data in SQL Server 2016, and applications can use T-SQL system stored procedures to call R scripts. If you’re an application developer, you don’t need to deep dive into R. You can rely on the T-SQL API for such tasks as creating SQL Server Reporting Services reports or Power BI dashboards with scores, predictions, and visuals from R.

You also get SQL Server built-in functions and mechanisms to accelerate performance and integration. For example, you can use columnstore indexes with R for faster queries. Built-in resource governance can control the resources allocated to the R runtime. The stored procedure interface gives you smooth integration with SQL Server Integration Services for integration with common extract, transform, and load and job scheduling. Learn more about SQL Server 2016 R Services and read about a real-world implementation.

Real-time operational analytics

With SQL Server 2016, you can do real-time operational analytics in two ways: on disk-based and memory-optimized tables. This means you don’t have to make changes to your applications when you perform real-time analytics.

SQL Server 2016 Real-Time Operational Analytics lets you use columnstore indexes to run analytics queries directly on your operational workload. Figure 1 shows a possible configuration, which uses Analysis Server in Direct Query mode, but if you have other analytics tools or a custom solution, you can use those, too. When you use both memory-optimized and columnstore, you get the best of online transaction processing performance and analytics query performance. Learn more about real-time operational analytics using in-memory technology.

Figure 1: A real-time operational analytics example

Multithreading and massive parallel processing

The cloud is built into SQL Server 2016, making it possible for you to take advantage of multithreading and massive parallel processing (MPP) to achieve high-performance data analysis. Azure SQL Data Warehouse uses an elastic MPP architecture built on the SQL Server 2016 database engine. This means you can continue using the SQL Server-based tools and BI applications that you use today when you want to interactively query and analyze data. Azure SQL Data Warehouse has built-in performance analytics and storage compression, the aggregation capabilities of SQL Server, and cutting-edge query optimization capabilities. In addition, with Polybase built in, you can query Hadoop systems directly, so that you have a single SQL-based query surface for all your data.

New R-models

SQL Server and Windows Server come with built-in functionality to make your job easier. In addition, Azure data services provides access to the work data scientists have already done by creating R-models that you can use. You can find a model you need in the Azure marketplace.

The Cortana Intelligence Solutions Gallery is the Microsoft Data Science VM that comes loaded with all the tools a data scientist needs. You can also find the code on GitHub, so you can run it locally on your own machine.

To learn more, see “Cortana Intelligence and Machine Learning Blog: Using SQL Server 2016 with R Services for Campaign Optimization”.

SQL Server 2016 and Windows Server 2016: It’s all built in

The role of data in business decision-making is taking on ever greater importance, and the difference between success and failure can hinge on how fast you’re able to analyze data. As a result, business intelligence and advanced analytics are changing the very nature of business. By examining all the built-in capabilities, surely Sherlock Holmes would deduce that with the combination of SQL Server 2016, Windows Server 2016, and Azure, you can make sure your business is equipped for success. For an overview of SQL Server 2016 advanced analytics and business intelligence, see “Decisions @ the speed of thought with SQL Server 2016.” For details on price/performance, see “Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost.”

Read more

• Five reasons to run SQL Server 2016 on Windows Server 2016, part 1
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 2
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 3
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 5

The post Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 4: Reach insights faster by running analytics at the point of creation appeared first on Microsoft SQL Server Blog.

In addition, join us for Microsoft Data Amp on April 19 at 8 a.m. PT. The online event will showcase how data is the nexus between application innovation and artificial intelligence. You’ll learn how data and analytics powered by the most trusted and intelligent cloud can help companies differentiate and out-innovate their competition. Microsoft Data Amp—where data gets to work.

When does 2 + 2 = 5? When two teams work hard to deliver great products individually, but also work together to make the combination more than the sum of their parts. Windows Server 2016 and SQL Server 2016 are prime examples. The development teams have collaborated closely to ensure that the very best experience for data professionals emerges when you take advantage of the synergies built into the Windows Server OS and the SQL Server data platform. In this post, we’ll share how the teams have worked together to deliver advanced functionality to improve database uptime and reliability, including effective disaster recovery across sites and domains.

Always On Availability Groups: Enhanced capabilities supporting new scenarios

Always On Availability Groups have been at the center of SQL Server availability since the 2012 release. Availability Groups establish a relationship between a set or group of databases and replicas of that group of databases on one or more replicas. This means all the databases in the group can move as a unit, eliminating the need for complex scripting solutions to do this task.

Up to now, with Windows Server Failover Cluster solutions, all nodes in the Availability Group had to reside in the same Active Directory domain. However, many organizations have multiple domains that can’t be merged, and they want to span an Availability Group across such domains. In other situations, organizations may have no Active Directory domains at all, yet still want to host disaster recovery replicas.

To give these organizations a solution, the SQL Server and Windows Server teams delivered Windows Server 2016 Failover Clusters (WSFC). Now, all nodes in a cluster no longer need to reside in the same domain—and indeed the nodes are no longer required to be in any domain at all. Instead, you can form a WSFC cluster with machines that are in workgroups.

SQL Server 2016 is able to deploy flexible Always On Availability Groups in environments with:

• All nodes in a single domain
• Nodes in multiple domains with full trust
• Nodes in multiple domains with no trust
• Nodes in no domain at all

With SQL Server 2016 and Windows Server 2016, Always On availability groups can include up to eight readable secondaries and can span multi-domain clusters. In addition, Active Directory authentication is no longer required. All this innovation opens up new scenarios and removes previous blocks that prevented migration from deprecated Database Mirroring technology to Always On Availability Groups. (For details, see “Enhanced Always On Availability Groups in SQL Server 2016.” Click here for a video demo.)

Hybrid Backup and Stretch Database provide online cold data availability in Azure

SQL Server 2016 and Windows Server 2016 are architected to work smoothly with the Microsoft Azure cloud in a hybrid environment. Microsoft hybrid cloud technology provides a consistent set of tools and processes between on-premises and cloud-based environments. This means that SQL Server 2016 is designed to work in a hybrid cloud environment in which data and services reside in various locations. You get faster hybrid backups and disaster recover that lets you back up and restore on-premises databases to Azure and place SQL Server Always On secondaries in Azure. The figures below show how Stretch Database works.

With this flexibility come new ways to save money and address business needs. For example, storing data is a critical business requirement that can be very expensive. To reduce this cost, SQL Server 2016 introduced Stretch Database. It allows production databases to offload older (cold) data to the Microsoft Azure cloud without losing access to the data. Many enterprises need reasonably quick access to their cold data for compliance reasons, and they can now push that data to the cloud to save money on storage costs while still having ready access for compliance audits. (Blog 5 in this series will discuss SQL Server running in a Windows Server infrastructure-as-a-service virtual machine on Azure.)

This means you no longer need to rely on extremely expensive dedicated solutions from storage vendors. In SQL Server 2016, Stretch Database lets you keep as much data as you need for as long as you need, without risking business service level agreements or the high cost of traditional storage. Database administrators need only to enable the database for stretch, and the endless storage and compute capacity of Azure ensures that your data is always online.

In addition, with SQL Server Backup to URL, you can easily back up directly to Microsoft Azure Blob Storage. You no longer need to manage hardware for backups, and you get the benefit of storing your backups in flexible, reliable, and virtually limitless cloud storage. (For details, see “SQL Server 2016 cloud backup and restore enhancements.”)

Storage Replica delivers inexpensive high availability and disaster recovery

Storage Replica is a new feature in Windows Server 2016 that offers new disaster recovery and preparedness capabilities. For the first time, Windows Server delivers the ability to synchronously protect data on different racks, floors, buildings, campuses, counties, and cities. If a disaster strikes, all data will be at a safe location. Before a disaster strikes, Storage Replica lets you switch workloads to safe locations if you have a few moments warning—again, with no data loss. (Read about how customer Danske Fragtmaend takes advantage of Storage Replica for its zero-data-loss SQL Server failover strategy.)

Storage Replica enables synchronous and asynchronous replication of volumes between servers or clusters. It helps you take more efficient advantage of multiple datacenters. When you stretch or replicate clusters, you can run workloads in multiple datacenters so that nearby users and applications can get quicker data access. In addition, you can better distribute load and compute resources. Most important, you can implement this built-in functionality on commodity hardware and use it with emerging technologies such as Flash and SSD (as Danske Fragtmaend did) to build cost-effective, high-performance storage solutions that can work with existing SAN/NAS implementations—or even replace dedicated SAN/NAS solutions at a fraction of the cost.

Visit the website for more details and demos on Storage Replica.

Rolling, in-place upgrades and less downtime

Customers often tell us they want to use the latest releases of SQL Server and Windows Server, but they need the upgrade process to be less time-consuming and complex. Now they can take advantage of rolling, in-place upgrades from previous versions to SQL Server 2016 and Windows Server 2016—while dramatically minimizing downtime.

Windows Server 2016 Cluster OS Rolling Upgrade lets you upgrade the operating system of the cluster nodes from Windows Server 2012 R2 to Windows Server 2016 without stopping the Hyper-V or the Scale-Out File Server workloads. Not only can you upgrade the OS in place, but Cluster OS Rolling Upgrade works for any cluster workload, including SQL Server 2016.

For SQL Server customers, this is important because you want to move the base OS without having to reinstall and reconfigure SQL Server. Now, in a rolling approach, you can move a cluster node, perform an in-place upgrade and do a clean install while other databases are being serviced by other nodes. The in-place upgrade preserves SQL Server backup and restore history, preserves permissions and group settings, and saves about 20‒30 minutes of upgrade time per node in the cluster. You can achieve this with minimal or no interruptions to the workload that’s running on the cluster, so you can upgrade the cluster in place. With a Hyper-V or Scale-Out File Server Workload, there’s zero downtime, which means you don’t need to buy new hardware. (For details, see Cluster operating system rolling upgrade. To see a video demonstration, watch Introducing Cluster OS Rolling Upgrades in Windows Server 2016.

Better together adds up to the best database reliability at a great price

For mission-critical workloads, you can’t settle for anything less than the best—and most cost-effective—data platform running on the OS that has built-in synergy to ensure database uptime and reliability with advanced disaster recover across domains and sites. Without spending vast amounts of your budget on third-party storage solutions, you can get the functionality you need built into SQL Server 2016 and Windows Server 2016.

Ready to give it a try?

• Free evaluation of SQL Server 2016
• Free evaluation of Windows Server 2016

For more info, check out this summary of five reasons to run SQL Server 2016 with Windows Server 2016.

Read more

• Five reasons to run SQL Server 2016 on Windows Server 2016, part 1
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 2
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 4
• Five reasons to run SQL Server 2016 on Windows Server 2016, part 5

The post Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 3: database uptime and reliability appeared first on Microsoft SQL Server Blog.