Microsoft

SQL Server AlwaysOn Offering in Microsoft Azure Portal Gallery

We are excited to announce the release of a SQL Server AlwaysOn template in the Microsoft Azure Portal Gallery. This offering was announced in Scott Guthrie’s blog post along with several other exciting new features.

This template fully automates the configuration of a highly available SQL Server deployment on Azure Infrastructure Services using AlwaysOn Availability Groups.

AlwaysOn Availability Groups

AlwaysOn Availability Groups, released in SQL Server 2012 and enhanced in SQL Server 2014, guarantee high availability for mission-critical workloads. Last year we started supporting Availability Groups on Azure Infrastructure Services. The main components of such a configuration are two SQL Server replicas (a primary and a secondary), and a listener (DNS name). The replicas are configured for automatic failover, and each replica is contained on a distinct Virtual Machine. The listener is a DNS name that client applications can use in their connection string to connect to the current primary replica. The image below shows a depiction of this setup.

 Other components required are a file share witness to guarantee quorum in the configuration to avoid “split brain” scenarios, and a domain controller to join all VMs to the same domain. Similar to the SQL replicas, there is a primary and secondary domain controller to prevent a single point of failure for the domain. The SQL replicas are deployed to an availability set to ensure they are in different Azure failure and upgrade domains. Likewise, the domain controller replicas are in their own availability set. The configuration is depicted in the image below.

 

SQL Server AlwaysOn Template

Setting up the Availability Group configuration requires a long set of steps and a decent time commitment. In order to dramatically simplify this, we have released a SQL Server AlwaysOn template in the Azure Gallery. This template fully automates the configuration of a highly available SQL Server deployment on Azure Infrastructure Services using an Availability Group. Currently, this feature only supports SQL Server 2014 Enterprise images.

The SQL Server AlwaysOn Template, depicted below, is found in the Gallery under “Virtual Machines” and “Recommended”.

 

After selecting it, it will show a description of the configuration that will be created, and the option to specify some arguments. This is depicted in the picture below.

The only arguments required are a Resource Group (an identifier of the deployment) and administrator credentials. From that point on, all settings are optional and will be auto-generated based on these 3 inputs. The domain Sysadmin account, the local SQL Server accounts, and the SQL Server service account password will be auto-generated based on the credentials entered. The names for all resources being created will be based off of what was entered for Resource Group name. The SQL Server service account name and the domain name will be auto-generated but will not be based on the Resource Group name or credentials. If you wish to customize any of these arguments, simply go to the other configurations and change the values entered for any setting. One argument that you may want to change is the Listener name, which your applications will use to connect to SQL Server. By default, entirely new resources will be provisioned for you. You have the option to select an existing domain for the deployment. In future updates, there will be more options to add existing resources to your configuration.

After the template has executed, 5 Virtual Machines will be created under the resource group: 2 Standard A5 VMs for the SQL Server replicas, 2 Standard A1 VMs for the Domain Controller replicas, and 1 Basic A0 VMs for the file share witness. This is depicted below:

You can RDP to one of the SQL Server VMs to see the Availability Group configuration as depicted below:

Try out the SQL Server AlwaysOn Template today by going to the Azure portal: http://portal.azure.com/

SQL Server Team Blog

SQL Server AlwaysOn Offering in Microsoft Azure Portal Gallery

We are excited to announce the release of a SQL Server AlwaysOn template in the Microsoft Azure Portal Gallery. This offering was announced in Scott Guthrie’s blog post along with several other exciting new features.

This template fully automates the configuration of a highly available SQL Server deployment on Azure Infrastructure Services using AlwaysOn Availability Groups.

AlwaysOn Availability Groups

AlwaysOn Availability Groups, released in SQL Server 2012 and enhanced in SQL Server 2014, guarantee high availability for mission-critical workloads. Last year we started supporting Availability Groups on Azure Infrastructure Services. The main components of such a configuration are two SQL Server replicas (a primary and a secondary), and a listener (DNS name). The replicas are configured for automatic failover, and each replica is contained on a distinct Virtual Machine. The listener is a DNS name that client applications can use in their connection string to connect to the current primary replica. The image below shows a depiction of this setup.

 Other components required are a file share witness to guarantee quorum in the configuration to avoid “split brain” scenarios, and a domain controller to join all VMs to the same domain. Similar to the SQL replicas, there is a primary and secondary domain controller to prevent a single point of failure for the domain. The SQL replicas are deployed to an availability set to ensure they are in different Azure failure and upgrade domains. Likewise, the domain controller replicas are in their own availability set. The configuration is depicted in the image below.

 

SQL Server AlwaysOn Template

Setting up the Availability Group configuration requires a long set of steps and a decent time commitment. In order to dramatically simplify this, we have released a SQL Server AlwaysOn template in the Azure Gallery. This template fully automates the configuration of a highly available SQL Server deployment on Azure Infrastructure Services using an Availability Group. Currently, this feature only supports SQL Server 2014 Enterprise images.

The SQL Server AlwaysOn Template, depicted below, is found in the Gallery under “Virtual Machines” and “Recommended”.

 

After selecting it, it will show a description of the configuration that will be created, and the option to specify some arguments. This is depicted in the picture below.

The only arguments required are a Resource Group (an identifier of the deployment) and administrator credentials. From that point on, all settings are optional and will be auto-generated based on these 3 inputs. The domain Sysadmin account, the local SQL Server accounts, and the SQL Server service account password will be auto-generated based on the credentials entered. The names for all resources being created will be based off of what was entered for Resource Group name. The SQL Server service account name and the domain name will be auto-generated but will not be based on the Resource Group name or credentials. If you wish to customize any of these arguments, simply go to the other configurations and change the values entered for any setting. One argument that you may want to change is the Listener name, which your applications will use to connect to SQL Server. By default, entirely new resources will be provisioned for you. You have the option to select an existing domain for the deployment. In future updates, there will be more options to add existing resources to your configuration.

After the template has executed, 5 Virtual Machines will be created under the resource group: 2 Standard A5 VMs for the SQL Server replicas, 2 Standard A1 VMs for the Domain Controller replicas, and 1 Basic A0 VMs for the file share witness. This is depicted below:

You can RDP to one of the SQL Server VMs to see the Availability Group configuration as depicted below:

Try out the SQL Server AlwaysOn Template today by going to the Azure portal: http://portal.azure.com/

SQL Server Team Blog

Get started backing up to the cloud with SQL Server Backup to Microsoft Azure Tool

If you’re considering backing up your SQL Server database to the cloud, there are many compelling reasons. Not only will you have an offsite copy of your data for business continuity and disaster recovery purposes, but you can save on CAPEX by using Microsoft Azure for cost-effective storage.  And now, you can choose to backup to Microsoft Azure even for databases that aren’t running the latest version of SQL Server – creating a consistent backup strategy across your database environment. 

SQL Server has these tools and features to help you back up to the cloud:

  • In SQL Server 2014, Managed Backup to Microsoft Azure manages your backup to Microsoft Azure, setting backup frequency based on data activity.  It is available inside the SQL Server Management Studio in SQL Server 2014.
  • In SQL Server 2012 and 2014, Backup to URL provides backup to Microsoft Azure using T-SQL and PowerShell scripting.
  • For prior versions, SQL Server Backup to Microsoft Azure Tool enables you to back up to the cloud all supported versions of SQL Server, including older ones.  It can also be used to provide encryption and compression for your backups – even for versions of SQL Server that don’t support these functions natively.

To show you how easy it is to get started with SQL Server Backup to Microsoft Azure Tool, we’ve outlined the four simple steps you need to follow:

Prerequisites: Microsoft Azure subscription and a Microsoft Azure Storage Account.  You can log in to the Microsoft Azure Management Portal using your Microsoft account.  In addition, you will need to create a Microsoft Azure Blob Storage Container:  SQL Server uses the Microsoft Azure Blob storage service and stores the backups as blobs. 

Step 1: Download the SQL Server Backup to Microsoft Azure Tool, which is available on the Microsoft Download Center.

Step 2: Install the tool. From the download page, download the MSI (x86/x64) to your local machine that has the SQL Server Instances installed, or to a local share with access to the Internet. Use the MSI to install the tool on your production machines. Double click to start the installation. 

Step 3: Create your rules.  Start the Microsoft SQL Server Backup to Microsoft Azure Tool Service by running SQLBackup2Azure.exe.  Going through the wizard to setup the rules allows the program to process the backup files that should be encrypted, compressed or uploaded to Azure storage. The Tool does not do job scheduling or error tracking, so you should continue to use SQL Server Management Studio for this functionality.

On the Rules page, click Add to create a new rule.    This will launch a three screen rule entry wizard.

The rule will tell the Tool what local folder to watch for backup file creation. You must also specify the file name pattern that this rule should apply to.

To store the backup in Microsoft Azure Storage, you must specify the name of the account, the storage access key, and the name of the container.  You can retrieve the name of the storage account and the access key information by logging into the Microsoft Azure management portal.

At this time, you can also specify whether or not you wish to have the backup files encrypted or compressed.

Once you have created one or more rules, you will see the existing rules and the option to Modify or Delete the rule.

Step 4: Restore a Database from a Backup Taken with SQL Server Backup to Microsoft Azure Tool in place. The SQL Server Backup to Microsoft Azure Tool creates a ‘stub’ file with some metadata to use during restore.  Use this file like your regular backup file when you wish to restore a database.  SQL Server uses the metadata from this file and the backup on Microsoft Azure storage to complete the restore. 

If the stub file is ever deleted, you can recover a copy of it from the Microsoft Azure storage container in which the backups are stored.  Place the stub file into a folder on the local machine where the Tool is configured to detect and upload backup files.

That’s all it takes!  Now you’re up and running with Backup to and Restore from Microsoft Azure.

To learn more about why to back up to the cloud, join Forrester Research analyst Noel Yuhanna in a webinar on Database Cloud Backup and Disaster Recovery.  You’ll find out why enterprises should make database cloud backup and DR part of their enterprise database strategy. 

The webinar takes place on Tuesday, 7/29 at 9 AM Pacific time; register now.

SQL Server Team Blog

Get started backing up to the cloud with SQL Server Backup to Microsoft Azure Tool

If you’re considering backing up your SQL Server database to the cloud, there are many compelling reasons. Not only will you have an offsite copy of your data for business continuity and disaster recovery purposes, but you can save on CAPEX by using Microsoft Azure for cost-effective storage.  And now, you can choose to backup to Microsoft Azure even for databases that aren’t running the latest version of SQL Server – creating a consistent backup strategy across your database environment. 

SQL Server has these tools and features to help you back up to the cloud:

  • In SQL Server 2014, Managed Backup to Microsoft Azure manages your backup to Microsoft Azure, setting backup frequency based on data activity.  It is available inside the SQL Server Management Studio in SQL Server 2014.
  • In SQL Server 2012 and 2014, Backup to URL provides backup to Microsoft Azure using T-SQL and PowerShell scripting.
  • For prior versions, SQL Server Backup to Microsoft Azure Tool enables you to back up to the cloud all supported versions of SQL Server, including older ones.  It can also be used to provide encryption and compression for your backups – even for versions of SQL Server that don’t support these functions natively.

To show you how easy it is to get started with SQL Server Backup to Microsoft Azure Tool, we’ve outlined the four simple steps you need to follow:

Prerequisites: Microsoft Azure subscription and a Microsoft Azure Storage Account.  You can log in to the Microsoft Azure Management Portal using your Microsoft account.  In addition, you will need to create a Microsoft Azure Blob Storage Container:  SQL Server uses the Microsoft Azure Blob storage service and stores the backups as blobs. 

Step 1: Download the SQL Server Backup to Microsoft Azure Tool, which is available on the Microsoft Download Center.

Step 2: Install the tool. From the download page, download the MSI (x86/x64) to your local machine that has the SQL Server Instances installed, or to a local share with access to the Internet. Use the MSI to install the tool on your production machines. Double click to start the installation. 

Step 3: Create your rules.  Start the Microsoft SQL Server Backup to Microsoft Azure Tool Service by running SQLBackup2Azure.exe.  Going through the wizard to setup the rules allows the program to process the backup files that should be encrypted, compressed or uploaded to Azure storage. The Tool does not do job scheduling or error tracking, so you should continue to use SQL Server Management Studio for this functionality.

On the Rules page, click Add to create a new rule.    This will launch a three screen rule entry wizard.

The rule will tell the Tool what local folder to watch for backup file creation. You must also specify the file name pattern that this rule should apply to.

To store the backup in Microsoft Azure Storage, you must specify the name of the account, the storage access key, and the name of the container.  You can retrieve the name of the storage account and the access key information by logging into the Microsoft Azure management portal.

At this time, you can also specify whether or not you wish to have the backup files encrypted or compressed.

Once you have created one or more rules, you will see the existing rules and the option to Modify or Delete the rule.

Step 4: Restore a Database from a Backup Taken with SQL Server Backup to Microsoft Azure Tool in place. The SQL Server Backup to Microsoft Azure Tool creates a ‘stub’ file with some metadata to use during restore.  Use this file like your regular backup file when you wish to restore a database.  SQL Server uses the metadata from this file and the backup on Microsoft Azure storage to complete the restore. 

If the stub file is ever deleted, you can recover a copy of it from the Microsoft Azure storage container in which the backups are stored.  Place the stub file into a folder on the local machine where the Tool is configured to detect and upload backup files.

That’s all it takes!  Now you’re up and running with Backup to and Restore from Microsoft Azure.

To learn more about why to back up to the cloud, join Forrester Research analyst Noel Yuhanna in a webinar on Database Cloud Backup and Disaster Recovery.  You’ll find out why enterprises should make database cloud backup and DR part of their enterprise database strategy. 

The webinar takes place on Tuesday, 7/29 at 9 AM Pacific time; register now.

SQL Server Team Blog

Sentiment Analysis with Microsoft APS and StreamInsight

In this overview and demo, we will show you what sentiment analysis is and how to build a quick mashup that combines real-time access to multiple data sources using tools from Microsoft.

Sentiment analysis is one of the hottest topics in the Big Data space. Sentiment analysis is the process of analyzing customer comments and feedback from Facebook, Twitter, Email, and more. The purpose of the analysis is to understand the overall sentiment the customer is trying to convey. This could be a negative sentiment, when the customer is unhappy with a company or its product. Neutral sentiment, when the customer is only mentioning a company or product, in passing, without a good or a bad feeling. The last is positive sentiment, when a customer is happy or excited about a company or its product.

Traditionally sentiment analysis was complicated because it required a mixture of very complex platforms and tools. Each component required for sentiment analysis was offered from a different company and required a large amount of custom work. The difficulty is further exasperated by hard-to- achieve business requirements. When we discuss sentiment analysis there are 3 key business requirements we see repeated:

  • Real-time access
  • Full granular data set (structured & unstructured)
  • BI and SQL front-end

Real-time Access

In the case of real-time access, business users need access to fresh data. In the world of social media, customer sentiment can change rapidly. With images and videos quickly being posted with re-tweets and Facebook ‘like’ capabilities, a good or bad aspect of a company’s product can go viral in minutes. Business users need to have the ability to analyze data as it comes in, in real-time. We will show in our overview video and demo, how we can utilize Microsoft’s StreamInsight technology for real-time data analysis and complex-event processing.

Full Granular Data Set

In the case of full granular data, in practice we have seen that using a traditional database system can hinder development. This is because a lot of the data that comes in for sentiment analysis such as email, is in a semi-structured or unstructured format. This means the data is not easily modeled into a database. The data does not come in a simple row/column format. Thus we utilize our Big Data technology that is meant for this type of data:  HDInsight (Hadoop). HDInsight is essentially Hortonworks Data Platform running on Windows. In our case we utilize HDInsight to land all of the data, in its raw original format, into the distributed file system HDFS. This allows us to ingest any kind of data, regardless of structure, and store that data online for further analysis at low cost. The Hadoop software is open-source and readily available.

BI and SQL Front-End

The most important area around delivering sentiment analysis to the business is access, making sure we are able to provide the data both in real-time (and high-fidelity) within the tools that our business users know and love. Previously when our customers were doing sentiment analysis on Hadoop systems, BI and SQL access was not available. This was not because the tools could not integrate with Hadoop systems. This was because they could not scale or have the same level of functionality. Some BI users have chosen Hive ODBC in Hadoop, which many claim to be slow and ‘buggy’. Instead here we utilize one of our flagship technologies: PolyBase. With PolyBase we expose the data in Hadoop, and relational SQL Server, with one T-SQL query. What this means is users can use BI tools like Excel, SSAS, or other 3rd party tools. They can then utilize PolyBase within Analytics Platform System (APS) to query that data either in Hadoop, or Parallel Data Warehouse (SQL Server), or mash up the data from both systems!

How It Works

Now we will show you how to use all of the tools from the SQL Server data platform to achieve sentiment analysis. This will allow you to quickly deploy and meet all 3 business requirements through a set of tools and platforms that are very easy to use, fully integrated, and ‘just work’ together.

Let’s get started with the first video (~5 minutes) where we present sentiment analysis using Microsoft technologies. We show you how sentiment analysis works, and how the Microsoft products fit. We then follow up by discussing the architecture in detail surrounding StreamInsight, HDInsight, and Analytics Platform System.

Watch the overview video:

Demo

In the second video (~7 minutes), we show you sentiment analysis in action. The demo will include a full sentiment-analysis engine running in real-time against Twitter data along with a web dashboard. We then stream Twitter data to both HDInsight and Parallel Data Warehouse. Finally, we end the demo by showcasing PolyBase, our flagship technology. With Polybase we can do data mashups combining data from relational and non-relational systems. We will use Polybase to write standard T-SQL queries against this data to determine tweet analytics and how social sentiment is fairing for our marketing campaigns and products.

Watch the demo video:

SQL Server Team Blog

1 2 3 14  Scroll to top